PL/SQL basics-Exception Handling

PL/SQL basics-Exception Handling

Exception Handling
I. Exception type
There are two types of Oracle exceptions: system exceptions and custom exceptions.
System exceptions are classified into pre-defined exceptions and non-pre-defined exceptions.
1. pre-defined exception
ORACLE defines their error numbers and names. Common predefined exceptions are used to handle common Oracle errors.

No data found when NO_DATA_FOUND SELECT......
DUL_VAL_ON_INDEX tries to store duplicate values on a column with uniqueness constraints.
CURSOR_ALREADY_OPEN tries to open an opened cursor
When TOO_MANY_ROWS SELECT... INTO..., the query result is multi-value.
ZERO_DIVIDE zero Division
2. Non-predefined exceptions
ORACLE defines an error number for it, but no exception name is defined. When we use it, we first declare an exception name,
Associate the Exception name with the error number through The PRAGMA EXCEPTION_INIT pseudo process.

3. custom exception
Some rules and restrictions developed by programmers from our business perspective.
Ii. Exception Handling
In PL/SQL, exception handling is performed in one step:
Definition exception
Throw an exception
Capture and handle exceptions

A. Definition exception

Exception_name EXCEPTION;

B. Throw an exception

RAISE prediction_name

C. Capture and handle exceptions

EXCEPTION
WHEN e_name1 [OR e_name2...] THEN
Statements;
WHEN e_name3 [OR e_name4...] THEN
Statements;
......
WHEN OTHERS THEN
Statements;
END;

-- Use a predefined exception
-- Check the product Inventory Based on the product ID entered in the log

DECLARE
V_ID ES_PRODUCT.ID % TYPE: = & V_ID; -- enter the product ID.
V_STOCKCOUNT ES_PRODUCT.STOCKCOUNT % TYPE; --- inventory
BEGIN
Select stockcount into V_STOCKCOUNT FROM ES_PRODUCT where id = V_ID;
DBMS_OUTPUT.PUT_LINE ('inventory: '| V_STOCKCOUNT );
-- Determine whether the inventory is normal
IF V_STOCKCOUNT> 0 THEN
-- Update the inventory and delete one at a time
UPDATE ES_PRODUCT
Set stockcount = STOCKCOUNT-1
-- Update specified, otherwise all updates will be made.
Where id = V_ID;
-- Propose data operations
COMMIT;
DBMS_OUTPUT.PUT_LINE ('inventory updated successfully ');
ELSIF V_STOCKCOUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE ('inventory is 0, no stocks ');
ELSE
DBMS_OUTPUT.PUT_LINE ('inventory exception ');
End if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('This item does not exist! ');
ROLLBACK;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE ('This product has multiple! ');
ROLLBACK;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('other error occurred! ');
ROLLBACK;
END;

 

-- Custom exception
-- Delete an order

 

SELECT * FROM ES_ORDER;
DECLARE
E_NO_RESULT EXCEPTION;
V_ID ES_ORDER.ID % TYPE: = & ID;
V_EXCEPTION1 CONSTANT VARCHAR2 (50): = 'data deletion failed! ';
V_EXCEPTION2 CONSTANT VARCHAR2 (50): = 'An error occurred! ';
BEGIN
Delete from ES_ORDER where id = V_ID;
IF (SQL % NOTFOUND) THEN
-- Deletion not executed
RAISE E_NO_RESULT;
End if;
EXCEPTION
WHEN E_NO_RESULT THEN
DBMS_OUTPUT.PUT_LINE (V_EXCEPTION1 );
ROLLBACK;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (V_EXCEPTION2 );
ROLLBACK;
END;

-------------------------------------- Split line --------------------------------------

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)

PL/SQL Developer Practical Skills

-------------------------------------- Split line --------------------------------------