Executer une requête dans une procédure

Version imprimable

29/10/2008, 18h45
Sieldev

Executer une requête dans une procédure

Salut,

N' ayant jamais codé sur Oracle, je souhaiterai faire 2 choses assez simple:

1. Creer une procedure stockée qui execute uniquement une requête de selection.
De cette maniére:

create or replace procedure nomProcedure as
BEGIN
select * from ma table
END;

Ainsi lorsque j' execute la procédure stockée, j' ai les resultats de la requête.

2. Et diverses choses sur les fonctions notamment:

- Declarer un paramétre à une fonction
- Peut-on assigner une valeur à une variable via un select sur Oracle ? de cette maniére:
select @C_codeparent = C.CODEPARENT from ...
-Comment est structuré une boucle while sur Oracle ?

Cordialement Sieldev,
29/10/2008, 20h13
mnitu

Commence par lire ce tutoriel sur PL/SQL.
30/10/2008, 09h44
Sieldev

Ok super merci.

Pour ce qui est des fonctions c' est bon.

Mais pour ce qui est d' une procédure stockée contenant une requête de sélection, toujours pas.
Je rappel ce que j' ai dis plus haut, je souhaiterai faire ça:

Creer une procedure stockée qui execute uniquement une requête de selection.
Sur SQL Server je pense que le curseur est fait implicitement donc le code est assez simple
Code:

1 2 3 create procedure maProcedure as select * from maTable
Donc il me reste plus qu' a executer la procédure et le résultat de la requête s' affiche.

Par contre sur Oracle j' ai pas l' impression, j' ai commencé quelque chose:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 create or replace procedure REPORT_LIST_OF_OPENED_ARTICLES IS cursor C1 is select TS.CODETRACESESSION, OA.CODETRACESEARCH, etc ... from etc ... join etc ... where etc ... BEGIN /* Donc aprés je ne suis plus sur, si vous avez une idée pour la suite ? */ For Cur IN C1 Loop ? End loop; END; /

30/10/2008, 17h01
mnitu

Si c’est pour afficher uniquement tu utilise une vue.
Si c’est pour traiter les enregistrements tu utilise une boucle.
Si c’est pour exécuter une procédure qui renvoie une « liste d’enregistrements » tu utilise un refcursor.
Etc.
Il faut que tu précise tes besoins.
31/10/2008, 08h55
Sieldev

Oui se serait plutôt ça:

Citation:

Si c’est pour exécuter une procédure qui renvoie une « liste d’enregistrements » tu utilise un refcursor.

Code:

1
2
3
4
5
6
 
CREATE OR REPLACE Procedure Crs_Emp(p_refcrs Out Sys_Refcursor) Is
Begin
  Open p_refcrs For
    Select * From emp;
End;

En sqlplus

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
SQL> set autoprint on
SQL> variable rc refcursor
SQL> exec crs_emp(:rc);
 
Procédure PL/SQL terminée avec succès.
 
 
     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM
---------- ---------- --------- ---------- -------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17/12/80        800
        20
 
      7499 ALLEN      SALESMAN        7698 20/02/81       1600        300
        30
 
      7521 WARD       SALESMAN        7698 22/02/81       1250        500
        30
...

Ok merci beaucoup mnitu, ça marche bien sur SQL Plus.
Par contre sur Toad j' ai l' erreur ORA-01008 ("toutes les variables ne sont pas liées")

Je mets une des fonctions que j' ai faites sur SQL Server et son equivalent Oracle afin d' aider les personnes qui sont dans le même cas que moi:

SQL Server 2008:

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
create function ARBO_CAT_FR_REPORT_LON (@TN_codecat decimal(12))  returns varchar(100)
BEGIN
 
declare @C_codeparent decimal(12)
declare @resultat varchar(100)
 
select @resultat =  TN.NAME from bdc_TRACENAVIGATION TN where CODECATEGORIE = @TN_codecat
 
select  @C_codeparent = C.CODEPARENT
from bdc_CATEGORIES C, bdc_CATEGORIES_LANGUE CL
where C.CODECATEGORIE = @TN_codecat
and CL.CODECATEGORIE = CODEPARENT
 
while @C_codeparent > 0
begin
	set @TN_codecat = @C_codeparent
 
	select @resultat = @resultat + '/' + CL.NOM_CATEGORIE from bdc_CATEGORIES_LANGUE CL where CL.CODECATEGORIE = @TN_codecat and CODELANGUE = 1
 
	select  @C_codeparent = C.CODEPARENT
	from bdc_CATEGORIES C, bdc_CATEGORIES_LANGUE CL
	where C.CODECATEGORIE = @TN_codecat
	and CL.CODECATEGORIE = CODEPARENT
 
end
 
select @resultat = @resultat + '/' + CL.NOM_CATEGORIE from bdc_CATEGORIES_LANGUE CL where CL.CODECATEGORIE = 0 and CODELANGUE = 1
 
return @resultat
 
END

Oracle:

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
39
40
CREATE OR REPLACE function ARBO_CAT_FR_REPORT_LON(codecat IN bdc_TRACENAVIGATION.CODECATEGORIE%Type ) Return varchar2 is
 
C_codeparent bdc_TRACENAVIGATION.CODECATEGORIE%Type;
resultat varchar2(100);
resultCourant varchar2(100);
TN_codecat bdc_TRACENAVIGATION.CODECATEGORIE%Type;
 
BEGIN
 
	TN_codecat := codecat;
 
	select TN.NAME into resultat from bdc_TRACENAVIGATION TN where CODECATEGORIE = TN_codecat;
 
	select C.CODEPARENT into C_codeparent
	from bdc_CATEGORIES C, bdc_CATEGORIES_LANGUE CL
	where C.CODECATEGORIE = TN_codecat
	and CL.CODECATEGORIE = CODEPARENT;
 
 
	while C_codeparent > 0
	Loop
		TN_codecat := C_codeparent;
 
		select CL.NOM_CATEGORIE into resultCourant from bdc_CATEGORIES_LANGUE CL where CL.CODECATEGORIE = TN_codecat and CODELANGUE = 1;
		resultat := resultat + resultCourant + '/';
 
		select C.CODEPARENT into C_codeparent
		from bdc_CATEGORIES C, bdc_CATEGORIES_LANGUE CL
		where C.CODECATEGORIE = TN_codecat
		and CL.CODECATEGORIE = CODEPARENT;
 
	End loop;
 
	select CL.NOM_CATEGORIE into resultCourant from bdc_CATEGORIES_LANGUE CL where CL.CODECATEGORIE = 0 and CODELANGUE = 1;
	resultat := resultat + resultCourant + '/';
 
	Return (resultat);
 
END;
/

Autre chose qui peut être utile, faire la différence et obtenir la durée en seconde entre deux datetime:

SQL Server 2008:

Code:

DATEDIFF(ss, monDatetime1, monDatetime2)

Oracle:

Code:

1
2
3
4
5
to_char(monDatetime1, 'SSSSS') - to_char(monDatetime2, 'SSSSS')
 
 
/*Une autre maniére mais que j' ai pas essayé, à confirmer:*/
(monDatetime1 - monDatetime2) *24 *3600

31/10/2008, 11h51
mnitu

Citation:

Envoyé par Sieldev

Ok merci beaucoup mnitu, ça marche bien sur SQL Plus.
Par contre sur Toad j' ai l' erreur ORA-01008 ("toutes les variables ne sont pas liées")

...

Ca marche aussi avec Toad mais il "faut lier les variables" comme il le dit.