UNIT 3

CHAPTER 9
Introduction to PL/SQL
Introduction:
SQL suffers from inherent disadvantages. They are
 It does not have procedural capabilities
 SQL statements are passed to the oracle engine one at a time. This decreases the processing
speed in a multi-user environment
 SQL has no facility of programmed handling of error messages

Q)LIST ANY TWO ADVANTAGES OF PLLSQL?2M or 5m
9.1 Advantages of PL/SQL:
 PL/SQL is a development tool that not only supports SQL data manipulation but also
provides facilities for condition checking, branching and looping
 PL/SQL sends the entire block of SQL code to oracle engine. As the entire block of
SQL code is passed to Oracle engine at one time for execution, all the changes made to
the data in the table are done or undone in one go.
 PL/SQL facilitates the displaying of user-friendly messages when errors are encountered
 PL/SQL allows declaration and use of variables in blocks of code. The variables are used to
store intermediate results of a query for later processing
 PL/SQL all sorts of calculations can be done efficiently and quickly
 Applications written in PL/SQL are portable to any computer hardware system and
operating system

Q)Explain the pl/sql program structure with example.(5m)

Generic PL/SQL block
A single PL/SQL code block consists of a set of SQL statements, clubbed together, and
passed to oracle engine entirely. This block has to be logically grouped together for the engine to
recognize it as singular block of code. A PL/SQL block has a definite structure, which can be
divided into sections. The sections of PL/SQL block are
a. Declare section
b. Master begin…end section
DECLARE Declarations of memory variables,
constants, cursors
BEGIN SQL executable statements
PL/ SQL executable statements
EXCEPTION Handles errors that arise during
execution of code block between
BEGIN and EXCEPTION
Q END; End of PL/SQL block

The Declare Section:

Code block starts with the declaration section, in which memory variables and other oracle objects are
declared, and initialized if necessary. Once declared they can be used in SQL statements for data
manipulation.

The Begin Section:

It consists of a set of SQL and PL/SQL statements which describe processes to be applied to
table data. Actual data manipulation, retrieval, branching and looping constructs are specified in
this section.
The Exception Section:
This section deals with handling of errors that arise during the execution of data
manipulation statement, which make up the PL/SQL code block. Errors can arise due to syntax,
logic or validation rule violation

The End Section:

This marks the end of a PL/SQL block.

Structure of PL/SQL block:

DECLARE <declarations
section>
BEGIN
<executable command(s)>
EXCEPTION <exception
handling>
END;

Example:
DECLARE
message varchar2(20):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/

Output:
Hello, World!

9.3 PL/SQL Execution Environment:

 The PL/SQL engine accepts any valid PL/SQL block as input
 PL/SQL engine resides in the Oracle engine. Oracle engine can process both SQL
statements and PL/SQL blocks
 These blocks are sent to the PL/SQL engine, where the procedural statements are executed.
SQL statements are sent to the SQL executor in the Oracle engine.
 The call to the oracle engine needs to be made only once to execute any number of SQL
statements if these SQL statements are bundled inside a PL/SQL block
PL/SQL Character set:
The basic character set in PL/SQL includes
 Upper case letters {A..Z}
 Lower case letters {a..z}
 Numerals {0..9}
 Symbols ( ) + - * / < > = ! @ %
Words used in PL/SQL blocks are called lexical units. Blank spaces can freely be inserted
between lexical units in a PL/SQL block. A literal is a numeric or character string used to represent
itself. A numeric literal can be a integer or float value. E.g. 5, -20, 29.6. A string literal is a set of
two or more characters enclosed in single quotes. E.g. ‘Hello’, ‘Hi’. A character literal is a single
character enclosed in single quotes. E.g. ‘Y’, ‘N’, ‘m’. Boolean literals or logical literals are
predetermined constants. The values these literals can hold are
TRUE, FALSE, NULL.

9.5 PL/SQL Data types:

The default data types that can be declared in PL/SQL are
 Number – Numeric values, on which arithmetic operations are performed.
 Character – Alphanumeric values that represent single characters or strings of characters.
 Boolean – Logical values, on which logical operations are performed.
 Date/time – Date and time.
The above data types can have NULL values. The % type attribute is used to declare variables
based on the definitions of columns in a table. It helps to declare a variable or constant to have same
data type as that of a column declared in a table.

Variables:
A variable in PL/SQL is a named variable. A variable name must begin with a character and
can be followed by a maximum of 29 characters. Reserved words cannot be used as variable
names unless enclosed within double quotes. A blank space cannot be embedded in a variable
name. variable names are not case sensitive. Variables are separated from each other by a comma.
Values can be assigned to variables in two ways:
1. Using the assignment operator :=
2. Selecting or fetching table data values into variables

q)How to declare a constant in pl/sql

Constants:
A constant is declared in the same way as how a variable is declared. The only difference is
that we use a keyword constant and a value is assigned to it immediately. Thereafter no further
assignments can be made.
Example: pi constant number(10,2):=3.145;

Logical Comparisons:
Comparisons in PL/SQL can be made using Boolean expressions. A Boolean expression
consists of variables separated by relational operators{<,>,<=,>=,<>}. A logical expression is a
combination of two or more Boolean expressions separated by logical operators{AND,OR, NOT}.
A boolean expression always evaluates to TRUE, FALSE or NULL

Q)How do display a user-message on the screen in pl/sql?2m

Displaying user messages on the VDU:

DBMS_OUTPUT is a package that includes a number of functions and procedures that
accumulate information in a buffer so that it can be retrieved later. These functions can also be used
to display a user message on the screen. PUT_LINE puts a piece of information in package buffer
followed by end of the line marker. It expects a single parameter of character data type. In order to
display a message, SERVEROUTPUT should be set ON. It is a SQL*Plus environment parameter
that displays the information passed as parameter to the PUT_LINE function.
Syntax:
SET SERVEROUTPUT [ON/OFF]
Example: dbms_output.put_line(‘welcome to pl/sql’);

Comments:
A comment in PL/SQL can be written in two ways:
1. It can begin with a double hyphen(--). Here, the entire line can be treated as a comment.
2. A group of lines can be enclosed between /* and */. All the lines will be ignored for
processing
Example:
a. --Single line comment
b. /* This is
a comment*/

Example PL/SQL code:

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

Radius: 9.5
Circumference: 59.69
Area: 283.53

9.6 Control Structures:

The flow of control statements can be classified as
 Conditional control
 Iterative control
 Sequential control

q)Explain the conditional statements?5m

9.6.1 Conditional control:
PL/SQL allows the use of an IF statement to control the execution of a block of code. In
PL/SQL, IF-THEN-ELSE structure allows to check certain conditions under which a specific block
of code should be executed.
It is the simplest form of IF control statement, frequently used in decision making and
changing the control flow of the program execution. The IF statement associates a condition with a
sequence of statements enclosed by the keywords THEN and END IF. If the condition is TRUE, the
statements get executed and if the condition is FALSE or NULL then the IF statement does nothing.
The different forms of IF statements are as follows:
1) IF condition THEN
statement 1;
END IF;

2) IF condition THEN
statement 1;
ELSE
statement 2;
END IF;
3)IF condition 1 THEN
statement 1;
statement 2;
ELSIF condtion2 THEN
statement 3;
ELSE
statement 4;
END IF;

Example:
SQL> ed samif.sql
set serveroutput on
declare
x number(2);
y number(2);
begin x:=&x;
y:=&y;
if x>y then
dbms_output.put_line (x || 'is bigger');
else
dbms_output.put_line (y || 'is bigger');
end if;
end;

Output:
Enter value for x: 35
old 5: x:=&x;
new 5: x:=35; Enter
value for y: 25 old 6:
y:=&y;
new 6: y:=25;
35is bigger
PL/SQL procedure successfully completed.

Q)Explain any two looping statements in PL/SQL with example.

 9.6.2 Iterative control:
Iterative control indicates the ability to repeat sections of code. A loop marks the sequence
of statements that has to be repeated. Iterative control Statements are used when we want to repeat
the execution of one or more statements for specified number of times.
There are three types of loops in PL/SQL:
 Simple Loop
 While loop
 For loop

Simple Loop:

A Simple Loop is used when a set of statements is to be executed at least once before the loop
terminates. An EXIT condition must be specified in the loop, otherwise the loop will get into an
infinite number of iterations. When the EXIT condition is satisfied the process exits from the loop.
Syntax of Simple Loop:
LOOP
statements;
[EXIT WHEN condition;]
END LOOP;

These are the important steps to be followed while using Simple Loop.
1. Initialize a variable before the loop body.
2. Increment the variable in the loop.
3. Use a EXIT WHEN statement to exit from the Loop. If you use a EXIT statement without
WHEN condition, the statements in the loop is executed only once
Example:
set server
output on declare
i number:=0;
begin
dbms_output.put_line('Even numbers from 1 to 10 are:');
loop
i:= i+2;
dbms_output.put_line (i);
exit when i>=10;
end loop;
end;

Output:
Even numbers from 1 to 10 are: 2
4 6 8 10
PL/SQL procedure successfully completed.

While loop:
A WHILE LOOP is used when a set of statements has to be executed as long as a condition
is true. The condition is evaluated at the beginning of each iteration. The iteration continues until
the condition becomes false.

Syntax of WHILE LOOP is:

WHILE <condition>
LOOP
statements;
 End loop;
Example:

set serveroutput on
declare
pi constant number(5,2):=3.14; r
number(2);
c number(5,2);
begin
r:=3; while
r<=7 loop
c:=2*pi*r;
insert into circle values(r,c);
r:=r+1;
end loop;
end;

Output:
SQL> @samwhile.sql;
PL/SQL procedure successfully completed.

SQL> select * from circle;

RADIUS CIRCUM
---------- ----------
3 18.84
4 25.12
5 31.4
6 37.68
7 43.96

FOR Loop
A FOR LOOP is used to execute a set of statements for a predetermined number of times.
Iteration occurs between the start and end integer values given. The counter is always incremented
by 1. The loop exits when the counter reachs the value of the end integer.

Syntax of FOR LOOP is:

FOR counter IN value1..value2

LOOP statements;
END LOOP;

Example:
set serveroutput on
declare
i number; n
number;
f number:=1;
begin
n:=&n;
for i in 1..n
loop f:=f*i;
end loop; dbms_output.put_line('Factorial
is '|| f);
 end;

Output:
Enter value for n: 5
old 6: n:=&n; new 6:
n:=5; Factorial is 120

PL/SQL procedure successfully completed.

9.6.3 Sequential Control

The GOTO statement changes the flow of control within a PL/SQL block. This statement
allows execution of a section of code which is not in the normal flow of control. The entry point to
such a block of code is marked using the tags << user-defined name>>. The GOTO statement can
then make use of this label to jump to that block of code for execution.
Syntax:
GOTO <code-block name>

9.7 Oracle Transactions

A series of one or more SQL statements that are logically related or a series of operations
performed on the Oracle table data is called as a transaction. Oracle treats changes made to data in
a table as a two step process. First, the requested changes are done. In order to make these changes
permanent, a COMMIT statement is issued at the SQL prompt. A ROLLBACK statement issued
at the SQL prompt can be used to undo a part of or the entire transaction. A transaction begins with
the first executable statement after a commit, rollback or a connection made to the Oracle engine. A
transaction is a group of events that occur between any of the following events:
 Connecting to Oracle
 Disconnecting from Oracle
 Committing changes to database table
 Rollback
Closing Transactions – A transaction can be closed using either a commit or rollback statement.
By using these statements, table data can be changes or all the changes made to the table data can
be undone

Q)Write the purpose of commit and rollback commands.(2m)

COMMIT – A commit ends the current transaction and makes permanent any changes made to the
table data during a transaction.
Syntax:
commit;

SAVEPOINT – It marks and saves the current point in the processing of a transaction. When a
savepoint is used with a rollback statement, parts of the transaction can be undone. An active
savepoint is one that is specified since the last COMMIT or ROLLBACK.
Syntax:
savepoint <savepointname>;

ROLLBACK – A rollback does exactly the opposite of COMMIT. It ends the transaction but
undoes any changes made during the transaction. Rollback can be fired at the SQL prompt with or
without savepoint clause.
Syntax:
rollback [work] [to [savepoint]<savepointname>];
DECLARE
a number(2) := 10;
BEGIN
<<loopstart>>
-- while loop execution
WHILE a < 20 LOOP
dbms_output.put_line ('value of a: ' || a);
a := a + 1;
IF a = 15 THEN
a := a + 1;
GOTO loopstart;
END IF;
END LOOP;
END;
/
 CURSORS
10.1 Introduction:
The Oracle engine uses a work area for its internal processing in order to execute an
SQL statement. This work area is private to SQL operations and is called a Cursor. The data
that is stored in the cursor is called the Active Data Set. The size of the cursor in the memory is the
size required to hold the number of rows in the active data set. Oracle has a predefined area in the
main memory set aside, within which cursors are opened. . A cursor contains information on a
select statement and the rows of data accessed by it. A cursor can hold more than one row, but can
process only one row at a time
Cursors are classified depending on the circumstances under which they are opened.
Both implicit and explicit cursors have the same functionality, but they differ in the way they
are accessed
q)what is implicit cursor?
10.2 Implicit cursors
If the oracle engine opened a cursor for its internal processing, it is known as implicit
cursor. These are created by default when DML statements like, INSERT, UPDATE, and DELETE
statements are executed. They are also created when a SELECT statement that returns just one row
is executed.
Implicit Cursor Attributes:
Attributes Return Value Example
%FOUND The return value is TRUE, if the DMLSQL%FOUND
statements like INSERT, DELETE and
UPDATE affect at least one row and if
SELECT ….INTO statement return at least one
row.
The return value is FALSE, if DML statements
like INSERT, DELETE and UPDATE do not
affect row and if SELECT….INTO statement
do not return a row.
%NOTFOUND The return value is FALSE, if DML statements SQL%NOTFOUND
like INSERT, DELETE and UPDATE at least
one row and if SELECT ….INTO statement
return at least one row.
The return value is TRUE, if a DML statement
like INSERT, DELETE and UPDATE do not
affect even one row and if SELECT ….INTO
statement does not return a row.
%ROWCOUNT Return the number of rows affected by the SQL%ROWCOUNT
DML operations INSERT, DELETE, UPDATE,
SELECT

Example: Consider the PL/SQL Block that uses implicit cursor attributes as shown below:
/*increase the salary of all employees by 1000*/

DECLARE
var_rows number(5);

BEGIN
UPDATE employ
SET salary = salary + 1000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
 ELSIF SQL%FOUND THEN var_rows
:= SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
END IF;
END;
Output:
Salaries for 7employees are updated PL/SQL
procedure successfully completed.

q)Explain how explicit cursor is declared and managed in PL/SQL.

10.3 Explicit cursors
A cursor can be opened They must be created when you are executing a SELECT statement
that returns more than one row. Even though the cursor stores multiple records, only one record can
be processed at a time, which is called as current row. When you fetch a row the current row
position moves to next row. When individual records in a table have to be processed inside a
PL/SQL block explicit cursor is used. This cursor will be declared and mapped to an SQL query in
the declare section of the PL/SQL block and used with the executable section of PL/SQL block.
The steps involved in using explicit cursor and manipulating data are
 Declare a cursor in the declare section of PL/SQL block
 Open the cursor
 Fetch the data from the cursor one row at a time into the memory variables
 Process the data that was fetched
 Exit from the loop after the processing is complete
 Close the cursor

Explicit cursor attributes:

Attributes Return Value Example
%ISOPEN Evaluates to TRUE, if an explicit cursor is open. Cursor_name%ISOPEN
It returns FALSE if the cursor is closed

%FOUND The return value is TRUE, if the DMLCursor_name%FOUND

statements like INSERT, DELETE and
UPDATE affect at least one row and if SELECT
….INTO statement return at least one row.

like INSERT, DELETE and UPDATE do not

affect row and if SELECT….INTO statement do
not return a row.
%NOTFOUND The return value is FALSE, if DML statements Cursor_name%NOTFOUND like
INSERT, DELETE and UPDATE at least
one row and if SELECT ….INTO statement return
at least one row.
The return value is TRUE, if a DML statement like
INSERT, DELETE and UPDATE do not affect
even one row and if SELECT ….INTO statement
does not return a row.
%ROWCOUNT Return the number of rows affected by the DML Cursor_name%ROWCOUNT
operations INSERT, DELETE, UPDATE,
SELECT

Q)Write the functionality of OPEN,FETCH AND CLOSE Command in explicit cursor with
syntax(5m)
Functionality of Open, Fetch and Close commands:

Declaring a cursor – A cursor is declared in the declare section of the PL/SQL block. This is done
to create an active data set. The cursor name is used to reference the active data set that is held
within the cursor
Syntax:
CURSOR cursor-name IS SELECT statement;
Example:
DECLARE
CURSOR emp_cur IS
SELECT *
FROM employ
WHERE salary > 5000;

In the above example we are creating a cursor ‘emp_cur’ on a query which returns the
records of all the employees with salary greater than 5000.

Opening a cursor – Opening a cursor executes a query and creates an active data set that contains
all rows which meet the search criteria of the query. An open statement retrieves the records from
the table and places the records in the cursor

Syntax:
OPEN cursor_name;

Fetching records from the cursor – The fetch statement retrieves the rows from the active data set
into the memory variables declared in the PL/SQL block one row at a time. Each time a fetch
statement is executed, the cursor pointer is advances to the next row in the active data set.

Syntax:
FETCH cursor-name INTO variable list;
Closing a cursor – The close statement disables the cursor and the active data set becomes
undefined. This will release the memory occupied by the cursor and the active data set.
Syntax:
CLOSE cursor-name;

General Form of using an explicit cursor is:

DECLARE
variables;
records;
create a cursor;
BEGIN
OPEN cursor;
FETCH cursor;
process the records;
CLOSE cursor;
END;

Example:
/*Display employee name and salary whose salary is more than 6000*/
SET SERVEROUTPUT ON
DECLARE
 emp_rec employ%rowtype;
CURSOR emp_cur IS
SELECT * FROM employ
WHERE sal > 6000;
BEGIN
OPEN emp_cur;
FETCH emp_cur INTO emp_rec;
dbms_output.put_line (emp_rec.ename || ' ' || emp_rec.sal);
CLOSE emp_cur;
END;
/
Output:
SQL> @EXPCUR.SQL
Gita 7000
PL/SQL procedure successfully completed.

10.4 Cursor FOR LOOP:

When using FOR LOOP you need not declare a record or variables to store the cursor
values, need not open, fetch and close the cursor. These functions are accomplished by the FOR
LOOP automatically.

General Syntax for using FOR LOOP:

FOR record_name IN cursor_name
LOOP
process the row...
END LOOP;

Example:
/*To display all employees whose salary is greater than 9000*/
SET SERVEROUTPUT ON
DECLARE
CURSOR emp_cur IS
SELECT *
FROM employ
WHERE sal > 9000;
BEGIN
for emp_rec in emp_cur
loop
dbms_output.put_line (emp_rec.ename || ' ' || emp_rec.sal);
end loop;
END;
/
Output:
Rani 10000
Ranbir 11000
PL/SQL procedure successfully completed.

5MARKS QUESTIONS
1. Explain PUSQL program structure with example.
2. What is a cursor ? Explain attributes of implicit and explicit cursor.
3. Explain any two looping statements in PUSQL with syntax and example.
4. Explain the following cursor attributes. i) %FouND ii) %NoT FoUND iii) %rsoPEN iv)
%ROWCOUNT
5. Explain the following cursor statements with syntax and example. i) Open statement ii)
Fetch statement.
6. Explain any two iterative control statemnets with example.
7. Explain with syntax and example,how to create a cursor and read the records from the
cursor.
8. Explain various forms of IF…..ENDIF statement with syntax and example.