Forum |  HardWare.fr | News | Articles | PC | S'identifier | S'inscrire | Shop Recherche
1206 connectés 

  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  [SGDB] PL/SQL : Fonction qui retourne un curseur

 
 


 Mot :   Pseudo :  
 
Bas de page
Auteur Sujet :

[SGDB] PL/SQL : Fonction qui retourne un curseur

n°1401001
Arjuna
Aircraft Ident.: F-MBSD
Posté le 05-07-2006 à 12:19:42  profilanswer
 

Oracle 10g
 
J'ai besoin d'une fonction appelable en tant que TABLE dans un select.
 

Select * from fct()


 
En T-SQL, il suffit de faire un "returns table" à la déclaration de la fonction et roule ma poule.
En PL/SQL, j'ai jamais trouvé un exemple qui marche.
 
En gros, j'ai besoin de ça pour effectuer des traîtements "à la con" que je ne peux pas faire côté programmation (utilisation depuis un EDI très simple) :
 
Avec en entrée, un truc du genre :
 
Table T1 :
Id   val
1    2
2    5
3    -4
4    7
 
Et en sortie :
Id    cpt
1    2
2    7   (cumul de 1 et 2)
4    10 (ligne 3 négative, donc ignorée, mais comptabilisée, et ligne 4 prise en compte, en reprenant le cumul à partir de la ligne 3)
 
Algo :
 

Code :
  1. Cpt = 0
  2. For each row in cursor
  3.     Cpt = cpt + row.val
  4.     If row.val > 0 then
  5.         Insert into returncursor (id, cpt) values (row.id, row.cpt)
  6.     End if
  7. Next


 
Voilà, si qq1 avec un petit exemple tout bête à me filer, je serai ravi.
(pas le peine de se faire chier avec mon algo, juste un exemple minimaliste me suffirait...)
 
Merci !

mood
Publicité
Posté le 05-07-2006 à 12:19:42  profilanswer
 

n°1401098
orafrance
Posté le 05-07-2006 à 13:52:28  profilanswer
 

il faut utiliser le type REF CURSOR :
http://www.developpez.net/forums/s [...] ref+cursor
 
à noter qu'une vue c'est pas plus mal ;)


Message édité par orafrance le 05-07-2006 à 13:52:52
n°1401120
Arjuna
Aircraft Ident.: F-MBSD
Posté le 05-07-2006 à 14:23:25  profilanswer
 

ok, ça rejoint ce que j'avais déjà trouvé une fois où j'avais déjà eu le problème.
 
le souci, c'est le coup du "TYPE type_curseur IS REF CURSOR;"
 
en fait, pour expliquer mon problème dans le détail, et bien comprendre ma problématique.
 
Je bosse sur un ERP, dont la base se trouve être Oracle 10g.
Un outil permet d'utiliser les fonctionnalités de l'ERP, et surtout sa base, via le web.
 
Ca se compose de la façon suivante :
 
- Un fichier XML, qui va dire "donc, pour cette businessview, dedans, j'ai des view, avec des link et toussa"
- Un servlet java lit le fichier XML, fait sa tambouille à partir des "view" qui sont détaillées dedans, et retourne un flux XML hiérachisé qui contient les résultats des données.
- Ensuite, un rendu XSL est appliqué au flux XML
 
Mon problème, donc, c'est que :
- en XSL, on ne peut pas faire de traîtement complexes, du genre cumul progressif, mise d'un flag sur la première (et uniquement la première) ligne qui répond à une condition, etc.
- Les "view" sont définies par un simple "select bidule from machin where chouette = tataouine"
 
=> A cause de ce dernier point, je ne peux pas déclarer un type, une variable ou quoi que ce soit avant ma requête. Je suis limité à une et une seule requête SQL, qui doit tout faire
=> Et ensuite, j'ai aucun moyen de faire des traîtements (calculs) sur les résultats
 
En somme, la moindre ligne de PL est impossible à utiliser.
 
Bon, je sens que je vais faire le truc qui tue (je suis champion dans le domaine)
=> une fonction JS qui crée un objet
=> le XSL qui génère une ligne d'appel à ce JS pour chaque ligne
=> onload de la page, une fonction qui fait les traîtements en question, avant de faire l'affichage du résultat
 
C'est gore, j'aurais préféré éviter :/

n°1401121
Arjuna
Aircraft Ident.: F-MBSD
Posté le 05-07-2006 à 14:24:04  profilanswer
 

PS: sinon, tu me parles de vue. On peut mettre du PL dans une vue (:??:)
Si oui, comment ?

Message cité 1 fois
n°1401162
Arjuna
Aircraft Ident.: F-MBSD
Posté le 05-07-2006 à 15:38:00  profilanswer
 

gotcha ! j'ai refoundé au fond de ma cerverlle endormie la syntaxe de la mort qui tue la vie et qui marche :
 


create table wt_test
(id number, val number);
 
insert into wt_test (id, val) values (1, 10);
insert into wt_test (id, val) values (2, 20);
insert into wt_test (id, val) values (3, -5);
insert into wt_test (id, val) values (4, 15);
 
SELECT
   id,
      val,  
      SUM(val) over (PARTITION by null order by id ROWS UNBOUNDED PRECEDING ) AS SOMME_PARTIELLE
FROM wt_test
ORDER BY id;


 
:bounce:


Message édité par Arjuna le 05-07-2006 à 15:40:07
n°1401217
orafrance
Posté le 05-07-2006 à 16:23:31  profilanswer
 

Arjuna a écrit :

PS: sinon, tu me parles de vue. On peut mettre du PL dans une vue (:??:)
Si oui, comment ?


 
non :D

n°1401220
Arjuna
Aircraft Ident.: F-MBSD
Posté le 05-07-2006 à 16:25:45  profilanswer
 

je me disais aussi :D ;)

n°1401261
Arjuna
Aircraft Ident.: F-MBSD
Posté le 05-07-2006 à 17:04:09  profilanswer
 

ce que j'aime bien, c'est que vu que c'est du xml, la mise en forme de mes requête est perdue à chaque fois...
 
ça donne ce genre de trucs à réindenter à chaque fois que t'as une couille dans le potage :o c'est un peu reou :D
 

SELECT pro.sfapro, pro.sigdep, pro.codpro, prm.nompro, pro.codblocage, decode(sign(dsk.c01 - dsk.c02 - dsk.c03), -1, 0, dsk.c01 - dsk.c02 - dsk.c03) dispo, (dsk.c01 - dsk.c02 - dsk.c03 + dsk.c05 + dsk.c04) terme, round(tsc.prxtar, 2) prxtar, tsc.coddev, tsc.datfin, des.lib1 destination, pro.codzn3 dest, pro.motcle, max(nvl(n.numnew, 0)) numnew FROM wt_news n, wv_prm prm, tbl des, mev mevdes, tsc, mev mevtsc, dsk, mev mevdsk, parav, mev mevparav, pro, mev mevpro, fam, mev mevfam WHERE mevfam.codent = 'FAM' and mevfam.segment = 'PRO' and fam.codsoc = mevfam.codsoc_phy and fam.typtie = 'PRO' and mevpro.codsoc = mevfam.codsoc and mevpro.codent = 'PRO' and mevpro.segment = ' ' AND pro.codsoc = fam.codsoc and pro.fampro = fam.codefam AND pro.sfapro = fam.codesfa AND pro.ssfpro = fam.codessf and pro.codpro not like 'SAV%' and pro.suistk = 'S' and pro.codblocage not in ('ECH', 'INT') and ((pro.sigfou = ? and fam.codefam = 'BOU' and fam.codessf in (' ', 'SANS')) or (pro.ssfpro = fam.codessf)) and mevdsk.codsoc = pro.codsoc and mevdsk.codent = 'DSK' and mevdsk.segment = ' ' and dsk.codsoc = mevdsk.codsoc_phy and dsk.codpro = pro.codpro and dsk.sigdep = pro.sigdep and (pro.codblocage not in ('STO', 'SOM', 'FDS', 'NAP', 'SOL') or (pro.codblocage in ('STO', 'SOM', 'FDS', 'NAP', 'SOL') and (dsk.c01 > 0 or dsk.c02 > 0 or dsk.c03 > 0 or dsk.c04 > 0 or dsk.c05 > 0))) and exists (select null from tie dep, mev mevdep where mevdep.codsoc = dsk.codsoc and mevdep.codent = 'TIE' and mevdep.segment = 'DEP' and dep.codsoc = mevdep.codsoc and dep.typtie = 'DEP' and dep.sigtie = dsk.sigdep and dep.codzn1 = 'O' and dep.codett = 'ACT') and mevparav.codsoc = pro.codsoc and mevparav.codent = 'PARAV' and mevparav.segment = ' ' and parav.codsoc = mevparav.codsoc_phy and parav.codpar = 'DEVNAT' and mevtsc.codsoc = pro.codsoc and mevtsc.codent = 'TSC' and mevtsc.segment = 'V' and tsc.codsoc = mevtsc.codsoc_phy and tsc.achvte = 'V' and tsc.codpro = pro.codpro and tsc.datdeb = (select max(tsc2.datdeb) from tsc tsc2 where tsc2.codsoc = mevtsc.codsoc_phy and tsc2.achvte = tsc.achvte and tsc2.codpro = tsc.codpro and tsc2.coddev = tsc.coddev and tsc2.datdeb <= to_char(sysdate, 'YYYYMMDD') and decode(tsc2.datfin, ' ', to_char(sysdate, 'YYYYMMDD'), tsc2.datfin) >= to_char(sysdate, 'YYYYMMDD')) and decode(tsc.datfin, ' ', to_char(sysdate, 'YYYYMMDD'), tsc.datfin) >= to_char(sysdate, 'YYYYMMDD') and tsc.coddev = parav.para1 and mevdes.codsoc = pro.codsoc and mevdes.codent = 'TBL' and mevdes.segment = '101' and des.codsoc = mevdes.codsoc_phy and des.codtbl = '101' and des.cletbl = pro.codzn3 and prm.codsoc = pro.codsoc and prm.codpro = pro.codpro and n.codsoc(+) = pro.codsoc and n.codpro(+) like '%' || pro.codpro || '%' and to_char(sysdate, 'YYYYMMDD') between decode(rtrim(n.datdeb), null, to_char(sysdate, 'YYYYMMDD'), n.datdeb) and decode(rtrim(n.datfin), null, to_char(sysdate, 'YYYYMMDD'), n.datfin) and mevfam.codsoc = ? and fam.codesfa = ? and (fam.codessf = ? or (? = 'SANS' and fam.codessf = ' ')) and prm.codlan = ? and (n.typdes = ? or rtrim(n.typdes) is null) and ((null is not null [or pro.codpro = upper(?)] [or pro.refpro = upper(?)] [or fam.codessf = upper(?)] [or prm.nompro like concat('%', concat(upper(?), '%'))] [or prm.tradesig1 like concat('%', concat(upper(?), '%'))] [or prm.tradesig2 like concat('%', concat(upper(?), '%'))] [or prm.tradesig3 like concat('%', concat(upper(?), '%'))] [or fam.libfam like concat('%', concat(upper(?), '%'))]) [or ? = 'Y']) group by pro.sfapro, pro.sigdep, pro.codpro, prm.nompro, pro.codblocage, decode(sign(dsk.c01 - dsk.c02 - dsk.c03), -1, 0, dsk.c01 - dsk.c02 - dsk.c03), dsk.c01 - dsk.c02 - dsk.c03 + dsk.c05 + dsk.c04, tsc.prxtar, tsc.coddev, tsc.datfin, des.lib1, pro.codzn3, pro.motcle

n°1401714
orafrance
Posté le 06-07-2006 à 09:56:05  profilanswer
 

Voila qui devrait te plaire : http://www.sqlinform.com/  :D
 
Résultat :  

Citation :


SELECT  pro.sfapro,  
        pro.sigdep,  
        pro.codpro,  
        prm.nompro,  
        pro.codblocage,  
        decode(sign(dsk.c01 - dsk.c02 - dsk.c03), -1, 0, dsk.c01 - dsk.c02 - dsk.c03) dispo,  
        (dsk.c01 - dsk.c02 - dsk.c03 + dsk.c05 + dsk.c04) terme,  
        round(tsc.prxtar, 2) prxtar,  
        tsc.coddev,  
        tsc.datfin,  
        des.lib1 destination,  
        pro.codzn3 dest,  
        pro.motcle,  
        max(nvl(n.numnew, 0)) numnew  
FROM    wt_news n,  
        wv_prm prm,  
        tbl des,  
        mev mevdes,  
        tsc,  
        mev mevtsc,  
        dsk,  
        mev mevdsk,  
        parav,  
        mev mevparav,  
        pro,  
        mev mevpro,  
        fam,  
        mev mevfam  
WHERE   mevfam.codent      = 'FAM'  
        AND mevfam.segment = 'PRO'  
        AND fam.codsoc     = mevfam.codsoc_phy  
        AND fam.typtie     = 'PRO'  
        AND mevpro.codsoc  = mevfam.codsoc  
        AND mevpro.codent  = 'PRO'  
        AND mevpro.segment = ' '  
        AND pro.codsoc     = fam.codsoc  
        AND pro.fampro     = fam.codefam  
        AND pro.sfapro     = fam.codesfa  
        AND pro.ssfpro     = fam.codessf  
        AND pro.codpro not like 'SAV%'  
        AND pro.suistk = 'S'  
        AND pro.codblocage not in ('ECH', 'INT')  
        AND ((pro.sigfou = ?  
        AND fam.codefam  = 'BOU'  
        AND fam.codessf in (' ', 'SANS'))  
        OR (pro.ssfpro     = fam.codessf))  
        AND mevdsk.codsoc  = pro.codsoc  
        AND mevdsk.codent  = 'DSK'  
        AND mevdsk.segment = ' '  
        AND dsk.codsoc     = mevdsk.codsoc_phy  
        AND dsk.codpro     = pro.codpro  
        AND dsk.sigdep     = pro.sigdep  
        AND (pro.codblocage not in ('STO', 'SOM', 'FDS', 'NAP', 'SOL')  
        OR (pro.codblocage in ('STO', 'SOM', 'FDS', 'NAP', 'SOL')  
        AND (dsk.c01 > 0  
        OR dsk.c02   > 0  
        OR dsk.c03   > 0  
        OR dsk.c04   > 0  
        OR dsk.c05   > 0)))  
        AND exists  
        (SELECT null  
        FROM    tie dep,  
                mev mevdep  
        WHERE   mevdep.codsoc      = dsk.codsoc  
                AND mevdep.codent  = 'TIE'  
                AND mevdep.segment = 'DEP'  
                AND dep.codsoc     = mevdep.codsoc  
                AND dep.typtie     = 'DEP'  
                AND dep.sigtie     = dsk.sigdep  
                AND dep.codzn1     = 'O'  
                AND dep.codett     = 'ACT'
        )  
        AND mevparav.codsoc  = pro.codsoc  
        AND mevparav.codent  = 'PARAV'  
        AND mevparav.segment = ' '  
        AND parav.codsoc     = mevparav.codsoc_phy  
        AND parav.codpar     = 'DEVNAT'  
        AND mevtsc.codsoc    = pro.codsoc  
        AND mevtsc.codent    = 'TSC'  
        AND mevtsc.segment   = 'V'  
        AND tsc.codsoc       = mevtsc.codsoc_phy  
        AND tsc.achvte       = 'V'  
        AND tsc.codpro       = pro.codpro  
        AND tsc.datdeb       =  
        (SELECT max(tsc2.datdeb)  
        FROM    tsc tsc2  
        WHERE   tsc2.codsoc                                                              = mevtsc.codsoc_phy  
                AND tsc2.achvte                                                          = tsc.achvte  
                AND tsc2.codpro                                                          = tsc.codpro  
                AND tsc2.coddev                                                          = tsc.coddev  
                AND tsc2.datdeb                                                         <= to_char(sysdate, 'YYYYMMDD')  
                AND decode(tsc2.datfin, ' ', to_char(sysdate, 'YYYYMMDD'), tsc2.datfin) >= to_char(sysdate, 'YYYYMMDD')
        )  
        AND decode(tsc.datfin, ' ', to_char(sysdate, 'YYYYMMDD'), tsc.datfin) >= to_char(sysdate, 'YYYYMMDD')  
        AND tsc.coddev                                                         = parav.para1  
        AND mevdes.codsoc                                                      = pro.codsoc  
        AND mevdes.codent                                                      = 'TBL'  
        AND mevdes.segment                                                     = '101'  
        AND des.codsoc                                                         = mevdes.codsoc_phy  
        AND des.codtbl                                                         = '101'  
        AND des.cletbl                                                         = pro.codzn3  
        AND prm.codsoc                                                         = pro.codsoc  
        AND prm.codpro                                                         = pro.codpro  
        AND n.codsoc(+)                                                        = pro.codsoc  
        AND n.codpro(+) like '%' || pro.codpro || '%'  
        AND to_char(sysdate, 'YYYYMMDD') BETWEEN decode(rtrim(n.datdeb), null, to_char(sysdate, 'YYYYMMDD'), n.datdeb) AND decode(rtrim(n.datfin), null, to_char(sysdate, 'YYYYMMDD'), n.datfin)  
        AND mevfam.codsoc = ?  
        AND fam.codesfa   = ?  
        AND (fam.codessf  = ?  
        OR (?             = 'SANS'  
        AND fam.codessf   = ' '))  
        AND prm.codlan    = ?  
        AND (n.typdes     = ?  
        OR rtrim(n.typdes) is null)  
        AND ((null is not null [
        OR pro.codpro  = upper(?)] [
        OR pro.refpro  = upper(?)] [
        OR fam.codessf = upper(?)] [
        OR prm.nompro like concat('%', concat(upper(?), '%'))] [
        OR prm.tradesig1 like concat('%', concat(upper(?), '%'))] [
        OR prm.tradesig2 like concat('%', concat(upper(?), '%'))] [
        OR prm.tradesig3 like concat('%', concat(upper(?), '%'))] [
        OR fam.libfam like concat('%', concat(upper(?), '%'))]) [
        OR ? = 'Y'])  
GROUP BY pro.sfapro,  
        pro.sigdep,  
        pro.codpro,  
        prm.nompro,  
        pro.codblocage,  
        decode(sign(dsk.c01 - dsk.c02 - dsk.c03), -1, 0, dsk.c01 - dsk.c02 - dsk.c03),  
        dsk.c01 - dsk.c02 - dsk.c03 + dsk.c05 + dsk.c04,  
        tsc.prxtar,  
        tsc.coddev,  
        tsc.datfin,  
        des.lib1,  
        pro.codzn3,  
        pro.motcle

n°1401723
Arjuna
Aircraft Ident.: F-MBSD
Posté le 06-07-2006 à 10:03:04  profilanswer
 

:love:
 
alors, un grand merci beaucoup du fond du coeur !
 
(comme quoi, même un post inutile peut devenir utile :D)

mood
Publicité
Posté le 06-07-2006 à 10:03:04  profilanswer
 

n°1401732
orafrance
Posté le 06-07-2006 à 10:20:54  profilanswer
 

:jap: ;)


Aller à :
Ajouter une réponse
  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  [SGDB] PL/SQL : Fonction qui retourne un curseur

 

Sujets relatifs
Problème de type dans une requête SQL[SQL] Regrouper des tuples par familles...
En tete de champs dans SQL plusPasser des parametres dans une fonction JS
AVIS e AIDE sur Requete SQLProblème Passage de Paramètres à une fonction Javascript
[MySQL] Fonction recherche complexedate et php : extraire le mois d'une date SQL (debutant)
[matlab] fonction imwriteUtilisation de SQL SERVER
Plus de sujets relatifs à : [SGDB] PL/SQL : Fonction qui retourne un curseur

Forum MesDiscussions.Net, Version 2010.2
(c) 2000-2011 Doctissimo
Page générée en 0.047 secondes

Copyright © 1997-2022 Hardware.fr SARL (Signaler un contenu illicite / Données personnelles) / Groupe LDLC / Shop HFR