拼接动态SQL的时候,注意在字符类型的变量两边需要跟单引号的,而处于单引号中的单引号则以2个单引号表示,如:v_value2||'''',这里2个单引号内的2个单引号表示在v_value2后面跟1个单引号。没有跟单引号的就表示是数字类型。如果数据类型不符,可能会报ORA-00904的错误。
下面是我自己测试成功的例子:
create table test
(acc1 char(19),
acc2 char(19),
acc_name1 char(40),
paper_no char(18),
ptkey number
)
partition by range (ptkey)
(partition p1 values less than (20),
partition p2 values less than (40),
partition p3 values less than (60),
partition p4 values less than (80),
partition p5 values less than (100)
);
insert into test select acc1,acc2,acc_name1,paper_no,rownum ptkey from ppjjss where rownum<100;
declare
v_num1 number := 100200300400;
v_tab varchar(30) := 'TEST';
v_sql1 varchar2(4000);
v_sql2 varchar2(4000);
v_sql3 varchar2(4000);
v_sql4 varchar2(4000);
v_sql5 varchar2(4000);
v_value1 char(19) := '0000000000000000000';
v_value2 char(40) := '路人甲';
v_value3 char(18) := '111111111111111111';
begin
for v_pt in (select partition_name from user_tab_partitions where table_name=v_tab) loop
v_sql1 := 'update '||v_tab||' partition ('||v_pt.partition_name||') set acc1=acc1+'||v_num1;
v_sql2 := 'update '||v_tab||' partition ('||v_pt.partition_name||') set acc1='''||v_value1||''' where acc1='||v_num1;
v_sql3 := 'update '||v_tab||' partition ('||v_pt.partition_name||') set acc2=null';
v_sql4 := 'update '||v_tab||' partition ('||v_pt.partition_name||') set acc_name1='''||v_value2||'''';
v_sql5 := 'update '||v_tab||' partition ('||v_pt.partition_name||') set paper_no='''||v_value3||'''';
execute immediate v_sql1;
execute immediate v_sql2;
execute immediate v_sql3;
execute immediate v_sql4;
execute immediate v_sql5;
commit;
end loop;
end;
/
下面是我自己测试成功的例子:
create table test
(acc1 char(19),
acc2 char(19),
acc_name1 char(40),
paper_no char(18),
ptkey number
)
partition by range (ptkey)
(partition p1 values less than (20),
partition p2 values less than (40),
partition p3 values less than (60),
partition p4 values less than (80),
partition p5 values less than (100)
);
insert into test select acc1,acc2,acc_name1,paper_no,rownum ptkey from ppjjss where rownum<100;
declare
v_num1 number := 100200300400;
v_tab varchar(30) := 'TEST';
v_sql1 varchar2(4000);
v_sql2 varchar2(4000);
v_sql3 varchar2(4000);
v_sql4 varchar2(4000);
v_sql5 varchar2(4000);
v_value1 char(19) := '0000000000000000000';
v_value2 char(40) := '路人甲';
v_value3 char(18) := '111111111111111111';
begin
for v_pt in (select partition_name from user_tab_partitions where table_name=v_tab) loop
v_sql1 := 'update '||v_tab||' partition ('||v_pt.partition_name||') set acc1=acc1+'||v_num1;
v_sql2 := 'update '||v_tab||' partition ('||v_pt.partition_name||') set acc1='''||v_value1||''' where acc1='||v_num1;
v_sql3 := 'update '||v_tab||' partition ('||v_pt.partition_name||') set acc2=null';
v_sql4 := 'update '||v_tab||' partition ('||v_pt.partition_name||') set acc_name1='''||v_value2||'''';
v_sql5 := 'update '||v_tab||' partition ('||v_pt.partition_name||') set paper_no='''||v_value3||'''';
execute immediate v_sql1;
execute immediate v_sql2;
execute immediate v_sql3;
execute immediate v_sql4;
execute immediate v_sql5;
commit;
end loop;
end;
/
上面代码中:
v_sql4 := 'update '||v_tab||' partition ('||v_pt.partition_name||') set acc_name1='''||v_value2||'''';
这一段我一开始是这样写的:
v_sql4 := 'update '||v_tab||' partition ('||v_pt.partition_name||') set acc_name1='||v_value2;
运行就出错了:ORA-00904
然后我把v_value2 char(40) := '路人甲';改为v_value2 char(40) := '888';可以运行成功,但这实际上存成数据类型的隐式转化。