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