What is a Trigger?

What is a Trigger?

A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

Syntax of Triggers

The Syntax for creating a trigger is:

 CREATE [OR REPLACE ] TRIGGER trigger_name

 {BEFORE | AFTER | INSTEAD OF }

 {INSERT [OR] | UPDATE [OR] | DELETE}

 [OF col_name]

 ON table_name

 [REFERENCING OLD AS o NEW AS n]

 [FOR EACH ROW]

 WHEN (condition) 

 BEGIN

   --- sql statements 

 END; 

Exception Handling

Exception Handling

In this section we will discuss about the following,
1) What is Exception Handling.
2) Structure of Exception Handling.
3) Types of Exception Handling.

1) What is Exception Handling?

PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block known as exception Handling. Using Exception Handling we can test the code and avoid it from exiting abruptly. When an exception occurs a messages which explains its cause is recieved.
PL/SQL Exception message consists of three parts.
1) Type of Exception
2) An Error Code
3) A message
By Handling the exceptions we can ensure a PL/SQL block does not exit abruptly.

2) Structure of Exception Handling.

The General Syntax for coding the exception section

 DECLARE

   Declaration section

 BEGIN

   Exception section

 EXCEPTION

 WHEN ex_name1 THEN

    -Error handling statements

 WHEN ex_name2 THEN

    -Error handling statements

 WHEN Others THEN

   -Error handling statements

END;

General PL/SQL statments can be used in the Exception Block.

When an exception is raised, Oracle searches for an appropriate exception handler in the exception section. For example in the above example, if the error raised is 'ex_name1 ', then the error is handled according to the statements under it. Since, it is not possible to determine all the possible runtime errors during testing fo the code, the 'WHEN Others' exception is used to manage the exceptions that are not explicitly handled. Only one exception can be raised in a Block and the control does not return to the Execution Section after the error is handled.

If there are nested PL/SQL blocks like this.

 DELCARE

   Declaration section

 BEGIN

    DECLARE

      Declaration section

    BEGIN

      Execution section

    EXCEPTION

      Exception section

    END;

 EXCEPTION

   Exception section

 END;

In the above case, if the exception is raised in the inner block it should be handled in the exception block of the inner PL/SQL block else the control moves to the Exception block of the next upper PL/SQL Block. If none of the blocks handle the exception the program ends abruptly with an error.

PL/SQL Functions

PL/SQL Functions

What is a Function in PL/SQL?

A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

The General Syntax to create a function is:

CREATE [OR REPLACE] FUNCTION function_name [parameters]

RETURN return_datatype; 

IS 

Declaration_section 

BEGIN 

Execution_section

Return return_variable; 

EXCEPTION 

exception section 

Return return_variable; 

END; 

What are Cursors?

What are Cursors?

A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

There are two types of cursors in PL/SQL:

Implicit cursors:

These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.

Explicit cursors:

They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.