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