我手头有一个存储过程,里边有2个自定义参数。现在想用一张表,这个临时表里,有p_oldsitecode,p_newsitecode。将这个表所有数据批量的传到这个存储过程里,然后自动执行这个。不知道这个该怎么写了。用cursor 不知道怎么往里传值
第一个存储过程 RenameSiteCode111
-----
create or replace procedure RenameSiteCode111(p_oldsitecode varchar2
,p_newsitecode varchar2) is
cursor c_room(v_sitecode varchar2) is
select code,floorno from gisroom where sitecode= v_sitecode;
l_floorno gisroom.floorno%type;
l_roomcode gisroom.code%type;
l_error varchar2(200);
array_room_id type_number_array;
array_term_id type_number_array;
array_device_id type_number_array;
begin
--pkg_public.disableconstraints('GISSITE','CODE'); --backup id into collection
update gisroom
set sitecode=null
where sitecode=p_oldsitecode returning id bulk collect into array_room_id; update giscableterm
set sitecode= null
where sitecode= p_oldsitecode returning id bulk collect into array_term_id; update gisdevice
set sitecode= null
where sitecode= p_oldsitecode returning id bulk collect into array_device_id; --update gissite value
update gissite
set name=p_newsitecode
,code=p_newsitecode
where code=p_oldsitecode; --recover new site value
forall i in 1..array_room_id.count
update gisroom set sitecode=p_newsitecode where id=array_room_id(i);
forall i in 1..array_term_id.count
update giscableterm set sitecode=p_newsitecode where id=array_term_id(i);
forall i in 1..array_device_id.count
update gisdevice set sitecode=p_newsitecode where id=array_device_id(i); open c_room(p_newsitecode) ;
loop
fetch c_room into l_roomcode,l_floorno;
exit when c_room%notfound;
RenameRoomCode(p_oldsitecode||lpad(nvl(l_floorno,0),2,0)||'F',p_newsitecode||lpad(nvl(l_floorno,0),2,0)||'F');
end loop;
close c_room;
commit;
--pkg_public.enableconstraints('GISSITE','CODE');
exception when others then
begin
l_error:=sqlerrm;
rollback;
--pkg_public.enableconstraints('GISSITE','CODE');--add by qlm for bug after sqlerr constraints not be enabled at 20081112
--raise;
end ;
end RenameSiteCode111;
第一个存储过程 RenameSiteCode111
-----
create or replace procedure RenameSiteCode111(p_oldsitecode varchar2
,p_newsitecode varchar2) is
cursor c_room(v_sitecode varchar2) is
select code,floorno from gisroom where sitecode= v_sitecode;
l_floorno gisroom.floorno%type;
l_roomcode gisroom.code%type;
l_error varchar2(200);
array_room_id type_number_array;
array_term_id type_number_array;
array_device_id type_number_array;
begin
--pkg_public.disableconstraints('GISSITE','CODE'); --backup id into collection
update gisroom
set sitecode=null
where sitecode=p_oldsitecode returning id bulk collect into array_room_id; update giscableterm
set sitecode= null
where sitecode= p_oldsitecode returning id bulk collect into array_term_id; update gisdevice
set sitecode= null
where sitecode= p_oldsitecode returning id bulk collect into array_device_id; --update gissite value
update gissite
set name=p_newsitecode
,code=p_newsitecode
where code=p_oldsitecode; --recover new site value
forall i in 1..array_room_id.count
update gisroom set sitecode=p_newsitecode where id=array_room_id(i);
forall i in 1..array_term_id.count
update giscableterm set sitecode=p_newsitecode where id=array_term_id(i);
forall i in 1..array_device_id.count
update gisdevice set sitecode=p_newsitecode where id=array_device_id(i); open c_room(p_newsitecode) ;
loop
fetch c_room into l_roomcode,l_floorno;
exit when c_room%notfound;
RenameRoomCode(p_oldsitecode||lpad(nvl(l_floorno,0),2,0)||'F',p_newsitecode||lpad(nvl(l_floorno,0),2,0)||'F');
end loop;
close c_room;
commit;
--pkg_public.enableconstraints('GISSITE','CODE');
exception when others then
begin
l_error:=sqlerrm;
rollback;
--pkg_public.enableconstraints('GISSITE','CODE');--add by qlm for bug after sqlerr constraints not be enabled at 20081112
--raise;
end ;
end RenameSiteCode111;
RenameSiteCode111 这个存储过程是一个完整的,平时的时候,可以一条数据一条数据的往里填着去修改现在有一批数据要进行刷新,一条一条的填 会累死,所以,想利用这个RenameSiteCode111把它打成包,给这个里边附上新数据,让这个存储过程不断的去执行。。
已经弄了一张临时表,把要传的oldcode,newcode都整理好了,就差把这些数据往这个 RenameSiteCode111 里传了。
--声明一个游标,查询你做好的那个表
CURSOR c1 IS
SELECT empno, ename FROM emp ;
BEGIN
--游标循环
FOR ee IN c1 (mgr_no) LOOP
--循环调用你的存储过程,传入参数
RenameSiteCode111(ee.empno,ee.ename);
END LOOP;
COMMIT;
END;
还歹麻烦下,我用这个整个考过去,改了个表名,但是提示错误了。
Compilation errors for PACKAGE BODY NETGEO.PKG_DATA_TOOL001Error: PLS-00103: 出现符号 "CURSOR"在需要下列之一时:
( ; is with authid as
cluster order using external deterministic parallel_enable
pipelined
Line: 63
Text: CURSOR c1 ISprocedure find_staff
--声明一个游标,查询你做好的那个表
CURSOR c1 IS SELECT oldname, newname FROM temp_site
BEGIN
--游标循环
FOR ee IN c1 (mgr_no) LOOP
--循环调用你的存储过程,传入参数
RenameSiteCode(ee.oldname,ee.newname);
END LOOP;
COMMIT;
END;
还有mgr_no这个是啥啊,在网上没找到这个解释。
如果有问题可以自己调试下,基本语法和思路都有了,有些语法错误很容易解决:PROCEDURE find_staff
is -- 刚才少了上面的is,声明一个游标,查询你做好的那个表
CURSOR c1 IS
SELECT empno, ename FROM emp ;
BEGIN
--游标循环
FOR ee IN c1 LOOP
--循环调用你的存储过程,传入参数 ,mgr_no是参数,游标可以传入参数,你这里不需要。
RenameSiteCode111(ee.empno,ee.ename);
END LOOP;
COMMIT;
END;