问题一:
在定义部分,没有:号的。
问题二:
不能在触发体再次引用触发表。用一个函数实现你的方法:
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/create function name_f(p_id in varchar2)
return pkg_test.myrctype
as
p_rc pkg_test.myrctype;
str varchar2(80);
begin
str:='select equip_data.Region_id
from equip_data,sdh_ne
where sdh_ne.Equip_id = equip_data.equip_id and
sdh_ne.sdh_sys_id = '''||p_id||'''';
open p_rc for str;
return p_rc;
end;
/CREATE OR REPLACE TRIGGER tg_sdh_ne_delete
before delete on sdh_ne
for each row
declare
v_Region_id varchar2(50);
v_Region_Flag number(6);
rc pkg_test.myrctype
begin
v_Region_Flag := 0;
rc:=name_f(:old.sdh_sys_id);
open rc;
loop
fetch rc into v_Region_id;
exit when rc%notfound;
v_Region_Flag := Bit_Or_tonum(v_Region_Flag, power(2,v_Region_id- 1));
end loop;
close rc;
--...................
--................
end ;
/
在定义部分,没有:号的。
问题二:
不能在触发体再次引用触发表。用一个函数实现你的方法:
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/create function name_f(p_id in varchar2)
return pkg_test.myrctype
as
p_rc pkg_test.myrctype;
str varchar2(80);
begin
str:='select equip_data.Region_id
from equip_data,sdh_ne
where sdh_ne.Equip_id = equip_data.equip_id and
sdh_ne.sdh_sys_id = '''||p_id||'''';
open p_rc for str;
return p_rc;
end;
/CREATE OR REPLACE TRIGGER tg_sdh_ne_delete
before delete on sdh_ne
for each row
declare
v_Region_id varchar2(50);
v_Region_Flag number(6);
rc pkg_test.myrctype
begin
v_Region_Flag := 0;
rc:=name_f(:old.sdh_sys_id);
open rc;
loop
fetch rc into v_Region_id;
exit when rc%notfound;
v_Region_Flag := Bit_Or_tonum(v_Region_Flag, power(2,v_Region_id- 1));
end loop;
close rc;
--...................
--................
end ;
/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货