PLSQL

NOTES

Focus

Training

Services 1

PLSQL

NOTES

INDEX
VARIABLE
CONTROL . COMPOSITE EXPLICIT
EXCEPTIONS PROCEDURES
FUNCTIONS PACKAGES .

DECLARATION
STATEMENTS VARIABLES CURSORS
. .
. .

.
. . . .
. .
. .

.
. . .
. .
. .

.
. . .
. .
. .

.
. . .
. .
. .

.
. . .
. .
. .

.
. . .
. .
. .

.
. . .
. .
. .

.
. . .
. .
. .

05
18 26 31
43 52
55 58

.
. .
. .

. .
. .

. .
. .

. .
. .

ORACLE
DYNAMIC TRIGGERS

PACKAGES
SQL . . . . . .

.
. .

.
. .

.
. .

.
. .

.
. .

.
. .

.
. .

.
. .

.
. .

.
. .

67
75 77

PLSQL

NOTES

Focus

Training

Services 2

Server Connection

Focus

Training

Services 3

Important
Each student has his

Instructions
own unix login id to server.

Use ssh -l command to login to server. e.g. ssh -l nagnath 172.24.8.60 in above case nagnath is unix id. Default password is xxxxxx for all students. Change password using passwd command after first login. After login in server, set database name in ORACLE_SID variable. e.g. export ORACLE_SID=DB11G in above case DB11G is database name. Conncet to database using your oracle sql login name and password. e.g. sqlplus nagnath/nagnath In above case username is nagnath and password is also nagnath. For each student oracle login id and password will be his name. Connect to oracle writing your plsql programs. e.g.[nagnath@server1 ~]$ from where you are

cd

plsql/

[nagnath@server1 ~]$ sqlplus nagnath/nagnath

Focus

Training

Services 4

PLSQL ENGINE

PLSQL ENGINE

PL/SQL

PLSQL BLOCK
BLOCK

PROCEDURAL STATEMENT EXECUTION

SQL ENGINE SQL statement executor

Focus

Training

Services 5

CONTEXT SWITCH

SQL

DATABASE

PLSQL

The PL/SQL engine executes procedural statements and sends all SQL statements present in the code to the SQL engine. The SQL engine will parse and execute the query or DML statement and return the expected output back to the PL/SQL engine. This switch between the two engines is called context switching.

Focus

Training

Services 6

VARIABLE

DECLARATION

Focus

Training

Services 7

DATA DICTIONARY VIEWS

USER_OBJECTS USER_TABLE USER_VIEW USER_INDEX USER_SEQUENCE USER_SOURCE USER_CONSTRAINTS

DBA_TABLES

All tables in the entire database. Reserved for user accounts that have dba privileges

ALL_TABLES

All tables owned by a user plus all tables to which the user has been granted access. USER_TABLES All tables owned by a particular database user.

Focus

Training

Services 8

Types of variables CHAR [(maximum length)] VARCHAR2 (maximum length) LONG NUMBER [(precision , scale)] BINARY INTEGER PLS_INTEGER BOOLEAN

BINARY INTEGER:Base type for integer between 2,147,483,647 and 2,147,483,647

PLS_INTEGER

:-

Base type for signed integers between 2,147,483,647 and 2,147,483,647.PLS_INTEGER values require less storage and are faster than NUMBER and BINARY_INTEGER values

BOOLEAN

:-

Base type that stores one of three possible values used for logical calculations:TRUE,FALSE,NULL

Focus

Training

Services 9

This is world.
create or

the

first

program

in

plsql

to

print

hello

replace

procedure

sp11

as -- First Program of Plsql -- This Program Prints -- Hello World on the Screen begin dbms_output.put_line('Hello end; World');

To

Compile @file the

Stored

Procedures name.sql prompt you are on SQL Prompt

Type To

name/procedure output on on

see

sql

type

command in.

set serveroutput TO Execute exec

when

logged

Stored

Procedures

Type

procedure_name

--OUTPUT SQL> @sp11.sql Procedure created. No errors. SQL> exec sp11 Hello Word PL/SQL procedure successfully completed.

Focus

Training

Services 10

PLSQL NOTES

This program shows how to check errors in procedures.

create or replace procedure sp11_1 as --This Program shows how to Read Errors begin dbms_output.put_line('Hello World') --Semi colon missing at the end end; /

To You

See can

Errors write

Type

'show

errors' at

on

SQL end of

prompt procedure.

'show errors'

the

OUTPUT
SQL> @sp11_1.sql Warning: Procedure created with compilation errors. SQL> show errors Errors for PROCEDURE SP11_1: LINE/COL ERROR ----------------------------------------------------------------7/1 PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( % ;
The symbol ";" was substituted for "END" to continue.

Focus

Training

Services 11

Variable declaration variables.

create or replace

and

assigning

value

to

procedure

sp12

--This Program and constants. as

Shows How to declare variables, assign values

v_myname CHAR(50); v_myage v_mycity v_salary begin

NUMBER(2) NOT NULL := 21; VARCHAR2(13) := Pune; CONSTANT NUMBER := 1400;

v_myname := Nagnath; dbms_output.put_line('My dbms_output.put_line('My end; / show Name Age is is '||v_myname); '||v_myage);

errors

OUTPUT:
SQL> @sp12.sql Procedure created. No errors. SQL> exec sp12 My Name is Nagnath My Age is 21 PL/SQL procedure successfully

completed

Focus

Training

Services 12

Passing value to variables.

create or replace procedure sp13(l_name varchar2,l_age number default 100) --This Program shows how to --Pass Values to procedures --Accept values in variable --without specifying its --length as begin
dbms_output.put_line('Name Entered By User: '||l_name);

dbms_output.put_line('Age

Entered

By

User:

'||l_age);

end; / show

errors

OUTPUT:
SQL> @sp13.sql Procedure created. No errors. SQL> exec sp13('nagnath',21); Name Entered By User: nagnath Age Entered By User: 21 PL/SQL procedure successfully completed.

Focus

Training

Services 13

Selecting

values

from

database

tables.

create or replace procedure sp13_1 --This Programs Shows how to --Execute SQL quries from plsql as l_employee_id l_employee_name begin select into from where last_name l_employee_name Employees employee_id = l_employee_id; Number varchar2(30); := 100;

--into clause copies selected column's value --into given variables dbms_output.put_line('Employees Name is: '|| l_employee_name); end; / show errors

OUTPUT SQL> @sp13_1.sql Procedure created. No errors. SQL> exec sp13_1 Employees Name is: King PL/SQL procedure successfully

completed.

Focus

Training

Services 14

Use

of

%type

variable.

create or replace procedure sp15(l_employee_id number) --This Program shows --how to use variables with same data type from tables --Objective --Accept EmployeeID From User --Print Name of That Employee as l_last_name begin select last_name into l_last_name from employees where employee_id = employees.last_name%type;

l_employee_id;
of Employee is: '||l_last_name);

dbms_output.put_line('Name

end; / show

errors

SQL> @sp15 Procedure created. No errors. SQL> set serveroutput on SQL> exec sp15(200); Name of Employee is: Whalen PL/SQL procedure successfully

completed.

Focus

Training

Services 15

Use

of

%type

variable

create or replace procedure sp14 --This Program shows --how to declare Variables With %Type Attribute --Means Same Data Type as in Table as l_last_name employees.last_name%type; --In this case data type of --l_last_name is same as
--data type of column last_name from employees

begin select last_name into l_last_name from employees where employee_id

101;

dbms_output.put_line(l_last_name); end; / show

errors

OUTPUT: SQL> @sp14.sql Procedure created. No errors. SQL> exec sp14 Kochhar PL/SQL procedure successfully

completed.

Focus

Training

Services 16

PLSQL

NOTES

Composite

Variable:Records

create or replace procedure sp31 --This program shows how to --create composite data types --First Composite data type is record as TYPE emp_sal_record is RECORD (last_name varchar2(20), salary number(10)); emp_sal emp_sal_record; --emp_sal_record can store --last_name and salary of employee --in single variable begin select last_name,salary into emp_sal from employees where employee_id = 100; dbms_output.put_line('Employee Name: '||emp_sal.last_name); dbms_output.put_line('Employees Salary: '||emp_sal.salary); end; / show

errors

Composite Data type Records stores more than one data type under single record.

OUTPUT: SQL> @sp31.sql Procedure created. No errors. SQL> set serveroutput on SQL> exec sp31 Employee Name: King Employees Salary: 24000 PL/SQL procedure successfully

completed.

Focus

Training

Services 17

PLSQL

NOTES

Composite

Data

type:%rowtype

create or replace procedure sp32 --This program shows how to --create composite data types --Second composite data type is %rowtype as emp_record employees%rowtype; --emp_record stores --all values of all columns --from employees table begin select * into emp_record from employees where employee_id = 100; dbms_output.put_line('Name: '||emp_record.last_name);

dbms_output.put_line('Department Id: '|| emp_record.department_id); end; / show

errors

%rowtype variable is used to store all column data types in single variable.

OUTPUT: SQL> @sp32.sql Procedure created. No errors. SQL> set serveroutput on SQL> exec sp32 Name: King Department Id: 90 PL/SQL procedure successfully

completed.

Focus

Training

Services 18

Scope

of

variables

in

procedure.

create or replace procedure sp16 --This Program shows --Scope Of Variable in Stored Procedure as l_no number := 600; l_msg varchar2(20) := 'Global Variable'; Begin <<Inner_Block1>> declare l_no Number := 1; l_msg varchar2(20) := 'Local variable'; begin l_no := l_no +1; dbms_output.put_line('In Inner Block1'); dbms_output.put_line(l_no); dbms_output.put_line(l_msg); end; <<Inner_Block2>> Declare l_no number :=100; Begin dbms_output.put_line('In Inner Block2'); dbms_output.put_line(l_no); dbms_output.put_line(sp16.l_msg); end; dbms_output.put_line('In Main'); dbms_output.put_line(l_no); dbms_output.put_line(l_msg); end; / show errors

Variables declared in main procedure (Outermost variable) are global variables.

Global variables can be accessable in all inner procedures.

Inner procedures can define their own variables with same name as global variable and access. But it is not good programing practice.

Focus

Training

Services 19

OUTPUT SQL> @sp16.sql Procedure created. No errors. SQL> exec sp16 In Inner Block2 2 Local Variable

In Inner Block2 100 Global Variable In Inner Main 600 Global

PL/SQL

Variable
procedure successfully completed

Focus

Training

Services 20

DML

Statements

in

Procedure

create or replace procedure sp17 --This Programs shows --Executing DML statement in Plsql as begin --Inserting Data From Plsql Procedure insert into test(test_id,test_name) values(1,'sql'); insert into test(test_id,test_name) values(2,'plsql'); --Updating Data From Plsql Procedure update test set test_id = 20 where test_id = 2; --Deleting Data From Plsql Procedure delete from test where test_id = 20; commit; end; / show

errors

--OUTPUT create table test(test_id number,test_name varchar2(10)); Table created. SQL> @sp17.sql Procedure created. No errors. SQL> exec sp17 PL/SQL procedure successfully completed. SQL> select * from test; TEST_ID TEST_NAME ---------- ---------1 sql

Focus

Training

Services 21

CONTROL

STATEMENTS

Control

Statement

If

-Else

Condition

create or replace procedure sp21(p_employee_id in number) as This procedure will give a salary raise -- to an employee. Rules for the raise are as follows
-1. 20% for Employees working with us for at least 12 years

----

2.
3.

and whose salary is less than Rs. 6000/15% for Employees whose salary less than
10% for employees working with us for at

Rs.
12

6000/years

least

l_hire_date employees.hire_date%type; l_salary employees.salary%type; l_years_of_service number := 0; l_new_salary number := 0; begin select hire_date, salary into l_hire_date, l_salary from employees where employee_id = p_employee_id; l_years_of_service := months_between(sysdate, l_hire_date)/12;

if

l_salary

<

6000

and

l_years_of_service

>

12

then

l_new_salary := l_salary * 1.2; dbms_output.put_line('Giving 20% raise'); elsif l_salary < 6000 then

l_new_salary := l_salary * 1.15; dbms_output.put_line('Giving 15% raise'); elsif l_years_of_service > 12 then

l_new_salary := l_salary * 1.1; dbms_output.put_line('Giving 10% raise'); else

Focus

Training

Services 23

l_new_salary := l_salary; dbms_output.put_line('No salary raise '); end if; update employees set salary = l_new_salary where employee_id = p_employee_id; commit; end; / show errors

IF

condition statements;

THEN

[ELSIF

condition

THEN

statements;] [ELSE statements;] END IF;

Focus

Training

Services 24

OUTPUT SQL> @sp21.sql Procedure created. No errors. SQL> select salary from employees where SALARY ---------17000 SQL> exec sp21(101); Giving 10% raise PL/SQL procedure successfully completed. SQL> select salary from employees where SALARY ---------18700

employee_id=101;

employee_id=101;

Focus

Training

Services

25

PLSQL

NOTES

Control

Statement

Case

create or replace procedure sp22(p_grade in varchar2) as -- Case Statement ------------------CASE selector -WHEN expression1 THEN result1 -WHEN expression2 THEN result2 -... -WHEN expressionN THEN resultN -- [ELSE resultN+1;] --END; --A CASE expression selects a result and returns l_appraisal begin l_appraisal varchar2(100);

it

:=

CASE

p_grade 'A' 'B' 'C' 'No THEN THEN THEN such 'Excellent' 'Very Good' 'Good' grade'

WHEN WHEN WHEN ELSE END; DBMS_OUTPUT.PUT_LINE ' end; / show

('Grade: '|| p_grade || Appraisal ' || l_appraisal);

errors

OUTPUT SQL> @sp22.sql Procedure created. No errors. SQL> exec sp22('C'); Grade: C Appraisal Good PL/SQL procedure successfully

completed.

Focus

Training

Services

26

PLSQL

NOTES

Control

Statements

Basic

Loop
number)

create or replace procedure sp23 ( p_loop_counter in as -- Basic Loop construct -------------------------LOOP -- statement1; -... -- EXIT [WHEN condition ]; --END LOOP; --Use the basic loop when the statements inside the -- loop must execute at least once. i number; Begin i := 1; loop dbms_output.put_line(to_char(i) ); exit when i >= p_loop_counter; i := i + 1; end loop; end; / show errors Basic loop perform can use repetative exit actions. to terminate

Programer

condition

the

loop.

OUTPUT SQL> @sp23.sql Procedure created. No errors. SQL> exec sp23(5); 1 2 3 4 5

PL/SQL

procedure

successfully

completed.

Focus

Training

Services

27

PLSQL

NOTES

Control

Statement

While

Loop

create or replace procedure sp24 ( p_loop_counter in number) as -- While Loop construct -------------------------WHILE condition LOOP -- statement1; -- statement2; -... --END LOOP; -- Use the WHILE loop if the condition has to be -- evaluated at the start of each iteration. i number; begin i := 1; while i<= p_loop_counter loop dbms_output.put_line(to_char(i) ); i := i + 1; end loop; end; / show errors While loop performs repetative actions until controling condition is no longer True. The condition is checked at start of each transaction. OUTPUT SQL> @sp24.sql Procedure created. No errors. SQL> exec sp24(5); 1 2 3 4 5

PL/SQL

procedure

successfully

completed.

Focus

Training

Services

28

PLSQL

NOTES

Control

Statement

For

Loop

create or replace procedure sp25( p_loop_counter in number) as -- For Loop construct -------------------------FOR counter IN [REVERSE] -lower_bound..upper_bound LOOP -... --END LOOP; -- Use a FOR loop if the number of iterations is known. i number; begin -- Naming a loop is optional <<my_for_loop>> for i in 1..p_loop_counter loop sp5(to_char(i) , 2); end loop my_for_loop; dbms_output.put_line('------------------------'); -- now the reverse for loop for i in reverse 1..p_loop_counter loop dbms_output.put_line(to_char(i) ); end loop; end; / show errors OUTPUT SQL> @sp25.sql Procedure created. No errors. SQL> exec sp25(3); 1 2 3

-----------------------3 2 1 PL/SQL procedure successfully

Focus Training Services
29

completed.

PLSQL

NOTES

Infinite
create or replace procedure sp54 as -- How to write an infinite loop begin

Loop

while (999 = 999) loop dbms_output.put_line('Hi'); end loop;

while ( true) loop dbms_output.put_line('Hi'); end loop; end; / show

errors programer will is bad did not mentioned exit condition,

As

Proram This

repeat

printing

infinitelly.

programming.

Focus

Training

Services

30

PLSQL

NOTES

EXPLICIT

CURSORS

Focus

Training

Services 31

PLSQL

NOTES

Explicit Cursors Attribute

%ISOPEN Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. If a cursor is open, cursor_name%ISOPEN returns TRUE; otherwise, it returns FALSE.

%NOTFOUND Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. Before the first fetch from an open cursor, cursor_name%NOTFOUND returns NULL. Thereafter, it returns FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row.

%ROWCOUNT Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. When a cursor is opened, %ROWCOUNT is zeroed. Before the first fetch, cursor_name%ROWCOUNT returns 0. Thereafter, it returns the number of rows fetched so far. The number is incremented if the latest fetch returned a row.

Focus

Training

Services

32

Explicit

Cursors

create or replace procedure sp61 --This Program shows --How to write explicit cursor --How to open cursor --How to fetch data from cursor --How to close cursor as cursor c1 is select last_name,salary from employees where department_id = 20; --Declaration of cursor l_emp_name employees.last_name%type; l_sal employees.salary%type; begin open c1; --Opening of a Cursor loop fetch c1 into l_emp_name,l_sal; exit when c1%notfound; --Fetching Data from Cursor dbms_output.put_line(l_emp_name||' '||to_char(l_sal)); end loop; close c1; --Closing of Cursor end; / show errors

For every SQL statement execution, certain area in memory is allocated.

Programer can give name to that area and as known as cursor.

Using

cursor,

fetched

rows

can

be

process

one

by

one.

Focus

Training

Services

32

PLSQL

NOTES

OUTPUT SQL> !vi sp61.sql SQL> @sp61.sql Procedure created. No errors. SQL> exec sp61 Hartstein 13000 Fay 6000
PL/SQL procedure successfully completed.

Focus

Training

Services

33

PLSQL

NOTES

Explicit

cursor

Fetching

Data

into

records

create or replace procedure sp62 --This Program shows --How to fetch data from cursor into records as cursor c1 is select * from employees where department_id=50; --Cursor Declaration rec_c1 c1%rowtype; --Record Declaration begin open c1; loop fetch c1 into rec_c1; exit when c1%notfound; --Fetching Data from cursor into record dbms_output.put_line('Name: '|| rec_c1.last_name); dbms_output.put_line('Salary: '|| rec_c1.salary); end loop; close c1; end; / show errors

OUTPUT SQL> @sp62.sql Procedure created. No errors. SQL> exec sp62 Name: Hartstein Salary: 13000 Name: Fay Salary: 6000 PL/SQL procedure successfully

completed.

Focus

Training

Services

34

PLSQL

NOTES

Explicit

Cursor

Use

of

For

Loop

create or replace procedure sp63 --This Program shows --Cursor For as cursor c1 is select * from employees where department_id = 20; begin for rec_c1 in c1 loop -implicit open dbms_output.put_line('Name: '||rec_c1.last_name); dbms_output.put_line('salary: '|| to_char(rec_c1.salary)); end end; / show loop;

errors

OUTPUT: SQL> @sp63.sql Procedure created. No errors. SQL> exec sp63 Name: Hartstein salary: Hartstein Name: Fay salary: Fay PL/SQL procedure successfully

completed.

Focus

Training

Services

35

PLSQL

NOTES

Explicit

Cursor

Subqueries

create or replace procedure sp64 --Cursor for loop using subqueries as l_last_name varchar2(30); begin for l_last_name in (select last_name from employees where department_id=20) loop dbms_output.put_line('Name: '|| l_last_name); end loop; end; /

OUTPUT: SQL> @sp64.sql Procedure created. No errors. SQL> exec sp64 Name: Hartstein Salary: 13000 Name: Fay Salary: 6000 PL/SQL procedure successfully

completed.

Focus

Training

Services

36

PLSQL

NOTES

Explicit

Cursor

Passing

parameters

to

cursors

create or replace procedure sp65(deptno number,job varchar2) --This Program shows --How to pass parameters to cursor as cursor c1(l_deptno number,l_job varchar2) is select employee_id,last_name from employees where department_id = l_deptno job_id = l_job; and rec_c1 c1%rowtype;

--Declaration of cursor begin for rec_c1 in c1(90,'AD_VP') loop --Passing Paramenters to cursor dbms_output.put_line('Depatment 80 '||' Job id is SA_MAN'); dbms_output.put_line('Employee ID:'|| to_char(rec_c1.employee_id)); dbms_output.put_line('Employee Name: '|| rec_c1.last_name); end loop; open c1(deptno,job); --Passing Paramenters to cursor loop fetch c1 into rec_c1; exit when c1%notfound;
dbms_output.put_line(deptno||' Job id is '||

job); dbms_output.put_line('Employee ID: '|| to_char(rec_c1.employee_id)); dbms_output.put_line('Employee Name: rec_c1.last_name); end loop; close c1; end; / show errors
Focus Training Services
37

'||

PLSQL

NOTES

SQL> @sp65.sql Procedure created. No errors.

SQL> exec sp65(60,'IT_PROG'); Depatment 80 Job id is SA_MAN Employee ID: 101 Employee Name: Kochhar Depatment 80 Job id is SA_MAN Employee ID: 102 Employee Name: De Haan 60 Job id is IT_PROG Employee ID: 103 Employee Name: Hunold 60 Job id is IT_PROG Employee ID: 104 Employee Name: Ernst
PL/SQL procedure successfully completed.

Focus

Training

Services

38

PLSQL

NOTES

Explicit

Cursor

Update

Clause

create or replace procedure sp66 --This Program shows --The For Update Clause in cursor as cursor c1 is select * from employees where department_id = 20 for update of salary nowait; rec_c1 c1%rowtype; l_new_sal number; begin dbms_output.put_line(rpad('Employee',10)|| rpad('Old Salary',10)|| rpad('New Salary',10)); open c1; loop fetch c1 into rec_c1; exit when c1%notfound; if rec_c1.salary < 7000 then l_new_sal := rec_c1.salary * 1.25; update employees set salary = l_new_sal where employee_id = rec_c1.employee_id; else l_new_sal := rec_c1.salary * 1.15; update employees set salary = l_new_sal where employee_id = rec_c1.employee_id; end if; dbms_output.put_line (rpad(rec_c1.last_name,10)|| rpad(rec_c1.salary,10)|| rpad(l_new_sal,10)); end loop; close c1; end; / show

errors

Focus

Training

Services

39

PLSQL

NOTES

Programer

can

lock
any
open

rows
update
no one

Before
While

performing
cursor is

or
can

delete
access

using

cursor.
rows.

selected

SQL>

@sp66.sql created.

Procedure No

errors. exec sp66 Old SalaryNew Salary

SQL>

Employee Hartstein
Fay

19771.38
9918.75

22737.087
11406.5625

PL/SQL

procedure

successfully

completed.

Focus

Training

Services

40

PLSQL

NOTES

Explicit

Cursor

Where

current

of

clause

create or replace procedure sp67 --This Program shows --The use of where current of clause as cursor c1 is select employee_id,salary from employees where department_id = 20 for update of salary nowait; l_new_sal number; rec_c1 c1%rowtype; begin dbms_output.put_line(rpad('Employee',10)|| rpad('Old Salary',10)|| rpad('New Salary',10)); open c1; loop fetch c1 into rec_c1; exit when c1%notfound; if rec_c1.salary < 7000 then l_new_sal := rec_c1.salary * 1.25; update employees set salary = l_new_sal where current of c1; else l_new_sal := rec_c1.salary * 1.25; update employees set salary = l_new_sal where current of c1; end if; end loop; close c1; dbms_output.put_line(rpad(rec_c1.employee_id,10)|| rpad(rec_c1.salary,10)|| rpad(l_new_sal,10)); end; / show

errors

Focus

Training

Services

41

PLSQL

NOTES

Programer
only current

can
row

update
in

or

delete
by defining

cursor

'where

current

of

clause'

SQL> @sp67.sql Procedure created. No errors. SQL> exec sp67

Employee Old SalaryNew Salary 202 11406.56 14258.2

PL/SQL procedure successfully completed.

Focus

Training

Services

42

IMPLICIT

CURSORS

Focus

Training

Services 43

Implicit

Cursors

in

Plsql

create or replace procedure sp18 --This Program shows how to use --Sql Cursor Attributes(Set by default by sql) --SQL%ROWCOUNT --SQL%FOUND --SQL%NOTFOUND --SQL%ISOPEN as begin insert into test(test_id,test_name) values(3,'Red Hat'); if sql%found then dbms_output.put_line('Row is inserted'); end if; delete from test; dbms_output.put_line(sql%rowcount ||' Rows are selected'); delete from test; if sql%notfound then dbms_output.put_line('No row is deleted'); end if; end; / show errors

Implicit They

cursors

are

implicitly about of

define

by

oracle. query. by query.

by query.

return

information returns
true

result rows
one row

of

SQL%ROWCOUNT
SQL%FOUND

number
if

affected
is

return

atleast

affected

SQL%NOTFOUND

return

true

if

zero

rows

are

affected

by

query.

Focus

Training

Services 44

SQL> @sp18.sql Procedure created. No errors. SQL> select * from test; TEST_ID TEST_NAME -------------------

1 sql SQL> exec sp18 Row is inserted

2 Rows are selected No row is deleted

PL/SQL procedure successfully completed.

Focus

Training

Services 45

PLSQL

NOTES

EXCEPTIONS

Focus

Training

Services 46

PLSQL

NOTES

Exceptions:

Predefined

Exceptions

create or replace procedure sp81 --This Program shows --How as l_last_name begin select into from where last_name
l_last_name

to

handle

Predefine

exceptions

employees.last_name%type;

employees
employee_id = 99999;

dbms_output.put_line(l_last_name);

exception when --No_data_found is no_data_found one of then exception

Not exist');

predefined

dbms_output.put_line('Employee

end; / show

errors

Focus

Training

Services 47

PLSQL

NOTES

Exception is a kind of error that turminates user's program execution example 'divide by 0'.
Oracle has defined approximately 20 errors occur most often.

Known

as

'Predefine

Exceptions'.

OUTPUT SQL> @sp81.sql

Procedure

created.

No

errors.

SQL> exec sp82 Employee Not exist

PL/SQL

procedure

successfully

completed.

Focus

Training

Services 48

PLSQL

NOTES

Exceptions

Non

Predefined

Exceptions

create or replace procedure sp82 --This program shows --How to handle non predefine exceptions as duplicate_key exception; PRAGMA EXCEPTION_INIT (duplicate_key , -00001); begin insert into departments values(20,'New Department',200,1800); commit; exception when duplicate_key then dbms_output.put_line('Cannot insert duplicate department, department already exist'); end; / show errors

Non Predefine exceptions are defined by oracle server, but has no name.
Use Pragma exception to give name to respective exception.

SQL> @sp82.sql Procedure created. No errors. SQL> set serveroutput on SQL> exec sp82 Cannot insert duplicate department, department already exist PL/SQL procedure successfully completed.

Focus

Training

Services 49

PLSQL

NOTES

Exceptions
create or replace procedure sp83 as begin delete from departments where department_id = 20;

Others

exception when others then dbms_output.put_line('In Exception'); dbms_output.put_line(SQLCODE||SQLERRM); end; / show

errors

Exception Others is used when programer does not know the oracle number associated with error. Programer can find error associated number and error message using SQLCODE , SQLERRM

OUTPUT SQL> @sp83.sql Procedure No created.

errors. exec sp83

SQL> In

Exception

-2292ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated child record found PL/SQL procedure successfully completed.

Focus

Training

Services 50

PLSQL

NOTES

Exceptions

User

Defined

Exceptions

create or replace procedure sp84 as l_last_name l_salary l_new_sal invalid_raise begin select last_name,salary into l_last_name,l_salary from employees where employee_id = 100; l_new_sal := l_salary * 1.20; employees.last_name%type; employees.salary%type; float; exception;

if l_new_sal > 2000 then raise invalid_raise; end if; exception when invalid_raise then
Applicable Salary raise');

dbms_output.put_line('Not

end; / show

errors can introduce are known errors as on certain conditions.

Programer Those

errors

user

define

exception.

--OUTPUTSQL> @sp84.sql Procedure created. No errors. SQL> exec sp84 Not Applicable Salary raise PL/SQL procedure successfully

completed.

Focus

Training

Services 51

PLSQL

NOTES

Exceptions

Raise

Application

Errors

create or replace procedure sp85 as l_last_name employees.last_name%type; l_salary employees.salary%type; l_new_sal float; new_exception exception; PRAGMA EXCEPTION_INIT(new_exception,-20999); begin select last_name,salary into l_last_name,l_salary from employees where employee_id = 100; l_new_sal := l_salary * 1.20;
if l_new_sal
> 2000 then

raise_application_error(-20999,'This is not valid salary increase'); end if; exception when new_exception then dbms_output.put_line('in exception'); dbms_output.put_line(SQLCODE||SQLERRM); end; / show errors Programer can issue user define error messages using 'raise_application_error' procedure. Progrmer can assign error code and error message for new exceptions. Error code should be greater than 20,000. --OUTPUT SQL> @sp85.sql Procedure created. No errors. SQL> exec sp85 in exception -20999ORA-20999: This is not valid salary increase PL/SQL procedure successfully completed.

Focus

Training

Services 52

PLSQL

NOTES

Exception

Flow

through

Procedures

create or replace procedure sp86(l_employee_id number) --This program shows --How exception handling passes from one procedure to other as l_salary number; l_last_name varchar2(30); begin dbms_output.put_line('In outer Block'); select salary into l_salary from employees where employee_id = l_employee_id; begin
dbms_output.put_line('In Inner Block');

select last_name into l_last_name from employees; end; exception when no_data_found then dbms_output.put_line('No data found'); when too_many_rows then dbms_output.put_line('too many rows'); end; / show

errors

Focus

Training

Services 53

PLSQL

NOTES

OUTPUT SQL> @sp86.sql

Procedure

created.

No

errors. exec sp86(100); Block

SQL> In

outer

In Inner Block too many rows

PL/SQL

procedure

successfully

completed.

SQL> In No

exec

sp86(1111); Block found

outer data

PL/SQL

procedure

successfully

completed.

Focus

Training

Services 54

PLSQL

NOTES

Most Occurring Errors Select:

ORA-01403 : ORA-00913 : ORA-00902 : ORA-00903 : ORA-00904 : ORA-00905 : ORA-00253 : exceeds limit of num no data found too many values invalid datatype invalid table name invalid column name missing keyword length num of specified archive string 'name'

Insert:
ORA-02259 ORA-02260 ORA-02273 foreign keys ORA-02252 ORA-01400 during insert ORA-01556 : duplicate UNIQUE/PRIMARY KEY specifications : table can have only one primary key : this unique/primary key is referenced by some : check constraint condition not properly ended : mandatory (NOT NULL) column is missing or NULL : maximum number of extents exceeded

Delete:
ORA-02273 : this unique/primary key is referenced by some foreign keys

Focus

Training

Services 55

PLSQL

NOTES

PROCEDURES

Focus

Training

Services

56

PLSQL

NOTES

Procedures
create or replace

IN

OUT

parameters
in out number, number)

procedure

sp91(l_emp_id l_salary

--This Programs shows --How to use in,out parameter as begin select salary into l_salary from Employees where employee_id = l_emp_id; end; / show errors create or replace procedure sp92 as salary number; begin sp91(100,salary); --salary used as out parameter in sp91 dbms_output.put_line(salary); end; / show errors In parameter used to pass value to procedure. from parameter.

Out parameter is used to pass value OUTPUT SQL> @sp91.sql Procedure created. No errors. SQL> @sp92.sql Procedure created. No errors. SQL> exec sp92 24000 PL/SQL procedure successfully completed.

Focus

Training

Services

57

PLSQL

NOTES

Procedures

IN

OUT

Parameters

create or replace procedure sp93 --This program shows --How to use inout parameter as p_phone_no varchar2(20); begin p_phone_no := '1234567890'; sp94(p_phone_no); dbms_output.put_line(p_phone_no); end; / show errors

create or replace procedure sp94 (p_phone_no IN OUT varchar2) is begin p_phone_no := '(' || substr(p_phone_no,1,3) || ')'||' '|| substr(p_phone_no,4,3) || '-' || substr(p_phone_no,7); end; / show errors IN OUT parameter used to pass value to procedure and return some value in the same variable. i.e. Programer need only 1 variable. OUTPUT SQL> @sp94.sql Procedure created. No errors. SQL> @sp93.sql Procedure created. No errors. SQL> exec sp93 (123) 456-7890 PL/SQL procedure successfully completed.

Focus

Training

Services

58

PLSQL

NOTES

FUNCTIONS

Focus

Training

Services

59

PLSQL

NOTES

Functions
create or replace function get_dept_name(dept_no departments.department_id%type) return varchar2 --This program shows --How to write user is l_dept_name begin select into from where return end; / show department_name l_dept_name Departments department_id = l_dept_name; departments.department_name%type;

define

functions

dept_no;

errors

Functions Functions Functions

are are

used

when to

one

task

is

executed

repeatedly.

similar

procedures. some value to callie.

always

returns

After functions are they can be called

created, from sql

query

also.

Focus

Training

Services

60

PLSQL

NOTES

OUTPUT SQL> @sp10_1.sql Function No created.

errors.

SQL> select last_name,department_name 2 from employees e,departments d 3 4 where and e.department_id employee_id = = d.department_id

100;

LAST_NAME

DEPARTMENT_NAME

------------------------- -----------------------------King Executive

SQL> 2 3

select from where

last_name,get_dept_name(department_id)

employees employee_id = 100;

LAST_NAME -----------------------King

GET_DEPT_NAME(DEPARTMENT_ID) -----------------------------Executive

Focus

Training

Services

61

PLSQL

NOTES

PACKAGES

Focus

Training

Services

62

PLSQL

NOTES

Packages
create or replace package calculator as --This Programs shows package declaration/specification --How to create package with public procedures procedure procedure end calculator; / show errors Package use to group related procedures together. add(no1 number, no2 number); no2 number);

subtract(no1

number,

In Package declaration declare names of procedures and global variables. In Package body write code for those procedures.

create or replace package body calculator is --This Program shows --How to declare body of package procedure add(no1 number,no2 number) is begin dbms_output.put_line('Addition no2)); end add;

is:

'||to_char(no1

procedure subtract(no1 number,no2 number) is begin dbms_output.put_line('Subtraction is: - no2)); end subtract; end calculator; / show errors

'||to_char(no1

Focus

Training

Services

63

PLSQL

NOTES

SQL>

@cal_pac.sql created.

Package

No errors.

SQL> @cal.sql Package body created. No errors. SQL> exec calculator.add(20,30); Addition is: 50 PL/SQL procedure successfully completed. SQL> exec calculator.subtract(30,40); Subtraction is: -10
PL/SQL procedure successfully completed.

Focus

Training

Services

64

PLSQL

NOTES

Package
create or replace package calculator as

--This --How

Programs to create

shows package with public procedures

--How to define global variables with default values --How to define public functions

count_add count_subtract

number number

:= 0; := 0;

procedure add(no1 number, no2 number); procedure subtract(no1 number, no2 number);

function get_add_count return number; function get_subtract_count return number;

end calculator; / show errors

Focus

Training

Services

65

PLSQL

NOTES

create or replace package body calculator is --This Program shows --How to define private procedures in package body procedure print(text varchar2); function get_add_count return number as begin return count_add; end get_add_count; function get_subtract_count return number as begin return count_subtract; end get_subtract_count; procedure add(no1 number,no2 number) is begin count_add := count_add + 1; print('Addition is: '||to_char(no1 + no2)); end add; procedure subtract(no1 number,no2 number) is begin count_subtract := count_subtract + 1; print('Subtraction is: '||to_char(no1 - no2)); end subtract; procedure is begin dbms_output.put_line(text); end print; calculator; errors
Training Services
66

print(text

varchar2)

end / show
Focus

PLSQL

NOTES

SQL> @cal1.sql Package body created. No errors. SQL> !vim cal1.sql SQL> @cal_pac1.sql Package created. No errors. SQL> @cal1.sql Package body created. No errors.
SQL> exec calculator.add(5,2); Addition is: 7

PL/SQL procedure successfully completed. SQL> exec calculator.subtract(10,5); Subtraction is: 5

PL/SQL procedure successfully completed.

Focus

Training

Services

67

PLSQL

NOTES

Package

Procedure

Overloading

create or replace package calculator1 as --This Programs shows --How to create package with public procedures --And Procedure overloading procedure add(no1 number, no2 number);

procedure

add(no1

varchar2,

no2

varchar2);

procedure

add(no1

number,

no2

number,

no3

number);

end / show

calculator1;

errors

Focus

Training

Services

68

PLSQL

NOTES

create

or

replace

package

body

calculator1

is

procedure add(no1 number,no2 number) is begin dbms_output.put_line('Addition no2));

is:

'||to_char(no1

end add; procedure add(no1 varchar2,no2 varchar2) is begin dbms_output.put_line('Concatination no2); end add;

is:

'||no1

||

procedure add(no1 number,no2 number,no3 number) is begin dbms_output.put_line('Subtraction is: '||to_char(no1 + no2 + no3)); end add; end calculator1; / show errors

Procedure Overloading means defining procedure with same name but with different parameters,data types. In above package procedure add is overloaded.

Focus

Training

Services

69

PLSQL

NOTES

SQL> exec calculator1.add(10,20); Addition is: 30 PL/SQL procedure successfully completed. SQL> exec calculator1.add('scott','tiger'); Concatination is: scott tiger PL/SQL procedure successfully completed. SQL> exec calculator1.add(10,20,30); Addition is: 60 PL/SQL procedure successfully completed.

Focus

Training

Services

70

PLSQL

NOTES

ORACLE

PACKAGES

Focus

Training

Services

71

PLSQL

NOTES

Default

Packages

UTL_FILE

create or replace procedure sp11_1 --This Program shows --How to send mails using UTL_MAIL package as ora_no number; ora_msg varchar2(100); begin UTL_MAIL.SEND ( sender => 'gaurav@server1.example.com', recipients => 'gaurav@server1.example.com', cc bcc => => 'mithilesh@server1.example.com', 'krunal@server1.example.com', => => 'test 'hi mail', r u??'

subject message );

how

dbms_output.put_line('Message exception when others then

send

successfully');

ora_no := sqlcode; ora_msg := sqlerrm; dbms_output.put_line('Message not send');

dbms_output.put_line(ora_no end; / show

||'

'||ora_msg);

errors

Focus

Training

Services

72

PLSQL

NOTES

OUTPUT SQL> @sp11_1.sql

Procedure

created.

No

errors. exec sp11_1 successfully

successfully completed.

SQL>

Message
PL/SQL

send

procedure

Focus

Training

Services

73

PLSQL

NOTES

Default

Packages

UTL_FILE

create or replace procedure sp11_2 --This programs show --How to read data from text file --using UTL_FILE Package as v_dir varchar2(200); --Specify Directory name and path v_file_name varchar2(100); --Specify File Name v_line varchar2(500) --Accept file line by line in this variable v_file UTL_FILE.FILE_TYPE; --File Handler begin v_dir := '/home/gaurav/plsql/'; v_file_name :='utl_file.txt';

:= v_file UTL_FILE.FOPEN(v_dir,v_file_name,'r'); --File Open in read only mode loop begin UTL_FILE.GET_LINE(v_file,v_line); exception when no_data_found then exit; end;

dbms_output.put_line(v_line); end end; / show loop;

errors

Focus

Training

Services

74

PLSQL

NOTES

OUTPUT [gaurav@server1 plsql]$ cat utl_file.txt hello how r u?? gaurav OUTPUT SQL> !vim sp11_2.sql

SQL>

@sp11_2.sql

Procedure

created.

No

errors.

SQL> exec sp11_2 hello how r u?? gaurav

PL/SQL

procedure

successfully

completed.

Focus

Training

Services

75

PLSQL

NOTES

Default

Packages

DBMS_SCHEDULER

create or replace procedure sp11_6 --This procedure shows --How to use default package --DBMS_SCHEDULER to schedule some task as orr_code number; orr_msg varchar2(500); begin --create_job is inbuild procedure in

DBMS_SCHEDULER

DBMS_SCHEDULER.CREATE_JOB ( job_name => 'update_sales', job_type => 'STORED_PROCEDURE', job_action => 'sp11_4', start_date => '20-APR-10 03.10.00.000000000 ASIA/CALCUTTA', repeat_interval => 'FREQ=SECONDLY;INTERVAL=10', end_date => '20-APR-10 03.11.00.000000000 ASIA/CALCUTTA', comments => 'My new job');
exception when others then orr_code := sqlcode; orr_msg := sqlerrm; dbms_output.put_line(orr_code||'

PM

PM

'||orr_msg);

end; / show

errors

Focus

Training

Services

76

PLSQL

NOTES

OUTPUT 15:04:48 SQL> truncate table test_sch; Table truncated. 15:04:57 SQL> @sp11_6.sql Procedure created. No errors. 15:05:01 SQL> exec sp11_6 PL/SQL procedure successfully

completed.

15:05:06 SQL> exec dbms_scheduler.set_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', 2); -- requires manage scheduler privilege PL/SQL procedure successfully completed. 15:05:22 -- enable SQL> the exec job dbms_scheduler.enable('update_sales');

PL/SQL

procedure SQL>

successfully * from

completed. test_sch;

15:05:43 no rows

select

selected SQL> select count(*) from test_sch;

15:07:31

COUNT(*) ---------0 15:09:41 SQL> / COUNT(*) ---------100

Focus

Training

Services

77

PLSQL

NOTES

15:10:04 COUNT(*) ---------200 15:10:14 COUNT(*) ---------300 15:10:27 COUNT(*) ---------400 15:10:37 COUNT(*) ---------500 15:10:46 COUNT(*) ---------600 15:10:53 COUNT(*) ---------600

SQL>

SQL>

SQL>

SQL>

SQL>

SQL>

Focus

Training

Services

78

PLSQL

NOTES

DYNAMIC

SQL

Focus

Training

Services

79

PLSQL

NOTES

Dynamic

Sql

create or replace procedure sp12_1(l_table_name varchar2) --This program shows --How to build dynamic sql queries. as sql_query varchar2(50); l_count number; begin sql_query := 'select count(*) from '||l_table_name; execute immediate sql_query into l_count; --Write sql command in a varchar2 variable --And then use command 'execute immediate' --Accept returing value in appropriate variable dbms_output.put_line(l_count); end; / show

errors

OUTPUT SQL> @sp12_1.sql created.

Procedure No

errors. exec sp12_1('REGIONS');

SQL> 4

PL/SQL

procedure

successfully

completed.

Focus

Training

Services

80

PLSQL

NOTES

TRIGGERS

Focus

Training

Services

81

PLSQL

NOTES

Trrigers
create or replace trigger chk_emp_sal --This programs shows --How to declare triggers --for each row before of on for declare v_error begin if :new.salary > 25000 then VARCHAR2(2000); insert or salary employees
each row

update

v_error:=:old.first_name||' cannot have that much!'; raise_application_error(-20999,v_error); end end; / show if;

errors

Oracle lets you define procedures called triggers that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table or, in some cases, against a view, or when database system actions occur.

Focus

Training

Services

82

PLSQL

NOTES

OUTPUT SQL> @14_1.sql

Trigger

created.

No

errors.

SQL> insert into employees(EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,SALARY)

2 values(215,'Bhide','abc@gmail.com',sysdate,'IT_PROG',26000);

insert into employees(EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,SALARY) * ERROR at line 1:

ORA-20999: cannot have that much! ORA-06512: at "HR.CHK_EMP_SAL", line 7

ORA-04088: error during execution of trigger 'HR.CHK_EMP_SAL'

Focus

Training

Services

83

PLSQL

NOTES

Triggers
create or replace

Updating,Inserting,Deleting
trig_example

trigger

before insert or delete or update on trig_eg for each row declare ChangeType begin
/* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */

varchar2(10);

if inserting then ChangeType := 'I'; elsif updating then ChangeType := 'U'; else ChangeType end insert into if; changes_record values(ChangeType,USER,SYSDATE); := 'D';

end trig_example; / show errors

Focus

Training

Services

84

PLSQL

NOTES

OUTPUT SQL> @sp14_2.sql Trigger created. No errors. SQL> select * from CHANGES_RECORD; no rows selected SQL> insert into trig_eg values(1,'aaa'); 1 row created. SQL> insert into trig_eg values(2,'bbb'); 1 row created. SQL> update trig_eg set name = 'xxxx' where id = 2; 1 row updated. SQL> delete from trig_eg where id = 2; 1 row deleted. SQL> select * from CHANGES_RECORD; CHANGE USER_NAME CHANGE_DA ---------- ---------- --------I I U D SQL> GAURAV GAURAV GAURAV GAURAV select ID ---------1 * from 18-APR-10 18-APR-10 18-APR-10 18-APR-10 trig_eg;

NAME ---------aaa

Focus

Training

Services

85