--計算某讀者已借幾本書
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
v_rule rule%rowtype;
cursor cur_rule is (select readerid from reader);
begin
select * into v_rule from rule ;
for v_readerid 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;
End;
/
此時報錯：錯誤(270,61): PLS-00382: 運算式型別錯誤
求大佬們告知我原因和如何改正！！！謝謝。
uj5u.com熱心網友回復：
select * into v_rule from rule ;這是要查出來多行，怎么辦 ？
uj5u.com熱心網友回復：
嗯，rule的確不止只一條，難道用雙重游標回圈嗎，這個我不太會；試了一下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;
/
但還是報同樣的錯誤。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/65083.html
標籤：基礎和管理
上一篇：Oracle查詢問題