一字段值为null,怎么和另一值null值比较.下面的这个存储过程错在哪?
create or replace procedure cm_p_chxx IS
CURSOR cursor_jzxx IS SELECT * FROM cm_jzxxb;
record_result cm_jzxxb%ROWTYPE;
--TYPE t_cursor_chxx IS REF CURSOR;
--cursor_chxx t_cursor_chxx;
v_bhfl number(10,2);
v_chdm varchar2(6);
v_count INTEGER;
BEGIN
OPEN cursor_jzxx;
LOOP
FETCH cursor_jzxx INTO record_result;
EXIT WHEN cursor_jzxx%NOTFOUND;
-- 如果存在有相同仓号的记录则更新,否则插入一条新记录
SELECT COUNT(*) INTO v_count
FROM cm_chxxb
WHERE bdxh=record_result.bdxh
AND tlx=record_result.tlx
AND baduan=record_result.baduan
AND bakuai=record_result.bakuai
AND zyc=record_result.zyc;
-- 如果有一条
IF v_count=1 THEN
SELECT chdm,bhfl INTO v_chdm,v_bhfl
FROM cm_chxxb
WHERE bdxh=record_result.bdxh
AND tlx=record_result.tlx
AND baduan=record_result.baduan
AND bakuai=record_result.bakuai AND zyc=record_result.zyc;
--更新数据
UPDATE cm_chxxb SET
--开仓时间
ksSJ=record_result.kcsj ,
--收仓时间
jssj=record_result.scsj,
--设计方量
sjfl=record_result.sjfl,
--起始高层
qsgc=record_result.qsgc,
--结束高层
jsgc=record_result.jsgc1,
--浇筑方量
bhfl=v_bhfl+record_result.bhfl
WHERE chdm=v_chdm;
COMMIT;
ELSIF v_count=0 THEN
--插入一条新的仓号记录
INSERT INTO cm_chxxb(qsgc,sjfl,jsgc,baduan,bakuai,bdxh,tlx,zyc,cengh,cfq,kssj,jssj,bhfl)
VALUES
(record_result.qsgc,record_result.sjfl,record_result.jsgc1,record_result.baduan,
record_result.bakuai,record_result.bdxh,record_result.tlx,record_result.zyc,
record_result.cengh,record_result.cfq,record_result.kcsj,record_result.scsj,
record_result.bhfl);
COMMIT;
ELSE
dbms_output.put_line('错误');
END IF;
END LOOP;
CLOSE Cursor_Jzxx;
end cm_p_chxx;
如果不加上zyc(where里最后一个条件)执行正确.加上zyc后,zyc只要为null(没有值),则选择不出记录.
问题点数：50、回复次数：4Top
1 楼lialin（阿林）回复于 2003-08-03 18:17:26 得分 0
空记录是选择不出来的，如果要选择的话，你可以再加一个OR条件Top
2 楼beckhambobo（beckham）回复于 2003-08-03 18:31:40 得分 50
create or replace procedure cm_p_chxx IS
CURSOR cursor_jzxx IS SELECT * FROM cm_jzxxb;
--record_result cm_jzxxb%ROWTYPE;
--TYPE t_cursor_chxx IS REF CURSOR;
--cursor_chxx t_cursor_chxx;
v_bhfl number(10,2);
v_chdm varchar2(6);
v_count INTEGER;
BEGIN
for v_jzxx in cursor_jzxx loop
-- 如果存在有相同仓号的记录则更新,否则插入一条新记录
if v_jzxx.zyc is null then
SELECT COUNT(*) INTO v_count
FROM cm_chxxb
WHERE bdxh=v_jzxx.bdxh
AND tlx=v_jzxx.tlx
AND baduan=v_jzxx.baduan
AND bakuai=v_jzxx.bakuai
AND zyc is null;
else
SELECT COUNT(*) INTO v_count
FROM cm_chxxb
WHERE bdxh=v_jzxx.bdxh
AND tlx=v_jzxx.tlx
AND baduan=v_jzxx.baduan
AND bakuai=v_jzxx.bakuai
AND zyc=v_jzxx.zyc;
end if;
-- 如果有一条
IF v_count=1 THEN
if v_jzxx.zyc is null then
SELECT chdm,bhfl INTO v_chdm,v_bhfl
FROM cm_chxxb
WHERE bdxh=v_jzxx.bdxh
AND tlx=v_jzxx.tlx
AND baduan=v_jzxx.baduan
AND bakuai=v_jzxx.bakuai AND zyc is null;
else
SELECT chdm,bhfl INTO v_chdm,v_bhfl
FROM cm_chxxb
WHERE bdxh=v_jzxx.bdxh
AND tlx=v_jzxx.tlx
AND baduan=v_jzxx.baduan
AND bakuai=v_jzxx.bakuai AND zyc=v_jzxx.zyc;
end if;
--更新数据
UPDATE cm_chxxb SET
--开仓时间
ksSJ=record_result.kcsj ,
--收仓时间
jssj=record_result.scsj,
--设计方量
sjfl=record_result.sjfl,
--起始高层
qsgc=record_result.qsgc,
--结束高层
jsgc=record_result.jsgc1,
--浇筑方量
bhfl=v_bhfl+record_result.bhfl
WHERE chdm=v_chdm;
COMMIT;
ELSIF v_count=0 THEN
--插入一条新的仓号记录
INSERT INTO cm_chxxb(qsgc,sjfl,jsgc,baduan,bakuai,bdxh,tlx,zyc,cengh,cfq,kssj,jssj,bhfl)
VALUES
(record_result.qsgc,record_result.sjfl,record_result.jsgc1,record_result.baduan,
record_result.bakuai,record_result.bdxh,record_result.tlx,record_result.zyc,
record_result.cengh,record_result.cfq,record_result.kcsj,record_result.scsj,
record_result.bhfl);
COMMIT;
ELSE
dbms_output.put_line('错误');
END IF;
END LOOP;
end cm_p_chxx;
/
以上程序，楼主有空作一个优化吧Top
3 楼zhangshunshi（宇轩）回复于 2003-08-04 13:13:37 得分 0
注意：null不等于任何值，只能作判断is nullTop
4 楼qiyousyc（沈阳棋友）回复于 2003-08-04 15:22:08 得分 0
在没有关闭游标前，COMMIT会自动关闭游标的。Top
