create or replace procedure proc_update
is
cursor mycursor is select a.packname,a.itemname from iteminfotest a ,bas_iteminfo b where a.itemname=b.itemname;
theRow mycursor%rowType;
begin
open mycursor;
loop
fetch mycursor into theRow;
exit when mycursor%notfound;
update bas_iteminfo d set compcode = (select c.compcode from sys_company c where c.compname like '% ' | |theRow.packname| | '% ') where d.itemname=theRow.itemname;
end loop;
close mycursor;
commit;
end;
is
cursor mycursor is select a.packname,a.itemname from iteminfotest a ,bas_iteminfo b where a.itemname=b.itemname;
theRow mycursor%rowType;
begin
open mycursor;
loop
fetch mycursor into theRow;
exit when mycursor%notfound;
update bas_iteminfo d set compcode = (select c.compcode from sys_company c where c.compname like '% ' | |theRow.packname| | '% ') where d.itemname=theRow.itemname;
end loop;
close mycursor;
commit;
end;
另外一个SQL就可以搞定。update bas_iteminfo d
set compcode = (select c.compcode from iteminfotest a ,bas_iteminfo b , sys_company c where a.itemname=b.itemname and c.compname like '% ' | |a.packname| | '% ' and d.itemname = a.itemname)
where exsits ( select 'x' from iteminfotest where d.itemname = iteminfotest.itemname)
CURSOR MYCURSOR IS
SELECT A.PACKNAME, A.ITEMNAME
FROM ITEMINFOTEST A, BAS_ITEMINFO B
WHERE A.ITEMNAME = B.ITEMNAME;
TYPE THEROW IS TABLE OF MYCURSOR%ROWTYPE INDEX BY BINARY_INTEGER;
C_THEROW THEROW;
BEGIN
OPEN MYCURSOR;
FETCH MYCURSOR BULK COLLECT
INTO C_THEROW;
FOR I IN 1 .. C_THEROW.COUNT LOOP
BEGIN
UPDATE BAS_ITEMINFO D
SET COMPCODE = (SELECT C.COMPCODE
FROM SYS_COMPANY C
WHERE C.COMPNAME LIKE
'% ' || THEROW.PACKNAME || '% ')
WHERE D.ITEMNAME = C_THEROW(I).ITEMNAME;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
/*建议记录错误日志,以便出错查找;下面用null代替*/
NULL;
END;
END LOOP;
CLOSE MYCURSOR;
END;
UPDATE BAS_ITEMINFO D
SET COMPCODE = (SELECT C.COMPCODE
FROM SYS_COMPANY C
WHERE C.COMPNAME LIKE '% ' || THEROW.PACKNAME || '% ')
WHERE D.ITEMNAME = THEROW.ITEMNAME
AND EXISTS
(SELECT 1
FROM SYS_COMPANY C
WHERE C.COMPNAME LIKE '% ' || THEROW.PACKNAME || '% ');