This presentation is the property of its rightful owner.
Sponsored Links
1 / 57

第九章 异常处理 PowerPoint PPT Presentation


  • 71 Views
  • Uploaded on
  • Presentation posted in: General

第九章 异常处理. 9.1 什么是异常情态 异常处理处理的是运行时错误,异常分为预定义异常和用户自定义异常。 声明异常情态 异常情态在块的声明部分进行声明,在块的执行部分进行触发,在块的异常部分进行处理。 用户定义类型异常情态 例: Declare e_toomanystudents exception;. e_toomanystudents 的作用域和本块的其他变量相同。 预定义型异常情态 预定义型异常情态可以直接使用,没有必要声明。 Invalid_cursor: 当执行非法的游标操作时,会引发这个错误,如试图关闭已关闭的游标。

Download Presentation

第九章 异常处理

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


6431163

  • 9.1

    • Declare

    • e_toomanystudents exception;


  • 6431163

    • e_toomanystudents

    • Invalid_cursor:

    • Cursor_already_open

    • No_data_foundselect intopl/sql

    • Too_may_rowsselect into

    • Invalid_numbervalue_error

    • invalid_numberx

    • Insert into students(id,first_name,last_name)

    • values (x,scott,smith);


  • 6431163

    • Storage_errorprogram_error

    • Value_errorsqlinvalid_numberselect intovalue_error

    • Declare

    • v_tempvar varchar2(3);

    • Begin

    • v_tempvar:='ABCD';

    • End;

    • Declare

    • v_tempvar number(2);

    • Begin

    • select id into v_tempvar from students

    • where last_name='smith';

    • End;


    6431163

    • Rowtype_mismatch:pl/sql

    • raiseraise

      • Declare

      • e_toomanystudents exception

      • v_currentstudents number(3);

      • v_maxstudents number(3);

      • Begin

      • select current_students,max_students

      • into v_currentstudents,v_maxstudents

      • from classes

      • where department=HIS and course=101;


  • 6431163

    • if v_currentstudents>v_maxstudents then

    • raise e_toomanystudents

    • End if;

    • End;

      • Exception

      • when exception_name then

      • when exception_name then

      • when others then

      • end;


  • 6431163

    • or

    • Exception when no_data_found or too_many_rows then

    • insert into log_table(info) values (a select error occurred);

    • End;

  • Others

    • Declare

    • e_toomanystudents exception

    • v_currentstudents number(3);

    • v_maxstudents number(3);

    • v_errorcode number;

    • v_errortext varchar2(200);

    • Begin

    • select current_students,max_students

    • into v_currentstudents,v_maxstudents

    • from classes

    • where department=HIS and course=101;


  • 6431163

    • if v_currentstudents>v_maxstudents then

    • raise e_toomanystudents

    • End if;

    • Exception

    • when e_toomanystudents then

    • insert into log_table(info)

    • values (history 101 has || v_currentstudents ||

    • students: max allowed is || v_maxstudents);

    • when others then

    • v_errorcode:=sqlcode;

    • v_errortext:=substr(sqlerrm,1,200);

    • insert into log_table(info)

    • values (another error occurred);

    • end;

    • *sqlcodesqlerrmsqlsqlcodesqlerrmsqlsqlcodesqlerrm


    6431163

    • Sqlerrm

    • Sqlerrm(0): ora-0000:normal,successful completion

    • sqlerrm(100): ora-1403:no data found

    • 100user-defined exception

    • sqlerrm()ora-0000:normal,successful completion

    • sqlcode0

    • sqlcode: ora-1403:no data found,sqlcode+100.


    6431163

    • Declare

    • v_errortext log_table.message%type;

    • Begin

    • v_errortext:=substr(sqlerrm(0),1,200);

    • insert into log_table(code,message,info)

    • values(0,v_errortext,sqlerrm(0));

    • v_errortext:=substr(sqlerrm(100),1,200);

    • insert into log_table(code,message,info)

    • values(100,v_errortext,sqlerrm(100));

    • v_errortext:=substr(sqlerrm(10),1,200);

    • insert into log_table(code,message,info)

    • values(10,v_errortext,sqlerrm(10));


    6431163

    • v_errortext:=substr(sqlerrm,1,200);

    • insert into log_table(code,message,info)

    • values(null,v_errortext,sqlerrm);

    • v_errortext:=substr(sqlerrm(-1),1,200);

    • insert into log_table(code,message,info)

    • values(-1,v_errortext,sqlerrm(-1));

    • v_errortext:=substr(sqlerrm(-54),1,200);

    • insert into log_table(code,message,info)

    • values(-54,v_errortext,sqlerrm(-54));

    • End;

    • log_table


    6431163

    • Exception_init

      • oraclewhenothersexception_init pragma

      • Pragma exception_init (exception_name,oracle_error_number)

      • Pragma

      • ora-1400mandatory not

      • null column missing or null during inserte_missingnull

      • Declare

      • e_missingnull exception;

      • pragma exception_init (e_missingnull,-1400);

      • Begin

      • insert into students(id) values(null);

      • Exception

      • when e_missingnull then

      • insert into log_table(info) values(ora-1400 occurred);

      • End;


    6431163

    • pragma exception_initoraclesqlcodesqlerrmoracle

    • Declare

    • ex_hfy exception;

    • Pragma exception_init(ex_hfy,-1400);

    • Begin

    • insert into students values(null);

    • Exception

    • when ex_hfy then

    • dbms_output.put_line('');

    • End;

    • -1400insert


    6431163

    • raise_application_error

      • raise_application_error

      • Raise_application_error(error_number,error_message,[keep-errors]);

      • error_number20,00020,999error_messageKeep_errorstruefalse


    6431163

    • Create or replace procedure register(

    • p_studentid in students.id%type,

    • p_department in classes.department%type,

    • p_course in classes.course%type) as

    • v_currentstudents number;

    • v_maxstudents number;

    • Begin

    • select current_students,max_students

    • into v_currentstudent,v_maxstudents

    • from classes

    • where department=p_department

    • and course=p_course;


    6431163

    • if v_currentstudents+1>v_maxstudents then

    • raise_application_error(-20000,

    • cant add more students to ||

    • p_department || ||p_course);

    • end if;

    • classpackage.addstudent(p_studentid,

    • p_department,p_course);

    • Exception

    • when no_data_found then

    • raise_application_error(-20001,p_department ||

    • || p_course || doesnt exist!);

    • End register;

    • ora-20000: cant add more students to his 101


    6431163

    • oracle

  • 1


  • 6431163

    • 12

      • Declare

      • --outer block

      • Begin

      • declare

      • --begin inner block 1

      • begin

      • end;


    6431163

    • begin

    • --inner block 2

    • end;

    • --end outer block

    • End;

    • Begin

    • --f

    • f( );

    • End;


    6431163

    • 1

      • 1a

    Declare

    A exception ;

    begin

    Begin

    Raise a

    Exception

    when a then

    End;

    End;


    6431163

    • 2

      • 2

    Declare

    A exception;

    B exception;

    begin

    Begin

    Raise B

    Exception

    when a then

    End;

    Exception

    When b then

    End;


    6431163

    • 3

      • 22

  • 1.4

    • value_error

    • V_number number(3):=abc;

    • others


  • 6431163

    begin

    Declare

    v_number number:=abc;

    Begin

    Exception

    When others then

    End;

    Exception

    When others then

    End;


    6431163

    • s

      • raise

      • raise


    6431163

    begin

    • Declare

    • a exception;

    • b exception;

    • Begin

    • raise a;

    • Exception

    • when a then

    • raise b;

    • when b then

    • End;

    Exception

    when b then

    End;


    6431163

        • Declare

        • Begin

        • declare

        • e_userdefinedexception exception;

        • begin

        • raise e_userdefinedexception;

        • end;


    6431163

    • Exception

    • e_usedefinedexceptionothers

    • when others then

    • End;

    • raise_application_error

    • Create or replace package globals

    • e_userdefinedexception exception;

    • End globals;

    • globals


    6431163

    • Declare

    • Begin

    • begin

    • raise e_userdefinedexception;

    • end;

    • Exception

    • when globals.e_userdefinedexception then

    • when others then

    • End;


    6431163

      • others

        • Declare

        • v_errornumber number;

        • v_errortext varchar2(200);

        • Begin

        • Exception

        • when others then

        • v_errornumber:=sqlcode;

        • v_errortext:=substr(sqlerrm1,200);


    6431163

    • insert into log_table(code,message,info) values

    • (v_errornumber,v_errortext,oracle error occurred at ||

    • to_char(sysdate,DD-MON-YY HH24:MI:SS));

    • END;

    • sql

    • Begin

    • select

    • select

    • select

    • Exception

    • when no_data_found then

    • End;


  • 6431163

    • sql

    • Declare

    • v_selectcounter number:=1;

    • Begin

    • select..

    • v_selectcounter:=2;

    • select

    • v_selectcounter:=3;

    • select

    • Exception

    • when no_data_found then

    • insert into log_table((info) values

    • (no data found in select || v_selectcounter);

    • End;


    6431163

    • Begin

    • begin

    • select

    • exception

    • when no_data_found then

    • insert into log_table(info) values

    • (no data found in select 1);

    • end;

    • begin

    • select

    • exception

    • when no_data_found then

    • insert into log_table(info) values

    • (no data found in select 2);

    • end;

    • End;


    6431163

    • pl/sql

      • Pl/sqldbms_utility.format_call_stackvarchar2abbc

      • Create or replace procedure c as

      • v_callstack varchar2(2000);

      • Begin

      • v_callstack:=dbms_utility.format_call_stack;

      • insert into temp_table(char_col) values (v_callstack);

      • insert into temp_table(num_col) values (-1);

      • End c;

      • Create or replace procedure b as

      • Begin

      • c;

      • End b;


    6431163

    • Create or replace procedure a as

    • b

    • End a;

    • temp_table

    • ----Pl/sql call stack---

    • Object line object

    • Handle number name

    • 1699810 4 procedure example.c

    • 1699ca0 3 procedure example.b

    • 1691918 3 procedure example.a

    • 1667ef0 1 anonymous block

    • Dbms_utility.format_error_stack


    6431163

    • Create table errors(

    • module varchar2(50),

    • seq_number number,

    • error_number number,

    • error_mesg varchar2(100),

    • error_stack varchar2(2000),

    • call_stack varchar2(2000),

    • timestamp date,

    • primary key(module,seq_number));


    6431163

    • Create table call_stacks(

    • module varchar2(50),

    • seq_number number,

    • call_order number,

    • object_handle varchar2(10),

    • line_num number,

    • object_name varchar2(80),

    • primary key(module,seq_number,call_order),

    • foreign key(module,seq_number) references errors on delete cascade);

    • Drop table error_stacks;


    6431163

    • Create table error_stacks(

    • module varchar2(50),

    • seq_number number,

    • error_order number,

    • facility char(3),

    • error_number number(5),

    • error_mesg varchar2(100),

    • primary key(module,seq_number,error_order),

    • foreign key(module,seq_number) references errors on delete cascade);

    • Create sequence error_seq

    • start with 1

    • increment by 1;

    • errorpkg

    • --errorscall_stackserors_stacks


    6431163

    • Create or replace errorpkg as

    • --handleall---p_toptruefalse

    • procedure handleall(p_top boolean);

    • --moduleseq_numberdbms_output

    • --

    • procedure printstacks (p_module in errors.module%type,

    • p_seqnum in errors.seq_number%type);

    • /*errorscall_stacks

    • seq_numberp_commitflagtruestorestackshandleallp_top=true*/


    6431163

    • procedure storestacks(p_module errors.module%type,

    • p_seqnum errors.seq_number%type,

    • p_commitflag boolean default false);

    • End errorpkg;

    • Create or replace package body errorpkg as

    • v_newline constant char(1):=char(10);

    • v_handled boolean:=false;

    • v_errorstack varchar2(2000);

    • v_callstack varchar2(2000) ;


    6431163

    • Procedure handleall(p_top in boolean) is

    • Begin

    • if p_top then

    • v_handled:=false;

    • elsif not v_handled then

    • v_handled:=true;

    • v_errorstack:=dbms_utility.format_error_stack;

    • v_callstack:=dbms_utility.format_call_stack;

    • end if;

    • End handleall;

    • Procedure printstack(p_module in errors.module%type,

    • p_seqnum in errors.seq_number%type) is

    • v_timestamp errors.timestamp%type;

    • v_errormsg errors.error_mesg%type;


    6431163

    • cursor c_callcur is

    • select object_handle,lin_num,object_name

    • from call_stacks

    • where module=p_module and seq_number=p_seqnum

    • order by call_order;

    • cursor c_errorcur is

    • select facility,error_number,error_mesg

    • from error_stacks

    • where module=p_module and seq_number=p_seqnum

    • order by error_order;

    • Begin

    • select timestamp,error_mesg

    • into v_timestamp,v_errormsg

    • from errors

    • where module=p_module and seq_number=p_seqnum;


    6431163

    • --

    • dbms_output.put(to_char(v_timestamp,

    • DD-MON-YY HH24:MI:SS));

    • dbms_output.put(module: || p_module);

    • dbms_output.put(error# || p_seqnum || :);

    • dbms_output.put_line(v_errormsg);

    • --

    • Dbms_output.put_line(complete call stack:);

    • Dbms_output.put_line(object handle line number object name);

    • Dbms_output.put_line(---------------------------------------------);

    • For v_callrec in c_callcur loop

    • dbms_output.put(rpad( || v_callrec.object_handle,15));

    • dbms_output.put(rpad( ||v_callrec.linenum,13));

    • dbms_output.put_line( ||v_callrec.object_name);

    • End loop;


    6431163

    • --

    • Dbms_output.put_line(complete error stack:);

    • For v_errorrec in c_errorcur loop

    • dbms_output.put( || v_errorrec.facility || -);

    • dbms_output.put(to_char(v_errorrec.error_number) || :);

    • dbms_output.put_line(v_errorrec.error_mesg);

    • End loop;

    • End printstacks;

    • procedure storestacks(p_module in errors.module%type,

    • p_seqnum out errors.seq_number%type,

    • p_commitflag boolean default false) is

    • v_seqnum number;

    • v_index number;

    • v_length number;

    • v_end number;

    • v_call varchar2(100);


    6431163

    • v_callorder number:=1;

    • v_handle call_stacks.object_handle%type;

    • v_linenum call_stacks.line_num%type;

    • v_object_name call_stacks.object_name%type;

    • v_error varchar2(120);

    • v_errororder number:=1;

    • v_facility error_stacks.facility%type;

    • v_errnum error_stacks.error_number%type;

    • v_errmsg error_stacks.error_mesg%type;

    • v_firsterrnum error.error_number%type;

    • v_firsterrmsg error.error_mesg%type;

    • Begin

    • select error_seq.nextval

    • into v_seqnum

    • from dual;


    6431163

    • p_seqnum:=v_seqnum;

    • --errors

    • insert into errors(

    • module,seq_number,error_stack,call_stack,timestamp)

    • values

    • (p_module,v_seqnum,v_errorstack,v_callstack,sysdate);

    • --

    • v_index:=1;

    • --

    • while v_index <length(v_callstack) loop

    • --v_endv_newline


    6431163

    • v_end:=instr(v_errorstack,v_newline,v_index);

    • --index

    • v_error:=substr(v_errorstack,v_index,v_end-v_index);

    • --

    • v_index:=v_index+length(v_error)+1;

    • --facility-number:mesg

    • --facility3

    • v_facility:=substr(v_error,1,3);

    • --facility4

    • v_error:=substr(v_error,5);

    • --

    • v_errnum:=to_number(substr(v_error,1,

    • instr(v_error,:,)-1);

    • --7

    • v_error:=substr(v_error,8);

    • v_errmsg:=v_error;


    6431163

    • insert into error_stacks

    • (module,seq_number,error_order,facility,error_number,

    • error_mesg)

    • Values

    • (p_module,p_seqnum,v_errororder,v_facility,v_errnum,

    • v_errmsg);

    • If v_errororder=1 then

    • v_firsterrnum:=v_errnum;

    • v_firsterrmsg:=v_facility || - || to_number(v_errnum)

    • || : || v_errmsg;

    • End if;

    • V_error:=v_errorder+1;

    • End loop;


    6431163

    • --messagecode

    • Update errors

    • set error_number=v_firsterrnum,

    • error_mesg=v_fisrerrmsg

    • where module=p_module and seq_number=v_seqnum;

    • --name

    • v_index:=instr(v_callstack,name)+5;

    • --


    6431163

    • while v_index<length(v_callstack) loop

    • --v_end

    • v_end:=instr(v_callstack,v_newline,v_index);

    • --v_callindex

    • v_call:=substr(v_callstack,v_index,v_end-v_index);

    • --callcall

    • v_index:=v_index+length(v_call)+1;

    • --callobject handle,line number,------object name,

    • v_call:=ltrim(v_call);

    • --object handle

    • v_handle:=substr(v_call,1,instr(v_call, ));

    • --callobject handle

    • v_call:=substr(v_call,length(v_handle)+1);

    • v_call:=ltrim(v_call);

    • --line number

    • v_linenum:=to_number(substr(v_call,1,instr(v_call, )));


    6431163

    • --line_number

    • V_call:=substr(v_call,length(v_linenum)+1);

    • V_call:=ltrim(v_call);

    • --object name

    • V_objectname:=v_call;

    • --callcall_stackserrorpkg

    • If v_callorder>1 then

    • insert into call_stacks

    • (module,seq_number,call_order,object_handle,line_num,

    • object_name)

    • values

    • (p_module,v_seqnum,v_callorder,v_handle,v_linenum,

    • v_objectname);

    • end if;


    6431163

    • v_callorder:=v_callorder+1;

    • End loop;

    • If p_commitflag then

    • commit;

    • End if;

    • End storestacks;

    • End errpkg;

    • temp_tablezero_divideABC

    • Create or replace trigger temp_insert

    • before insert on temp_table

    • Begin

    • raise zero_divide;

    • End temp_insert;


    6431163

    • Create or replace procedure c as

    • Begin

    • insert into temp_table (num_col) values (7);

    • Exception

    • when others then

    • errorpkg.handleall(false);

    • raise;

    • End c;


    6431163

    • Create or replace procedure b as

    • Begin

    • c;

    • Exception

    • when others then

    • errorpkg.handleall(false);

    • raise;

    • End b;


    6431163

    • Create or replace procedure a as

    • v_errorseq number;

    • Begin

    • b;

    • Exception

    • when others then

    • errorpkg.handleall(true);

    • errorpkg.storestacks(error test,v_errorseq,true);

    • errorpkg.printstacks(error test,v_errorseq);

    • End a;

    • (A)


    6431163

    • When others then

    • errorpkg.handle(false);

    • raise;

    • errorpkgtruehandleallerrorpkgstorestackserrorserror_stackscall_stacksstorestacks

    • printstacksdbms_output

    • sql*plusA


    6431163

    • SQL>set serveoutput on size 1000000 formate truncated

    • SQL>exec A;

    • 18-jan-97 22:14:49 module: error test error# 7:ora-1476:divisor is equal to zero

    • Complte call stack:

    • Object handle line number object name

    • ---------------------------------------------------

    • 16998f0 6 procedure example.c

    • 1699ca0 3 procedure example.b

    • 1691918 4 procedure example.a

    • 16570e0 1 anonymous block


    6431163

    • Complete error stack:

    • ORA-1476:divisor is equal to zero

    • ORA-6512:at example.temp_insert,line 2

    • ORA-4088:error during execution of trigger exapmle.temp_insert


  • Login