HI - TECH COMPUTERS

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.

Number
SQL> Set Serveroutput On;

H
SQL> Declare
2 a Number :=30;
3 b Number :=20;

C
4 c Number;
5 Begin
6 c:=a+b;
TE
7 Dbms_Output.Put_Line('The c value is : '||c);
8 End;
9 /
The c value is : 50

Float
I-

PL/SQL procedure successfully completed.

SQL> Declare
2 a Float :=30;
H

3 b Float :=20.23;
4 c Float;
5 Begin
6 c:=a+b;
7 Dbms_Output.Put_Line('The c value is : '||c);
8 End;
9 /
The c value is : 50.23
PL/SQL 1
 HI - TECH COMPUTERS

PL/SQL procedure successfully completed.

Char
SQL> Declare
2 a Varchar2(2) :='H';
3 Begin
4 Dbms_Output.Put_Line('The a value is : '||a);
5 End;
6 /

H
The a value is : H

PL/SQL procedure successfully completed.

C
String

SQL> Declare
TE
2 a Varchar2(10) :='HI-TECH';
3 Begin
4 Dbms_Output.Put_Line('The a value is : '||a);
5 End;
6 /
The a value is : HI-TECH

PL/SQL procedure successfully completed.

I-

User Input
SQL> Declare
2 a Number;
H

3 b Number;
4 c Number;
5 Begin
6 a:=&a;
7 b:=&b;
8 c:=a+b;
9 Dbms_Output.Put_Line('The c value is : '||c);
10 End;
PL/SQL 2
 HI - TECH COMPUTERS
11 /

Enter value for a: 5

old 6: a:=&a;
new 6: a:=5;
Enter value for b: 6
old 7: b:=&b;

H
new 7: b:=6;
The c value is : 11

C
PL/SQL procedure successfully completed.
TE
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.

I-

Syntax: (IF-THEN statement):

1. IF condition
2. THEN
H

3. Statement: {It is executed when condition is true}

4. END IF;

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

Syntax: (IF-THEN-ELSE statement):

1. IF condition
2. THEN

PL/SQL 3
 HI - TECH COMPUTERS
3. {...statements to execute when condition is TRUE...}
4. ELSE
5. {...statements to execute when condition is FALSE...}
6. 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):

H
1. IF condition1
2. THEN
3. {...statements to execute when condition1 is TRUE...}

C
4. ELSIF condition2
5. THEN
6. {...statements to execute when condition2 is TRUE...}
TE
7. 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):

1. IF condition1
I-

2. THEN
3. {...statements to execute when condition1 is TRUE...}
4. ELSIF condition2
5. THEN
H

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

7. ELSE
8. {...statements to execute when both condition1 and condition2 are
FALSE...}
9. 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

PL/SQL 4
 HI - TECH COMPUTERS
condition2 is TRUE or a different set of statements when both the
condition1 and condition2 are FALSE.

Example of PL/SQL If Statement

H
SQL> Set Serveroutput On;
SQL> Declare
2 a Number(3):=500;

C
3 Begin
4 If(a<20)
5 Then
6 Dbms_Output.Put_Line('a is Less Then 20');
TE
7 Else
8 Dbms_Output.Put_Line('a is Not Less Then 20');
9 End If;
10 Dbms_Output.Put_Line('Value of a is: '||a);
11 End;
12 /
a is Not Less Then 20
Value of a is: 500

PL/SQL procedure successfully completed.

I-
H

PL/SQL 5
 HI - TECH COMPUTERS
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:

H
1. CASE [ expression ]
2. WHEN condition_1 THEN result_1

C
3. WHEN condition_2 THEN result_2
4. ...
5. WHEN condition_n THEN result_n
TE
6. ELSE result
7. END

Example of PL/SQL case statement

SQL> Declare
2 Grade Char(1):='A';
I-

3 Begin
4 Case Grade
5 When 'A'
6 Then
7 Dbms_Output.Put_Line('Excellent');
8 When 'B'
H

9 Then
10 Dbms_Output.Put_Line('Very Good');
11 When 'C'
12 Then
13 Dbms_Output.Put_Line('Good');
14 When 'D'
15 Then
16 Dbms_Output.Put_Line('Average');
17 When 'F'
18 Then
19 Dbms_Output.Put_Line('Passed');
PL/SQL 6
 HI - TECH COMPUTERS
20 Else
21 Dbms_Output.Put_Line('Fail');
22 End Case;
23 End;
24 /
Excellent

PL/SQL procedure successfully completed.

H
C
TE
I-
H

PL/SQL 7
 HI - TECH COMPUTERS
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:

1. LOOP
2. Sequence of statements;
3. END LOOP;

H
Types of PL/SQL Loops
There are 4 types of PL/SQL Loops.

C
Basic Loop / Exit Loop

While Loop
TE
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.
I-

Syntax of basic loop:

1. LOOP
2. Sequence of statements;
H

3. END LOOP;

Syntax of exit loop:

1. LOOP
2. statements;
3. EXIT;
4. {or EXIT WHEN condition;}

PL/SQL 8
 HI - TECH COMPUTERS
5. END LOOP;

Example of PL/SQL EXIT Loop

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

1. DECLARE
2. i NUMBER := 1;
3. BEGIN
4. LOOP

H
5. EXIT WHEN i>10;
6. DBMS_OUTPUT.PUT_LINE(i);
7. i := i+1;

C
8. END LOOP;
9. END;
TE
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 the EXIT WHEN statement to exit from the Loop.
Otherwise the EXIT statement without WHEN condition, the statements
in the Loop is executed only once.
I-

PL/SQL EXIT Loop Example 2

1. DECLARE
H

2. VAR1 NUMBER;
3. VAR2 NUMBER;
4. BEGIN
5. VAR1:=100;
6. VAR2:=1;
7. LOOP
8. DBMS_OUTPUT.PUT_LINE (VAR1*VAR2);

PL/SQL 9
 HI - TECH COMPUTERS
9. IF (VAR2=10) THEN
10. EXIT;
11. END IF;
12. VAR2:=VAR2+1;
13. END LOOP;
14. END;

H
C
TE
I-
H

PL/SQL 10
 HI - TECH COMPUTERS
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:

1. WHILE <condition>
2. LOOP statements;
3. END LOOP;

H
Example of PL/SQL While Loop

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

1. DECLARE
2. i INTEGER := 1;
TE
3. BEGIN
4. WHILE i <= 10 LOOP
5. DBMS_OUTPUT.PUT_LINE(i);
6. i := i+1;
7. END LOOP;
8. END;
I-

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

● 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 11
 HI - TECH COMPUTERS

PL/SQL WHILE Loop Example 2

1. DECLARE
2. VAR1 NUMBER;
3. VAR2 NUMBER;

H
4. BEGIN
5. VAR1:=200;
6. VAR2:=1;

C
7. WHILE (VAR2<=10)
8. LOOP
9. DBMS_OUTPUT.PUT_LINE (VAR1*VAR2);
TE
10. VAR2:=VAR2+1;
11. END LOOP;
12. END;
I-
H

PL/SQL 12
 HI - TECH COMPUTERS
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:

1. FOR counter IN initial_value .. final_value LOOP

2. LOOP statements;
3. END LOOP;

H
● initial_value : Start integer value

● final_value : End integer value

PL/SQL For Loop Example 1

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

1. BEGIN
2. FOR k IN 1..10 LOOP
3. -- note that k was not declared
4. DBMS_OUTPUT.PUT_LINE(k);
5. END LOOP;
I-

6. END;

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

● You don't need to declare the counter variable explicitly because it

H

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 13
 HI - TECH COMPUTERS

H
C
TE
I-
H

PL/SQL 14
 HI - TECH COMPUTERS
PL/SQL For Loop Example 2
1. DECLARE
2. VAR1 NUMBER;
3. BEGIN
4. VAR1:=10;
5. FOR VAR2 IN 1..10
6. LOOP
7. DBMS_OUTPUT.PUT_LINE (VAR1*VAR2);

H
8. END LOOP;
9. END;

C
PL/SQL For Loop REVERSE Example 3
Let's see an example of PL/SQL for loop where we are using REVERSE
keyword.
TE
1. DECLARE
2. VAR1 NUMBER;
3. BEGIN
4. VAR1:=10;
5. FOR VAR2 IN REVERSE 1..10
6. LOOP
I-

7. DBMS_OUTPUT.PUT_LINE (VAR1*VAR2);
8. END LOOP;
9. END;
H

PL/SQL 15
 HI - TECH COMPUTERS
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).

H
SQL> BEGIN

C
2 FOR i IN 1 .. 5 LOOP

3 IF i = 3 THEN
TE
4 CONTINUE;

5 END IF;

6 DBMS_OUTPUT.PUT_LINE('Iteration # ' || i);

7 END LOOP;

8 END;

9 /
I-

Iteration # 1

Iteration # 2

Iteration # 4
H

Iteration # 5

PL/SQL procedure successfully completed.

PL/SQL 16
 HI - TECH COMPUTERS
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:

1. GOTO label_name;

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

C
1. GOTO label_name;
2. ..
TE
3. ..
4. <<label_name>>
5. Statement;

Example of PL/SQL GOTO statement

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

1. DECLARE
2. a number(2) := 30;
3. BEGIN
H

4. <<loopstart>>
5. -- while loop execution
6. WHILE a < 50 LOOP
7. dbms_output.put_line ('value of a: ' || a);
8. a := a + 1;
9. IF a = 35 THEN
10. a := a + 1;
11. GOTO loopstart;
PL/SQL 17
 HI - TECH COMPUTERS
12. END IF;
13. END LOOP;
14. END;
15. /

H
C
TE
I-
H

PL/SQL 18
 HI - TECH COMPUTERS
Oracle Procedures
A procedure is a group of PL/SQL statements that can be called by name.
The call specification (sometimes called call spec) specifies a java
method or a third-generation language routine so that it can be called
from SQL and PL/SQL.

Create Procedure
Syntax

1. CREATE [OR REPLACE] PROCEDURE procedure_name

H
2. [ (parameter [,parameter]) ]
3. AS

C
4. [declaration_section]
5. BEGIN
6. executable_section
TE
7. [EXCEPTION
8. exception_section]
9. END [procedure_name];

Following are the three types of procedures that must be defined to create
a procedure.

● IN: It is a default parameter. It passes the value to the subprogram.

I-

● OUT: It must be specified. It returns a value to the caller.

● IN OUT: It must be specified. It passes an initial value to the

subprogram and returns an updated value to the caller.
H

SQL> Create Or Replace Procedure Hitech (x In Number,y In Number,z Out

Number) As
2 Begin
3 z:=x+y;
4 End;
5 /

Procedure created.

PL/SQL 19
 HI - TECH COMPUTERS

SQL> Variable Sum Number;

SQL> Execute Hitech (10,2,:Sum);

PL/SQL procedure successfully completed.

SQL> print Sum;

SUM
----------
12

SQL> Execute Hitech (10,20,:Sum);

H
PL/SQL procedure successfully completed.

SQL> print Sum;

C
SUM
----------
30
TE
SQL> Create Table Yes
2 (
3 Id Number(10),
4 Name Varchar2(10)
5 );

Table created.
I-

SQL> Insert Into Yes Values

2 (1,'Hari');

1 row created.
H

SQL> Insert Into Yes Values

2 (2,'Ram');

1 row created.

SQL> Select * From Yes;

ID NAME
---------- ----------
1 Hari
2 Ram
PL/SQL 20
 HI - TECH COMPUTERS

SQL> Create Procedure HitechYes(EId In Number,EName In Varchar2) As

2 Begin
3 Insert Into Yes Values(EId,EName);
4 End;
5 /

Procedure created.

SQL> Execute HitechYes(3,'Suma');

PL/SQL procedure successfully completed.

H
SQL> Select * From Yes;

ID NAME
---------- ----------

C
1 Hari
2 Ram
3 Suma
TE
I-
H

PL/SQL 21
 HI - TECH COMPUTERS
Oracle Function
A function is a subprogram that is used to return a single value. You must
declare and define a function before invoking it. It can be declared and
defined at the same time or can be declared first and defined later in the
same block.
CREATE function in Oracle
Syntax
CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]

H
BEGIN
executable_section
[EXCEPTION
exception_section]

C
END [function_name];

You must have define some parametrs before creating a procedure or a

function. These parameters are
TE
IN: It is a default parameter. It passes the value to the subprogram.
OUT: It must be specified. It returns a value to the caller.
IN OUT: It must be specified. It passes an initial value to the subprogram
and returns an updated value to the caller.

SQL> Create Table Student

2 (
3 Id Number(10),
4 Name Varchar2(10),
I-

5 Tel Number(10),
6 Eng Number(10)
7 );

Table created.
H

SQL> Insert Into Student Values

2 (1,'Guna',35,75);

1 row created.

SQL> Insert Into Student Values

2 (2,'Raju',45,65);

1 row created.

SQL> Select * From Student;

PL/SQL 22
 HI - TECH COMPUTERS

ID NAME TEL ENG

---------- ---------- ---------- ----------
1 Guna 35 75
2 Raju 45 65

SQL> Create Or Replace Function Ave(M1 In Number,M2 In Number) Return

Number As
2 Mark1 Number;
3 Mark2 Number;
4 Begin
5 Select Tel Into Mark1 From Student Where ID=M1;
6 Select Eng Into Mark2 From Student Where ID=M2;

H
7 Return (Mark1+Mark2)/2;
8 End;
9 /

C
Function created.

SQL> Select Ave (1,1) From Dual;

TE
AVE(1,1)
----------
55
I-
H

PL/SQL 23
 HI - TECH COMPUTERS
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:

H
● Implicit Cursors

● Explicit Cursors

C
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
TE
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,
I-

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

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

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

PL/SQL 24
 HI - TECH COMPUTERS
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

H
SQL cursor is automatically closed after executing its
associated SQL statements.

%ROWCOUNT It returns the number of rows affected by DML statements

C
like INSERT, DELETE, and UPDATE or returned by a SELECT
INTO statement.
TE
SQL> Create Table Curser
2 (
3 Id Number(5),
4 Name Varchar2(10),
5 Age Number(5),
6 Address Varchar2(10),
7 salary Number(5)
I-

8 );

Table created.

SQL> insert into curser values

2 (1,'Ramesh',23,'Puttur',20000);
H

1 row created.

SQL> insert into curser values

2 (2,'Suresh',22,'Puttur',22000);

1 row created.

SQL> insert into curser values

2 (3,'Mahesh',24,'Nagari',24000);

PL/SQL 25
 HI - TECH COMPUTERS
1 row created.

SQL> insert into curser values

2 (4,'Chandra',25,'Chittoor',26000);

1 row created.

SQL> insert into curser values

2 (5,'Alex',23,'Chittoor',28000);

1 row created.

SQL> select * from Curser;

H
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Ramesh 23 Puttur 20000

C
2 Suresh 22 Puttur 22000
3 Mahesh 24 Nagari 24000
4 Chandra 25 Chittoor 26000
5 Alex 23 Chittoor 28000
TE
SQL> Set Serveroutput on;
SQL> Declare
2 total_rows number(2);
3 Begin
4 Update Curser
5 Set salary = salary+5000;
6 if sql%notfound Then
7 dbms_output.put_line('No Curser Update');
8 elsif sql%found Then
I-

9 total_rows:=sql%rowcount;
10 dbms_output.put_line(total_rows||'Curser Updated');
11 end if;
12 end;
13 /
H

5Curser Updated

PL/SQL procedure successfully completed.

SQL> select * from curser;

ID NAME AGE ADDRESS SALARY

---------- ---------- ---------- ---------- ----------
1 Ramesh 23 Puttur 25000
2 Suresh 22 Puttur 27000
3 Mahesh 24 Nagari 29000
PL/SQL 26
 HI - TECH COMPUTERS
4 Chandra 25 Chittoor 31000
5 Alex 23 Chittoor 33000

H
C
TE
I-
H

PL/SQL 27
 HI - TECH COMPUTERS
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:

H
1. CURSOR cursor_name IS select_statement;;

Steps:

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

1. Declare the cursor to initialize in the memory.

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

I-

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

Syntax for explicit cursor decleration

1. CURSOR name IS
H

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

1. OPEN cursor_name;

PL/SQL 28
 HI - TECH COMPUTERS
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:

1. FETCH cursor_name INTO variable_list;

4) Close the cursor:

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

Syntax for cursor close:

C
1. Close cursor_name;
TE
SQL> Create Table Curser

2 (

3 Id Number(5),

4 Name Varchar2(10),

5 Age Number(5),

6 Address Varchar2(10),
I-

7 Salary Number(5)

8 );
H

Table created.

SQL> Insert Into Curser Values

2 (1,'Ramesh',23,'Puttur',25000);

1 row created.

PL/SQL 29
 HI - TECH COMPUTERS

SQL> Insert Into Curser Values

2 (2,'Suresh',22,'Puttur',27000);

1 row created.

SQL> Insert Into Curser Values

H
2 (3,'Mahesh',24,'Nagari',29000);

1 row created.

SQL> Insert Into Curser Values

C
TE
2 (4,'Chandra',25,'Chittoor',31000);

1 row created.

SQL> Insert Into Curser Values

I-

2 (5,'Alex',23,'Chittoor',33000);

1 row created.
H

SQL> Select * From Curser;

ID NAME AGE ADDRESS SALARY

---------- ---------- ---------- ---------- ----------

1 Ramesh 23 Puttur 25000

2 Suresh 22 Puttur 27000

PL/SQL 30
 HI - TECH COMPUTERS
3 Mahesh 24 Nagari 29000

4 Chandra 25 Chittoor 31000

5 Alex 23 Chittoor 33000

SQL> Set Serveroutput On;

SQL> DECLARE

H
2 c_id [Link]%type;

3 c_name [Link]%type;

4 c_addr [Link]%type;

C
5 Cursor c_Curser is

6 Select id,Name,Address From Curser;

TE
7 Begin

8 Open c_Curser;

9 Loop

10 Fetch C_Curser Into c_id,c_name,c_addr;

11 Exit When c_Curser%notfound;

I-

12 Dbms_Output.put_Line(c_id||''||c_name||''||c_addr);

13 End Loop;

14 Close c_Curser;
H

15 End;

16 /

1RameshPuttur

2SureshPuttur

3MaheshNagari

4ChandraChittoor

5AlexChittoor
PL/SQL 31
 HI - TECH COMPUTERS

PL/SQL procedure successfully completed.

H
C
TE
I-
H

PL/SQL 32
 HI - TECH COMPUTERS

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:

H
● System-defined Exceptions

● User-defined Exceptions

PL/SQL Exception Handling

C
TE
Following is a general syntax for exception handling:

1. DECLARE
2. <declarations section>
3. BEGIN
4. <executable command(s)>
5. EXCEPTION
I-

6. <exception handling goes here >

7. WHEN exception1 THEN
8. exception1-handling-statements
9. WHEN exception2 THEN
H

10. exception2-handling-statements
11. WHEN exception3 THEN
12. exception3-handling-statements
13. ........
14. WHEN others THEN
15. exception3-handling-statements
16. END;

PL/SQL 33
 HI - TECH COMPUTERS

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

---------- ---------- ---------- ---------- ----------

H
1 Ramesh 23 Puttur 25000

2 Suresh 22 Puttur 27000

C
3 Mahesh 24 Nagari 29000

4 Chandra 25 Chittoor 31000

5 Alex 23 Chittoor 33000

TE
DECLARE

1. c_id [Link]%type := 8;
2. c_name [Link]%type;
3. c_addr [Link]%type;
4. BEGIN
5. SELECT name, address INTO c_name, c_addr
I-

6. FROM customers
7. WHERE id = c_id;
8. DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
H

9. DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);

10. EXCEPTION
11. WHEN no_data_found THEN
12. dbms_output.put_line('No such customer!');
13. WHEN others THEN
14. dbms_output.put_line('Error!');
15. END;
16. /

PL/SQL 34
 HI - TECH COMPUTERS

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

No such customer!

PL/SQL procedure successfully completed.

H
1. DECLARE
2. c_id [Link]%type := 5;

C
TE
3. c_name [Link]%type;
4. c_addr [Link]%type;
5. BEGIN
6. SELECT name, address INTO c_name, c_addr
7. FROM customers
8. WHERE id = c_id;
I-

9. DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);

10. DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
11. EXCEPTION
12. WHEN no_data_found THEN
H

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

14. WHEN others THEN
15. dbms_output.put_line('Error!');
16. END;
17. /

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

PL/SQL 35
 HI - TECH COMPUTERS

H
C
TE
I-
H

PL/SQL 36
 HI - TECH COMPUTERS

PL/SQL Predefined Exceptions

There are many pre-defined exceptions 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:

H
Exception Oracle SQL Code Description
Error

C
ACCESS_INTO_NULL 06530 -6530 It is raised when a NULL
object is automatically
TE
assigned a value.

CASE_NOT_FOUND 06592 -6592 It is raised when none of

the choices in the "WHEN"
clauses of a CASE statement
is selected, and there is
I-

no else clause.

COLLECTION_IS_NU 06531 -6531 It is raised when a program

H

LL 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

PL/SQL 37
 HI - TECH COMPUTERS
uninitialized nested table
or varray.

DUP_VAL_ON_INDEX 00001 -1 It is raised when duplicate

values are attempted to be
stored in a column with
unique index.

H
INVALID_CURSOR 01001 -1001 It is raised when attempts
are made to make a cursor

C
operation that is not
allowed, such as closing an
unopened cursor.
TE
INVALID_NUMBER 01722 -1722 It is raised when the
conversion of a character
string into a number fails
because the string does not
represent a valid number.
I-

LOGIN_DENIED 01017 -1017 It is raised when s program

attempts to log on to the
H

database with an invalid

username or password.

NO_DATA_FOUND 01403 +100 It is raised when a select

into statement returns no
rows.

PL/SQL 38
 HI - TECH COMPUTERS

NOT_LOGGED_ON 01012 -1012 It is raised when a

database call is issued
without being connected to
the database.

PROGRAM_ERROR 06501 -6501 It is raised when PL/SQL

has an internal problem.

H
ROWTYPE_MISMATCH 06504 -6504 It is raised when a cursor
fetches value in a variable

C
having incompatible data
type.
TE
SELF_IS_NULL 30625 -30625 It is raised when a member
method is invoked, but the
instance of the object type
was not initialized.
I-

STORAGE_ERROR 06500 -6500 It is raised when PL/SQL

ran out of memory or memory
was corrupted.
H

TOO_MANY_ROWS 01422 -1422 It is raised when a SELECT

INTO statement returns more
than one row.

PL/SQL 39
 HI - TECH COMPUTERS

VALUE_ERROR 06502 -6502 It is raised when an

arithmetic, conversion,
truncation, or
size-constraint error
occurs.

ZERO_DIVIDE 01476 1476 It is raised when an

H
attempt is made to divide a
number by zero.

C
TE
I-
H

PL/SQL 40
 HI - TECH COMPUTERS
Oracle Trigger
In Oracle, you can define procedures that are implicitly executed when an
INSERT, UPDATE or DELETE statement is issued against the associated table.
These procedures are called database triggers.

There are six CREATE TRIGGER statements according to their firing points.

Firing Point: BEFORE

● BEFORE INSERT TRIGGER

H
● BEFORE UPDATE TRIGGER

● BEFORE DELETE TRIGGER

C
Firing Point: AFTER

● AFTER INSERT TRIGGER

TE
● AFTER UPDATE TRIGGER

● AFTER DELETE TRIGGER

SQL> Show User;

USER is "SYS"
I-

SQL> Create User C##Book Identified By Book;

H

User created.

SQL> Grant Connect, Resource To C##Book;

Grant succeeded.

SQL> Grant Create Session To C##Book;

PL/SQL 41
 HI - TECH COMPUTERS

Grant succeeded.

SQL> Grant Unlimited TableSpace To C##Book;

Grant succeeded.

SQL> Show User;

H
USER is "SYS"

SQL> Connect C##Book;

C
Enter password:

Connected.
TE
SQL> Show User;

USER is "C##BOOK"

SQL> Create Table Flipkart

2 (
I-

3 F_Id Number(5),

4 F_Name Varchar2(10),

5 F_Unit_Price Number(5)
H

6 );

Table created.

SQL> Create Table Price

2 (

3 P_Id Number(5),
PL/SQL 42
 HI - TECH COMPUTERS
4 P_Name Varchar2(10),

5 P_Unit_Price Number(5)

6 );

Table created.

SQL> Insert Into Flipkart Values

H
2 (101,'CRT',1000);

1 row created.

SQL> Select * From Flipkart;

C
TE
F_ID F_NAME F_UNIT_PRICE

---------- ---------- ------------

101 CRT 1000

I-

SQL> Select * From Price;

no rows selected
H

SQL> Create Or Replace Trigger Price_Trigger Before Update Of F_Unit_Price

On Flipkart

2 For Each Row

3 Begin

4 Insert Into Price Values (:OLD.F_Id,:OLD.F_Name,:OLD.F_Unit_Price);

5 End;

PL/SQL 43
 HI - TECH COMPUTERS
6 /

Trigger created.

SQL> Select * From Flipkart;

F_ID F_NAME F_UNIT_PRICE

H
---------- ---------- ------------

101 CRT 1000

C
SQL> Select * From Price;
TE
no rows selected

SQL> Update Flipkart Set F_Unit_Price = 500 Where F_Id = 101;

I-

1 row updated.

SQL> Select * From Flipkart;

H

F_ID F_NAME F_UNIT_PRICE

---------- ---------- ------------

101 CRT 500

SQL> Select * From Price;

PL/SQL 44
 HI - TECH COMPUTERS
P_ID P_NAME P_UNIT_PRICE

---------- ---------- ------------

101 CRT 1000

H
C
TE
I-
H

PL/SQL 45
 HI - TECH COMPUTERS
PL/SQL Interview Questions

PL/SQL is an advance version of SQL. There are given top list of PL/SQL
interview questions with answer.

1) What is PL/SQL?

PL/SQL stands for procedural language extension to SQL. It supports

procedural features of programming language and SQL both. It was developed
by Oracle Corporation in early of 90's to enhance the capabilities of SQL.

H
2) What is the purpose of using PL/SQL?

C
PL/SQL is an extension of SQL. While SQL is non-procedural, PL/SQL is a
procedural language designed by Oracle. It is invented to overcome the
limitations of SQL.
TE
3) What are the most important characteristics of PL/SQL?

A list of some notable characteristics:

● PL/SQL is a block-structured language.

I-

● It is portable to all environments that support Oracle.

● PL/SQL is integrated with the Oracle data dictionary.

● Stored procedures help better sharing of application.

H

4) What is PL/SQL table? Why it is used?

Objects of type tables are called PL/SQL tables that are modeled as
database table. We can also say that PL/SQL tables are a way to providing
arrays. Arrays are like temporary tables in memory that are processed very

PL/SQL 46
 HI - TECH COMPUTERS
quickly. PL/SQL tables are used to move bulk data. They simplifies moving
collections of data.

5) What are the datatypes available in PL/SQL?

There are two types of datatypes in PL/SQL:

1. Scalar datatypes Example are NUMBER, VARCHAR2, DATE, CHAR, LONG,

BOOLEAN etc.

H
2. Composite datatypes Example are RECORD, TABLE etc.

C
6) What is the basic structure of PL/SQL?

PL/SQL uses BLOCK structure as its basic structure. Each PL/SQL program
TE
consists of SQL and PL/SQL statement which form a PL/SQL block.

PL/SQL block contains 3 sections.

1. The Declaration Section (optional)

2. The Execution Section (mandatory)
3. The Exception handling Section (optional)
I-

7) What is the difference between FUNCTION, PROCEDURE AND

PACKAGE in PL/SQL?
H

Function: The main purpose of a PL/SQL function is generally to compute

and return a single value. A function has a return type in its
specification and must return a value specified in that type.

Procedure: A procedure does not have a return type and should not return
any value but it can have a return statement that simply stops its
execution and returns to the caller. A procedure is used to return
multiple values otherwise it is generally similar to a function.

PL/SQL 47
 HI - TECH COMPUTERS
Package: A package is schema object which groups logically related PL/SQL
types , items and subprograms. You can also say that it is a group of
functions, procedure, variables and record type statement. It provides
modularity, due to this facility it aids application development. It is
used to hide information from unauthorized users.

8) What is exception? What are the types of exceptions?

Exception is an error handling part of PL/SQL. There are two type of

H
exceptions: pre_defined exception and user_defined exception.

C
9) How to write a single statement that concatenates the
words ?Hello? and ?World? and assign it in a variable named
Greeting?
TE
Greeting := 'Hello' || 'World';

10) Does PL/SQL support CREATE command?

No. PL/SQL doesn't support the data definition commands like CREATE.
I-

11) Write a unique difference between a function and a

stored procedure.
H

A function returns a value while a stored procedure doesn?t return a

value.

12) How exception is different from error?

PL/SQL 48
 HI - TECH COMPUTERS
Whenever an Error occurs Exception arises. Error is a bug whereas
exception is a warning or error condition.

13) What is the main reason behind using an index?

Faster access of data blocks in the table.

H
14) What are PL/SQL exceptions? Tell me any three.

1. Too_many_rows
2. No_Data_Found

C
3. Value_error
4. Zero_error etc.
TE
15) How do you declare a user-defined exception?

You can declare the User defined exceptions under the DECLARE section,
with the keyword EXCEPTION.

Syntax:
I-

1. <exception_name> EXCEPTION;

16) What are some predefined exceptions in PL/SQL?

H

A list of predefined exceptions in PL/SQL:

● DUP_VAL_ON_INDEX

● ZERO_DIVIDE

● NO_DATA_FOUND

● TOO_MANY_ROWS

PL/SQL 49
 HI - TECH COMPUTERS
● CURSOR_ALREADY_OPEN

● INVALID_NUMBER

● INVALID_CURSOR

● PROGRAM_ERROR

● TIMEOUT _ON_RESOURCE

● STORAGE_ERROR

● LOGON_DENIED

H
● VALUE_ERROR

● etc.

17) What is a trigger in PL/SQL?

C
TE
A trigger is a PL/SQL program which is stored in the database. It is
executed immediately before or after the execution of INSERT, UPDATE, and
DELETE commands.

18) What is the maximum number of triggers, you can apply

on a single table?
I-

12 triggers.
H

19) How many types of triggers exist in PL/SQL?

There are 12 types of triggers in PL/SQL that contains the combination of

BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL keywords.

● BEFORE ALL ROW INSERT

● AFTER ALL ROW INSERT
● BEFORE INSERT

PL/SQL 50
 HI - TECH COMPUTERS
● AFTER INSERT etc.

20) What is the difference between execution of triggers

and stored procedures?

A trigger is automatically executed without any action required by the

user, while, a stored procedure is explicitly invoked by the user.

H
21) What happens when a trigger is associated to a view?

When a trigger is associated to a view, the base table triggers are

C
normally enabled.
TE
22) What is the usage of WHEN clause in trigger?

A WHEN clause specifies the condition that must be true for the trigger to
be triggered.

23) How to disable a trigger name update_salary?

I-

ALTER TRIGGER update_salary DISABLE;

H

24) Which command is used to delete a trigger?

DROP TRIGGER command.

25) what are the two virtual tables available at the time
of database trigger execution?

PL/SQL 51
 HI - TECH COMPUTERS
Table columns are referred as THEN.column_name and NOW.column_name.

For INSERT related triggers, NOW.column_name values are available only.

For DELETE related triggers, THEN.column_name values are available only.

For UPDATE related triggers, both Table columns are available.

26) What is stored Procedure?

H
A stored procedure is a sequence of statement or a named PL/SQL block
which performs one or more specific functions. It is similar to a
procedure in other programming languages. It is stored in the database and

C
can be repeatedly executed. It is stored as schema object. It can be
nested, invoked and parameterized.
TE
27) What are the different schemas objects that can be
created using PL/SQL?

● Stored procedures and functions

● Packages
I-

● Triggers

● Cursors
H

28) What do you know by PL/SQL Cursors?

Oracle uses workspaces to execute the SQL commands. When Oracle processes
a SQL command, it opens an area in the memory called Private SQL Area.
This area is identified by the cursor. It allows programmers to name this
area and access it?s information.

PL/SQL 52
 HI - TECH COMPUTERS
29) What is the difference between the implicit and
explicit cursors?

Implicit cursor is implicitly declared by Oracle. This is a cursor to all

the DDL and DML commands that return only one row.

Explicit cursor is created for queries returning multiple rows.

30) What will you get by the cursor attribute SQL%ROWCOUNT?

H
The cursor attribute SQL%ROWCOUNT will return the number of rows that are
processed by a SQL statement.

C
31) What will you get by the cursor attribute SQL%FOUND?
TE
It returns the Boolean value TRUE if at least one row was processed.

32) What will you get by the cursor attribute SQL%NOTFOUND?

It returns the Boolean value TRUE if no rows were processed.

I-

33) What do you understand by PL/SQL packages?

A PL/SQL package can be specified as a file that groups functions,

H

cursors, stored procedures, and variables in one place.

34) What are the two different parts of the PL/SQL

packages?

PL/SQL packages have the following two parts:

PL/SQL 53
 HI - TECH COMPUTERS
Specification part: It specifies the part where the interface to the
application is defined.

Body part: This part specifies where the implementation of the

specification is defined.

35) Which command is used to delete a package?

The DROP PACKAGE command is used to delete a package.

H
36) How to execute a stored procedure?

C
There are two way to execute a stored procedure.

From the SQL prompt, write EXECUTE or EXEC followed by procedure_name.

TE
1. EXECUTE or [EXEC] procedure_name;

Simply use the procedure name

1. procedure_name;
I-

37) What are the advantages of stored procedure?

Modularity, extensibility, reusability, Maintainability and one time

compilation.
H

38) What are the cursor attributes used in PL/SQL?

%ISOPEN: it checks whether the cursor is open or not.

%ROWCOUNT: returns the number of rows affected by DML operations:

INSERT,DELETE,UPDATE,SELECT.

PL/SQL 54
 HI - TECH COMPUTERS
%FOUND: it checks whether cursor has fetched any row. If yes - TRUE.

%NOTFOUND: it checks whether cursor has fetched any row. If no - TRUE.

39) What is the difference between syntax error and runtime

error?

A syntax error can be easily detected by a PL/SQL compiler. For example:

incorrect spelling etc. while, a runtime error is handled with the help of

H
exception-handling section in a PL/SQL block. For example: SELECT INTO
statement, which does not return any rows.

40) Explain the Commit statement.

C
Following conditions are true for the Commit statement:
TE
● Other users can see the data changes made by the transaction.

● The locks acquired by the transaction are released.

● The work done by the transaction becomes permanent.

I-

41) Explain the Rollback statement?

The Rollback statement is issued when the transaction ends. Following

conditions are true for a Rollback statement:
H

● The work done in a transition is undone as if it was never issued.

● All locks acquired by transaction are released.

42) Explain the SAVEPOINT statement.

With SAVEPOINT, only part of transaction can be undone.

PL/SQL 55
 HI - TECH COMPUTERS

43) What is mutating table error?

Mutating table error is occurred when a trigger tries to update a row that
it is currently using. It is fixed by using views or temporary tables.

44) What is consistency?

H
Consistency simply means that each user sees the consistent view of the
data.

Consider an example: there are two users A and B. A transfers money to B's

C
account. Here the changes are updated in A's account (debit) but until it
will be updated to B's account (credit), till then other users can't see
the debit of A's account. After the debit of A and credit of B, one can
TE
see the updates. That?s consistency.

45) What is cursor and why it is required?

A cursor is a temporary work area created in a system memory when an SQL

statement is executed.
I-

A cursor contains information on a select statement and the row of data

accessed by it. This temporary work area stores the data retrieved from
the database and manipulate this data. A cursor can hold more than one
H

row, but can process only one row at a time. Cursor are required to
process rows individually for queries.

46) How many types of cursors are available in PL/SQL?

There are two types of cursors in PL/SQL.

1. Implicit cursor, and

PL/SQL 56
 HI - TECH COMPUTERS
2. explicit cursor

H
C
TE
I-
H

PL/SQL 57