PL/SQL - Functions

A function is like a procedure and having a PL/SQL block.

• Like a procedure, it is a stored block.

• The main difference between a function and a procedure is that a function always returns a
value to the calling block.

A function is characterized as follows:

 A function can be passed zero or more parameters.

 A function must have an explicit RETURN statement in the executable section to
return a value.
 The datatype of the return value must be declared in the function’s header.
 A function cannot be executed a stand-alone program.

 The use of OUT and IN OUT parameter types in functions is rare—and considered to
be a bad practice.

Creating a Function
A standalone function is created using the CREATE FUNCTION statement. The
simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as
follows −

CREATE [OR REPLACE] FUNCTION function_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];

Where,
 function-name specifies the name of the function.
 [OR REPLACE] option allows the modification of an existing function.
 The optional parameter list contains name, mode and types of the parameters. IN
represents the value that will be passed from outside and OUT represents the
parameter that will be used to return a value outside of the function.
 The function must contain a return statement.
 The RETURN clause specifies the data type you are going to return from the function.
 function-body contains the executable part.
  The AS keyword is used instead of the IS keyword for creating a standalone function.

Example
The following example illustrates how to create and call a standalone function. This
function returns the total number of CUSTOMERS in the customers table.

CREATE OR REPLACE FUNCTION totalCustomers

RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM customers;

RETURN total;
END;
/

When the above code is executed using the SQL prompt, it will produce the following
result −
Function created.

Calling a Function
While creating a function, you give a definition of what the function has to do. To use
a function, you will have to call that function to perform the defined task. When a
program calls a function, the program control is transferred to the called function.
A called function performs the defined task and when its return statement is executed
or when the last end statement is reached, it returns the program control back to the
main program.
To call a function, you simply need to pass the required parameters along with the
function name and if the function returns a value, then you can store the returned
value. Following program calls the function totalCustomers from an anonymous
block −

DECLARE
c number(2);
BEGIN
c := totalCustomers();
dbms_output.put_line('Total no. of Customers: ' || c);
END;
/
When the above code is executed at the SQL prompt, it produces the following result
−
Total no. of Customers: 6

PL/SQL procedure successfully completed.

Example
The following example demonstrates Declaring, Defining, and Invoking a Simple
PL/SQL Function that computes and returns the maximum of two values.
DECLARE
a number;
b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:= 23;
b:= 45;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
/
When the above code is executed at the SQL prompt, it produces the following result
−
Maximum of (23,45): 45

PL/SQL procedure successfully completed.

DROP Function in PL/SQL

To drop a function in PL/SQL, DROP function statement is used.
Syntax
 DROP Function <function_name>;
Example
DROP Function func1;

TRIGGER
Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers
are, in fact, written to be executed in response to any of the following events :

 A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)

 A database definition (DDL) statement (CREATE, ALTER, or DROP).

 A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers can be defined on the table, view, schema, or database with which the event is associated.

Benefits of Triggers

Triggers can be written for the following purposes −

 Generating some derived column values automatically

 Enforcing referential integrity

 Event logging and storing information on table access

 Synchronous replication of tables

 Imposing security authorizations

 Preventing invalid transactions

Creating 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 NEW AS n]

[FOR EACH ROW]

WHEN (condition)

DECLARE

Declaration-statements

BEGIN

Executable-statements

EXCEPTION

Exception-handling-statements

END;

Where,

 CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger with
the trigger_name.
  {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The
INSTEAD OF clause is used for creating trigger on a view.

 {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.

 [OF col_name] − This specifies the column name that will be updated.

 [ON table_name] − This specifies the name of the table associated with the trigger.

 [REFERENCING OLD AS NEW AS n] − This allows you to refer new and old values for various
DML statements, such as UPDATE

 [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed for each
row being affected. Otherwise the trigger will execute just once when the SQL statement is executed,
which is called a table level trigger.

 WHEN (condition) − This provides a condition for rows for which the trigger would fire. This clause
is valid only for row-level triggers.

Example:

To start with, we will be using the CUSTOMERS table we had created and used in the previous chapters −

Select * from customers;

ID NAME AGE ADDRESS SALARY

1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00

The following program creates a row-level trigger for the customers table that would fire for INSERT or
UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the
salary difference between the old values and new values −

CREATE OR REPLACE TRIGGER display_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON customers

FOR EACH ROW

WHEN (NEW.ID > 0)

DECLARE

sal_diff number;

BEGIN

sal_diff := :NEW.salary - :OLD.salary;

dbms_output.put_line('Old salary: ' || :OLD.salary);

dbms_output.put_line('New salary: ' || :NEW.salary);

 dbms_output.put_line('Salary difference: ' || sal_diff);

END;

When the above code is executed at the SQL prompt, it produces the following result −

Trigger created.

The following points need to be considered here −

 OLD and NEW references are not available for table-level triggers, rather you can use them for
record-level triggers.

 If you want to query the table in the same trigger, then you should use the AFTER keyword, because
triggers can query the table or change it again only after the initial changes are applied and the table
is back in a consistent state.

 The above trigger has been written in such a way that it will fire before any DELETE or INSERT or
UPDATE operation on the table, but you can write your trigger on a single or multiple operations,
for example BEFORE DELETE, which will fire whenever a record will be deleted using the
DELETE operation on the table.

Triggering a Trigger

Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT statement, which
will create a new record in the table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

When a record is created in the CUSTOMERS table, the above create trigger, display_salary_changes will
be fired and it will display the following result −

Old salary:

New salary: 7500

Salary difference:

Because this is a new record, old salary is not available and the above result comes as null. Let us now
perform one more DML operation on the CUSTOMERS table. The UPDATE statement will update an
existing record in the table −

UPDATE customers

SET salary = salary + 500

WHERE id = 2;

When a record is updated in the CUSTOMERS table, the above create trigger, display_salary_changes will
be fired and it will display the following result −

Old salary: 1500

New salary: 2000

Salary difference: 500

PL/SQL: Types of Triggers

The above diagram clearly indicated that Triggers can be classified into three categories:

1. Level Triggers

2. Event Triggers

3. Timing Triggers

Which are further divided into different parts.

Level Triggers

There are 2 different types of level triggers, they are:

1. ROW LEVEL TRIGGERS

 It fires for every record that got affected with the execution of DML statements like INSERT,
UPDATE, DELETE etc.

 It always use a FOR EACH ROW clause in a triggering statement.

2. STATEMENT LEVEL TRIGGERS

 It fires once for each statement that is executed.

Event Triggers

There are 3 different types of event triggers, they are:

1. DDL EVENT TRIGGER

 It fires with the execution of every DDL statement (CREATE, ALTER, DROP,
TRUNCATE).

2. DML EVENT TRIGGER

 It fires with the execution of every DML statement (INSERT, UPDATE, DELETE).

3. DATABASE EVENT TRIGGER

 It fires with the execution of every database operation which can be LOGON, LOGOFF,
SHUTDOWN, SERVERERROR etc.

Timing Triggers

There are 2 different types of timing triggers, they are:

  BEFORE TRIGGER

 It fires before executing DML statement.

 Triggering statement may or may not executed depending upon the before condition block.

 AFTER TRIGGER

 It fires after executing DML statement.

INSERT/UPDATE/DELETE Trigger
This statement specifies that Oracle will fire this trigger BEFORE the INSERT/UPDATE or DELETE
operation is executed.

Syntax

1. CREATE [ OR REPLACE ] TRIGGER trigger_name

2. BEFORE INSERT or UPDATE or DELETE

3. ON table_name

4. [ FOR EACH ROW ]

5. DECLARE

6. -- variable declarations

7. BEGIN

8. -- trigger code

9. EXCEPTION

10. WHEN ...

11. -- exception handling

12. END;

Where,

OR REPLACE: It is an optional parameter. It is used to re-create the trigger if it already exists. It facilitates
you to change the trigger definition without using a DROP TRIGGER statement.

trigger_name: It specifies the name of the trigger that you want to create.

BEFORE INSERT or UPDATE or DELETE: It specifies that the trigger will be fired before the INSERT
or UPDATE or DELETE operation is executed.

table_name: It specifies the name of the table on which trigger operation is being performed.

Limitations

o BEFORE trigger cannot be created on a view.

 o You cannot update the OLD values.

Oracle BEFORE Trigger Example

Consider, you have a "suppliers" table with the following parameters.

1. CREATE TABLE "SUPPLIERS"

2. ( "SUPPLIER_ID" NUMBER,

3. "SUPPLIER_NAME" VARCHAR2(4000),

4. "SUPPLIER_ADDRESS" VARCHAR2(4000)

5. )

6. /

You can use the following CREATE TRIGGER query to create a BEFORE INSERT or UPDATE or
DELETE Trigger:

1. CREATE OR REPLACE TRIGGER "SUPPLIERS_T1"

2. BEFORE

3. insert or update or delete on "SUPPLIERS"

4. for each row

5. begin

6. when the person performs insert/update/delete operations into the table.

7. end;

8. /

9. ALTER TRIGGER "SUPPLIERS_T1" ENABLE

10. /

Here the trigger name is "SUPPLIERS_T1" and it is fired BEFORE the insert or update or delete operation
is executed on the table "suppliers".

Oracle After INSERT/UPDATE/DELETE Trigger

This statement specifies that Oracle will fire this trigger AFTER the INSERT/UPDATE or DELETE
operation is executed.

Syntax
 1. CREATE [ OR REPLACE ] TRIGGER trigger_name

2. AFTER INSERT or UPDATE or DELETE

3. ON table_name

4. [ FOR EACH ROW ]

5. DECLARE

6. -- variable declarations

7. BEGIN

8. -- trigger code

9. EXCEPTION

10. WHEN ...

11. -- exception handling

12. END;

Where,

OR REPLACE: It is an optional parameter. It is used to re-create the trigger if it already exists. It facilitates
you to change the trigger definition without using a DROP TRIGGER statement.

trigger_name: It specifies the name of the trigger that you want to create.

AFTER INSERT or UPDATE or DELETE: It specifies that the trigger will be fired after the INSERT or
UPDATE or DELETE operation is executed.

table_name: It specifies the name of the table on which trigger operation is being performed.

Limitations

Oracle AFTER Trigger Example

Consider, you have a "suppliers" table with the following parameters.

1. CREATE TABLE "SUPPLIERS"

2. ( "SUPPLIER_ID" NUMBER,

3. "SUPPLIER_NAME" VARCHAR2(4000),

4. "SUPPLIER_ADDRESS" VARCHAR2(4000)

5. )

6. /

You can use the following CREATE TRIGGER query to create a AFTER INSERT or UPDATE or
DELETE Trigger:

1. CREATE OR REPLACE TRIGGER "SUPPLIERS_T2"

 2. AFTER

3. insert or update or delete on "SUPPLIERS"

4. for each row

5. begin

6. when the person performs insert/update/delete operations into the table.

7. end;

8. /

9. ALTER TRIGGER "SUPPLIERS_T2" ENABLE

10. /

Here the trigger name is "SUPPLIERS_T2" and it is fired AFTER the insert or update or delete operation is
executed on the table "suppliers".

Oracle DISABLE Trigger

The ALTER TRIGGER statement is used to disable a trigger.

Syntax

1. ALTER TRIGGER trigger_name DISABLE;

Parameters

trigger_name: It specifies the name of the trigger that you want to disable.

Oracle DISABLE Trigger Example

1. ALTER TRIGGER SUPPLIERS_T2 DISABLE;

This example will disable the trigger called "SUPPLIERS_T2" from the table "SUPPLIERS".

Oracle DISABLE ALL Triggers Example

If there is more than one trigger in a table and you want to disable all the triggers from the database then you
can do it by ALTER TABLE statement.

Syntax

1. ALTER TABLE table_name DISABLE ALL TRIGGERS;

Example
 1. ALTER TABLE SUPPLIERS DISABLE ALL TRIGGERS;

This example will disable all triggers from the table "suppliers".

Oracle ENABLE Trigger

The ALTER TRIGGER statement is used to enable a trigger.

Syntax

1. ALTER TRIGGER trigger_name ENABLE;

Parameters

trigger_name: It specifies the name of the trigger that you want to enable.

Oracle ENABLE Trigger Example

1. ALTER TRIGGER SUPPLIERS_T1 ENABLE;

This example will enable the trigger named "SUPPLIERS_T1" in the "SUPPLIERS" table.

Oracle ENABLE ALL Triggers Example

Syntax

1. ALTER TABLE table_name ENABLE ALL TRIGGERS;

Example

1. ALTER TABLE SUPPLIERS ENABLE ALL TRIGGERS;

This example will enable all the triggers on the table name "SUPPLIERS".