PL/SQL exceptions are raised using the RAISE command. This command is used when exceptions are defined by programmer and not implicit exceptions.

Example:

Declare and raising an exception:

DECLARE

short_of_attendance EXCEPTION;

min_attendance NUMBER(4);

BEGIN

...

IF min_attendance < 10 THEN

RAISE short_of_attendance;

END IF;

EXCEPTION

WHEN short_of_attendance THEN

-- handle the error

END;

1)Divide by zero        – This is raised when any number is attempted to divide by zero.

2)TOO MANY ROWS   - A SELECT INTO statement returns more than one row.

3)CASE_NOT_FOUND - No choice in the WHEN clause of a case statement is selected.

4)LOGIN_DENIED      - An attempt to login with an invalid username or password.

5)PROGRAM_ERROR  - An internal PL/SQL problem.

- %FOUND

 This is a Boolean variable which evaluates to TRUE if the last row is successfully fetched.

- %NOTFOUND

 This is a Boolean variable which evaluates to TRUE if the last row is not successfully fetched. This means there are no more rows to fetch.

- %ROWCOUNT

 Returns the number of rows fetched by the cursor.

- %ISOPEN

If the cursor is open, it evaluates to TRUE else FALSE.

Cursors help you manipulate the information retrieved by select statements. This can be done by assigning a name to the cursor.

Example:

CURSOR emp_cur 

IS 

SELECT emp_number from employee_tbl where employee_name = name_in;

Types of Cursors:

Implicit cursors- These cursors are not declared by the programmer. They are issued when the SQL statement is executed. The open, close and fetching is done by itself.

Example:

UPDATE employee SET salary = salary * 2.1;

Here, an implicit cursor is issued to identify the set of rows in the table which would be affected by the update.

Explicit cursors- These cursors are defined by programmer. They are used in queries that return multiple rows.

Example:

CURSOR emp_cur 

IS 

SELECT emp_number from employee_tbl where employee_name = name_in;

A record data type represents a data type for that row in a database table. It lets u define your own records and not your own fields.

A collection just like an array is an ordered group of elements of the same type. Each elements position is determined by a unique subscript.

Index by tables:- They are similar to hash arrays that allows to search for subscript values using arbitrary numbers and strings.

They can be declared as:

TYPE type_name IS TABLE OF element_type [NOT NULL]

INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)];

INDEX BY key_type;

Example:

TYPE studenttyp IS TABLE OF emp%ROWTYPE

INDEX BY BINARY_INTEGER;

stud_tab studenttyp;

Nested tables:- they hold random number of elements and use sequential numbers as sub scripts.

They can be declared as:

TYPE type_name IS TABLE OF element_type [NOT NULL];

Example: TYPE employee_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);

Varrays: Holds a fixed number of elements which can be changed in run time. 

They can be declared as:

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];

Example: TYPE Calendar IS VARRAY(366) OF DATE;

%type is used to declare a field of a table while %rowtype is used to declare a record with the same type as specified in that table, view or cursor.

Example of %type:

DECLARE

         v_EmployeeName emp.ename%TYPE

Example of %rowtype

DECLARE

          v_empployee emp%ROWTYPE;

EXECUTE IMMEDIATE command can be used to call DDL statements. 

Example:

BEGIN

EXECUTE IMMEDIATE  'TRUNCATE TABLE employee';

END;