How to use exception correctly in Oracle? |
|
尚未結案
|
|
tom18mychan
一般會員   發表：3 回覆：13 積分：3 註冊：2005-04-17 發送簡訊給我 |
In oracle, assume we have the following tables
Employee (eid, name)
Actor (aid, nationality)
Director (did, level)
Movie (mname, year, did, d_pay)
Starring (aid, mname, a_pay)
Sequel (original_name, sequel_name) And the requirement is
Let the “expenditure” of a movie be the total amount of money paid to its director and actors.
Then, the expenditure of any sequel should not exceed that of the original movie. I wrote
create or replace trigger overexpenditure before insert on sequel1
for each row
Declare
tempd_pay1 Integer;
tempa_pay1 Integer;
tempd_pay2 Integer;
tempa_pay2 Integer;
cost1 Integer;
cost2 Integer;
Begin
select d_pay into tempd_pay1 from movie1 where mname = :new.original_name;
exception
when no_data_found then
tempd_pay1:=0;
select sum(a_pay) into tempa_pay1 from starring1 group by mname having mname = :new.original_name;
exception
when no_data_found then
tempa_pay1:=0;
select d_pay int 2 o tempd_pay2 from movie1 where mname = :new.sequel_name;
exception
when no_data_found then
tempd_pay2:=0;
select sum(a_pay) into tempa_pay2 from starring1 group by mname having mname = :new.sequel_name;
exception
when no_data_found then
tempa_pay2:=0;
cost1:=tempd_pay1 tempa_pay1;
cost2:=tempd_pay2 tempa_pay2;
if (cost2>cost1) Then
raise_application_error (-20000, 'Illegal insert becasue expenditure of sequel is exceed its original');
end if;
end;
.
run;
But it doesn't work... However..if I type
create or replace trigger overexpenditure before insert on sequel1
for each row
Declare
tempd_pay1 Integer;
tempa_pay1 Integer;
tempd_pay2 Integer;
tempa_pay2 Integer;
cost1 Integer;
cost2 Integer;
Begin
select d_pay into tempd_pay1 from movie1 where mname = :new.original_name;
if sql%rowcount=0 then
tempd_pay1:=0;
end if;
select sum(a_pay) into tempa_pay1 from starring1 group by mname having mname = :new.original_name;
if sql%rowcount=0 then
tempa_pay1:=0;
end if;
select d_pay into tempd_pay2 from movie1 where mname = :new.sequel_name;
if sql%rowcount=0 then
tempd_pay2:=0;
end if;
select sum(a_pay) into tempa_pay2 from starring1 group by mname having mname = :new.sequel_name;
if sql%rowcount=0 then
tempa_pay2:=0;
end if;
cost1:=tempd_pay1 tempa_pay1;
cost2:=tempd_pay2 tempa_pay2;
if (cost2>cost1) Then
raise_application_error (-20000, 'Illegal insert becasue expenditure of sequel is exceed its original');
end if;
end;
.
run;
It will also output
the following error message when I input
[blue]
insert into sequel1 values('troy returns', 'troy returns again');
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "C50481550.OVEREXPENDITURE", line 13
ORA-04088: error during execution of trigger 'C50481550.OVEREXPENDITURE'
Please help me~~~
I spent a lot of time on it...
|
|
tom18mychan
一般會員 發表：3 回覆：13 積分：3 註冊：2005-04-17 發送簡訊給我 |
I have a new idea for the requirement, that is to create 2 function plus the trigger...
however, it has compilation error...
please help me to correct the syntax below....
create or replace function chkexistd (targetname1 varchar2(50))
return Integer as
temp1 Integer;
begin
select d_pay into temp1 from movie1 where mname = targetname1;
return temp1;
Exception
when no_data_found then
temp1:=0;
return temp1;
end;
/ create or replace function chkexista (targetname2 varchar2(50))
return Integer as temp2;
begin
select sum(a_pay) into temp2 from starring1 group by mname having mname =targetname2;
return temp2;
Exception
when no_data_found then
temp2:=0;
return temp2;
end chkexista;
/ create or replace trigger overexpenditure before insert on sequel1
for each row
Declare
tempd_pay1 Integer;
tempa_pay1 Integer;
tempd_pay2 Integer;
tempa_pay2 Integer;
cost1 Integer;
cost2 Integer;
Begin
tempd_pay1=chkexistd(:new.original_name);
tempa_pay1=chkexista(:new.original_name);
tempd_pay2=chkexistd(:new.sequel_name);
tempa_pay2=chkexista(:new.sequel_name);
cost1:=tempd_pay1 tempa_pay1;
cost2:=tempd_pay2 tempa_pay2;
if (cost2>cost1) Then
raise_application_error (-20000, 'Illegal insert becasue expenditure of sequel is exceed its original');
end if;
end;
.
run;
/
Thanks you
|
|
tom18mychan
一般會員 發表：3 回覆：13 積分：3 註冊：2005-04-17 發送簡訊給我 |
The error of the first function is the following
NAME TYPE SEQUENCE LINE POSITION
------------------------------ ------------ ---------- ---------- ----------
TEXT
--------------------------------------------------------------------------------
CHKEXISTD FUNCTION 1 1 41
PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character
The symbol ":=" was substituted for "(" to continue.
|
|
Fishman
尊榮會員   發表：120 回覆：1949 積分：2163 註冊：2006-10-28 發送簡訊給我 |
Hi tom18mychan,
create or replace function chkexista(targetname2 in varchar2)
return integer is
temp2 integer;
begin
begin
select nvl(sum(a_pay),0) into temp2 from starring1 where mname = targetname2;
Exception
when no_data_found then
temp2:=0;
end;
return temp2;
end chkexista ;
------
Fishman |
發表時間：2005-04-20 19:38:25
IP:144.214.xxx.xxx 未訂閱
系統時間：2024-07-07 11:44:12
聯絡我們 | Delphi K.Top討論版
| 本站聲明 |
|
1. 本論壇為無營利行為之開放平台，所有文章都是由網友自行張貼，如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權，而損及您的利益，請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨，是藍是綠本站無權干涉，但這裡不是政治性論壇！ |