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
|
CREATE TABLE OUTPUT(
ID varchar2(10),
ACTION varchar2(20),
TYPE varchar2(5),
REQ_TYPE NUMBER,
REQ_OBJ NUMBER,
IMSI1 varchar2(50)
);
CREATE TABLE CONVERT(
ACTION VARCHAR2(20),
TYPE VARCHAR2(5),
SET_STMT VARCHAR2(4000)
);
insert into OUTPUT values ('x1','CRE_AUC','NT',NULL,NULL,'1234');
insert into convert values ('CRE_AUC','NT','REQ_TYPE=1,REQ_OBJ=1,IMSI1=''60503'' || cur.IMSI1');
CREATE OR REPLACE PROCEDURE CONVERTING_TEST
IS
CURSOR tasks IS SELECT * FROM OUTPUT;
set_expression varchar2(4000);
sql_stmt varchar2(4000);
BEGIN
FOR cur IN tasks
LOOP
SELECT SET_STMT
INTO set_expression
FROM CONVERT
WHERE ACTION = cur.ACTION
AND TYPE = cur.TYPE;
sql_stmt:= 'UPDATE OUTPUT SET ' || set_expression || ' WHERE ID = '''
|| cur.id
|| ''' AND ACTION = '''
|| cur.action
|| '''';
execute immediate sql_stmt;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END CONVERTING_TEST;
/ |
Partager