Comment faire pour récupérer plusieurs lignes à partir d'une fonction stockée avec oracle

Je suis en train de créer une fonction stockée dans oracle qui retourne plusieurs lignes.

Ma question est très similaire à cette une sauf que je veux extraire un select * requête

En un mot, je veux créer une fonction qui renvoie le résultat de cette requête

select * from t_email_queue

Ce que j'ai essayé est ce :

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;

Le paquet compile très bien, mais lorsque j'essaie de l'appeler avec cette requête

select * from table(email_queue.lock_and_get);

Oracle déclenche l'erreur suivante

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

Je pense Oracle veux me créer mon type de retour au schéma de niveau mais quand j'essaie de faire

create type t_email_queue_type is table of T_EMAIL_QUEUE%ROWTYPE;

Oracle se plaint

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

Quelqu'un peut-il m'indiquer la bonne direction? Ce qui me manque ici?

Merci pour la lecture!

OriginalL'auteur Alfwed | 2012-12-12

  1. 4

    avec des types SQL vous ne cant %ROWTYPE, vous avez le type de chaque colonne afin de correspondre à la table*.

    *sys.anydataset de côté. mais cette route est beaucoup plus complexe à coder.

    par exemple, si votre table a été 

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

    puis

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

    et l'utilisation de la table email_queue_type_tab que pour la sortie de votre fonction.

    mais je vous conseille un pipeline de fonction, comme votre code actuel n'est pas évolutive.

    par exemple:

    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>
    Le problème avec cette solution est que quand je vais avoir à ajouter une colonne à la table t_email_queue, je vais devoir modifier mon type email_queue_type ainsi et c'est une douleur; je vais devoir arrêter mes scripts PHP appelant le colis alors que je suis en modifiant le type et le package et ce n'est pas une option.
    êtes-vous sur 11g. si donc "éditions" peut signifier que vous pouvez mettre à jour le type de + /type sans arrêter le PHP app pour cela.
    Oui, je suis en cours d'exécution 11g. Cette fonction va nous appelle à peu près à chaque seconde. Vous êtes en train de dire que je vais être en mesure de modifier le tableau, le type et le paquet entre les 2?
    eh bien, vous a la table de problème avant, mais comme je l'ai dit avec les editions de modifier le type de + package dans une nouvelle édition (donc le code en cours d'exécution ne peux pas le voir). puis quand vous avez fini vous le définir comme actif, de sorte que les nouveaux appels de le voir.
    par exemple Un appel arrive, puis modifier le type (dans une nouvelle édition), alors B est en. B pourront toujours voir l'ancien type. je vous recommande de lire à ce sujet. par exemple voir ici youtube.com/watch?v=cZSIaZCWaOs&t=7m6s

    OriginalL'auteur DazzaL

  2. 4

    Si vous n'êtes pas particulièrement désireux de posséder le type SQL, vous pouvez le faire avec un sys_refcursor à la place:

    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;
/

    À partir de SQL*Plus, pourrait-on dire quelque chose comme:

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

    et comme exec est une abréviation pour un simple bloc anonyme vous pouvez appeler si d'autres PL/SQL objets trop. Ce que vous ne pouvez pas le faire, c'est:

    select * from table(email_queue.lock_and_get);

    Je ne suis pas familier avec les appels de fonctions à partir de PHP, mais à partir de Java, vous pouvez l'utiliser directement comme le retour d'un callable déclaration, de sorte que vous n'avez pas besoin de la select * from table() construire. Je n'ai aucune idée si vous pouvez exécuter un bloc anonyme dans un appel PHP, quelque chose comme begin $cur = email_queue.lock_and_get; end;, et ont $cur que votre jeu de résultats que vous pouvez alors parcourir?

    Je me rends compte que ce n'est pas une réponse complète comme le PHP côté est trop vague, mais il peut vous donner quelques idées.

    La bonne réponse. Je suis actuellement à l'aide de cette technique mais je ne l'aime pas beaucoup parce que l'appel à ce genre de fonction PHP est un peu bizarre. D'où ma question sur le "tableau des types de retour".
    peut-être un sens de la distinction, mais le retour à la référence de curseur comme un out paramètre dans une procédure est également une option. Probablement pas moins bizarre en PHP, mais je n'ai aucune idée *8-)

    OriginalL'auteur Alex Poole