Introduction to PL/SQL

What is PL/SQL
PL/SQL is a sophistical programming language used to
access an Oracle database from a various environments.

PL/SQL stands for Procedural Language/SQL.

It extends SQL by adding constructs found in other
procedural languages, such as: loops, conditional
statements, declared variables, accessing individual
records one at a time, and many others.
 Why use PL/SQL
Compared to SQL, PL/SQL has the procedural constructs
that are useful to express a desired process from start
to end.

One block of PL/SQL code can bundled several SQL

statements together as a single unit. Making less
network traffic and improving application performance.

PL/SQL can be integrated with other languages, such

as Java, to take advantage of the strongest features of
both languages.
 Advantages of using PL/SQL to
access Oracle
• PL/SQL is managed centrally within the database

• Code is managed by the DBA and execution privileges

are managed in the same way as with other objects

• PL/SQL objects are first-class Oracle DB objects

• Easy to read
– With modularity features and error handling
 Using PL/SQL as a programming
language
• Permits all operations of standard programming
languages e.g.
– Conditions IF-THEN-ELSE-END IF;
– Jumps GOTO

• Provides loops for controlling iteration

– LOOP-EXIT; WHEN-END LOOP; FOR-END LOOP;
WHILE-END LOOP

• Allows extraction of data into variables and its

subsequent manipulation
 Data Types
• VARCHAR2(size) - variable length character string
having a maximum of size bytes (up to 4000)

• NUMBER - numeric column with space for 40 digits,

plus space for a decimal point and sign.

• NUMBER(size) - numeric column of specified size

• NUMBER(size, d) - d digits after the decimal point

 More Data Types
• CHAR(size) - fixed length character data. Maximum
size is 2000. Default size is 1.

• DATE - valid dates range from 1/1/4712 BC to

12/31/4712 AD

• DECIMAL - same as NUMBER, does not accept

arguments such as size or d

• FLOAT - same as NUMBER

• INTEGER - same as NUMBER, does not accept d

as argument
 More Data Types
• LONG - character data of variable size up to 2GB, only
one LONG column can be defined per table. LONG
columns cannot be used in WHERE clauses or indexes.
Other restrictions also apply.

• LONG RAW - raw binary data, otherwise same as LONG

• LONG VARCHAR - same as LONG

• BLOB - binary large object, up to 4GB

• CLOB - character large object, up to 4GB

 Use of Data-Types
• Number – used to store any number

• Char(size) & varchar2(size) e.g.: char(10) – used to

store alphanumerical text strings, the char data type will
pad the value stored to the full length declared.

• Date – used to store dates

• Long – used to store large blocks of text up to 2

gigabytes in length (limited operations)
 Structure of PL/SQL
• PL/SQL is Block Structured
A block is the basic unit from which all PL/SQL
programs are built. A block can be named (functions and
procedures) or anonymous.
• Sections of block
1- Header Section
2- Declaration Section
3- Executable Section
4- Exception Section
 Structure of PL/SQL
HEADER
Type the Name of block
DECLARE
Variables; Constants; Cursors;
BEGIN
PL/SQL and SQL Statements
EXCEPTION
Exception handlers
END;
 Structure of PL/SQL
DECLARE
a number;
text1 varchar2(20);
text2 varchar2(20) := “HI”;
BEGIN
---------- ---------- ----------
END;
Important Data Types in PL/SQL include
NUMBER, INTEGER, CHAR, VARCHAR2, DATE
etc
to_date(‘02-05-2007','dd-mm-yyyy') { Converts
String to Date}
 Modules in PL/SQL
There are 4 types of modules in PL/SQL
• Procedures – series of statements may or may not return
a value

• Functions – series of statements must return a single

value

• Triggers – series of PL/SQL statements (actions)

executing after an event has triggered a condition (ECA)

• Packages – collection of procedures and function that has

2 parts:
– a listing and a body.
 PL/SQL Control Structure
• PL/SQL has a number of control structures
which includes:
• Conditional controls
•  Iterative or loop controls.
• Exception or error controls

• These controls used singly or together, that

allow the PL/SQL developer to direct the flow of
execution through the program.
 PL/SQL Control Structure
• Conditional Controls
IF....THEN....END IF;
IF....THEN...ELSE....END IF;
IF....THEN...ELSIF....THEN....ELSE....END IF;
 PL/SQL Control Structure
• LOOP
...SQL Statements...
EXIT;
END LOOP;

• WHILE loops
• WHILE condition LOOP
...SQL Statements...
END LOOP;

• FOR loops
• FOR <variable(numeric)> IN [REVERSE]
<lowerbound>..<upperbound> LOOP .... ..... END LOOP;
 PL/SQL Control Structure
• Cursor
DECLARE
  name varchar2(20);  
Cursor c1 is
select t.name
from table t
where date is not null;
BEGIN
OPEN c1;
LOOP
FETCH c1 into name;
exit when c1%NOTFOUND;
END LOOP;
CLOSE c1;
END;
 A Sample UNIVERSITY database
• A sample database containing the following relations
to illustrate PL/SQL programming:

• STUDENTS(id, fname, lname, major, credits)

• ROOMS(rid, building, roomno, seats, desc)
• CLASSES(dept, course, desc, maxenroll, curenroll,
credits, rid)
• REGISTERED(sid, dept, course, grade)

(Assume a sequence STU_SEQ has been created)

declare
v_major varchar2(10) := ‘History’;
v_fname varchar2(10) := ‘Scott’;
v_lname varchar2(10) := ‘Mason’;
begin
Update STUDENTS
set major = v_newmajor
where fname = v_fname and
lname = v_lname;
if SQL%NOTFOUND then
INSERT INTO students (id, fname, lname, major)
VALUES
(stu_seq.nextval, v_fname, v_lname, v_major);
end if;
end;
.
/
 The Declare Section
• The declaration section begins a PL/SQL block. It
begins with the “declare” keyword, followed by a list of
variable and cursor definitions.

• Declaration syntax for variables:

<var_name> [constant] <data_type> [:=
<initial_val>];

• Declaration syntax for anchored variables:

<var_name> <object>%type [:= <initial_val>];
<var_name> <object>%rowtype [:= <initial_val>];
where object can be a database object.
 Declaration Examples
declare
amount integer(10);
name varchar2(25);
hiredate date;
commission real(5,2) := 12.5;
pi constant number(9,7) :=
3.1415926;

cursor emp_cursor is
select * from employee;
emp emp_cursor%ROWTYPE;
ename employee.name%type;
 Variable Declaration
• PL/SQL variables can be of the same type as
database columns
– Varchar2(20), date, number(5), …

• Additional PL/SQL specific types:

– Binary_integer (used to store signed integer
values)
– Boolean
– User defined types (objects)
 Cursor Variable

 A cursor is used to process multiple rows retrieved

from the database. Using a cursor, your program can
step through the set of rows returned one at a time,
processing each one.
 The Executable Section

• The executable section begins with the

keyword “begin”. Executable statements
include:

– Assignments
– Programming constructs such as IF … THEN
… ELSE … END IF, WHILE, FOR, GOTO …
– SQL data manipulation statements
– OPEN and CLOSE cursor statements

• variable := expression; (an assignment

statement)
 Conditional Control
• if (…) then … end if;
• if (…) then … else … end if;
• if (…) then … elsif (…) then … ... else
… end if;
• For example:

if SQL%NOTFOUND then
INSERT INTO STUDENTS (id, fname,
lname, major) VALUES
(stu_seq.nextval, v_fname,
v_lname, v_major);
end if;
 Cursor Attributes
• A cursor has 4 attributes each returning a value that can be
used in expressions.

• %FOUND: a boolean attribute, TRUE if the previous

FETCH returned a row, FALSE otherwise.

• %NOTFOUND: opposite of %FOUND.

• %ISOPEN: TRUE if the associated cursor is open, FALSE

otherwise.

• %ROWCOUNT: the number of rows fetched by the cursor

so far.
 Loop Control
• loop …... end loop;
– Make sure you have an “exit when …” statement
in the loop.
• for count in 1..7 loop …… end loop;
– count is declared as an variable, does not need to
be initialized or incremented.
• for rad_val in rad_cursor loop …… end loop;
– rad_cursor must be declared as a cursor. rad_val
must be declared as an anchored variable. Do not
need to open or close the cursor. Do not need to
fetch explicitly.
• while (…) loop …… end loop;
 Using SQL in PL/SQL

• The only SQL statements allowed in a PL/SQL

program are DML and transaction control statements.

• DML: SELECT, INSERT, UPDATE, DELETE

• Transaction Control: COMMIT, ROLLBACK, SET

TRANSACTION, SAVEPOINT
 Transaction Control
COMMIT;
SET TRANSACTION;
Update ACCOUNTS set blnce = blnce – t_amt
where actno = from_acct;
Update ACCOUNTS set blnce = blnce + t_amt
where actno = to_acct;
Savepoint A;
Insert into …..;
if (condition) then rollback to savepoint A;
Savepoint B;
more SQL commands …
if (condition2) then rollback to savepoint B;
COMMIT;
 The Exception Handling Section

• When user-defined or system-related exceptions

occur, the control of the PL/SQL block is transferred
to the exception handling section which always begin
with the “exception” keyword.

• The exception handling section must precede the

“end” keyword that terminates the executable section.
 Sample Exceptions

• CURSOR_ALREADY_OPEN
• DUP_VAL_ON_INDEX (during insert)
• INVALID_CURSOR (cursor not open)
• INVALID_NUMBER (e.g. ’34+some’)
• NO_DATA_FOUND (select .. Into returns no rows)
• TOO_MANY_ROWS (select returns more than 1 row)
• VALUE_ERROR (assignment or select into)
• ZERO_DIVIDE
• OTHERS
 Exception example
• declare
• x number := 5;
• y number;
• z number;
• begin
• z := sqrt(x);
• y := 1 / z;
• z := x + y;
• Exception
• when others then
• handle_error(…);
• end;

• /
 A PL/SQL Block
• declare
• <declaration section>
• begin
• <executable section>
• exception
• <exception handling section>
• end
 Kinds of PL/SQL BLOCKS
The basic unit in any PL/SQL PROGRAM is a BLOCK.
All PL/SQL programs are composed of a single block or
blocks that occur either sequentially or nested within
another block. There are two kinds of blocks:
Anonymous blocks are generally constructed
dynamically and executed only once by the user. It is
sort of a complex SQL statement.
Named blocks are blocks that have a name associated
with them, are stored in the database, and can be
executed again and again, can take in parameters, and
can modify and existing database.
Structure of Anonymous Block
DECLARE
/* Declare section (optional). */

BEGIN
/* Executable section (required). */

EXCEPTION
/* Exception handling section
(optional). */

END; -- end the block (do not forget

the “ ; “ in the end.)
/
 Example (Anonymous Block)
Declare
v_employee employee%rowtype;
Begin
select *
Selects entire row of data into 1
into v_employee
variable called v_employee
from employee
where empid = 65284;
Is updating the value of
salary based on selected
element of a variable
update employee
set salary = v_employee.salary + 1000
where empid = v_employee.empid;
End;
Example of Anonymous Block
SET SERVEROUTPUT ON
DECLARE
v_last_name VARCHAR2(15);
v_salary employee.salary%TYPE;
CURSOR c_query IS
SELECT lname, salary FROM employee;
BEGIN
OPEN c_query;
LOOP
FETCH c_query INTO v_last_name, v_salary;
IF v_salary >= 40000 THEN
DBMS_OUTPUT.PUT_LINE (v_last_name||' '||v_salary);
END IF;
EXIT WHEN c_query%NOTFOUND;
END LOOP;
CLOSE c_query;
END;
/
 PL/SQL Build-In Utility Packages
DBMS_OUTPUT.PUT_LINE
• SET SERVEROUTPUT ON to allow output to
be displayed to the screen
• DBMS_OUTPUT.PUT_LINE
– Usage:
DBMS_OUTPUT.PUT_LINE ( Argument )
– Argument tends to resemble the concatenated
arguments of the SELECT clause in an SQL query.
– If the argument is not initialized, then a NULL
VALUE will be displayed.
 PL/SQL Build-In Utility Packages
Example
SET SERVEROUTPUT ON
DECLARE
v_name VARCHAR2(30);
v_title VARCHAR2(20);
BEGIN
v_name := 'James Dullea';
v_title := 'Research Scientist';
DBMS_OUTPUT.PUT_LINE(v_name||'
works as a '||v_title);
END;
/
 NAMED BLOCKS
The following are types of NAMED BLOCKS
• Stored Procedures
Similar to an anonymous block except it can be
stored in the database, can accept parameters,
and can be executed over and over again (with
different parameters)
• Functions
Type of named blocks that is executed within a
DML or SQL statement. It may take in one or
more parameters and Returns only one value back
to the calling application.
• Triggers
A named block that executes only when an
associated DML statement is executed, such as
an INSERT, UPDATE, or DELETE statement.
Block Structure for PL/SQL Subprograms
(Procedures or Functions)

• Program Comments (optional)

• Header (mandatory)
• IS|AS (mandatory)
Declaration Section
• BEGIN (mandatory)
Executable Section
• EXCEPTION (optional)
Exception Section
• END; (mandatory)
/
 Block Structure for PL/SQL subprograms
Completed procedure example

CREATE OR REPLACE PROCEDURE AddDepartment

/*Procedure to add rows In the COMPANY.department table */
(p_DepartmentName IN department.dname%TYPE,
p_DepartmentNumber IN department.dnumber%TYPE,
p_DepartmentManager IN department.mgrssn%TYPE,
p_ManagerStartDate IN department.mgrstartdate%TYPE) AS
BEGIN
INSERT INTO DEPARTMENT(dname, dnumber, mgrssn,
mgrstartdate)
VALUES
(p_DepartmentName, p_DepartmentNumber,
p_DepartmentManager, p_ManagerStartDate);
COMMIT;
END AddDepartment;
/
 
 Syntax of a Stored Functions
CREATE OR REPLACE FUNCTION function_name
(parameters IN datatypes)
RETURN datatype
IS|AS
Declaration Section
BEGIN
Executable Section
RETURN variable_name
EXCEPTION (optional)
Exception Section
END;
 Example of a Stored Functions

1.Given the salary of an employee,

calculate the state tax of 2.8% from the
salary and return it.
 Example of a Stored Functions

CREATE OR REPLACE FUNCTION CalcStateTax (

p_salary IN employee.salary%TYPE)
RETURN NUMBER
AS
BEGIN
RETURN (p_salary * 0.028);
END CalcStateTax;
/
Execution of a Stored Functions

SELECT fname||' '||lname AS "EMPLOYEE",

CalcStateTax(salary) AS "STATE TAX"
FROM employee;
 Execution of a Stored Functions

EMPLOYEE STATE TAX

------------------------------- ----------
James Borg 1540
Jennifer Wallace 1204
Franklin Wong 1120
John Smith 840
Alicia Zelaya 700
Ramesh Narayan 1064
Joyce English 700
Ahmad Jabbar 700

8 rows selected.
 What is a Trigger
Similar to stored procedures and functions.
• Contains a Declaration, Executable, and Exception
sections

Differences
• Triggers are not executed explicitly, they are implicitly
execute when a triggering event occurs. (This is called
firing the trigger)

• Triggers do not accept parameters

• Triggering events are fired by DML Statements

( INSERTs, UPDATEs, or DELETEs) against tables or
views AND certain system events
 Why Use Triggers
• Complex integrity constraints are not always
possible through declarative constraints enabled
at table creation time, such as salary may not be
lowered.
• Auditing information, such as who updated an
employee's salary, may be required. Remember
triggers happen at the basic DML level.
• Triggers can signal other application that action
needs to take place when changes are made to a
table. Example, update employee statistics
contained in another table.
 Block Structure for a PL/SQL Trigger
CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF a_trigger_event
ON table_name (or view_name)
[FOR EACH ROW[WHEN trigger_condition]]
DECLARE (optional)
BEGIN (mandatory)
Executes only when trigger_condition is TRUE on a ROW LEVEL
TRIGGER
EXCEPTION (optional)
Exception Section
END; (mandatory)
NOTE: a_trigger_event may be any combination of an
INSERT, DELETE, and/or UPDATE on a table or view
 Errors and Error Handling
Errors can be classified into two types:
1) Compile-Time errors and warnings.
After compiling use the command
SHOW ERRORS
use SET ECHO ON to see statement numbers

2) Run-Time errors occur during execution and

throw exception that can be handled by the program.
 Error Handling
When errors occur during the execution,
control will be branched to the exception
handling section.

A corresponding error handler will be found

to deal with the error.