create or replace procedure fsa_update_sp
as
v_TABLE_NAME varchar2(30);
CURSOR table_cursor is
select TABLE_NAME from FSA.TABCLEZD order by XH;
begin
open table_cursor ;
loop
fetch table_cursor into v_TABLE_NAME ;
exit when table_cursor%notfound ;
update v_TABLE_NAME a set (a.item_code,a.ITEM_NAM,a.ITEM_ABV)
=(select 新编码,新名称,新名称
from FSA.master b where a.item_code=b.旧编码 )
where a.item_code=b.旧编码 ;
end loop ;
close table_cursor ;
endCompilation errors for PROCEDURE FSA_LX.FSA_UPDATE_SPError: PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
; <an identifier>
<a double-quoted delimited-identifier> delete exists prior
<a single-quoted SQL string>
符号 ";" 被替换为 "end-of-file" 后继续。
Line: 18
as
v_TABLE_NAME varchar2(30);
CURSOR table_cursor is
select TABLE_NAME from FSA.TABCLEZD order by XH;
begin
open table_cursor ;
loop
fetch table_cursor into v_TABLE_NAME ;
exit when table_cursor%notfound ;
update v_TABLE_NAME a set (a.item_code,a.ITEM_NAM,a.ITEM_ABV)
=(select 新编码,新名称,新名称
from FSA.master b where a.item_code=b.旧编码 )
where a.item_code=b.旧编码 ;
end loop ;
close table_cursor ;
endCompilation errors for PROCEDURE FSA_LX.FSA_UPDATE_SPError: PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
; <an identifier>
<a double-quoted delimited-identifier> delete exists prior
<a single-quoted SQL string>
符号 ";" 被替换为 "end-of-file" 后继续。
Line: 18
select TABLE_NAME from FSA.TABCLEZD order by XH;
定义游标了,但是游标里面各个字段的类型没有定义;update v_TABLE_NAME a set (a.item_code,a.ITEM_NAM,a.ITEM_ABV)
=(select 新编码,新名称,新名称
from FSA.master b where a.item_code=b.旧编码 )
where a.item_code=b.旧编码 ;
更新数据表,能保证b表中的数据在a表中一定有记录吗?如果不能保证需要加判断条件end
结尾的end没有分号
CREATE OR REPLACE PROCEDURE fsa_update_sp
AS
v_table_name VARCHAR2 (30); CURSOR table_cursor
IS
SELECT table_name
FROM fsa.tabclezd
ORDER BY xh;
BEGIN
OPEN table_cursor; LOOP
FETCH table_cursor
INTO v_table_name; EXIT WHEN table_cursor%NOTFOUND; UPDATE v_table_name a
SET (a.item_code, a.item_nam, a.item_abv) =
(SELECT 新编码, 新名称, 新名称
FROM fsa.MASTER b
WHERE a.item_code = b.旧编码)
WHERE a.item_code = b.旧编码;
END LOOP; CLOSE table_cursor;
END;
as
v_TABLE_NAME varchar2(30);
CURSOR table_cursor is
select TABLE_NAME from FSA.TABCLEZD order by XH;
begin
open table_cursor ;
begin
loop
fetch table_cursor into v_TABLE_NAME ;
exit when table_cursor%notfound ;
update v_TABLE_NAME a set (a.item_code,a.ITEM_NAM,a.ITEM_ABV)
=(select 新编码,新名称,新名称
from FSA.master b where a.item_code=b.旧编码 )
where a.item_code=b.旧编码 ;
end loop ;
end;
close table_cursor ;
end fsa_update_sp;
这样就OK了
AS
v_table_name VARCHAR2 (30);
CURSOR table_cursor IS SELECT table_name FROM fsa.tabclezd ORDER BY xh;
BEGIN
OPEN table_cursor;
LOOP
FETCH table_cursor INTO v_table_name;
EXIT WHEN table_cursor%NOTFOUND;
execute immediate 'UPDATE '||v_table_name||' a SET (a.item_code, a.item_nam, a.item_abv) =
(SELECT 新编码, 新名称, 新名称
FROM fsa.MASTER b WHERE a.item_code = b.旧编码)
WHERE a.item_code = b.旧编码';
END LOOP;
CLOSE table_cursor;
END;