PL/SQL
1 : PL/SQL BLOCK
2 : CONDITIONAL STRUCTURES
Ø IF - THEN
Ø IF - THEN - ELSE
Ø IF - THEN- ELSIF
3 : USER DEFINED DATA TYPES
Ø TABLE TYPE
4 : LOOPIN STRUCTURES
Ø LOOP - EXIT -
END LOOP
Ø LOOP - EXIT WHEN
- END LOOP
Ø WHILE - LOOP - END LOOP
Ø FOR - IN - LOOP -
END LOOP
5 : CUSOR
Ø IMPLICIT CURSOR
Ø EXPLICIT CURSOR
Ø CURSOR FOR LOOP
Ø PARAMETERIZED
CURSOR
6 : PROCEDURE
7 : FUNCTION
8 : TRIGGER
9 : PACKAGE
10 : RAISE_APPLICATION_ERROR
1 : PL/SQL BLOCK
(1) Example
for BLOCK :
DECLARE
BAL NUMBER(11,2);
NO1 NUMBER(3);
DB_AMT CONSTANT NUMBER(5):=2000;
MIN_BAL CONSTANT NUMBER(5,2):=500;
BEGIN
NO1 := &NO1;
SELECT BAL INTO BAL FROM ACCOUNTS_SAM WHERE NO=NO1;
BAL:=BAL-DB_AMT;
IF BAL >= MIN_BAL THEN
UPDATE ACCOUNTS_SAM SET
BAL=BAL-DB_AMT
WHERE NO=ACCOUNTS_SAM.NO;
END IF;
END;
(2) Example
for BLOCK :
DECLARE
PI
CONSTANT NUMBER(4,2) :=3.14;
RADIUS1
NUMBER(10,4);
AREA1
NUMBER(10,4);
BEGIN
/*
INITIALISATION OF RADIUS TO 3 */
RADIUS1
:= 51.22;
/*
SET A LOOP SO THAT IT FIRES TILL THE RADIUS
VALUE REACHES 7 */
WHILE
RADIUS1<=55.22
LOOP
AREA1:=PI
* POWER(RADIUS1,2);
INSERT
INTO AREA_SAM
VALUES(RADIUS1,AREA1);
RADIUS1:=RADIUS1
+1;
END
LOOP;
END;
(3) Example
for BLOCK :
DECLARE
TYPE
TE_TABLE IS TABLE OF TE.NO%TYPE INDEX BY BINARY_INTEGER;
S TE_TABLE;
I
NUMBER;
P
NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(S.FIRST);
DBMS_OUTPUT.PUT_LINE(S.LAST);
S(-5)
:= 10;
S(-4)
:= 20;
S(1)
:= 11;
S(3)
:= 24;
S(2)
:= 1;
S(15)
:=60;
S(9)
:= 22;
S(10)
:=34;
I:=S.FIRST;
P:=I;
DBMS_OUTPUT.PUT_LINE(S.FIRST);
DBMS_OUTPUT.PUT_LINE(S.LAST);
S.DELETE(3);
I:=S.NEXT(I);
DBMS_OUTPUT.PUT_LINE(I);
I:=S.NEXT(I);
P:=S.PRIOR(I);
DBMS_OUTPUT.PUT_LINE(I||'=='||P);
I:=S.NEXT(I);
DBMS_OUTPUT.PUT_LINE(I);
I:=S.NEXT(I);
DBMS_OUTPUT.PUT_LINE(I);
I:=S.NEXT(I);
DBMS_OUTPUT.PUT_LINE(I);
I:=S.NEXT(I);
DBMS_OUTPUT.PUT_LINE(I);
I:=S.NEXT(I);
DBMS_OUTPUT.PUT_LINE(I);
I:=S.NEXT(I);
DBMS_OUTPUT.PUT_LINE(I);
I:=S.NEXT(I);
DBMS_OUTPUT.PUT_LINE(I);
I:=S.NEXT(I);
DBMS_OUTPUT.PUT_LINE(I);
IF
S.EXISTS(-3) THEN
DBMS_OUTPUT.PUT_LINE(S(-3));
END
IF;
END;
(4) Example
for BLOCK :
declare
salary emp1.sal%type;
id emp1.no%type;
begin
id:=
&id;
select
sal into salary from emp1 where no = id;
if salary
>=1000 then
update emp1
set
sal = 1500 where no = id;
else
dbms_output.put_line('salary cannot be
updated');
end
if;
end;
(5) Example
for BLOCK :
declare
cnt
number(3):= 1;
tot
number(3);
id
emp1.no%type;
salary
emp1.sal%type;
begin
select count(*) into tot from emp1;
while
cnt <= tot
loop
select sal,no into salary,id from
emp1
where
no like to_char(cnt);
if
salary >= 1500 then
update
emp1
set
sal= sal-(sal * 0.2) where no like id;
elsif salary
>= 1000 then
update emp1
set sal= sal-(sal * 0.15) where no
like id;
end if;
cnt:= cnt + 1;
end
loop;
end;
(6) Example
for BLOCK :
DECLARE
cursor
cemp IS select no,sal from emp1
where
depno like '1';
id emp1.no%type;
salary
emp1.sal%type;
BEGIN
OPEN
cemp;
IF
cemp%ISOPEN THEN
LOOP
FETCH cemp INTO id,salary;
EXIT WHEN cemp%NOTFOUND;
update emp1
set sal= salary + (salary * 0.05) where no
like id;
END LOOP;
COMMIT;
ELSE
dbms_output.put_line('UNABLE
TO OPEN CURSOR');
END IF;
END;
2 : CONDITIONAL STRUCTURES
1 : IF - THEN
(1) : Example of IF - THEN :
If var1 > 10 then
var2
:= var1 + 20;
End if;
(2) : Example of IF - THEN :
If not(var1 <= 10) then
var2
:= var1 + 20;
End if;
(3) : Example of NESTED IF - THEN :
if var1 > 10 then
if var2 < var1 then
var2
:= var1 + 20;
end
if;
end if;
Ø GUIDELINES :
(1) Each if statement is followed by its own then.
There is no semicolon (;) terminator
on the line that starts with if.
(2)Each if statement block is terminated by a
matching end if.
2 : IF - THEN - ELSE
(1) : Example of
IF - THEN - ELSE :
if var1 >
10 then
var2
:= var1 + 20;
else
var2
:= var1 + var1;
end if;
(2) : Example of
IF - THEN - ELSE :
if mycur%ISOPEN then
null;
else
open mycur;
end if;
(3) : Example of
IF - THEN - ELSE :
if var1 > 10 then
var2
:= var1 + 20;
else
if
var1 between 7 and 9 then
var2
:= 2 * var1;
else
var2
:= var1 * var1;
end
if;
end if;
Ø GUIDELINES :
(1)There can be one and only one else with every if
statement.
(2)There is no semicolon (;) terminator on the line starting with else.
3 : IF - THEN - ELSIF
(1) : Example of
IF - THEN - ELSIF :
if var1 > 10 then
var2 := var1 + 20;
elsif var1 between 7 and 9 then
var2
:= 2 * var1;
else
var2
:= var1 * var1;
end if;
(2) : Example of
IF - THEN - ELSIF :
if var1 > 10 then
var2
:= var1 + 20;
elsif var1 between 7 and 9 then
var2
:= 2 * var1;
end if;
(3) : Example of
IF - THEN - ELSIF :
if location = 'Bombay'
then
cricket_team_name
= 'Maharashtra';
elsif location = 'Rajkot' then
cricket_team_name
= 'Gujrat';
elsif location = 'Calcutta' then
cricket_team_name
= 'West Bengal';
end if;
(4) : Example of
IF - THEN - ELSIF :
if cnt >=
90 then
null;
else
insert
into tab1 values('Still in loop',cnt);
end if;
Ø GUIDELINES :
(1)
There is no matching end if with each elsif.
3 : USER DEFINED DATA TYPE
1 :TABLE TYPE
(1)Example of
TABLE type :
set serveroutput on size 100000
DECLARE
state_rec state%rowtype;
type just_names is table of
state.name%type
index
by binary_integer;
i
binary_integer := 0;
nametab
just_names;
BEGIN
FOR
state_rec IN (SELECT name FROM state)
LOOP
i
:= i+1;
nametab(i)
:= state_rec.name;
dbms_output.put_line
(nametab (i) );
END
LOOP;
END ;
4 :LOOPING STRUCTURES
(1) : LOOP - EXIT - END LOOP
(1) Example
for LOOP - EXIT - END LOOP :
DECLARE
CURSOR
c_emp IS SELECT emp_code,salary FROM employee
WHERE
dept_no = 20;
str_emp_code
employee.emp_code%type;
num_salary
employee.salary%type;
BEGIN
OPEN
c_emp;
LOOP
FETCH
c_emp INTO str_emp_code,num_salary;
IF
c_emp%FOUND THEN
UPDATE
employee SET salary = num_salary + (sum_salary * 0.5)
WHERE emp_code
= str_emp_code;
INSERT INTO
emp_raise VALUES(str_emp_code,sysdate,num_salart * 0.5);
ELSE
exit;
END IF;
END
LOOP;
COMMIT;
CLOSE c_emp;
END;
(2) : LOOP - EXIT WHEN -
END LOOP
(1) :
Example for LOOP - EXIT WHEN - END LOOP
:
DECLARE
CURSOR
scantable IS
SELECT
item_id,qty,desc FROM item_transaction;
vitemidno
item_transaction.item_id%type;
vqty
item_transaction.qty%type;
vdesc
item_transaction.desc%type;
valexists
number(1);
BEGIN
LOOP
FETCH
scantable INTO vitemidno,vqty,vdesc
EXIT
WHEN scantable%NOTFOUND;
valexists
:= f_itemidchk(vitemidno);
IF
valexists = 0 THEN
INSERT
INTO item_master(item_id,desc,bal_stock)
VALUES
(vitemidno,vdesc,vqty);
ELSIF
valexists = 1 THEN
UPDATE
item_master
SET
bal_stock = bal_stock + vqty;
WHERE
item_id = vitemid;
END
IF;
END
LOOP;
CLOSE
scantable;
COMMIT;
END;
(2) Example
for LOOP - EXIT WHEN - END LOOP
DECLARE
a
number := 100;
BEGIN
LOOP
a
:= a +25;
EXIT
WHEN a = 250;
END
LOOP;
dbms_output.put_line(to_char(a));
END;
(3) : WHILE - LOOP - END LOOP
(1) : Example
for WHILE - LOOP - END LOOP :
DECLARE
i
number := 0;
j
number := 0;
BEGIN
WHILE
i <= 100
LOOP
j
:= j+1;
i
:= i+2;
END
LOOP;
dbms_ouput.put_line(to_char(i));
END;
(4) : FOR - IN - LOOP - END LOOP
(1) : Example
for FOR - IN - LOOP - END LOOP :
FOR cnt IN 1 .. 5
LOOP
INSERT
INTO tab1 VALUES ('Still in loop',cnt);
END LOOP;
(2) : Example
for FOR - IN - LOOP - END LOOP :
BEGIN
FOR
i in 1..10
LOOP
dbms_output.put_line(to_char(i));
END
LOOP;
END;
5 : CURSOR
(1) : IMPLICIT CURSOR
(1) : Example for
SQL%FOUND
BEGIN
UPDATE
emp SET salary = salary *0.15
WHERE emp_code = &emp_code;
IF
SQL%FOUND THEN
dbms_output.put_line('Employee
Record Modified Succesfully');
ELSE
dbms_output.put_line('Employee
No. Does not Exist');
END IF;
END;
(2) : Example for
SQL%NOTFOUND
BEGIN
UPDATE
emp SET salary = salary *0.15
WHERE emp_code = &emp_code;
IF SQL%NOTFOUND THEN
dbms_output.put_line('Employee No.Does not
Exist');
ELSE
dbms_output.put_line('Employee Record Modified
Successfully');
END IF;
END;
(3) - Example
for SQL%ROWCOUNT
DECLARE
rows_affected char(4);
BEGIN
UPDATE
emp SET salary = salary * 0.25
WHERE emp_code
= '001';
rows_affected
:= to_char(sql%rowcount);
IF
SQL%ROWCOUNT > 0 THEN
dbms_output.put_line(rows_affected ||
'Employee Records Modified Successfully');
ELSE
dbms_output.put_line('There are
no Employees working as EMP_CODE = 001');
END IF;
END;
(2) : EXPLICIT CURSOR
(1) Example of
Explicit Cursor :
declare
v_rating number(3);
cursor c1 is select rating from
customers;
begin
open c1;
loop
fetch c1 into v_rating;
if v_rating =100
then v_rating :=150;
elsif v_rating =
200 then v_rating :=250;
else v_rating :=
350;
end if;
dbms_output.put_line(v_rating);
exit when c1%notfound;
end loop;
close c1;
end;
(2) : Example of cursorname%ISOPEN :
DECLARE
CURSOR c_emp IS SELECT emp_code,salary
FROM employee
WHERE
dept_no = 20;
str_emp_code
employee.emp_code%type;
num_salary
employee.salary%type;
BEGIN
OPEN c_emp;
IF
c_emp %ISOPEN THEN
LOOP
FETCH
c_emp INTO str_emp_code,num_salary;
exit
when c_emp%NOTFOUND;
UPDATE
employee SET salary = num_salary + (sum_salary * 0.5)
WHERE
emp_code = str_emp_code;
INSERT
INTO emp_raise VALUES(str_emp_code,sysdate,num_salart * 0.5);
END
LOOP;
COMMIT;
CLOSE
c_emp;
ELSE
dbms_output.put_line('Unable
to open Cursor');
END
IF;
END;
(3) : Example
for cursorname %FOUND :
DECLARE
CURSOR c_emp IS SELECT emp_code,salary
FROM employee
WHERE
dept_no = 20;
str_emp_code
employee.emp_code%type;
num_salary
employee.salary%type;
BEGIN
OPEN c_emp;
LOOP
FETCH
c_emp INTO str_emp_code,num_salary;
IF
c_emp%FOUND THEN
UPDATE employee SET salary = num_salary +
(sum_salary * 0.5)
WHERE
emp_code = str_emp_code;
INSERT INTO emp_raise
VALUES(str_emp_code,sysdate,num_salart * 0.5);
ELSE
exit;
END
IF;
END
LOOP;
COMMIT;
CLOSE
c_emp;
END;
(4) : Example
for cursorname %NOTFOUND :
DECLARE
CURSOR
c_emp IS SELECT emp_code,salary FROM employee
WHERE
dept_no = 20;
str_emp_code
employee.emp_code%type;
num_salary
employee.salary%type;
BEGIN
OPEN
c_emp;
LOOP
FETCH
c_emp INTO str_emp_code,num_salary;
exit
when c_emp%NOTFOUND;
UPDATE employee SET salary = num_salary +
(sum_salary * 0.5)
WHERE emp_code =
str_emp_code;
INSERT INTO emp_raise
VALUES(str_emp_code,sysdate,num_salart * 0.5);
END
LOOP;
COMMIT;
CLOSE
c_emp;
END;
(5) : Example
for cursorname%ROWCOUNT :
DECLARE
CURSOR c_emp IS
SELECT
emp_nm,dept_no,salary from EMP,DEPT_MASTER
WHERE
dept_master.dept_no = emp.dept_no;
ORDER
BY salary desc;
str_ename
emp.emp_nm %type;
num_dept_no
emp.dept_no%type;
num_salary
emp.salary%type;
BEGIN
OPEN c_emp;
dbms_output.Put_line('Name Department Salary');
dbms_output.Put_line('-------- ------------------ ----------');
LOOP
FETCH
c_emp INTO str_ename,num_dept_no,num_salary;
exit
when c_emp%ROWCOUNT = 10 or c_emp%NOTFOUND;
dbms_output.Put_line(str_ename|| ' ' || num_dept_no || ' ' ||
num_salary);
END
LOOP;
END;
(3) : CURSOR FOR LOOP
(1) Example of
Cursor For Loop :
DECLARE
CURSOR
c_emp IS
SELECT
emp_code,salary FROM employee WHERE dept_no = 20;
BEGIN
FOR
emp_rec in c_emp
LOOP
UPDATE
employee
SET
salary = emp_rec.salary + (emp_rec.salary * 0.5)
WHERE
emp_code = emp_rec.emp_code;
INSERT
INTO emp_raise
VALUES(emp_rec.emp_code,sysdate,emp_rec.salary
* 0.5);
END
LOOP;
COMMIT;
END;
(2) : Example
of Parameterized Cursor For Loops :
DECLARE
CURSOR c1 (deptno) IS
SELECT
* FROM emp WHERE dept_no = deptno;
BEGIN
FOR empreco in c1(10)
LOOP
DBMS_OUTPUT.PUT_LINE
(empreco.ename);
END
LOOP;
END;
(4) : PARAMETERIZED CURSORS
(1) : Example of Parameterized Cursors :
DECLARE
CURSOR
c_item_tran IS
SELECT
itemid,description,quantity FROM item_transaction;
CURSOR
c_itemchk (mast_itemid number) IS
SELECT
itemid FROM item_master WHERE itemid = mast_itemid;
itemidno
number(4);
desc
varchar2(30);
qty
number(3);
mast_ins_uptd
number(4);
BEGIN
OPEN c_item_tran;
LOOP
FETCH
c_item_tran INTO itemidno,desc,qty;
EXIT
WHEN c_item_tran%NOTFOUND;
OPEN
c_itemchk(itemidno);
FETCH
c_itemchk INTO mast_ins_uptd;
IF
c_itemchk%FOUND THEN
UPDATE
item_master
SET
bal_stock = bal_stock - quantity WHERE itemid = itemidno;
ELSE
INSERT
INTO item_master(itemid,description,bal_stock)
VALUES(itemidno,desc,qty);
END
IF;
CLOSE
c_itemchk;
END
LOOP;
CLOSE
c_item_tran;
COMMIT;
END;
6 : PROCEDURE
(1)
: Example 1 :
Ø Creating Procedure for use :
CREATE OR REPLACE PROCEDURE proc_update(vproduct_no
IN char,
vsorder_no
IN char,qty IN number) IS
total_qty_ordered number(8);
total_qty_disp number(8);
BEGIN
UPDATE
product_master
SET
qty_on_hand = qty_on_hand - qty;
WHERE
product_no = vproductno;
SELECT
sum(qty_ordered),sum(qty_disp)
INTO
total_qty_ordered,total_qty_disp
FROM
sales_order_details
WHERE
detl_order_no = vsorder_no;
IF
total_qty_ordered = total_qty_disp THEN
UPDATE
sales_order
SET
order_status = 'Fulfilled'
WHERE
order_no = vsorder_no;
ELSIF
total_qty_disp = 0 THEN
UPDATE
sales_order
SET
order_status = 'Backorder'
WHERE
order_no = vsorder_no;
ELSE
UPDATE
sales_order
SET
order_status = 'In Process'
WHERE
order_no = vsorder_no;
END
IF;
END;
Ø Calling the Procedure in a PL/SQL code block :
DECLARE
CURSOR
c_mast_check IS
SELECT
challan_no,order_no from challan_header;
vproduct_no
varchar2(6);
vsorder_no
varchar2(6);
vmast_challan
varchar2(6);
vdetl_challan
varchar2(6);
qty
number(3);
BEGIN
vproduct_no
:= '&vproduct_no';
qty
:= '&qty';
vdetl_challan
:= '&vdetl_challan';
OPEN
c_mast_check;
LOOP
FETCH
c_mast_check INTO vmast_challan,vsorder_no;
EXIT
WHEN c_mast_check %NOTFOUND;
IF
vdetl_challan = vmast_challan_details THEN
INSERT
INTO challan_details
VALUES(vdetl_challan,vproduct_no,qty);
proc_update(vproduct_no,vsorder_no,qty);
EXIT;
END
IF;
END
LOOP;
IF
c_mast_check %NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('The
given challan_no does not
have
a master record');
END
IF;
CLOSE
c_mast_check;
COMMIT;
END;
Ø Deleting a Stored Procedure
:
DROP PROCEDURE proc_update;
7 :FUNCTION
(1)
Example 1 :
Ø Creating Function for use :
CREATE FUNCTION f_itemidchk(vitemidno IN number)
RETURN number IS
dummyitem number(4);
BEGIN
SELECT
item_id INTO dummyitem FROM item_master
WHERE
item_id = vitemidno;
RETURN
1;
EXCEPTION
WHEN
NO_DATA_FOUND THEN
RETURN
0;
END;
Ø Calling the Function f_itemidchk in a PL/SQL code block:
DECLARE
CURSOR
scantable IS
SELECT
item_id,qty,desc FROM item_transaction;
vitemidno
item_transaction.item_id%type;
vqty
item_transaction.qty%type;
vdesc
item_transaction.desc%type;
valexists
number(1);
BEGIN
LOOP
FETCH
scantable INTO vitemidno,vqty,vdesc
EXIT
WHEN scantable%NOTFOUND;
valexists
:= f_itemidchk(vitemidno);
IF
valexists = 0 THEN
INSERT
INTO item_master(item_id,desc,bal_stock)
VALUES
(vitemidno,vdesc,vqty);
ELSIF
valexists = 1 THEN
UPDATE
item_master
SET
bal_stock = bal_stock + vqty;
WHERE
item_id = vitemid;
END
IF;
END
LOOP;
CLOSE
scantable;
COMMIT;
END;
Ø Deleting a Stored Functions :
DROP FUNCTION f_itemidchk;
8 : TRIGGER
(1) - Example
: 1
Ø Create a Trigger :
CREATE TRIGGER audit_trail
AFTER
UPDATE OR DELETE ON client_master
FOR
EACH ROW
DECLARE
oper
varchar2(8);
client_no
varchar2(6);
name
varchar2(20);
bal_due
number(10,2);
BEGIN
IF
updating THEN
oper
:= 'update';
END
IF;
IF
deleting THEN
oper
:= 'delete';
END
IF;
client_no
:= :old.client_no;
name
:= :old.name;
bal_due
:= :old.bal.due;
INSERT
INTO auditclient
VALUES(client_no,name,bal_due,oper,user,sysdate);
END;
(2) - Example
: 2
create or replace trigger ins_trigger
after insert on stud1 for each row
declare
begin
insert into stud1(total,per)
values((:new.s1 + :new.s2 + :new.s3
+ :new.s4 + :new.s5), :new.total / 5);
commit;
end;
(3) - Example
: 3
Ø USE OF INSERTING / UPDATING / DELETING IN A TRIGGER :
CREATE OR REPLACE TRIGGER trigl_1
AFTER
INSERT or UPDATE or DELETE ON emp
BEGIN
IF inserting THEN
DBMS_OUTPUT.PUT_LINE
(" One row is INSERTED ! ");
ELSIF
updating THEN
DBMS_OUTPUT.PUT_LINE
(" One row is UPDATED ! ");
ELSIF
deleting THEN
DBMS_OUTPUT.PUT_LINE
(" One row is DELETED ! ");
END
IF;
END;
(4) - Example
: 4
Ø You
have two tables with the Same Structure . If you DELETE a Record from one
Table(Emp), It will be INSERT into Second Table(Backup)
CREATE OR REPLACE TRIGGER backup
AFTER
delete ON emp
FOR
EACH ROW
BEGIN
INSERT
INTO emp VALUES(:old.ecode,:old.ename,:old.job,:old.salary);
END;
(5) - Example
: 5
Ø
Write a TRIGGER - capital for Table -
EMP on ename field , such that
if you entered ename into LOWER
case , it will convert into CAPITAL CASE and then stored it into the table.
CREATE OR REPLACE TRIGGER capital
BEFORE
INSERT ON emp
FOR
EACH ROW
BEGIN
:new.ename
= UPPER(:new.ename);
END:
(6) - Example
: 6
Ø Write
a TRIGGER - cons_pk on Table - emp that
do not allow any DUPLICATE or NULL VALUES in
field - empno.
OR
Ø CREATE
A TRIGGER cons_pk THAT WORKS AS A PRIMARY KEY
CREATE OR REPLACE TRIGGER cons_pri
BEFORE INSERT ON emp
FOR EACH ROW
DECLARE
CURSOR c1 IS SELECT
* FROM emp;
reco emp%rowtype;
BEGIN
OPEN
c1;
LOOP
FETCH c1 INTO reco;
IF
:new.empno = reco.empno THEN
DBMS_OUTPUT.PUT_LINE
("Your no. is DUPLICATE !!!! " );
ELSIF
:new.empno IS NULL THEN
DBMS_OUTPUT.PUT_LINE
("Your no. is NULL !!! " );
END
IF;
EXIT
WHEN c1%NOTFOUND;
END
LOOP;
END;
Ø Dropping a Trigger :
DROP
TRIGGER audit_trail;
9 : PACKAGE
(1)
Example of Package 1 :
Ø Package Specification :
CREATE OR REPLACE PACKAGE area_pack IS
FUNCTION area (r number) RETURN number;
FUNCTION pack (r number, h number) RETURN number;
END;
Ø Packager Body :
CREATE OR REPLACE PACKAGE BODY area_pack IS
--creating body of funtion : area
FUNCTION area(r number) RETURN number IS
BEGIN
RETURN (3.14 * r
* r);
END;
--creating body of funtion : pack
FUNCTION
pack (r number,h number) RETURN number IS
BEGIN
RETURN
(3.14 * r * r * h);
END;
Ø USE OF PACKAGE area_pack IN PL/SQL BLOCK :
DECLARE
a
number;
v
number;
BEGIN
a
:= area_pack.area (10);
v
:= area_pack.pack(10,4);
dbms_output.put_line(a);
dbms_output.put_line(v);
END;
10 : RAISE APPLICATION_ERROR
(1) Example :
1
CREATE or REPLACE TRIGGER trig_1
BEFORE
INSERT ON item
FOR
EACH ROW
DECLARE
item_id Item.itemid%type;
BEGIN
SELECT
itemid INTO item_id FROM item WHERE qty
= 4543;
IF
Item_id = 1000 THEN
RAISE_APPLICATION_ERROR
(-200001.,"ENTER SOME OTHER NUMBER !");
END
IF;
END;
(2)
Example : 1
Ø To
strict in salary field by Trigger(trig_valid), means WHEN you ENTER / INSERT
greater than 5000, then this trigger should be executed :
CREATE OR REPLACE TRIGGER trig_valid
CHECK
BEFORE
INSERT
ON emp
FOR
EACH ROW WHEN (:new.salary >5000)
BEGIN
RAISE_APPLICATION_ERROR(-2000,"Your
salary is greater than 5000");
END:
(3) Example :
1
Ø Write
a trigger that do not allow any updation / changes on Table - emp on SATURDAY /
SUNDAY.
CREATE OR REPLACE TRIGGER change
BEFORE ON emp
FOR
EACH ROW
WHEN
(to_char(Sysdate,"dy") IN ("SAT","SUN"))
BEGIN
RAISE_APPLICATION_ERROR(-20001."You
are not enter a data in SAT or SUN !!! ");
END;
Question
Bank – Oracle
1. Explain various field types
in Oracle.
2. Write down E.F. Codd’s 12
rules for RDBMS.
3. Explain DDL, DML & DCL
statement with example.
4. Justify Oracle as RDBMS.
5. Distinguish the followings.
1. SQL - SQL*Plus 5. Having – Group By
2. DMBS – RDBMS 6.
Group By – Order By
3. Char – Varchar 7. Primary Key –
Foreign Key
4. DML – DDL 8.
6. What is constraint? Explain
various constraints in detail.
7. Explain following operators
with example.
a. Between d. In
b. Like e. Any
c. Exists f. All
8. Write a short note on ALTER
TABLE command.
9. Explain following functions.
a. to_date( ) f. substr( ) k. avg( )
b. date_diff( ) g. instr( ) l. rpad( )
c. decode( ) h. next_day( ) m. ceil( )
d. round( ) i. greatest( ) n. count(*)
e. nvl( ) j. to_char( ) o. months_between( )
10.
Explain self-join with example.
11.
What is join? Explain various
types of joins with example.
12.
Discuss view & it’s
different types in Oracle.
13.
Explain CREATE SEQUENCE
statement with various clause.
14.
Explain INDEX syntax with
example.
15.
Write a note on GRANT and REVOKE
with example.
16.
What is transaction? Explain the
use of Rollback, Commit and Savepoint.
17.
What is Cluster? Also Explain
Snapshot in brief.
18.
Write a short note on Synonyms.
19.
Discuss PL/SQL block structure.
20.
What is Cursor? Explain explicit
& implicit cursor.
21.
Explain Memory Structure of
Oracle in detail.
22.
List Database Processes in
Oracle & explain any five.
23.
Explain Redo Log files &
Control files.
24.
What is Trigger? Explain
different types of it.
25.
Explain Exception Handling in
detail.
26.
Explain Tablespace & related
commands.
27.
Short note: Import, Export &
SQL*Loader
28.
What is Backup? Explain various
types of Backup.
29.
Write a short note on Net 8 and
it’s features.
30.
Give the difference between
followings.
1. SQL – PL/SQL 5. %NOTFOUND – NO_DATA_FOUND
2. Procedure – Function
6. Implicit – Explicit Cursor
3. Nested Table – Varray 7.
4. %TYPE - %ROWTYPE 8.
PRACTICAL EXERCISE
1. Various Queries based on
Create, Select, Update, Insert using
Group by, having, where, in, exists clause
And also use
of built in functions.
2. Create a package that
consist of one function and one procedure to check stud roll_no & to add
record in the table.
3. Create the after update row
level trigger to display the old value as well as new value from the table.
4. Write a PL/SQL block which
update employee’s salary by 0.5 of salary whose id is given at run time. And
display message if salary is update otherwise display appropriate message.
(Using implicit cursor)
5. Write a function which
returns yes or no if substing is found within the string.
6. Write a procedure which
returns total salary paid by specified department.
7. Write a trigger which stores
modified and deleted records of emp table within emp_backup table.
8. Write a PL/SQL block which
display user defined error message when duplicate data is entered within id
field through the block.
9. Write a PL/SQL block which
display employee’s detail of specified department. (Using explicit cursor)
0 comments:
Post a Comment