PLSQL Notes

What is PL/SQL

PL/SQL is a block structured language. The programs of PL/SQL are logical blocks that can contain any number of nested sub-blocks. Pl/SQL stands for "Procedural Language extension of SQL" that is used in Oracle. PL/SQL is integrated with Oracle database (since version 7). The functionalities of PL/SQL usually extended after each release of Oracle database. Although PL/SQL is closely integrated with SQL language, yet it adds some programming constraints that are not available in SQL.

Functionalities

PL/SQL includes procedural language elements like conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variable of those types and triggers. It can support Array and handle exceptions (runtime errors). After the implementation of version 8 of Oracle database have included features associated with object orientation. You can create PL/SQL units like procedures, functions, packages, types and triggers, etc. which are stored in the database for reuse by applications.

With PL/SQL, you can use SQL statements to manipulate Oracle data and flow of control statements to process the data.

The PL/SQL is known for its combination of data manipulating power of SQL with data processing power of procedural languages. It inherits the robustness, security, and portability of the Oracle Database.

PL/SQL is not case sensitive so you are free to use lower case letters or upper case letters except within string and character literals. A line of PL/SQL text contains groups of characters known as lexical units. It can be classified as follows:

• Delimeters
• Identifiers
• Literals
• Comments

PL/SQL Variables

A variable is a meaningful name which facilitates a programmer to store data temporarily during the execution of code. It helps you to manipulate data in PL/SQL programs. It is nothing except a name given to a storage area. Each variable in the PL/SQL has a specific data type which defines the size and layout of the variable's memory.

A variable should not exceed 30 characters. Its letter optionally followed by more letters, dollar signs, numerals, underscore etc.

How to declare variable in PL/SQL

You must declare the PL/SQL variable in the declaration section or in a package as a global variable. After the declaration, PL/SQL allocates memory for the variable?s value and the storage location is identified by the variable name.

Syntax for declaring variable:

Following is the syntax for declaring variable:

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

Here, variable_name is a valid identifier in PL/SQL and datatype must be valid PL/SQL data type. A data type with size, scale or precision limit is called a constrained declaration. The constrained declaration needs less memory than unconstrained declaration.

Naming rules for PL/SQL variables

The variable in PL/SQL must follow some naming rules like other programming languages.

• The variable_name should not exceed 30 characters.
• The name of the variable must begin with ASCII letter. The PL/SQL is not case sensitive so it could be either lowercase or uppercase. For example: v_data and V_DATA refer to the same variables.
• You should make your variable easy to read and understand, after the first character, it may be any number, underscore (_) or dollar sign ($).
• NOT NULL is an optional specification on the variable.

Initializing Variables in PL/SQL

Evertime you declare a variable, PL/SQL defines a default value NULL to it. If you want to initialize a variable with other value than NULL value, you can do so during the declaration, by using any one of the following methods.

• The DEFAULT keyword
• The assignment operator

1. counter binary_integer := 0;  
2. greetings varchar2(20) DEFAULT'SURESH PLSQL CLASS;    

You can also specify NOT NULL constraint to avoid NULL value. If you specify the NOT NULL constraint, you must assign an initial value for that variable.

You must have a good programming skill to initialize variable properly otherwise, sometimes program would produce unexpected result.

Example of initializing variable

Let's take a simple example to explain it well:

1. DECLARE
2. a integer := 30;  
3. b integer := 40;  
4. c integer;  
5. f real;  
6. BEGIN
7. c := a + b;  
8. put_line('Value of c: '|| c);  
9. f := 100.0/3.0;  
10. put_line('Value of f: '|| f);  
11. END;  

After the execution, this will produce the following result:

1. Value ofc: 70  
2. Value off: 33.333333333333333333  
4. PL/SQL proceduresuccessfully completed.  

Variable Scope in PL/SQL:

PL/SQL allows nesting of blocks. A program block can contain another inner block. If you declare a variable within an inner block, it is not accessible to an outer block. There are two types of variable scope:

• Local Variable: Local variables are the inner block variables which are not accessible to outer blocks.
• Global Variable: Global variables are declared in outermost block.

Example of Local and Global variables

Let's take an example to show the usage of Local and Global variables in its simple form:

DECLARE  

 -- Global variables   

   num1 number := 95;   

   num2 number := 85;   

BEGIN   

   dbms_output.put_line('Outer Variable num1: ' || num1);  

   dbms_output.put_line('Outer Variable num2: ' || num2);  

   DECLARE   

      -- Local variables  

      num1 number := 195;   

      num2 number := 185;   

   BEGIN   

      dbms_output.put_line('Inner Variable num1: ' || num1);  

      dbms_output.put_line('Inner Variable num2: ' || num2);  

   END;   

END;  

/  

After the execution, this will produce the following result:

Outer Variable num1: 95  

Outer Variable num2: 85  

Inner Variable num1: 195  

Inner Variable num2: 185  

  

PL/SQL procedure successfully completed.  

PL/SQL Constants

A constant is a value used in a PL/SQL block that remains unchanged throughout the program. It is a user-defined literal value. It can be declared and used instead of actual values.

Let's take an example to explain it well:

Suppose, you have to write a program which will increase the salary of the employeesupto 30%, you can declare a constant and use it throughout the program. Next time if you want to increase the salary again you can change the value of constant than the actual value throughout the program.

Syntax to declare a constant:

constant_name CONSTANT datatype := VALUE;  

• Constant_name:it is the name of constant just like variable name. The constant word is a reserved word and its value does not change.
• VALUE: it is a value which is assigned to a constant when it is declared. It can not be assigned later.

Example of PL/SQL constant

Let's take an example to explain it well:

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;  

/  

After the execution of the above code at SQL prompt, it will produce the following result:.

Radius: 9.5  

Diameter: 19  

Circumference: 59.69  

Area: 283.53  

  

Pl/SQL procedure successfully completed.  

PL/SQL Literals

Literals are the explicit numeric, character, string or boolean values which are not represented by an identifier. For example: TRUE, NULL, etc. are all literals of type boolean. PL/SQL literals are case-sensitive. There are following kinds of literals in PL/SQL:

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

Example of these different types of Literals:

Literals	Examples
Numeric	75125, 3568, 33.3333333 etc.
Character	'A' '%' '9' ' ' 'z' '('
String	Hello PLSQL CLASS!
Boolean	TRUE, FALSE, NULL etc.
Date and Time	'26-11-2002' , '2012-10-29 12:01:01'

Next Topic

PL/SQL If

PL/SQL supports the programming language features like conditional statements and iterative statements. Its programming constructs are similar to how you use in programming languages like Java and C++.

Syntax for IF Statement:

There are different syntaxes for the IF-THEN-ELSE statement.

Syntax: (IF-THEN statement):

IF condition   

THEN   

Statement: {It is executed when condition is true}  

END IF;  

This syntax is used when you want to execute statements only when condition is TRUE.

Syntax: (IF-THEN-ELSE statement):

IF condition   

THEN  

   {...statements to execute when condition is TRUE...}  

ELSE  

   {...statements to execute when condition is FALSE...}  

END IF;   

This syntax is used when you want to execute one set of statements when condition is TRUE or a different set of statements when condition is FALSE.

Syntax: (IF-THEN-ELSIF statement):

IF condition1   

THEN  

   {...statements to execute when condition1 is TRUE...}  

ELSIF condition2   

THEN  

   {...statements to execute when condition2 is TRUE...}  

END IF;  

This syntax is used when you want to execute one set of statements when condition1 is TRUE or a different set of statements when condition2 is TRUE.

Syntax: (IF-THEN-ELSIF-ELSE statement):

IF condition1   

THEN  

   {...statements to execute when condition1 is TRUE...}  

ELSIF condition2   

THEN  

   {...statements to execute when condition2 is TRUE...}  

ELSE  

   {...statements to execute when both condition1 and condition2 are FALSE...}  

END IF;  

It is the most advance syntax and used if you want to execute one set of statements when condition1 is TRUE, a different set of statement when condition2 is TRUE or a different set of statements when both the condition1 and condition2 are FALSE.

Example of PL/SQL If Statement

Let's take an example to see the whole concept:

DECLARE  

   a number(3) := 500;  

BEGIN  

   -- check the boolean condition using if statement   

   IF( a < 20 ) THEN  

      -- if condition is true then print the following    

      dbms_output.put_line('a is less than 20 ' );  

   ELSE  

      dbms_output.put_line('a is not less than 20 ' );  

   END IF;  

   dbms_output.put_line('value of a is : ' || a);  

END;  

After the execution of the above code in SQL prompt, you will get the following result:

a is not less than 20

value of a is : 500

PL/SQL procedure successfully completed.

PL/SQL Case Statement

The PL/SQL CASE statement facilitates you to execute a sequence of satatements based on a selector. A selector can be anything such as variable, function or an expression that the CASE statement checks to a boolean value.

The CASE statement works like the IF statement, only using the keyword WHEN. A CASE statement is evaluated from top to bottom. If it get the condition TRUE, then the corresponding THEN calause is executed and the execution goes to the END CASE clause.

Syntax for the CASE Statement:

CASE [ expression ]  

WHEN condition_1 THEN result_1  

   WHEN condition_2 THEN result_2  

   ...  

   WHEN condition_n THEN result_n  

 ELSE result  

END   

Example of PL/SQL case statement

Let's take an example to make it clear:

DECLARE  

   grade char(1) := 'A';  

BEGIN  

   CASE grade  

      when 'A' then dbms_output.put_line('Excellent');  

      when 'B' then dbms_output.put_line('Very good');  

      when 'C' then dbms_output.put_line('Good');  

      when 'D' then dbms_output.put_line('Average');  

      when 'F' then dbms_output.put_line('Passed with Grace');  

      else dbms_output.put_line('Failed');  

   END CASE;  

END;  

After the execution of above code, you will get the following result:

Excellent

PL/SQL procedure successfully completed.

 

PL/SQL Loop

The PL/SQL loops are used to repeat the execution of one or more statements for specified number of times. These are also known as iterative control statements.

Syntax for a basic loop:

LOOP  

  Sequence of statements;  

END LOOP;  

Types of PL/SQL Loops

There are 4 types of PL/SQL Loops.

1. Basic Loop / Exit Loop
2. While Loop
3. For Loop
4. Cursor For Loop

PL/SQL Exit Loop (Basic Loop)

PL/SQL exit loop is used when a set of statements is to be executed at least once before the termination of the loop. There must be an EXIT condition specified in the loop, otherwise the loop will get into an infinite number of iterations. After the occurrence of EXIT condition, the process exits the loop.

Syntax of basic loop:

LOOP  

  Sequence of statements;  

END LOOP;  

Syntax of exit loop:

LOOP   

   statements;   

   EXIT;   

   {or EXIT WHEN condition;}  

END LOOP;  

Example of PL/SQL EXIT Loop

Let's take a simple example to explain it well:

DECLARE  

i NUMBER := 1;  

BEGIN  

LOOP  

EXIT WHEN i>10;  

DBMS_OUTPUT.PUT_LINE(i);  

i := i+1;  

END LOOP;  

END;  

After the execution of the above code, you will get the following result:

1

2

3

4

5

6

7

8

9

10

Note: You must follow these steps while using PL/SQL Exit Loop.

• Initialize a variable before the loop body
• Increment the variable in the loop.
• You should use EXIT WHEN statement to exit from the Loop. Otherwise the EXIT statement without WHEN condition, the statements in the Loop is executed only once.

PL/SQL EXIT Loop Example 2

DECLARE   

VAR1 NUMBER;  

VAR2 NUMBER;  

BEGIN   

VAR1:=100;  

VAR2:=1;  

LOOP  

DBMS_OUTPUT.PUT_LINE (VAR1*VAR2);  

IF (VAR2=10) THEN  

EXIT;  

END IF;  

VAR2:=VAR2+1;  

END LOOP;  

END;  

Output:

100

200

300

400

500

600

700

800

900

1000

 

PL/SQL While Loop

PL/SQL while loop is used when a set of statements has to be executed as long as a condition is true, the While loop is used. The condition is decided at the beginning of each iteration and continues until the condition becomes false.

Syntax of while loop:

WHILE <condition>   

 LOOP statements;   

END LOOP;  

Example of PL/SQL While Loop

Let's see a simple example of PL/SQL WHILE loop.

DECLARE  

i INTEGER := 1;  

BEGIN  

WHILE i <= 10 LOOP  

DBMS_OUTPUT.PUT_LINE(i);  

i := i+1;  

END LOOP;  

END;  

After the execution of the above code, you will get the following result:

1

2

3

4

5

6

7

8

9

10

Note: You must follow these steps while using PL/SQL WHILE Loop.

• Initialize a variable before the loop body.
• Increment the variable in the loop.
• You can use EXIT WHEN statements and EXIT statements in While loop but it is not done often.

PL/SQL WHILE Loop Example 2

DECLARE   

VAR1 NUMBER;  

VAR2 NUMBER;  

BEGIN   

VAR1:=200;  

VAR2:=1;  

WHILE (VAR2<=10)  

LOOP  

DBMS_OUTPUT.PUT_LINE (VAR1*VAR2);  

VAR2:=VAR2+1;  

END LOOP;  

END;  

 

Output:

200

400

600

800

1000

1200

1400

1600

1800

2000

 

PL/SQL FOR Loop

PL/SQL for loop is used when when you want to execute a set of statements for a predetermined number of times. The loop is iterated between the start and end integer values. The counter is always incremented by 1 and once the counter reaches the value of end integer, the loop ends.

Syntax of for loop:

FOR counter IN initial_value .. final_value LOOP  

  LOOP statements;   

END LOOP;  

• initial_value : Start integer value
• final_value : End integer value

PL/SQL For Loop Example 1

Let's see a simple example of PL/SQL FOR loop.

BEGIN  

FOR k IN 1..10 LOOP  

-- note that k was not declared  

DBMS_OUTPUT.PUT_LINE(k);  

END LOOP;  

END;   

After the execution of the above code, you will get the following result:

1

2

3

4

5

6

7

8

9

10

Note: You must follow these steps while using PL/SQL WHILE Loop.

• You don't need to declare the counter variable explicitly because it is declared implicitly in the declaration section.
• The counter variable is incremented by 1 and does not need to be incremented explicitly.
• You can use EXIT WHEN statements and EXIT statements in FOR Loops but it is not done often.

PL/SQL For Loop Example 2

DECLARE   

VAR1 NUMBER;  

BEGIN   

VAR1:=10;  

FOR VAR2 IN 1..10  

LOOP  

DBMS_OUTPUT.PUT_LINE (VAR1*VAR2);  

END LOOP;  

END;  

Output:

10

20

30

40

50

60

70

80

90

100

PL/SQL For Loop REVERSE Example 3

Let's see an example of PL/SQL for loop where we are using REVERSE keyword.

DECLARE   

VAR1 NUMBER;  

BEGIN   

VAR1:=10;  

FOR VAR2 IN REVERSE 1..10  

LOOP  

DBMS_OUTPUT.PUT_LINE (VAR1*VAR2);  

END LOOP;  

END;  

 

Output:

100

90

80

70

60

50

40

30

20

10

 

PL/SQL Continue Statement

The continue statement is used to exit the loop from the reminder if its body either conditionally or unconditionally and forces the next iteration of the loop to take place, skipping any codes in between.

The continue statement is not a keyword in Oracle 10g. It is a new feature encorporated in oracle 11g.

For example: If a continue statement exits a cursor FOR LOOP prematurely then it exits an inner loop and transfer control to the next iteration of an outer loop, the cursor closes (in this context, CONTINUE works like GOTO).

Syntax:

continue;  

Example of PL/SQL continue statement

Let's take an example of PL/SQL continue statement.

DECLARE  

  x NUMBER := 0;  

BEGIN  

  LOOP -- After CONTINUE statement, control resumes here  

    DBMS_OUTPUT.PUT_LINE ('Inside loop:  x = ' || TO_CHAR(x));  

    x := x + 1;  

    IF x < 3 THEN  

      CONTINUE;  

    END IF;  

    DBMS_OUTPUT.PUT_LINE  

      ('Inside loop, after CONTINUE:  x = ' || TO_CHAR(x));  

    EXIT WHEN x = 5;  

  END LOOP;  

   

  DBMS_OUTPUT.PUT_LINE (' After loop:  x = ' || TO_CHAR(x));  

END;  

/  

After the execution of above code, you will get the following result:

Inside loop:  x = 0

Inside loop:  x = 1

Inside loop:  x = 2

Inside loop, after CONTINUE:  x = 3

Inside loop:  x = 3

Inside loop, after CONTINUE:  x = 4

Inside loop:  x = 4

Inside loop, after CONTINUE:  x = 5

After loop:  x = 5

 

PL/SQL GOTO Statement

In PL/SQL, GOTO statement makes you able to get an unconditional jump from the GOTO to a specific executable statement label in the same subprogram of the PL/SQL block.

Here the label declaration which contains the label_name encapsulated within the <<>> symbol and must be followed by at least one statement to execute.

Syntax:

GOTO label_name;  

Here the label declaration which contains the label_name encapsulated within the <<>> symbol and must be followed by at least one statement to execute.

GOTO label_name;  

 ..  

..  

<<label_name>>  

Statement;  

Example of PL/SQL GOTO statement

Let's take an example of PL/SQL GOTO statement.

DECLARE  

   a number(2) := 30;  

BEGIN  

   <<loopstart>>  

   -- while loop execution   

   WHILE a < 50 LOOP  

      dbms_output.put_line ('value of a: ' || a);  

      a := a + 1;  

      IF a = 35 THEN  

         a := a + 1;  

         GOTO loopstart;  

      END IF;  

   END LOOP;  

END;  

/  

After the execution of above code, you will get the following result:

value of a: 30

value of a: 31

value of a: 32

value of a: 33

value of a: 34

value of a: 36

value of a: 37

value of a: 38

value of a: 39

value of a: 40

value of a: 41

value of a: 42

value of a: 43

value of a: 44

value of a: 45

value of a: 46

value of a: 47

value of a: 48

value of a: 49

 

Statement processed.

Restriction on GOTO statement

Following is a list of some restrictions imposed on GOTO statement.

• Cannot transfer control into an IF statement, CASE statement, LOOP statement or sub-block.
• Cannot transfer control from one IF statement clause to another or from one CASE statement WHEN clause to another.
• Cannot transfer control from an outer block into a sub-block.
• Cannot transfer control out of a subprogram.
• Cannot transfer control into an exception handler.

 

PL/SQL Exception Handling

 

What is Exception

An error occurs during the program execution is called Exception in PL/SQL.

PL/SQL facilitates programmers to catch such conditions using exception block in the program and an appropriate action is taken against the error condition.

There are two type of exceptions:

• System-defined Exceptions
• User-defined Exceptions

PL/SQL Exception Handling

Syntax for exception handling:

Following is a general 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  

      exception3-handling-statements  

END;  

Example of exception handling

Let's take a simple example to demonstrate the concept of exception handling. Here we are using the already created CUSTOMERS table.

SELECT* FROM COUSTOMERS;

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	23	Allahabad	20000
2	Suresh	22	Kanpur	22000
3	Mahesh	24	Ghaziabad	24000
4	Chandan	25	Noida	26000
5	Alex	21	Paris	28000
6	Sunita	20	Delhi	30000

 

DECLARE  

   c_id customers.id%type := 8;  

   c_name  customers.name%type;  

   c_addr customers.address%type;  

BEGIN  

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

EXCEPTION  

   WHEN no_data_found THEN  

      dbms_output.put_line('No such customer!');  

   WHEN others THEN  

      dbms_output.put_line('Error!');  

END;  

/   

After the execution of above code at SQL Prompt, it produces the following result:

No such customer!PL/SQL procedure successfully completed.

The above program should show the name and address of a customer as result whose ID is given. But there is no customer with ID value 8 in our database, so the program raises the run-time exception NO_DATA_FOUND, which is captured in EXCEPTION block.

If you use the id defined in the above table (i.e. 1 to 6), you will get a certain result. For a demo example: here, we are using the id 5.

DECLARE  

   c_id customers.id%type := 5;  

   c_name  customers.name%type;  

   c_addr customers.address%type;  

BEGIN  

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

EXCEPTION  

   WHEN no_data_found THEN  

      dbms_output.put_line('No such customer!');  

   WHEN others THEN  

      dbms_output.put_line('Error!');  

END;  

/   

 

After the execution of above code at SQL prompt, you will get the following result:

Name: alexAddress: parisPL/SQL procedure successfully completed.

Raising Exceptions

In the case of any internal database error, exceptions are raised by the database server automatically. But it can also be raised explicitly by programmer by using command RAISE.

Syntax for raising an exception:

DECLARE  

   exception_name EXCEPTION;  

BEGIN  

   IF condition THEN  

      RAISE exception_name;  

   END IF;  

EXCEPTION  

   WHEN exception_name THEN  

   statement;  

END;

 

 

  PL/SQL User-defined Exceptions

PL/SL facilitates their users to define their own exceptions according to the need of the program. A user-defined exception can be raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.

Syntax for user define exceptions

DECLARE  

my-exception EXCEPTION;   

PL/SQL Pre-defined Exceptions

There are many pre-defined exception in PL/SQL which are executed when any database rule is violated by the programs.

For example: NO_DATA_FOUND is a pre-defined exception which is raised when a SELECT INTO statement returns no rows.

Following is a list of some important pre-defined exceptions:

 

Exception	Oracle Error	SQL Code	Description
ACCESS_INTO_NULL	6530	-6530	It is raised when a NULL object is automatically assigned a value.
CASE_NOT_FOUND	6592	-6592	It is raised when none of the choices in the ?WHEN? clauses of a CASE statement is selected, and there is no else clause.
COLLECTION_IS_NULL	6531	-6531	It is raised when a program attempts to apply collection methods other than exists to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
DUP_VAL_ON_INDEX	1	-1	It is raised when duplicate values are attempted to be stored in a column with unique index.
INVALID_CURSOR	1001	-1001	It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER	1722	-1722	It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
LOGIN_DENIED	1017	-1017	It is raised when s program attempts to log on to the database with an invalid username or password.
NO_DATA_FOUND	1403	100	It is raised when a select into statement returns no rows.
NOT_LOGGED_ON	1012	-1012	It is raised when a database call is issued without being connected to the database.
PROGRAM_ERROR	6501	-6501	It is raised when PL/SQL has an internal problem.
ROWTYPE_MISMATCH	6504	-6504	It is raised when a cursor fetches value in a variable having incompatible data type.
SELF_IS_NULL	30625	-30625	It is raised when a member method is invoked, but the instance of the object type was not initialized.
STORAGE_ERROR	6500	-6500	It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS	1422	-1422	It is raised when a SELECT INTO statement returns more than one row.
VALUE_ERROR	6502	-6502	It is raised when an arithmetic, conversion, truncation, or size-constraint error occurs.
ZERO_DIVIDE	1476	1476	It is raised when an attempt is made to divide a number by zero.

 

PL/SQL Procedure

The PL/SQL stored procedure or simply a procedure is a PL/SQL block which performs one or more specific tasks. It is just like procedures in other programming languages.

The procedure contains a header and a body.

• Header: The header contains the name of the procedure and the parameters or variables passed to the procedure.
• Body: The body contains a declaration section, execution section and exception section similar to a general PL/SQL block.

How to pass parameters in procedure:

When you want to create a procedure or function, you have to define parameters .There is three ways to pass parameters in procedure:

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. INOUT 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.

PL/SQL Create Procedure

Syntax for creating procedure:

CREATE [OR REPLACE] PROCEDURE procedure_name  

    [ (parameter [,parameter]) ]  

IS  

    [declaration_section]  

BEGIN  

    executable_section  

[EXCEPTION  

    exception_section]  

END [procedure_name];  

Create procedure example

In this example, we are going to insert record in user table. So you need to create user table first.

Table creation:

create table user(id number(10) primary key,name varchar2(100));  

Now write the procedure code to insert record in user table.

Procedure Code:

create or replace procedure "INSERTUSER"    

(id IN NUMBER,    

name IN VARCHAR2)    

is    

begin    

insert into user values(id,name);    

end;    

/       

Output:

Procedure created.

PL/SQL program to call procedure

Let's see the code to call above created procedure.

BEGIN    

   insertuser(101,'Rahul');  

   dbms_output.put_line('record inserted successfully');    

END;    

/    

Now, see the "USER" table, you will see one record is inserted.

PL/SQL Drop Procedure

ID	Name
101	Rahul

Syntax for drop procedure

DROP PROCEDURE procedure_name;   

Example of drop procedure

DROP PROCEDURE pro1;  

PL/SQL Function

The PL/SQL Function is very similar to PL/SQL Procedure. The main difference between procedure and a function is, a function must always return a value, and on the other hand a procedure may or may not return a value. Except this, all the other things of PL/SQL procedure are true for PL/SQL function too.

Syntax to create a function:

CREATE [OR REPLACE] FUNCTION function_name [parameters]  

[(parameter_name [IN | OUT | IN OUT] type [, ...])]  

RETURN return_datatype  

{IS | AS}  

BEGIN  

   < function_body >  

END [function_name];  

Here:

Function_name: specifies the name of the function.

[OR REPLACE] option allows modifying an existing function.

The optional parameter list contains name, mode and types of the parameters.

IN represents that value will be passed from outside and OUT represents that this parameter will be used to return a value outside of the procedure.

The function must contain a return statement.

• RETURN clause specifies that data type you are going to return from the function.
• Function_body contains the executable part.
• The AS keyword is used instead of the IS keyword for creating a standalone function.

PL/SQL Function Example

Let's see a simple example to create a function.

create or replace function adder(n1 in number, n2 in number)    

return number    

is     

n3 number(8);    

begin    

n3 :=n1+n2;    

return n3;    

end;    

/    

Now write another program to call the function.

DECLARE    

   n3 number(2);    

BEGIN    

   n3 := adder(11,22);    

   dbms_output.put_line('Addition is: ' || n3);    

END;    

/    

Output:

Addition is: 33Statement processed.0.05 seconds

Another PL/SQL Function Example

Let's take an example to demonstrate Declaring, Defining and Invoking a simple PL/SQL function which will compute and return the maximum of two values.

DECLARE  

   a number;  

   b number;  

   c number;  

FUNCTION findMax(x IN number, y IN number)   

RETURN number  

IS  

    z number;  

BEGIN  

   IF x > y THEN  

      z:= x;  

   ELSE  

      Z:= y;  

   END IF;  

  

   RETURN z;  

END;   

BEGIN  

   a:= 23;  

   b:= 45;  

  

   c := findMax(a, b);  

   dbms_output.put_line(' Maximum of (23,45): ' || c);  

END;  

/  

Output:

Maximum of (23,45): 45Statement processed.0.02 seconds

PL/SQL function example using table

Let's take a customer table. This example illustrates creating and calling a standalone function. This function will return the total number of CUSTOMERS in the customers table.

Customers
Id	Name	Department	Salary
1	alex	web developer	35000
2	ricky	program developer	45000
3	mohan	web designer	35000
4	dilshad	database manager	44000

 

Create Function:

CREATE OR REPLACE FUNCTION totalCustomers  

RETURN number IS  

   total number(2) := 0;  

BEGIN  

   SELECT count(*) into total  

   FROM customers;  

    RETURN total;  

END;  

/  

After the execution of above code, you will get the following result.

Function created.

Calling PL/SQL Function:

While creating a function, you have to give a definition of what the function has to do. To use a function, you will have to call that function to perform the defined task. Once the function is called, the program control is transferred to the called function.

After the successful completion of the defined task, the call function returns program control back to the main program.

To call a function you have to pass the required parameters along with function name and if function returns a value then you can store returned value. Following program calls the function totalCustomers from an anonymous block:

DECLARE  

   c number(2);  

BEGIN  

   c := totalCustomers();  

   dbms_output.put_line('Total no. of Customers: ' || c);  

END;  

/  

After the execution of above code in SQL prompt, you will get the following result.

Total no. of Customers: 4

PL/SQL procedure successfully completed.

PL/SQL Recursive Function

You already know that a program or a subprogram can call another subprogram. When a subprogram calls itself, it is called recursive call and the process is known as recursion.

Example to calculate the factorial of a number

Let's take an example to calculate the factorial of a number. This example calculates the factorial of a given number by calling itself recursively.

DECLARE  

   num number;  

   factorial number;  

  

FUNCTION fact(x number)  

RETURN number   

IS  

   f number;  

BEGIN  

   IF x=0 THEN  

      f := 1;  

   ELSE  

      f := x * fact(x-1);  

   END IF;  

RETURN f;  

END;  

  

BEGIN  

   num:= 6;  

   factorial := fact(num);  

   dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);  

END;  

/  

After the execution of above code at SQL prompt, it produces the following result.

Factorial 6 is 720

PL/SQL procedure successfully completed.

PL/SQL Drop Function

Syntax for removing your created function:

If you want to remove your created function from the database, you should use the following syntax.

DROP FUNCTION function_name;  

 

PL/SQL Cursor

When an SQL statement is processed, Oracle creates a memory area known as context area. A cursor is a pointer to this context area. It contains all information needed for processing the statement. In PL/SQL, the context area is controlled by Cursor. A cursor contains information on a select statement and the rows of data accessed by it.

A cursor is used to referred to a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors:

Implicit Cursors

Explicit Cursors

1) PL/SQL Implicit Cursors

The implicit cursors are automatically generated by Oracle while an SQL statement is executed, if you don?t use an explicit cursor for the statement.

These are created by default to process the statements when DML statements like INSERT, UPDATE, DELETE etc. are executed.

Orcale provides some attributes known as Implicit cursor?s attributes to check the status of DML operations. Some of them are: %FOUND, %NOTFOUND, %ROWCOUNT and %ISOPEN.

For example: When you execute the SQL statements like INSERT, UPDATE, DELETE then the cursor attributes tell whether any rows are affected and how many have been affected. If you run a SELECT INTO statement in PL/SQL block, the implicit cursor attribute can be used to find out whether any row has been returned by the SELECT statement. It will return an error if there no data is selected.

Attribute	Description
%FOUND	Its return value is TRUE if DML statements like INSERT, DELETE and UPDATE affect at least one row or more rows or a SELECT INTO statement returned one or more rows. Otherwise it returns FALSE.
%NOTFOUND	Its return value is TRUE if DML statements like INSERT, DELETE and UPDATE affect no row, or a SELECT INTO statement return no rows. Otherwise it returns FALSE. It is a just opposite of %FOUND.
%ISOPEN	It always returns FALSE for implicit cursors, because the SQL cursor is automatically closed after executing its associated SQL statements.
%ROWCOUNT	It returns the number of rows affected by DML statements like INSERT, DELETE, and UPDATE or returned by a SELECT INTO statement.

 

The following table soecifies the status of the cursor with each of its attribute.

PL/SQL Implicit Cursor Example

Create customers table and have records:

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	23	Allahabad	20000
2	Suresh	22	Kanpur	22000
3	Mahesh	24	Ghaziabad	24000
4	Chandan	25	Noida	26000
5	Alex	21	Paris	28000
6	Sunita	20	Delhi	30000

Let's execute the following program to update the table and increase salary of each customer by 5000. Here, SQL%ROWCOUNT attribute is used to determine the number of rows affected:

Create procedure:

DECLARE   

   total_rows number(2);  

BEGIN  

   UPDATE  customers  

   SET salary = salary + 5000;  

   IF sql%notfound THEN  

      dbms_output.put_line('no customers updated');  

   ELSIF sql%found THEN  

      total_rows := sql%rowcount;  

      dbms_output.put_line( total_rows || ' customers updated ');  

   END IF;   

END;  

/  

Output:

6 customers updated

PL/SQL procedure successfully completed.

Now, if you check the records in customer table, you will find that the rows are updated.

select * from customers;  

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	23	Allahabad	25000
2	Suresh	22	Kanpur	27000
3	Mahesh	24	Ghaziabad	29000
4	Chandan	25	Noida	31000
5	Alex	21	Paris	33000
6	Sunita	20	Delhi	35000

2) PL/SQL Explicit Cursors

The Explicit cursors are defined by the programmers to gain more control over the context area. These cursors 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.

Following is the syntax to create an explicit cursor:

Syntax of explicit cursor

Following is the syntax to create an explicit cursor:

CURSOR cursor_name IS select_statement;;  

Steps:

You must follow these steps while working with an explicit cursor.

1. Declare the cursor to initialize in the memory.
2. Open the cursor to allocate memory.
3. Fetch the cursor to retrieve data.
4. Close the cursor to release allocated memory.

1) Declare the cursor:

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

Syntax for explicit cursor decleration

CURSOR name IS  

 SELECT statement;   

2) Open the cursor:

It is used to allocate memory for the cursor and make it easy to fetch the rows returned by the SQL statements into it.

Syntax for cursor open:

OPEN cursor_name;  

3) Fetch the cursor:

It is used to access one row at a time. You can fetch rows from the above-opened cursor as follows:

Syntax for cursor fetch:

FETCH cursor_name INTO variable_list;  

4) Close the cursor:

It is used to release the allocated memory. The following syntax is used to close the above-opened cursors.

Syntax for cursor close:

Close cursor_name;  

PL/SQL Explicit Cursor Example

Explicit cursors are defined by programmers to gain more control over the context area. It is defined in the declaration section of the PL/SQL block. It is created on a SELECT statement which returns more than one row.

Let's take an example to demonstrate the use of explicit cursor. In this example, we are using the already created CUSTOMERS table.

Create customers table and have records:

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	23	Allahabad	20000
2	Suresh	22	Kanpur	22000
3	Mahesh	24	Ghaziabad	24000
4	Chandan	25	Noida	26000
5	Alex	21	Paris	28000
6	Sunita	20	Delhi	30000

Create procedure:

Execute the following program to retrieve the customer name and address.

DECLARE  

   c_id customers.id%type;  

   c_name customers.name%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;  

/  

Output:

1  Ramesh  Allahabad

2  Suresh  Kanpur

3  Mahesh  Ghaziabad

4  Chandan  Noida

5  Alex  Paris

6  Sunita  Delhi

PL/SQL procedure successfully completed.

Package

PL/SQL packages are schema objects that groups logically related PL/SQL types, variables and subprograms.

A package will have two mandatory parts:

• Package specification
• Package body or definition

Package Specification

The specification is the interface to the package. It just DECLARES the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. In other words, it contains all information about the content of the package, but excludes the code for the subprograms.

All objects placed in the specification are called public objects. Any subprogram not in the package specification but coded in the package body is called aprivate object.

The following code snippet shows a package specification having a single procedure. You can have many global variables defined and multiple procedures or functions inside a package.

CREATE PACKAGE cust_sal AS   PROCEDURE find_sal(c_idcustomers.id%type);ENDcust_sal;/

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

Package created.

Package Body

The package body has the codes for various methods declared in the package specification and other private declarations, which are hidden from code outside the package.

The CREATE PACKAGE BODY Statement is used for creating the package body. The following code snippet shows the package body declaration for the cust_salpackage created above. I assumed that we already have CUSTOMERS table created in our database as mentioned in PL/SQL - Variables chapter.

CREATE OR REPLACE PACKAGE BODY cust_sal AS   PROCEDURE find_sal(c_idcustomers.id%TYPE) ISc_salcustomers.salary%TYPE;BEGIN      SELECT salary INTO c_sal      FROM customers      WHERE id =c_id;dbms_output.put_line('Salary: '||c_sal);ENDfind_sal;ENDcust_sal;/

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

Package body created.

Using the Package Elements

The package elements (variables, procedures or functions) are accessed with the following syntax:

package_name.element_name;

Consider, we already have created above package in our database schema, the following program uses the find_sal method of the cust_sal package:

DECLAREcodecustomers.id%type:=&cc_id;BEGINcust_sal.find_sal(code);END;/

When the above code is executed at SQL prompt, it prompt to enter customer ID and when you enter an ID, it displays corresponding salary as follows:

Enter value forcc_id:1Salary:3000 PL/SQL procedure successfully completed.

Example:

The following program provides a more complete package. We will use the CUSTOMERS table stored in our database with the following records:

Select*from customers; +----+----------+-----+-----------+----------+| ID | NAME     | AGE | ADDRESS   | SALARY   |+----+----------+-----+-----------+----------+|1|Ramesh|32|Ahmedabad|3000.00||2|Khilan|25|Delhi|3000.00||3|kaushik|23|Kota|3000.00||4|Chaitali|25|Mumbai|7500.00||5|Hardik|27|Bhopal|9500.00||6|Komal|22| MP        |5500.00|+----+----------+-----+-----------+----------+

THE PACKAGE SPECIFICATION:

CREATE OR REPLACE PACKAGE c_package AS--Adds a customer   PROCEDURE addCustomer(c_idcustomers.id%type,c_namecustomers.name%type,c_agecustomers.age%type,c_addrcustomers.address%type,c_salcustomers.salary%type); --Removes a customer   PROCEDURE delCustomer(c_idcustomers.id%TYPE);--Lists all customers   PROCEDURE listCustomer; ENDc_package;/

When the above code is executed at SQL prompt, it creates the above package and displays the following result:

Package created.

CREATING THE PACKAGE BODY:

CREATE OR REPLACE PACKAGE BODY c_package AS   PROCEDURE addCustomer(c_idcustomers.id%type,c_namecustomers.name%type,c_agecustomers.age%type,c_addrcustomers.address%type,c_salcustomers.salary%type)   ISBEGIN      INSERT INTO customers (id,name,age,address,salary)VALUES(c_id,c_name,c_age,c_addr,c_sal);ENDaddCustomer;    PROCEDURE delCustomer(c_idcustomers.id%type) ISBEGIN       DELETE FROM customers         WHERE id =c_id;ENDdelCustomer;    PROCEDURE listCustomer IS   CURSOR c_customersisSELECT  name FROM customers;   TYPE c_listis TABLE OF customers.name%type;name_listc_list:=c_list();counter integer :=0;BEGIN      FOR n IN c_customers LOOPcounter:= counter +1;name_list.extend;name_list(counter):= n.name;dbms_output.put_line('Customer('||counter||')'||name_list(counter));END LOOP;ENDlistCustomer;ENDc_package;/

Above example makes use of nested table which we will discuss in the next chapter. When the above code is executed at SQL prompt, it produces the following result:

Package body created.

USING THE PACKAGE:

The following program uses the methods declared and defined in the packagec_package.

DECLAREcodecustomers.id%type:=8;BEGINc_package.addcustomer(7,'Rajnish',25,'Chennai',3500);c_package.addcustomer(8,'Subham',32,'Delhi',7500);c_package.listcustomer;c_package.delcustomer(code);c_package.listcustomer;END;/

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

Customer(1):RameshCustomer(2):KhilanCustomer(3):kaushikCustomer(4):ChaitaliCustomer(5):HardikCustomer(6):KomalCustomer(7):RajnishCustomer(8):SubhamCustomer(1):RameshCustomer(2):KhilanCustomer(3):kaushikCustomer(4):ChaitaliCustomer(5):HardikCustomer(6):KomalCustomer(7):Rajnish PL/SQL procedure successfully completed

 

PL/SQL Trigger

Trigger is invoked by Oracle engine automatically whenever a specified event occurs.Trigger is stored into database and invoked repeatedly, when specific condition match.

Triggers are stored programs, which are automatically executed or fired when some event occurs.

Triggers are written to be executed in response to any of the following events.

• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
• A database definition (DDL) statement (CREATE, ALTER, or DROP).
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

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

Advantages of Triggers

These are the following advantages of Triggers:

• Trigger generates some derived column values automatically
• Enforces referential integrity
• Event logging and storing information on table access
• Auditing
• Synchronous replication of tables
• Imposing security authorizations
• Preventing invalid transactions

Creating a trigger:

Syntax for creating 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)    

DECLARE  

   Declaration-statements  

BEGIN   

   Executable-statements  

EXCEPTION  

   Exception-handling-statements  

END;  

Here,

1. CREATE [OR REPLACE] TRIGGER trigger_name: It creates or replaces an existing trigger with the trigger_name.
2. BEFORE | AFTER | INSTEAD OF} : This specifies when the trigger would be executed. The INSTEAD OF clause is used for creating trigger on a view.
3. {INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML operation.
4. [OF col_name]: This specifies the column name that would be updated.
5. [ON table_name]: This specifies the name of the table associated with the trigger.

[REFERENCING OLD AS o NEW AS n]: This allows you to refer new and old values for various DML statements, like INSERT, UPDATE, and DELETE.

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

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

PL/SQL Trigger Example

Let's take a simple example to demonstrate the trigger. In this example, we are using the following CUSTOMERS table:

Create table and have records:

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	23	Allahabad	20000
2	Suresh	22	Kanpur	22000
3	Mahesh	24	Ghaziabad	24000
4	Chandan	25	Noida	26000
5	Alex	21	Paris	28000
6	Sunita	20	Delhi	30000

Create trigger:

Let's take a program to create a row level trigger for the CUSTOMERS table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between the old values and new values:

CREATE OR REPLACE TRIGGER display_salary_changes  

BEFORE DELETE OR INSERT OR UPDATE ON customers  

FOR EACH ROW  

WHEN (NEW.ID > 0)  

DECLARE  

   sal_diff number;  

BEGIN  

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

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

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

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

END;  

/  

After the execution of the above code at SQL Prompt, it produces the following result.

Trigger created.

Check the salary difference by procedure:

Use the following code to get the old salary, new salary and salary difference after the trigger created.

DECLARE   

   total_rows number(2);  

BEGIN  

   UPDATE  customers  

   SET salary = salary + 5000;  

   IF sql%notfound THEN  

      dbms_output.put_line('no customers updated');  

   ELSIF sql%found THEN  

      total_rows := sql%rowcount;  

      dbms_output.put_line( total_rows || ' customers updated ');  

   END IF;   

END;  

/  

Output:

Old salary: 20000

New salary: 25000

Salary difference: 5000

Old salary: 22000

New salary: 27000

Salary difference: 5000

Old salary: 24000

New salary: 29000

Salary difference: 5000

Old salary: 26000

New salary: 31000

Salary difference: 5000

Old salary: 28000

New salary: 33000

Salary difference: 5000

Old salary: 30000

New salary: 35000

Salary difference: 5000

6 customers updated

Note: As many times you executed this code, the old and new both salary is incremented by 5000 and hence the salary difference is always 5000.

After the execution of above code again, you will get the following result.

Old salary: 25000

New salary: 30000

Salary difference: 5000

Old salary: 27000

New salary: 32000

Salary difference: 5000

Old salary: 29000

New salary: 34000

Salary difference: 5000

Old salary: 31000

New salary: 36000

Salary difference: 5000

Old salary: 33000

New salary: 38000

Salary difference: 5000

Old salary: 35000

New salary: 40000

Salary difference: 5000

6 customers updated

Important Points

Following are the two very important point and should be noted carefully.

1. OLD and NEW references are used for record level triggers these are not avialable for table level triggers.
2. If you want to query the table in the same trigger, then you should use the AFTER keyword, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.

Records

A PL/SQL record is a data structure that can hold data items of different kinds. Records consist of different fields, similar to a row of a database table.

For example, you want to keep track of your books in a library. You might want to track the following attributes about each book like, Title, Author, Subject, Book ID. A record containing a field for each of these items allows treating a BOOK as a logical unit and allows you to organize and represent its information in a better way.

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 andcursor-based records.

The following example would illustrate the concept of table-based records. We will be using the CUSTOMERS table we had created and used in the previous chapters:

DECLAREcustomer_reccustomers%rowtype;BEGIN   SELECT *intocustomer_rec   FROM customers   WHERE id =5; dbms_output.put_line('Customer ID: '|| customer_rec.id);dbms_output.put_line('Customer Name: '|| customer_rec.name);dbms_output.put_line('Customer Address: '||customer_rec.address);dbms_output.put_line('Customer Salary: '||customer_rec.salary);END;/

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

Customer ID:5CustomerName:HardikCustomerAddress:BhopalCustomerSalary:9000 PL/SQL procedure successfully completed.

Cursor-Based Records

The following example would illustrate the concept of cursor-based records. We will be using the CUSTOMERS table we had created and used in the previous chapters:

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

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

1Ramesh2Khilan3kaushik4Chaitali5Hardik6Komal PL/SQL procedure successfully completed.

User-Defined Records

PL/SQL provides a user-defined record type that allows you to define different record structures. Records consist of different fields. Suppose you want to keep track of your books in a library. You might want to track the following attributes about each book:

• Title
• Author
• Subject
• Book ID

Defining a Record

The record type is defined as:

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

Here is the way you would declare the Book record:

DECLARETYPE books IS RECORD(title  varchar(50),author  varchar(50),subjectvarchar(100),book_id   number);book1 books;book2 books;

Accessing Fields

To access any field of a record, we use the dot (.) operator. The member access operator is coded as a period between the record variable name and the field that we wish to access. Following is the example to explain usage of record:

DECLAREtype books is record(titlevarchar(50),authorvarchar(50),subjectvarchar(100),book_id number);book1 books;book2 books;BEGIN--Book1 specificationbook1.title  :='C Programming';book1.author :='Nuha Ali ';book1.subject :='C Programming Tutorial';   book1.book_id :=6495407; --Book2 specificationbook2.title :='Telecom Billing';book2.author :='Zara Ali';book2.subject :='Telecom Billing Tutorial';   book2.book_id :=6495700; --Print book 1 recorddbms_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 recorddbms_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;/

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

Book1title : C ProgrammingBook1author :NuhaAliBook1subject : C ProgrammingTutorialBook1book_id:6495407Book2title :TelecomBillingBook2author :ZaraAliBook2subject :TelecomBillingTutorialBook2book_id:6495700 PL/SQL procedure successfully completed.

Records as Subprogram Parameters

You can pass a record as a subprogram parameter in very similar way as you pass any other variable. You would access the record fields in the similar way as you have accessed in the above example:

DECLAREtype books is record(title  varchar(50),author  varchar(50),subjectvarchar(100),book_id   number);book1 books;book2 books; PROCEDURE printbook(book books) ISBEGINdbms_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--Book1 specificationbook1.title  :='C Programming';book1.author :='Nuha Ali ';book1.subject :='C Programming Tutorial';   book1.book_id :=6495407; --Book2 specificationbook2.title :='Telecom Billing';book2.author :='Zara Ali';book2.subject :='Telecom Billing Tutorial';   book2.book_id :=6495700; --Use procedure to print book infoprintbook(book1);printbook(book2);END;/

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

Book  title: C ProgrammingBook  author:NuhaAliBooksubject : C ProgrammingTutorialBookbook_id:6495407Booktitle :TelecomBillingBookauthor :ZaraAliBooksubject :TelecomBillingTutorialBookbook_id:6495700 PL/SQL procedure successfully completed.

 

 

 

 

Collection

A collection is an ordered group of elements having the same data type. Each element is identified by a unique subscript that represents its position in the collection.

PL/SQL provides three collection types:

• Index-by tables or Associative array
• Nested table
• Variable-size array or 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

We have already discussed varray in the chapter 'PL/SQL arrays'. In this chapter, we will discuss PL/SQL tables.

Both types of PL/SQL tables, i.e., index-by tables and nested tables have the same structure and their rows are accessed using the subscript notation. However, these two types of tables differ in one aspect; the nested tables can be stored in a database column and the index-by tables cannot.

Index-By Table

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

An index-by table is created using the following syntax. Here, we are creating an index-by table named table_name whose keys will be of subscript_typeand associated values will be of element_type

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

 

table_nametype_name;

Example:

Following example shows how to create a table to store integer values along with names and later it prints the same list of names.

DECLARE

   TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);

salary_list salary;

name   VARCHAR2(20);

BEGIN

-- adding elements to the table

salary_list('Rajnish'):=62000;

salary_list('Minakshi'):=75000;

salary_list('Martin'):=100000;

salary_list('James'):=78000;

 

-- printing the table

name:=salary_list.FIRST;

   WHILE name IS NOT null LOOP

dbms_output.put_line

('Salary of '|| name ||' is '|| TO_CHAR(salary_list(name)));

name:=salary_list.NEXT(name);

END LOOP;

END;

/

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

Salary of Rajnishis62000

Salary of Minakshiis75000

Salary of Martinis100000

Salary of Jamesis78000

 

PL/SQL procedure successfully completed.

Example:

Elements of an index-by table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as:

Select*from customers;

 

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|1|Ramesh|32|Ahmedabad|2000.00|

|2|Khilan|25|Delhi|1500.00|

|3|kaushik|23|Kota|2000.00|

|4|Chaitali|25|Mumbai|6500.00|

|5|Hardik|27|Bhopal|8500.00|

|6|Komal|22| MP        |4500.00|

+----+----------+-----+-----------+----------+

 

DECLARE

   CURSOR c_customersis

select  namefrom customers;

 

   TYPE c_list IS TABLE of customers.name%type INDEX BY binary_integer;

name_listc_list;

counter integer :=0;

BEGIN

   FOR n IN c_customers LOOP

counter:= counter +1;

name_list(counter):= n.name;

dbms_output.put_line('Customer('||counter||'):'||name_list(counter));

END LOOP;

END;

/

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

Customer(1):Ramesh

Customer(2):Khilan

Customer(3):kaushik

Customer(4):Chaitali

Customer(5):Hardik

Customer(6):Komal

 

PL/SQL procedure successfully completed

Nested Tables

A nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in the following aspects:

• 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, i.e., it always has consecutive subscripts. A nested array is dense initially, but it can become sparse when elements are deleted from it.

A nested table is created using the following syntax:

TYPE type_name IS TABLE OF element_type[NOT NULL];

 

table_nametype_name;

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

A nested table can be stored in a database column and so it could be used for simplifying SQL operations where you join a single-column table with a larger table. An associative array cannot be stored in the database.

Example:

The following examples illustrate the use of nested table:

DECLARE

   TYPE names_table IS TABLE OF VARCHAR2(10);

   TYPE grades IS TABLE OF INTEGER;

 

namesnames_table;

marks grades;

total integer;

BEGIN

names:=names_table('Kavita','Pritam','Ayan','Rishav','Aziz');

marks:= grades(98,97,78,87,92);

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;

/

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

Total5Students

Student:Kavita,Marks:98

Student:Pritam,Marks:97

Student:Ayan,Marks:78

Student:Rishav,Marks:87

Student:Aziz,Marks:92

 

PL/SQL procedure successfully completed.

Example:

Elements of a nested table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as:

Select*from customers;

 

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|1|Ramesh|32|Ahmedabad|2000.00|

|2|Khilan|25|Delhi|1500.00|

|3|kaushik|23|Kota|2000.00|

|4|Chaitali|25|Mumbai|6500.00|

|5|Hardik|27|Bhopal|8500.00|

|6|Komal|22| MP        |4500.00|

+----+----------+-----+-----------+----------+

DECLARE

   CURSOR c_customersis

SELECT  name FROM customers;

 

   TYPE c_list IS TABLE of customers.name%type;

name_listc_list:=c_list();

counter integer :=0;

BEGIN

   FOR n IN c_customers LOOP

counter:= counter +1;

name_list.extend;

name_list(counter):= n.name;

      dbms_output.put_line('Customer('||counter||'):'||name_list(counter));

END LOOP;

END;

/

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

Customer(1):Ramesh

Customer(2):Khilan

Customer(3):kaushik

Customer(4):Chaitali

Customer(5):Hardik

Customer(6):Komal

 

PL/SQL procedure successfully completed.

 

Collection Methods

 

PL/SQL provides the built-in collection methods that make collections easier to use. The following table lists the methods and their purpose:

S.N.	Method Name & Purpose
1	EXISTS(n) Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
2	COUNT Returns the number of elements that a collection currently contains.
3	LIMIT Checks the Maximum Size of a Collection.
4	FIRST Returns the first (smallest) index numbers in a collection that uses integer subscripts.
5	LAST Returns the last (largest) index numbers in a collection that uses integer subscripts.
6	PRIOR(n) Returns the index number that precedes index n in a collection.
7	NEXT(n) Returns the index number that succeeds index n.
8	EXTEND Appends one null element to a collection.
9	EXTEND(n) Appends n null elements to a collection.
10	EXTEND(n,i) Appends n copies of the ith element to a collection.
11	TRIM Removes one element from the end of a collection.
12	TRIM(n) Removes n elements from the end of a collection.
13	DELETE Removes all elements from a collection, setting COUNT to 0.
14	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.
15	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.

 

Collection Exceptions

 

The following table provides the collection exceptions and when they are raised:

Collection Exception	Raised in Situations
COLLECTION_IS_NULL	You try to operate on an atomically null collection.
NO_DATA_FOUND	A subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNT	A subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMIT	A subscript is outside the allowed range.
VALUE_ERROR	A subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.

A database transaction is an atomic unit of work that may consist of one or more related SQL statements. It is called atomic because the database modifications brought about by the SQL statements that constitute a transaction can collectively be either committed, i.e., made permanent to the database or rolled back (undone) from the database.

A successfully executed SQL statement and a committed transaction are not same. Even if an SQL statement is executed successfully, unless the transaction containing the statement is committed, it can be rolled back and all changes made by the statement(s) can be undone.

Starting an Ending a Transaction

A transaction has a beginning and an end. A transaction starts when one of the following events take place:

• The first SQL statement is performed after connecting to the database.
• At each new SQL statement issued after a transaction is completed.

A transaction ends when one of the following events take place:

• A COMMIT or a ROLLBACK statement is issued.
• A DDL statement, like CREATE TABLE statement, is issued; because in that case a COMMIT is automatically performed.
• A DCL statement, such as a GRANT statement, is issued; because in that case a COMMIT is automatically performed.
• User disconnects from the database.
• User exits from SQL*PLUS by issuing the EXIT command, a COMMIT is automatically performed.
• SQL*Plus terminates abnormally, a ROLLBACK is automatically performed.
• A DML statement fails; in that case a ROLLBACK is automatically performed for undoing that DML statement.

Committing a Transaction

A transaction is made permanent by issuing the SQL command COMMIT. The general syntax for the COMMIT command is:

COMMIT;

For example,

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)VALUES (1,'Ramesh',32,'Ahmedabad',2000.00);INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)VALUES (2,'Khilan',25,'Delhi',1500.00);INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)VALUES (3,'kaushik',23,'Kota',2000.00);INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)VALUES (4,'Chaitali',25,'Mumbai',6500.00);INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)VALUES (5,'Hardik',27,'Bhopal',8500.00);INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)VALUES (6,'Komal',22,'MP',4500.00);COMMIT;

Rolling Back Transactions

Changes made to the database without COMMIT could be undone using the ROLLBACK command.

The general syntax for the ROLLBACK command is:

ROLLBACK [TO SAVEPOINT <savepoint_name>];

When a transaction is aborted due to some unprecedented situation, like system failure, the entire transaction since a commit is automatically rolled back. If you are not using savepoint, then simply use the following statement to rollback all the changes:

ROLLBACK;

 

Savepoints

 

Savepoints are sort of markers that help in splitting a long transaction into smaller units by setting some checkpoints. By setting savepoints within a long transaction, you can roll back to a checkpoint if required. This is done by issuing the SAVEPOINT command.

The general syntax for the SAVEPOINT command is:

SAVEPOINT <savepoint_name>;

For example:

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)VALUES (7,'Rajnish',27,'HP',9500.00);INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)VALUES (8,'Riddhi',21,'WB',4500.00);SAVEPOINT sav1; UPDATE CUSTOMERSSET SALARY = SALARY +1000;ROLLBACK TO sav1; UPDATE CUSTOMERSSET SALARY = SALARY +1000WHERE ID =7;UPDATE CUSTOMERSSET SALARY = SALARY +1000WHERE ID =8;COMMIT;

Here,ROLLBACK TO sav1; statement rolls back the changes up to the point, where you had marked savepoint sav1 and after that new changes will start.

Automatic Transaction Control

To execute a COMMIT automatically whenever an INSERT, UPDATE or DELETE command is executed, you can set the AUTOCOMMIT environment variable as:

SET AUTOCOMMIT ON;

You can turn-off auto commit mode using the following command:

SET AUTOCOMMIT OFF;

PL/SQL provides two classes of date and time related data types:

• Datetime data types
• Interval data types

The Datetime data types are:

• DATE
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH LOCAL TIME ZONE

The Interval data types are:

• INTERVAL YEAR TO MONTH
• INTERVAL DAY TO SECOND

Field Values for Datetime and Interval Data Types

Both datetime and interval data types consist of fields. The values of these fields determine the value of the datatype. The following table lists the fields and their possible values for datetimes and intervals.

Field Name	Valid Datetime Values	Valid Interval Values
YEAR	-4712 to 9999 (excluding year 0)	Any nonzero integer
MONTH	01 to 12	0 to 11
DAY	01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the calendar for the locale)	Any nonzero integer
HOUR	00 to 23	0 to 23
MINUTE	00 to 59	0 to 59
SECOND	00 to 59.9(n), where 9(n) is the precision of time fractional seconds The 9(n) portion is not applicable for DATE.	0 to 59.9(n), where 9(n) is the precision of interval fractional seconds
TIMEZONE_HOUR	-12 to 14 (range accommodates daylight savings time changes) Not applicable for DATE or TIMESTAMP.	Not applicable
TIMEZONE_MINUTE	00 to 59 Not applicable for DATE or TIMESTAMP.	Not applicable
TIMEZONE_REGION	Not applicable for DATE or TIMESTAMP.	Not applicable
TIMEZONE_ABBR	Not applicable for DATE or TIMESTAMP.	Not applicable

 

The Datetime Data Types and Functions

Following are the Datetime data types:

• DATE- it stores date and time information in both character and number datatypes. It is made of information on century, year, month, date, hour, minute, and second. It is specified as:
• TIMESTAMP- it is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, along with hour, minute, and second values. It is useful for storing precise time values.
• TIMESTAMP WITH TIME ZONE- it is a variant of TIMESTAMP that includes a time zone region name or a time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC. This datatype is useful for collecting and evaluating date information across geographic regions.
• TIMESTAMP WITH LOCAL TIME ZONE- it is another variant of TIMESTAMP that includes a time zone offset in its value.

Following table provides the Datetime functions (where, x has datetime value):

S.N	Function Name & Description
1	ADD_MONTHS(x, y); Adds y months to x.
2	LAST_DAY(x); Returns the last day of the month.
3	MONTHS_BETWEEN(x, y); Returns the number of months between x and y.
4	NEXT_DAY(x, day); Returns the datetime of the next day after x.
5	NEW_TIME; Returns the time/day value from a time zone specified by the user.
6	ROUND(x [, unit]); Rounds x;
7	SYSDATE(); Returns the current datetime.
8	TRUNC(x [, unit]); Truncates x.

Timestamp functions (where, x has a timestamp value):

S.N	Function Name & Description
1	CURRENT_TIMESTAMP(); Returns a TIMESTAMP WITH TIME ZONE containing the current session time along with the session time zone.
2	EXTRACT({ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | } TIMEZONE_ABBR ) FROM x) Extracts and returns a year, month, day, hour, minute, second, or time zone from x;
3	FROM_TZ(x, time_zone); Converts the TIMESTAMP x and time zone specified by time_zone to a TIMESTAMP WITH TIMEZONE.
4	LOCALTIMESTAMP(); Returns a TIMESTAMP containing the local time in the session time zone.
5	SYSTIMESTAMP(); Returns a TIMESTAMP WITH TIME ZONE containing the current database time along with the database time zone.
6	SYS_EXTRACT_UTC(x); Converts the TIMESTAMP WITH TIMEZONE x to a TIMESTAMP containing the date and time in UTC.
7	TO_TIMESTAMP(x, [format]); Converts the string x to a TIMESTAMP.
8	TO_TIMESTAMP_TZ(x, [format]); Converts the string x to a TIMESTAMP WITH TIMEZONE.

Examples:

The following code snippets illustrate the use of the above functions:

SELECT SYSDATE FROM DUAL;

Output:

08/31/20125:25:34 PM

SELECT TO_CHAR(CURRENT_DATE,'DD-MM-YYYY HH:MI:SS') FROM DUAL;

Output:

31-08-2012 05:26:14

SELECT ADD_MONTHS(SYSDATE,5) FROM DUAL;

Output:

01/31/2013 5:26:31 PM

SELECT LOCALTIMESTAMP FROM DUAL;

Output:

8/31/20125:26:55.347000 PM

The Interval Data Types and Functions

Following are the Interval data types:

• INTERVAL YEAR TO MONTH - it stores a period of time using the YEAR and MONTH datetime fields.
• INTERVAL DAY TO SECOND - it stores a period of time in terms of days, hours, minutes, and seconds.

Interval functions:

S.N	Function Name & Description
1	NUMTODSINTERVAL(x, interval_unit); Converts the number x to an INTERVAL DAY TO SECOND.
2	NUMTOYMINTERVAL(x, interval_unit); Converts the number x to an INTERVAL YEAR TO MONTH.
3	TO_DSINTERVAL(x); Converts the string x to an INTERVAL DAY TO SECOND.
4	TO_YMINTERVAL(x); Converts the string x to an INTERVAL YEAR TO MONTH.

 

The DBMS_OUTPUT is a built-in package that enables you to display output, display debugging information, and send messages from PL/SQL blocks, subprograms, packages, and triggers. We have already used this package all throughout our tutorial.

Let us look at a small code snippet that would display all the user tables in the database. Try it in your database to list down all the table names:

BEGINdbms_output.put_line(user ||' Tables in the database:');   FOR t IN (SELECT table_name FROM user_tables)   LOOPdbms_output.put_line(t.table_name);END LOOP;END;/

DBMS_OUTPUT Subprograms

The DBMS_OUTPUT package has the following subprograms:

S.N	Subprogram & Purpose
1	DBMS_OUTPUT.DISABLE; Disables message output
2	DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000); Enables message output. A NULL value of buffer_size represents unlimited buffer size.
3	DBMS_OUTPUT.GET_LINE (line OUT VARCHAR2, status OUT INTEGER); Retrieves a single line of buffered information.
4	DBMS_OUTPUT.GET_LINES (lines OUT CHARARR, numlines IN OUT INTEGER); Retrieves an array of lines from the buffer.
5	DBMS_OUTPUT.NEW_LINE; Puts an end-of-line marker
6	DBMS_OUTPUT.PUT(item IN VARCHAR2); Places a partial line in the buffer.
7	DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2); Places a line in the buffer.

Example:

DECLARElinesdbms_output.chararr;num_lines number;BEGIN-- enable the buffer withdefault size 20000dbms_output.enable; dbms_output.put_line('Hello Reader!');dbms_output.put_line('Hope you have enjoyed the tutorials!');dbms_output.put_line('Have a great time exploring pl/sql!'); num_lines:=3; dbms_output.get_lines(lines,num_lines);    FOR i IN 1..num_lines LOOPdbms_output.put_line(lines(i));END LOOP;END;/

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

HelloReader!Hope you have enjoyed the tutorials!Have a great time exploring pl/sql! PL/SQL procedure successfully completed.

 

Object Oriented Programming Structure:

PL/SQL allows defining an object type, which helps in designing object-oriented database in Oracle. An object type allows you to crate composite types. Using objects allow you implementing 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 behaviors.

Objects are created using the CREATE [OR REPLACE] TYPE statement. Below is an example to create a simple address object consisting of few attributes:

CREATE OR REPLACE TYPE addressAS OBJECT

(house_novarchar2(10),

street varchar2(30),

city varchar2(20),

state varchar2(10),

pincode varchar2(10)

);

/

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

Type created.

Let's create one more object customer where we will wrap attributes andmethods together to have object oriented feeling:

CREATE OR REPLACE TYPE customer AS OBJECT

(code number(5),

name varchar2(30),

contact_novarchar2(12),

addr address,

member procedure display

);

/

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

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('103A','M.G.Road','Jaipur','Rajasthan','201301');

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;

/

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

HouseNo:103A

Street:M.G.Road

City:Jaipur

State:Rajasthan

Pincode:201301

 

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('103A','M.G.Road','Jaipur','Rajasthan','201301');

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

• Map method: The Map methodis 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 and one. So the relationship between these two objects would depend upon the value of code.
• Order method: The Order methodsimplement 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 above concepts using the following rectangle object:

CREATE OR REPLACE TYPE rectangle AS OBJECT

(length number,

width number,

memberfunction enlarge(inc number)return rectangle,

member procedure display,

map member function measure return number

);

/

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

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;

/

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

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;

/

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

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

);

/

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

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;

/

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

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;

/

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

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 existing base objects. To implement inheritance, the base objects should be declared as NOT FINAL. The default is FINAL.

The following programs illustrate inheritance in PL/SQL Objects. Let us create another object named TableTop, which is inheriting from the Rectangle object. Creating the base rectangle object:

CREATE OR REPLACE TYPE rectangle AS OBJECT

(length number,

width number,

memberfunction enlarge(inc number)return rectangle,

 NOT FINAL member procedure display) NOT FINAL

/

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

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;

/

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

Type body created.

Creating the child object tabletop:

CREATE OR REPLACE TYPE tabletop UNDER rectangle

(

material varchar2(20),

   OVERRIDING member procedure display

)

/

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

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;

/

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

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;

/

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

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

/

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

Type created.