今天遇到存储过程的两个坑。
1,fetch into的变量名与列名相同,大大的有问题。
就是下面这对红配绿。你改成一样的试试。一定要定义一个和列名不一样的变量,加前缀v_之类的就挺好的。
・代码:
create or replace PROCEDURE DAILY_Update_PD_Flg AS
target_flg VARCHAR(1);
v_pd_cd VARCHAR(18);
CURSOR PD_CUR IS
SELECT pd_cd FROM T_PD WHERE PD_FLG = '1' FOR UPDATE;
BEGIN
OPEN PD_CUR;
LOOP
FETCH PD_CUR INTO v_pd_cd;
EXIT WHEN PD_CUR%NOTFOUND;
BEGIN
SELECT MC.target_flg INTO target_flg
FROM
T_PD_P MP, T_PD_P_SLV MC
WHERE
MP.pd_cd = RTRIM(v_pd_cd)
AND MP.od_type(+) = MC.od_type
;
EXCEPTION
WHEN NO_DATA_FOUND THEN
target_flg := '0';
WHEN TOO_MANY_ROWS THEN
target_flg := '2';
END;
dbms_output.put_line('***pd_cd = ' || v_pd_cd || '--target_flg--'||target_flg);
IF target_flg ='1'
THEN
UPDATE T_PD
SET PD_FLG = NULL,
RECV_YMD = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
WHERE CURRENT OF PD_CUR;
END IF;
END LOOP;
CLOSE PD_CUR;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('***E pd_cd = ' || v_pd_cd || '--ERROR--'||SQLERRM);
CLOSE PD_CUR;
END DAILY_Update_Flg;
2,NVL2,存储过程不支持。
这个问题发现是因为上述1的代码原来是用Pro*C写的,循环几十万条数据,每条数据去判断一下,然后在更新,这个网络传输量惊人。本来都是本地服务器还不觉得,后来改AWS了,立刻处理时间翻了好几倍。
所以想改成存储过程,调用一下即可,省去了网络传输了。
这好是好,但是也有别的问题,就是本问题。
因为,Pro*C里面还有其他代码,包括普通SQL语句,编译阶段就要统统先检查一遍,且按procedure标准来,里面用到了NVL2,嘿嘿,不好意思,编译通不过。听说Oracle公司还在努力,至于什么时候能解决就不知道了,兴许已经解决了,我们自己用的版本老也说不定。
编译参数要加这个:SQLCHECK=SEMANTICS USERID=U001/P001@DB_001
错误信息:PLS-00201: identifier 'NVL2' must be declared
NVL2(XX,aaa,bbb) 改成 CASE WHEN XX IS NOT NULL THEN aaa ELSE bbb END 就可以了