Retourner un select dans une procédure

Version imprimable

14/09/2012, 08h36
jmjmjm

Retourner un select dans une procédure

Bonjour, je travaille actuellement sur un projet comprenant une multitude de base SQL SERVER, souhaitant en migrer une partie sous oracle, je rencontre quelques difficultés notamment pour remplacer mes procédures SELECT.

Je m'explique, actuellement le serveur d'application interroge la base MSSQL à l'aide de procédure stockée, c'est à dire que si un utilisateur veut la liste des documents d'un auteur il appelle par exemple "select_document_par_auteur" en passant le paramètre @Auteur...

Sous oracle, on ne peut à priori pas retourner de select, est ce que je me trompe ?
J'ai vu qu'il fallait retourner un paramètre output de type cursor, est ce une bonne pratique ? comment faites vous pour avoir une procedure qui retourne une table.

Par avance Merci

Oui c'est ça

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
41
42
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> Create Or Replace Procedure Get_EmpByDept (
  2    Deptno          In  emp.deptno%Type,
  3    refCrs          Out sys_refcursor
  4  ) Is
  5  Begin
  6    Open refCrs For
  7      Select e.*
  8        From emp e
  9       Where e.deptno = Get_EmpByDept.deptno;
 10  End;
 11  /
 
Procedure created
SQL> set serveroutput on
SQL> Declare
  2    rc          sys_refcursor;
  3    Employee    emp%rowtype;
  4  Begin
  5    Get_EmpByDept(10, rc);
  6    Loop
  7      Fetch rc Into Employee;
  8      Exit When rc%NOTFOUND;
  9      --
 10      dbms_output.put_line(Employee.ename);
 11      --
 12    End Loop;
 13    Close rc;
 14  End;
 15  /
 
CLARK
KING
MILLER
 
PL/SQL procedure successfully completed
 
SQL>

14/09/2012, 10h07
Rams7s

Je dirais si. Mais vous ne cherchez probablement pas avec les bons mot clefs par contre. ;)
Pour savoir ce que vous cherchez, va falloir oublier le mot clef que vous utilisez sous SqlServer (select_document_par_auteur) et comprendre ce que vous voulez faire, c'est ca qu'il faudra chercher pour Oracle.

Mais on peut effectivement renvoyer un curseur, ou alors une collection, ou alors une collection ligne par ligne avec une fonction pipelined...
14/09/2012, 10h21
dariyoosh

Bonjour,

Comme l'exemple de notre ami Mnitu le montre, effectivement d'un point de vue technique c'est faisable mais on ne traduit pas directement SQLServer vers oracle, il faut donc savoir sous quelle forme est-ce que vous souhaitez récupérer les données de l'autre côté dans cet nouvel environnement.

Cordialement,
14/09/2012, 10h51
jmjmjm

A dariyoosh :
Idéalement je souhaiterai ne pas l'avoir dans un paramètre out.
Je souhaiterais que lorsque je fais appel à une procédure "selectTOTO"

Code:

(SELECT * FROM TOTO)

je récupère le résultat du select comme si j'avais executer le code SQL.

A priori ça pourrait être possible avec des functions sous oracle, vous me confirmer ?

A mnitu :
Si il faut passer par des paramètres out ça devrait pouvoir se faire coté code. Par contre tout le parc ne sera pas migré en Oracle tout de suite. J'ai fait des tests de serveurs liés MSSQL<->ORACLE ça fonctionne presque pour tout sauf justement les requêtes SELECT, si je passe un int en out pas de souci, je met un paramètre int en out dans sql server mais dans le cas d'un cursor, comment puis je faire ? il existe un type CURSOR dans MSSQL mais qui est réservé pour le parcours des ligne et ne peut être utilisé comme variable out.

Merci à vous

Probablement que la traduction de vos procédures en Oracle devrait passer tout simplement par les vues:

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
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> Create view v_emp as
  2  select * from emp
  3  /
 
View created
SQL> var deptno number
SQL> exec :deptno := 10
 
PL/SQL procedure successfully completed
deptno
---------
10
SQL> Select count(*)
  2    from v_emp
  3   Where deptno = :deptno
  4  /
 
  COUNT(*)
----------
         3
deptno
---------
10
 
SQL>

Citation:

Envoyé par jmjmjm

A dariyoosh :
Idéalement je souhaiterai ne pas l'avoir dans un paramètre out.
Je souhaiterais que lorsque je fais appel à une procédure "selectTOTO"

Code:

(SELECT * FROM TOTO)

je récupère le résultat du select comme si j'avais executer le code SQL.

De toute façon c'est sûr qu'il faut avoir un élément intermédiaire (object collection, CURSOR, etc.) afin de récupérer et stocker le résultat de la requête pour le traitement ultérieur. On n'est pas dans un contexte SQL*Plus où on exécute une simple requête SQL brute (d'au moins c'est ce que j'ai compris de votre problème, côté oracle, on est dans un environnement du code du genre PL/SQL, on est bien d'accord?).

Après si pour une raison quelconque vous ne souhaitez pas avoir le cursor en mode OUT de la procédure, une autre solution possible serait l'utilisation une fonction. Voici un exemple similaire:
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 DECLARE TYPE employee_ref_cursor IS REF CURSOR RETURN employees%ROWTYPE; FUNCTION ma_fonction(dept_id employees.department_id%TYPE) RETURN employee_ref_cursor AS l_ref_cursor employee_ref_cursor; BEGIN OPEN l_ref_cursor FOR SELECT * FROM employees WHERE department_id = dept_id; RETURN l_ref_cursor; END ma_fonction; BEGIN DECLARE test_mafoncion employee_ref_cursor; employee test_mafoncion%ROWTYPE; BEGIN test_mafoncion := ma_fonction(100); LOOP FETCH test_mafoncion INTO employee; EXIT WHEN test_mafoncion%NOTFOUND; DBMS_OUTPUT.PUT_LINE('employee_id = (' || employee.employee_id || ')'); END LOOP; END; END; /
Cordialement,