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
|
CREATE OR REPLACE
PROCEDURE MATCH_CLASSIF(colclassif VARCHAR2,testnb NUMBER) AS
TYPE t_Cursor IS REF CURSOR;
C1 t_Cursor;
pNumber T_CDQ.gsi_party_number%TYPE;
pName T_CDQ.gsi_party_name%TYPE;
oldclassif VARCHAR2(255);
newclassif T_CLASSIF.classification%TYPE;
pCertif T_CDQ.party_certification%TYPE;
aNumber T_CDQ.account_number%TYPE;
aStatus T_CDQ.account_status%TYPE;
industryCat T_CDQ.industry_category%TYPE;
requete VARCHAR2(1000);
BEGIN
execute immediate 'drop table T_MATCH_CLASSIF';
execute immediate 'create table T_MATCH_CLASSIF(gsi_party_number VARCHAR2(100), gsi_party_name VARCHAR2(255), '||colclassif||' VARCHAR2(255), new_classification VARCHAR2(255), party_certification VARCHAR2(255), account_number VARCHAR2(100), account_status VARCHAR2(255), industry_category VARCHAR2(255))';
IF (testnb=1) THEN
requete:='OPEN C1 FOR
SELECT gsi_party_number, gsi_party_name, :1, classification, party_certification, account_number, account_status, industry_category
FROM T_CDQ,T_CLASSIF
WHERE gsi_party_number=registry_id
AND gsi_party_name=business_name;';
execute immediate requete USING colclassif;
ELSIF (testnb=2) THEN
requete:='OPEN C1 FOR
SELECT gsi_party_number, gsi_party_name, :1, classification, party_certification, account_number, account_status, industry_category
FROM T_CDQ,T_CLASSIF
WHERE gsi_party_number=registry_id
AND gsi_party_name<>business_name;';
execute immediate requete USING colclassif;
END IF;
LOOP
EXIT WHEN C1%NOTFOUND;
FETCH C1 INTO pNumber,pName,oldclassif,newclassif,pCertif,aNumber,aStatus,industryCat;
INSERT INTO T_MATCH_CLASSIF VALUES(pNumber,pName,oldclassif,newclassif,pCertif,aNumber,aStatus,industryCat);
COMMIT;
END LOOP;
CLOSE C1;
END; |
Partager