PL/SQL
======
-- it is procedural
lang used for only oracle db
-- developed by
oracle
-- introduced in ora
7.0
-- versition pl/sql
9.2
-- it is an extension
of SQL
-- it is only
procedural lang which has 2 powers
a.non-procedural --
what to do
b.procedural --how to
do
-- it is used to
process data in oracle or used for db progamming
-- it is known as
block structured language
-- it provides 2 type
of pl/sql block
1.anonymous pl/sql
block
2.named pl/sql block
1.anonymous pl/sql block:
-- the block which
has no name
-- it can not be
invoked/called
-- such block is used
in
-- orcle product
(d2k)/forms/reports/graphics/..
-- in named block
2.named pl/sql block:
-- The block with
name
- PROCEDURE
- FUNCTION
- PACKAGE
- TRIGGER
# pl/sql block can be nested
# it uses 04 keyword to form block
DECLARE
BEGIN
EXCEPTION
END
# it has 3 parts/sections
DECLARATION section
EXECUTION section
EXCEPTION section
SELECT ename FROM emp
WHERE empno=11111;
dbms_sql
EXECUTE IMMEDIATE --
procedure
note --> select statement
must return 1 and only 1 row due to select error raised as
1.data not found
2.many rows
how to display data from variable
or how to display server message
-- by using pkg
DBMS_OUTPUT.PUT_LINE(<'message'>);
-- it accept only 1
message at a time
DECLARE
v_name VARCHAR2(15);
v_sal NUMBER;
BEGIN
SELECT ename,sal INTO
v_name, v_sal FROM emp WHERE ename='FORD';
DBMS_OUTPUT.PUT_LINE(v_name||'
'||v_sal);
END;
/
-- writing DML operation
BEGIN
UPDATE emp SET
comm=100 WHERE empno=11111;
END;
pl/sql block can be nested:
<<A>>
DECLARE
x NUMBER;
BEGIN
x:=20;
<<B>>
DECLARE
x NUMBER;
x:=2;
<<C>>
DECLARE
x:=A.x * B.x;
DBMS_OUTPUT.PUT_LINE(x);
END;
END;
END;
-- used to define variable dynamically based on Column of table or
existing variable
-- by using varible attributes
%TYPE
%ROWTYPE
%TYPE: used to define
a variable based on a single column
<var_name>
<TABLE_name>.<col_name>;
v_enmae
emp.ename%TYPE;
DECLARE
v_ename
emp.ename%TYPE;
v_job emp.job%TYPE;
BEGIN
SELECT ename,job INTO
v_ename,v_job FROM emp WHERE empno=&eno;
DBMS_OUTPUT.PUT_LINE(v_ename||'
'||v_job);
END;
%ROWTYPE: used to
define a single variable for all column of a TABLE
<var_name>
<tabl_name>%ROWTYPE;
<var_name.<col_name>
vrec emp%ROWTYPE;
how to use thIS
variable FOR each column?
<var_name>.<col_name>
vrec.ename
vrec.empno
DECLARE
vrec emp%ROWTYPE;
BEGIN
SELECT name,job INTO
vrec FROM emp WHERE empno=&eno;
DBMS_OUTPUT.PUT_LINE(vrec.ename||'
'||vrec.job);
END;
DECLARE
v_empno NUMBER;
v_sal NUMBER
BEGIN
SELECT v_empno,v_sal
INTO v_empno,v_sal FROM emp emp WHERE empno=&eno;
--process--
--pl statement--
--flow control Statement--
UPDATE emp SET
comm=comm+100 WHERE empno=v_empno;
END;
FLOW CONTROL STATEMENT:
=======================
1.Conditional
2.iterative
3.Sequencial
4.Unconditional
1.Conditional:
-----------
-- It provides 3 fomates
a.checking only 1
condition
b.checking only 2
condition
c.checking more than
2 conditions
a.checking only 1 condition:
-------------------------
IF <condition>
THEN
<SQL
statement>;
END IF;
b.checking only 2 condition:
-------------------------
IF <condition1>
THEN
<SQL
statement>;
ELSE
<SQL
statement>;
END IF;
c.checking more than 2 conditions:
-------------------------------
IF <condition1>
THEN
<SQL
statement>;
ELSIF
<condition> THEN
<SQL statement>:
ELSIF
<condition3> THEN
<SQL
statement>;
........
........
[ELSE]
<SQL
statement>;
END IF;
# only 1 condition
processed & once
2.iterative:
---------
-- to process same statement more than 1 time
-- it provides 3 formate
a.LOOP
b.WHILE LOOP
c.FOR LOOP
a).LOOP:
----
--Process--
END LOOP;
-- it not having its
own termination point
-- we use exteral
terminating point how ?
EXIT
WHEN<condition>;
or
IF <condition>
THEN
EXIT;
END IF;
b).WHILE LOOP:
----------
WHILE
<condition>
LOOP
--process--
END LOOP;
c).FOR LOOP:
--------
FOR <COUNTer>
IN [reverse] 1..<limit>
LOOP
--process--
END LOOP;
3.Sequencial:
----------
4.Unconditional:
-------------
-- Avoid to use goto
<<level_name>>
DECLARE
BEGIN
sql1
GOTO
<<L1>>
sql2 skip
<<L1>>
sql3
END;
DECLARE
v_empno NUMBER;
v_sal NUMBER;
BEGIN
SELECT empno,sal INTO
v_empno,v_sal FROM emp WHERE empno=&eno;
--process--
IF v_sal<2000 THEN
UPDATE emp st
comm=sal*10 WHERE empno=v_empno;
DBMS_OUTPUT.PUT_LINE('<2000
processed..');
ELSE
UPDATE emp SET
comm=sal*15 WHERE empno=v_empno;
DBMS_OUTPUT.PUT_LINE('>2000
processed..');
END IF;
END;
prINt1:
------
1
12
123
1234
12345
12345
1234
123
12
1
prINt2:
------
1 1
12 12
123 123
1234 1234
12345 12345
12345 12345
1234 1234
123 123
12 12
1 1
DECLARE
str
VARCHAR2(10):='ASHOK'
vln NUMBER;
BEGIN
vln:=LENGTH(str);
FOR i IN 1..5
LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(str,1,I)||LPAD('
',2*vln,' ')||SUBSTR(str,1,I));
vln:=vln+1;
END LOOP;
END;
DECLARE
x NUMBER:=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(x||'
'||'webnology');
EXIT WHEN x=10;
x:=x+1;
END LOOP;
END;
/
BEGIN
FOR j IN 1..10
LOOP
DBMS_OUTPUT.PUT_LINE(j||'
'||'webnology');
END LOOP;
END;
/
DECLARE
x NUMBER:=1;
BEGIN
WHILE x<=10
LOOP
DBMS_OUTPUT.PUT_LINE(x||'
'||'webnology');
x:=x+1;
END LOOP;
END;
/
DECLARE
str
VARCHAR2(10):=''12345;
BEGIN
FOR 1 IN 1..5
LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(str,1,I));
END LOOP;
FOR i IN reverse 1..5
LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(str,1,I));
END LOOP;
END;
/
DECLARE
str
VARCHAR2(10)='12345';
vln NUMBER(2);
i NUMBER(2):=1;
BEGIN
LOOP
vln:=LENGTH(str);
DBMS_OUTPUT.PUT_LINE(SUBSTR(str,1,vln));
EXIT WHEN I=vln;
I:=I+1;
END LOOP;
LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(str,1,vln));
vln:=vln-1;
EXIT WHEN vln=0;
END LOOP;
END;
/
DECLARE
str
VARCHAR2(10):='12345';
vln NUMBER;
I NUMBER:=1;
BEGIN
LOOP
vln:=vln-1;
EXIT WHEN I=vln;
I:=I+1;
END LOOP;
LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(str,1,vln));
vln:=vln-1;
EXIT WHEN vln=0;
END LOOP;
END;
/
DECLARE
str
VARCHAR2(10):'12345';
vln NUMBER;
I NUMBER:=1;
BEGIN
vln:LENGTH(str);
WHILE I<vln
LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(str,1,I));
END LOOP;
I:=1;
WHILE vln>=I
LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(str,1,I));
vln:vln-1;
END LOOP;
END;
/
-----------------------------------------------------------------------------------
--------
# how to process single-row using pl/sql block ?
# how to process multiple multiple-rows using pl/sql block ?(by
using cursors)
----==============********************
END FOR PL/SQL BASICS
********************==============----
CURSORS:
=======
-- It is a pl/sql
construct(structure) to process multiple rows.
CURSOR TYPE
1.explicit cursor --
user defined
2.implicit cursor
--oracle defined
1.explicit cursor:
having 2 formates
a.explicit cursor
with keywords
b.explicit cursor
without keywords(also know as for-loop cursor)
a.explicit cursor
with keywords:
OPEN
FETCH
EXIT
CLOSE
how cursor works?
cursor query syntax
-- define variable
depending query
DECLARE
CURSOR c1 IS SELECT
....;
<variables>
BEGIN
OPEN
<cur_name>;
LOOP
FETCH
<cur_name> INTO <var1>,<var2>,...;
EXIT WHEN
<cur_name>%notfound;
--process--
END LOOP;
END;
-- update comm for
deptno=20
comm=10 % of sal WHEN
sal<2000
comm=15 % of sal WHEN
sal>=2000
DECLARE
CURSOR c1 IS SELECT
empno,sal FROM emp WHERE deptno=20;
v_sal NUMBER;
v_empno NUMBER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_empno,v_sal;
EXIT WHEN
c1%notfound;
--process--
IF v_sal<2000 THEN
UPDATE emp SET
comm=sal*.10 WHERE empno=v_empno;
DBMS_OUTPUT.PUT_LINE(v_sal||'
'||'processed<2000');
ELSE
UPDATE emp SET
comm=sal*.15 WHERE emono=v_empno;
DBMS_OUTPUT.PUT_LINE(v_sal||'
'||'processed>=2000');
END IF;
END LOOP;
CLOSE c1;
END;
/
--> using cursor
process comm for deptno=30
comm=10% of sal WHEN
jobn='MANAGER'
comm=20% of sal WHEN
sal>=2000
comm=5% of sal wjen
comm IS null
-- concept of cursor
CURSOR TYPE
explicit CURSOR
syntax with keywords
-------------------------------------------
CURSOR FOR LOOP -- know as shor cut method of explicit cursor
-------------------------------------------
<CURSOR query>
-- optional
OPEN -- by oracle
FETCH -- by oracle
EXIT -- by oracle
CLOSE -- by oracle
variable can be
defined using %ROWTYPE based on CURSOR
%TYPE --single row
column
%ROWTYPE --all column
of table or all column in query
DECLARE
FOR <var> IN
<cur_name>
LOOP
--process--
END LOOP;
END;
example: -->
cursor for loop with query
UPDATE comm for any
deptno=20
comm=10% of WHEN
sal<2000
20% of sal WHEN
sal>=2000
DECLARE
CURSOR c1 IS SELECT
empno,sal FROM emp WHERE deptno=20;
BEGIN
FOR vrec IN c1
LOOP
--process--
--check the sal
status--
IF vrec.sal<2000
THEN
UPDATE emp SET
comm=sal*.10 WHERE empno=vrec.empno;
DBMS_OUTPUT.PUT_LINE('process
<2000 sal');
ELSE
UPDATE emp SET
comm=sal*.20 WHERE empno=vrec.empno;
DBMS_OUTPUT.PUT_LINE('process
>=2000 sal');
END IF;
--process over--
END LOOP;
END;
exaple: --> cursor
for loop without query
BEGIN
FOR vrec IN (SELECT
empno,sal FROM emp WHERE deptno=20)
LOOP
--process--
--check the sal
status--
IF vrec.sal<2000
THEN
UPDATE emp SET
comm=sal*.10 WHERE empno=vrec.empno;
DBMS_OUTPUT.PUT_LINE('process
<2000 sal');
ELSE
UPDATE emp SET
comm=sal*.20 WHERE empno=vrec.empno;
DBMS_OUTPUT.PUT_LINE('process
>=2000 sal');
END IF;
--process over--
END LOOP;
END;
---------------------------------
how to view the CURSOR status
---------------------------------
-- by using cursor
attributes(pre-defined program like funtion which return the
status of cursor)
--after open cursor
<cur_name>%ISOPEN
--T/F
--to check CURSOR IS
OPEN or not OPEN
--after fetch
<cur_name>%FOUND
--T/F
--rows retrieved from
active set
--after fetch
<cur_name>%notfound
--t/f
--rows retrieved from
active set
<cur_name>%rowCOUNT
--t/f
--return no of rows
passed to execution area from active set
example: -->
example of cursor attributes
DECLARE
CURSOR c1 IS SELECT
empno,sal FROM emp;
BEGIN
OPEN c1;
DBMS_OUTPUT.PUT_LINE('no
of rows processed '||c1%rowCOUNT);
LOOP
FETCH c1 INTO
v_empno,v_sal;
EXIT WHEN
c1%notfound;
--process--
IF v_sal<2000 THEN
UPDATE emp SET
comm=sal*.10 WHERE empno=v_empno;
ELSE
UPDATE emp SET
comm=sal*.15 WHERE empno=v_empno;
END IF;
DBMS_OUTPUT.PUT_LINE('no
of rows processed'||c1%rowCOUNT);
END LOOP;
DBMS_OUTPUT.PUT_LINE('no
of rows processed'||c1%rowCOUNT);
CLOSE c1;
END;
/
---------------------------
how to stop process in case of cursor before completing the active
set (-- by using
%rowcount)
DECLARE
CURSOR c1 IS SELECT
empno,sal FROM emp;
v_sal NUMBER;
v_empno NUMBER;
BEGIN
OPEN c1;
DBMS_OUTPUT.PUT_LINE('no
of rows processed'||c1%rowCOUNT);
LOOP
FETCH c1 INTO
v_empno,v_sal;
EXIT WHEN
c1%notfound;
--process--
IF v_sal<2000 THEN
UPDATE emp SET
comm=sal*.10 WHERE empno=v_empno;
ELSE
UPDATE emp SET
comm=sal*.15 WHERE empno=v_empno;
END IF;
DBMS_OUTPUT.PUT_LINE('no
of rows processed'||c1%rowCOUNT);
EXIT WHEN
c1%rowCOUNT=10;
END LOOP;
CLOSE c1;
END;
/
--------------------------------
limitations of cursor for-loop
--------------------------------
cursor attributes not allowed
--------------------------------
professional track(cursor)
--------------------------------
--Advanced concepts
-- PARAMETRIC CURSOR
-- FOR UPDATE with
CURSOR
-- WHERE CURRENT of
<cur_name>
-- ROWID WITH CURSOR
-- INLINE VIEW WITH
CURSOR
-- NESTED CURSOR
-- CURSOR IN PACKAGE
--------------------------------
CURSOR tASk
-- syncup of 2 tables
-- emp
-->emp_report
write pl/sql
anonymous block
1.in case of 1'st
time run enter all rows to emp_report
2.in case of 2'nd run
*update emp_report as
what
*if any rows in
emp_report and not emp_report
---------------------
syncup of 2 tables
---------------------
DECLARE
CURSOR c1 IS SELECT
empno FROM emp;
v_empno NUMBER;
cnt NUMBER;
vsal1 NUMBER;
vsal2 NUMBER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO
v_empno;
EXIT WHEN
c1%notfound;
-- check new rows --
and insert --
SELECT COUNT(*) INTO
cnt FROM emp_report WHERE empno=v_empno;
IF cnt=0 THEN
INSERT INTO
emp_report
SELECT *FROM emp
WHERE empno=v_empno;
ELSE
--check old rows --
and update --
SELECT e.sal,r.sal
INTO vsal1,vsal2 WHERE e.empno=r.emono
and e.empno=v_empno;
IF vsal1<>vsal2
THEN
UPDATE emp_report SET
sal=vsal1 WHERE empno=v_empno;
END IF;
END LOOP;
CLOSE c1;
-- delete from
emp_report which not in -- emp --
DELETE FROM
emp_report
WHERE empno IN(SELECT
empno FROM emp_report
MINUS
SELECT empno FROM
emp);
END;
/
------------------------------
PARAMETRIC CURSOR:
=================
--defining cursor
with parameter
-- Parameter:
---------
-- it is also a kind
of variable
-- it is used to
*pass value to pl/sql
construct
*receive value from
pl/sql construct
-- it is defined with
pl/sql construct
-- it is not allowed
to use size
syntax:
------
<pm name>
[<mode>]<data type>
CURSOR c1(p_deptno
NUMBER) IS
SELECT empno,sal FROM
emp WHERE deptno=p_deptno;
OPEN c1(10);
DECLARE
CURSOR c1(p_deptno
NUMBER) IS
SELECT empno FROM emp
WHERE deptno=p_deptno;
v_empno NUMBER;
cnt NUMBER;
vsal1 NUMBER;
vsal2 NUMBER;
BEGIN
OPEN c1(&pdno);
LOOP
FETCH c1 INTO
v_empno;
EXIT WHEN
c1%notfound;
--check new rows and
insert
SELECT COUNT(*) INTO
cnt FROM emp_report
WHERE empno=v_empno;
IF cnt=0 THEN
INSERT INTO
emp_report
SELECT * FROM emp
WHERE empno=v_empno;
ELSE
--check old rows and
update
SELECT e.sal,r.sal
INTO vsal1,vsal2 FROM emp e,emp_report r
WHERE e.empno=r.empno
and e.empno=v_empno;
IF vsal1<>vsal2
THEN
UPDATE emp_report
SET sal=vsal1 WHERE
empno=v_empno;
END IF;
END IF;
END LOOP;
CLOSE c1;
-- delete from
emp_report which not in emp
DELETE FROM
emp_report
WHERE empno IN(SELECT
empno FROM emp_report
MINUS
SELECT empno FROM
emp);
END;
/
----------------------
FOR UPDATE with CURSOR:
----------------------
-- use to lock the
active set data where current of <cur_name>
-- used always
with(for update)
-- in this case we
not need to use any other codition to modify
the selectd rows in
active set
-- it is used for
better maintenance
-- execution is
faster
CURSOR c1 IS SELECT
hiredate FROM emp;
INSERT INTO
emp_report -- not allowed
SELECT *FROM emp
WHERE CURRENT of c1;
UPDATE emp SET
comm=100 WHERE CURRENT of c1;
DELETE FROM emp WHERE
CURRENT of c1;
IN cASe of FOR UPDATE
we use COMMIT at --2/--3
COMMIT; --1
END LOOP;
COMMIT; --2
CLOSE c1;
COMMIT; --3
END;
---------------------------------
ROWID with CURSOR --
for better performance
CURSOR c1 IS SELECT
rowid FROM emp;
v_rowid rowid;
UPDATE emp SET
comm=100 WHERE rowid=v_rowid;
--------------------------------------------
INlINe view with
CURSOR:
-----------------------
CURSOR c1 IS
SELECT
d.deptno,d.dname,v.sal
FROM dept d,(SELECT
deptno,sum(sal) sal FROM emp group by deptno) v
WHERE
d.deptno=v.deptno
-----------------------------------
Nested CURSOR:
-------------
-- generally used
when we need to process single row based on group rows
UPDATE comm for any
dept which hAS >3 employee FOR same job IN same dept
condition: job IN
('MANAGER','CLERK')
----------------------------------------
UPDATE comm FOR all
det
comm=deptno% of sal
----------------------------------------
10% 20% 30% 40%
DECLARE
CURSOR c1 IS SELECT deptno
FROM dept;
v_dno NUMBER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_dno;
EXIT WHEN
c1%notfound;
--process--
UPDATE emp SET
comm=sal*v_dno/100
WHERE deptno=v_dno;
IF sql%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_dno||'
'||sql%rowCOUNT);
ELSE
INSERT INTO track VALUES
(sysdate,user,v_dno);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(c1%rowCOUNT);
CLOSE c1;
END;
/
Implicit CURSOR:
---------------
--defined by oracle
in pl/sql block
SELECT statement
DML operation
--we use implicit
cursor attributes
sql%ISOPEN
sql%FOUND
sql%notfound
sql%rowCOUNT
note:
-- use as name for
expression/sql funtion cursor query
-- mandatory for
group funtion
CURSOR c1 IS SELECT
deptno,sum(sal) FROM emp;
v_empno NUMBER;
v_sal
-----------------------
----=============*******************
END CURSORS
******************==============----
Named pl/sql:
============
PROCEDURES:
==========
-- it IS a pl/sql
stored in data base
-- IN oracle it is
known as stored procedure
-- oracle store
procedure after compilation
USER_SOURE
-- it is used for
data process
-- it can be
invoked/called by
-- any other
procedure
-- by itself
-- function
-- package
-- Trigger
-- anonymous block
-- it accepts
parameter
-- it may or may not
return value depend how we write procedure
--by default row
returning any value
TYPE OF PROCEDURE:
-----------------
1.stand alone(out
side of package)
2.packaged(inside
package)
-- procedure having 2
parts
*specification(mandatory-->used
for any declaration)
*body(mandatory-->used
for business logic process)
syntax:
------
CREATE or REPLACE
PROCEDURE
<proc_name>(pm_lISt)
IS/AS
<variable>
<CURSOR>
BEGIN
--process--
EXCEPTION
END[<proc_name>];
EXECUTE method:
--------------
sql> exec
<proc_name>(pm_lISt);
------------------------------------
Parameter mode:
--------------
-- It define the
parameter action/working behaviour
parameter mode type:
-------------------
1.in -- read only
default
2.out -- write only
3.inout -- read/write
both
cursor accepts only
in mode parameter
parameter type:
--------------
1.formal
parameter:used to define the construct
(construct is known
as actual parameter)
2.actual
parameter:used to call/invoke the parameter which refernces
the formal parameter
CREATE or REPLACE
PROCEDURE
proc_upd(p_dno
NUMBER)
IS
DECLARE
CURSOR c1 IS SELECT
deptno FROM dept WHERE deptno=p_dno;
v_dno NUMBER;
BEGIN
--p_dno:=30; --not
allowed
OPEN c1;
LOOP
FETCH c1 INTO v_dno;
EXIT WHEN
c1%notfound;
--process--
UPDATE emp SET
comm=sal*v_dno/100 WHERE deptno=v_dno;
IF sql%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_dno||'
'||sql%rowCOUNT);
ELSE
INSERT INTO track
VALUES(sysdate,user,v_dno);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(c1%rowCOUNT);
CLOSE c1;
END proc_upd;
SQL>EXECUTE
proc_upd(10);
SQL>DECLARE
x NUMBER;
BEGIN
x:=&dno;
proc_upd(x*2);
--allowed
END;
----------------------------------------------------------------
formal parameter vs actual parameter vs parameter mode:
---------------------------------------------------------
IN parameter(read):
------------------
--such parameter can
be used for
*comparison
*expression p_dno*2
*calculation
--not used for
*assignment
p_dno:=20;
actual parameter
value:
--It may be
*literal
*variable
*expression
OUT parameter(write):
--------------------
--such parameter can
be used for
*assignment
--not used for
0......*comparison
*expression
*calculation
Actual parameter
value:
--variable allowed only
CREATE or REPLACE
PROCEDURE
proc_upd(p_dno IN
NUMBER,p_cnt OUT NUMBER)
IS
DECLARE
CURSOR c1 IS SELECT
deptno FROM deptn
WHERE deptno=p_dno;
v_dno NUMBER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_dno;
EXIT WHEN
c1%notfound;
--process--
UPDATE emp SET
comm=sal*v_dno/100;
WHERE deptno=v_dno;
p_cnt:=sql%rowCOUNT;
IFsql%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_dno||'
'||sql%rowCOUNT);
ELSE
INSERT INTO track
VALUES(sysdate,user,v_dno);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(c1%rowCOUNT);
CLOSE c1;
END proc_upd;
/
DECLARE
x NUMBER;
BEGIN
proc_upd(10,x);
END;
-----------------------------------------
INOUT parameter(read/write):
---------------------------
--such parameter can
be used for
*comparison
*expression
*calculation
*assignment
Actual parameter
value:
--only varible
allowed
CREATE or REPLACE
PROCEDURE
proc_upd(p_dno IN
NUMBER,p_cnt OUT NUMBER)
IS
DECLARE
CURSOR c1 IS SELECT
deptno FROM dept WHERE deptno=p_dno;
v_dno NUMBER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_dno;
EXIT WHEN
c1%notfound;
--process--
UPDATE emp SET
comm=sal*v_dno/100
WHERE deptno=v_dno;
p_cnt:=sql%rowCOUNT;
IF sql%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_dno||'
'||sql%rowCOUNT);
ELSE
INSERT INTO track VALUES(sysdate,user,v_dno);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(c1%rowCOUNT);
CLOSE c1;
END proc_upd;
/
DECLARE
x NUMBER;
BEGIN
x:=&dno;
proc_upd(x);
END;
proc_emp(p_dno
NUMBER,p_job VARCHAR2)
SQL>EXECUTE
proc_emp(10,'MANAGER')
SQL>EXECUTE
proc_emp('MANAGER',10) -- not allowed
Notation of
Parameter:
---------------------
--sequence between formal/actual parameter
1.positional notation
2.named notation
3.mixed notation
1.positional notation:
-------------------
--in this,we have to
follow same sequence for actual pm as formal pm
proc_emp(p_dno
NUMBER,p_job VARCHAR2)
SQL>EXECUTE
proc_emp(10,'MANAGER'); --allowed
SQL>EXECUTE
proc_emp('MANAGER',10); --not allowed
2.Named notation:
--------------
--in this case we can
change the sequence, for this we use
--name of formal
parameter
--associative
operator
proc_emp(p_dno
NUMBER,p_job VARCHAR2)
SQL>EXECUTE
proc_emp(p_dno=>10
,p_job=>'MANAGER'
);
SQL>EXECUTE
proc_emp(p_job=>'MANAGER'
,p_dno=>10
); --allowed
3.mixed notation-->(not recomended):
---------------------------------
--combination of
--positional &
named notation
proc_emp(p_dno
NUMBER,p_job VARCHAR2)
SQL>EXECUTE
proc_emp(p_dno=>10,'MANAGER');
SQL>EXECUTE
proc_emp(p_dno=10, p_job=>'MANAGER');
SQL>EXECUTE
proc_emp(p_job=>'MANAGER',p_dno=10);
---------------------------------------------------------
how to define default
value to parameter?
----------------------------------------
proc_emp(p_dno
NUMBER:=10, p_job VARCHAR2 default 'MANAGER')
by using := (or)
default
SQL>EXECUTE
proc_emp;
SQL>EXECUTE
proc_emp(20);
SQL>EXECUTE
proc_emp(30,'SALESMAN');
SQL>EXECUTE
proc_emp(p_job=>'CELRK');
--------------------------------------------------------
how to invoke
procedure in case of out/inout
at sql prompt(not
using anonymous block)
--by using bind
variable
--it is a host
variable
--uses prefix as
--can hold only 1
value at a time
syntax:
------
SQL>variable
<var_name> <data_TYPE>(size)
:x :z :y
proc_upd(10,:x)
prINt x
--------------------------------------------------------
advanced concepts of
procedure:
------------------------------
--overloading of
procedure
--recursive procedure
--forward declaration
of procedure
--mutually recursive
procedure
--------------------------------------------------------
----===============******************
END PROCEDURES
*****************==============----
FUNCTIONS:
=========
--It is used for
calculations
--It always return
the value
--It returns only 1
value at a time
--It also accepts
parameters(IN,OUT,INOUT)
--recomended to use
only in parameter
--It can be used as
*expression fn1*3
*comparison if fn1=x
--it can be used with
select (procedure not allowed)
## FUNCTION used with
select with some restriction
--FUNCTION should not
be defined with OUT and INOUT
--FUNCTION should not
be defined using DML operation
--FUNCTION IS known
as part of expression, means not a complete statement
--PROCEDURE is a
complete statement
SQL>EXECUTE
proc_upd;
SQL>EXECUTE
fn_sal; --not allowed
SQL>EXECUTE
:=:fn_sal;
advantages of
funciton:
----------------------
--easy maintanence of
program for business logic
--reuse to diffrent
program for same logic
--so we reduce the
code also
syntax of function:
------------------
CREATE or REPLACE
FUNCTION <func_name>[(pm lISt)]
RETURN <data
type>
IS/AS
BEGIN
IF x=10 THEN
RETURN(value1);
ELSIF x=20 THEN
RETURN(value2);
END IF;
--statement
EXCEPTION
END
<func_name>;
note:
----
*RETURN caluse --only
one
*RETURN statement
--may be more than 1 (but only 1 processed);
-- write a function
to calculate anual salary for any employee
CREATE or REPLACE
FUNCTION
fn_sal(p_empno
NUMBER)
RETURN NUMBER
IS
v_sal NUMBER;
BEGIN
SELECT sal*12 INTO
v_Sal
FROM emp WHERE
empno=p_empno;
RETURN(v_Sal);
END fn_Sal;
## it IS also a
database OBJECT USER_SOURCE
--how to use function
EXECUTE
:x:=fn_sal(7902);
--funtion can be
invoked/called
--procedure
--by itself
--function
--package
--trigger
--anonymous block
DECLARE
z NUMBER;
BEGIN
z:=fn_sal(&pno);
DBMS_OUTPUT.PUT_LINE(z);
END;
-----------------------------------
process comm FOR
anual sal comm=10% of sal WHEN anual sal<50000
--write procedure
--write function
use FUNCTION IN
PROCEDURE
CREATE or REPLACE
PROCEDURE
proc_comm(p_empno
NUMBER)
IS
a_sal NUMBER;
BEGIN
a_sal:=fn_sal(p_empno);
--if
fn_sal(p_empno)<50000 then
IF a_sal<50000
THEN
UPDATE emp SET
comm=sal*.10 WHERE empno=p_empno;
DBMS_OUTPUT.PUT_LINE('processed...');
END IF;
END proc_comm;
------------------------------------
--using function at
select...
DECLARE
x NUMBERl;
BEGIN
SELECT fn_sal(7902)
INTO x FROM dual;
DBMS_OUTPUT.PUT_LINE(x);
END;
------------------------------------
----===============********************
END FUNCTIONS
********************==============----
PACKAGES:
========
syntax package
specification:
----------------------------
CREATE or REPLACE
PACKAGE<pak_name>
IS/AS
<variable>
<CURSOR>
<EXCEPTION>
<PROCEDURE>
<FUNCTION>
<TYPE>
END <pkg_name>;
CREATE or REPLACE
PACKAGE pkg_emp
IS
x NUMBER;
FUNCTION
fn_sal(p_empno
NUMBER)
RETURN NUMBER;
PROCEDURE
proc_upd(p_dno
NUMBER);
END pkg_emp;
syntax of PACKAGE
Body:
----------------------
CREATE or REPLACE
PACKAGE body pkg_emp
IS
--function starts--
FUNCTION
fn_sal(p_empno NUMBER) RETURN NUMBER
IS
v_sal NUMBER;
BEGIN
SELECT sal*12 INTO
v_sal FROM emp WHERE empno=p_empno;
RETURN(v_sal);
END fn_sal;
--function over
--procedure starts--
PROCEDURE
proc_upd(p_dno NUMBER)
IS
CURSOR c1 IS SELECT
deptno FROM dept WHERE deptno=p_dno;
v_dno NUMBER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_dno;
EXIT WHEN
c1%notfound;
--process--
UPDATE emp SET
comm=sal*v_dno/100
WHERE deptno=v_dno;
IF sql%notfound THEN
DBMS_OUTPUT.PUT_LINE(v_dno||'
'||sql%rowCOUNT);
ELSE
INSERT INTO track1
VALUES(sysdate,user,v_dno);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(c1%rowCOUNT);
CLOSE c1;
END proc_upd;
--procedure over
END pkg_emp --end
package
SELECT dIStINct
TYPE,NAME FROM USER_SOURCE
WHERE NAME like
'%PKG_EMP%'
/
using pkg
SQL>
<pkg_name>.<OBJECT_name>(parameters);
EXECUTE
:x:=pkg_emp.fn_sal(7369);
Bodiless pkg:
------------
--only specification
--No Body
--it contains
*variable
*CURSOR
*EXCEPTION
*TYPE
--it is used to
maintain the
*global
variable(known AS pkg date)
(or)
*global
CURSOR/EXCEPTION/TYPE which can be through the session
life of variable
*IN PACKAGE
--throught session
*IN
PROCEDURE/FUNCTION --temporary till execution
QTY*RATE => bASe
price
10 * 100 => 1000.00
excise calculation
@16% on bASe price
(qty*rate)*6/100
=>160
total
value=>1000+160=>1160
INSERT INTO
grn(qty,rate,..)
CREATE or REPLACE
PACKAGE pkg_exc
AS
v_exrate NUMBER:=16;
v_total NUMBER;
END pkg_exc; --
package over
CREATE or REPLACE
PROCEDURE
proc_recv(pqty*prate)*pkg_exc.v_exrate;
--process--
DBMS_OUTPUT.PUT_LINE(v_tval);
END proc_recv;
/
BEGIN
DBMS_OUTPUT.PUT_LINE(pkg_exc.v_total);
END;
/
--------------------------------------------------------------------
PROCEDURE --> for
data processing
FUNCTION --> for
calculation
PACKAGE --> for
hiding the business logic
TRIGGERs --> for
implementing complex business logic to database
--------------------------------------------------------------------
----===============*****************
END PACKAGES
*****************==============----
TRIGGERS:
========
--it is used to
implement complex business logic to database.
--automate the column
value
--it is also a
database OBJECT USER_TRIGGERS
--it is just like a
procedure which invoked/called implicitly agianst event
associated to the
trigger
-->diffrence
betwee PROCEDURE and TRIGGER?
TRIGGER PROCEDURE
------- ---------
implicitly
invoked/called | explicitly invoked/called
|
cant be parametarized
nested | yes
|
TCL(commit,rollback)
not allowed | yes
--allowed IN 8i |
--by using PRAGMA(?)
|
|
user_triggers |
user_source
|
used for business
logic implementation | for data processing
------------------------------------------------------------------------------
component of
triggers:
---------------------
1.triggering event
2.restriction
3.action
1.triggering event:
----------------
--user level
events(DDL) --DBA 9i
CREATE/alter/drop
(scott)
--schema events(DML)
--programmer
INSERT/UPDATE/DELETE
--database events
--DBA 8i
startup/shutdown'logon/logoff'
servererror
2.restriction:
-----------
WHEN clause
3.action:
------
what tASk hAS to be
done by TRIGGER
--pl/sql code(trg
body)
trigger level:
-------------
1.table level
--default fire only 1 time for all rows
2.row level --fire
FOR EACH ROW keyword used FOR EACH ROW
-----------------------------------------------------------------
--events
--timing
--level
-----------------------------------------------------------------
TRIGGER can be
defined on
TABLE(DML)
View (DML) --only
insted of
User (DDL)
database(startup,..)
syntax:
------
CREATE or REPLACE
TRIGGER<trg_name>
BEFORE/AFTER
INSERT/DELETE/UPDATE [of col_name]
on <tabl_name>
[FOR EACH ROW]
[WHEN
<condition>]
[DECLARE]
<variable>
<CURSOR>
BEGIN
.......
.......
EXCEPTION
.......
.......
END
<trggr_name>;
CREATE or REPLACE
TRIGGER<trg_name>
BEFORE/AFTER
INSERT/DELETE/UPDATE [of col_name]
on <TABLE_name>
BEGIN
.......
.......
END
<trggr_name>;
example:
-------
capture
username,date,oprTYPE IN track TABLE
--table level
trigger--
CREATE or REPLACE
TRIGGER trg_del
BEFORE DELETE on emp
FOR EACH ROW
BEGIN
INSERT INTO track
VALUES(sysdate,user,'DELETE');
END trg_del;
example 2:
---------
capture deleted
employees data in emp_del table
CREATE or REPLACE
TRIGGER trg_empdel
BEFORE DELETE on emp
FOR EACH ROW
WHEN
(old.sal>1500)
BEGIN
INSERT INTO emp_del
VALUES(:old.empno
,:old.ename
,:old.job
,:old.mgr
,:old.hiredate
,:old.sal
,:old.comm,:old.deptno);
END trg_empdel;
TRG qualifire(pseudo
column) --used only for trigger
OLD NEW
--- ---
DELETE INSERT
UPDATE UPDATE
note:
----
-- : Is not used with
qualifire in case of when clause
-- old/new not
allowed to table level trigger
----------------------------------------------------------
avanced concept of
triggers:
---------------------------
what is mutating
error?
compilation of
trigger?
trigger status
cascading of trigger
how to write trigger
on view?
what is predicate of
trigger/how to use?
-----------------------------------------------------------
CREATE or REPLACE
TRIGGER trg_ins BEFORE INSERT
on emp FOR EACH ROW
delcare
vsa NUMBER;
BEGIN
SELECT sum(sal) INTO
vsal
FROM emp;
DBMS_OUTPUT.PUT_LINE(vsal);
END trg_INs;
/
TRIGGER CREATED.
CREATE or REPLACE
TRIGGER trg_ins
AFTER INSERT on emp
FOR EACH ROW
DECLARE
vsa NUMBER;
BEGIN
vsa:=pkg_ins.v_sal+:new.sal;
DBMS_OUTPUT.PUT_LINE(vsal);
END trg_INs;
error at line 1:
ora-04091: table
scott.emp is mutating, trigger/function may not see it
ora-06512: at
"SCOTT.TRG_INS", line 4
ora-04088: error
during execution of trigger 'SCOTT.TRG_INS'
-------------------------------------------------------------
--> what is
mutating table/error?
-- the triggering
table is a mutating table
-- DDL/DML operation
not allowed on mutating table
-- due to mutating
table we get mutating error in case of
DDL/DML operation on
mutating table
-- the triggering
table not always behave as mutating table
* mutating error
raised in case of
TimINg AFTER &
Level FOR EACH ROW
AFTER & TABLE
Level --no mutating error
BEFORE & TABLE
Level --no mutating error
BEFORE & FOR EACH
ROW --no mutating error
## the currently
modified table is a mutating table
--> How to avoid
mutating error?
*BEFORE/FOR EACH
ROW(user pkg variable)
*AFTER/TABLE level
trg(user pkg variable)
CREATE pkg with
variable v_sal
CREATE trg(BEFORE/FOR
EACH ROW)
event: INSERT
TABLE=emp
trg tASk: ASsign
sum(sal to v_sal)
CREATE trg(AFTER/FOR
EACH ROW)
event: INSERT
TABLE: emp
tASk: read value FROM
v_sal and add with new sal to dISplay total sal
------------------------------------------------------------------------
--> cascading of
trigger?
How to write TRIGGER
on View?
what is predicate of
TRIGGER/how to use
compilation of
TRIGGER/other OBJECT:
-----------------------------------
SQL> alter TRIGGER
<TRIGGER_name> compile;
SQL> alter
PROCEDURE <proc_name> compile;
SQL> alter
FUNCTION <FUNCTION_name> compile;
SQL> alter PACKAGE
<pkg_name> compile;
SQL> alter PACKAGE
<pkg_name> compile body;
pl/sql OBJECT
depENDency USER_DEPENDENCIES
standard -->
support pl/sql block for sql function name as prefix is not
needed in case of
standard
TRIGGER Status:
--------------
ENABLE --default
DISABLE
--due to trg DML
operation become slow fro huge data dump we make them enable
SQL> alter TRIGGER
<TRIGGER_name> enable,dISable;
INSTEAD OF -->
uesd to modify the complex view
*only row level
trigger allowed
*use old/new
qualifire
*can use predicate
*DML operation
separate for each table
-------------------------------------------------------------------------------
CREATE or REPLACE
TRIGGER<trg_name>
INstead of
INSERT/UPDATE/DELETE
on <View>
FOR EACH ROW
[WHEN <cond>]
DECLARE
<var>
<cur>
BEGIN
INSERT INTO
<tbl1> VALUES(....);
INSERT INTO
<tbl2> VALUES(....);
--As above for
update/delete
EXCEPTION
.......
.......
END <trg_name>;
CREATE or REPLACE
TRIGGERtrg_empdept
INstead of UPDATE
on empdept
FOR EACH ROW
BEGIN
UPDATE emp SET
job=:new.job
WHERE
empno=:old.empno;
UPDATE dept SET
dname=:new.dname
WHERE
dname=:old.dname;
END trg_empdept;
SQL> UPDATE
empdept SET job='PROJECT',dname='LAB'
WHERE empno=7902;
----------------------------------------------------------
-->
proc/func/pkg/triggr
advanced concepts of
procedure/function:
---------------------------------------
*over loading of
procedure
*recursive procedure
*forward declaration
of procedure
*mutually recursive
procedure
----------------------------------------------------------
--> over loading
of procedure:
-------------------------
--oracle permit to
define more than 1 procedures with same name
only in pkg with
different parameter.
different parameters:
--------------------
*sequence of
parameter is different
*no of parameter is
different
*data type is
different (different family)
proc1(p_job
VARCHAR2,p_dno NUMBER)
proc1(p_dno
NUMBER,p_job VARCHAR2)
proc1(p_dno NUMBER)
proc1(p_job
VARCHAR2,p_dno VARCHAR2) -- work
Recursive PROCEDURE:
-------------------
--a procedure calling
itself
CREATE or REPLACE
PROCEDURE proc1
AS
BEGIN
IF x=10 THEN
proc1;
END IF;
END proc1;
FORward declaration
of PROCEDURE:
--------------------------------
--calling a procedure
before defining that procedure
CREATE or REPLACE
PROCEDURE proc_cal
AS
PROCEDURE proc1;
--definition
PROCEDURE proc2
--complete prog
AS
BEGIN
proc1; --calling
proc1
END proc2;
PROCEDURE proc1
--complete proc1
AS
BEGIN
END proc1;
BEGIN
proc2; --calling
proc2
END proc_call;
EXECUTE proc_call;
Mutually Recursive
PROCEDURE:
----------------------------
--when 2
procedure/func calling each other known as mutually recursive call
pending.
----===========**************************
END TRIGGERs
************************============----
EXCEPTION:
=========
-- errors raised in
execution area is known as exception.
-- If any exception
raised in execution area controls goes to exception area of
pl/sql block.
-- if
exception(error) not handled in exception area the whole pl/sql block is
terminated with
failure
-- we are not allowed
to write any pl/sql program with out exception
EXCEPTION TYPE:
--------------
1. pre-defined
exception -- raised by oracle
2. non pre-defined
exception -- raised by oracle
3. user-defined
exception -- raised by user
# we handle the
exception using corresponding -- exception handler
# we can use many
handler togather
# only 1 handler is
processed
1. pre-defined
exception (raised by oracle):
----------------------------------------
-- oracle provides
handlers for few errors
-- these are total 19
handlers +1
no_data_found
too_many_rows
invalid_cursor
value_error
dup_val_on_index
.........
.........
others -- can handle
any type of error
-- it should be the
last handler
=> How to use
handler?
WHEN
<handler_name> THEN
SQL statement; or
PL/SQL block
WHEN
<handler_name2> THEN
SQL statement; or
PL/SQL block
WHEN
<handler_name1> or <handler_name2> THEN
SQL statement; or
PL/SQL block
DECLARE
v_Sal NUMBER;
BEGIN
SELECT sal INTO v_Sal
FROM emp WHERE
deptno=&dno;
EXCEPTION
WHEN no_data_found
THEN
DBMS_OUTPUT.PUT_LINE('invalid
condition..');
WHEN too_many_rows
THEN
DBMS_OUTPUT.PUT_LINE('more
rows FETCHed..');
END
/
DECLARE
v_sal NUMBER;
BEGIN
SELECT sal INTO v_Sal
FROM emp
WHERE
deptno=&dno;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE('error
raised');
END;
2. non pre-defined
exception (raised by oracle):
--------------------------------------------
-- many oracle error
not having pre-defined (exception handler)
-- such error are
handled by using user-defined exception for those errors
Step 1:
------
-- define exception
for oracle error
<error_name>
EXCEPTION;
Step 2:
------
-- associate
<error_name> / exception to oracle error
How?
by using PRAGMA
PRAGMA: used in
declaration area
-- it is an oracle
directive (pseudo instruction) to pl/sql compiler to do some
specific task.
-- oracle provides 4
pragmas
EXCEPTION_INIT -->
used to associate the user-defined
AUTONOMOUS -->
used to make child transaction independent(autonomous)
to use TCL, can be
used for procecures, functions,
triggers
RESTRICT_REFERENCES
--> not in use after 8i, automatically done, used with
function with pkg.
SERIALLY_REUSABLE
--> used in pkg, to restrict to use pkg data globally.
-2292
EXCEPTION_INIT
pragma
EXCEPTION_INit(<error_name>, <ora err no>)
Step 3:
------
handle error
WHEN
<error_name> THEN
SQL statement:
-------------
DECLARE
intg_err EXCEPTION;
-- step 1
pragma
EXCEPTION_INit(intg_err, -2292); -- step 2
BEGIN
DELETE FROM dept
WHERE deptno=&dno;
EXCEPTION
WHEN intg_err THEN
DBMS_OUTPUT.PUT_LINE('deptno
having child rows..');
END;
--------------------------------
error reportINg
FUNCTION
SQLCODE -- return
error code
SQLERRM -- return err
message
both are used with
the handler
OTHERS -- not used in
DML operation directly
DECLARE
VSQLCD NUMBER;
VMAG VARCHAR2(1000);
v_sal NUMBER;
v_empno NUMBER;
err_Sal EXCEPTION; --
step 1
BEGIN
SELECT empno,sal INTO
v_empno, v_Sal FROM emp
WHERE v_Sal>2000
THEN UPDATE emp SET comm=sal*.10
WHERE empno=v_empno;
DBMS_OUTPUT.PUT_LINE(v_Sal||'
'||'processed...');
ELSE
raISe err_Sal; --
step 2
ENDIF;
-- INSERT
-- UPDATE
EXCEPTION
WHEN err_sal THEN
DBMS_OUTPUT.PUT_LINE(v_sal
||' '||'less sal to process...');
WHEN others THEN
VSQLCD:=SQLCODE;
VMSG:=SQLERRM;
INSERT INTO ERR_MSG
VALUES(SYSDATE, USER, 'PROCA', VSQLCD, VMSG);
DBMS_OUTPUT.PUT_LINE('error
raised');
END;
/
error code
descreption
----------
-----------
100 no data found
-ve for any oracle
error
1 user-defined error
0 no any error
NEXT CLASS
RAISE_APPLICATION_ERROR
-- used to stop the
execution and return then message to caller
-- can be used in
1. excecution area
2.exception area
How to use?
RAISE_APPLICATION_ERROR(-ve
num, 'message');
-ve NUMBER allowed
-20000 to -20999
DECLARE
v_sal NUMBER;
BEGIN
SELECT sal INTO v_Sal
FROM emp WHERE empno=&&Pno;
IF v_sal<2000 THEN
UPDATE emp SET
comm=sal*.10
WHERE
empno=&&Pno;
ELSE
RAISE_APPLICATION_ERROR(-20006,
'no process');
END it
-- UPDATE
-- INSERT
EXCEPTION
WHEN no_data_found
THEN
RAISE_APPLICATION_ERROR(-20006,
'invalid condition');
END;
3. user-defined
exception (raised by user):
---------------------------------------
-- know as
conditional error based on bl
Step 1;
------
-- define exception
<error_name>
EXCEPTION;
Step 2;
------
-- raise error
raISe <error_name>;
Step 3;
------
-- handle error
WHEN
<error_name> THEN
SQL statement;
-- process comm for
empno=7902
only WHEN sal>2000
ELSE
EXIT (dont process
othe BL)
DECLARE
v_Sal numeber;
v_empno NUMBER;
err_sal EXCEPTION; --
step 1
BEGIN
SELECT empno,sal INTO
v_empno, v_sal FROM emp
WHERE empno=&Pno;
IF v_sal>2000 THEN
UPDATE emp SET
comm=sal*.10
WHERE empno=v_empno;
DBMS_OUTPUT.PUT_LINE(v_sal||'
'||' processed...');
ELSE
raISe err_sal;
END IF;
-- INSERT
-- UPDATE
EXCEPTION
WHEN err_Sal THEN
DBMS_OUTPUT.PUT_LINE(v_Sal||'
'||'less sal to process...');
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'
'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('error
raised');
END;
/
----===============******************
END EXCEPTIONS
*****************==============----
PL/SQL Advanced Topics:
======================
1.
AUTONOMOUS_TRANSACTION
2. RECORD
3. PL/SQL TABLE
4. REF CURSOR
5. BULK COLLECT
6. DYNAMIC SQL
1.
AUTONOMOUS_TRANSACTION:
------------------------
CREATE or REPLACE
TRIGGER TRG_EMPDEL
AFTER or DELETE on
emp FOR EACH ROW
DECLARE
PRAGMA
AUTONOMUOUS_TRANSACTION; -- 8i
BEGIN
INSERT INTO
emp_del(empno,ename,deptno) VALUES (:OLD.EMPNO,:OLD.ENAME,:OLD.DEPTNO);
COMMIT;
END TRG_EMPDEL:
-----------------------------------------------------------------------------------
2. RECORD:
------
vrec emp%ROWTYPE;
-- composeite data
type
-- user defined data
type
-- can be used in
pl/sql block
-- not used for
column of table
-- only for variable
RECORD
PL/SQL TABLE
RECORD:
------
-- user-defined data
type
-- used in place of
%rowtype for few columns
=> How to define
& use?
syntax:
------
Step 1:
------
TYPE <typ name>
IS RECORD(<col_name> <datatype> (size),
<col_name>
<tbl_name>%TYPE,
...........................
);
Step 2:
------
-- define variable
based on type
vrec <typ
name>;
example:
-------
DECLARE
TYPE rectype IS
RECORD(empno NUMBER(4),
ename emp.ename%TYPE,
job emp.job%TYPE); --
step1
vrec rectype; -- step
2
BEGIN
SELECT
empno,ename,job INTO vrec FROM emp WHERE empno=&Pno6;
---- process ----
DBMS_OUTPUT.PUT_LINE(vrec.empno||'
'||vrec.name||' '||vrec.job);
END;
/
-----------------------------------------------------------------------------------
3. PL/SQL TABLE:
------------
-- known as
associative array in ora 9i
-- also known as
index table
-- it is used as
user-defined data type
-- work as table
-- it has only 1
column
-- using record type
we can have more than 1 column
-- column not named
-- any datatype can
be used for column
-- pl/sql table
having 1 index column also
-- this column also
not having name
-- datatype of index
column
BINARY_INTEGER
pls_INTEGER -- 9i
-- we load data in
pl/sql table by using
CURSOR
-- it is defined in 2
steps
Step 1:
------
-- define pl/sql type
TYPE <typ name>
IS TABLE of <data type> (size)
INDEX BY
BINARY_INTEGER;
Step 2:
------
-- define the
variable based on pl/sql type
vtab
<typ_name>;
example:
-------
DECLARE
TYPE tabtype IS TABLE
OF NUMBER(4) -- step 1
INDEX BY
BINARY_INTEGER;
vtab tabtype; -- step
2
j INTEGER:=1;
BEGIN
-- loading data into
vtab --
FOR vrec IN (SELECT
empno FROM emp)
LOOP
vtab(j) :=vrec.empno;
j:=j+1;
END LOOP;
-- loading is over --
FOR j IN
1..vtab.COUNT
LOOP
-- process using vtab
--
IF vtab(j)=7902 THEN
UPDATE emp SET
comm=sal*.10 WHERE empno=vtab(j);
DBMS_OUTPUT.PUT_LINE('processed...'||'
'||vtab(j));
END IF;
DBMS_OUTPUT.PUT_LINE(vtab(j));
END LOOP;
END;
/
CREATE or REPLACE
PACKAGE pkg_tab
AS
TYPE tabtype IS TABLE
OF NUMBER(4) -- step 1
INDEX BY
BINARY_INTEGER;
vtab tabtype; -- step
2
END pkg_tab;
--------------------------
PL/SQL TABLE
attributes which can be used for any collection
COUNT VTAB.COUNT
EXTEND VTAB.EXTEND(1)
FIRST VTAB.FIRST
NEXT VTAB(j).NEXT
LAST VTAB.LAST
PRIOR VTAB(j).PRIOR
DELETE VTAB(j).DELETE
TRIM VTAB.TRIM(j)
EXISTS VTAB(j).EXISTS
-----------------------------------------------------------------------------------
4. REF CURSOR:
----------
-- explicit cursor is
a static cursor
-- it reads only 1
memory location at run time
-- ref cursor is
known as a dynamic cursor
-- it is defined using
ref variable
-- ref variable is a
composite type to define the ref cursor
-- ref cursor work as
pointer in c-languages
-- it can reads more
than 1 location in memory
REF CURSOR TYPE:
---------------
1. WEAK REF CURSOR:
---------------
-- also known as
unconstrained ref cursor
-- defined without
return type
2. STRONG REF CURSOR:
-----------------
-- also known as
constrained ref cursor
-- defined with
return type
%TYPE -- not allowed
%ROWTYPE -- allowed
RECORD TYPE --
allowed
-- it is also using
cursor keywords
OPEN,FETCH,EXIT,CLOSE
-- it is defined in 2
steps
Step 1:
------
-- define type of ref
cursor
TYPE <typ name>
IS REF CURSOR; -- weak ref cursor
or
TYPE <typ name>
IS REF CURSOR RETURN <tb1_name>%ROWTYPE; -- strong ref cursor
Step 2:
------
<ref var>
<typ name>;
=> How to OPEN REF
CURSOR?
OPEN <ref_var>
FOR query;
example:
-------
DECLARE
v_dno NUMBER;
TYPE ref_c IS REF
CURSOR RETURN emp%ROWTYPE;
c1 ref_c;
vrec emp%ROWTYPE;
BEGIN
v_dno:=&dno;
IF v_dno=10 THEN
OPEN c1 FOR SELECT *
FROM emp WHERE deptno=10;
els IF v_dno=20 THEN
OPEN c1 FOR SELECT *
FROM emp WHERE job='MANAGER';
els IF v_dno=30 THEN
OPEN c1 FOR SELECT *
FROM emp WHERE sal>=3000;
END IF;
LOOP
FETCH c1 INTO vrec;
EXIT WHEN
c1%notfound;
-- process --
DBMS_OUTPUT.PUT_LINE(vrec.deptno||'
'||vrec.ename||' '||vrec.job||' '||vrec.sal);
END LOOP;
CLOSE c1;
END;
/
-----------------------------------------------------------------------------------
5. BULK COLLECT:
------------
-- used for DDL
operation
-- it is used for
better performance
-- used to fetch more
than 1 row at a time
-- can be used with
cursor (or) without cursor
Syntax:
------
SELECT
<col1>,<col2> BULK COLLECT INTO <collection
var1>,<collection var2>
FROM <tbl_name>
WHERE
<condition>;
ex 1:
----
DECLARE
TYPE tab_ty IS TABLE
OF NUMBER(4)
INDEX BY BINARY
INTEGER;
vtab tab_ty;
BEGIN
SELECT empno BULK
COLLECT INTO vtab FROM emp;
FOR j IN
1..vtab.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(vtab(j));
END LOOP;
END;
=> FORALL:
------
-- not used loop/end
loop
-- used for DML
operation with bulk collect
ex 1:
----
FORALL j IN
1..vtab.COUNT
UPDATE emp SET
comm=sal*.10
WHERE empno=vtab(j);
ex 2:
----
DECLARE
TYPE tab_ty IS TABLE
OF NUMBER(4)
INDEX BY
BINARY_INTEGER;
vtab tab_ty;
BEGIN
SELECT empno BULK
COLLECT INTO vtab FROM emp;
FORALL j IN 1..vtab.COUNT
UPDATE emp SET
comm=sal*.10
WHERE empno=vtab(j);
/
-----------------------
ex 1:
----
DECLARE
CURSOR C1 IS SELECT
EMPNO FROM EMP;
TYPE tab_ty IS TABLE
OF NUMBER(4)
INDEX BY
BINARY_INTEGER;
vtab tab_ty;
BEGIN
OPEN C1;
LOOP
FETCH C1 BULK COLLECT
INTO VTAB LIMIT 1000;
..........
END;
-----------------------------------------------------------------------------------
6. DYNAMIC SQL:
-----------
-- it is used to use
DDL in pl/sql block
-- can be used for
DDL pl/sql block
-- sql statement
constructed at run time
-- for dynamic sql we
use
DBMS_SQL -- pkg
-- not easy to use --
CREATE/ALTER/DROP
INSERT/UPDATE/DELETE
SELECT
PL/SQL block
(or)
EXECUTE IMMEDIATE --
procedure
CREATE/ALTER/DROP
INSERT/UPDATE/DELETE
SELECT
PL/SQL block
example(FOR
DBMS_SQL):
---------------------
-- creating table in
pl/sql block
DECLARE
curid NUMBER;
feedbk NUMBER;
VAR VARCHAR(2000);
BEGIN
VAR:=
curid:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(CURID,'CREATE
TABLE DBMSTAB(ID NUMBER,NAME
VARCHAR2(10))',DBMS_SQL.NATIVE);
FEEDBK :=DBMS_SQL.EXECUTE(CURID);
DBMS_SQL.CLOSE_CURSOR(curid);
DBMS_OUTPUT.PUT_LINE('TABLE
CREATEd...');
END;
/
EXECUTE IMMEDIATE
-- example for drop
table
DECLARE
VAR VARCHAR2(2000);
BEGIN
VAR :='DROP TABLE
DBMSTAB'
EXECUTE IMMEDIATE
VAR;
DBMS_OUTPUT.PUT_LINE('TABLE
dropped...');
END;
-- how to use select
with execute immediate
DECLARE
VAR VARCHAR2(2000);
vsa NUMBER;
BEGIN
VAR :='SELECT sal
FROM emp WHERE empno=7902';
EXECUTE IMMEDIATE VAR
INTO vsal;
DBMS_OUTPUT.PUT_LINE(vsal);
END;
----===============*****************
END PL/SQL Advanced Topics
******************==============----
OOPS in oracle db (ORDBMS):
--------------------------
-- maintaining the
same standard
-- increasing the
productivity
-- easy maintenance
-- re-usability
OBJECT ON ORACLE:
----------------
-- when we store data
& access method that is known as object.
-- next class:
* OBJECT
* collection
* PL/SQL TABLE
* VARRAY
* NESTED TABLE
--> diffrence b/w
varry and nested table
VARRAY NESTED TABLE
------ ------------
1.Bounded 1.Unbounded
2.ordered 2.unorderd
3.DELETE not allowed
3.DELETE allowed
4.INLINE database
4.OFF-LINE database
----------------------------------------------------
# USER-defined data
type:
PL/SQL TABLE -- used
only for variable
RECORD -- used only
for variable
OBJECT TYPE -- both
(column/variable)
VARRAY TYPE -- both
(column/variable)
NESTED TABLE -- both
(column/variable)
=> How to used to
OBJECT:
---------------------
step1:
-----
-- define object type
step2;
-----
-- define column
based on object type
Syntax:
------
CREATE TYPE
<TYPE_name> AS OBJECT
(
col dataTYPE(size);
col dataTYPE(size);
........
........
);
ex:
--
CREATE TYPE adds_obj
AS OBJECT
(
plotno NUMBER,
area VARCHAR2(10),
apt VARCHAR2(10),
flatno NUMBER
);
USER_TYPES
CREATE TABLE ST
(
id NUMBER,
name VARCHAR2(10),
adds adds_obj);
inserting data to
OBJECT
----------------------------------------
-- by using
constructor
INSERT INTO ST
VALUES(1,'SCOTT',adds_obj(101,'SRNGR','DURGA',503));
=> How to use
SELECT on OBJECT:
---------------------------
-- use alias name for
table
SELECT
s.adds.plotno,s.adds.area,s.adds.apt,s.adds.flatno FROM ST s
WHERE id=1;
DECLARE
v_adds adds_obj;
BEGIN
SELECT adds INTO
v_adds FROM ST WHERE id=1;
DBMS_OUTPUT.PUT_LINE(v_adds.plotno||'
'||v_adds.area);
END;
UPDATE ST s SET
s.adds.area='WHITE FIELD' WHERE id=1;
DECLARE
v_adds adds_obj;
BEGIN
SELECT adds INTO
v_adds FROM ST WHERE id=1;
DBMS_OUTPUT.PUT_LINE(v_adds.plotno||'
'||v_adds.area);
v.adds.area:='bhills';
UPDATE ST SET
adds=v_adds WHERE id=1;
END;
---------------------------------------
=> How to use
VARRAY:
-----------------
Step1:
-----
-- define varray type
Step2:
-----
-- define column on
type
Syntax:
------
CREATE TYPE
<type-name> IS varray <limit> of <datatype> (size);
ex:
--
CREATE TYPE ph_va IS
varray(3) OF NUMBER(10);
USER_VARRAYS
CREATE TABLE ST2
(
id NUMBER,
name VARCHAR2(10),
phone ph_va
);
INSERT INTO ST2
VALUES(1,'WEB',PH_VA(111111,222222));
=> How to Retrieve
data from varray:
--------------------------------
-- by using special
function table
SELECT * FROM
TABLE(SELECT PHONE FROM ST2 WHERE id=1);
SELECT column_name
FROM TABLE(SELECT PHONE FROM ST2 WHERE id=1);
/
DECLARE
vph ph_va;
BEGIN
SELECT phone INTO vph
FROM ST2 WHERE id=1;
FOR j IN 1..vph.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(vph(j));
END LOOP;
END;
DECLARE
vph ph_va;
BEGIN
SELECT phone INTO vph
FROM ST2 WHERE id=1;
FOR j IN 1..vph.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(vph(j));
END LOOP;
IF vph.COUNT <>
vph.limit THEN
vph.extEND(1);
vph(vph.COUNT):=33333;
UPDATE ST2 SET phone=vph
WHERE id=1;
ELSE
DBMS_OUTPUT.PUT_LINE('no
slot free..');
END IF;
END;
/
---------------------------------------------------------------------------
NESTED TABLE:
------------
step1:
-----
-- define the object
step2:
-----
-- define the table
type based on object type
step3:
-----
-- define column of
table based on table type
Syntax:
------
CREATE TYPE dgr_obj
AS OBJECT
(
yr NUMBER,
un VARCHAR2(10),
dgr VARCHAR2(10)
);
ex:
--
CREATE TYPE dgr_tab
IS TABLE of dgr_obj;
CREATE TABLE ST3
(
id NUMBER,
name VARCHAR2(10),
dgr dgr_tab;
)
nested TABLE dgr
store AS education;
USER_NESTED_TABLES
INSERT INTO ST3
VALUES(1,'SCOTT',
(dgr_tab(1998,'JNTU','MTECH'),dgr_obj(2006,'ORACLE','OCP')));
SELECT * FROM
TABLE(SELECT dgr FROM ST3 WHERE id=1);
DECLARE
vdgr dgr_tab;
BEGIN
SELECT dgr INTO vdgr
FROM ST3 WHERE id=1;
FOR j IN
1..vdgr.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(vdgr.(j).yr||chr(9)||vdgr(j).unv||chr(9)||vdgr(j).dgr);
END LOOP;
vdgr.extEND(1,1);
vdgr(vdgr.COUNT).yr:=2008;
vdgr(vdgr.COUNT).unv:='BHU';
vdgr(vdgr.COUNT).dgr:='MBA';
UPDATE ST3 SET
dgr=vdgr WHERE id=1;
END;
----===============****************
OOPS IN oracle DB (ORDBMS)
*******************==============----
NEW COMMANDS/FUNCTIONS IN SQL:
-----------------------------
* CASE
* MERGE
* INSERT ALL
* NVL2
* NULLIF
* COALESCE
* CUBE
* ROLLUP
* ESCAPE
* CASE:
----
-- it is a
replacement of decode function
8i (SQL)
9i (PLSQL)
-- it is used in 2
format
-- easy to use
-- can use more than
1 col format 1;
ex1:(format1):
-------------
SELECT ename,job,CASE
job WHEN 'MANAGER' THEN 'M'
WHEN 'CLERK' THEN 'C'
ELSE 'OTH' END title
FROM emp;
ex2:(format2):
-------------
SELECT
ename,job,sal,CASE job WHEN 'MANAGER' THEN 1
WHEN sal=3000 THEN 2
WHEN comm IS null
THEN 3
ELSE 0 END code
FROM emp;
ex3:(format3):
-------------
SELECT * FROM (
SELECT ename,job,sal,CASE WHEN job='MANAGER' THEN 1
WHEN sal=3000 THEN 2
WHEN comm IS null
THEN 3
ELSE 0 END code
FROM emp)
WHERE code=3;
---------------------------------------
* MERGE:
-----
-- used for
insert/update (9i)
-- used for syncup
source data to destination data emp1 --> emp2
MERGE INTO emp2 A
using emp B ON(A.empno=B.empno)
WHEN MATCHED THEN
UPDATE SET
A.ename=B.ename,
A.job=B.job,
A.mgr=B.mgr,
A.hiredate=B.hiredate,
A.sal=B.sal,
A.comm=B.comm,
A.deptno=B.deptno
WHEN NON MATCHED THEN
INSERT INTO
VALUES(B.empno,B.ename,B.job,B.mgr,B.hiredate,B.sal,B.comm,B.deptno);
--------------------------------------
* INSERT ALL:
----------
No comments:
Post a Comment