create or replace procedure zdjc(
dbname varchar2,
tblname varchar2,
tblzj varchar2,
zdname varchar2,
)
is
cursor zdjccursor is select tblzj,zdname from dbname.tblname 其中这句的dbname.tblname ,tblzj,zdname 都是参数,如何做???
begin
FOR cur_result in zdjccursor LOOP
end loop;
end
dbname varchar2,
tblname varchar2,
tblzj varchar2,
zdname varchar2,
)
is
cursor zdjccursor is select tblzj,zdname from dbname.tblname 其中这句的dbname.tblname ,tblzj,zdname 都是参数,如何做???
begin
FOR cur_result in zdjccursor LOOP
end loop;
end
dbname in varchar2,
tblname in varchar2,
tblzj in varchar2,
zdname in varchar2,
)
is v_sql varchar2(2000) := null;
type t_cur is ref cursor;
zdjccursor t_cur;
begin
v_sql := 'select '||tblzj||','||zdname||' from '||dbname||'.'||tblname;
open zdjccursor for v_sql
LOOP
end loop;
end
要是不能改就用动态sql
create or replace procedure zdjc(
dbname varchar2,
tblname varchar2,
tblzj varchar2,
zdname varchar2,
resu out varchar2)
is zj varchar2(100);
zdnr varchar2(1000);
bzdbname varchar2(100);
bztblname varchar2(100);
bzzdname varchar2(100);
bzzj varchar2(100);
sqls varchar2(200);
v_sql varchar2(2000) := null;
type t_cur is ref cursor;
zdjccursor t_cur;
begin
select dbname, bz_tblname,bz_zdname into bzdbname,bztblname,bzzdname from lagaoazd.user_zyi_zdgx where xg_dbname=dbname and xg_tblname=tblname and xg_zdname=zdname;
if bzzdname <>'' then
resu:=1;
select bz_tblzj into bzzj from lagaoazd.user_zyi where bz_dbname=bzdbname and bz_tablename=bztblname and bz_issh=1 and bz_status=0;
if bzzj <>'' then
v_sql := 'select '||tblzj||','||zdname||' from '||dbname||'.'||tblname;
open zdjccursor for v_sql LOOP begin
zj:=cur_result.tblzj;
zjnr:=cur_result.zdname;
insert into lagaoazd.bfb (dbname,tablename,zdname,zjnr,zdnr )values (dbname,tblname,zdname,zj,zjnr);
update '||dbname||'.'||tblname|| 'set'|| zdname||' ='''||bzzdname||''' where '||tblzj||'='||bzzj;
end
end loop;
end if;
end if;
end;
dbname in varchar2,
tblname in varchar2,
tblzj in varchar2,
zdname in varchar2,
resu out varchar2)
is bzdbname varchar2(100) := null;
bztblname varchar2(100) := null;
bzzdname varchar2(100) := null;
bzzj varchar2(100) := null;
sqls varchar2(200) := null;
v_sql varchar2(2000) := null;
cursor cur_zdjc(a in varchar2,b in varchar2,c in varchar2,d in varchar2)
is
select a,b from c.d; begin
select dbname
, bz_tblname
, bz_zdname
into bzdbname
, bztblname
, bzzdname
from lagaoazd.user_zyi_zdgx
where xg_dbname = dbname
and xg_tblname = tblname
and xg_zdname = zdname;
if bzzdname is not null then
resu := 1;
select bz_tblzj
into bzzj
from lagaoazd.user_zyi
where bz_dbname = bzdbname
and bz_tablename = bztblname
and bz_issh = 1
and bz_status = 0;
if bzzj is not null then
for rec in cur_zdjc(tblzj,zdname,dbname,tblname)
loop insert into lagaoazd.bfb (dbname,tablename,zdname,zjnr,zdnr )
values (dbname,tblname,zdname,rec.tblzj,rec.zdname);
v_sql := 'update '||dbname||'.'||tblname|| 'set'|| zdname||' ='''||bzzdname||''' where '||tblzj||'='||bzzj;
execute immediate v_sql;
end loop;
end if; end if;exception
when others then
dbms_output.put_line(substr(sqlerrm,1,254));
raise;
end zdjc;
try
1.open zdjccursor for v_sql LOOP
应该是open zdjccursor for v_sql;LOOP...END LOOP;
2.没有关闭CUROSOR如下是正确的:create or replace procedure zdjc(
dbname varchar2,
tblname varchar2,
tblzj varchar2,
zdname varchar2,
resu out varchar2)
is zj varchar2(100);
zdnr varchar2(1000);
bzdbname varchar2(100);
bztblname varchar2(100);
bzzdname varchar2(100);
bzzj varchar2(100);
sqls varchar2(200);
v_sql varchar2(2000) := null;
type t_cur is ref cursor;
zdjccursor t_cur;
--Add a new parameter
temp_sql varchar(400);
begin
select dbname, bz_tblname,bz_zdname into bzdbname,bztblname,bzzdname from lagaoazd.user_zyi_zdgx where xg_dbname=dbname and xg_tblname=tblname and xg_zdname=zdname;
if bzzdname is not null then
resu:=1;
select bz_tblzj into bzzj from lagaoazd.user_zyi where bz_dbname=bzdbname and bz_tablename=bztblname and bz_issh=1 and bz_status=0;
if bzzj is not null then
v_sql := 'select '||tblzj||','||zdname||' from '||dbname||'.'||tblname;
open zdjccursor for v_sql;
LOOP
--begin
zj:=cur_result.tblzj;
zjnr:=cur_result.zdname;
insert into lagaoazd.bfb (dbname,tablename,zdname,zjnr,zdnr )values (dbname,tblname,zdname,zj,zjnr);
temp_sql := 'update'|| dbname ||'.'||tblname ||'set'|| zdname||'='||bzzdname || ' where'|| tblzj||'='||bzzj;
--update '||dbname||'.'||tblname|| 'set'|| zdname||' ='''||bzzdname||''' where '||tblzj||'='||bzzj;
--end
execute immediate temp_sql;
commit;
end loop;
--add a new line to close the CURSOR
close zdjccursor;
end if;
end if;
end;
/