Unit 1

PL/SQL
Introduction to PL/SQL
 SQL does not have any procedural
capabilities such as looping and
branching nor does it have any
conditional checking capabilities vital for
data testing before storage.
 For this, Oracle provides PL/SQL.
Programmers can use it to create
programs for validation and manipulation
of table data.
PL/SQL
 PL/SQL is basically an extension of SQL. It has:

 SQL data manipulation statements like INSERT,

SELECT, UPDATE and DELETE
 SQL transaction processing statements like
COMMIT, ROLLBACK
 SQL functions
 SQL predicates
- Comparison operators like BETWEEN.. AND, EXIST
- Logical operators AND, OR and NOT
- Combined with programming constructs like:
a) Assignment statements(A:= B - C)
b) Flow of control statements (IF...THEN..ELSE...)
c) Iterative statements (FOR..LOOP, WHILE..LOOP).
Advantages of PL/SQL
 Procedural Capabilities

 Improved Performance

 Enhanced Productivity

 Portability

 Integration with RDBMS

Data Types: PL/SQL
 CHAR Type
 VARCHAR2 Type
 NUMBER
 DECIMAL
 CONSTANTS
 DATE Type
Operators
 Comparison  Is NULL/Not NULL
 Relational  Like Operator
Operators  BETWEEN Operator
 <  IN Operator
 >  Logical
 =  AND
 <=  OR
 >=  NOT
 !=
Statements in PL/SQL
 DBMS_OUTPUT.PUT_LINE – print output on
screen
 := assignment statements
 -- single line comment, /* */ multiline
comment
 standard Oracle date format is
DD-MON-YY
e.g. '13-NOV-92‘
 :x to get input from user at runtime
(instead of x , you can take any variables)
PL/SQL Block
 A standard PL/SQL code segment is
called a block. A standard PL/SQL
block is made up of three sections.
 Declaration Section
 Executable Statements
 Exception Handling Section
PL/SQL Block Structure
Declaration Section (Variables)

DECLARE

fname varchar2(30);
lname varchar2(30);
Counter number :=0;
Anchored data type
Declarations
 %TYPE declaration attribute
 to anchor the datatype of one
variable to another data structure,
such as a PL/SQL variable or a column
in a table.
 When you anchor a datatype, you tell
PL/SQL to set the datatype of one
variable from the datatype of another
element.
Anchored data type
Declarations
 The syntax for an anchored datatype
is:
 <variable name> <type attribute>
%TYPE [optional default value
assignment];
 where <variable name> is the name
of the variable you are declaring and
<type attribute> is Table column in
format "table.column"
Anchored data type
Declarations
Executable Section
Begin

Select fname, lname into fname, lname from

student where sid=‘1’;
DBMS_OUTPUT.PUT_LINE(‘ student name : ‘ ||
fname || ‘ ‘ || lname );

End;
 Exception Handling Section
 An error condition during a program execution is called
an exception in PL/SQL
 There are two types of exceptions:
 System-defined exceptions
 User-defined exceptions
 Syntax:
 EXCEPTION
WHEN exception1 THEN exception1-handling-statements
WHEN exception2 THEN exception2-handling-statements
WHEN others THEN exception3-handling-statements END
System-defined exceptions
 System-defined exceptions are raised by database
server automatically whenever there is any
internal database error
 E.g.
Exception
When No_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘ There is no student
with ‘ || ‘ student id 123 ‘ );
End;
 Exceptions can be used explicitly by programmer
by using Raise command
User-defined Exceptions
 PL/SQL allows to define your own exceptions as per
the need of your program
 User defined exceptions must be declared and then
raised explicitly using raise command
 Syntax:
DECLARE ex_invalid_id EXCEPTION;
BEGIN if condition then
raise ex_invalid_id;
EXCEPTION
WHEN ex_invalid_id THEN
dbms_output.put_line('ID must be greater than zero!');
End;
PL/SQL examples
Example 1: find record from
student table
SQL> create table student(SID number, FIRSTNAME varchar2(20), LASTNAME
varchar2(20));
SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NUMBER
FIRSTNAME VARCHAR2(20)
LASTNAME VARCHAR2(20)

SQL> select * from student;

SID FIRSTNAME LASTNAME
1 seema patil
2 pooja kamat
3 suman tanwar
Example 1: find record from
student table
Declare
fname varchar2(20);
lname varchar2(20);
Begin
Select firstname, lastname into fname, lname from student where sid=1;
DBMS_OUTPUT.PUT_LINE(' student name : '|| fname || ' ' || lname );
Exception
When No_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(' There is no student with '|| ' student id 1
');
End;
Example 1: find record from
student table
Output 1: (sid 1)
student name : seema patil
Statement processed.
Output 2: (sid 4)
There is no student with student id 4
Statement processed.
 Example 2: Calculate power of no

DECLARE
vnum number;
vresult number;

Begin
vnum:= :num;
vresult := power(vnum,2);
Dbms_output.put_line('result is ' || vresult);
End;
Example 3: Determine day of week
based on today's date
Declare
V_day varchar2(20);
Begin
V_day:=to_char(sysdate,'day');
Dbms_output.put_line('Today is :' || v_day);
End;
Conditional Control statements

 If – then statement
 If – then – else statement
 ELSIF statement (nested if else
statement)
IF-THEN Statements Start IF

 If Condition then
 Statement1; is

 ………………….
 Statement n;
 End if Execute statements

End IF

Next stmt
IF-THEN example
Declare
V_num1 number:=5;
V_num2 number:=3;
V_temp number;
Begin
If v_num1>v_num2 then
V_temp:= v_num1;
V_num1:=v_num2;
End if;
Dbms_output.put_line('num1 is :' || v_num1);
Dbms_output.put_line('num2 is :' || v_num2);
End;
IF-THEN-ELSE
Start IF
If condition then
Stmt1; Y Is
Cond
N
true

Else
Stmt2 Execute
Execute
stmt1
stmt2
End if
Stmt3; End if

Next stmt
IF-THEN-ELSE Example
Declare
V_num number:= :v_num;
Begin
If mod(v_num,2)=0 then
Dbms_output.put_line(v_num|| ' is even no');
else
Dbms_output.put_line(v_num|| ' is odd no');
End if;
Dbms_output.put_line('done');
End;
ELSIF
If condition1 then
Stmt1;
Elsif condition2 then
Stmt2;
Elsif condition3 then
Stmt3;
Else
Stmt n;
End if;
ELSIF example
Declare
vnum number := :v_num;
Begin
If vnum<0 then
Dbms_output.put_line(vnum|| ' is –ve no');
Elsif vnum=0 then
Dbms_output.put_line(vnum|| ' is equal to 0');
else
Dbms_output.put_line(vnum|| ' is +ve no');
end if;
end;
Iterative Control statements
 Simple loop
 Exit statement
 While loop
 For loop
 Simple Loop

Loop
Stmt1; Start loop
Stmt2;
Stmt N;
End loop;
Execute stmts

Example:
Loop End loop
ctr:= ctr + 1;
IF ( ctr > 5) THEN
END IF;
END LOOP;
Exit

Loop
Start loop
Stmt1; No
Stmt2;
If condition then Execute stmt
Exit;
End if;
Is exit condn
End loop; true
Example: yes
LOOP
ctr:= ctr + 1; Exit loop
EXIT WHEN ctr > 5;
END LOOP;
Next stmt
Simple loop example
Declare
counter number:=0;
Begin
Loop
counter:=counter+1;
Dbms_output.put_line('counter = ' || counter);
Exit when counter>=5;
End loop;
End;
Simple loop example
 Output:
counter = 1
counter = 2
counter = 3
counter = 4
counter = 5
Statement processed.
WHILE LOOP
WHILE cond loop
stmt1; No
Is condn true

stmt2;
………….. Yes

stmtN Execute stmt

No
End loop
End loop

Next stmt
While Loop example
Declare
v_counter number:=5;
Begin
While v_counter>0 loop
Dbms_output.put_line('v_counter:' || v_counter);
v_counter:=v_counter-1;
End loop;
dbms_output.put_line('done');
End;
While Loop example
 Output:
v_counter:5
v_counter:4
v_counter:3
v_counter:2
v_counter:1
done
Statement processed.
For Loop
For loop_counter IN [REVERSE] lower_limit..upper_limit
loop
Initialize counter
stmt1;
stmt2;
………… Is counter
bet lower &
StmtN; Upper
limits

end loop;
yes
No
Exeute stmt

Increment counter

Next stmt
For Loop example
Begin
For v_counter IN 1..5 loop
Dbms_output.put_line('v_counter' || v_counter);
End loop;
End;

v_counter 1
v_counter 2
v_counter 3
v_counter 4
v_counter 5
Statement processed.
For Loop example
Begin
For v_counter in reverse 1..5 loop
Dbms_output.put_line('v_counter' || v_counter);
End loop;
End;

v_counter 5
v_counter 4
v_counter 3
v_counter 2
v_counter 1
Statement processed.
PL/SQL DATABASE OBJECTS
Pl/SQL database objects
 Procedures
 Functions
 Triggers
 Cursor
Functions
 These subprograms return a single
value, mainly used to compute and
return a value.
 A function must return a value back
to the caller. A function can return
only one value
Function Syntax

create [or replace] function function_name

(parameter list)
return datatype;
IS
BEGIN
<body>
RETURN (return_value);
END;
Function example
CREATE OR REPLACE FUNCTION evenodd(num number)
return number
IS
result number;
BEGIN
IF mod(num,2)=0 then
result:=1;
ELSE
result:=2;
END IF;
return result;
end;
Function example
DECLARE
result number;
BEGIN
result:=evenodd(:number);
IF result=1 then
DBMS_OUTPUT.PUT_LINE('THE NUMBER IS EVEN!');
ELSE
DBMS_OUTPUT.PUT_LINE('THE NUMBER IS ODD!');
END IF;
DBMS_OUTPUT.PUT_LINE('DONE!');
END;
Function Assignments
 CREATE A FUNCTION TO FIND THE FACTORIAL OF A
NUMBER.

 CREATE A FUNCTION TO CHECK IF A NUMBER IS PRIME

OR NOT.

 CREATE A FUNCTION TO FIND THE SUM OF THE 1ST 100

ODD NUMBERS.

 CREATE A FUNCTION TO FIND THE SUM OF THE 1ST 100

EVEN NUMBERS
Procedures
 Procedure is a module performing one or
more actions;
 It does not return any value.
 By defining multiple out parameters in a
procedure, multiple values can be
passed to the caller.
Procedure syntax
Create or replace Procedure name
[ (parameter list) ]
As
[local declarations]
Begin
Executable statements
end [name];
Procedures parameters

Mode Description Usage

IN Passes a value into Read only value
the program
OUT Passes a value Write only value
back from program

IN OUT Passes values in Values will be read

and also sends and then written.
values back.
 Procedure example 1

 Create a simple procedure that displays the string

'Hello World!' on the screen when executed
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN dbms_output.put_line('Hello World!');
END;
 The procedure can also be called by using
BEGIN
greetings;
END;
Procedure example 2
Create or replace procedure find_sname
( studentid in number,
firstname out varchar2,
lastname out varchar2)
As
Begin
Select firstname,lastname into firstname,lastname
from student where sid=studentid;
End;
Procedure example 2

Declare
Local_first_name varchar2(50);
Local_last_name varchar2(50);
Begin
find_sname(1,Local_first_name,Local_last_name );
Dbms_output.put_line(Local_first_name);
Dbms_output.put_line(Local_last_name);
End;
Trigger
 Trigger is a named PL/SQL block stored
in a database and executed implicitly
when a triggering event occurs.
 Triggering event is a DML statement
executed against database table.
 Trigger can fire before or after a
triggering event.
 To check triggers in database
 SELECT * FROM user_triggers;
Uses of database Triggers
 It provides a highly customizable database
management system
 A trigger can permit DML statements against a
table only if they are issued, during regular
business hours or on predetermined weekdays
 It can be used to keep an audit trail of the table
 It can be used to prevent invalid transactions
 Enforce complex security authorization
Database Triggers V/S
Procedures
 Triggers do not accept parameters
whereas procedures can
 A triggers is executed implicitly by the
oracle engine itself upon modification
of an associated table or its data
 To execute a procedure, it has to be
explicitly called by a user
 Types of Triggers
 A Row trigger: fires once for each row
affected. It uses FOR EACH ROW clause.
 Statement Trigger: fires once, irrespective
of number of rows affected in the table.
 Combination trigger:
 Before statement trigger
 Before row trigger
 After statement trigger
 After row trigger
Types of Triggers
Before and after Triggers
While defining the trigger we can specify whether to
perform the trigger action (i.e. execute trigger body)
before or after the triggering statement. BEFORE and
AFTER triggers fired by DML statements can only be
defined on tables.

BEFORE triggers The trigger action here is run

before the trigger statement.

AFTER triggers The trigger action here is run after

the trigger statement.
Syntax for creating trigger
Create or replace trigger <TriggerName>
{Before, After} {delete, insert, update }
On [schema.] <table name>
[Referencing {OLD as old, NEW as new}]
[For each row [When condition]]
Declare
<variable declaration>;
<variable declaration>;
Begin
<PL/SQL subprogram body>;
Exception
<Exception PL/SQL block>;
End;
Trigger Example 1
Create or replace trigger tr1
Before insert or update or delete on employee
Begin
if to_char(sysdate,'dy')='thu' then
raise_application_error(-20112,'Invalid operation
on employee table');
else
dbms_output.put_line('welcome');
end if;
End;
 Trigger Example 1: output
insert into employee values (5, 'amit',45000,'20-dec-89');

ORA-20112: Invalid operation on employee table

ORA-06512: at "SYSTEM.TR1", line 3
ORA-04088: error during execution of trigger
'SYSTEM.TR1'
1. insert into employee values (5, 'amit',45000,'20-
dec-89');
Trigger Example 2
CREATE OR REPLACE TRIGGER display_mark_changes
BEFORE UPDATE ON student
FOR EACH ROW
WHEN (NEW.SID > 0)
DECLARE
mark_diff number;
BEGIN
mark_diff := :NEW.marks - :OLD.marks;
dbms_output.put_line('Old marks: ' || :OLD.marks);
dbms_output.put_line('New marks: ' || :NEW.marks);
dbms_output.put_line('marks difference: ' || mark_diff);
END;

Trigger created.
Trigger Example 2
select * from student;
SID FIRSTNAME LASTNAME MARKS
1 seema patil 150
2 pooja kamat -
3 kavita buchade 160

update student set marks=200 where sid=2;

Old marks:
New marks: 200
marks difference:
welcome back 1 row(s) updated.

select * from student;

SID FIRSTNAME LASTNAME MARKS
1 seema patil 150
2 pooja kamat 200
3 kavita buchade 160
Trigger Example 3
create or replace trigger audit_sal
after update of sal on student
for each row
begin
insert into emp_audit
values(:old.empid,sysdate,:old.sal,:ne
w.sal);
end;
Cursor
 A cursor is a memory area, known as
context area, for processing an SQL
statement, which contains all information
needed for processing the statement.
 Cursors allow you to fetch and process
rows returned by a select statement, one
row at a time.
 Cursor is named so that it can be
referenced.
Types of cursors: Implicit Cursor
 Implicit cursor is automatically declared by
oracle every time an sql statement is
executed. The user will not be aware of this
happening.

 Cursor is automatically associated with every

DML statement.

 All Update and delete statements have

cursors that identify the set of rows that will be
affected by operation.
Types of cursors: Explicit Cursor
 Explicit cursor is defined by the program for
any query that returns more than one row of
data.
 Declaring the cursor :- This initializes the cursor into
memory.
 Opening cursor :- The previously declared cursor can
now be opened; memory is allotted.
 Fetching the cursor :- previously declared and opened
cursor can now retrieve data; this is the process of
fetching the cursor.
 Closing the cursor :- Previously declared, opened, and
fetched cursor must now be closed to release memory
allocation.
 Cursor Attributes
Cursor Attribute Syntax Explanation
%NOTFOUND Cursorname%NOTFOUND Returns true if the
previous fetch didn’t
return row and false if it
did.
%FOUND Cursorname%FOUND Returns true if the
previous fetch returned a
row ,and false if it did
not.
%ROWCOUNT Cursorname%ROWCOUNT Records fetched from
cursor at that point in
time.
%ISOPEN Cursorname%ISOPEN Returns true if cursor is
open,false if it is not.
Example: Cursors allow you to fetch and process rows
returned by a select statement, one row at a time.

declare
vname varchar2(20);
begin
select firstname into vname from student;
dbms_output.put_line('name is ' || vname);
end;

ORA-01422:
exact fetch returns more than requested number of rows
Cursor Example 1
Declare Cursor student1 is
Select firstname from student;
Vname student.firstname%type;
Begin
Open student1;
Loop
Fetch student1 into vname;
Exit when student1%notfound;
Dbms_output.put_line('Student name is ' ||
vname);
End loop;
Close student1;
end;
Output: Student name is seema
Student name is pooja
Student name is kavita
Statement processed.
Cursor Example 2
Declare Cursor student1 is
Select firstname, marks from student;
Vname student.firstname%type;
student_marks student.marks%type;
Begin
Open student1;
Loop
Fetch student1 into vname,student_marks;
Exit when student1%notfound;
Dbms_output.put_line('Student name is ' || vname ||' and marks: ' ||
student_marks);
End loop;
Close student1;
end;
Cursor Example 2: Output
Student name is john and marks: 800
Student name is amit and marks: 700
Student name is sumit and marks: 390
Student name is neil and marks: 300
Student name is smith and marks: 500
Student name is bob and marks: 600
Student name is xyz and marks: 400
Statement processed.
Cursor Example 3
 create table emp(empno number(4,0),ename
varchar2(10),job varchar2(9),mgr number(4,0),hiredate
date, sal number(7,2), comm number(7,2),deptno
number(2,0));

 insert into emp values( 7839, 'KING', 'PRESIDENT', null,

to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10 );
 insert into emp values( 7698, 'BLAKE', 'MANAGER', 7839,
to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30 );
 insert into emp values( 7566, 'JONES', 'MANAGER', 7839,
to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20 );
Cursor Example 3
declare
cursor employee is
select ename,job from emp
where sal>2000;
vname emp.ename%type;
desg emp.job%type;
begin
open employee;
loop
fetch employee into vname,desg;
exit when employee%notfound;
dbms_output.put_line('name is '||vname);
dbms_output.put_line('designation is '||desg);
end loop;
close employee;
end;
Cursor Example 3
name is KING
designation is PRESIDENT
name is BLAKE
designation is MANAGER
name is CLARK
designation is MANAGER
Statement processed.
Cursor assignment
 Write a cursor to count the number of
employees from emp table.
 Write a cursor to find experience of all
employees.
Programmatic SQL
 SQL standard lacked computational
completeness:
 It contained no flow of control commands
such as
IF . .. THEN . . . ELSE, exit, or loop.
 SQL allows statements to be embedded in
a high-level procedural language, as well as
being able to enter SQL statements
interactively at a terminal
 In many case, the SQL language is identical
Types of Programmatic SQL
 Embedded SQL statements
 ISO standard specifies embedded support for C,
COBOL, Fortran, Pascal
 Dynamic SQL
 Application Programming Interface (API)
 ODBC - open database connectivity
 JDBC – Java database connectivity
 Procedure SQL
 PL/SQL
Embedded SQL statements
 SQL statements are embedded directly into the
program source code and mixed with the host
language statements
 This approach allows users to write programs that
access the database directly.
 A special precompiler modifies the source code to
replace SQL statements with calls to DBMS routines.
 The source code can then be compiled and linked in
the normal way
 The ISO standard specifies embedded support for
Ada, ‘C’, COBOL, Fortran, MUMPS, Pascal etc
Dynamic SQL
 There are many situations where the pattern of
database access is not fixed and is known only at
runtime
 E.g. the production of a frontend that allows users to
define their queries or reports graphically, and then
generates the corresponding interactive SQL
statements, requires more flexibility than static SQL.
The ISO standard defines an alternative approach for
such programs called dynamic SQL
 The basic difference between the two types of
embedded SQL is that static SQL does not allow host
variables to be used in place of table names or
column names