--计算某读者已借几本书
Create or replace function f_borrowed_num(v_readerid number)
return number
as
V_num number;
Begin
Select count(*) into v_num from reader,borrow where reader.readerid=v_readerid and reader.readerid=borrow.readerid;
Return v_num;
End;--利用自定义函数写一个只要在borrow表上有变动时就进行更新rest_borrow_num的触发器(双重游标循环我不太通,不太确定用的对不对)
create or replace trigger tr_update_borrow
after update or delete or insert on borrow
for each row
declare
cursor cur_reader is select readerid from reader;
cursor cur_rule is select * from rule ;
begin
for v_readerid in cur_reader loop
for v_rule in cur_rule loop
update reader set rest_borrow_num=v_rule.num-f_borrowed_num(v_readerid)
where v_readerid=:old.readerid and reader.readertype=v_rule.readertype;
end loop cur_reader;
end loop cur_rule;
End;
/
运行触发器时报错:错误(270,61): PLS-00382: 表达式类型错误
求大佬告诉我触发器哪咋错了,游标双重循环错了吗?
Create or replace function f_borrowed_num(v_readerid number)
return number
as
V_num number;
Begin
Select count(*) into v_num from reader,borrow where reader.readerid=v_readerid and reader.readerid=borrow.readerid;
Return v_num;
End;--利用自定义函数写一个只要在borrow表上有变动时就进行更新rest_borrow_num的触发器(双重游标循环我不太通,不太确定用的对不对)
create or replace trigger tr_update_borrow
after update or delete or insert on borrow
for each row
declare
cursor cur_reader is select readerid from reader;
cursor cur_rule is select * from rule ;
begin
for v_readerid in cur_reader loop
for v_rule in cur_rule loop
update reader set rest_borrow_num=v_rule.num-f_borrowed_num(v_readerid)
where v_readerid=:old.readerid and reader.readertype=v_rule.readertype;
end loop cur_reader;
end loop cur_rule;
End;
/
运行触发器时报错:错误(270,61): PLS-00382: 表达式类型错误
求大佬告诉我触发器哪咋错了,游标双重循环错了吗?
另外声明一下,结束循环的地方粘错了,不是报错原因。
end loop cur_rule;
end loop cur_reader;
另外声明一下,结束循环的地方粘错了,不是报错原因。
end loop cur_rule;
end loop cur_reader;
另外声明一下,结束循环的地方粘错了,不是报错原因。
end loop cur_rule;
end loop cur_reader;