(Notes) (Subject Notes) Informatics Practices (Tables)

Disclaimer: This website is not at associated with CBSE, For official website of CBSE visit - www.cbse.nic.in

(Subject Notes) Informatics Practices

Tables

 

PL/SQl TABLEs combine characteristics of SQL tables and C/Pascal arrays.
Like SQL tables:

  • consist of records (must have a numeric primary key)
  • can grow/shrink as elements are added/removed

Exceptions

An exception is an unusual/erroneous condition encountered during execution:

  • system error (e.g. "out of memory")
  • error caused by user program
  • warning issued by application

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:

  • the exception handler in this block
  • the exception handler at the next enclosing block
  • ... and so on out to the system level

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.

 

 Go To Page :   1,   2,   3,   456   >>

 

Disclaimer: This website is not at associated with CBSE, For official website of CBSE visit - www.cbse.nic.in

NEW!  CBSE Old Papers Books : Class-X, Class-XII