PL/SQL – Program Structure

PL/SQL is a block-structured language and programs are divided and written in logical blocks of code. The  basic parts of PL/ SQL blocks are

  • Declarative block ( optional)
  • Executable block  (Mandatory )
  • Exception Handling block (optional) 

Declarative block

It is used to define user defined types, variable which can be used in the executable part for further manipulations.

Executable block

All procedural statement are included between BEGIN and END statements. It consists of the executable PL/SQL statements of the program. It should have at least one executable line of code, which may be NULL command.

Exception Handling block

This block starts with keyword EXCEPTION. Errors that occur during execution will handle in this block.

Notes : 

  • A PL/SQL program is a logical block , which contain any number of nested sub blocks.
  • Block can be nested in the executable and exception handling parts of a PL/SQL block, or a sub program.
  • A PL/ SQL in marked with either a DECLARE or BEGIN keywords and ends with the keyword  END.
  • Only BEGIN and END keywords are mandatory.
  • A semicolon (;) has to be placed after the END keyword.
BASIC SYNATX :
DECLARE 
  variables;
  cursor declerations;
  user_defined exceptions; 
BEGIN 
  <executable command(s)>
EXCEPTION 
   Action to perform when error occured
END;

PL/SQL – Literals

PL/SQL Literals.

A Literal is an explicit numeric, string or Boolean values not represented by an identifiers. For examples, 999,  TRUE, NULL, ‘learndba’ are literal types number, Boolean or string. PL/SQL literals are case sensitive. PL/SQL supports following literals.

S.No Literal Type & Example
1 Numeric Literals

050 78 -14 0 +32767

6.6667 0.0 -12.0 3.14159 +7800.00

6E5 1.0E-8 3.14159e0 -1E38 -9.5e-3

2 Character Literals

‘A’ ‘%’ ‘9’ ‘ ‘ ‘z’ ‘(‘

3 String Literals

‘Hello, world!’

‘Tutorials Point’

’19-NOV-12′

4 BOOLEAN Literals

TRUE, FALSE, and NULL.

5 Date and Time Literals

DATE ‘1978-12-25’;

TIMESTAMP ‘2012-10-29 12:01:01’;

To embed single quotes with in a string literal, place two single quotes to each other as shown in below program.

set SERVEROUTPUT ON;
DECLARE
 message varchar2(20) := 'It''s time to learn';
BEGIN

dbms_output.put_line(message); 
END; 
/
It's time to learn

PL/SQL procedure successfully completed.

PL/SQL – Operators

An operator is reserved word that manipulates individual data items and returns a result. The data items are called operands or arguments.

Operators are represented by special characters. For example, the Addition operator is represented by plus (+) and subtraction operator represented by minus (-) etc…

There are two general classes of operators:

  • Unary operators such as the negation operator (-) operate on one operand.
  • Binary operators such as the division operator (/) operate on two operands.

PL/SQL has no ternary operators.

PL/SQL language is rich in built-in operators and provides the following types of operators.

  • Arithmetic operators
  • Relational operators
  • Comparison operators
  • Logical operators
  • String operators

Arithmetic operators

Arithmetic operators perform mathematical operations on numeric operands involved. The + and – operators can also be used in date arithmetic.

Operator Description Example
+ Adds two operands 2 + 3 will give 5
Subtracts second operand from the first 2 – 3 will give  -1
* Multiplies both operands 2 * 3 will give 6
/ Divides numerator by de-numerator 2 / 3 will give 0.666
** Exponentiation operator, raises one operand to the power of other 2 ** 3 will give 8

Relational operators

The relational operators allow you to compare arbitrarily complex expressions. The following list gives the meaning of each operator.

Operator Meaning

=

equal to

!=~=^=

not equal to

<

less than

>

greater than

<=

less than or equal to

>=

greater than or equal to

Comparison Operators

Comparison operators compare one expression to another. The result is always true, false, or null.

Operator Description Example
LIKE The LIKE operator to compare a character, string, or CLOB value to a pattern. Case is significant. LIKE returns the Boolean value TRUE if the patterns match or FALSE if they do not match.

The patterns matched by LIKE can include two special-purpose characters called wildcards. An underscore (_) matches exactly one character; a percent sign (%) matches zero or more characters. For example, if the value of ename is 'JOHNSON', the following expression is true:

ename LIKE ‘J%SON’

BETWEEN The BETWEEN operator tests whether a value lies in a specified range. It means “greater than or equal to low value and less than or equal to high value. 41 BETWEEN 30 AND 45
IN The IN operator tests set membership. It means “equal to any member of.” The set can contain nulls, but they are ignored.  DELETE FROM emp WHERE ename IN (NULL, ‘KING’, ‘FORD’);
IS NULL The IS NULL operator returns the Boolean value TRUE if its operand is null or FALSE if it is not null. Comparisons involving nulls always yield NULL. IF variable IS NULL THEN …

Logical operators

Logical operators manipulate the results of conditions. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE, FALSE, or NULL.

AND and OR are binary operators; NOT is a unary operator.

Operator Description Examples
AND Called the logical AND operator. If both the operands are true then condition becomes true. (A and B) is false.
OR Called the logical OR Operator. If any of the two operands is true then condition becomes true. (A or B) is true.
NOT Called the logical NOT Operator. Used to reverse the logical state of its operand. If a condition is true then Logical NOT operator will make it false. not (A and B) is true.

Operator Precedence

The operations within an expression are done in a particular order depending on their precedence (priority). Default order of operations from first to last (top to bottom) shown below.

Operator Operation

**

exponentiation

+-

identity, negation

*/

multiplication, division

+-||

addition, subtraction, concatenation

=<><=>=!=~=^=IS NULLLIKEBETWEENIN

comparison

NOT

logical negation

AND

conjunction

OR

inclusion

Operators with higher precedence are applied first. In the example below, both expressions yield 8 because division has a higher precedence than addition. Operators with the same precedence are applied in no particular order.

5 + 12 / 4  — > (12 / 4) + 5 ( division has high precedence than addition, so 12 gets divide by 4 and the result will add to 4).

You can use parentheses to control the order of evaluation like (5 + 12) / 4.

Concatenation Operator

Double vertical bars (||) serve as the concatenation operator, which appends one string (CHARVARCHAR2CLOB, or the equivalent Unicode-enabled type) to another. For example, the expression.

‘suit’ || ‘case’

returns the following value:

'suitcase'

If both operands have datatype CHAR, the concatenation operator returns a CHAR value. If either operand is a CLOB value, the operator returns a temporary CLOB. Otherwise, it returns a VARCHAR2 value.

The concatenation operator ignores null operands. For example, the expression

‘apple’ || NULL || NULL || ‘sauce’

returns the following value:

'applesauce'

The String operators will be discussed in a later chapter .

PL/SQL – Packages

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Package is compiled and stored in database that can be used later.

PL/SQL package basically have two components.

  • Package Specification
  • Package Body

Package Specification :

Specification is the interface to the package. It consists of a declaration of all the variables, constants, cursors, types, procedures, functions, and exceptions that can be referenced from outside the package.

The elements which are all declared declared in the specification are called public elements. Any subprogram not in the package specification but coded in the package body is called a private element.

Syntax :

CREATE [OR REPLACE] PACKAGE < package name >
IS
< sub programs and element declaration>
END < package name >;

Example :

CREATE PACKAGE emp_sal AS 
 PROCEDURE find_sal(e_id emp.id%type); 
END emp_sal; 
/

Package Body

The body holds implementation details and private declarations, which are hidden from code outside the package (can be called only from inside the package).

It should contain implementation for all the subprograms/cursors that have been declared in the specification.

It can also have more subprograms or other elements that are not declared in specification. These are called private elements.

It is a dependable object, and it depends on package specification.

The state of the package body becomes ‘Invalid’ every time when the specification is compiled. Therefore, it needs to be recompiled each time after the compilation of specification.

Syntax :

CREATE [OR REPLACE] PACKAGE BODY < package name >
IS
< global_declaration part >
< private element definition >
< sub programs and element declaration>
< Package Initialization >

END < package name >;

Example :

CREATE OR REPLACE PACKAGE BODY emp_sal AS 
 
 PROCEDURE find_sal(e_id emp.id%TYPE) IS 
 e_sal emp.salary%TYPE; 
 BEGIN 
 SELECT salary INTO e_sal 
 FROM customers 
 WHERE id = e_id; 
 dbms_output.put_line('Salary: '|| e_sal); 
 END find_sal; 
END emp_sal; 
/

Referencing Package Contents

To reference the types, items, subprograms, and call specs declared within a package spec, use dot notation:

package_name.type_name
package_name.item_name
package_name.subprogram_name

Example :

DECLARE 
 id emp.id%type := &e_id; 
BEGIN 
 emp_sal.find_sal(id); 
END; 
/

Creating the emp_admin Package

-- create the audit table to track changes
CREATE TABLE emp_audit(date_of_action DATE, user_id VARCHAR2(20), 
 package_name VARCHAR2(30));

CREATE OR REPLACE PACKAGE emp_admin AS
-- Declare externally visible types, cursor, exception
 TYPE EmpRecTyp IS RECORD (emp_id NUMBER, sal NUMBER);
 CURSOR desc_salary RETURN EmpRecTyp;
 invalid_salary EXCEPTION;
-- Declare externally callable subprograms
 FUNCTION hire_employee (last_name VARCHAR2, first_name VARCHAR2, 
 email VARCHAR2, phone_number VARCHAR2, job_id VARCHAR2, salary NUMBER,
 commission_pct NUMBER, manager_id NUMBER, department_id NUMBER) 
 RETURN NUMBER;
 PROCEDURE fire_employee (emp_id NUMBER); -- overloaded subprogram
 PROCEDURE fire_employee (emp_email VARCHAR2); -- overloaded subprogram
 PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER);
 FUNCTION nth_highest_salary (n NUMBER) RETURN EmpRecTyp;
END emp_admin;
/
CREATE OR REPLACE PACKAGE BODY emp_admin AS
 number_hired NUMBER; -- visible only in this package
-- Fully define cursor specified in package
 CURSOR desc_salary RETURN EmpRecTyp IS
 SELECT employee_id, salary FROM employees ORDER BY salary DESC;
-- Fully define subprograms specified in package
 FUNCTION hire_employee (last_name VARCHAR2, first_name VARCHAR2, 
 email VARCHAR2, phone_number VARCHAR2, job_id VARCHAR2, salary NUMBER,
 commission_pct NUMBER, manager_id NUMBER, department_id NUMBER) 
 RETURN NUMBER IS
 new_emp_id NUMBER;
 BEGIN
 SELECT employees_seq.NEXTVAL INTO new_emp_id FROM dual;
 INSERT INTO employees VALUES (new_emp_id, last_name, first_name, email,
 phone_number, SYSDATE, job_id, salary, commission_pct, manager_id,
 department_id);
 number_hired := number_hired + 1;
 DBMS_OUTPUT.PUT_LINE('The number of employees hired is ' 
 || TO_CHAR(number_hired) ); 
 RETURN new_emp_id;
 END hire_employee;
 PROCEDURE fire_employee (emp_id NUMBER) IS
 BEGIN
 DELETE FROM employees WHERE employee_id = emp_id;
 END fire_employee;
 PROCEDURE fire_employee (emp_email VARCHAR2) IS
 BEGIN
 DELETE FROM employees WHERE email = emp_email;
 END fire_employee;
 -- Define local function, available only inside package
 FUNCTION sal_ok (jobid VARCHAR2, sal NUMBER) RETURN BOOLEAN IS
 min_sal NUMBER;
 max_sal NUMBER;
 BEGIN
 SELECT MIN(salary), MAX(salary) INTO min_sal, max_sal FROM employees
 WHERE job_id = jobid;
 RETURN (sal >= min_sal) AND (sal < = max_sal);
 END sal_ok;
 PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) IS
 sal NUMBER(8,2);
 jobid VARCHAR2(10);
 BEGIN
 SELECT job_id, salary INTO jobid, sal FROM employees
 WHERE employee_id = emp_id;
 IF sal_ok(jobid, sal + amount) THEN
 UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id;
 ELSE
 RAISE invalid_salary;
 END IF;
 EXCEPTION -- exception-handling part starts here
 WHEN invalid_salary THEN
 DBMS_OUTPUT.PUT_LINE('The salary is out of the specified range.');
 END raise_salary;
 FUNCTION nth_highest_salary (n NUMBER) RETURN EmpRecTyp IS
 emp_rec EmpRecTyp;
 BEGIN
 OPEN desc_salary;
 FOR i IN 1..n LOOP
 FETCH desc_salary INTO emp_rec;
 END LOOP;
 CLOSE desc_salary;
 RETURN emp_rec;
 END nth_highest_salary;
BEGIN -- initialization part starts here
 INSERT INTO emp_audit VALUES (SYSDATE, USER, 'EMP_ADMIN');
 number_hired := 0;
END emp_admin;
/
-- calling the package procedures
DECLARE
 new_emp_id NUMBER(6);
BEGIN
 new_emp_id := emp_admin.hire_employee('Belden', 'Enrique', 'EBELDEN',
 '555.111.2222', 'ST_CLERK', 2500, .1, 101, 110);
 DBMS_OUTPUT.PUT_LINE('The new employee id is ' || TO_CHAR(new_emp_id) );
 EMP_ADMIN.raise_salary(new_emp_id, 100);
 DBMS_OUTPUT.PUT_LINE('The 10th highest salary is '|| 
 TO_CHAR(emp_admin.nth_highest_salary(10).sal) || ', belonging to employee: ' 
 || TO_CHAR(emp_admin.nth_highest_salary(10).emp_id) );
 emp_admin.fire_employee(new_emp_id);
-- you could also delete the newly added employee as follows:
-- emp_admin.fire_employee('EBELDEN');
END;
/

PL/SQL Records

A PL/SQL record is a composite data structure that is a group of related data stored in fields. Each field in the PL/SQL record has its own name and data type.

A record is a group of related data items stored in fields, each with its own name and datatype. Records are composed of a group of fields, similar to the columns in a row. The %ROWTYPE attribute lets you declare a PL/SQL record that represents a row in a database table, without listing all the columns.

PL/SQL can handle the following types of records

  • Table-based
  • Cursor-based records
  • User-defined records

Table Based Records

The %ROWTYPE attribute enables a programmer to create table-based and cursorbased records.

Syntax :

DECLARE
 table_based_record table_name%ROWTYPE;

Example :

DECLARE
 r_emp employees%ROWTYPE;
 n_emp_id employees.employee_id%TYPE := 200;
BEGIN
 SELECT *
 INTO r_emp
 FROM employees
 WHERE employee_id = n_emp_id;
 dbms_output.put_line('ID: ' || r_emp.id); 
 dbms_output.put_line('Name: ' || r_emp.name); 
 dbms_output.put_line('Salary: ' || r_emp.salary);
END;
/

Cursor-Based Records

The following example illustrates the concept of cursor-based records.

DECLARE 
 CURSOR customer_cur is 
 SELECT id, name, address 
 FROM customers; 
 customer_rec customer_cur%rowtype; 
BEGIN 
 OPEN customer_cur; 
 LOOP 
 FETCH customer_cur into customer_rec; 
 EXIT WHEN customer_cur%notfound; 
 DBMS_OUTPUT.put_line(customer_rec.id || ' ' || customer_rec.name); 
 END LOOP; 
END; 
/

User-Defined Records

PL/SQL provides a user-defined record type that allows you to define the different record structures. These records consist of different fields.

Defining a Record

The record type is defined as

TYPE 
type_name IS RECORD 
 ( field_name1 datatype1 [NOT NULL] [:= DEFAULT EXPRESSION], 
 field_name2 datatype2 [NOT NULL] [:= DEFAULT EXPRESSION], 
 ... 
 field_nameN datatypeN [NOT NULL] [:= DEFAULT EXPRESSION); 
record-name type_name;

Example :

DECLARE 
TYPE books IS RECORD 
(title varchar(50), 
 author varchar(50), 
 subject varchar(100), 
 book_id number); 
book1 books; 
book2 books;

Accessing Fields

To access any field of a record, we use the dot (.) operator.

Example :

DECLARE 
 type books is record 
 (title varchar(50), 
 author varchar(50), 
 subject varchar(100), 
 book_id number); 
 book1 books; 
 book2 books; 
BEGIN 
 -- Book 1 specification 
 book1.title := 'C Programming'; 
 book1.author := 'Nuha Ali '; 
 book1.subject := 'C Programming Tutorial'; 
 book1.book_id := 6495407; 
 -- Book 2 specification 
 book2.title := 'Telecom Billing'; 
 book2.author := 'Zara Ali'; 
 book2.subject := 'Telecom Billing Tutorial'; 
 book2.book_id := 6495700; 
 
 -- Print book 1 record 
 dbms_output.put_line('Book 1 title : '|| book1.title); 
 dbms_output.put_line('Book 1 author : '|| book1.author); 
 dbms_output.put_line('Book 1 subject : '|| book1.subject); 
 dbms_output.put_line('Book 1 book_id : ' || book1.book_id); 
 
 -- Print book 2 record 
 dbms_output.put_line('Book 2 title : '|| book2.title); 
 dbms_output.put_line('Book 2 author : '|| book2.author); 
 dbms_output.put_line('Book 2 subject : '|| book2.subject); 
 dbms_output.put_line('Book 2 book_id : '|| book2.book_id); 
END; 
/

Records as Subprogram Parameters

You can pass a record as a subprogram parameter just as you pass any other variable. You can also access the record fields in the same way as you accessed.

Example :

DECLARE 
 type books is record 
 (title varchar(50), 
 author varchar(50), 
 subject varchar(100), 
 book_id number); 
 book1 books; 
 book2 books; 
PROCEDURE printbook (book books) IS 
BEGIN 
 dbms_output.put_line ('Book title : ' || book.title); 
 dbms_output.put_line('Book author : ' || book.author); 
 dbms_output.put_line( 'Book subject : ' || book.subject); 
 dbms_output.put_line( 'Book book_id : ' || book.book_id); 
END; 
 
BEGIN 
 -- Book 1 specification 
 book1.title := 'C Programming'; 
 book1.author := 'Nuha Ali '; 
 book1.subject := 'C Programming Tutorial'; 
 book1.book_id := 6495407;
 
 -- Book 2 specification 
 book2.title := 'Telecom Billing'; 
 book2.author := 'Zara Ali'; 
 book2.subject := 'Telecom Billing Tutorial'; 
 book2.book_id := 6495700; 
 
 -- Use procedure to print book info 
 printbook(book1); 
 printbook(book2); 
END; 
/

PL/SQL Collections

A collection is an ordered group of elements, all of the same data type and are called elements. Each element is addressed by a unique subscript that represents its position in the collection. You access each element by its unique subscript.

PL/SQL has three collection types,

  • Associative array (or index-by table)
  • Nested table
  • Variable-size array (varray)

Oracle documentation provides the following characteristics for each type of collections

Collection Type Number of Elements Subscript Type Dense or Sparse Where Created Can Be Object Type Attribute
Associative array (or index-by table) Unbounded String or integer Either Only in PL/SQL block No
Nested table Unbounded Integer Starts dense, can become sparse Either in PL/SQL block or at schema level Yes
Variable-size array (varray) Bounded Integer Always dense Either in PL/SQL block or at schema level Yes

Unbounded – there is no limit to the number of elements in the collection.

Dense means that the collection has no gaps between elements – every element between the first and last element is defined and has a value (which can be NULL).

We have already discussed varray in the chapter ‘PL/SQL – Arrays’.

Associative Arrays (Index-By Tables)

An associative array (also called an index-by table) is a set of key-value pairs. Each key is unique, and is used to locate the corresponding value. The key can be either an integer or a string.

Syntax :

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; 
 
table_name type_name;

Example :

DECLARE

-- Associative array indexed by string
 TYPE balance IS TABLE OF NUMBER INDEX BY VARCHAR2(20); 
-- Associative array variable
 bal_list balance;
 name VARCHAR2(10);
 
BEGIN 
 -- adding elements to the table 
 bal_list('AAA') := 2000; 
 bal_list('BBB') := 5000; 
 bal_list('CCC') := 10000; 
 bal_list('DDD') := 8000; 
 
 -- printing the table
 
 name := bal_list.FIRST; 
 WHILE name IS NOT null LOOP 
 dbms_output.put_line 
 ('Balance of ' || name || ' is ' || TO_CHAR(bal_list(name))); 
 name := bal_list.NEXT(name); 
 END LOOP; 
END; 
/
Balance of AAA is 2000
Balance of BBB is 5000
Balance of CCC is 10000
Balance of DDD is 8000

PL/SQL procedure successfully completed.

Example 2:

DECLARE 
 CURSOR c_cust is 
 select name from customers;

TYPE c_list IS TABLE of customers.Name%type INDEX BY binary_integer; 
 name_list c_list; 
 counter integer :=0; 
BEGIN 
 FOR n IN c_cust LOOP 
 counter := counter +1; 
 name_list(counter) := n.name; 
 dbms_output.put_line('Customer('||counter||'):'||name_list(counter)); 
 END LOOP; 
END; 
/
Customer(1):AAA
Customer(2):BBB
Customer(3):CCC
Customer(4):DDD
Customer(5):EEE
Customer(6):FFF

PL/SQL procedure successfully completed.

Nested Table Collections

A nested table is like a one-dimensional array with an arbitrary number of elements.

A nested table differs from an array in these important ways:

  • An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.
  • An array is always dense (that is, it always has consecutive subscripts). A nested array is dense initially, but it can become sparse, because you can delete elements from it.

Syntax :

TYPE type_name IS TABLE OF element_type [NOT NULL]; 
 
table_name type_name;

This declaration is similar to the declaration of an index-by table, but there is no INDEX BY clause.

Example :

DECLARE 
 TYPE names_table IS TABLE OF VARCHAR2(10); 
 TYPE grades IS TABLE OF INTEGER; 
 names names_table; 
 marks grades; 
 total integer; 
BEGIN 
 names := names_table('AAA', 'BBB', 'CCC', 'DDD'); 
 marks:= grades(98, 97, 78, 87); 
 total := names.count; 
 dbms_output.put_line('Total '|| total || ' Students'); 
 FOR i IN 1 .. total LOOP 
 dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i)); 
 end loop; 
END; 
/
Total 4 Students
Student:AAA, Marks:98
Student:BBB, Marks:97
Student:CCC, Marks:78
Student:DDD, Marks:87

PL/SQL procedure successfully completed.

Collection Methods

A collection method is a built-in PL/SQL subprogram that returns information about a collection or operates on a collection. Collection methods make collections easier to use, and make your applications easier to maintain.

The following table lists the methods and their purpose

Method Name Purpose
EXISTS(n) Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
COUNT Returns the number of elements that a collection currently contains.
LIMIT Checks the maximum size of a collection.
FIRST Returns the first (smallest) index numbers in a collection that uses the integer subscripts.
LAST Returns the last (largest) index numbers in a collection that uses the integer subscripts.
PRIOR(n) Returns the index number that precedes index n in a collection.
NEXT(n) Returns the index number that succeeds index n.
EXTEND Appends one null element to a collection.
EXTEND(n) Appends n null elements to a collection.
EXTEND(n,i) Appends n copies of the ith element to a collection.
TRIM Removes one element from the end of a collection.
TRIM(n)< Removes n elements from the end of a collection.
DELETE Removes all elements from a collection, setting COUNT to 0.
DELETE(n) Removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
DELETE(m,n) Removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

 

PL/SQL Cursors

A cursor is a pointer to a private SQL area that stores information about processing a specific SELECT or DML statement. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.

There are two types of cursors:

  • Implicit cursors
  • Explicit cursors

A cursor that is constructed and managed by PL/SQL is an implicit cursor.
A cursor that you construct and manage is an explicit cursor.

Implicit Cursors

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed. We cannot control an implicit cursor, but we can get information from its attributes.

The syntax of an implicit cursor attribute value is SQL%attribute (therefore, an implicit cursor is also called a SQL cursor), it always refers to the most recently run SELECT or DML statement. If no such statement has run, the value of SQL attribute is NULL.

An implicit cursor closes once its associated statement runs and its attribute values remain available until another SELECT or DML statement runs.

To save an attribute value for later use, assign it to a local variable immediately.

The implicit cursor attributes are:

SQL%ISOPEN : Is the Cursor Open ?
SQL%FOUND : Were Any Rows Affected ?
SQL%NOTFOUND : Were No Rows Affected ?
SQL%ROWCOUNT : How Many Rows Were Affected ?
SQL%BULK_ROWCOUNT : Getting Number of Rows Affected by FORALL Statement
SQL%BULK_EXCEPTIONS : Handling FORALL Exceptions After FORALL Statement Completes

Example :

DECLARE
 total_rows number(4);
BEGIN
 UPDATE emp
 SET sal = sal + 500;
 IF sql%notfound THEN
 dbms_output.put_line('no employees sal updated');
 ELSIF sql%found THEN 
 total_rows := sql%rowcount;
 dbms_output.put_line( total_rows || 'employees sal updated ');
 END IF;
END;
/

You can get the number of rows that each DML statement affected from the implicit cursor attribute SQL%BULK_ROWCOUNT.

Showing Number of Rows Affected by Each DELETE in FORALL

DECLARE
 TYPE NumList IS TABLE OF NUMBER;
 depts NumList := NumList(30, 40, 50);
BEGIN
 FORALL j IN depts.FIRST..depts.LAST
 DELETE FROM emp_temp WHERE department_id = depts(j);

FOR i IN depts.FIRST..depts.LAST LOOP
 DBMS_OUTPUT.PUT_LINE (
 'Statement #' || i || ' deleted ' ||
 SQL%BULK_ROWCOUNT(i) || ' rows.'
 );
 END LOOP;

DBMS_OUTPUT.PUT_LINE('Total rows deleted: ' || SQL%ROWCOUNT);
END;
/

Statement #1 deleted 6 rows.
Statement #2 deleted 45 rows.
Statement #3 deleted 5 rows.
Total rows deleted: 56

Explicit Cursors

An explicit cursor is a session cursor that you construct and manage. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. Then you can process the query result set in either of these ways:

  • Open the explicit cursor with the OPEN statement, fetch rows from the result set with the FETCH statement, and close the explicit cursor with the CLOSE statement.
  • Use the explicit cursor in a cursor FOR LOOP statement

Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated SELECT statement.

CURSOR cursor_name [ parameter_list ] [ RETURN return_type ]
 IS select_statement;

Cursor with parameters

CURSOR cursor_name (parameter_list)
IS
 SELECT_statement;

Cursor with return clause

CURSOR cursor_name
RETURN field%ROWTYPE
IS
 SELECT_statement;

Opening the Cursor

After declaring and defining an explicit cursor, you can open it with the OPEN statement.

OPEN cursor_name;

Fetching Data with Explicit Cursors

After opening an explicit cursor, you can fetch the rows of the query result set with the FETCH statement.

FETCH cursor_name INTO into_clause;

Closing the Cursor

Closing the cursor means releasing the allocated memory.

CLOSE cursor_name;

Example :

DECLARE 
 c_id customers.id%type; 
 c_name customerS.No.ame%type; 
 c_addr customers.address%type;
 
 CURSOR c_customers is 
 SELECT id, name, address FROM customers; 
BEGIN 
 OPEN c_customers; 
 LOOP 
 FETCH c_customers into c_id, c_name, c_addr; 
 EXIT WHEN c_customers%notfound; 
 dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); 
 END LOOP; 
 CLOSE c_customers; 
END; 
/

Fetching Same Explicit Cursor into Different Variables

DECLARE
 CURSOR c IS
 SELECT e.job_id, j.job_title
 FROM employees e, jobs j
 WHERE e.job_id = j.job_id AND e.manager_id = 100
 ORDER BY last_name;
 
 -- Record variables for rows of cursor result set:
 
 job1 c%ROWTYPE;
 job2 c%ROWTYPE;
 job3 c%ROWTYPE;
 job4 c%ROWTYPE;
 job5 c%ROWTYPE;
 
BEGIN
 OPEN c;
 FETCH c INTO job1; -- fetches first row
 FETCH c INTO job2; -- fetches second row
 FETCH c INTO job3; -- fetches third row
 FETCH c INTO job4; -- fetches fourth row
 FETCH c INTO job5; -- fetches fifth row
 CLOSE c;
 
 DBMS_OUTPUT.PUT_LINE(job1.job_title || ' (' || job1.job_id || ')');
 DBMS_OUTPUT.PUT_LINE(job2.job_title || ' (' || job2.job_id || ')');
 DBMS_OUTPUT.PUT_LINE(job3.job_title || ' (' || job3.job_id || ')');
 DBMS_OUTPUT.PUT_LINE(job4.job_title || ' (' || job4.job_id || ')');
 DBMS_OUTPUT.PUT_LINE(job5.job_title || ' (' || job5.job_id || ')');
END;
/

Explicit Cursor FOR LOOP Statement

DECLARE
 CURSOR c1 IS
 SELECT last_name, job_id FROM employees
 WHERE job_id LIKE '%CLERK%' AND manager_id > 120
 ORDER BY last_name;
BEGIN
 FOR item IN c1
 LOOP
 DBMS_OUTPUT.PUT_LINE
 ('Name = ' || item.last_name || ', Job = ' || item.job_id);
 END LOOP;
END;
/

Passing Parameters to Explicit Cursor FOR LOOP Statement

DECLARE
 CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS
 SELECT * FROM employees
 WHERE job_id = job
 AND salary > max_wage;
BEGIN
 FOR person IN c1('ST_CLERK', 3000)
 LOOP
 -- process data record
 DBMS_OUTPUT.PUT_LINE (
 'Name = ' || person.last_name || ', salary = ' ||
 person.salary || ', Job Id = ' || person.job_id
 );
 END LOOP;
END;
/

Subquery in FROM Clause of Parent Query

DECLARE
 CURSOR c1 IS
 SELECT t1.department_id, department_name, staff
 FROM departments t1,
 ( SELECT department_id, COUNT(*) AS staff
 FROM employees
 GROUP BY department_id
 ) t2
 WHERE (t1.department_id = t2.department_id) AND staff >= 5
 ORDER BY staff;

BEGIN
 FOR dept IN c1
 LOOP
 DBMS_OUTPUT.PUT_LINE ('Department = '
 || dept.department_name || ', staff = ' || dept.staff);
 END LOOP;
END;
/

 

PL/SQL Exceptions

An exception is a PL/SQL error that is raised during program execution. PL/SQL supports programmers to catch errors using Exception block in the program and appropriate action is taken against the error condition.

Exception types

There are three types of exceptions:

  • Predefined exceptions are error conditions that are defined by PL/SQL.
  • Non-predefined exceptions include any standard TimesTen errors.
  • User-defined exceptions are exceptions specific to your application.

Syntax for Exception Handling.

DECLARE 
  
BEGIN 
  
EXCEPTION 
  
 WHEN exception1 THEN 
 exception1-handling-statements 
 WHEN exception2 THEN 
 exception2-handling-statements 
 WHEN exception3 THEN 
 exception3-handling-statements 
 ........ 
 WHEN others THEN 
 others-handling-statements 
END;

Example 1:

DECLARE
   n_empno emp.empno%type := &empno;
   v_ename emp.ename%type;
   v_job emp.job%type;
BEGIN
   SELECT ename,job into v_ename,v_job
   from emp where empno=n_empno;
   DBMS_OUTPUT.PUT_LINE('The emp details are' || v_ename || v_job);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('The empno is not available');
END;
/

Example 2:

DECLARE
    n_accno accounts.accno%type:=&accno;
    v_name accounts.name%type:='&name';
    v_bal accounts.bal%type:=&bal;
BEGIN
    INSERT INTO accounts(accno,name,bal)
    VALUES(v_accno,v_name,v_bal);
    DBMS_OUTPUT.PUT_LINE('Account details inserted successfully');
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE('Account aleady exists');
END;
/

Example 3:

DECLARE
    n_empno emp.empno%type;
    v_ename emp.ename%type;
    n_deptno emp.deptno%type;
BEGIN
    SELECT empno,ename,deptno
    INTO n_empno,v_ename,n_deptno
    FROM emp WHERE empno=115 and ename='John';
    DBMS_OUTPUT.PUT_LINE(v_name || 'Works in ' || n_deptno || 'department');
    SELECT empno,ename,deptno
    INTO n_empno,v_ename,n_deptno
    FROM emp WHERE deptno=20;
    DBMS_OUTPUT.PUT_LINE('Employee no is' || n_empno);
    DBMS_OUTPUT.PUT_LINE('Employee name is' || v_ename);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data found');
    WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('More than one employee works in dept no 20');
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occured while processing the program');
END;
/

Predefined Exceptions :

Predefined exceptions are internally defined exceptions that have predefined names, which PL/SQL declares globally in the package STANDARD. The runtime system raises predefined exceptions implicitly (automatically). Because predefined exceptions have names, you can write exception handlers specifically for them.

lists the names and error codes of the predefined exceptions.

Exception Name Error Code
ACCESS_INTO_NULL -6530
CASE_NOT_FOUND -6592
COLLECTION_IS_NULL -6531
CURSOR_ALREADY_OPEN -6511
DUP_VAL_ON_INDEX -1
INVALID_CURSOR -1001
INVALID_NUMBER -1722
LOGIN_DENIED -1017
NO_DATA_FOUND +100
NO_DATA_NEEDED -6548
NOT_LOGGED_ON -1012
PROGRAM_ERROR -6501
ROWTYPE_MISMATCH -6504
SELF_IS_NULL -30625
STORAGE_ERROR -6500
SUBSCRIPT_BEYOND_COUNT -6533
SUBSCRIPT_OUTSIDE_LIMIT -6532
SYS_INVALID_ROWID -1410
TIMEOUT_ON_RESOURCE -51
TOO_MANY_ROWS -1422
VALUE_ERROR -6502
ZERO_DIVIDE -1476

Example 4:

DECLARE
 stock_price NUMBER := 9.73;
 net_earnings NUMBER := 0;
 pe_ratio NUMBER;
BEGIN
 pe_ratio := stock_price / net_earnings; -- raises ZERO_DIVIDE exception
 DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio);
EXCEPTION
 WHEN ZERO_DIVIDE THEN
 DBMS_OUTPUT.PUT_LINE('Company had zero earnings.');
 pe_ratio := NULL;
END;
/

User-Defined Exceptions

A user defined exception is an error that is defined by the program. The developer to handle the business situations define use-defined exceptions during the execution of the PL/SQL block.

An exception name declaration has this syntax:

exception_name EXCEPTION;

Steps

1. Declare exception
2. Raise in executable section explicitly using RAISE ;
3. Handle the raised exception.

Raising Exceptions

Syntax :

DECLARE
exception_name EXCEPTION; 
BEGIN
IF condition THEN 
 RAISE exception_name; 
 END IF;
EXCEPTION
WHEN exception_name THEN 
 statement;
END;
/

Example 5:

DECLARE 
 c_id customers.id%type := &cc_id; 
 c_name customers.name%type; 
 c_addr customers.address%type; 
 -- user defined exception 
 ex_invalid_id EXCEPTION; 
BEGIN
IF c_id <= 0 THEN 
 RAISE ex_invalid_id; 
 ELSE 
 SELECT name, address INTO c_name, c_addr 
 FROM customers 
 WHERE id = c_id;
 DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name); 
 DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); 
 END IF;

EXCEPTION 
 WHEN ex_invalid_id THEN 
 dbms_output.put_line('ID must be greater than zero!'); 
 WHEN no_data_found THEN 
 dbms_output.put_line('No such customer!'); 
 WHEN others THEN 
 dbms_output.put_line('Error!'); 
END; 
/

RAISE_APPLICATION_ERROR

This built-in procedure is used to create your own error message, which can be more descriptive than named Exceptions.
It is used to communicate a predefined exception interactively by returning a non standard error code and error message.
Using this procedure we can report error to application and avoid returning unhandled exception.

Syntax :

RAISE_APPLICATION_ERROR (error_number,'error_message');

Note :

  • Error number must exists between -20000 and -20999
  • Error_message is the text associate with this error, and keep_errors is Boolean value.
  • The error_message parameter must less than 512 characters.

Example 6:

DECLARE
 i emp%rowtype;
BEGIN
 i.empno:=&eno;
 SELECT ename,sal INTO i.ename,i.sal
 FROM emp WHERE empno=i.empno;
 IF i.sal < 2000 THEN
 RAISE_APPLICATION_ERROR(-20345,'The emp sal is less than 2000');
 
 ELSE
 i.sal := i.sal+i.sal*0.35;
 UPDATE emp set sal=i.sal
 WHERE empno=i.empno;
 dbms_output.put_line('Salary updated to' || i.sal);

END IF;
END;
/

Non-Predefined exceptions

We can associate an exception with an error code. The EXCEPTION_INIT pragma associates a user-defined exception name with an error code. The EXCEPTION_INIT pragma can appear only in the same declarative part as its associated exception, anywhere after the exception declaration.

Syntax :

PRAGMA EXCEPTION_INIT (exception_name, error_code);
DECLARE
 exception_name EXCEPTION;
 PRAGMA EXCEPTION_INIT(exception_name, -60);
BEGIN
 ...
EXCEPTION
 WHEN exception_name THEN
 ...
END;
/

Example 7:

CREATE PROCEDURE account_status (
 due_date DATE,
 today DATE
) AUTHID DEFINER
IS
BEGIN
 IF due_date < today THEN -- explicitly raise exception
 RAISE_APPLICATION_ERROR(-20000, 'Account past due.');
 END IF;
END;
/
 
DECLARE
 past_due EXCEPTION; -- declare exception
 PRAGMA EXCEPTION_INIT (past_due, -20000); -- assign error code to exception
BEGIN
 account_status ('1-JUL-10', '9-JUL-10'); -- invoke procedure
EXCEPTION
 WHEN past_due THEN -- handle exception
 DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20000)));
END;
/

SQLCODE Function

In an exception handler, the SQLCODE function returns the numeric code of the exception being handled. (Outside an exception handler, SQLCODE returns 0.)
For an internally defined exception, the numeric code is the number of the associated Oracle Database error. This number is negative except for the error “no data found”, whose numeric code is +100.

For a user-defined exception, the numeric code is either +1 (default) or the error code associated with the exception by the EXCEPTION_INIT pragma.

A SQL statement cannot invoke SQLCODE.

SQLERRM

The SQLERRM function returns the error message associated with an error code.

Like SQLCODE, SQLERRM without error_code is useful only in an exception handler. Outside an exception handler, or if the value of error_code is zero, SQLERRM returns ORA-0000.

If the value of error_code is +100, SQLERRM returns ORA-01403.

If the value of error_code is a positive number other than +100, SQLERRM returns this message:

-error_code: non-ORACLE exception

Ex:

BEGIN
 DBMS_OUTPUT.PUT_LINE('SQLERRM(-6511): ' || TO_CHAR(SQLERRM(-6511)));
END;
/

SQLERRM(-6511): ORA-06511: PL/SQL: cursor already open

Example 8:

CREATE TABLE errors (
 code NUMBER,
 message VARCHAR2(64)
);

CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS
 name EMPLOYEES.LAST_NAME%TYPE;
 v_code NUMBER;
 v_errm VARCHAR2(64);
BEGIN
 SELECT last_name INTO name
 FROM EMPLOYEES
 WHERE EMPLOYEE_ID = -1;
EXCEPTION
 WHEN OTHERS THEN
 v_code := SQLCODE;
 v_errm := SUBSTR(SQLERRM, 1, 64);
 DBMS_OUTPUT.PUT_LINE
 ('Error code ' || v_code || ': ' || v_errm);
 
 /* Invoke another procedure,
 declared with PRAGMA AUTONOMOUS_TRANSACTION,
 to insert information about errors. */
 
 INSERT INTO errors (code, message)
 VALUES (v_code, v_errm);

RAISE;
END;
/

Error code 100: ORA-01403: no data found

 

PL/SQL Triggers

A trigger is a named PL/SQL unit that is stored in the database and can be invoked repeatedly when some events occur.A trigger is a named PL/SQL unit that is stored in the database and can be invoked repeatedly when some events occur.

You can enable and disable a trigger, but you cannot explicitly invoke it. While a trigger is enabled, the database automatically invokes it that is, the trigger fires whenever its triggering event occurs. While a trigger is disabled, it does not fire.

You can write triggers that fire whenever one of the following operations occurs.

  • DML statements (INSERT, UPDATE, DELETE)
  • DDL statements (CREATE, ALTER or DROP)
  • Database events, such as logon/logoff, errors, or startup/shutdown
  • Use of Triggers:
    Automatically generate derived column values
  • Provide auditing
  • Prevent invalid transactions
  • Gather statistics on table access
  • Modify table data when DML statements are issued against views
  • Enforce complex security authorizations
  • Enforce referential integrity
  • Enforce complex business rules
  • Provide transparent event logging
  • Publish information about database events, user events, and SQL statements to subscribing applications

Types of Triggers

  • Row Triggers and Statement Triggers
  • BEFORE and AFTER Triggers
  • INSTEAD OF Triggers
  • Triggers on System Events and User Events

Row Triggers:

A row trigger is fired each time the table is affected by the triggering statement. 
For example, 
If an UPDATE statement updates multiple rows of a table, then row trigger is fired for each row affected by the UPDATE statement.If a triggering statement affects zero rows, then trigger won’t run.

Statement Triggers:

A statement trigger is fired only once when the table is affected by the triggering statement, regardless of the number of rows affected the triggering statement.Even if zero rows are affected, trigger will run.

BEFORE Triggers:

BEFORE triggers run the trigger action before the triggering statement is run. 

AFTER Triggers:

AFTER triggers run the trigger action after the triggering statement is run.

INSTEAD OF Triggers:

INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements. 

These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.

You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.

Triggers on System Events and User Events

You can use triggers to publish information about database events to subscribers. Applications can subscribe to database events just as they subscribe to messages from other applications. These database events can include:

System events

  • Database startup and shutdown
  • Data Guard role transitions
  • Server error message events
  • User events
  • User logon and logoff

Triggers on system events can be defined at the database level or schema level. The DBMS_AQ package is one example of using database triggers to perform certain actions. For example, a database shutdown trigger is defined at the database level:

CREATE TRIGGER register_shutdown
ON DATABASE SHUTDOWN
BEGIN
...
DBMS_AQ.ENQUEUE(...);
...
END;

Syntax of Triggers
Syntax for Creating a Trigger

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;

Example 1 :

BEFORE UPDATE FOR EACH ROW

1) Create the ‘product’ and ‘product_price_history’ table

CREATE TABLE product 
(id number(5), 
name varchar2(32), 
supplier varchar2(32), 
price number(7,2) 
); 
CREATE TABLE product_price_history 
(product_id number(5), 
product_name varchar2(32), 
supplier_name varchar2(32), 
unit_price number(7,2) );
 
Table PRODUCT created.

Table PRODUCT_PRICE_HISTORY created.

2) Create the price_history_trigger and execute it.

CREATE or REPLACE TRIGGER price_history_trigger 
BEFORE UPDATE OF price ON product 
FOR EACH ROW 
BEGIN 
INSERT INTO product_price_history 
VALUES (
:old.id,  
:old.name,  
:old.supplier,  
:old.price); 
END; /

3) Lets update the price of a product.

UPDATE PRODUCT SET price = 500 WHERE id = 101;

Once the above update query is executed, the trigger fires and updates the ‘product_price_history’ table.

4)If you ROLLBACK the transaction before committing to the database, the data inserted to the table is also rolled back.

Example 2:

AFTER UPDATE FOR EACH ROW

1. Create Emp_log table.

CREATE TABLE Emp_log 
(  Emp_id     NUMBER,  
Log_date   DATE,  
New_salary NUMBER,  
Action     VARCHAR2(20)
);

2. Create the log_salary_increase trigger and execute it.

CREATE OR REPLACE TRIGGER log_salary_increase  
AFTER UPDATE OF salary ON employees  
FOR EACH ROW
BEGIN  
INSERT INTO Emp_log 
(Emp_id, Log_date, New_salary, Action)  
VALUES (:NEW.employee_id, SYSDATE, :NEW.salary, 'New Salary');
END;
/

3. Update salary of an employee

UPDATE employeesSET salary = salary + 1000.0WHERE Department_id = 20;

4. Check Emg_log data.

SELECT * FROM Emp_log;
    EMP_ID LOG_DATE  NEW_SALARY ACTION
---------- --------- ---------- --------------------       
101        22-SEP-16   10049.50 New Salary       
102        22-SEP-16    6900.00 New Salary 
2 rows selected.

Example 3:

Conditional Trigger Prints Salary Change Information

CREATE OR REPLACE TRIGGER print_salary_changes  
BEFORE DELETE OR INSERT OR UPDATE ON employees  
FOR EACH ROW  
WHEN (NEW.job_id <> 'AD_PRES')  -- do not print information about President
DECLARE  
sal_diff  NUMBER;
BEGIN  sal_diff  := :NEW.salary  - :OLD.salary;  
DBMS_OUTPUT.PUT(:NEW.last_name || ': ');  
DBMS_OUTPUT.PUT('Old salary = ' || :OLD.salary || ', ');  
DBMS_OUTPUT.PUT('New salary = ' || :NEW.salary || ', ');  
DBMS_OUTPUT.PUT_LINE('Difference: ' || sal_diff);
END;
/
UPDATE employeesSET salary = salary * 1.05

WHERE department_id IN (10, 20, 90);
Whalen: Old salary = 2800, New salary = 2940, Difference: 140
Hartstein: Old salary = 13000, New salary = 13650, Difference: 650
Fay: Old salary = 6000, New salary = 6300, Difference: 300
Kochhar: Old salary = 17000, New salary = 17850, Difference: 850
De Haan: Old salary = 17000, New salary = 17850, Difference: 850 
6 rows updated.
CREATE TABLE product_check
(Message varchar2(50),
Current_Date number(32));

Example 4:

BEFORE UPDATE, Statement Level: 

This trigger will insert a record into the table ‘product_check’ before a sql update statement is executed, at the statement level.

CREATE or REPLACE TRIGGER Before_Update_Stat_product 
BEFORE UPDATE ON product_check 
Begin 
INSERT INTO product_check 
Values('Before update, statement level',sysdate); 
END; 
/ 

Example 5:

BEFORE UPDATE, Row Level: 

This trigger will insert a record into the table ‘product_check’ before each row is updated.

CREATE or REPLACE TRIGGER Before_Upddate_Row_product  
BEFORE  UPDATE ON product_check  
FOR EACH ROW  
BEGIN  INSERT INTO product_check  
Values('Before update row level',sysdate);  
END;  
/ 

Example 5:

AFTER UPDATE, Statement Level: 

This trigger will insert a record into the table ‘product_check’ after a sql update statement is executed, at the statement level.

CREATE or REPLACE TRIGGER After_Update_Stat_product  
AFTER  UPDATE ON product_check  
BEGIN  
INSERT INTO product_check  
Values('After update, statement level', sysdate);  
End;  
/ 

Example 6:  

AFTER UPDATE, Row Level:   

This trigger will insert a record into the table ‘product_check’ after each row is updated.

CREATE or REPLACE TRIGGER After_Update_Row_product  
AFTER   insert On product_check  
FOR EACH ROW  
BEGIN  
INSERT INTO product_check  
Values('After update, Row level',sysdate);  
END;  
/ 

Now lets execute a update statement on table product.

UPDATE PRODUCT SET unit_price = 800
WHERE product_id in (100,101); 

Lets check the data in ‘product_check’ table to see the order in which the trigger is fired.

SELECT * FROM product_check; 
 Mesage                              Current_Date
---------------------------------   -------------
Before update, statement level        22-SEP-2016
Before update, row level              22-SEP-2016
After update, Row level               22-SEP-2016
Before update, row level              22-SEP-2016
After update, Row level               22-SEP-2016
After update, statement level         22-SEP-2016

Example 7:

Trigger with REFERENCING Clause

creates a table with the same name as a correlation name, new, and then creates a trigger on that table. To avoid conflict between the table name and the correlation name, the trigger references the correlation name as Newest.

CREATE TABLE new 
(  field1  NUMBER,  
field2  VARCHAR2(20));

CREATE OR REPLACE TRIGGER Print_salary_changes
BEFORE UPDATE ON new
REFERENCING new AS Newest
FOR EACH ROW
BEGIN  
:Newest.Field2 := TO_CHAR (:newest.field1);
END;
/

How To know Information about Triggers.

We can use the data dictionary view ‘USER_TRIGGERS’ to obtain information about any trigger.

This view stores information about header and body of the trigger.

SELECT * FROM user_triggers
WHERE trigger_name = 'Before_Update_Stat_product'; 

The above sql query provides the header and body of the trigger ‘Before_Update_Stat_product’.

You can drop a trigger using the following command.

DROP TRIGGER trigger_name;

PL/SQL User defined Functions

A function is a set of PL/SQL statements you can call by name. Functions are very similar to procedures, except that a function returns a value to the environment in which it is called. User defined functions can be used as part of a SQL expression.

The data type of the return values must be declared in the header of the function. A function has output that needs to be assigned to a variable or it can be used in a SELECT statement.

Funtion can not call when it has RETURN data type as Boolean. A Function can contain more than one return statement, each exception should have a RETURN statement.

Syntax :

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

Create a Function

CREATE OR REPLACE FUNCTION totalCustomers 
RETURN number IS 
 total number(2) := 0; 
BEGIN 
 SELECT count(*) into total 
 FROM customers; 
 
 RETURN total; 
END; 
/
Function TOTALCUSTOMERS compiled

Calling a Function

DECLARE 
 t number(2); 
BEGIN 
 t := totalCustomers(); 
 dbms_output.put_line('Total no.of Customers : ' || t); 
END; 
/

Total no.of Customers : 6

PL/SQL procedure successfully completed.

Example : 2

CREATE FUNCTION get_bal(acc_no IN NUMBER) 
 RETURN NUMBER 
 IS acc_bal NUMBER(11,2);
 BEGIN 
 SELECT order_total 
 INTO acc_bal 
 FROM orders 
 WHERE customer_id = acc_no; 
 RETURN(acc_bal); 
 END;
/


Function GET_BAL compiled

Calling function with select statement

SELECT get_bal(100) FROM DUAL;

GET_BAL(100)
------------
        2500

Examples :3

CREARE OR REPLACE FUNCTION
employcount ( dept_no emp.deptno%typ)
RETURN NUMBER IS
emp_count number(4);
BEGIN
SELECT count(empno) INTO emp_count
FROM emp WHERE deptno=dept_no;
RETURN emp_count;
END;
/
SELECT unique deptno,employcount(deptno)
FROM emp where employcount(deptno) > 10;

Alter Function

Use the ALTER FUNCTION statement to recompile an invalid standalone stored function. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead.

ALTER FUNCTION employcount
 COMPILE;

Drop Function

Use the DROP FUNCTION statement to remove a standalone stored function from the database.

DROP FUNCTION employcount;