DB Projects Help >> Overview of PL SQL Error Handling
In PL/SQL, an error or warning state is called an exception. Conditions can be internal identified (by the run-time system) or person identified. Illustrations of internal identified exceptions contain split by zero and out of storage. Some common inner exceptions have definite brands, such as ZERO_DIVIDE and STORAGE_ERROR. The other inner exceptions can be given brands.
You can explain exceptions of your own in the declarative piece of any PL/SQL prevent, subprogram, or program. For example, you might explain different called insufficient funds to banner over drafted records. As opposed to inner exceptions, user-defined exceptions must be given brands.
When one takes place, a different is increased. That is, standard performance prevents and management transactions to the exception-handling piece of your PL/SQL prevent or subprogram. Internal exceptions are elevated unconditionally (automatically) by the run-time program. User-defined exceptions must be increased clearly by RAISE phrases, which can also raise definite exceptions.
To manage increased exceptions, you produce distinct workouts known as different handlers. After a different owner works, the present prevent prevents undertaking and the attaching prevent continues with the next report. If there is no attaching prevent, management dividends to the variety atmosphere.
In the example below, you evaluate and store a price-to-earnings rate for a business with ticker mark XYZ. If the business has zero income, the definite different ZERO_DIVIDE is increased. This prevents standard performance of the prevent and transactions management to the different handlers. The suggested OTHERS owner attracts all exceptions that the prevent does not name precisely.
DECLARE
pe_ratio NUMBER (3, 1);
BEGIN
SELECT price / earnings INTO pe_ratio FROM stocks
WHERE symbol = 'XYZ'; -- might cause division-by-zero error
INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
COMMIT;
EXCEPTION -- exception handlers begin
WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error
INSERT INTO stats (symbol, ratio) VALUES ('XYZ', NULL);
COMMIT;
...
WHEN OTHERS THEN -- handles all other errors
ROLLBACK;
END; -- exception handlers and block end here
Advantages of PL/SQL Exceptions
Using conditions for problem managing has several rewards. Without different managing, every time you problem a control, you must test for performance errors:
BEGIN
SELECT...
-- Check for 'no data found' error
SELECT...
-- Check for 'no data found' error
SELECT...
-- Check for 'no data found' error
Problem control is not clearly divided from standard processing; nor is it effective. If you forget to value a check, whole body goes hidden and is likely to cause other, somewhat not related problems.
With conditions, you can manage problems quickly without the need to value many assessments, as follows:
BEGIN
SELECT...
SELECT...
SELECT...
...
EXCEPTION
WHEN NO_DATA_FOUND THEN -- catches all 'no data found' errors
Conditions develop legibility by allowing you to identify error-handling workouts. The major criteria are not hidden by problem restoration methods. Conditions also develop stability. You need not fear about looking at for one at every point it might appear. Just add a different owner to your PL/SQL prevent. If the different is ever increased in that prevent (or any sub-block), you can be sure it will be managed.
DBMS Assignment Help - Homework Help at Expertsmind.com
Experts at Expertsmind.com offers DBMS homework help, DBMS assignment help and projects help with best online support from qualified and experienced DBMS tutors. We provide optimized database problems and solutions, university assignments and best online tutor for database management course help. Our expert tutors have proven their excellence and we have done many projects in database management system. Find your database question's answers here at Expertsmind.com by just one step ahead by submitting your homework.