Cursor For avec requête prédéfinie

Version imprimable

05/05/2009, 09h05
ilalaina

Cursor For avec requête prédéfinie
Bonjour à tous.

J'ai une requête prédéfinie dans une chaîne, et je voudrais utiliser un curseur for pour cette requête.
Mon but étant d'éviter de déclarer les types des variables qui accueilleront le curseur.
C'est à dire du genre :
Code:

1 2 3 4 5 6 7 create or replace procedure test(p_sql varchar2) is cursor c_1 is p_sql; begin for c1 in c_1 loop ...; end loop; end test;
L'exemple est un peu bête mais c'est ce qui décrit le mieux ce que je voudrais faire.

Merci de votre aide.
05/05/2009, 10h09
rvfranck

Je ne pense pas que ce sera facile (voire possible), le SQL DYNAMIQUE est souvent utilisé pour faire ce genre de chose mais avec un motif de requête fixe où souvent seuls les noms des tables, des colonnes ou des parametres changent; et même là le type des variables est toujours précisé. 8O

Le lien que je t'ai filé, ne résolvera certainement pas ton problème, mais te donnera certainement des idées ;)

En tout cas si tu y arrives, j'aimerais bien savoir comment cela se fait :D
05/05/2009, 10h41
ilalaina

Citation:

Envoyé par rvfranck

SQL DYNAMIQUE est souvent utilisé pour faire ce genre de chose ... Le lien que je t'ai filé, ne résolvera certainement pas ton problème, mais te donnera certainement des idées ;)

En tout cas si tu y arrives, j'aimerais bien savoir comment cela se fait :D

J'ai déjà lu le doc, et cherché de ce côté mais je n'ai trouvé aucune solution :arf:
PS : J'utilise Oracle 8.

En fait ce que je voudrais faire c'est de créer un package qui factorise toutes les requêtes que j'utilise fréquemment, ainsi je n'aurai plus à ré ecrire ces requêtes dans chaque procédure que je crée, mais j'appelle simplement ledit package.
Ci-dessous un exemple simple :

La table :
Code:

1 2 3 4 5 6 7 create table operateurs(matricule varchar2(64), nom varchar2(128), prenom varchar2(128)); insert into operateurs values('1', 'sdfqsd', 'gfsdg'); insert into operateurs values('2', 'fggg', 'ghfdh'); insert into operateurs values('3', 'ghfd', 'hgfhd'); insert into operateurs values('4', 'gfhd', 'hdfh'); commit;
Le package :
Code:

1 2 3 4 5 6 CREATE OR REPLACE PACKAGE common AS TYPE TP_REF_CURSOR IS REF CURSOR; PROCEDURE get_oper(p_matr_oper IN operateurs.matricule%TYPE DEFAULT NULL, p_cur_res OUT COMMON.TP_REF_CURSOR); END;
Le corps du package :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE OR REPLACE PACKAGE BODY common AS PROCEDURE get_oper(p_matr_oper IN operateurs.matricule%TYPE DEFAULT NULL, p_cur_res OUT COMMON.TP_REF_CURSOR) IS v_req VARCHAR2(256); BEGIN v_req := 'SELECT * FROM operateurs where 1=1'; IF (p_matr_oper IS NOT NULL) THEN v_req := v_req || ' and matricule=''' || p_matr_oper || ''''; END IF; OPEN p_cur_res FOR v_req; END get_rdps_oper; END common;
et pour tester :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 DECLARE c_cursor COMMON.TP_REF_CURSOR; v_res operateurs%ROWTYPE; BEGIN common.get_oper(p_matr_oper => '1787', p_cur_res => c_cursor); LOOP FETCH c_cursor INTO v_res; EXIT WHEN c_cursor%NOTFOUND; dbms_output.put_line(v_res.nom||' '||v_res.prenom); END LOOP; CLOSE c_cursor; END;
Là c'est encore simple parce que je fais un select * et ensuite je peux utiliser %ROWNUM.
Mais quand je ne fais plus select * (en nommant les colonnes du select), ou quand je fais des jointures dans le curseur je ne peux plus utiliser %ROWTYPE parce qu'il y a plusieurs tables différentes, à moins de déclarer un à un les variables.

Il existe sûrement un meilleur moyen de réaliser ceci, pourrait un âme charitable m'aider?

Merci.

Finalement, voilà ce que j'ai fait : j'ai declaré un curseur (c_oper) dans le package et je fais reférence au %rowtype de ce curseur ensuite.

Table :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
create table OPERATEURS
(
  MATRICULE NUMBER not null,
  NOM       VARCHAR2(64)
)
;
alter table OPERATEURS
  add constraint CL_OPER_PK primary key (MATRICULE);
 
create table CONTACTS
(
  MATRICULE NUMBER,
  TEL_TYPE  VARCHAR2(64),
  TEL_NUM   VARCHAR2(64)
)
;
alter table CONTACTS
  add constraint CL_CONT_FK foreign key (MATRICULE)
  references OPERATEURS (MATRICULE);

Package :

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
 
CREATE OR REPLACE PACKAGE common AS
  CURSOR c_oper IS
    SELECT oper.nom, cont.tel_num
      FROM operateurs oper, contacts cont;
  TYPE REF_CURSOR IS REF CURSOR;
  PROCEDURE get_operateurs(p_matr     operateurs.matricule%TYPE DEFAULT NULL,
                           p_tel_type contacts.tel_type%TYPE DEFAULT NULL,
                           ret_cur    OUT ref_cursor);
END;
/
CREATE OR REPLACE PACKAGE BODY common AS
  PROCEDURE get_operateurs(p_matr     operateurs.matricule%TYPE DEFAULT NULL,
                           p_tel_type contacts.tel_type%TYPE DEFAULT NULL,
                           ret_cur    OUT ref_cursor) IS
    v_req VARCHAR2(1024);
  BEGIN
    v_req := 'select oper.nom, cont.tel_num from operateurs oper, contacts cont';
    v_req := v_req || ' where oper.matricule=cont.matricule';
    IF (p_matr IS NOT NULL) THEN
      v_req := v_req || ' and oper.matricule=:b_matr';
    ELSE
      v_req := v_req || ' and (1=1 or :b_matr is null)';
    END IF;
    IF (p_tel_type IS NOT NULL) THEN
      v_req := v_req || ' and cont.tel_type=:b_tel_type';
    ELSE
      v_req := v_req || ' and (1=1 or :b_tel_type is null)';
    END IF;
    OPEN ret_cur FOR v_req
      USING p_matr, p_tel_type;
  END;
END;
/

Test :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
-- Appel de la procédure
DECLARE
  c_cursor common.REF_CURSOR;
  v_res    common.c_oper%rowtype;
BEGIN
  common.get_operateurs (ret_cur => c_cursor);
  LOOP
    FETCH c_cursor
      INTO v_res;
    EXIT WHEN c_cursor%NOTFOUND;
    dbms_output.put_line(v_res.nom || ' ' || v_res.tel_num);
  END LOOP;
  CLOSE c_cursor;
END;
/

Je voudrais seulement avoir vos avis sur l'impact de la déclaration de ce curseur sur la performance.

Merci d'avance.