Introduction To PL/SQL

 Outline

Introduction, Advantages, PL/SQL Block, PL/SQL Execution Environment,

PL/SQL Character set, Literals, Data types,
PL/SQL Block: Attributes %type, %rowtype,Variables, Constants,
Displaying User Message on screen, Conditional Control in PL/SQL,
Iterative Control Structure: While Loop, For Loop, Goto Statement,
Commit, Rollback, Save point
Disadvantages Of SQL:
1. SQL does not have any procedural capabilities.
2. SQL statements are passed to Oracle engine one at a time. Each time the SQL
statement is executed , the call is made to engine resources. Which
decreases speed of data processing.
3. While processing SQL statement if an error occurs, the Oracle engine displays
its own error message.
• Advantages of PL/SQL:-
 Support SQL data manipulation.
Provide facilities like conditional checking, branching and
looping.
Provide fast code execution since it sends SQL statement as a
block to the oracle engine.
PL/SQL provides high security level.
PL/SQL provides support for Object-Oriented Programming.
PL/SQL allows sending an entire block of statements to the
database at one time. This reduces network traffic and
provides high performance for the applications.
• Difference between PL/SQL:
SQL PL/SQL

PL/SQL is a block of codes that used to

SQL is a single query that is used to
write the entire program blocks/
Differences between SQL and PL/SQL:
perform DML and DDL operations.
procedure/ function, etc.

Execute as a single statement. Execute as a whole block.

Mainly used to manipulate data. Mainly used to create an application.

It is an extension of SQL, so it can contain

Cannot contain PL/SQL code in it.
SQL inside it.
• PL/SQL Character Set:
The basic character set includes the valid characters that can be used
while declaring variables ,constants and writing expressions.

All upper case and lower case : A-Z & a-z

Digits : 0 -9
White space ,tab
Symbols like () + - / < >= ! ~ : :. @% , “ # $ ^ & _ | { } ? []
• Literals:
A literal is a numeric value or a character string used to represent itself.
1)Numeric Literal: integer or float
e.g. 25 ,6.37
2)String literal : one or more characters enclosed within single quotes
e.g. ‘Hello World’
3) Character literal : single character
e.g. ‘A’ ,’Y’
4) Logical (Boolean)literal : These are predetermined constants. The value can be assigned to this data
types are: TRUE ,FALSE, NULL.

5)Date & Time literal:

e.g. DATE '1978-12-25';
TIMESTAMP '2012-10-29 12:01:01';
• PL/SQL Operators:
• Operators are used in expressions.PL/SQL operators are listed below:
1)Arithmetic Operators
2)Relational Operators
• PL/SQL Data Types:
Number
Char
Varchar
Date
Boolean
Structure of PL/SQL
HEADER
Type and Name of block
DECLARE
Declaration section
Variables; Constants; Cursors;

BEGIN
Executable section
PL/SQL and SQL Statements
EXCEPTION
Exception handlers(error handling code)
END;
Types of PL/SQL block
PL/SQL blocks are of mainly two types.
 Anonymous blocks
 Named Blocks
 Anonymous blocks:
• These blocks start with the keyword 'DECLARE' or 'BEGIN'.
• Since these blocks do not have any reference name, these cannot be stored for later purpose. They shall be
created and executed in the same session.
 Named blocks: (labeled block)
• Named blocks have a specific and unique name for them. They are stored as the database objects in the server.
• These blocks can be called from other blocks.
• The block structure is same as an anonymous block, but with a label which gives a name to the
block, put a label before DECLARE keyword.
• These blocks can be nested within other blocks. It can also contain nested blocks.
• Named blocks are basically of two types:
 Procedure
 Function
• Attributes %type, %rowtype,

%TYPE: PL/SQL can use the %type attribute to declare variables based on definition of columns
in a table.
%ROWTYPE: It provides the record type that represents a entire row of a table or view or columns
selected in the cursor.
Advantage:
1) I need not to know about variables data type.
2) If the database definition of a column in a table changes, the data type of a variable changes
accordingly.
• Variables
Variable is a named data items. Variables have a name and a data type .A variable must be declared before it is
used. Variables names must start with a letter an alphabet. We can not use reserved words as a variable name.
Variables can be used to store the results of query or to calculate values and hold them for some use later.It can
be used in expressions in SQL or Pl/SQL.
Assigning value to a variable can be done in two ways:
Using assignment operator := (colon followed by an equal sign)
Selecting or fetching table data values into variables.

Declaring Variables:
Variable_name type[size] [Not Null] [: =value]

Example:
V_salary Number(7) := 5000;
• Constants
• The declaration of a constant is similar to a variable declaration except the keyword CONSTANT is
required .It needs to be assigned a value in the declaration.
• Syntax:

Constant name CONSTANT type[size] := value ;

Example:

PI CONSTANT NUMBER( 4,3) := 3.14 ;

• Displaying User Message on screen

DBMS_OUTPUT is a package that includes a number of procedures and functions that accumulate information
in a buffer so that it can be retrieved later. This functions can also be used to display messages.

Put_LINE puts a piece of information in the package buffer followed by an end of line marker. It can also be
used to display messages.
To display message ,the SERVEROUTPUT should be set to ON SERVEROUTPUT
Syntax:
SET SERVEROUTPUT [ON /OFF]
• Control Structures:

• Conditional Control
• Iterative Control
• Sequential Control

1. Conditional Control:-PL/SQL allows use of an IF stmt. To control the execution of

a block of code. In Oracle, the IF-THEN-ELSE statement is used to execute code when a
condition is TRUE, or execute different code if the condition evaluates to FALSE.

if <condition> then
sequence of statements;
end if;
• Write PL/SQL code that will except an account number from the user and debit amount of Rs. 2000 from a/c if the balance of a/c
remains minimum Rs.500. The process is to be fired on Account table. account (ac_Id, Name, bal)

Declare
ac_bal number(10,2);
ac_no varchar2(6);
debit_amt number(5) :=2000;
min_bal constant number(5,2):=500;
Begin
ac_no:=&ac_no;
select min_bal into ac_bal
from account
where ac_id=ac_no;

ac_bal:=ac_bal - debit_amt;
if ac_bal >= min_bal then
update accounts set bal=bal – debit_amt
where ac_id=ac_no;
end if;
End;
2. Iterative Control:
i) Simple loop
ii) For loop
iii) while loop

• i) Simple loop : In Oracle, the LOOP statement is used when you are not sure how many times you want
the loop body to execute and you want the loop body to execute at least once.
loop
sequence of statements;
end loop;
• ii) For loop
for counter in [reverse]start .. end
loop
sequence of statements;
end loop;
• iii) while loop
while <condition>
loop
<action>
• While Loop:
In Oracle, you use a WHILE LOOP when you are not sure how many times you will execute the loop body
and the loop body may not execute even once.
• Syntax: while loop
while <condition>
loop
<action>
end loop;

The condition tested each pass through the loop. If condition evaluate to TRUE, the loop body is executed .If
condition evaluates to FALSE ,the loop is terminated.
• Sequential Control:
• The GOTO statement changes the flow of control within a PL/SQL block.
The syntax for the GOTO statement in Oracle/PLSQL consists of two parts - the GOTO
statement and the Label Declaration:
Syntax:-

goto <code block name>;

Example:- Create a simple loop such that a msg is displayed when a loop exceeds a
particular value.
SQL> set serveroutput on
SQL> DECLARE
2 i number:=0;
3 BEGIN
4 LOOP
5 i:=i+2;
6 EXIT WHEN i>10;
7 END LOOP;
8 dbms_output.put_line('loop exited as the value of i has reached'||to_number(i));
9 END;
10 /
Output:-
loop exited as the value of i has reached12

PL/SQL procedure successfully completed.

• Write a PL/SQL block to calculate the area of a circle for a value of radius
varying from 3 to 7. Store the radius & the corresponding values of calculated
area in a table, ‘Areas’.

Declare
pi constant number(4,2) := 3.14;
radius number(5);
area number(10,2);
Begin
radius:=3;
while radius<=7
loop
area:=pi*power(radius,2);
insert into areas values(radius,area);
end loop;
End;
• Write a PL/SQL block of code for inverting a number 5639 to 9365.

SQL> set serveroutput on

SQL> Declare
2 given_number varchar2(5);
3 str_len number(2);
4 inverted_no varchar2(5);
5 Begin
6 given_number:='12345';
7 str_len:=length(given_number);
8 for cntr in reverse 1.. Str_len
9 loop
10 inverted_no := inverted_no||substr(given_number, cntr, 1);
11 end loop;
12 dbms_output.put_line('The given no. is' || given_number);
13 dbms_output.put_line('The inverted number is' || inverted_no);
14 End;
15 /
The given no. is12345
The inverted number is54321

PL/SQL procedure successfully completed.

• Write PL/SQL block of code to achieve the following. If the price of product p1 is less than 4000, then change
the price to 4000. Th price change is to be recorded in the old_price_table along with the product_no & the
date on which the price was last changed.

Declare
selling_price number(10,2)
Begin
select sell_price into selling_price
from product_master
where product_no=‘p1’;

if selling_price<4000 then
goto add_old_price;
else
dbms_output.put_line(‘Current price’ || selling _price);
end if
<<Add_old_price>>
update product_master set sell_price=4000
where product_no= ‘p1’
insert into old_price (prduct_no, date_change, old_price)
values(‘p1’, sysdate, selling_price);
dbms_output.put_line(‘the new price of p1 is 4000’);
• Commit : To save the changes.

• Rollback : To roll back the changes. ROLLBACK in SQL is a transactional control language which is
used to undo the transactions that have not been saved in database. The command is only be used to undo
changes since the last COMMIT.

• Save point: creates points within the groups of transactions in which to ROLLBACK.
• Error Handling in PL/SQL:

when <exception name> then

user defined actions to be carried out;

• Types Of Exceptions:
1. predefined exceptions:
They are raised automatically by the system during run time.

2. user defined Exceptions:

They must be raised explicitly using Raise statement.
• Some Predefined Exceptions:

1. No_data_found
2. Cursor_already_open
3. Dup_val_on_index
4. Storage_error
5. Program_error
6. Zero_divide
7. Invalid_cursor
8. Login_denied
9. Invalid_cursor
10. Too_many_rows
• User Defined Exception:
exception name <exception>;

Raise Statement:
raise <exception name>;

• Declare
exception name <exception>;
Begin
SQL sentence;
if condition then
raise <exception name>;
end if;
Exception
when <exception name> then
user defined actions to be carried out;
End;
• The X company wants to check qty_in_hand.if it is less than 500
the company wants to display msg.

Declare
lo_val exception;
qty item_master.qty_in_hand%type;
Begin
select qty_in_hand into qty
from item_master where itemcode=‘i100’;
if qty<500 then
raise lo_val;
end if;
Exception
when lo_val then
dbms_output.put_line=(‘Qty not enogh’);
End;
THANK YOU!!!