AIDE. Paramétrer un curseur.

**Lau.c** · 26/10/2010, 11h50

Bonjour, je vous explique en quelques phrases mon problème.

J'ai différentes procédures qui tournent (et qui fonctionnent) et qui me permettent, au final, de générer des statistiques.
Dans certaines de ces procédures, il me faudrait insérer des données dans une table à partir d'un select.
Donc j'ai mis en place une boucle basée sur un curseur. Ca fonctionne.
Cependant, ca fait un brol pas possible, c'est pas super lisible.

Donc, sachant que la table cible est toujours la même et que seule la requete liée au curseur change, j'aurais voulu faire une procédure qui ne s'occupe que de l'insert et de la boucle sur le curseur.

J'ai essayé en injectant la clause WHERE, mais une erreur d'operateur apparait. Normal puisqu'il attend une comparaison.
Dans ce genre là:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
PROCEDURE INSERT_VALUES_FROM_CURSOR (StatDay IN DATE, STAT_NAME VARCHAR2, WHERE_CLAUSE VARCHAR2) IS
CURSOR CURS IS
	SELECT STAT_DAY, RECORD_ID FROM TEMPDATA WHERE WHERE_CLAUSE;
BEGIN
	FOR Cur IN CURS
	LOOP
		INSERT INTO TABLE_CIBLE(FK_RECORD_ID, STAT_DATE, STAT_NAME) VALUES (Cur.RECORD_ID, TO_TIMESTAMP(StatDay), STAT_NAME);
	END LOOP ;
END;

Ensuite, j'ai eu l'idée de donner carrément le 'SQL statement' complet au curseur mais sans succès.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
PROCEDURE INSERT_VALUES_FROM_CURSOR (StatDay IN DATE, STAT_NAME VARCHAR2, SQL_STATEMENT VARCHAR2) IS
CURSOR CURS IS
	SQL_STATEMENT;
BEGIN
	FOR Cur IN CURS
	LOOP
		INSERT INTO TABLE_CIBLE(FK_RECORD_ID, STAT_DATE, STAT_NAME) VALUES (Cur.RECORD_ID, TO_TIMESTAMP(StatDay), STAT_NAME);
	END LOOP ;
END;

Donc pour récapituler, mon besoin est de pouvoir paramétrer la requête derrière mon curseur en fonction d'un parametre à lui donner (que ce soit une chaine de carcatère ou autre). Je précise que j'ai jamais touché à PL/SQL avant vendredi. Je suis donc un novice total.

Edit: L'environnement est en 10g ou 11g. Je ne me souviens plus bien. A priori, si c'est la même version que l'application server, je dirais 11g

Quelqu'un peu gentiment m'aider? :p

Merci d'avance.

**Garuda** · 26/10/2010, 12h06

rechercher "SQL DYNAMIQUE" (EXECUTE IMMEDIATE ou DBMS_SQL)

**Lau.c** · 26/10/2010, 12h10

Envoyé par Garuda

rechercher "SQL DYNAMIQUE" (EXECUTE IMMEDIATE ou DBMS_SQL)

Mouarf, ca m'aurais épargné bcp de temps si j'avais été unpeu plus curieux du EXECUTE IMMEDIATE. Sorry.
J pensais, à tord, qu'il s'agissait justement d'une execution brute, vu son nom.

Et concernant ma recherche sur le SQL dynamique, déjà fait sur Google, mais pas tjs simple à tout comprendre pour une néophyte.

Bref, désolé du dérangement. J vais essayer ca.

**cala** · 26/10/2010, 12h23

Bonjour,

Voici une solution. L'utilisation du SYS_REFCURSOR fournit de bonne performance et permet de recevoir en paramêtre une chaine de cratère.
Il y a d'autre méthode suivant si tu veux passé en paramêtre directement une requête SQL et du client appelant ta procédure.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
PROCEDURE INSERT_VALUES_FROM_CURSOR (StatDay IN DATE, STAT_NAME VARCHAR2, WHERE_CLAUSE VARCHAR2) IS
  SQL_CURSOR SYS_REFCURSOR ;
  RECORD_ID VARCHAR2(20);
 
BEGIN
        OPEN SQL_CURSOR FOR 'SELECT RECORD_ID FROM TEMPDATA WHERE ' || WHERE_CLAUSE;
 
	LOOP
                FETCH SQL_CURSOR INTO RECORD_ID;
                EXIT WHEN SQL_CURSOR%NOTFOUND;
		INSERT INTO TABLE_CIBLE(FK_RECORD_ID, STAT_DATE, STAT_NAME) VALUES (RECORD_ID, TO_TIMESTAMP(StatDay), STAT_NAME);
	END LOOP ;
END;

Cette solution est valable pour tes deux cas.

Salutations,

Calà

**Lau.c** · 26/10/2010, 12h33

Envoyé par cala

Bonjour,

Voici une solution. L'utilisation du SYS_REFCURSOR fournit de bonne performance et permet de recevoir en paramêtre une chaine de cratère.
Il y a d'autre méthode suivant si tu veux passé en paramêtre directement une requête SQL et du client appelant ta procédure.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
PROCEDURE INSERT_VALUES_FROM_CURSOR (StatDay IN DATE, STAT_NAME VARCHAR2, WHERE_CLAUSE VARCHAR2) IS
  SQL_CURSOR SYS_REFCURSOR ;
  RECORD_ID VARCHAR2(20);
 
BEGIN
        OPEN SQL_CURSOR FOR 'SELECT RECORD_ID FROM TEMPDATA WHERE ' || WHERE_CLAUSE;
 
	LOOP
                FETCH SQL_CURSOR INTO RECORD_ID;
                EXIT WHEN SQL_CURSOR%NOTFOUND;
		INSERT INTO TABLE_CIBLE(FK_RECORD_ID, STAT_DATE, STAT_NAME) VALUES (RECORD_ID, TO_TIMESTAMP(StatDay), STAT_NAME);
	END LOOP ;
END;

Cette solution est valable pour tes deux cas.

Salutations,

Calà

Ste syntaxe Ó_ò !!Wow!
Merci, j vais aller tester ca.

Parce que je pense que j'ai pas trop compris la syntaxe du SQL dynamique.

Voyez vous même:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
PROCEDURE INSERT_VALUES_FROM_CURSOR (StatDay IN DATE, STAT_NAME VARCHAR2, OTHER_WHERE_CLAUSE_PARAMS VARCHAR2) IS
sqlStmt VARCHAR2 := 'SELECT STAT_DAY, RECORD_ID FROM TEMPDATA WHERE STAT_DAY =' || TO_CHAR(StatDay,'YYYYMMDD') || OTHER_WHERE_CLAUSE_PARAMS;
CURSOR CURS IS
	EXECUTE IMMEDIATE sqlStmt;
BEGIN
	FOR Cur IN CURS
	LOOP
		INSERT INTO TABLE_CIBLE(FK_RECORD_ID, STAT_DATE, STAT_NAME) VALUES (Cur.RECORD_ID, TO_TIMESTAMP(StatDay), STAT_NAME);
	END LOOP ;
END;

ce qui donne: Error(73,2): PLS-00103: Encountered the symbol "EXECUTE" when expecting one of the following: ( select <a SQL statement>

En tous cas, merci pour votre aide

**Garuda** · 26/10/2010, 13h29

Tu devrais encore un peu potasser le EXECUTE IMMEDIATE

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
PROCEDURE INSERT_VALUES_FROM_CURSOR (
   p_StatDay                     IN DATE,
   p_STAT_NAME                      VARCHAR2,
   p_OTHER_WHERE_CLAUSE_PARAMS      VARCHAR2
)
IS
BEGIN
   EXECUTE IMMEDIATE 'INSERT INTO TABLE_CIBLE(FK_RECORD_ID, STAT_DATE, STAT_NAME)'
                    || 'SELECT RECORD_ID, TO_TIMESTAMP(StatDay),'''
                    || P_STAT_NAME||''','
                    || ' FROM TEMPDATA'
                    || ' WHERE STAT_DAY ='''
                    || TO_CHAR (p_StatDay, 'YYYYMMDD')||''''
                    || p_OTHER_WHERE_CLAUSE_PARAMS;
END;

**Garuda** · 26/10/2010, 13h33

Et le SQL aussi un peu

!

**OracleFan** · 26/10/2010, 14h10

J'opterai plutot pour un bulk bind...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
create or replace INSERT_VALUES_FROM_CURSOR is
  type MonType is table of TEMPDATA%ROWTYPE;
  uneVarDeTypeMonType MonType;
 
begin
    select STAT_DAY, RECORD_ID .... BULK COLLECT INTO uneVarDeTypeMonType
      from TEMPDATA WHERE STAT_DAY = ...;
 
    forall x in uneVarDeTypeMonType.First .. uneVarDeTypeMonType.Last
       insert into TABLE_CIBLE values uneVarDeTypeMonType(x) ;
end;

Le code n'est pas testé.

Je propose ceci car c'est ce qui est conseillé. Lorsque tu réalise des insert dans une boucle, pour éviter le changement de contexte (entre SQL et PL/SQL), il est recommandé d'utiliser le bulk binding.

**Garuda** · 26/10/2010, 14h42

Envoyé par OracleFan

J'opterai plutot pour un bulk bind...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
create or replace INSERT_VALUES_FROM_CURSOR is
  type MonType is table of TEMPDATA%ROWTYPE;
  uneVarDeTypeMonType MonType;
 
begin
    select STAT_DAY, RECORD_ID .... BULK COLLECT INTO uneVarDeTypeMonType
      from TEMPDATA WHERE STAT_DAY = ...;
 
    forall x in uneVarDeTypeMonType.First .. uneVarDeTypeMonType.Last
       insert into TABLE_CIBLE values uneVarDeTypeMonType(x) ;
end;

Le code n'est pas testé.

Je propose ceci car c'est ce qui est conseillé. Lorsque tu réalise des insert dans une boucle, pour éviter le changement de contexte (entre SQL et PL/SQL), il est recommandé d'utiliser le bulk binding.

Ce qui ne répond pas au where dynamique !
D'autre part, inutile de faire une boucle !!!
UN 'INSERT INTO VALUES(....) SELECT...FROM' est nettement plus adapté, rapide et maintenable !

la meme avec du BINDING (don't forget to commit !)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
PROCEDURE INSERT_VALUES_FROM_CURSOR (
   p_StatDay                     IN DATE,
   p_STAT_NAME                      VARCHAR2,
   p_OTHER_WHERE_CLAUSE_PARAMS      VARCHAR2
)
IS
BEGIN
   EXECUTE IMMEDIATE 'INSERT INTO TABLE_CIBLE(FK_RECORD_ID, STAT_DATE, STAT_NAME)'||
   ' SELECT RECORD_ID, TO_TIMESTAMP(StatDay),:V_STAT_NAME FROM TEMPDATA'||
   ' WHERE STAT_DAY =TO_CHAR (:V_StatDay, ''YYYYMMDD'')'||P_OTHER_WHERE_CLAUSE_PARAMS
   USING p_stat_name,p_statday;
END;

Envoyé par OracleFan

J'opterai plutot pour un bulk bind...
....

Je propose ceci car c'est ce qui est conseillé....

Par qui ?????

**OracleFan** · 26/10/2010, 15h38

Envoyé par Garuda

Ce qui ne répond pas au where dynamique !
D'autre part, inutile de faire une boucle !!!
UN 'INSERT INTO VALUES(....) SELECT...FROM' est nettement plus adapté, rapide et maintenable !

la meme avec du BINDING (don't forget to commit !)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
PROCEDURE INSERT_VALUES_FROM_CURSOR (
   p_StatDay                     IN DATE,
   p_STAT_NAME                      VARCHAR2,
   p_OTHER_WHERE_CLAUSE_PARAMS      VARCHAR2
)
IS
BEGIN
   EXECUTE IMMEDIATE 'INSERT INTO TABLE_CIBLE(FK_RECORD_ID, STAT_DATE, STAT_NAME)'||
   ' SELECT RECORD_ID, TO_TIMESTAMP(StatDay),:V_STAT_NAME FROM TEMPDATA'||
   ' WHERE STAT_DAY =TO_CHAR (:V_StatDay, ''YYYYMMDD'')'||P_OTHER_WHERE_CLAUSE_PARAMS
   USING p_stat_name,p_statday;
END;

Par qui ?????

Dans le message 5, tu trouves

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
PROCEDURE INSERT_VALUES_FROM_CURSOR (StatDay IN DATE, STAT_NAME VARCHAR2, OTHER_WHERE_CLAUSE_PARAMS VARCHAR2) IS
sqlStmt VARCHAR2 := 'SELECT STAT_DAY, RECORD_ID FROM TEMPDATA WHERE STAT_DAY =' || TO_CHAR(StatDay,'YYYYMMDD') || OTHER_WHERE_CLAUSE_PARAMS;
CURSOR CURS IS
	EXECUTE IMMEDIATE sqlStmt;
BEGIN
	FOR Cur IN CURS
	LOOP
		INSERT INTO TABLE_CIBLE(FK_RECORD_ID, STAT_DATE, STAT_NAME) VALUES (Cur.RECORD_ID, TO_TIMESTAMP(StatDay), STAT_NAME);
	END LOOP ;
END;

Et je disais juste que c'est pas bon de faire ça et DONC UN BULK COLLECT est plus intéressant... Pour le reste, j'ai même pas fait attention. Il a un where dynamique? Ok, ben alors soit tu utilise un Cursor paramétrer soit soit un SQL Dynamique (DBMS_SQL) ou un EXECUTE...

Pas la peine de monter sur ses grands chevaux!!!

**Lau.c** · 27/10/2010, 08h09

Re,

j'avoue le SQL et moi, ca fait 3.
Fin bref, je suis quand même arrivé à mes fins hier en debut d'aprem.

Donc je marque ce poste en RESOLU et vous fait plein de bisous

Merci pour le coup de pouce.

AIDE. Paramétrer un curseur.

PL/SQL Oracle

Discussions similaires

Partager

Partager