Bonsoir Je suis Josiane, je ne connais pas grand chose en PL/SQL et je souhaiterais apprendre mais j'ai un souci. Mon patron vient de me donner un projet: lui donner des informations à partir d'une procédure PL/SQL. J'ai fait pour ma part la requete SQL qui me donnait ces résultats, mais il veut que je les lui donne dans une procedure PL/ SQL. Je souhaiterais donc avec votre aide que vous m'indiquiez comment construire ma procedure qui afficherait ces résultats en PL/ SQL.
voici mon script:

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
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
43
44
45
46
47
48
49
50
51
52
53
54
55
select TYPE_ABONNEMENT,
       CLIENT,
       CONTRAT,
       STATUT,
       sum(ACTIVES)"LIGNES_ACTIVES",
       sum(RESILIES) "LIGNES_RESILIES"
from
   (SELECT A.BCC_CONTRACT_NO AS CONTRAT,
           nvl(rtrim(cust.first_name||' '||father_name||' '||family_name),corporate_name) AS CLIENT,
           BRS_PHONE_NUMBER AS NUMERO,
           DECODE (CONT.status,1,'CONNECTE',6,'TERMINE') as Statut,
           DECODE(A.STATUS,1,'1',2,'1',3,'1',4,'1',5,'1','0') "ACTIVES",
           DECODE(A.STATUS,6,'1','0') "RESILIES",
           C.BPM_PPL_CODE as PLAN,
           DECODE(C.BPM_PPL_CODE,'MVPN-PP','KOZBUSI','MVPN-2','KOZBUSI','PREV-PP','GAA','B2B-PP','B2B','B2B-2','B2B')AS TYPE_ABONNEMENT
   FROM BIL.BCC_CUSTOMERS CUST,
        BCC_CONTRACTS CONT,
        BIL.BCC_SUBSCRIPTIONS A,
        BIL.BCC_SUB_PPL C,
        BIL.BCC_SUB_NUMBERS B,
        BIL.BCC_SUB_PRODUCTS D
  WHERE CONTRACT_NO =A.BCC_CONTRACT_NO
  AND A.BCC_CONTRACT_NO=C.BCC_CONTRACT_NO
  AND A.SUB_NO=C.BCC_SUB_NO
  AND B.BCC_CONTRACT_NO=C.BCC_CONTRACT_NO
  AND C.BCC_CONTRACT_NO=D.BCC_CONTRACT_NO
  AND C.BCC_SUB_NO = D.BCC_SUB_NO
  AND B.BCC_SUB_NO=C.BCC_SUB_NO
  AND C.STATUS=2 
--AND A.SUB_TYPE=2
  AND C.BPM_PPL_CODE  in ('MVPN-PP','MVPN-2','PREV-PP','B2B-PP','B2B-2')
--D.BPM_PRD_CODE IN ('ROAM'))
  and CONTRACT_TYPE=1
  AND B.STATUS IN (2,3) 
  AND cont.BCC_CUSTOMER_NO=cust.customer_no
  and USER_NAME not like '%Testing' and USER_NAME not like '%TEST%' and USER_NAME not like '%ESKADENIA%'
  and nvl(rtrim(cust.first_name||' '||father_name||' '||family_name) ,corporate_name) NOT LIKE 'COMIUM%'
  and nvl(rtrim(cust.first_name||' '||father_name||' '||family_name) ,corporate_name) NOT LIKE '%COMIUM'
  and nvl(rtrim(cust.first_name||' '||father_name||' '||family_name) ,corporate_name) NOT LIKE '%COMIUM%'
  and nvl(rtrim(cust.first_name||' '||father_name||' '||family_name) ,corporate_name) NOT LIKE '%Testing'
  and UPPER(nvl(rtrim(cust.first_name||' '||father_name||' '||family_name) ,corporate_name)) NOT LIKE '%TEST%'
  and nvl(rtrim(cust.first_name||' '||father_name||' '||family_name) ,corporate_name) NOT IN ('PREPAID','EPP Exp','ESKA')
 and nvl(rtrim(cust.first_name||' '||father_name||' '||family_name) ,corporate_name) NOT IN ('COMIUM Departement Marketing','COMIUM Direction des Ventes')
 and nvl(rtrim(cust.first_name_2||' '||father_name_2||' '||family_name_2) ,corporate_name_2 ) NOT LIKE 'COMIUM%'
 and nvl(rtrim(cust.first_name_2||' '||father_name_2||' '||family_name_2) ,corporate_name_2 ) NOT LIKE '%COMIUM'
 and nvl(rtrim(cust.first_name_2||' '||father_name_2||' '||family_name_2) ,corporate_name_2 ) NOT LIKE '%COMIUM%'
 and nvl(rtrim(cust.first_name_2||' '||father_name_2||' '||family_name_2) ,corporate_name_2 ) NOT LIKE '%Testing'
 and upper(nvl(rtrim(cust.first_name_2||' '||father_name_2||' '||family_name_2) ,corporate_name_2 )) NOT LIKE '%TEST%'
 and nvl(rtrim(cust.first_name_2||' '||father_name_2||' '||family_name_2) ,corporate_name_2 ) NOT IN ('PREPAID','EPP Exp','ESKA')
 and nvl(rtrim(cust.first_name_2||' '||father_name_2||' '||family_name_2) ,corporate_name_2 ) NOT IN ('COMIUM Departement Marketing','COMIUM Direction des Ventes')
 and nvl(rtrim(cust.first_name_2||' '||father_name_2||' '||family_name_2) ,corporate_name_2 ) NOT LIKE 'COMIUM VIP'
 ) A
group by  CONTRAT,STATUT,CLIENT,TYPE_ABONNEMENT
having sum(ACTIVES)||sum(RESILIES)<> 10 
order by TYPE_ABONNEMENT
je compte sur votre aide.
Merci d'avance