L'exécution d'une instruction sql stockée dans un paramètre de procédure

J'ai une question à propos des procédures stockées dans Oracle.
Ci-dessous la procédure stockée et tables comme il est:

create table STORES
(
    ID number,
    NAME varchar2(100),
    CITY varchar2(100),
    EXPIRES DATE
)

insert into stores values(1, 'Store 1', 'City 1', sysdate);
insert into stores values(2, 'Store 2', 'City 1', sysdate);
insert into stores values(3, 'Store 3', 'City 2', sysdate);

create table CLOSED
(
    ID number,
    NAME varchar2(100),
    CITY varchar2(100)
)

create or replace PROCEDURE 
pr_TestProc(subQuery  IN VARCHAR2)
IS
begin
    insert into CLOSED (ID, NAME, CITY) 
    select ID, NAME, CITY 
    from STORES 
    where ID in (1, 2, 3);
end;

Ce que je voudrais faire est de remplacer le "en" valeurs avec la sous-requête transmise en tant que paramètre.
Donc, si je lance la procédure comme:

execute pr_TestProc('select ID from STORES where EXPIRES <= sysdate');

La requête passée dans devrait être exécutée comme une sous-requête en cours d'exécution à l'intérieur de la procédure.
Quelque chose comme:

insert into CLOSED (ID, NAME, CITY) select ID, NAME, CITY 
from STORES 
where ID in (execute(subQuery));

Évidemment, cela ne fonctionne pas, mais quelle serait la meilleure façon d'y parvenir, ou est-ce même possible?

Grâce,
Brian

InformationsquelleAutor Lord Byron | 2012-06-20

Vous pouvez utiliser le SQL dynamique

 create or replace PROCEDURE pr_TestProc(subQuery  IN VARCHAR2)
 IS
   l_sql_stmt varchar2(1000);
 begin
   l_sql_stmt := 'insert into CLOSED (ID, NAME, CITY) ' ||
                 ' select ID, NAME, CITY ' ||
                 '   from STORES ' ||
                 '  where id in (' || subquery || ')';
   dbms_output.put_line( l_sql_stmt );
   EXECUTE IMMEDIATE l_sql_stmt;
 end;

SQL> execute pr_TestProc('select ID from STORES where EXPIRES <= sysdate');

PL/SQL procedure successfully completed.

SQL> column name format a20
SQL> column city format a20
SQL> select * from closed;

        ID NAME                 CITY
---------- -------------------- --------------------
         1 Store 1              City 1
         2 Store 2              City 1
         3 Store 3              City 2

Si vous êtes à l'appel de cette procédure rarement dans un système qui est relativement inactif, qui fonctionnera probablement de façon acceptable. Si vous appelez fréquemment avec les différents sous-requêtes, cependant, vous allez générer une tonne de non-partageable SQL. Qui va les obliger à Oracle à faire beaucoup de dur de l'analyse. Il sera également l'inondation de votre piscine partagée avec les non-partageable SQL, probablement en forçant les plans que vous souhaitez être mis en cache, forçant le plus dur de l'analyse lorsque ces instructions SQL sont exécutées ensuite de nouveau. Et si vous le faites assez rapidement, vous êtes susceptibles de finir par faire des erreurs (ou de causer d'autres procédés pour obtenir des erreurs) que l'Oracle ne pouvait pas allouer suffisamment de mémoire dans la piscine commune pour une requête particulière. De Plus, le SQL dynamique est plus difficile d'écrire, plus difficile à déboguer, vulnérables aux attaques par injection SQL, etc. il est donc généralement le système plus difficile à gérer.

Une solution plus élégante serait de passer dans une collection plutôt qu'une sous-requête serait de passer dans une collection

SQL> create type id_coll
  2      as table of number;
  3  /

Type created.


SQL> ed
Wrote file afiedt.buf

  1  create or replace PROCEDURE pr_TestProc( p_ids IN id_coll)
  2  is
  3  begin
  4      insert into CLOSED (ID, NAME, CITY)
  5      select ID, NAME, CITY
  6      from STORES
  7      where ID in (select column_value
  8                     from table( p_ids ) );
  9* end;
SQL> /

Procedure created.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_ids id_coll;
  3  begin
  4    select id
  5      bulk collect into l_ids
  6      from stores
  7     where expires <= sysdate;
  8    pr_TestProc( l_ids );
  9* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> select * from closed;

        ID NAME                 CITY
---------- -------------------- --------------------
         1 Store 1              City 1
         2 Store 2              City 1
         3 Store 3              City 2

InformationsquelleAutor Justin Cave

Peut-être que vous n'avez pas besoin de passer la requête dans la procédure stockée. Il suffit d'appeler la procédure stockée avec la requête en tant que paramètre.

create or replace PROCEDURE 
pr_TestProc(listOfIds_IN  IN integer)
IS
begin
ids integer := listOfIds_IN;
insert into CLOSED (ID, NAME, CITY) 
select ID, NAME, CITY from STORES where ID in (ids );
end;

Appeler la procédure stockée comme ceci:

pr_TestProc(SELECT id FROM Table WHERE condition)

InformationsquelleAutor Robert Niestroj

Vous devez vous connecter pour publier un commentaire.