- /*the experiment environment*/
- create table parent ( p1 varchar2(100),p2 varchar2(100) );
- create table son(s1 number,s2 varchar2(100));
- create table log_table(user_id varchar2(100),error_date date,error_action varchar2(20));
- hr>insert into parent values('A','B');
- >commit;
- sys>alter user sh account unlock identified by sh;
- /*create procedure to record error action*/
- create or replace
- procedure proc_ins_log(
- p_act varchar2
- )
- is
- begin
- insert into log_table values(user,sysdate,p_act);
- end proc_ins_log;
- /*create procedure to insert values to son table*/
- create or replace procedure proc_ins_son(
- p_s1 son.s1%type,
- p_s2 son.s2%type,
- p_count out number
- )
- is
- v_count number :=0;
- e_foreign_key exception;
- e_primary_key exception;
- begin
- for rec in (select p1 from parent ) loop
- if rec.p1=p_s2
- then
- v_count :=v_count+1;
- end if;
- end loop;
- ---primary exception
- for rec in (select s1 from son) loop
- if p_s1=rec.s1 then
- raise e_primary_key;
- end if;
- end loop;
- ----foreignkey exception
- if v_count=0 then
- raise e_foreign_key;
- end if;
- ---normal
- insert into son values(p_s1,p_s2);
- commit;
- select count(*) into p_count from son;
- exception
- when e_foreign_key then
- rollback;
- dbms_output.put_line('foreign_error');
- proc_ins_log('foreign_key_error');
- when e_primary_key then
- rollback;
- dbms_output.put_line('primary_key');
- proc_ins_log('primary_key_error');
- end;
- select * from user_errors;
- -----test
- hr>grant execute on proc_ins_son to sh;
- >grant select on log_table to sh;
- >grant select on prant to sh;
- >grant select on son to sh;
- sh> select * from parent;
- P1 P2
- A B
- sh> select * from hr.son;
- S1 S2
- 1 A
- hr>select * from parent;
- P1 P2
- A B
- sh> set serverout on;
- sh> ed
- Wrote file afiedt.buf
- 1 declare
- 2 v_count number;
- 3 begin
- 4 hr.proc_ins_son(1,'A',v_count);
- 5 dbms_output.put_line(v_count);
- 6* end;
- sh> /
- primary_key
- PL/SQL procedure successfully completed.
- sh> ed
- Wrote file afiedt.buf
- 1 declare
- 2 v_count number;
- 3 begin
- 4 hr.proc_ins_son(2,'B',v_count);
- 5 dbms_output.put_line(v_count);
- 6* end;
- SQL> /
- foreign_error
- PL/SQL procedure successfully completed.
- sh> ed
- Wrote file afiedt.buf
- 1 declare
- 2 v_count number;
- 3 begin
- 4 hr.proc_ins_son(1,'B',v_count);
- 5 dbms_output.put_line(v_count);
- 6* end;
- sh> /
- primary_key /*when two errors are raise in the same time ,firstly to raise the first exception you catch*/
- PL/SQL procedure successfully completed.