Quelle est la meilleure méthode pour renvoyer des valeurs de colonne spécifiques à partir d'une procédure. Par exemple, le code ci-dessous ne fonctionne pas

/*DECLARATION*/
TYPE t_data IS TABLE OF Table1%ROWTYPE;

PROCEDURE get_values(data OUT t_data) AS    
  BEGIN    
    SELECT a.object_id, a.num, b.descrip
      BULK COLLECT INTO data
      FROM Table1 a INNER JOIN
           Table2 b ON (a.id = b.id)
     WHERE a.date IS NULL;                
  END get_values;

Dans le même scénario si j'utilise un SELECT *, ça marche ...

0
Dev Ngron 17 janv. 2017 à 17:09

2 réponses

Meilleure réponse

Je suppose que c'est un exercice sur la façon d'utiliser une procédure pour obtenir une liste de valeurs structurées; Je ne recommanderais jamais une telle approche pour obtenir des données à partir d'une table, préférant, si possible, une méthode SQL pure.

Vous semblez avoir des tableaux comme ceux-ci:

create table table1(id ,object_id, num, "date")  as (
    select 1, 1, 100, sysdate from dual union all
    select 2, 2, 200, null from dual
);
create table table2(id, descrip) as (
    select 1, 'desc1' from dual union all
    select 2, 'desc2' from dual
);

Vous essayez de créer une procédure qui renvoie un ensemble de lignes, où chaque ligne contient des éléments des deux tables; pour ce faire, vous devez créer un type qui correspond au résultat de votre requête de sélection.

Vous voudrez peut-être définir votre package comme ceci:

CREATE OR REPLACE PACKAGE yourPackage AS
    TYPE tRec IS RECORD /* made to match the columns you want to extract in your query */
    (
        object_id                               NUMBER,
        num                                     NUMBER,
        descrip                                 VARCHAR2(100)
    );

    TYPE tTab IS TABLE OF tRec;

    PROCEDURE get_values(data OUT tTab);
END yourPackage;
create or replace package body yourPackage as
PROCEDURE get_values(data OUT tTab) AS    
  BEGIN    
    SELECT a.object_id, a.num, b.descrip
      BULK COLLECT INTO data
      FROM Table1 a INNER JOIN
           Table2 b ON (a.id = b.id)
     WHERE a."date" IS NULL;                
  END get_values;
 end yourPackage ;

Vous pouvez appeler la procédure dans le package de cette manière:

declare
    someVar yourPackage.tTab;
begin
    yourPackage.get_values(someVar);
    --
    if someVar.first is not null then
        for i in someVar.first .. someVar.last loop
            dbms_output.put_line(someVar(i).object_id || ' - ' || someVar(i).num || ' - ' || someVar(i).descrip);
        end loop;
    end if;
end;

Et voici le résultat que vous obtenez:

2 - 200 - desc2
2
Aleksej 17 janv. 2017 à 14:56

Premièrement, vous ne pouvez pas créer un Type de tout table%rowtype en dehors d'un bloc PLSQL. Vous devez créer une table en tant que object, puis créer un type of that object. Ensuite, vous pouvez l'utiliser.

Voir ci-dessous:

CREATE OR REPLACE TYPE Table11 AS OBJECT
(
   id NUMBER,
   num NUMBER,
   description VARCHAR2 (20)
)

CREATE OR REPLACE TYPE t_data IS TABLE OF Table11;

CREATE OR REPLACE PROCEDURE get_values (v_data OUT t_data)
AS
BEGIN

   SELECT Table11 (a.row_id, 222, 'hello')
     BULK COLLECT INTO v_data
     FROM Table1 a INNER JOIN Table2 b 
     ON (a.row_id = b.appid)
    WHERE a.date IS NULL;

END get_values;

Exécution:

DECLARE
   v_var   t_data;
BEGIN
   get_values (v_var);

   FOR i IN 1 .. v_var.COUNT
   LOOP
      DBMS_OUTPUT.put_line (v_var (i).id ||'  ' ||v_var(i).num  ||'  ' || v_var(i).description );
   END LOOP;
END;

Production:

SQL> /
1  222  hello
2  222  hello

PL/SQL procedure successfully completed.
0
XING 17 janv. 2017 à 15:13