PL/SQL

© 2009 Wipro Ltd – Internal & Restricted

Agenda

1 Introduction to PL/SQL

2 PL/SQL Programming Constructs

3 PL/SQL Language Features

4 SQL in PL/SQL

5 Control Structures

2 © 2009 Wipro Ltd – Internal & Restricted

Agenda

6 Stored sub programs

7 Cursors and Data Types as in 3GL

8 Cursors

9 Packages

10 Exceptions

3 © 2009 Wipro Ltd – Internal & Restricted

Agenda (Contd.).

11 Triggers

12 Dependencies among PL/SQL Constructs

4 © 2009 Wipro Ltd – Internal & Restricted

 1. Introduction to PL/SQL

5 © 2009 Wipro Ltd – Internal & Restricted

PL/SQL - Introduction
• Integrating Programming Features with SQL
Programming Language +SQL=PL/SQL

• PL/SQL submits the request as a Block

6 © 2009 Wipro Ltd – Internal & Restricted

What is PL/SQL Block
• PL/SQL Block comprises of
– SQL Statements
– PL features like
• Conditional Statements
• Looping Constructs
– Additional features like
• Stored Procedures
• Exceptions
• Triggers

7 © 2009 Wipro Ltd – Internal & Restricted

Why PL/SQL?
• Connectivity exists between consecutive requests in PL/SQL
• Logical implementation of user’s requirement is made possible with
PL/SQL

8 © 2009 Wipro Ltd – Internal & Restricted

PL/SQL Engine
• It is a separator, which splits the SQL and PL/SQL statements.
• PL/SQL is available
– Client Side
– Server Side

9 © 2009 Wipro Ltd – Internal & Restricted

Where PL/SQL stands?

Front end application

Program

…
…. Query
Invoking
PL/SQL SQL DATABASE
PL/SQL
PROCEDURE Data
….
….

………………..SERVER…………..……..

10 © 2009 Wipro Ltd – Internal & Restricted

PL/SQL Block
[Declare

Variable Declarations (if any) ]

Begin

SQL / PL-SQL Statements

[ Exception]
….
End;

11 © 2009 Wipro Ltd – Internal & Restricted

Advantages of PL/SQL
• Block structured
– SQL statements are grouped and send as one consolidated request to
the server.
• Improved Performance
– Network traffic can be reduced
• Security
– Access to the data through PL/SQL
– Limited actions allowed over the table through PL/SQL
• Portable
– Work wherever Oracle Works

12 © 2009 Wipro Ltd – Internal & Restricted

Advantages-Contd.
• Compatibility
– Supports all the data types available in Oracle/SQL.
• Stored Procedures
– Additional Features like Stored procedures and Triggers are included
– Stored Procedures are available in the server as database objects and is
ready to execute

13 © 2009 Wipro Ltd – Internal & Restricted

Nesting of Blocks
• Blocks within the other Block
• Sub Blocks are allowed to enhance the modularity
• Inner blocks follow the similar syntax
• The variables declared in the inner block is valid till the end of that block
• No limitations on number of nested blocks

14 © 2009 Wipro Ltd – Internal & Restricted

 2. PL/SQL Program Constructs

15 © 2009 Wipro Ltd – Internal & Restricted

PL/SQL Program Constructs

Anonymous
Block Packages

PL/SQL
BLOCK
Stored
Procedure/ Triggers
Programs

Procedures Functions

16 © 2009 Wipro Ltd – Internal & Restricted

Anonymous Block
• PL/SQL block without a name for identification
• It has to be brought to the server’s memory every time it is required.
• It can’t be accessed/referred neither by oracle nor by user for execution
• Has to be compiled every time we need that block of statements
• Both compilation and execution are done at the server end.

17 © 2009 Wipro Ltd – Internal & Restricted

Stored Procedures
• Named PL/SQL Block
• Stored as Database Objects like Tables, Views, Indexes, Sequences,
Synonyms
• Stored in the ready to executable form
• Can be referred by other PL/SQL block
• One copy is brought to the buffer for sharing by multiple users

18 © 2009 Wipro Ltd – Internal & Restricted

Packages
• Collection of related objects
• The components can be variables, Procedures or Functions
• Collectively identified as one Database object

19 © 2009 Wipro Ltd – Internal & Restricted

Triggers
• The PL/SQL Block which gets fired automatically whenever some
specified event happens
• The chain of actions to be performed can be written in the trigger body for
continual execution
• Will be invoked by oracle automatically

20 © 2009 Wipro Ltd – Internal & Restricted

 3. PL/SQL Language Features

21 © 2009 Wipro Ltd – Internal & Restricted

Glimpse of PL/SQL Block
Declare
v_designation varchar2(15); Variable declaration

Begin
select job
into v_designation Data Retrieval from Table
into Variable
from emp
where ename='SCOTT';
if (v_designation = 'MANAGER') then
update emp set sal=sal*1.5 where ename='SCOTT';
else
update emp set sal=sal*1.05 where ename='SCOTT';
End if;
End;

22 © 2009 Wipro Ltd – Internal & Restricted

Lexical Unit
• Character Set
– Alphabets (both upper and lower)
– Numerals
– Special Characters like #,%,$,etc.,
• Commenting
– Two Consecutive hyphens -- for single line comment and /*…..*/ for
multi line comments
• Delimiter
– Every statement in PL/SQL Block is separated using semicolon(;)

23 © 2009 Wipro Ltd – Internal & Restricted

Variables
• Identifier is the name given for variables
• Identifiers used to hold values that is to be used in PL/SQL block
• All the variables must be declared before the begin and end block
• Scope of this variable is within the block

24 © 2009 Wipro Ltd – Internal & Restricted

Rules-Identifier
• It is user defined name and should not be oracle defined names (reserved
words)
• It should start with an alphabet
• Can have zero or more additional characters
– 0 to 9 or Alphabets(A-Z or a-z)
– Symbols like # or $ or _ are also allowed
• Follow Standards – Naming Conventions

25 © 2009 Wipro Ltd – Internal & Restricted

Data Types
• Primitive Data Type
– Numeric – Number, Integer
– Character –Char,Varchar2,Varchar,Long
– Date
– Boolean
• Composite Data Type
– Record, Tables
– Cursors

26 © 2009 Wipro Ltd – Internal & Restricted

Declaration - Example
Syntax is
Variable Name Data Type [ DEFAULT ]
[ NOT NULL ] [ NULL ];
Example
Salary Number(7,2);
HRA Number(7,2) default 3000;
DA Number(7,2):=2000;

27 © 2009 Wipro Ltd – Internal & Restricted

Declaration
• Identifiers are to be declared before their usage
• Map to relevant data types
• Memory and initialization will be done every time it is executed
• Multiple Declaration is not allowed

28 © 2009 Wipro Ltd – Internal & Restricted

Scope & Visibility of Variables
In nested Blocks
Declare
A number: =200;
BEGIN
…….
Declare --Inner Block Starts What would be
A number:=100; the value of A?

BEGIN
DBMS_OUTPUT.PUT_LINE(A); -- Print A
…..
A is 100 or 200?
END; -- Inner Block ends
DBMS_OUTPUT.PUT_LINE(A);
END;

29 © 2009 Wipro Ltd – Internal & Restricted

LABEL << … >>
<<outer>>
Declare
A number: =200;
BEGIN
…….
Declare --Inner Block Starts
Print 100 and
A number:=100;
then 200
BEGIN
DBMS_OUTPUT.PUT_LINE(A || outer.A); …..
END; -- Inner Block ends
DBMS_OUTPUT.PUT_LINE(A);
END;

30 © 2009 Wipro Ltd – Internal & Restricted

Operators-Assignment
• := is the assignment operator in PL/SQL
• Example A:=10;
– Assign 10 to the variable A;
• Data :=null;
– Assign null to the variable ‘Data’
– By default un-initialized variables are assigned to null irrespective of
the data type of the variable

31 © 2009 Wipro Ltd – Internal & Restricted

Operators
• Arithmetic Operators
– * , / , + , - ,mod
• Comparison Operators
– Relational Operators
• <, <=, >, >=, = and != or <> or ~=
– IS NULL, LIKE,BETWEEN,IN
• Logical Operators
– NOT, AND, OR

32 © 2009 Wipro Ltd – Internal & Restricted

Built-in-Functions
• Number
• Character
• Conversion
• Date
• Error Reporting
• Others

33 © 2009 Wipro Ltd – Internal & Restricted

Number Functions
• ROUND
• TRUNC
• MOD
• POWER,SQRT
• COS,SIN,TAN,LOG,

34 © 2009 Wipro Ltd – Internal & Restricted

Character Function
• Lower, Upper, Initcap
• Lpad, Rpad
• Ltrim, Rtrim
• Substr, Concat
• Length
• Replace, Translate

35 © 2009 Wipro Ltd – Internal & Restricted

Date -Function
• Sysdate
• Last_day
• Months_between
• Round
• Trunc
• Add_months
• New_time
• Next_day

36 © 2009 Wipro Ltd – Internal & Restricted

Conversion Function
• To_char
• To_date
• To_number
• Chartorowid
• Rowidtochar
• Hextoraw
• rawtohex

37 © 2009 Wipro Ltd – Internal & Restricted

Error Reporting Functions
• SQLERRM
• SQLCODE

38 © 2009 Wipro Ltd – Internal & Restricted

Others - Functions
• DECODE
• GREATEST
• LEAST
• NVL
• USER

39 © 2009 Wipro Ltd – Internal & Restricted

 4. SQL In PL/SQL

40 © 2009 Wipro Ltd – Internal & Restricted

Data Retrieval From PL/SQL
• SQL Select statement is the only source for data retrieval.
• From PL/SQL the data can be retrieved with the help of Select Statement
with added INTO clause.
• PL/SQL Block can include all DML,TCL commands and not DDL & DCL
commands directly

41 © 2009 Wipro Ltd – Internal & Restricted

INTO Clause
Data From SQL into PL/SQL
• Syntax of SELECT statement in PL/SQL is
SELECT column [list]
INTO variable [list]
FROM table [list]
[WHERE condition(s) ]
• SQL data retrieval – SELECT list
• PL/SQL data mapping - INTO list

42 © 2009 Wipro Ltd – Internal & Restricted

INTO Clause - Example
DECLARE
v_name varchar2(10);
v_dno number(2);
BEGIN
SELECT ename, deptno

INTO v_name, v_dno

FROM emp
WHERE empno=7788;
dbms_output.put_line(v_name||’is working for
‘||v_dno);
END;

43 © 2009 Wipro Ltd – Internal & Restricted

Example – 2
DECLARE
v_name varchar2(10);
Begin
SELECT ename
INTO v_name
FROM emp
WHERE deptno=10;
end;
• What is wrong with this query?
• Will this query work if there are more that 1 employee
working for deptno 10 ?
• Will this query work if there are no employees working for
deptno 10?

44 © 2009 Wipro Ltd – Internal & Restricted

Restrictions
• Select statement in PL/SQL can retrieve only one record at a
time
• SELECT & INTO clause should contain
– Equal number of columns list and variable list
– Similar (compatible) data types
– Order of specification of select statement corresponding to
its appropriate variable list

45 © 2009 Wipro Ltd – Internal & Restricted

DML in PL/SQL
Begin
v_eno:=7782;
v_new_mgr:=7788
update emp set mgr=v_new_mgr where mgr=v_eno;
insert into dept values(60,’FINANCE’,’TEXAS’);
delete emp where empno=v_eno;
end;
• DML commands can be executed through PL/SQL without any
modification in the syntax.
• DML Can use compatible data items

46 © 2009 Wipro Ltd – Internal & Restricted

TCL in PL/SQL
DECLARE
v_sal number(7,2);
BEGIN
update emp set sal=sal*1.50;
select sum(sal)
into v_sal
from emp;
if v_sal > 1000000 then
rollback;
else
commit;
end if;
END;

47 © 2009 Wipro Ltd – Internal & Restricted

& Substitution Variable
• Prompt the user for data
• Example
– data:=&value1;
– Select * from emp where deptno=&dno;
– Insert into dept values (&dno, &dname, &loc);
• SQL * PLUS specific command
• Prompts at compile time with the predefined message ‘enter the value for
dno:’

48 © 2009 Wipro Ltd – Internal & Restricted

Host variables
• Referred as bind variables
• Usage
– SQL:>VAR X NUMBER --declare variable
– SQL:>EXECUTE :X:=10 --define it
– SQL:>PRINT X --refer it in SQL
• Scope - Session specific

49 © 2009 Wipro Ltd – Internal & Restricted

Bind variables - Example
PL/SQL Block 1
Begin
a:=:x; --acts as global variable
:x:=:x + a; --assign 20 to variable x
end;

PL/SQL Block 2
begin
dbms_output.put_line (:x); --prints 20
end;

50 © 2009 Wipro Ltd – Internal & Restricted

 5. Control Structures

51 © 2009 Wipro Ltd – Internal & Restricted

Control Structures
• By default control flows in sequential manner - Executing the statements
one after the other
• Control can be transferred as required based on the logic using any one of
the control structures
– Conditional Statements
– Looping Constructs

52 © 2009 Wipro Ltd – Internal & Restricted

Conditional Statements
• Branches the execution by checking the condition
• Can be done using IF statements
• Types of IF statements available are
– IF….THEN….END IF
– IF….THEN….ELSE….END IF
– IF….THEN….ELSIF….END IF

53 © 2009 Wipro Ltd – Internal & Restricted

IF Statement -Example
Example 1:
IF (V_JOB=‘CLERK’) THEN
V_BONUS:=2000;
END IF;
Example 2:
IF (V_JOB=‘SALESMAN’) THEN
V_BONUS:=2000;
ELSE
V_BONUS:=1000;
END IF;

54 © 2009 Wipro Ltd – Internal & Restricted

IF –THEN-ELSE
Example 3:
IF (V_JOB=‘SALESMAN’) THEN
V_BONUS:=2000;
ELSE
IF (V_JOB=‘CLERK’) THEN
V_BONUS:=3000;
ELSE
V_BONUS:=1000;
END IF;
END IF;

55 © 2009 Wipro Ltd – Internal & Restricted

ELSIF -Example
Example 4:
IF (V_JOB=‘SALESMAN’) THEN
V_BONUS:=4000;
ELSIF (V_JOB=‘CLERK’) THEN
V_BONUS:=3000;
ELSIF (V_JOB=‘ANALYST’) THEN
V_BONUS:=2000;
ELSE
V_BONUS:=1000;
END IF;

56 © 2009 Wipro Ltd – Internal & Restricted

CASE
• To group multiple conditions
• Syntax is
CASE
WHEN condition THEN
statements;
WHEN condition THEN
statements;
ELSE
statements;
END CASE;

57 © 2009 Wipro Ltd – Internal & Restricted

Looping Constructs
• Executes set of statements repeatedly
• Looping Constructs
– LOOP….END LOOP
– WHILE …LOOP…END LOOP
– FOR…IN…LOOP…END LOOP

58 © 2009 Wipro Ltd – Internal & Restricted

LOOP…END LOOP
LOOP
…..
…..
END LOOP
• Statements between LOOP and END LOOP will be executed repeated and
also infinitely
• Use EXIT to terminate the endless looping

59 © 2009 Wipro Ltd – Internal & Restricted

EXIT
LOOP
…..
EXIT;
….
END LOOP
…..

60 © 2009 Wipro Ltd – Internal & Restricted

EXIT WHEN - Example
Loop
…
EXIT WHEN CNT>10;
…
END LOOP;

IF (CNT>10) THEN
DBMS_OUTPUT.PUT_LINE (‘Exceeded limit 10’);
EXIT;
END IF;

61 © 2009 Wipro Ltd – Internal & Restricted

WHILE LOOP
WHILE (condition)
LOOP
…
END LOOP;
• Statements between LOOP and END LOOP will be executed while the
condition is true
• Toggle the condition in loop body - to avoid infinite looping

62 © 2009 Wipro Ltd – Internal & Restricted

FOR LOOP
FOR counter IN [REVERSE]
start_value .. final_value
LOOP
…
END LOOP;
• Implicit declaration – FOR loop variable
• Scope is within the loop

63 © 2009 Wipro Ltd – Internal & Restricted

FOR LOOP - Example
FOR cnt IN 1..10 LOOP
dbms_output.put_line (cnt);
END LOOP;

FOR cnt IN REVERSE 1..10 LOOP

dbms_output.put_line (cnt);
END LOOP;

64 © 2009 Wipro Ltd – Internal & Restricted

NULL - Statement
• PL/SQL statement used to do nothing
• Blindly transfers the control to the next line without executing anything
Example
if (a=10) then
NULL;
else
dbms_output.put_line (‘a is not equal to 10’);
end if;

65 © 2009 Wipro Ltd – Internal & Restricted

 6. Stored Sub Programs

66 © 2009 Wipro Ltd – Internal & Restricted

Advantages of Subprograms
• Modular approach
• Improved Maintenance
• Stored in an executable format
• Stored as Database objects in the server
• Security
• Parameterized

67 © 2009 Wipro Ltd – Internal & Restricted

Types of Parameters
• Formal Parameters
• Actual Parameters

68 © 2009 Wipro Ltd – Internal & Restricted

 6.1. Stored Procedures

69 © 2009 Wipro Ltd – Internal & Restricted

Different Modes of Parameters
(IN, OUT, IN OUT)
• IN Parameter
– To send input values to the sub program
• OUT Parameter
– Carry output values to the calling environment
• IN OUT Parameter
– Used in both the directions to send and carry from the sub block to the
calling block

70 © 2009 Wipro Ltd – Internal & Restricted

IN Parameter
• Takes input to the sub program
• Formal Parameter
– If mode is not specified treated as IN parameter
– Is used as constant (IN parameter can’t be altered inside the sub
program)
• The actual Parameter
– Can be variable, literal or an expression
– Should be an initialized variable

71 © 2009 Wipro Ltd – Internal & Restricted

OUT Parameter
• Carries the output to the calling environment
• Formal Parameter
– Mode OUT is specified explicitly
– OUT parameter is filled with the output data
• The actual Parameter
– Must be a variable
– Should be un-initialized

72 © 2009 Wipro Ltd – Internal & Restricted

IN OUT Parameter
• Send and receive data to and from the sub block
• Formal Parameter
– Mode IN OUT is specified explicitly
– Acts as input as well as output parameter
• The actual Parameter
– Must be a variable
– Should be initialized

73 © 2009 Wipro Ltd – Internal & Restricted

Creating the Procedure
CREATE [OR REPLACE] PROCEDURE Procedure name
[(Formal parameter1 [MODE] Data type,
Formal parameter2 [MODE] Data type,
….)]
IS/AS
[Local_variable Data type]
PL/SQL Block

74 © 2009 Wipro Ltd – Internal & Restricted

Procedure - Example
Create or replace procedure retrieve(
v_eno IN number,
v_name OUT varchar2,
v_job OUT varchar2)
is
begin
select ename,job
into v_name,v_job
from emp
where empno=v_eno;
end;

76 © 2009 Wipro Ltd – Internal & Restricted

Invoking the Procedure

To call the procedure “retrieve” with 1 IN parameter and 2

OUT parameters
SQL:>variable name varchar2(15)
SQL:>variable job varchar2(15)
SQL:> execute retrieve(7788,:name,:job);

Use PRINT command to know the ouptut

SQL:>PRINT NAME
SQL:>PRINT JOB

77 © 2009 Wipro Ltd – Internal & Restricted

Invoking the Procedure
• To call the procedure “retrieve” with 1 IN parameter and 2 OUT
parameters from an anonymous block
Declare
v_name varchar2(15);
v_job varchar2(15);
Begin
retrieve(7788,v_name,v_job);
End;

78 © 2009 Wipro Ltd – Internal & Restricted

Removing the Procedure
• Syntax is
DROP PROCEDURE procedure_name;

• Example
drop procedure retrieve;

79 © 2009 Wipro Ltd – Internal & Restricted

Data Dictionary Views
• DESC procedure_name
• DESC USER_SOURCE
• DESC USER_OBJECTS
• DESC USER_DEPENDENCIES

80 © 2009 Wipro Ltd – Internal & Restricted

 6.2. Stored Functions

81 © 2009 Wipro Ltd – Internal & Restricted

Stored Function
• Named and stored PL/SQL Block
• Should return at least one value
• Used like Built-in functions
– It can be single row function and not group function
• Invoked as part of expression
– No separate statement is needed to invoke functions

82 © 2009 Wipro Ltd – Internal & Restricted

Creating the Functions
Syntax is
CREATE [OR REPLACE] FUNCTION function_name
[(Formal parameter1 [MODE] Data_type,
Formal parameter2 [MODE] Data_type,
….)]
RETURN Data_type
IS|AS
PL/SQL Block;

83 © 2009 Wipro Ltd – Internal & Restricted

Return Statement
• RETURN data_type
– Only type has to be specified and size specification is not allowed
– Can be Scalar data type and composite data types.
– Can return only one value
– Should have at least one return statement in the PL/SQL block
• RETURN variable;

84 © 2009 Wipro Ltd – Internal & Restricted

Example
Create or replace function ret_maxsal (dno IN number)
Return number
IS
v_sal number;
Begin
Select max(sal)
Into v_sal
From emp
Where deptno = dno;
Return v_sal;
End;

85 © 2009 Wipro Ltd – Internal & Restricted

Invoking the Function
• SQL:>execute :salary:=ret_maxsal(10)

• Insert into emp (empno, sal, deptno)

values(1234,ret_maxsal(10),50);

• select ename
from emp
where sal>ret_maxsal(20);

86 © 2009 Wipro Ltd – Internal & Restricted

Removing the Function
• DROP FUNCTION function_name

Example
– DROP FUNCTION ret_maxsal
Will remove the stored function ret_maxsal.
Also remove the related data dictionary entries and contents.

87 © 2009 Wipro Ltd – Internal & Restricted

Restrictions on using functions
• Single row function and not group function
• Should not have OUT and IN OUT parameters
• In case the functions are invoked from SQL
– Return data type should be a primitive data type like NUMBER,
CHAR, DATE
– Return data type should not be composite data type which is supported
only in PL/SQL

88 © 2009 Wipro Ltd – Internal & Restricted

Procedures Vs Functions
PROCEDURE FUNCTION
Invoked as separate statement Can be invoked as part of
expression

Return zero or more values Return one value using Return

using OUT parameter statement

89 © 2009 Wipro Ltd – Internal & Restricted

Data Dictionary Views
• DESC function_name
• DESC USER_SOURCE
• DESC USER_OBJECTS
• DESC USER_DEPENDENCIES
• DESC USER_ERRORS

90 © 2009 Wipro Ltd – Internal & Restricted

 7. Cursors and Data types
(as in 3GL)

91 © 2009 Wipro Ltd – Internal & Restricted

%TYPE

DATA DICTIONARY
Declare
DESC EMP
V_SAL EMP.SAL%TYPE;
…..
SAL NUMBER(7,2)
….

NUMBER(7,2)

92 © 2009 Wipro Ltd – Internal & Restricted

%Type
• Declare
v_sal emp.sal%type;
…
– Implicit data type (structure) specification.
– Refer the data dictionary for EMP table.
– Fetch the current data type of SAL column.
– Map it for the variable v_sal.

93 © 2009 Wipro Ltd – Internal & Restricted

%TYPE - Example
Example 1
DECLARE
var1 NUMBER(7,2);
var2 var1 %TYPE;
Example 2
DECLARE
sal emp.sal%TYPE;
eno NUMBER(4);

94 © 2009 Wipro Ltd – Internal & Restricted

Example - 1
• Retrieve all the columns of particular employee
Declare
V_eno number(4);
V_name varchar2(15);
V_job varchar2(10);
V_mgr number(4);
V_hiredate date;
V_sal number(7,2);
V_comm number(7,2);
V_dno number(2);
Begin
Select *
Into v_eno,v_ename,v_job,v_mgr,v_hiredate,v_sal,
v_comm,v_dno
……

95 © 2009 Wipro Ltd – Internal & Restricted

%ROWTYPE
Declare
myemp emp%rowtype;
Begin
select *
into myemp
….
if (myemp.comm is not null) then …
• Structure of emp table is referred and copied to variable myemp.
• To access the variables use rowtype variable and name of the column.
i.e.,myemp.comm

96 © 2009 Wipro Ltd – Internal & Restricted

Record Type
• User defined, customized data type
• Like structures in C
• Collection of various members
• Create the Data type and declare record variable which refers the user
defined record type

97 © 2009 Wipro Ltd – Internal & Restricted

Record Type - Example

DECLARE
TYPE my_data is RECORD
(ename emp.ename%TYPE,
dname dept.dname%TYPE);
my_emp my_data;
BEGIN
SELECT ename,dname
INTO my_emp
FROM emp,dept
WHERE emp.deptno=dept.deptno AND empno=&ENO;
dbms_output.put_line(my_emp.ename ||my_emp.dname);
END;

98 © 2009 Wipro Ltd – Internal & Restricted

Record Type - Example

TYPE WBP IS RECORD

(LTA NUMBER(7,2),
HRA NUMBER(7,2),
fon NUMBER(7,2));
my_allowance WBP;
-- declare variable of record type WBP
BEGIN
my_allowance.LTA:=3000;
my_allowance.fon:=2000;
…

99 © 2009 Wipro Ltd – Internal & Restricted

PL/SQL Table
• Collection of records and not rows
• Store records like arrays
• Single dimensional
• No limits for upper bound
– Can store as many records as needed.

100 © 2009 Wipro Ltd – Internal & Restricted

PL/SQL Tables-Example

101 © 2009 Wipro Ltd – Internal & Restricted

Built-in-Functions
• Built in functions used to access PL/SQL tables are
– COUNT
– EXISTS(N)
– FIRST , LAST
– NEXT , PRIOR
– DELETE(N)

102 © 2009 Wipro Ltd – Internal & Restricted

Advantages-Composite Data types
• When the structure of base table changes no need to modify the PL/SQL
block
• Refers the current structure and variables are declared accordingly
• Provides Logical Independence
• %ROWTYPE helps user to avoid individual member specification
• Helpful when row is retrieved from SQL

103 © 2009 Wipro Ltd – Internal & Restricted

 8. Cursors

104 © 2009 Wipro Ltd – Internal & Restricted

When and Why Cursors
• Fetching more than one record is not possible with simple variables
• Can be done through cursors
• Cursor is the memory area with pointers
• Temporary memory area used to store data getting referred during
– PL/SQL fetch operation
– And DML statements

105 © 2009 Wipro Ltd – Internal & Restricted

Implicit & Explicit Cursors
• Implicit Cursors
– Handled and controlled by Oracle
– System defined cursors
– Memory area used temporarily for fetching and DML operation
• Explicit Cursors
– Defined ,Named and used by programmer
– Controlled by user

106 © 2009 Wipro Ltd – Internal & Restricted

Cursor Attributes-Implicit
• SQL%ROWCOUNT
– Number of rows affected by the most recent SQL statement
• SQL%FOUND
– TRUE if the most recent SQL found any row
– Set to TRUE if SQL%ROWCOUNT is <>0
• SQL%NOTFOUND
– TRUE if the most recent SQL didn’t find any row
– Set to true if SQL%ROWCOUNT is =0 or SQL%Found is false
• SQL%ISOPEN
– Always FALSE because control will be transferred to the user after
closing implicit cursor

107 © 2009 Wipro Ltd – Internal & Restricted

Example
Declare
dno number(4);
Begin
dno:=&depno;
delete from emp where deptno=dno;
if (sql%found) then
dbms_output.put_line (sql%rowcount);
end if;
if (sql%notfound) then
dbms_output.put_line ('no employees working for
deptno ‘||dno);
end if;
end;

108 © 2009 Wipro Ltd – Internal & Restricted

Stages in Cursors

START

No into in select
DECLARE CURSOR
Declare
Cursor my_cur is select ename,sal from emp;

OPEN CURSOR
open my_cur; Brings the active set into
the cursor area

NP

109 © 2009 Wipro Ltd – Internal & Restricted

Stages in Cursors
NP

FETCH CURSOR INTO

VARIABLE
Fetch my_cur into cur_rec;

IS
NO
EMPT
Y

YES
CLOSE CURSOR
close my_cur;

STOP

110 © 2009 Wipro Ltd – Internal & Restricted

Cursor to Record type
• Fetch cursor into variable
– Fetches the current record from active set to the PL/SQL variable.
– Data type of the variable depends on structure of the cursor
– Variable can be declared using %rowtype of cursor or individual
component specification is also allowed

111 © 2009 Wipro Ltd – Internal & Restricted

Cursor to Record type
Example
Declare
Cursor my_cur is
select ename, sal from emp;
cur_rec my_cur%rowtype;
Begin
….
Fetch my_cur into cur_rec;
Example
Declare
….
v_ename emp.ename%type;
v_sal emp.sal%type;
Begin
….
Fetch my_cur into v_ename,v_sal;

112 © 2009 Wipro Ltd – Internal & Restricted

Explicit Cursor Attributes
• %ROWCOUNT
– Number of rows fetched so far by the cursor
• %FOUND
– TRUE if the most recent fetch operation found any row
• %NOTFOUND
– TRUE if the most recent fetch didn’t find any new record in cursor’s
active set
– Set to TRUE if %Found is FALSE
• %ISOPEN
– Shows the status of the cursor
– TRUE if it is open else it is FALSE.

113 © 2009 Wipro Ltd – Internal & Restricted

Cursor Attributes-Explicit

114 © 2009 Wipro Ltd – Internal & Restricted

Cursor with FOR loop
• For record_variable IN cursor_name
LOOP
….
END LOOP;
• No need
– To declare the record_variable
– To Open
– To Fetch into record_variable
– To Check for new data found or not
– To Close

115 © 2009 Wipro Ltd – Internal & Restricted

Example

declare
cursor dep_cur is select * from emp where deptno=10 order by sal;
begin
dbms_output.put_line('-------------------------------');
dbms_output.put_line ('Sl.No. Salary details');
dbms_output.put_line('-------------------------------');
FOR dep_rec IN dep_cur
LOOP
dbms_output.put_line (dep_cur%rowcount||' '||dep_rec.ename
||'''s salary is '||dep_rec.sal);

END LOOP;
dbms_output.put_line('-------------------------------');
end;

117 © 2009 Wipro Ltd – Internal & Restricted

Sub queries in FOR loop
• FOR record_variable in (Sub query)
LOOP
…
END LOOP;
• No need to
– declare the cursor and record variable
– Implicit open, fetch, check for empty, close

118 © 2009 Wipro Ltd – Internal & Restricted

Example
•Begin
Implicit declaration of cursor is done in the for loop
FOR dep_rec IN (select * from emp where deptno=10 order by sal)
• No Access to the Explicit cursor attributes
LOOP
…..
END LOOP;
End;

119 © 2009 Wipro Ltd – Internal & Restricted

Cursor with Parameters
Declare
cursor dep_cur (v_did number) is
select count(*) cnt from emp where deptno=v_did ;
dep_rec dep_cur%rowtype;
Begin
open dep_cur(10); --Retrieve dept 10 employees
fetch dep_cur into dep_rec;
dbms_output.put_line (dep_rec. cnt);
close dep_cur;
open dep_cur(20); --Retrieve dept 20 employees
fetch dep_cur into dep_rec;
dbms_output.put_line (dep_rec. cnt);
close dep_cur;
end;

120 © 2009 Wipro Ltd – Internal & Restricted

Locking
• Locks the data retrieved for updating
• No separate request is needed during update operation
• Use “FOR UPDATE” clause in SELECT statement
• To refer recently fetched record use “WHERE CURRENT OF cursor
name”

121 © 2009 Wipro Ltd – Internal & Restricted

FOR UPDATE
• Syntax is
Select …..
FOR UPDATE [OF COLUMN] [NOWAIT]
• Locks the ROWS being read for updating
• Other user’s request for the rows locked will be in the wait state
• Locks can be released using rollback/commit
• Example
– SELECT * FROM EMP WHERE DEPTNO=10 FOR UPDATE;

122 © 2009 Wipro Ltd – Internal & Restricted

WHERE CURRENT OF
• Syntax
WHERE CURRENT OF cursor name;
• Used in Update & Delete commands
• Refers the rows recently fetched by the cursor.
• No additional specifier is needed to refer the locked rows.
• Example
– DELETE emp WHERE CURRENT OF my_cur;

123 © 2009 Wipro Ltd – Internal & Restricted

Example
Declare
Cursor my_cur IS
Select * from emp where deptno=10
FOR UPDATE;
current_rec my_cur%rowtype;
Begin
….
FETCH my_cur INTO current_rec;
update emp set comm=1000
WHERE CURRENT OF my_cur;
……

124 © 2009 Wipro Ltd – Internal & Restricted

 9. Packages

125 © 2009 Wipro Ltd – Internal & Restricted

Introduction to Packages
• Groups related components together and stored as single object - Package
• Components includes
– Variables
– Sub Programs –Packaged Subprograms
 Functions

 Procedures

– Cursors
– And all PL/SQL allowed Constructs

126 © 2009 Wipro Ltd – Internal & Restricted

Advantages of Packages
• Modularity
– Related modules are grouped and kept as one collection
• Interface
– Provides single and simple interface to access its components
• Sharable object
– Can be shared by many users at a time
• Minimal disk I/O
– Loads the code into the memory when it is referred for the first time

127 © 2009 Wipro Ltd – Internal & Restricted

Advantages
• Extended Functionality
– Additional facility provided along with stored sub programs
• Security
– Decide upon the list of operations allowed over the database objects
• Global Access
– Declared variable are accessible from anywhere
– Data remains throughout the session
• Overloading
– Is allowed with packaged sub programs and not with stored sub
programs

128 © 2009 Wipro Ltd – Internal & Restricted

Packages -Restrictions
• Nesting is not allowed
– Creating package within another package is not allowed
• Parameter
– Parameter can’t be passed in packages
• Calling the Package directly is not possible
– Package can be referred by invoking any of the components and not
the package directly.

129 © 2009 Wipro Ltd – Internal & Restricted

Packages
• Components of the package is referred
– Using the package name as primary identifier
– Component name as second identifier
– Separated using a dot operator ‘.’
• Creation of Packages involves two steps
– Package Specification-Header
• Includes details about WHAT it consists of?
– Package Body
• Implements/defines the specification
• Can change the contents without disturbing the header

130 © 2009 Wipro Ltd – Internal & Restricted

Package Specification
• Syntax
CREATE [OR REPLACE] PACKAGE package_name
IS
Variable Declaration;
Sub Program header specification;
END [package_name];

131 © 2009 Wipro Ltd – Internal & Restricted

Package Specification
Syntax
CREATE [OR REPLACE] PACKAGE package_name
IS
Variable Declaration;
Sub Program header specification;
END [package_name];

Example
CREATE OR REPLACE PACKAGE bonus_pack
IS
v_bonus NUMBER:=0;
PROCEDURE retrieve_bonus(p_eno NUMBER);
END bonus_pack;

132 © 2009 Wipro Ltd – Internal & Restricted

Package Body
• Local variables and sub programs are declared and used inside the body
• They are local to the Package body -Private

CREATE [OR REPLACE] PACKAGE BODY package_name

IS
local variable declaration;
local sub program declaration;
packaged sub program definition;
END [package_name];

133 © 2009 Wipro Ltd – Internal & Restricted

Package Body
1 CREATE OR REPLACE PACKAGE body bonus_pack
2 IS
3 PROCEDURE retrieve_bonus (p_eno NUMBER)
4 IS
5 v_sal NUMBER;
6 BEGIN
7 SELECT sal, comm
8 INTO v_sal, v_bonus
9 FROM emp
10 WHERE empno = p_eno;
11 v_bonus:=v_sal*0.2+v_bonus;
12 END retrieve_bonus;
13* END bonus_pack;
SQL> /
Package body created.

134 © 2009 Wipro Ltd – Internal & Restricted

Referencing Package objects

SQL:>EXECUTE bonus_pack.v_bonus:=1000;
SQL:>EXECUTE bonus_pack.retrieve_bonus(7788);

BEGIN
bonus_pack.v_bonus:=200;
dbms_output.put_line(bonus_pack.v_bonus);
….
bonus_pack.retrieve_bonus(7369);
END;

135 © 2009 Wipro Ltd – Internal & Restricted

Public and Private Members
• Declarations in package specification are public members
– Allowed for public reference
• In package body all the declarations made like local variable, subprograms
are private
– Accessible only within the Body and not allowed for public
– Private member

136 © 2009 Wipro Ltd – Internal & Restricted

Private Member -Example
1 CREATE OR REPLACE PACKAGE body bonus_pack
2 IS
3 v_sal NUMBER;
4 FUNCTION retrieve_sal(p_eno NUMBER) Private function
5 RETURN NUMBER
6 IS
7 BEGIN
8 SELECT sal
9 INTO v_sal
10 FROM emp
11 WHERE empno = p_eno;
12 RETURN v_sal;
13 END;
14 PROCEDURE RETRIEVE_BONUS(p_eno NUMBER)
15 IS
16 BEGIN
17 v_bonus:=retrieve_sal(p_eno)*0.2;
18 END retrieve_bonus;
19* END bonus_pack;
SQL> /

Package body created.

137 © 2009 Wipro Ltd – Internal & Restricted

Session state -Package variables
• Used as global variable
– Accessible from anywhere
• Initialization is done once per session
– Data are maintained throughout the session
• Scope of the value remains for the current session.
• Value accessed by one user can’t be shared/disturbed by other user

138 © 2009 Wipro Ltd – Internal & Restricted

Removing the Packages
• To drop the package body
– DROP PACKAGE BODY package_name
• To drop the package header
– DROP PACKAGE package_name
• After removing the package body for the existing header new body can be
created
• But after deleting header the body will be no longer valid

139 © 2009 Wipro Ltd – Internal & Restricted

Overloading
• Overloading is the concept of using the same name
• Overloading stored sub programs are not allowed whereas possible with
packaged sub programs

FUNCTION compute_salary(p_eno NUMBER)

RETURN NUMBER;
FUNCTION compute_salary(p_ename VARCHAR2,p_doj DATE)
RETURN NUMBER;
PROCEDURE print_data(p-job VARCHAR2)
PROCEDURE print_data(p_ename VARCHAR2,p-job VARCHAR2)

140 © 2009 Wipro Ltd – Internal & Restricted

One time only Procedures

Example
CREATE OR REPLACE PACKAGE dept_pack
IS
v_dept_cnt NUMBER:=0;
PROCEDURE print_details;
END dept_pack;

141 © 2009 Wipro Ltd – Internal & Restricted

One time only Procedures
CREATE OR REPLACE PACKAGE BODY dept_pack
IS
PROCEDURE print_details
IS
BEGIN
DBMS_OUTPUT.PUT_LINE (‘No. of Records '|| v_dept_cnt);
END;
BEGIN Gets executed once when
SELECT COUNT(*) loaded for the first time
INTO v_dept_cnt
FROM DEPT;
END;

142 © 2009 Wipro Ltd – Internal & Restricted

Example
SQL> EXECUTE dept_pack.print_details
No. of Records4

SQL> INSERT INTO DEPT VALUES(50,'Operations','NY');

SQL> SELECT COUNT(*) FROM DEPT;

COUNT(*)
----------
5

SQL> EXECUTE dept_pack.print_details

No. of Records4

143 © 2009 Wipro Ltd – Internal & Restricted

Compiler directives on functions
• Pragma is the compiler directive
• Helps to insist purity level for the packaged functions
• PRAGMA RESTRICT_REFERENCES (function_name, WNDS ,
WNPS , RNDS, RNPS)
– WNDS,WNPS to restrict function updating database and package
variables
– RNDS,RNPS to restrict function reading database and package
variables

144 © 2009 Wipro Ltd – Internal & Restricted

Built in packages
• DBMS_OUTPUT
• UTL_FILE
• DBMS_ALERT
• DBMS_LOCK
• DBMS_JOB
• DBMS_SESSION

145 © 2009 Wipro Ltd – Internal & Restricted

 10. Exceptions

146 © 2009 Wipro Ltd – Internal & Restricted

Introduction - Exception
• Any abnormal condition to the normal execution of PL/SQL Block is an
Exception
• Way of handling Run time errors

147 © 2009 Wipro Ltd – Internal & Restricted

Exception Block
• Syntax
….
EXCEPTION
WHEN name_of_exception1 THEN
handler – PL/SQL statements
WHEN name_of_exception2 THEN
handler
END;

148 © 2009 Wipro Ltd – Internal & Restricted

Exception
• Any PL/SQL block can have its own exception block
• Exception block contains
– Name of the exception thrown
– HANDLER explains the actions to be performed when error occur
• Exception is not going to solve the current error condition.

149 © 2009 Wipro Ltd – Internal & Restricted

Need for Exception
• When Error occurs
– Abruptly stop executing the current block
– ROLLBACK
• If Exception is Handled
– Stops the current process
– Saves the process done so far
– Transfer the control to exception handler
– Smooth Transition from error condition to end of program

150 © 2009 Wipro Ltd – Internal & Restricted

Demonstrate
• Observe and execute the following code
DECLARE
v_empno EMP.EMPNO%TYPE;
BEGIN
INSERT INTO DEPT VALUES(55,’Operations’,’NY’);
SELECT empno
INTO v_empno
FROM emp
WHERE ename=‘ABC’;
DBMS_OUTPUT.PUT_LINE (v_empno);
END;

• Will the new dept 55 get inserted?

151 © 2009 Wipro Ltd – Internal & Restricted

Pre-defined Exceptions
• PL/SQL defined exceptions
• Sensed and mapped to the corresponding exception handler automatically
• User has to DEFINE it - handler
• No need to DECLARE and INVOKE

• No_data_found • Invalid_number • Cursor_already_open

• Too_many_row • Zero_divide • Dup_val_on_index
s

152 © 2009 Wipro Ltd – Internal & Restricted

Example
DECLARE
v_empno EMP.EMPNO%TYPE; Will this get inserted in
BEGIN database?
INSERT INTO DEPT VALUES(55,’Operations’,’NY’);
SELECT empno
INTO v_empno
FROM emp
Will this get printed?
WHERE ename=‘ABC’;
DBMS_OUTPUT.PUT_LINE (v_empno);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE (‘no employee found);
END;

153 © 2009 Wipro Ltd – Internal & Restricted

When Others
• Unhandled exception can be trapped in the OTHERS category
Example
…..
EXCEPTION
…..
WHEN OTHERS THEN
DBMS_OUTPUT,PUT_LINE (SQLCODE);
DBMS_OUTPUT,PUT_LINE (SQLERRM);
END;

• When PL/SQL senses exception, handler is searched. If specific handler is

not found OTHERS handler is executed
• Use SQLCODE & SQLERRM to print the error code and message

154 © 2009 Wipro Ltd – Internal & Restricted

User defined Exception
• Designed by user as error
• Kind of Logical error
– Manager getting commission > 500 is invalid
• User has to
– DECLARE
– DEFINE &
– INVOKE

155 © 2009 Wipro Ltd – Internal & Restricted

User defined Exception
• In Declaration Block - DECLARE
– Exception_name EXCEPTION;
• In Exception Block – DEFINE
– WHEN Exception_name THEN
Handler
• In Execution Block – INVOKE
– RAISE Exception_name

156 © 2009 Wipro Ltd – Internal & Restricted

Example
DECLARE
v_comm EMP.comm%TYPE;
v_job EMP.job%TYPE;
invalid_commisn EXCEPTION;
BEGIN
……
IF (v_job=‘MANAGER’ AND v_comm>500) THEN
RAISE invalid_commisn;
END IF;
…..
EXCEPTION
WHEN invalid_commisn THEN
….
END;

157 © 2009 Wipro Ltd – Internal & Restricted

Non-Predefined Exception
• Name is not defined by PL/SQL
• PL/SQL identifies such exceptions as errors with error code
• User has to assign name for it, map a exception name for the error.
• User need to
– DECLARE and DEFINE it
– Implicit INVOKING is done after user assigns name
– Use pragma EXEPTION_INIT to associate user defined exception
name with the error code

158 © 2009 Wipro Ltd – Internal & Restricted

Example
DECLARE
unique_constraint EXCEPTION;
PRAGMA EXCEPTION_INIT(unique_constraint,-0001);
BEGIN
…..
INSERT INTO DEPT VALUES(10,'Finance','CA');
…..
EXCEPTION
WHEN unique_constraint THEN
DBMS_OUTPUT.PUT_LINE ('caught exception');
……
END;

 PRAGMA EXCEPTION_INIT
– Associates name “unique constraint” with the error code
-0001
 Exception got declared, handled and not invoked
159 © 2009 Wipro Ltd – Internal & Restricted
RAISE APPLICATION ERROR
• Used to print user defined error message

….
RAISE_APPLICATION_ERROR(-20010,'Check Constraint Violation');
…

ERROR at line 1:
ORA-20010: Check Constraint Violation

160 © 2009 Wipro Ltd – Internal & Restricted

Propagation -Exception
DECLARE
v_empno EMP.EMPNO%TYPE;
BEGIN
INSERT INTO DEPT VALUES(55,’Operations’,’NY’);
SELECT empno
INTO v_empno
FROM emp
WHERE ename=‘ABC’;
DBMS_OUTPUT.PUT_LINE (v_empno);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE (‘no employee found);
END;

161 © 2009 Wipro Ltd – Internal & Restricted

Propagation-Nested Blocks
DECLARE
…
When no_data_found
BEGIN
Exception is raised
….
BEGIN --Inner block begins
…..
EXCEPTION --Exception handler for inner block
WHEN NO_DATA_FOUND THEN
…..
END --Inner block ends
…..
…..
EXCEPTION --Exception handler for outer block
…….
……
END;

162 © 2009 Wipro Ltd – Internal & Restricted

Propagation-Nested Blocks
DECLARE
…
When too_many_rows
BEGIN
Exception is raised
….
BEGIN --Inner block begins
…..
EXCEPTION --Exception handler for inner block
WHEN NO_DATA_FOUND THEN
…..
END --Inner block ends
…..
…..
EXCEPTION --Exception handler for outer block
…….
……
END;

163 © 2009 Wipro Ltd – Internal & Restricted

Propagation-Nested Blocks
DECLARE
… When no_data_found Exception
is raised in inner block and
BEGIN
handled in both blocks
….
BEGIN --Inner block begins
…..
EXCEPTION --Exception handler for inner block
WHEN NO_DATA_FOUND THEN
…..
END --Inner block ends
…..
…..
EXCEPTION --Exception handler for outer block
WHEN NO_DATA_FOUND THEN
……
END;

164 © 2009 Wipro Ltd – Internal & Restricted

RE-RAISE
DECLARE
… When no_data_found Exception
is raised in inner block and
BEGIN
handled in both blocks
….
BEGIN --Inner block begins
…..
EXCEPTION --Exception handler for inner block
WHEN NO_DATA_FOUND THEN
RAISE;
END --Inner block ends
…..
…..
EXCEPTION --Exception handler for outer block
WHEN NO_DATA_FOUND THEN
……
END;

165 © 2009 Wipro Ltd – Internal & Restricted

 11. Triggers

166 © 2009 Wipro Ltd – Internal & Restricted

What a trigger is?
• PL/SQL block consists of actions to be continued implicitly for the current
event
• Chain of action is to be performed whenever insertion happens - trigger is
the solution for it
• Gets fired automatically/implicitly.
• Treats user’s request and trigger action as one transaction

167 © 2009 Wipro Ltd – Internal & Restricted

Need for a Trigger
• Owner of the database objects can decide upon the chain of action to
happen
• Along with the specified request trigger will also get executed

168 © 2009 Wipro Ltd – Internal & Restricted

Components of Triggers
• Trigger timing
– When to fire trigger after the request or before the current request
• Triggering event
– For what event this trigger is to be fired
– Is it insert/update/delete
• Table name
– Name of the table for which trigger is

169 © 2009 Wipro Ltd – Internal & Restricted

Components of Triggers
• Trigger type
– Two categories of trigger exist –decides upon how trigger gets fired
– Statement level – once per request
– Row level – for each and every row got affected trigger is executed
• When clause
– Prepares selected rows for trigger action
– Allowed only for Row level trigger
• Trigger body
– Explains what to be done in the trigger end

170 © 2009 Wipro Ltd – Internal & Restricted

Database Triggers
CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE/AFTER
INSERT [OR DELETE OR
UPDATE [OF COLUMNS] ]
ON table_name
PL/SQL BLOCK;

171 © 2009 Wipro Ltd – Internal & Restricted

Database Triggers-Example
CREATE TRIGGER salhike_emp
AFTER
UPDATE OF SAL
ON EMP
BEGIN
INSERT INTO LOG_HIKE(HIKEDATE,USER_NAME)
VALUES(SYSDATE,USER);
END;

172 © 2009 Wipro Ltd – Internal & Restricted

Database Triggers-Example
• How many times the previous trigger get executed if all the 14 employees
are eligible for salary hike?
• Will this trigger help us to store details of the salary hike, like
– name of the person eligible for hike,
– old salary and
– the revised salary?

173 © 2009 Wipro Ltd – Internal & Restricted

Statement level & Row level Trigger
• Decides upon how trigger gets fired
– Statement level
– once per request
– Row level
– for each and every row got affected trigger body is executed

174 © 2009 Wipro Ltd – Internal & Restricted

Row level Triggers
CREATE [OR REPLACE] TRIGGER
BEFORE/AFTER
INSERT [OR UPDATE OR DELETE]
ON table_name
[REFERENCING OLD AS old | NEW AS new]
FOR EACH ROW
[WHEN condition]
PL/SQL Block;

175 © 2009 Wipro Ltd – Internal & Restricted

Row level Triggers
CREATE TRIGGER log_hike_employee
AFTER
UPDATE
ON EMP
FOR EACH ROW
BEGIN
INSERT INTO LOG_HIKE_EMP(EMPNO,PREVSAL,CURSAL)
VALUES(:OLD.EMPNO,:OLD.SAL,:NEW.SAL);
END;

176 © 2009 Wipro Ltd – Internal & Restricted

Row level Triggers
CREATE OR REPLACE TRIGGER TR1
AFTER
UPDATE
ON EMP
REFERENCING OLD AS PREVSAL NEW AS CURSAL
FOR EACH ROW
WHEN (PREVSAL.JOB='CLERK')
BEGIN
INSERT INTO HIK_EMP_HISTRY(EMPNO,PREVSAL,CURSAL)
VALUES(:PREVSAL.EMPNO,:PREVSAL.SAL,
:CURSAL.SAL);
END;

177 © 2009 Wipro Ltd – Internal & Restricted

Requirement - Demo
• Whenever salary hike is announced comm is revised by 10%.
• Will this trigger solve the problem?

create or replace trigger sal_hike_comm

before update of sal on emp
for each row
begin
update emp set comm=comm*1.1 where empno=:old.empno;
end;

178 © 2009 Wipro Ltd – Internal & Restricted

Mutation Errors in Triggers
• When the data referred in the user query is used in its trigger mutation
error occurs
• Example
In the trigger written for update event if we try to delete the rows locked by
update command mutation occurs

179 © 2009 Wipro Ltd – Internal & Restricted

Row level Triggers
CREATE TRIGGER sal_hike_rowlevel
BEFORE
Will this work for After trigger?
UPDATE
ON EMP
FOR EACH ROW
BEGIN
INSERT INTO LOG_HIKE_EMP(EMPNO,PREVSAL,CURSAL)
VALUES(:OLD.EMPNO,:OLD.SAL,:NEW.SAL);
:NEW.COMM:=500;
END;

180 © 2009 Wipro Ltd – Internal & Restricted

Instead of Triggers
• Triggers are written for Tables and not for views
• Instead of triggers are applicable only for views and not for tables
• Get fired for any DML operations requested over the view
– Instead of updating view, modification can be done somewhere else
– Can be defined in the trigger body

181 © 2009 Wipro Ltd – Internal & Restricted

Syntax
CREATE OR REPLACE TRIGGER trigger_name
INSTEAD OF event ON view_name
PL/SQL Block;
• Where event is the DML event which is requested towards this
view_name

182 © 2009 Wipro Ltd – Internal & Restricted

Instead of trigger
CREATE OR REPLACE TRIGGER TRG1
INSTEAD OF INSERT ON CLER_VIEW
BEGIN
INSERT INTO LOG(USER_NAME,DTE)
VALUES(USER,SYSDATE);
END;
/

183 © 2009 Wipro Ltd – Internal & Restricted

System Triggers
• Considered during the following event
– DDL
• CREATE, ALTER and DROP
– Database events
• server startup/shutdown
• user logon/logoff

184 © 2009 Wipro Ltd – Internal & Restricted

Syntax
CREATE OR REPLACE TRIGGER Trigger_name
[BEFORE |AFTER]
[ddl event list| database_event list]
[ON| DATABASE| [schema.][SCHEMA]
[WHEN CLUASE]
[trigger_body]

185 © 2009 Wipro Ltd – Internal & Restricted

Example
CREATE TRIGGER scott_login_trigg
AFTER
LOGON
ON SCOTT. schema
WHEN (to_char(SYSDATE,'dy') not in ('sun','sat'))
Begin
insert into log values(user,sysdate);
end;

186 © 2009 Wipro Ltd – Internal & Restricted

Example-DDL triggers
CREATE TRIGGER scott_ddl_trigg
AFTER
CREATE ON schema
Begin
insert into log values (user,sysdate);
end;

187 © 2009 Wipro Ltd – Internal & Restricted

Restrictions in Triggers
• TCL commands are not allowed
– Treats user’s request and trigger action as one transaction
– Part of the transactions can be neither committed nor rollback
• Avoid data/function Mutation

188 © 2009 Wipro Ltd – Internal & Restricted

Removing Triggers
• DROP TRIGGER trigger_name
• DROP TRIGGER emp_insert_trigg;
• ALTER TRIGGER trigger_name
ENABLE|DISABLE
• ALTER TABLE table_name ENABLE/DISABLE ALL TRIGGERS

189 © 2009 Wipro Ltd – Internal & Restricted

Data Dictionaries -Triggers
• DESC USER_OBJECTS
• DESC USER_SOURCE
• DESC USER_ERRORS
• DESC USER_TRIGGERS

190 © 2009 Wipro Ltd – Internal & Restricted

 12. Dependencies among PL/SQL
Constructs

191 © 2009 Wipro Ltd – Internal & Restricted

Dependant and Referenced Objects
• When a Procedure body refers a table, procedure is dependant on table
and table is referenced by procedure
– Procedure is dependant object –which depends on table
– Table is referenced object – getting referred by other objects
• If referenced object is modified dependant object is affected

192 © 2009 Wipro Ltd – Internal & Restricted

Dependencies
• Assume
– stored function FUN1 invokes PROC1.
– PROC1 is getting changed NOW
– Makes FUN1 outdated and change its status to INVALID
– ALTER FUNCTION FUN1 COMPILE
• Similarly
– ALTER FUNCTION function_name COMPILE
– ALTER PROCEDURE procedure_name COMPILE
– ALTER PACKAGE package_name COMPILE PACKAGE
– ALTER PACKAGE package_name COMPILE BODY
– ALTER TRIGGER trigger_name [ENABLE|DISABLE|
COMPILE[DEBUG]

193 © 2009 Wipro Ltd – Internal & Restricted

Dependencies - package
• Package body is dependant on header
• Changes in body doesn’t affect specification
• But changes in header affects the body needs to be reconstructed.

194 © 2009 Wipro Ltd – Internal & Restricted

Direct and Remote Dependencies
• Direct dependencies exist if dependant objects are in the same server
• In case of dependant objects scattered to different servers remote
dependencies exists

195 © 2009 Wipro Ltd – Internal & Restricted

Data Dictionary views
• DESC USER_OBJECTS
• DESC USER_SOURCE
• DESC USER_ERRORS
• DESC USER_TRIGGERS
• DESC USER_DEPENDENCIES

196 © 2009 Wipro Ltd – Internal & Restricted

 Thank You

© 2009 Wipro Ltd – Internal & Restricted