' WHERE ';
16 V_OLDLIST := V_ROWLCR.GET_VALUES('OLD');
17 FOR I IN 1..V_OLDLIST.COUNT LOOP
18 IF V_OLDLIST(I) IS NOT NULL THEN
19 V_INSERT_STR := V_INSERT_STR || V_OLDLIST(I).COLUMN_NAME || ' = ';
20 MY_PRINT_ANY(V_OLDLIST(I).DATA, V_INSERT_STR, V_FLAG);
21 IF V_FLAG = 1 THEN
22 V_COUNT := INSTR(V_INSERT_STR, '=', -1);
23 V_INSERT_STR := SUBSTR(V_INSERT_STR, 1, V_COUNT -1) || 'IS' || SUBSTR(V_INSERT_STR, V_COU
NT 1);
24 END IF;
25 V_INSERT_STR := V_INSERT_STR || ' AND ';
26 END IF;
27 END LOOP;
28 INSERT INTO MY_RECORDS VALUES (SEQ_MY_REC.NEXTVAL, SUBSTR(V_INSERT_STR, 1, LENGTH(V_INSERT_S
TR) - 4));
29 ELSIF V_ROWLCR.GET_COMMAND_TYPE = 'INSERT' THEN
30 V_INSERT_STR := 'INSERT INTO ' || V_ROWLCR.GET_OBJECT_OWNER || '.' || V_ROWLCR.GET_OBJECT_N
AME || '( ';
31 V_NEWLIST := V_ROWLCR.GET_VALUES('NEW', 'N');
32 FOR I IN 1..V_NEWLIST.COUNT LOOP
33 IF V_NEWLIST(I) IS NOT NULL THEN
34 V_INSERT_STR := V_INSERT_STR || V_NEWLIST(I).COLUMN_NAME || ', ';
35 END IF;
36 END LOOP;
37 V_INSERT_STR := RTRIM(V_INSERT_STR, ', ') || ') VALUES (';
38 FOR I IN 1..V_NEWLIST.COUNT LOOP
39 IF V_NEWLIST(I) IS NOT NULL THEN
40 MY_PRINT_ANY(V_NEWLIST(I).DATA, V_INSERT_STR, V_FLAG);
41 V_INSERT_STR := V_INSERT_STR || ', ';
42 END IF;
43 END LOOP;
44 V_INSERT_STR := RTRIM(V_INSERT_STR, ', ') || ')';
45 INSERT INTO MY_RECORDS VALUES (SEQ_MY_REC.NEXTVAL, V_INSERT_STR);
46 ELSIF V_ROWLCR.GET_COMMAND_TYPE = 'UPDATE' THEN
47 V_INSERT_STR := 'UPDATE ' || V_ROWLCR.GET_OBJECT_OWNER || '.' || V_ROWLCR.GET_OBJECT_NAME |
| ' SET ';
48 V_NEWLIST := V_ROWLCR.GET_VALUES('NEW', 'N');
49 FOR I IN 1..V_NEWLIST.COUNT LOOP
50 IF V_NEWLIST(I) IS NOT NULL THEN
51 V_INSERT_STR := V_INSERT_STR || V_NEWLIST(I).COLUMN_NAME || ' = ';
52 MY_PRINT_ANY(V_NEWLIST(I).DATA, V_INSERT_STR, V_FLAG);
53 V_INSERT_STR := V_INSERT_STR || ', ';
54 END IF;
55 END LOOP;
56 V_INSERT_STR := RTRIM(V_INSERT_STR, ', ') || ' WHERE ';
57 V_OLDLIST := V_ROWLCR.GET_VALUES('OLD');
58 FOR I IN 1..V_OLDLIST.COUNT LOOP
59 IF V_OLDLIST(I) IS NOT NULL THEN
60 V_INSERT_STR := V_INSERT_STR || V_OLDLIST(I).COLUMN_NAME || ' = ' ;
61 MY_PRINT_ANY(V_OLDLIST(I).DATA, V_INSERT_STR, V_FLAG);
62 IF V_FLAG = 1 THEN
63 V_COUNT := INSTR(V_INSERT_STR, '=', -1);
64 V_INSERT_STR := SUBSTR(V_INSERT_STR, 1, V_COUNT -1) || 'IS' || SUBSTR(V_INSERT_STR, V_COU
NT 1);
65 END IF;
66 V_INSERT_STR := V_INSERT_STR || ' AND ';
67 END IF;
68 END LOOP;
69 INSERT INTO MY_RECORDS VALUES (SEQ_MY_REC.NEXTVAL, SUBSTR(V_INSERT_STR, 1, LENGTH(V_INSERT_S
TR) - 4));
70 END IF;
71 END IF;
72 END ;
73 /

过程已创建。

这个过程将LCR转化为可以执行的SQL语句，并将结果写到刚才的MY_RECORDS表中。

SQL> CREATE OR REPLACE PROCEDURE MY_PRINT_TRANSACTION(P_LTXNID IN VARCHAR2) IS
2 V_TXNID VARCHAR2(30);
3 V_SOURCE VARCHAR2(128);
4 V_MSGCNT NUMBER;
5 V_ERRNO NUMBER;
6 V_ERRMSG VARCHAR2(128);