Как получить несколько строк из хранимой функции с оракулом

Я пытаюсь создать хранимую функцию в Oracle, которая возвращает несколько строк.

Мой вопрос очень похож на этот, за исключением того, что я хочу получить select * запрос

Короче говоря, я хочу создать функцию, которая возвращает результат этого запроса

select * from t_email_queue

Я попробовал вот что:

create or replace
PACKAGE email_queue AS 

  type t_email_queue_type is table of T_EMAIL_QUEUE%ROWTYPE;  

  FUNCTION lock_and_get return t_email_queue_type;

END email_queue;

create or replace
PACKAGE BODY email_queue AS 

    FUNCTION lock_and_get RETURN t_email_queue_type AS 
      queue_obj t_email_queue_type;

      cursor c (lockid in varchar2) is select * from t_email_queue where lockedby = lockid;
      lockid varchar2(100) := 'alf';
    BEGIN

      OPEN c(lockid);
      FETCH c bulk collect INTO queue_obj;

      return queue_obj;

    END lock_and_get;

END email_queue;

Пакет компилируется просто отлично, но когда я пытаюсь вызвать его с помощью этого запроса

select * from table(email_queue.lock_and_get);

Oracle выдает следующую ошибку

ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"
*Cause:    
*Action:
Error at Line: 1 Column: 20

Я думаю, что Oracle хочет, чтобы я создал свой тип возврата на уровне схемы, но когда я пытаюсь сделать

create type t_email_queue_type is table of T_EMAIL_QUEUE%ROWTYPE;

Оракул жалуется

Type IMAIL.T_EMAIL_QUEUE_TYPE@imail dev
Error(1): PL/SQL: Compilation unit analysis terminated
Error(2,37): PLS-00329: schema-level type has illegal reference to IMAIL.T_EMAIL_QUEUE

Может ли кто-нибудь указать мне правильное направление? Что мне здесь не хватает?

Спасибо за прочтение!

Источник

2 ответа

Решение

С типами SQL вы не можете сделать%ROWTYPE, вам придется вводить каждый столбец в соответствии с таблицей *.

* sys.anydataset в сторону. но идти по этому пути намного сложнее.

например, если ваш стол был

create table foo (id number, cola varchar2(1));

затем

create type email_queue_type is object (id number, cola varchar2(1));
/
create type t_email_queue_type as table of email_queue_type;
/

и используйте эту таблицу email_queue_type_tab в качестве вывода из вашей функции.

но я бы порекомендовал конвейерную функцию, так как ваш текущий код не масштабируется.

например:

SQL> create table foo (id number, cola varchar2(1));

Table created.

SQL>
SQL> create type email_queue_type is object (id number, cola varchar2(1));
  2  /

Type created.

SQL> create type t_email_queue_type as table of email_queue_type;
  2  /

Type created.

SQL> insert into foo select rownum, 'a' from dual connect by level <= 10;

10 rows created.

SQL>
SQL> create or replace PACKAGE email_queue AS
  2
  3
  4    FUNCTION lock_and_get return t_email_queue_type pipelined;
  5
  6  END email_queue;
  7  /

Package created.

SQL> create or replace PACKAGE BODY email_queue AS
  2
  3      FUNCTION lock_and_get RETURN t_email_queue_type pipelined AS
  4        queue_obj t_email_queue_type;
  5
  6      BEGIN
  7
  8       for r_row in (select * from foo)
  9              loop
 10                pipe row(email_queue_type(r_row.id, r_row.cola));
 11              end loop;
 12
 13      END lock_and_get;
 14
 15  END email_queue;
 16  /

Package body created.

SQL> select * from table(email_queue.lock_and_get());

        ID C
---------- -
         1 a
         2 a
         3 a
         4 a
         5 a
         6 a
         7 a
         8 a
         9 a
        10 a

10 rows selected.

SQL>
Источник

Если вы не особенно заинтересованы в том, чтобы иметь тип SQL, вы можете сделать это с помощью sys_refcursor вместо:

create or replace package email_queue as 
    function lock_and_get return sys_refcursor;
end email_queue;
/

create or replace package body email_queue as 
    function lock_and_get return sys_refcursor AS 
        c sys_refcursor;
        lockid varchar2(100) := 'alf';
    begin
        open c for
            select * from t_email_queue
            where lockedby = lockid;

         return c;
    end lock_and_get;
end email_queue;
/

Из SQL*Plus вы можете назвать это примерно так:

var cur refcursor;
exec :cur := email_queue.lock_and_get;
print cur

и в качестве exec является сокращением для простого анонимного блока, который вы можете вызывать из других объектов PL/SQL. Что вы не можете сделать, это:

select * from table(email_queue.lock_and_get);

Я не знаком с вызовом функций из PHP, но из Java вы можете использовать это непосредственно как возвращение из вызываемого оператора, поэтому вам не нужно select * from table() построить на всех. Я понятия не имею, если вы можете выполнить анонимный блок в вызове PHP, что-то вроде begin $cur = email_queue.lock_and_get; end;, и имеют $cur как ваш набор результатов, который вы можете затем перебрать?

Я понимаю, что это не полный ответ, так как сторона PHP слишком расплывчата, но может дать вам некоторые идеи.

Источник

Если вы используете PHP и хотите получить доступ к сохраненной функции оракула. Вы можете использовать что-то вроде этого

//Your connection details
$conn = oci_connect($username, $password, '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))' );

/* Your query string; you can use oci_bind_by_name to bind parameters or just pass the variable in it*/

$query = "begin :cur := functionName('".$param1."','".$param2."','".$param3."'); end;";
$stid = oci_parse($conn, $query); 
$OUTPUT_CUR = oci_new_cursor($conn);
oci_bind_by_name($stid, ':cur', $OUTPUT_CUR, -1, OCI_B_CURSOR);
oci_execute($stid); 
oci_execute($OUTPUT_CUR);
oci_fetch_all($OUTPUT_CUR, $res);

// To get your result  
var_dump($res);

Надеюсь, это поможет.

Источник
Другие вопросы по тегам