我现在想要复制一个表的一行记录,这个记录是由传参决定的,这个表的字段比较多有80个字段,我也写了一个过程如下:
create or replace package body copys is str varchar2(3000);
col varchar2(100); procedure copy_productart(keyfield in varchar2,newkeyvalue in varchar2,oldkeyvalue in varchar2,tables in varchar2,outkeyvalue in varchar2) is
begin str:='insert into '||tables||' select '||newkeyvalue||' as productid';
declare cursor takecol(tables varchar2) is
select column_name from user_tab_columns
where user_tab_columns.table_name=tables
order by column_id;
begin
open takecol(tables);
loop
fetch takecol into col;
exit when takecol%notfound;
dbms_output.put_line(col);
if col<>keyfield then
str:=str||','||col;
end if;
end loop;
str:=str||' from '||tables ;
str:=str||' where productid='||oldkeyvalue;
execute immediate str;
commit; close takecol;
end;
end;--begin
-- Initialization
--<Statement>;
end copys;在运行时总提示出错(出错在execute语句),怎么搞定,那位帮帮忙!
create or replace package body copys is str varchar2(3000);
col varchar2(100); procedure copy_productart(keyfield in varchar2,newkeyvalue in varchar2,oldkeyvalue in varchar2,tables in varchar2,outkeyvalue in varchar2) is
begin str:='insert into '||tables||' select '||newkeyvalue||' as productid';
declare cursor takecol(tables varchar2) is
select column_name from user_tab_columns
where user_tab_columns.table_name=tables
order by column_id;
begin
open takecol(tables);
loop
fetch takecol into col;
exit when takecol%notfound;
dbms_output.put_line(col);
if col<>keyfield then
str:=str||','||col;
end if;
end loop;
str:=str||' from '||tables ;
str:=str||' where productid='||oldkeyvalue;
execute immediate str;
commit; close takecol;
end;
end;--begin
-- Initialization
--<Statement>;
end copys;在运行时总提示出错(出错在execute语句),怎么搞定,那位帮帮忙!
execute immediate str into ...;
str1 varchar2(3000);
col varchar2(100); procedure copy_productart(keyfield in varchar2,newkeyvalue in varchar2,oldkeyvalue in varchar2,tables in varchar2,outkeyvalue in varchar2) is
begin str:='insert into '||tables||'(productid'
str1 :=' select '||newkeyvalue||' as productid';
declare cursor takecol(tables varchar2) is
select column_name from user_tab_columns
where user_tab_columns.table_name=tables
order by column_id;
begin
open takecol(tables);
loop
fetch takecol into col;
exit when takecol%notfound;
dbms_output.put_line(col);
if col<>keyfield then
str:=str||','||col;
str1:=str1||','||col;
end if;
end loop;
str:=str||') '
str1:=str1||' from '||tables ;
str1:=str1||' where productid='||oldkeyvalue;
execute immediate str||str1;
commit; close takecol;
end;
end;--begin
-- Initialization
--<Statement>;
end copys;
str1:='select '||newkeyvalue||' as '||keyfield;
declare cursor takecol(tables varchar2) is
select column_name from user_tab_columns
where user_tab_columns.table_name=tables
order by column_id;
begin
open takecol(tables);
loop
fetch takecol into col;
exit when takecol%notfound;
if col<>keyfield then
str2:=str2||','||col;
end if;
end loop;
str:=str||str2||')';
str1:=str1||str2||' from '||tables ;
str1:=str1||' where productid='||oldkeyvalue;
execute immediate str||str1;
commit;
--dbms_output.put(str);
close takecol;
end;
这是我修改后的代码,测试的时候还是提示错误
open takecol(tables);
loop
fetch takecol into col;
exit when takecol%notfound;
dbms_output.put_line(col);
if col<>keyfield then
str:=str||','||col;
end if;
end loop;
str:=str||' from '||tables ;
str:=str||' where productid='||oldkeyvalue;
execute immediate str;
我的PL/SQL不能用了,
感觉红色地方有问题,
如果col<>keyfield 成立,SELECT语句就多一个字段,
如果col<>keyfield 不成立,SELECT就少一个字段,不知道这个怎么查到目标表里面去
但是我上面配出来的str||str1,在values中没有前后的''号,所以出错了,但是,这个要把它变成字符,就需要转义掉,这个我不知都怎么转义了