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;
解决方案 »
- 如何导出oracel数据脚本!!!!。。。。。。。
- 基于时间点的恢复代码语法出错,错在那
- 触发器sql脚本在DOS下执行出错
- oracle10g中上传文件报错
- 递归查询的问题,谢谢执教
- 在此命令ARCHIVE LOG LIST ,提示权限不够,我在赋权限时,又提示“无法赋权给全局角色”
- 我用下面的SQL语句,系统提示错误,我怎样才能实现我的目的,谢谢!
- 一个关于oracle和delphi的小问题,有分
- 如何查询有n列的table里前n-1个字段的值
- 要在时间字段中用LIKE来查2003年的数据
- oracle 数据库读取记录为空
- ORA-00932:inconsistent datatypes: expected NUMBER got LONG
另外一个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 || '% ');