Procédure plsql paramétrée
Bonjour les amis ,
je sollicite votre aide !j'aimerais bien paramétrée une procédure plsql déjà testée mais sans paramétres maintenant je veux la paramétrer de sorte que le dblink (@bdprod) soit en paramétre comment dois je faire ?
voilà ma procédure plsql :
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
|
CREATE OR REPLACE PROCEDURE ProcSynSection
IS
u pls_integer := 0 ;
j pls_integer := 0 ;
i pls_integer := 0 ;
SeqNbrMax Number;
BEGIN
FOR r IN ( SELECT * FROM SECTIONTYPE )
LOOP
UPDATE SECTIONTYPE@BDPROD tgt
SET tgt.SECTIONTYPENAME = r.SECTIONTYPENAME,
tgt.SECTIONTYPEDESC = r.SECTIONTYPEDESC
WHERE tgt.SECTIONTYPEID_PK=r.SECTIONTYPEID_PK ;
END LOOP;
select count(*) into i from sectiontype where sectiontypeid_pk not in (select sectiontypeid_pk from sectiontype@bdprod );
select count(*) into j from sectiontype@bdprod where sectiontypeid_pk not in (select sectiontypeid_pk from sectiontype);
IF i > 0 THEN
select min (sectiontypeid_pk) into from sectiontype where sectiontypeid_pk not in (select sectiontypeid_pk from sectiontype@bdprod );
if SeqNbrMax > 0 THEN
Execute Immediate 'drop sequence BDPROD.SECTIONTYPE_SEQ ' ;
Execute Immediate 'create sequence BDPROD.SECTIONTYPE_SEQ start with ' || SeqNbrMax;
Execute Immediate 'alter sequence BDPROD.SECTIONTYPE_SEQ increment by 1 ' ;
insert into SECTIONTYPE@BDPROD
select * from sectiontype where sectiontypeid_pk not in (select sectiontypeid_pk from sectiontype@bdprod );
END IF;
End IF ;
if j >0 then
delete from sectiontype@bdprod where sectiontypeid_pk not in (select sectiontypeid_pk from sectiontype);
END IF ;
commit ;
END ; |
