- 论坛徽章:
- 0
|
table a (id ,type ,col1 ,col2 ,col3)
select * from table a
1 1 22 33 55
1 2 22 38 55
2 1 44 40 50
2 2 44 40 55
table aa (COL)
select * from aa;
COL1
COL2
COL3
table aaa (id, old_value, now_value, modify_item,TYPE)
现在要比较表a中id相等,type不等的列的不同。将比较结果放入 AAA表中。
如上面的数据,我想要得到的 AAA表中的数据为
SELECT * FROM AAA;
id old_value now_value modify_item TYPE
1 33 38 'COL2' 2
2 50 55 'COL3' 2
我写的存储过程如下:
- /* Formatted on 2004/04/22 16:04 (Formatter Plus v4.8.0) */
- CREATE OR REPLACE PROCEDURE test_change (starttime DATE)
- AS
- v_check NUMBER;
- v_name VARCHAR2 (10);
- CURSOR v_colname
- IS
- SELECT COL
- FROM AA;
- CURSOR v_curnew
- IS
- SELECT *
- FROM A
- WHERE TYPE = 2;
- BEGIN
- FOR v_new IN v_curnew
- LOOP
- --*************
- FOR v_col IN v_colname
- LOOP
- v_name := v_col.COL;
- SELECT v_col.COL
- INTO v_check
- FROM A
- WHERE TYPE = 1 AND ID = v_new.ID;
- IF v_new.v_name <>; v_check
- THEN
- INSERT INTO aaa
- (ne_id, old_value, now_value, modify_item,
- TYPE
- )
- VALUES (v_new.id, v_check, v_new.v_name, v_col.COL,
- v_new.TYPE
- );
- END IF;
- END LOOP;
- --*****************
- END LOOP;
- END;
- /
复制代码
编译的时候出现
PLS-00302: component 'V_NAME' must be declared
v_new.v_name 不能用,哪位大侠帮我改改呀? |
|