17,086
社区成员
发帖
与我相关
我的任务
分享
create or replace type array_num is table of number(19)
create or replace procedure directory_path_change(from_catalogId integer,to_catalogId integer,except_array array_num) is
----把采购分类下的采购品平移到另一分类下, except_array 除外的采购品
tmp_ID NUMBER(19); --需要转移的采购品ID
catalog_treepath VARCHAR2(768 CHAR); --目标分类的路径
i number(10); --数组下标 从1开始
isUpdate number(1); --是否执行更新 0:不执行 ;1:执行
cursor directory_cursor is
select id from corp_directorys d where d.catalog_id = from_catalogId;
begin
select treepath into catalog_treepath from corp_catalogs where id = to_catalogId;
savepoint p1; -- 设置回滚点
open directory_cursor;
loop
fetch directory_cursor into
tmp_ID;
exit when directory_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('=============ID:');
DBMS_OUTPUT.PUT_LINE(tmp_ID);
i := 1;
isUpdate := 0;
for i in 1..except_array.count Loop
if except_array(i)=tmp_ID then
isUpdate := 1;
end if;
end loop;
if isUpdate=1 then
update corp_directorys d set d.catalog_id = to_catalogId , d.treepath = (catalog_treepath||d.id||'#') where d.id =tmp_ID;
end if;
commit;
end loop;
close directory_cursor;
--发生异常时,数据回滚
exception
when others then
dbms_output.put_line(sqlerrm);
rollback to savepoint p1;
end directory_path_change;
declare
except_array array_num := array_num( 90818041);
from_catalogid number :=90818040;
to_catalogid number :=91469311;
begin
-- Call the procedure
directory_path_change(from_catalogid ,to_catalogid ,except_array);
end;