关于动态SQL中的NULL
1 declare 2 v_sql varchar2(4000); 3 v_c1 number; 4 v_c2 number; 5 begin 6 v_c2 := 999; 7 v_sql := 'begin '; 8 v_sql := v_sql||'update te1 set c1='||v_c1||', c2='||v_c2||' where c1=1 and c2=1;'; 9 v_sql := v_sql||' if sql%notfound then '; 10 v_sql := v_sql||'begin '; 11 v_sql := v_sql||'insert into te1(c1,c2) values('||v_c1||','||v_c2||');'; 12 v_sql := v_sql||'end;'; 13 v_sql := v_sql||'end if;'; 14 v_sql := v_sql||'end;'; 15 execute immediate v_sql; 16 end; 17 /
以上代码因v_c1为null,会抛出一个ORA-00936（失效的表达式）的错误，做了几次试验，发现问题的所在，将代码改一下，把最终的动态SQL显示出来：
1 set serveroutput on 2 declare 3 v_sql varchar2(4000); 4 v_c1 number; 5 v_c2 number; 6 begin 7 v_c2 := 999; 8 v_sql := 'begin '; 9 v_sql := v_sql||'update te1 set c1='||v_c1||', c2='||v_c2||' where c1=1 and c2=1;'; 10 v_sql := v_sql||' if sql%notfound then '; 11 v_sql := v_sql||'begin '; 12 v_sql := v_sql||'insert into te1(c1,c2) values('||v_c1||','||v_c2||');'; 13 v_sql := v_sql||'end;'; 14 v_sql := v_sql||'end if;'; 15 v_sql := v_sql||'end;'; 16 --execute immediate v_sql; 17 dbms_output.put_line(v_sql); 18 end; 19 /
执行后输出
1 begin 2 update te1 3 set c1 =, c2 = 999 4 where c1 = 1 5 and c2 = 1; 6 if sql%notfound then 7 begin 8 insert into te1 (c1, c2) values (, 999); 9 end; 10 end if; 11 end;
从输出的结果中可以看出，update中的c1=后面没有值，insert into中的values后也缺少值，由此可以看出，当变量值为null时，则传入为空，而不是实际的null，因此整个语句不完整，会报ORA-00936的错误。
修正：为变量加一个nvl函数
1 declare 2 v_sql varchar2(4000); 3 v_c1 number; 4 v_c2 number; 5 begin 6 v_c2 := 999; 7 v_sql := 'begin '; 8 v_sql := v_sql||'update te1 set c1='||nvl(v_c1,0)||', c2='||v_c2||' where c1=1 and c2=1;'; 9 v_sql := v_sql||' if sql%notfound then '; 10 v_sql := v_sql||'begin '; 11 v_sql := v_sql||'insert into te1(c1,c2) values('||nvl(v_c1,0)||','||v_c2||');'; 12 v_sql := v_sql||'end;'; 13 v_sql := v_sql||'end if;'; 14 v_sql := v_sql||'end;'; 15 execute immediate v_sql; 16 end; 17 /
