存储过程代码如下,高手赐教,我只有5分了!!!
CREATE OR REPLACE PROCEDURE sptstr (tablename varchar2,spitstr varchar2) as
var_rownum number(10):=0;--id
var_gtmp varchar2(2000);--用于保存行中的car_wheelbase值
var_stmp varchar2(2000);--用于保存拆分的结果
var_sqlstr varchar2(1000);--用什么样的分割符
cursor my_cur is select * from car_Wheelbase_dd_temp;--给要拆分的表定义游标
var_row car_Wheelbase_dd_temp%rowtype;--定义属于游标的行
begin
--累计id
var_rownum:=var_rownum + 1;
---打开游标开始循环
open my_cur ;
loop
fetch my_cur into var_row;
exit when my_cur%notfound;
--把每行的数据存入var_gtmp
select var_row.car_wheelbase into var_gtmp from dual;
--如果没有分隔就直接插入原数据
if instr(var_row.car_wheelbase,spitstr) = 0 then
var_sqlstr:='insert into TEST_CARWHELLBASEDD (ID,car_wheelbase,Foreignid) values ('||to_char(var_rownum)||','''||var_row.car_wheelbase||''','||to_char(var_row.foreignid)||')';
execute immediate var_sqlstr;
var_rownum:=var_rownum + 1;
else
--有分隔符的情况
while nvl(length(var_gtmp),0)>1
loop
--最后已经没有了分隔符,要判断一下,防止错误
if instr(var_gtmp,spitstr) = 0 then
var_sqlstr:='insert into TEST_CARWHELLBASEDD (ID,car_wheelbase,Foreignid) values ('||to_char(var_rownum)||','''||var_gtmp||''','||to_char(var_row.foreignid)||')';
execute immediate var_sqlstr;
var_gtmp := '';
var_rownum:=var_rownum + 1;
else
--如果还含有分隔符就拆分
select substr(var_gtmp,1,instr(var_gtmp,spitstr)-1) into var_stmp from dual;
var_sqlstr:='insert into TEST_CARWHELLBASEDD (ID,car_wheelbase,Foreignid) values ('||to_char(var_rownum)||','''||var_stmp||''','||to_char(var_row.foreignid)||')';
execute immediate var_sqlstr;
select substr(var_gtmp,instr(var_gtmp,spitstr)+1) into var_gtmp from dual;
var_rownum:=var_rownum + 1;
end if;
end loop;
commit;
end if;
end loop;
close my_cur;
end sptstr;
CREATE OR REPLACE PROCEDURE sptstr (tablename varchar2,spitstr varchar2) as
var_rownum number(10):=0;--id
var_gtmp varchar2(2000);--用于保存行中的car_wheelbase值
var_stmp varchar2(2000);--用于保存拆分的结果
var_sqlstr varchar2(1000);--用什么样的分割符
cursor my_cur is select * from car_Wheelbase_dd_temp;--给要拆分的表定义游标
var_row car_Wheelbase_dd_temp%rowtype;--定义属于游标的行
begin
--累计id
var_rownum:=var_rownum + 1;
---打开游标开始循环
open my_cur ;
loop
fetch my_cur into var_row;
exit when my_cur%notfound;
--把每行的数据存入var_gtmp
select var_row.car_wheelbase into var_gtmp from dual;
--如果没有分隔就直接插入原数据
if instr(var_row.car_wheelbase,spitstr) = 0 then
var_sqlstr:='insert into TEST_CARWHELLBASEDD (ID,car_wheelbase,Foreignid) values ('||to_char(var_rownum)||','''||var_row.car_wheelbase||''','||to_char(var_row.foreignid)||')';
execute immediate var_sqlstr;
var_rownum:=var_rownum + 1;
else
--有分隔符的情况
while nvl(length(var_gtmp),0)>1
loop
--最后已经没有了分隔符,要判断一下,防止错误
if instr(var_gtmp,spitstr) = 0 then
var_sqlstr:='insert into TEST_CARWHELLBASEDD (ID,car_wheelbase,Foreignid) values ('||to_char(var_rownum)||','''||var_gtmp||''','||to_char(var_row.foreignid)||')';
execute immediate var_sqlstr;
var_gtmp := '';
var_rownum:=var_rownum + 1;
else
--如果还含有分隔符就拆分
select substr(var_gtmp,1,instr(var_gtmp,spitstr)-1) into var_stmp from dual;
var_sqlstr:='insert into TEST_CARWHELLBASEDD (ID,car_wheelbase,Foreignid) values ('||to_char(var_rownum)||','''||var_stmp||''','||to_char(var_row.foreignid)||')';
execute immediate var_sqlstr;
select substr(var_gtmp,instr(var_gtmp,spitstr)+1) into var_gtmp from dual;
var_rownum:=var_rownum + 1;
end if;
end loop;
commit;
end if;
end loop;
close my_cur;
end sptstr;
CREATE OR REPLACE PROCEDURE sptstr(tablename VARCHAR2, spitstr VARCHAR2) AS
...
my_cur SYS_REFCURSOR; --动态游标
var_row TEST_CARWHELLBASEDD%ROWTYPE; --定义属于游标的行
...
BEGIN
CURSOR my_cur IS 'SELECT * FROM ' || tablename;
....
end;
CREATE OR REPLACE PROCEDURE sptstr(tablename VARCHAR2, spitstr VARCHAR2) AS
...
my_cur SYS_REFCURSOR; --动态游标
var_row TEST_CARWHELLBASEDD%ROWTYPE; --定义属于游标的行
...
BEGIN
OPEN my_cur FOR 'SELECT * FROM ' || tablename;
...
END;