PL/SQl TABLEs combine characteristics of SQL tables and C/Pascal arrays.
Like SQL tables:
Exceptions
An exception is an unusual/erroneous condition encountered during execution:
PL/SQL's exception handling allows these to be handled "cleanly" in a central place.
Syntax for exception handlers:
BEGIN
... Statements ...
EXCEPTION
WHEN ExcepName1 THEN Statements1;
WHEN ExcepName2 THEN Statements2;
...
END;
If an error occurs in Statements, control is transferred to:
Example: Computing stock-market price/earnings ratio
DECLARE
pe_ratio NUMBER(5,1);
BEGIN
SELECT price/earnings INTO pe_ratio
FROM Stocks WHERE company_name = 'Acme';
INSERT INTO Statistics(co_name, ratio)
VALUES ('Acme', pe_ratio);
COMMIT;
EXCEPTION
WHEN ZERO_DIVIDE THEN -- divide-by-zero errors
INSERT INTO Statistics(co_name, ratio)
VALUES ('Acme', 0.0);
-- other exception handlers
END;
Predefined Exceptions
PL/SQL provides exceptions for low-level/system errors:
INVALID_CURSOR | Attempt to use non-open cursor |
INVALID_NUMBER | Non-numeric-looking string used in context where number needed |
NO_DATA_FOUND | SELECT..INTO returns no results |
NOT_LOGGED_ON | Attempted SQL operation without being connected to Oracle |
STORAGE_ERROR | PL/SQL store runs out or is corrupted |
VALUE_ERROR | Arithmetic conversion, truncation, size-constraint error |
User-defined Exceptions
Exceptions are defined by name; used by RAISE.
Example:
DECLARE
outOfStock EXCEPTION;
qtyOnHand INTEGER;
BEGIN
...
IF qtyOnHand < 1 THEN
RAISE outOfStock;
END IF;
...
EXCEPTION
WHEN outOfStock THEN
-- handle the problem
END;
User-defined exceptions are local to a block and its sub-blocks.