PL/SQL Exception handling method

PL/SQL Exception handling method  1: What is exception handling: PL/SQL provides a function to handle exceptions, called exception Handling in PL/SQL blocks, using exception handling we are able to test code and avoid exception exits. PL/SQL exception information contains three parts:      1: Exception type        2: Error code         3: Error message by handling exceptions we are able to ensure that the PL/SQL block does not unexpectedly exit unexpectedly.  2: Structure of exception handling declare   declaration section  begin    exception section   exception  when ex_name1 then    -error handling Statements  when ex_name2 then    -error handling statements  when Others then    -error Handling statements end;  Example: When others can intercept all exceptions, it is generally placed at the end of the exception handling. The above code explains: When an exception occurs, Oracle looks for the appropriate time exception type to perform the exception.   Good examples of exception handling: Except for 0 exceptions:[sql] sql> DECLARE    2     stock_price number: = 9.73;    3     net_earnings number: = 0;    4     Pe_ratio number;    5  begin    6  --calculation might cause Division-by-zero error.    7   &NBSp Pe_ratio: = stock_price/net_earnings;    8     Dbms_output. Put_Line (' price/earnings ratio = ' | | pe_ratio);    9  exception  --EXCEPTION handlers begin   10  --only one of the while blocks is ex Ecuted.   11     When Zero_divide then  --handles ' division by ZERO ' ERROR   12        dbms_output. Put_Line (' company must has had zero earnings. ');   13        pe_ratio: = NULL;   14     When OTHERS then  --handles all other errors   15         Dbms_output. Put_Line (' Some other kind of error occurred. ');   16        pe_ratio: = NULL;   17  END;  --exception handlers and block end here   18  /   pl/sql process completed successfully.    SQL> set serveroutput on  SQL>/ company must has had zero earnings. &nBSP; In order to avoid the addition of 0 can do this: [Sql] declare     stock_price Number: = 9.73;     net_earnings Number: = 0;     pe_ratio number;  begin     pe_ratio: =        Case net_earnings           when 0 Then NULL           else stock_price/net_earnings        End  END;  /    If there is a nested PL/SQL block like this: delcare   declaration section  begin    declare      Declaration section     begin       execution secti on     exception       EXCEPTION section     end;   exception   exception section  end;   In the above example: If an exception occurs inside a block, the inner exception handling block should handle the exception, If the internal processing block does not handle this exception, the control will transfer its upper-level PL/SQL block, and if there is no corresponding exception-handling block at the previous level, the program will end incorrectly.  3: Exception type: Exception has three kinds:   A: System named exception   B: System unnamed exception  C: User-defined exceptions (this is not used)  a: System-named Exceptions: when a program violates a relational database rule, Oracle's system exceptions automatically occur, and some system anomalies occur more frequently, and they are predefined exceptions, which have a name. For example, No_data_found and Zero_divide have a system exception with names. Common are: Oracle exception NameOracle Error explanation Dup_val_on_indexORA-00001 you tried to execute an INSERT or UPDATE statement that have created a duplicate value in a field rest Ricted by a unique index. Timeout_on_resourceORA-00051 were waiting for a resource and you timed out.Transaction_backed_outORA-00061 The remote portion of a transaction have rolled back. Invalid_cursorORA-01001 tried to reference a cursor, that does not yet exist. This may has happened because you ' ve executed a FETCH cursor or CLOSE cursor before OPENing the cursor. not_logged_onORA-01012 tried to execute a call to Oracle before logging in. Login_deniedORA-01017 tried to log into Oracle with an invalid Username/password combination. No_data_foundORA-01403 you tried one of the following: You executed a SELECT into statement and no rows were returned. You referenced the uninitialized row in a table. You read past the end of file with the Utl_file. Too_many_rowsORA-01422 you tried to execute a SELECT in statement and more than one row is returned. Zero_divideORA-01476 tried to divide a number by zero. Invalid_numberORA-01722 you tried to execute an SQL statement that tried to convert a string to a number, but it is unsucces Sful. Storage_ErrorORA-06500 you ran out of memory or memory is corrupted. Program_errorORA-06501 This is a generic ' contact Oracle support ' message because an internal problem was encountered. Value_errorORA-06502 tried to perform an operation and there is a error on a conversion, truncation, or invalid const Raining of numeric or character data. Cursor_already_openORA-06511 you tried to open a cursor this is already open. The above example has a method for using named exceptions. There's no more talking here. begin   execution Sectionexception when no_data_found then  dbms_output.put_line (' A SELECT ... into do not return any row. ');   end;  b: Unnamed System exception: These system exceptions do not have names, and these exceptions do not occur frequently, and these exceptions have error codes and associated information. There are two ways to handle unnamed exceptions:        method One: Use when OTHERS exception handling         Method Two: Associate exception codes and names with an exception, and then look like a named exception Use it as you like. Method One exception is not objective, the following shows method two: using pragma call Exception_init to associate a predefined Oracle error number to a program-defined exception is a demo:declare     Exception_name exception;    PRAGMA    exception_init (Exception_name, Err_code);  begin execution sectionexception  when Exception_name then     handle the ExceptionEND; Declare the exception name first, then call the Exception_init function binding error number and exception name, after binding the name of the exception can be used as a named exception. For example:[sql] sql> DECLARE    2         E_INSERT_EXCEP  EXCEPTION;  --defining exception Names    3         PRAGMA exception_init (e_insert_excep,-01400);  --associated exception name and XOR   number    4         BEGIN    5         INSERT into departments (department_id,depar Tment_name) VALUES (280,n  ull);    6         EXCEPTION    7          when  e_insert_ Excep then    8            dbms_output. Put_Line (' INSERT operation FAILED ');    9            dbms_output. Put_Line (SQLERRM);   10         END;   11  / insert Operation FAILED  ora-01400: NULL cannot be inserted ("HR". " Departments "." Department_name ") The    pl/sql process has completed successfully.    first defines a E_INSERT_EXCEP exception name, then calls the Exception_init function to bind the exception name, and finally an exception output error message. SQLERRM information is as follows: ORA-01400: Unable to insert NULL ("HR". " Departments "." Department_name ")    need to correlate numbers and custom error names; Two variables: SQLCODE   error code             SQLERRM   Error content     When an exception is thrown, these two variables are automatically populated to get both valuesTo judge     error:          3: User-defined exception: see three examples to explain:ex1:[sql] sql>  declare & nbsp;  2       V_DEPTNO number: = 500;    3       v_name  varchar2 ( := ' testing ');    4       e_invalid_department EXCEPTION;    5    begin    6       UPDATE departments SET department_name = V_name &nbs p;  7       WHERE  department_id = V_deptno;    8    9       IF Sql%notfound then   10          r AISE  e_invalid_department;   11       END IF;   12       COMMIT;   13     EXCEPTION   14       when E_invalid_department then   15 & nbsp      dbms_output. Put_Line (' No such department id ');   16    END;    / no Such department ID    pl/sql process completed successfully.    about raise_application_error   process; Syntax: Raise_application_error (error_number,message[,{true| FALSE}]); You can define your own exception number and exception information by this procedure note: Error_number is a number that is between: -20000..-20999 and the message is a string with a maximum length of 2k[sql] sql> DECLARE    2      V_DEPTNO number: = 500;    3      v_name VARCHAR2: = ' testing ';    4      e_invalid_department EXCEPTION;  --defines an exception    5      pragma  exception_init (e_invalid_department,-20188);    --to bind exception and exception  ,    6  begin    7      update  departments &N bsp;  8      set  department_name =v_name    9      where department_id = V_deptno;   10   11      if Sql%notfound then   12   13         Raise_application_ERROR ( -20188, ' I write my error message here! ');     14     END IF;   15     COMMIT;   16   EXCEPTION   17          when  e_invalid_department then &NB sp; 18               Dbms_output. Put_Line (SQLCODE | | '---> ' | | SQLERRM);   19  END;   20   21  / -20188--->ora-20188:i write my error message here! The    PL/SQL process has completed successfully.   Description: Raise_application_error () Raise_application_error is a built-in stored procedure that can display user-defined error messages and error numbers. These error numbers are open for developers by Oracle and range from: -20000 and-20999 when using Raise_application_error, the previous transaction is not committed and automatically rolled back. The syntax format is as follows: Raise_application_error (Error_number, error_message);  use raise_application_error steps as follows:  1: First customize a exception,2 in the declaration area: Raise user-defined Exception3 under a specific logic rule: Last catch this exception, using Raise_application_ after capture Error This procedure links the custom bug number and error message.   Focus: On the propagation mechanism of anomalies: compare the following three examples to understand the abnormal propagation mechanism: EX1:[SQL] create OR REPLACE PROCEDURE add_more_departments (p_name1 VARCHAR2, p_mgr1 number, P_loc1 number,p_name2 VARCHAR2 , P_MGR2 number, p_loc2 number) is  begin    INSERT into departments (Department_id,department_name, manager_id,location_id) VALUES (departments_seq. NEXTVAL,P_NAME1,P_MGR1,P_LOC1);    Dbms_output. Put_Line (' Add Dept: ' | | p_name1);      insert into departments (department_id,department_name,manager_id,location_id) VALUES ( Departments_seq. NEXTVAL,P_NAME2,P_MGR2,P_LOC2);   dbms_output. Put_Line (' Add Dept: ' | | p_name2);    exception         When OTHERS then           DBM S_output. Put_Line (' err:adding dept: ');  END;  /           create OR REPLACE PROCEDURE create_more_departments  is   BEGIN    add_more_departments (' Media ', 100,1800, ' Editing ', 99,1800);  END;  /   begin  create_more_departments;  END;     in this example 99 this record is already in the table, now insert again, will conflict error execution result: The above two insert only the first inserted, the second insert failed. The execution results are as follows:sql>/add Dept:MediaErr:adding The Dept:pl/sql process was completed successfully.   department_id department_name-------------------------------------------          IT helpdesk          Government sales          Retail sales          260 recruiting          payroll          340 media   has selected 28 rows. Only the first one is inserted EX2: Delete the previous experiment results before doing the second example: delete from departments where department_id >270;select department_id,department _name from Departments order by 1; [sql] create OR REPLACE PROCEDURE add_more_departments (p_name1 VARCHAR2, p_m GR1 number, P_loc1 number,p_name2 VARCHAR2, p_mgr2 number, p_loc2 number) is  begin    INSERT into Departme NTS (Department_id,department_name,manager_id,location_id) VALUES (departments_seq. NEXTVAL,P_NAME1,P_MGR1,P_LOC1);    Dbms_output. Put_Line (' Add Dept: ' | | p_name1);      insert into departments (department_id,department_name,manager_id,location_id) VALUES ( Departments_seq. NEXTVAL,P_NAME2,P_MGR2,P_LOC2);   dbms_output. Put_Line (' Add Dept: ' | | p_name2);    END;  /           create OR REPLACE PROCEDURE create_more_departments  is   BEGIN    add_more_departments (' Media ', 100,1800, ' Editing ', 99,1800);  END;  /    begin  create_more_departments;  END;     execution results are as follows:sql> begin  2  create_more_departments;  3  END;  4  /add dept:mediabegin* Line 1th Error: ORA-02291: violation of full constraint (HR. DEPT_MGR_FK)-parent keyword not found ORA-06512: in "HR. Add_more_departments ", line 6ora-06512: in" HR. Create_more_departments ", line 4ora-06512: On line 2  query result:   department_id departmeNt_name-------------------------------------------          IT helpdesk          Government sales          Retail sales        &N Bsp 260 recruiting          Payroll This time there is no exception handling, removing the exception handling. The result of this execution: Two records a record was not inserted.  ex3:delete from departments where department_id >270;select department_id,department_name from departments ORDER by 1; [sql] create OR REPLACE PROCEDURE add_more_departments (p_name1 VARCHAR2, p_mgr1 number, P_loc1 NUMB Er,p_name2 VARCHAR2, P_MGR2 number, p_loc2 number) is  begin    INSERT into departments (DEPARTMENT_ID,DEPA rtment_name,manager_id,location_id) VALUES (departments_seq. NEXTVAL,P_NAME1,P_MGR1,P_LOC1);    Dbms_output. Put_Line (' Add Dept: ' | | p_name1);      insert into departments (department_id,department_name,manager_id,location_id) VALUES ( Departments_seq. Nextval,p_name2,p_mgr2, P_LOC2);   dbms_output. Put_Line (' Add Dept: ' | | p_name2);    END;  /           create OR REPLACE PROCEDURE create_more_departments  is   BEGIN    add_more_departments (' Media ', 100,1800, ' Editing ', 99,1800);  exception         When OTHERS then           dbms_output. Put_Line (' Errors have happend ');    END;  /  begin  create_more_departments;  END;     execution Results:sql> begin  2  create_more_departments;  3  END;  4  /add Dept : Mediaerrors The Happend pl/sql process has completed successfully. Search Result: department_id department_name-------------------------------------------          230 IT helpdesk          Government sales          Retail sales&nbsp ;         260 recruiting      &NBSP   payroll          media  this time the exception handling is on the outermost: the outermost of the call: The execution result is as follows: The first statement that executed successfully was inserted and the second is captured.

PL/SQL exception handling methods