[Oracle10g][PL/SQL] Executer requete dans un FOR

Version imprimable

[Oracle10g][PL/SQL] Ref Cursor

Bonjour,

J'aimerais pouvoir executer une requête passé en paramètre dans une fonction PL/SQL

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE OR REPLACE
FUNCTION exemple (
    essai varchar2
) RETURN PLS_INTEGER
BEGIN
    EXECUTE IMMEDIATE essai; --> marche
 
    FOR r IN (
        SELECT champs1 from table1
    ) LOOP 
        delete from table2 where code=r.champs1
    END LOOP; --> marche
 
    FOR r IN (
        EXECUTE IMMEDIATE essai
    ) LOOP 
         delete from table2 where code=r.champs1
    END LOOP; --> marche pas
 
 
    return 1;
END exemple;

Je n'arrive pas a faire fonctionner ce code. Existe t'il d'autres solutions ?

Par exemple quelque chose qui permette de concatener une requete sql et un varchar ?

Code:

1
2
3
4
5
FOR r IN (
        SELECT champs1 from table1 where essai;
    ) LOOP 
        delete from table2 where code=r.champs1
    END LOOP;

Merci pour votre aide.

08/02/2007, 11h39
plaineR

Fais une recherche sur ref cursor tu devrais trouver ton bonheur ;)

Merci.
J'ai trouvé un exemple :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
CREATE TABLE employees (
empid   NUMBER(5),
empname VARCHAR2(30));
 
INSERT INTO employees (empid, empname) VALUES (1, 'Dan Morgan');
INSERT INTO employees (empid, empname) VALUES (2, 'Jack Cline');
INSERT INTO employees (empid, empname) VALUES (3, 'Caleb Small');
COMMIT;
---------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor SYS_REFCURSOR) IS
 
TYPE array_t IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;
 
rec_array array_t;
 
BEGIN
  FETCH p_cursor BULK COLLECT INTO rec_array;
 
  FOR i IN rec_array.FIRST .. rec_array.LAST
  LOOP
    dbms_output.put_line(rec_array(i));
  END LOOP;
END pass_ref_cur;
 
---------------------------------------------------------------------
 
set serveroutput on
 
DECLARE
 rec_array SYS_REFCURSOR;
BEGIN
  OPEN rec_array FOR
  'SELECT empname FROM employees';
 
  pass_ref_cur(rec_array);
  CLOSE rec_array;
END;

Cependant, je en comprend pas :

Code:

TYPE array_t IS TABLE OF VARCHAR2(4000)

Pourquoi ne pas créer un type de VARCHAR(30) comem dans la table ?

A quoi sert :

Code:

INDEX BY BINARY_INTEGER;

?

Merci.

12/02/2007, 17h03
plaineR

Pour deux raisons :
- la procédure est générique (=> s'applique à toutes les colonnes de toutes les tables)
- on ne peut charger une liste d'enregistrements que dans une collection

index by binary_integer indique que la collection est indexée par des entiers (contrairement à index by varchar2 où elle est indexée par des chaînes de caractères)

Pour plus d'informations sur les collections :
http://sheikyerbouti.developpez.com/...?page=Chap5#L5
12/02/2007, 17h53
pegase06
Citation:

Envoyé par plaineR

Pour deux raisons :
- la procédure est générique (=> s'applique à toutes les colonnes de toutes les tables)
- on ne peut charger une liste d'enregistrements que dans une collection

index by binary_integer indique que la collection est indexée par des entiers (contrairement à index by varchar2 où elle est indexée par des chaînes de caractères)

Pour plus d'informations sur les collections :
http://sheikyerbouti.developpez.com/...?page=Chap5#L5

Je récupère un requête séléctionnant un champ enregistré en NUMBER.
Je ne cherche pas à avoir une procédure générique, est ce que je peux faire :
Code:

1 2 TYPE array_t IS TABLE OF NUMBER; INDEX BY BINARY_INTEGER;
12/02/2007, 18h34
plaineR
C'est :
Code:

1 2 TYPE array_t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
(pas de point virgule après number)

Oui, techniquement cela va fonctionner si tu ne récupères que des number.
14/02/2007, 09h31
pegase06
J'ai une erreur provoquant une exception dans :
Code:

1 2 3 4 5 6 FETCH p_cursor BULK COLLECT INTO rec_array; FOR i IN rec_array.FIRST .. rec_array.LAST LOOP dbms_output.put_line(rec_array(i)); END LOOP;
La ligne FOR provoque une erreur quand rec_array est vide, c'est à dire que la requête de mon REFCURSOR ne retourne rien.
14/02/2007, 09h43
plaineR

Fais le test suivant avant ton for :

Code:

if rec_array.exists (0) then ...

J'ai un doute sur le 0, c'est peut-être 1 :roll:
14/02/2007, 11h20
pegase06

Citation:

Envoyé par plaineR

Fais le test suivant avant ton for :

Code:

if rec_array.exists (0) then ...

J'ai un doute sur le 0, c'est peut-être 1 :roll:

Après tests, ca marche bien avec le 1 (qui test si le 1er element existe, EXISTS(10) testera si le 10eme élement existe). :)