PL/SQL (Procedural Language/Structured Query Language) is procedural extension for SQL and the Oracle relational database.

PL/SQL is available in Oracle Database (since version 6 – stored pl/sql procedures/functions/packages/triggers since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).

Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.

PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers.

It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections.

Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation.

PL/SQL can be used for both server-side and Client side development. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces.

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 Syntax :

DECLARE
  variables;
  cursor declerations;
  user_defined exceptions;
BEGIN
  < executable command (s)>
EXCEPTION
  Action to perform when error occured
END;

Under Progress………………….

PL/SQL combines the data-manipulating power of SQL with the processing power of procedural languages.

When you can solve a problem with SQL, you can issue SQL statements from your PL/SQL program, without learning new APIs.

Like other procedural programming languages, PL/SQL lets you declare constants and variables, control program flow, define subprograms, and trap runtime errors.

You can break complex problems into easily understandable subprograms, which you can reuse in multiple applications.

PLSQL Advantages

Tight Integration with SQL

PL/SQL is tightly integrated with SQL, the most widely used database manipulation language.

PL/SQL fully supports SQL data types and lets use all SQL data manipulation, cursor control, and transaction control statements, and all SQL functions, operators.

High Performance

PL/SQL executes block of statements at a time, significantly reducing traffic between the application and the database.

The PL/SQL compiler has an optimizer that can rearrange code for better performance.

High Productivity

PL/SQL lets you write compact code for manipulating data. Just as a scripting language like PERL can read, transform, and write data in files, PL/SQL can query, transform, and update data in a database.

PL/SQL has many features that save designing and debugging time, and it is the same in all environments. If you learn to use PL/SQL with one Oracle tool, you can transfer your knowledge to other Oracle tools. For example, you can create a PL/SQL block in SQL Developer and then use it in an Oracle Forms trigger.

Portability

You can run PL/SQL applications on any operating system and platform where Oracle Database runs.

Scalability

PL/SQL stored subprograms increase scalability by centralizing application processing on the database server.

Manageability

PL/SQL stored subprograms increase manageability because you can maintain only one copy of a subprogram, on the database server, rather than one copy on each client system. Any number of applications can use the subprograms, and you can change the subprograms without affecting the applications that invoke them.

Support for Object-Oriented Programming

PL/SQL supports object-oriented programming with “Abstract Data Types”.

Support for Developing Web Applications

PL/SQL lets you create applications that generate web pages directly from the database, allowing you to make your database available on the Web and make back-office data accessible on the intranet.

You can implement a Web browser-based application entirely in PL/SQL with PL/SQL Gateway and the PL/SQL Web Toolkit.

SQLvsPLSQL

We will learn about DataTypes of PL/SQL in this chapter. The PL/SQL variables, constants and parameters must have a valid datatype, which specifies a storage format, constraints, and a valid range of values. We will focus on the SCALAR and the LOB data types in this chapter. The other two datatypes will be covered in other chapters.

PL/SQL Scalar DataTypes and Subtypes

PL/SQL Scalar Data Types and Subtypes come under the following categories

PL/SQL provides subtypes of data types. For example, the datatype NUMBER has a subtype called INTEGER. You can use the subtypes in your PL/SQL program to make the data types compatible with data types in other programs while embedding the PL/SQL code in another program, such as a Java program.

PL/SQL Numeric DataTypes and Sub-types

Following table lists out the PL/SQL pre-defined numeric datatypes and their sub-types –

Following is a valid declaration –

DECLARE
   num1 INTEGER;
   num2 REAL;
   num3 DOUBLE PRECISION;
BEGIN
   null;
END;
/

When the above code is compiled and executed, it produces the following result −

 PL/SQL procedure successfully completed

PL/SQL Character Data Types and Subtypes

PL/SQL Boolean Data Types

The BOOLEAN data type stores logical values that are used in logical operations. The logical values are the Boolean values TRUE and FALSE and the value NULL.
However, SQL has no data type equivalent to BOOLEAN. Therefore, Boolean values cannot be used in −

• SQL statements
• Built-in SQL functions (such as TO_CHAR)
• PL/SQL functions invoked from SQL statements

PL/SQL Datetime and Interval Types

The DATE datatype is used to store fixed-length datetimes, which include the time of day in seconds since midnight. Valid dates range from January 1, 4712 BC to December 31, 9999 AD.
The default date format is set by the Oracle initialization parameter NLS_DATE_FORMAT. For example, the default might be ‘DD-MON-YY’, which includes a two-digit number for the day of the month, an abbreviation of the month name, and the last two digits of the year. For example, 01-OCT-12.
Each DATE includes the century, year, month, day, hour, minute, and second. The following table shows the valid values for each field −

PL/SQL Large Object (LOB) Data Types

Large Object (LOB) data types refer to large data items such as text, graphic images, video clips, and sound waveforms. LOB datatypes allow efficient, random, piecewise access to this data. Following are the predefined PL/SQL LOB data types −

PL/SQL User-Defined Subtypes

A subtype is a subset of another data type, which is called its base type. A subtype has the same valid operations as its base type, but only a subset of its valid values. PL/SQL predefines several subtypes in package STANDARD. For example, PL/SQL predefine the subtypes CHARACTER and INTEGER as follows −

SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(38,0);

You can define and use your own subtypes. The following program illustrates defining and using a user-defined subtype −

DECLARE
   SUBTYPE name IS char(20);
   SUBTYPE message IS varchar2(100);
   salutation name;
   greetings message;
BEGIN
   salutation := 'Reader ';
   greetings := 'Welcome to the World of PL/SQL';
   dbms_output.put_line('Hello ' || salutation || greetings);
END;
/

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

Hello Reader Welcome to the World of PL/SQL

PL/SQL procedure successfully completed.

NULLs in PL/SQL

PL/SQL NULL values represent missing or unknown data and they are not an integer, a character, or any other specific data type. Note that NULL is not the same as an empty data string or the null character value ‘\0’. A null can be assigned but it cannot be equated with anything, including itself.

We will learn Variables in Pl/SQL in this lesson. A variable is nothing but a name given to a storage area that our programs can manipulate. Each variable in PL/SQL has a specific data type, which determines the size and the layout of the variable’s memory; the range of values that can be stored within that memory and the set of operations that can be applied to the variable.
The name of a PL/SQL variable consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs and should not exceed 30 characters. By default, variable names are not case-sensitive. You cannot use a reserved PL/SQL keyword as a variable name.
PL/SQL programming language allows to define various types of variables, such as date time data types, records, collections, etc. which we will cover in subsequent chapters. For this chapter, let us study only basic variable types.

Variable Declaration in PL/SQL

PL/SQL variables must be declared in the declaration section or in a package as a global variable. When you declare a variable, PL/SQL allocates memory for the variable’s value and the storage location is identified by the variable name.
The syntax for declaring a variable is

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

Where, variable_name is a valid identifier in PL/SQL, datatype must be a valid PL/SQL data type or any user defined data type which we already have discussed in the last chapter. Some valid variable declarations along with their definition are shown below −

sales number(10, 2);
pi CONSTANT double precision := 3.1415;
name varchar2(25);
address varchar2(100);

When you provide a size, scale or precision limit with the data type, it is called a constrained declaration. Constrained declarations require less memory than unconstrained declarations. For example −

sales number(10, 2);
name varchar2(25);
address varchar2(100);

Initializing Variables in PL/SQL

Whenever you declare a variable, PL/SQL assigns it a default value of NULL. If you want to initialize a variable with a value other than the NULL value, you can do so during the declaration, using either of the following

• The DEFAULT keyword
• The assignment operator

For example −

counter binary_integer := 0;
greetings varchar2(20) DEFAULT 'Have a Good Day';

You can also specify that a variable should not have a NULL value using the NOT NULL constraint. If you use the NOT NULL constraint, you must explicitly assign an initial value for that variable.
It is a good programming practice to initialize variables properly otherwise, sometimes programs would produce unexpected results. Try the following example which makes use of various types of variables

DECLARE
   a integer := 20;
   b integer := 30;
   c integer;
   f real;
BEGIN
   c := a + b;
   dbms_output.put_line('Value of c: ' || c);
   f := 70.0/3.0;
   dbms_output.put_line('Value of f: ' || f);
END;
/

When the above code is executed, it produces the following result

Value of c: 50
Value of f: 23.333333333333333333

PL/SQL procedure successfully completed.

Variable Scope in PL/SQL

PL/SQL allows the nesting of blocks, i.e., each program block may contain another inner block. If a variable is declared within an inner block, it is not accessible to the outer block. However, if a variable is declared and accessible to an outer block, it is also accessible to all nested inner blocks. There are two types of variable scope

1. Local variables: Variables declared in an inner block and not accessible to outer blocks.
2. Global variables: Variables declared in the outermost block or a package.

Following example shows the usage of Local and Global variables in its simple form

DECLARE
   -- Global variables
   num1 number := 75;
   num2 number := 45;
BEGIN
   dbms_output.put_line('Outer Variable num1: ' || num1);
   dbms_output.put_line('Outer Variable num2: ' || num2);
   DECLARE
      -- Local variables
      num1 number := 250;
      num2 number := 155;
   BEGIN
      dbms_output.put_line('Inner Variable num1: ' || num1);
      dbms_output.put_line('Inner Variable num2: ' || num2);
   END;
END;
/

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

Outer Variable num1: 75
Outer Variable num2: 45
Inner Variable num1: 250
Inner Variable num2: 155

PL/SQL procedure successfully completed.

Assigning SQL Query Results to PL/SQL Variables

You can use the SELECT INTO statement of SQL to assign values to PL/SQL variables. For each item in the SELECT list, there must be a corresponding, type-compatible variable in the INTO list. The following example illustrates the concept. Let us create a table named CUSTOMERS −

CREATE TABLE CUSTOMERS(
   ID        NUMBER NOT NULL,
   NAME      VARCHAR2(50) NOT NULL,
   AGE       NUMBER NOT NULL,
   ADDRESS   VARCHAR2(500),
   SALARY    NUMBER,
   PRIMARY KEY (ID)
);

Table Created

Let us now insert some values in the table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Joseph', 30, 'Athens', 2500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Henry', 35, 'Okland', 3500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'William', 27, 'Moscow', 2800.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'David', 25, 'Fresno', 4500.00 );

The following program assigns values from the above table to PL/SQL variables using the SELECT INTO clause of SQL

DECLARE
   c_id customers.id%type := 1;
   c_name  customers.name%type;
   c_addr customers.address%type;
   c_sal  customers.salary%type;
BEGIN
   SELECT name, address, salary INTO c_name, c_addr, c_sal
   FROM customers
   WHERE id = c_id;
   dbms_output.put_line
   ('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal);
END;
/

When the above code is executed, it produces the following result

Customer Joseph from Athens earns 2000

PL/SQL procedure completed successfully

Constants and Literals

We will learn constants and literals in PL/SQL. A constant holds a value that once declared, does not change in the program. A constant declaration specifies its name, data type, and value, and allocates storage for it. The declaration can also impose the NOT NULL constraint.

Declaring a Constant

A constant is declared using the CONSTANT keyword. It requires an initial value and does not allow that value to be changed.
Illustration

PI CONSTANT NUMBER := 3.141592654;

DECLARE
   -- constant declaration
   pi constant number := 3.141592654;
   -- other declarations
   radius number(5,2);
   dia number(5,2);
   circumference number(7, 2);
   area number (10, 2);
BEGIN
   -- processing
   radius := 9.5;
   dia := radius * 2;
   circumference := 2.0 * pi * radius;
   area := pi * radius * radius;
   -- output
   dbms_output.put_line('Radius: ' || radius);
   dbms_output.put_line('Diameter: ' || dia);
   dbms_output.put_line('Circumference: ' || circumference);
   dbms_output.put_line('Area: ' || area);
END;
/

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

Radius: 9.5
Diameter: 19
Circumference: 59.69
Area: 283.53

Pl/SQL procedure successfully completed.

The PL/SQL Literals

A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier. For example, TRUE, 786, NULL, ‘tutorialspoint’ are all literals of type Boolean, number, or string. PL/SQL, literals are case-sensitive. PL/SQL supports the following kinds of literals −

1. Numeric Literals
2. Character Literals
3. String Literals
4. BOOLEAN Literals
5. Date and Time Literals

The following table provides examples from all these categories of literal values.

To embed single quotes within a string literal, place two single quotes next to each other as shown in the following program −

DECLARE
   message  varchar2(30):= 'That''s tutorialspoint.com!';
BEGIN
   dbms_output.put_line(message);
END;
/

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

That's tutorialspoint.com!

PL/SQL procedure successfully completed.

PLSQL Operators

We will learn operators in PL/SQL in this lesson. An operator is a symbol that tells the compiler to perform specific mathematical or logical manipulation. 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

Here, we will understand the arithmetic, relational, comparison and logical operators one by one. The String operators will be discussed in a later chapter − PL/SQL Strings.

Arithmetic Operators

Following table shows all the arithmetic operators supported by PL/SQL. Let us assume variable A holds 10 and variable B holds 5, then

Relational Operators

Relational operators compare two expressions or values and return a Boolean result. Following table shows all the relational operators supported by PL/SQL. Let us assume variable A holds 10 and variable B holds 20, then −

Comparison Operators

Comparison operators are used for comparing one expression to another. The result is always either TRUE, FALSE or NULL.

Logical Operators

In this table shows the Logical operators supported by PL/SQL. All these operators work on Boolean operands and produce Boolean results. Let us assume variable A> holds true and variable B holds false, then −

PL/SQL Operator Precedence

Operator precedence determines the grouping of terms in an expression. This affects how an expression is evaluated. Certain operators have higher precedence than others; for example, the multiplication operator has higher precedence than the addition operator.
For example, x = 7 + 3 * 2; here, x is assigned 13, not 20 because operator * has higher precedence than +, so it first gets multiplied with 3*2 and then adds into 7.
Here, operators with the highest precedence appear at the top of the table, those with the lowest appear at the bottom. Within an expression, higher precedence operators will be evaluated first.
The precedence of operators goes as follows: =, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN.

A LOOP statement is an iterative control statement that allows you to executes a sequence of statements multiple times.

PLSQL provides these loop statements.

• Basic loop
• FOR loop
• WHILE loop
• Cursor FOR loop

Loop Control Statements

To prevent an infinite loop, at least one statement must transfer control outside the loop. The statements that can transfer control outside the loop are

• CONTINUE
• EXIT / EXIT WHEN
• GOTO

CONTINUE

The CONTINUE statement exits the current iteration of a loop, either conditionally or unconditionally, and transfers control to the next iteration of either the current loop or an enclosing labeled loop.

Restrictions on CONTINUE Statement

• A CONTINUE statement must be inside a LOOP statement.
• A CONTINUE statement cannot cross a subprogram or method boundary.

EXIT

The EXIT statement exits the current iteration of a loop, either conditionally or unconditionally, and transfers control to the end of either the current loop or an enclosing labeled loop.

Restriction on EXIT Statement

• An EXIT statement must be inside a LOOP statement.

GOTO

The GOTO statement transfers control to a labeled block or statement.

Restrictions on GOTO Statement

• A GOTO statement cannot transfer control into an IF statement, CASE statement, LOOP statement, or sub-block.
• A GOTO statement cannot transfer control from one IF statement clause to another, or from one CASE statement WHEN clause to another.
• A GOTO statement cannot transfer control out of a subprogram.
• A GOTO statement cannot transfer control into an exception handler.
• A GOTO statement cannot transfer control from an exception handler back into the current block.

Basic loop

With each iteration of the basic LOOP statement, its statements run and control returns to the top of the loop. The LOOP statement ends when a statement inside the loop transfers control outside the loop or raises an exception.

Syntax :

LOOP
sequence_of_statements;
END LOOP;

Basic loop statement with no EXIT keyword will be an INFINITE-LOOP that will never stop. To prevent this, the EXIT condition should be given inside this execution block so that control exit from the loop.

Syntax :

LOOP
 sequence_of_statements;
 EXIT;
END LOOP;

LOOP
 sequence_of_statements;
 EXIT WHEN condition;
END LOOP;

Example :

SET SERVEROUTPUT ON;
DECLARE 
counter NUMBER := 0;
BEGIN
 LOOP
 counter := counter + 1;
 DBMS_OUTPUT.PUT_LINE('Counter : ' || counter);
 IF counter = 5 THEN
 EXIT;
 END IF;
 END LOOP;
END;
/

Counter : 1
Counter : 2
Counter : 3
Counter : 4
Counter : 5


PL/SQL procedure successfully completed.

SET SERVEROUTPUT ON;
DECLARE 
counter NUMBER := 0;
BEGIN
 LOOP
 counter := counter + 1;
 DBMS_OUTPUT.PUT_LINE('Counter : ' || counter);
 EXIT WHEN counter=3;
 END LOOP;
END;
/

Counter : 1
Counter : 2
Counter : 3


PL/SQL procedure successfully completed.

FOR loop

PL/SQL FOR LOOP is an iterative statement that allows you to execute a sequence of statements a fixed number of times. The FOR LOOP statement ends when its index reaches a specified value, or when a statement inside the loop transfers control outside the loop or raises an exception.

Syntax :

FOR loop_counter IN [REVERSE]
lower_bound .. higher_bound
LOOP
 sequence_of_statements;
END LOOP;

Example :

DECLARE
 n_counter NUMBER := 5;
BEGIN
 FOR i IN 1 .. n_counter LOOP
 DBMS_OUTPUT.PUT_LINE('Iteration :' || i);
 END LOOP;
END;
/

Iteration :1
Iteration :2
Iteration :3
Iteration :4
Iteration :5

PL/SQL procedure successfully completed.

DECLARE
 n_counter NUMBER := 5;
BEGIN
 FOR i IN 1 .. n_counter LOOP
 IF i=3 THEN
 CONTINUE;
 END IF;
 DBMS_OUTPUT.PUT_LINE('Iteration :' || i);
 END LOOP;
END;
/

Iteration :1
Iteration :2
Iteration :4
Iteration :5

PL/SQL procedure successfully completed.

In above example, CONTINUE statement skipped the iteration when the condition is true and transfer the control to the next iteration of the current loop.

CONTINUE WHEN statement :

DECLARE
 n_counter NUMBER := 5;
BEGIN
 FOR i IN 1 .. n_counter LOOP
 CONTINUE WHEN i=3;

 DBMS_OUTPUT.PUT_LINE('Iteration :' || i);
 END LOOP;
END;
/

Reverse FOR LOOP Statements

DECLARE
 n_counter NUMBER := 5;
BEGIN
 FOR i IN REVERSE 1 .. n_counter LOOP
 CONTINUE WHEN i=3;

 DBMS_OUTPUT.PUT_LINE('Iteration :' || i);
 END LOOP;
END;
/

Iteration :5
Iteration :4
Iteration :2
Iteration :1

PL/SQL procedure successfully completed.

FOR LOOP with GOTO Statement.

DECLARE
 n_counter NUMBER := 5;
BEGIN
 FOR i IN 1 .. n_counter LOOP

 DBMS_OUTPUT.PUT_LINE('Iteration :' || i);
 IF i=3 THEN
 GOTO counter_reached;
 END IF;
 END LOOP;

 << counter_reached >>

 DBMS_OUTPUT.PUT_LINE('Counter reached to 3'); 
END;
/

In above example, when the condition is true, GOTO statement transfer the control out side the loop to the label “counter_reached”.

GOTO Statement Goes to Labeled NULL Statement

DECLARE
 done BOOLEAN;
BEGIN
 FOR i IN 1..10 LOOP
 IF done THEN
 GOTO end_loop;
 END IF;
 << end_loop >>
 NULL;
 END LOOP;
END;
/

GOTO Statement Cannot Transfer Control into IF Statement

DECLARE
 valid BOOLEAN := TRUE;
BEGIN
 GOTO update_row;

 IF valid THEN
 << update_row >>
 NULL;
 END IF;
END;
/

Error report -
ORA-06550: line 4, column 3:
PLS-00375: illegal GOTO statement; this GOTO cannot branch to label 'UPDATE_ROW'
ORA-06550: line 6, column 12:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

NULL Statement

The NULL statement only passes control to the next statement. Some languages refer to such an instruction as a no-op (no operation).

WHILE loop

The WHILE LOOP statement runs one or more statements while a condition is TRUE. The WHILE LOOP statement ends when the condition becomes FALSE or NULL, or when a statement inside the loop transfers control outside the loop or raises an exception.

Syntax :

WHILE condition
LOOP
 sequence_of_statements;
END LOOP;

Example :

DECLARE
 n_counter NUMBER := 5;
 n_factorial NUMBER := 1;
 n_number NUMBER;
BEGIN
 n_number := n_counter;
 WHILE n_counter > 0
 LOOP
 n_factorial := n_factorial * n_counter;
 n_counter := n_counter - 1;
 END LOOP;

 DBMS_OUTPUT.PUT_LINE('factorial of ' || n_number ||
 ' is ' || n_factorial);

END;
/

factorial of 5 is 120


PL/SQL procedure successfully completed.

We can include EXIT, EXIT WHEN, CONTINUE and GOTO statements in WHILE LOOP as well as per requirement.

Cursor FOR LOOP

The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns, and then opens a cursor.

With each iteration, the cursor FOR LOOP statement fetches a row from the result set into the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor.

Examples :

Implicit Cursor FOR LOOP Statement

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

Explicit Cursor FOR LOOP Statement

DECLARE
 CURSOR c 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 c
 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 c (job VARCHAR2, max_wage NUMBER) IS
 SELECT * FROM employees
 WHERE job_id = job
 AND salary > max_wage;
BEGIN
 FOR person IN c('ST_CLERK', 3000)
 LOOP

 DBMS_OUTPUT.PUT_LINE (
 'Name = ' || person.last_name || ', salary = ' ||
 person.salary || ', Job Id = ' || person.job_id
 );
 END LOOP;
END;
/

We will see more about cursors in detail in next chapters.

Nested Loops

The loop statements can also be nested. The outer and inner loop can be of different types. In nested loop, for every one iteration value of the outer loop, the inner loop will be executed fully.

Syntax :

LOOP -- outer
 statements;
 LOOP -- inner
 statements;
 END LOOP;
 statements;
END LOOP;

Example :

Labelling of Loops

In PL/SQL, the loops can be labeled. The label should be enclosed between “<<” and “>>”. The labeling of loops particularly in nested loop codes will give more readability.

The label can be given to GOTO statement to exit from that particular loop. Using label, the control can be made to directly exit the outer loop of the nested loops from anyplace inside the loops.

Syntax :

<< outer_loop >>
LOOP
 statements;
 << inner_loop >>
 LOOP
 statements;
 END LOOP;
 statements;
END LOOP';

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;

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;
/

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;
/

Under Progress………………….

Under Progress………………….

A procedure is a group of PL/SQL statements that you can call by name which performs one or more specific task.

A procedure has a header and a body.

• The header consists of the name of the procedure and the parameters or variables passed to the procedure.
•  
• The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block.

Syntax for creating procedure:

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
 < procedure_body > 
END procedure_name;

How to pass parameters in procedure:

We can pass parameters to procedures in three ways.

1) IN-parameters : The IN parameter can be referenced by the procedure or function. The value of the parameter cannot be overwritten by the procedure or the function.
2) OUT-parameters : The OUT parameter cannot be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
3) IN OUT-parameters : The INOUT parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

• A procedure may or may not return any value.
• IS – marks the beginning of the body of the procedure and is similar to DECLARE in anonymous PL/SQL Blocks. The code between IS and BEGIN forms the Declaration section.
• The AS keyword is used instead of the IS keyword for creating a standalone procedure.

How to execute a Stored Procedure

There are two ways to execute a procedure.

1) From the SQL prompt.

EXECUTE [or EXEC] procedure_name;

2) Within another procedure – simply use the procedure name.

procedure_name;

PL/SQL Create Procedure

CREATE OR REPLACE PROCEDURE 

remove_cust (cust_id NUMBER) IS

 BEGIN
 DELETE FROM CUSTOMERS
 WHERE CUSTOMERS.id = remove_cust.cust_id;

 DBMS_OUTPUT.PUT_LINE('Customer deleted');
 END;
/

Procedure REMOVE_CUST compiled

SET SERVEROUTPUT ON
BEGIN
remove_cust(2);

END;

Customer deleted

PL/SQL procedure successfully completed.

To view the Procedure availability

select object_name,object_type,status 
from USER_OBJECTS WHERE
object_name='REMOVE_CUST';

Recompile an Existing Procedure.

ALTER PROCEDURE remove_cust
 COMPILE;

Dropping Procedure.

Similar to dropping a table, procedure can also be dropped.

DROP PROCEDURE remove_cust;

IN & OUT Mode Example 1

This program finds the minimum of two values. Here, the procedure takes two numbers using the IN mode and returns their minimum using the OUT parameters.

DECLARE 
 a number; 
 b number; 
 c number;
PROCEDURE findmin(x IN number, y IN number, z OUT number) IS 
BEGIN 
 IF x < y THEN 
 z:= x; 
 ELSE 
 z:= y; 
 END IF; 
END;

BEGIN 
 a:= 25; 
 b:= 40; 
 findmin(a, b, c); 
 dbms_output.put_line(' Minimum of (25, 40) : ' || c); 
END; 
/


Minimum of (25, 40) : 25

PL/SQL procedure successfully completed.

IN & OUT Mode Example 2

This procedure computes the square of value of a passed value. This example shows how we can use the same parameter to accept a value and then return another result.

DECLARE 
 a number; 
PROCEDURE square(x IN OUT number) IS 
BEGIN 
 x := x * x; 
END; 
BEGIN 
 a:= 25; 
 square(a); 
 dbms_output.put_line(' Square of (25): ' || a); 
END; 
/


Square of (25): 625

PL/SQL procedure successfully completed.

Methods for Passing Parameters

Actual parameters can be passed in three ways

Positional notation

It is simple an association of the values by POSITION of the argumrnts at call time with that of declaration in the header of the procedure creation. The order of the parameters used when executing the procedure should match the order in the procedure header exactly.

Ex: findmin(a, b, c);

Named notation

It is an explicit association using the symbol =>

In Named notation, the order of the parameters is doesnot matter.

Ex: findmin(x => a, y => b, z => c);

Mixed notation

In mixed notation, you can mix both notations in procedure call; however, the positional notation should precede the named notation.

Ex: findmin(a, b, z => c); — is valid

Ex: findmin(X => a, b, c); — is not valid

A function is a set of PL/SQL statements you can call by name.

User defined functions can be used as part of a SQL expression.

A function has output that needs to be assigned to a variable or it can be used in a SELECT statement.

A function 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;

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

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 ‘PLSQL 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

PLSQL Objects

We will learn Object-Oriented in PL/SQL. PL/SQL allows defining an object type, which helps in designing object-oriented database in Oracle. An object type allows you to create composite types. Using objects allow you to implement real world objects with specific structure of data and methods for operating it. Objects have attributes and methods. Attributes are properties of an object and are used for storing an object’s state; and methods are used for modeling its behavior.
Objects are created using the CREATE [OR REPLACE] TYPE statement. Following is an example to create a simple address object consisting of few attributes

CREATE OR REPLACE TYPE address AS OBJECT
(house_no varchar2(10),
 street varchar2(30),
 city varchar2(20),
 state varchar2(10),
 pincode varchar2(10)
);
/

Type created.

Instantiating an Object

Defining an object type provides a blueprint for the object. To use this object, you need to create instances of this object. You can access the attributes and methods of the object using the instance name and the access operator (.) as follows

DECLARE
   residence address;
BEGIN
   residence := address('205', 'Haul Road', 'MN', 'Minnesota','524307');
   dbms_output.put_line('House No: '|| residence.house_no);
   dbms_output.put_line('Street: '|| residence.street);
   dbms_output.put_line('City: '|| residence.city);
   dbms_output.put_line('State: '|| residence.state);
   dbms_output.put_line('Pincode: '|| residence.pincode);
END;
/

House No: 205
Street: Haul Road
City: MN
State: Minnesota
Pincode: 524307

PL/SQL procedure successfully completed.

Member Methods

Member methods are used for manipulating the attributes of the object. You provide the declaration of a member method while declaring the object type. The object body defines the code for the member methods. The object body is created using the CREATE TYPE BODY statement.
Constructors are functions that return a new object as its value. Every object has a system defined constructor method. The name of the constructor is same as the object type. For example

residence := address('205', 'Haul Road', 'MN', 'Minnesota','524307');

The comparison methods are used for comparing objects. There are two ways to compare objects

Map method

The Map method is a function implemented in such a way that its value depends upon the value of the attributes. For example, for a customer object, if the customer code is same for two customers, both customers could be the same. So the relationship between these two objects would depend upon the value of code.

Order method

The Order method implements some internal logic for comparing two objects. For example, for a rectangle object, a rectangle is bigger than another rectangle if both its sides are bigger.

Using Map method

Let us try to understand the above concepts using the following rectangle object

CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
 width number,
 member function enlarge( inc number) return rectangle,
 member procedure display,
 map member function measure return number
);
/

Type created.

Creating the type body

CREATE OR REPLACE TYPE BODY rectangle AS
   MEMBER FUNCTION enlarge(inc number) return rectangle IS
   BEGIN
      return rectangle(self.length + inc, self.width + inc);
   END enlarge;
   MEMBER PROCEDURE display IS
   BEGIN
      dbms_output.put_line('Length: '|| length);
      dbms_output.put_line('Width: '|| width);
   END display;
   MAP MEMBER FUNCTION measure return number IS
   BEGIN
      return (sqrt(length*length + width*width));
   END measure;
END;
/

Type body created.

Now using the rectangle object and its member functions

DECLARE
   r1 rectangle;
   r2 rectangle;
   r3 rectangle;
   inc_factor number := 5;
BEGIN
   r1 := rectangle(3, 4);
   r2 := rectangle(5, 7);
   r3 := r1.enlarge(inc_factor);
   r3.display;
   IF (r1 > r2) THEN -- calling measure function
      r1.display;
   ELSE
      r2.display;
   END IF;
END;
/

Length: 8
Width: 9
Length: 5
Width: 7

PL/SQL procedure successfully completed.

Using Order method

Now, the same effect could be achieved using an order method. Let us recreate the rectangle object using an order method

CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
 width number,
 member procedure display,
 order member function measure(r rectangle) return number
);
/

Type created.

Creating the type body

CREATE OR REPLACE TYPE BODY rectangle AS
   MEMBER PROCEDURE display IS
   BEGIN
      dbms_output.put_line('Length: '|| length);
      dbms_output.put_line('Width: '|| width);
   END display;
   ORDER MEMBER FUNCTION measure(r rectangle) return number IS
   BEGIN
      IF(sqrt(self.length*self.length + self.width*self.width)>
         sqrt(r.length*r.length + r.width*r.width)) then
         return(1);
      ELSE
         return(-1);
      END IF;
   END measure;
END;
/

Type body created.

Using the rectangle object and its member functions

DECLARE
   r1 rectangle;
   r2 rectangle;
BEGIN
   r1 := rectangle(23, 44);
   r2 := rectangle(15, 17);
   r1.display;
   r2.display;
   IF (r1 > r2) THEN -- calling measure function
      r1.display;
   ELSE
      r2.display;
   END IF;
END;
/

Length: 23
Width: 44
Length: 15
Width: 17
Length: 23
Width: 44

PL/SQL procedure successfully completed.

Inheritance for PL/SQL Objects

PL/SQL allows creating object from the existing base objects. To implement inheritance, the base objects should be declared as NOT FINAL. The default is FINAL.
The following programs illustrate the inheritance in PL/SQL Objects. Let us create another object named TableTop, this is inherited from the Rectangle object. For this, we need to create the base rectangle object

CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
 width number,
 member function enlarge( inc number) return rectangle,
 NOT FINAL member procedure display) NOT FINAL
/

Type created.

Creating the base type body

CREATE OR REPLACE TYPE BODY rectangle AS
   MEMBER FUNCTION enlarge(inc number) return rectangle IS
   BEGIN
      return rectangle(self.length + inc, self.width + inc);
   END enlarge;
   MEMBER PROCEDURE display IS
   BEGIN
      dbms_output.put_line('Length: '|| length);
      dbms_output.put_line('Width: '|| width);
   END display;
END;
/

Type body created.

Creating the child object tabletop

CREATE OR REPLACE TYPE tabletop UNDER rectangle
(
   material varchar2(20),
   OVERRIDING member procedure display
)
/

 Type created.

Creating the type body for the child object tabletop

CREATE OR REPLACE TYPE BODY tabletop AS
OVERRIDING MEMBER PROCEDURE display IS
BEGIN
   dbms_output.put_line('Length: '|| length);
   dbms_output.put_line('Width: '|| width);
   dbms_output.put_line('Material: '|| material);
END display;
/

Type body created.

Using the tabletop object and its member functions

DECLARE
   t1 tabletop;
   t2 tabletop;
BEGIN
   t1:= tabletop(20, 10, 'Wood');
   t2 := tabletop(50, 30, 'Steel');
   t1.display;
   t2.display;
END;
/

Length: 20
Width: 10
Material: Wood
Length: 50
Width: 30
Material: Steel

PL/SQL procedure successfully completed.

Abstract Objects in PL/SQL

The NOT INSTANTIABLE clause allows you to declare an abstract object. You cannot use an abstract object as it is; you will have to create a subtype or child type of such objects to use its functionalities.
For example

CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
 width number,
 NOT INSTANTIABLE NOT FINAL MEMBER PROCEDURE display)
 NOT INSTANTIABLE NOT FINAL
/

Type created.

Under Progress………………….

An exception is a PLSQL error that is raised during program execution. PLSQL 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 
 < declarations section> 
BEGIN 
 < executable command(s)> 
EXCEPTION 
 < exception handling goes here > 
 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:

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 < exception_name >;
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

Example :

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

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

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.

Example 4:

CREATE TABLE product_check
(Message varchar2(50),
Current_Date number(32));

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 6:

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 7:

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;

Under Progress………………….

Under Progress………………….

Under Progress………………….