---
PL/SQL structure
declare
begin
exception
end;
Program
:
set
serveroutput on
begin
dbms_output.put_line('welcome');
end;
Program
:
declare
x number:=10;
or x number default :=10;
begin
dbms_output.put_line(x);
end;
Program
:
declare
x
number;
begin
x:=&x;
dbms_output.put_line(x);
end;
Program
:
declare
sal
emp.esal%type;
begin
select
esal into sal from emp where
eno='e001';
dbms_output.put_line(sal);
end;
Program
:
declare
empdet
emp%rowtype;
begin
select
* into empdet from emp where
eno='e001';
dbms_output.put_line(empdet.eno
|| empdet.ename);
end;
Program
:
----------
Control Structures
--
if
declare
age
number(3);
begin
age:=&age;
if
age < 18 then
dbms_output.put_line('minor');
else
dbms_output.put_line('major');
end
if;
end;
--
simple loop
declare
x number:=1;
begin
loop
dbms_output.put_line('hai');
x:=x+1;
exit when x=5;
end
loop;
end;
--
while loop
declare
x number:=1;
begin
while x<5 loop
dbms_output.put_line('hai');
x:=x+1;
end loop;
end;
--
for loop
declare
x number;
begin
for x in 1..5
dbms_output.put_line('hai');
end loop;
end;
--
goto
declare
sal
emp.esal%type;
begin
select
esal into sal from emp where
eno='e001';
if
sal < 5000 then
goto temp;
end
if;
<<temp>>
dbms_output.put_line('salary is less than 5000');
end;
---------
Exception Handling -----------
-----------------------------
Predefined
exception
-----------------------------
declare
qoh
itemfile.qty_hand%type;
begin
select
qty_hand into qoh from itemfile where itemc='i100';
exception
when
no_data_found then
dbms_output.put_line('such an item number not found');
dbms_output.put_line('after');
end;
---------------------------
User-defined
exception
----------------------------
declare
lo_value exception;
qoh itemfile.qty_hand%type;
begin
select qty_hand into qoh from itemfile where icode='i100';
if qoh < 100 then
raise lo_value;
end if;
exception
when lo_value then
dbms_output.put_line('quantity not enough-reorder');
end;
Predefined
exceptions :
------------------------
no_data_found
-
when select statement return no rows
dup_val_on_index
-
when we insert duplicate values in a
column, which is defined as unique index.
too_many_rows
- when the select into staement
return more than one row.
storage_error
-
when PL/SQL runs out of memory/memory is
corrupted.
zero_divide
- when
we try to divide a number by zero.
EXCEPTION_INIT
Pragma
---------------------
(e.x)
XYZ would like to trap any duplicate primary keys that are inserted
into
the itemfile table. This they would like to achieve without the
system
generated error message. They achieved generating their own error message,
Program
:
decalre
dup_prim_key exception;
pragma exception_init(dup_prim_key,-1);
begin
insert into itemfile values('i201',10,20);
exception
when dup_prim_key then
dbms_output.put_line('duplicate item number');
end;
---
RAISE_APPLICATION_ERROR
-------------------------------------------
Program
:
declare
qoh
itemfile.qty_hand%type;
begin
select
qty_hand into qoh from itemfile where itemc='i100';
exception
when
no_data_found then
raise_application_error(-20001,'such an item number not found');
end;
(note)
error no: -20,000
to -20,999 only.
SQLCODE
& SQLERRM ( when OTHERS )
---------------------------------------------------
Program
:
declare
s
emp.esal%type;
begin
select
esal into s from emp where eno='e007';
exception
when
others then
dbms_output.put_line(SQLCODE||SQLERRM);
end;
------------
Cursor Management --------------
-------------
Implicit Cursor---------------
Program
:
begin
delete from order_detail where orderno='o201';
if sql%NOTFOUND then
dbms_output.put_line('value not found');
else
dbms_output.put_line('value found and deleted');
end if;
end;
Program
:
declare
variable
rows_del number(10)
begin
delete from emp where esal > 10000;
:rows_del:=SQL%ROWCOUNT;
end;
print
rows_del
-------------
Explicit Cursor-----------------
Program
:
declare
icode order_detail.itemcode%type;
cursor a is select itemcode from order_detail
where itemcode='i201';
begin
open a;
loop
fetch a into icode;
update itemfile set itemrate=22.50 where itemcode=icode;
exit when a%NOTFOUND;
end loop;
dbms_output.put_line('table updated');
close a;
end;
Program
:
declare
cursor a is select * from order_detail
where order_no='o001';
myorder order_detail%rowtype;
begin
open a;
loop
fetch a into myorder;
exit when a%NOTFOUND;
dbms_output.put_line('fetched' || a%ROWCOUNT || 'from table');
end loop;
close a;
end;
-----Cursor
For Loop----------
Program
:
declare
cursor forcur is select orderno from order_master
where vencode='v002';
custrec forcur%rowtype;
begin
for custrec in forcur loop
delete from order_detail where orderno = custrec.orderno;
end loop;
dbms_output.put_line('detail
has been deleted');
end;
----
REF cursor
Program
:
declare
type
rcur is ref cursor;
var1
rcur;
nam
varchar2(5);
no
number(2);
begin
no:=&enterno;
if
no=10 then
open var1 for select orderno from order_master
where vencode='v001';
fetch var1 into nam;
dbms_output.put_line('order no is'||nam);
close var1;
else
open var1 for select qty_ord from order_detail
where orderno='o001';
loop
fetch var1 into no;
exit when var1%NOTFOUND;
dbms_output.put_line('qty ordered is'||no);
end loop;
close var1;
end
if;
end;
--------Advanced
Explicit Cursor Concepts ( 9i ) ---------
---
Cursors With Parameters
Program
:
declare
cursor
emp_cursor( dno number, job varchar2 ) is
select emp_id,emp_name from emp
where dept_id=dno and job_id=job;
begin
open emp_cursor( 80,'REP');
.....
close emp_cursor;
open emp_cursor( 60,'PROG');
.....
close emp_cursor;
end;
----
FOR UPDATE OF clause
*
Use explicit locking to deny access for the duration
of transaction.
*
Lock the rows before the update or delete.
(ex)
Retrieve the emp who work in dept 80 and update their salary
Program
:
declare
cursor
emp_cursor is select eno,ename,dname from emp,dept
where emp.dno=dept.dno and emp.dno=80
FOR UPDATE OF salary NOWAIT;
...
begin
end;
----
WHERE CURRENT OF clause
* use the where current of clause to reference the
current row from an explicit cursor.
Program
:
declare
cursor
emp_cursor is select eno,ename,dname from emp,dept
where emp.dno=dept.dno and emp.dno=80
FOR UPDATE OF salary NOWAIT;
begin
for emprec in emp_cursor loop
if emprec.salary < 5000 then
update emp set salary=salary * 1.10
WHERE CURRENT OF emp_cursor;
end if;
end;
---------------
Subprograms -----------------
---
Procedures
create
or replace procedure saldisp(no varchar2)
is
sal number;
begin
select
esal into sal from emp where eno=no;
dbms_output.put_line('salary
is:' || sal);
end;
o/p:-
exec
saldisp('e001');
Parameters
: in , out &
in out (default is in)
------------------------------------------------
--
out
create
or replace procedure saldisp(a in varchar2,b out number)
is sal number;
begin
select
esal into sal from emp where eno=a;
b:=sal;
end;
Program
:
declare
a varchar2(5);
b number;
begin
saldisp('e001',b);
dbms_output.put_line(b);
end;
--
in out
create
or replace procedure test(x in out number) is
begin
dbms_output.put_line(x);
x:=10;
end;
Program
:
declare
n number;
begin
n:=&n;
test(n);
dbms_output.put_line(n);
end;
----
Functions
Program
:
create
or replace function square(no number)
return number is
begin
return
no*no;
end;
Program
:
declare
s
number;
begin
s:=square(5);
dbms_output.put_line(s);
end;
-------------
Packages -----------------
Program
:
create
or replace package packme is
function square(no number) return number;
procedure saldisp(no varchar2);
end
packme;
Program
:
create
or replace package body packme as
procedure saldisp(no varchar2)
is sal number;
begin
select esal into sal from emp where eno=no;
dbms_output.put_line('salary is:' || sal);
end saldisp;
Program
:
function square(n number)
return number is
begin
return n*n;
end square;
end
packme;
o/p:-
exec
packme.saldisp('e001')
(notes)
overloading can be possible
--
cursors in package
Program
:
create
or replace package curpack is
cursor ordcur return order_master%rowtype;
procedure ord_pro(orno varchar2);
end
curpack;
create
or replace package body curpack as
cursor ordcur return order_master%rowtype
is select * from order_master;
procedure ord_pro(orno varchar2) is
orrec order_master%rowtype;
begin
open ordcur;
loop
fetch ordcut into orrec;
exit when ordcur%NOTFOUND;
dbms_output.put_line(orrec.orderno);
end loop;
end ord_pro;
end
curpack;
o/p:-
exec
curpack.ord_pro('o001');
© 2004-2005 Mercury Digital
Media
