PL/SQL Tutorial (Examples) - page 4
(go to page 1)
CURSORS
Cursor is a work area in pl/sql which is used by sql server used to store the
result of a query. Each column value is pointed using pointer. You can
independently manipulate cursor values. A bit about it's working..... suppose you
ask for a query stored in the server ... at first a cursor consisting of query
result is created in server...now the cursor is transferred to the client where
again cursor is created and hence the result is displayed......
Cursors are of 2 types: implicit and explicit.......implicit cursors are created
by oracle engine itself while explicit cursors are created by the
users......cursors are generally used in such a case when a query returns more
than one rows....normal pl/sql returning more than one rows givens error but
using cursor this limitation can be avoided....so cursors are used....
Cursor attributes
%ISOPEN == returns true if ursor is open, false otherwise
%FOUND == returns true if recod was fetched successfully, false otherwise
%NOTFOUND == returns true if record was not fetched successfully, false otherwise
%ROWCOUNT == returns number of records processed from the cursor.
Very important: Cursor can be controlled using following 3 control statements. They are Open, Fetch, Close.....open statement identifies the active set...i.e.
query returned by select statement...close statement closes the cursor...and fetch statement fetches rows into the variables...Cursors can be made into use
using cursor for loop and fetch statement...we will see the corresponding examples...
EXAMPLES
--EXAMPLE OF SQL%FOUND (IMPLICIT CURSORS)
begin
update employee set salary=salary *0.15
where emp_code = &emp_code;
if sql%found then
dbms_output.put_line('employee record modified successfully');
else
dbms_output.put_line('employee no does not exist');
end if;
end;
--EXAMPLE FOR SQL%NOTFOUND (IMPLICIT CURSORS)
begin
update employee 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;
--EXAMPLE FOR SQL%ROWCOUNT (IMPLICIT CURSORS)
declare
rows_affected char(4);
begin
update employee set salary = salary*0.15 where job='programmers';
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 programmers');
end if;
end;
Syntax of explicit cursor: Cursor cursorname is sql select statement;
Syntax of fetch : fetch cursorname into variable1, variable2...;
Syntax of close; close cursorname;
Syntax of open cursor; open cursorname;
--EXPLICIT CURSOR EG
DECLARE
CURSOR c1 is SELECT * FROM emp;
str_empno emp.empno%type;
str_ename emp.ename%type;
str_job emp.job%type;
str_mgr emp.mgr%type;
str_hiredate emp.hiredate%type;
str_sal emp.sal%type;
str_comm emp.comm%type;
str_deptno emp.deptno%type;
rno number;
BEGIN
rno := &rno;
FOR e_rec IN c1
LOOP
IF c1%rowcount = rno THEN
DBMS_OUTPUT.PUT_LINE (str_empno || ' ' || str_ename || ' ' || str_job || ' ' ||
str_mgr || ' ' || str_hiredate || ' ' || str_sal || ' ' || str_comm || ' ' ||
str_deptno);
END IF;
END LOOP;
END;
--ANOTHER EG DISPLAYING VALUE OF A TABLE
DECLARE
CURSOR c1 IS SELECT * FROM emp;
e_rec emp%rowtype;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO e_rec;
DBMS_OUTPUT.PUT_LINE('Number: ' || ' ' || e_rec.empno);
DBMS_OUTPUT.PUT_LINE('Name : ' || ' ' || e_rec.ename);
DBMS_OUTPUT.PUT_LINE('Salary: ' || ' ' || e_rec.sal);
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
END;
-- Display details of Highest 10 salary paid employee
DECLARE
CURSOR c1 IS SELECT * FROM emp ORDER BY sal DESC;
e_rec emp%rowtype;
BEGIN
FOR e_rec IN c1
LOOP
DBMS_OUTPUT.PUT_LINE('Number: ' || ' ' || e_rec.empno);
DBMS_OUTPUT.PUT_LINE('Name : ' || ' ' || e_rec.ename);
DBMS_OUTPUT.PUT_LINE('Salary: ' || ' ' || e_rec.sal);
EXIT WHEN c1%ROWCOUNT >= 10;
END LOOP;
END;
-- EXAMPLE OF CURSOR FOR LOOP
declare cursor c1 is select * from somdutt;
begin
for outvariable in c1
loop
exit when c1%notfound;
if outvariable.age < 21 then
dbms_output.put_line(outvariable.age || ' ' || outvariable.name);
end if;
end loop;
end;
--ref STRONG CURSORS
DECLARE
TYPE ecursor IS REF CURSOR RETURN emp%ROWTYPE;
ecur ecursor;
e_rec emp%ROWTYPE;
dn NUMBER;
BEGIN
dn := &deptno;
OPEN ecur FOR SELECT * FROM emp WHERE deptno = dn;
FOR e_rec IN ecur
LOOP
DBMS_OUTPUT.PUT_LINE ('Employee No : ' || e_rec.empno);
DBMS_OUTPUT.PUT_LINE ('Employee Salary: ' || e_rec.salary);
END LOOP;
END;
--REF WEAK CURSORS
DECLARE
TYPE tcursor IS REF CURSOR;
tcur tcursor;
e1 emp%ROWTYPE;
d1 dept%ROWTYPE;
tname VARCHAR2(20);
BEGIN
tname := &tablename;
IF tname = 'emp' THEN
OPEN tcur FOR SELECT * FORM emp;
DBMS_OUTPUT.PUT_LINE ('Emp table opened.');
close tcur;
DBMS_OUTPUT.PUT_LINE ('Emp table closed.');
ELSE IF tname = 'dept' THEN
OPEN tcur FOR SELECT * FROM dept;
DBMS_OUTPUT.PUT_LINE ('Dept table opened.');
close tcur;
DBMS_OUTPUT.PUT_LINE ('Emp table closed.');
ELSE
RAISE_APPLICATION_ERROR (-20004, 'Table name is wrong');
END IF;
END;
--CURSOR FOR LOOP WITH PARAMETERS
Declare
Cursor c1(Dno number) is select * from emp where deptno = dno;
begin
for empree in c1(10) loop;
dbms_output.put_line(empree.ename);
end loop;
end;
TRIGGERS
Trigger is a stored procedure which is called implicitly by oracle engine whenever a insert, update or delete statement is fired.
Advantages of database triggers:
---> Data is generated on it's own
---> Replicate table can be maintained
---> To enforce complex integrity contraints
---> To edit data modifications
---> To autoincrement a field
etc..
Syntax: Create or replace trigger --triggername-- [before/after] [insert/pdate/delete]
on --tablename-- [for each satement/ for each row] [when --condition--]
plus..begin.and exception
Triggers are of following type: before or after trigger ....and for each row and
for each statement trigger... before trigger is fired before insert/update/delete statement while after trigger is fired after
insert/update/delete statement...for each row and for each statements triggers are self explainatory..
EXAMPLE
-- A database trigger that allows changes to employee table only during the business hours(i.e. from 8 a.m to 5.00 p.m.) from monday to saturday. There is
no restriction on viewing data from the table -CREATE OR REPLACE TRIGGER Time_Check BEFORE INSERT OR UPDATE OR DELETE ON EMP BEGIN IF
TO_NUMBER(TO_CHAR(SYSDATE,'hh24')) < 10 OR TO_NUMBER(TO_CHAR(SYSDATE,'hh24')) >=
17 OR TO_CHAR(SYSDATE,'DAY') = 'SAT' OR TO_CHAR(SYSDATE,'DAY') = 'SAT' THEN
RAISE_APPLICATION_ERROR (-20004,'YOU CAN ACCESS ONLY BETWEEN 10 AM TO 5 PM ON
MONDAY TO FRIDAY ONLY.'); END IF; END; --YOU HAVE 2 TABLES WITH THE SAME
STRUCTURE. IF U DELETE A RECORD FROM ONE TABLE , IT WILL BE INSERTED IN 2ND
TABLE ED TRIGGERNAME Create or replace trigger backup after delete on emp fro
each row begin insert into emp/values (:old.ename,:old.job,:old.sal); end; save
the file.. and then sql> @ triggername --To STICK IN SAL FIELD BY TRIGGER MEANS
WHEN U ENTER GREATER THAN 5000, THEN THIS TRIGGER IS EXECUTED Create or replace
trigger check before insert on emp for each row when (New.sal > 5000); begin
raise_application_error(-20000, 'your no is greater than 5000'); end; --NO
CHANGES CAN BE DONE ON A PARTICULAR TABLE ON SUNDAY AND SATURDAY Create or
replace trigger change before on emp for each row when (to_char(sysdate,'dy') in
('SAT','SUN')) begin raise_application_error(-200001, 'u cannot enter data in
saturnday and sunday'); end; --IF U ENTER IN EMP TABLE ENAME FIELD'S DATA IN ANY
CASE IT WILL BE INSERTED IN CAPITAL LETTERS'S ONLY Create or replace trigger cap
before insert on emp for each row begin :New.ename = upper(:New.ename); end; --A
TRIGGER WHICH WILL NOT ALLOW U TO ENTER DUPLICATE VALUES IN FIELD EMPNO IN EMP
TABLE Create or replace trigger dubb before insert on emp for each row Declare
cursor c1 is select * from emp; x emp%rowtype; begin open c1; loop fetch c1 into
x; if :New.empno = x.empno then dbms_output.put_line('you entered duplicated
no'); elseif :New.empno is null then dbms_output.put_line('you empno is null');
end if; exit when c1%notfound; end loop; close c1; end;
Remember trigger can be dropped using Drop Trigger triggername ; statement...
PROCEDURES AND FUNCTIONS
procedure is a subprogram...which consists of a set of sql statement. Procedures are not very different from functions. A procedure or function is a logically
grouped set of SQL and PL/SQL statements that perform a specific task. A stored procedure or function is a named pl/sql code block that have been compiled and
stored in one of the oracle engines's system tables.
To make a procedure or function dynamic either of them can be passed parameters before execution. A procedure or function can then change the way it works
depending upon the parameters passed prior to its execution.
Procedures and function are made up of a declarative part, an executable part and an optional exception-handling part
A declaration part consists of declarations of variables. A executable part consists of the logic i.e. sql statements....and exception handling part handles
any error during run-time
The oracle engine performs the following steps to execute a procedure or function....Verifies user access, Verifies procedure or function validity and executes the
procedure or function. Some of the advantages of using procedures and functions are: security, performance, memory allocation, productivity, integrity.
Most important the difference between procedures and functions: A function must return a value back to the caller. A function can return only one value to the
calling pl/sql block. By defining multiple out parameters in a procedure, multiple values can be passed to the caller. The out variable being global by
nature, its value is accessible by any pl/sql code block including the calling pl/sql block.
Syntax for stored procedure:
CREATE OR REPLACE PROCEDURE [schema] procedure name (argument { IN, OUT, IN OUT}
data type, ..) {IS, AS}
variable declarations; constant declarations; BEGIN
pl/sql subprogram body;
EXCEPTION
exception pl/sql block;
END;
Syntax for stored function:
CREATE OR REPLACE FUNCTION [schema] functionname(argument IN data type, ..)
RETURN data type {IS, AS}
variable declarations; constant declarations; BEGIN
pl/sql subprogram body;
EXCEPTION
exception pl/sql block;
END;
The above syntax i think is self explanatory...but i will give you some details...IN : specifies that a value for the argument must be specified when
calling the procedure or function. argument : is the name of an argument to the procedure or function. parentheses can be omitted if no arguments are present.
OUT : specifies that the procedure passes a value for this argument back to its calling environment after execution. IN OUT : specifies that a value for the
argument must be specified when calling the procedure and that the procedure passes a value for this argument back to its calling environment after
execution. By default it takes IN. Data type : is the data type of an argument.
EXAMPLES
--PROCEDURE USING NO ARGUMENT..AND USING CURSOR
CREATE OR REPLACE PROCEDURE P2 IS
cursor cur1 is select * from emp;
begin
for erec in cur1
loop
dbms_output.put_line(erec.ename);
end loop;
end;
--PROCEDURE USING ARGUMENT
CREATE OR REPLACE PROCEDURE ME( X IN NUMBER) IS
BEGIN
dbms_output.put_line(x*x);
end;
sql> exec me(3);
--FUNCTION using argument
CREATE OR REPLACE FUNCTION RMT(X IN NUMBER) RETURN NUMBER IS
BEGIN
dbms_output.put_line(x*x);
--return (x*x);
end;
(make a block like this to run it.....)
begin
dbms_output.put_line(rmt(3));
end;
--CREATE A PROCEDURE THAT DELETE ROWS FROM ENQUIRY
--WHICH ARE 1 YRS BEFORE
Create or replace procedure myprocedure is begin
delete from enquiry where enquirydate <= sysdate - 1;
end;
--CREATE A PROCEDURE THAT TAKES ARGUMENT STUDENT NAME,
--AND FIND OUT FEES PAID BY THAT STUDENT
CREATE or REPLACE procedure me (namee in varchar) is
cursor c1 is select a.feespaiddate from feespaid a, enrollment b, enquiry c
where
c.enquiryno = b.enquiryno and
a.rollno = b.rollno and
c.fname = namee;
begin
for erec in c1
loop
dbms_output.put_line(erec.feespaiddate);
end loop;
end;
--SUM OF 2 Numbers
CREATE or replace procedure p1 is
Declare
a number;
b number;
c number;
Begin
a:=50;
b:=89;
c:=a+b;
dbms_output.put_line('Sum of '||a||' and '||b||' is '||c);
End;
--DELETION PROCEDURE
create or replace procedure myproc is
begin
delete from enquiry where fname='somdutt';
end;
--IN and OUT procedure example
Create or replace procedure lest ( a number, b out number) is
identify number;
begin
select ordid into identity from item where
itemid = a;
if identity < 1000 then
b := 100;
end if;
end l
--in out parameter
Create or replace procedure sample ( a in number, b in out number) is
identity number;
begin
select ordid, prodid into identity, b from item where itemid=a;
if b<600 then
b := b + 100;
end if;
end;
now procedure is called by passing parameter
declare
a number;
b number;
begin
sample(3000, b)
dbms_output.put_line(1th value of b is 11 b);
end ;
--SIMILAR EXAMPLE AS BEFORE
create or replace procedure getsal( sal1 in out number) is
begin
select sal into sal1 from emp
where empno = sal1;
end ;
now use the above in plsql block
declare
sal1 number := 7999;
begin
getsal(sal1);
dbms_output.put_line('The employee salary is' || sal1);
end ;
You can make a procedure and functions similarly.....also if u wanna drop a
function then use drop function functionname and for procedure use drop
procedure procedurename
PACKAGES
A package is an oracle object, which holds other objects within it. Objects
commonly held within a package are procedures, functions, variables, constants,
cursors and exceptions. Packages in plsql is very much similar to those packages
which we use in JAVA......yeah!! java packages holds numerous classes..right!!!...
A package has 2 parts..... package specification and package body
A package specification part consists of all sort of declaration of functions and procedures while package body consists of codings and logic of declared
functions and procedures...
EXAMPLE
--SIMPLEST EG
--specification
create or replace package pack2 is
function rmt(x in number) return number;
procedure rmt1(x in number);
end;
--body
create or replace package body pack2 is
function rmt(x in number) return number is
begin
return (x*x);
end;
procedure rmt1(x in number) is
begin
dbms_output.put_line(x*x);
end;
end;
(how to run.....)
exec packagename.procedurename
i.e.
exec pack2.rmt1(3);
As shown above u can put in complicated procedures and functions inside the package...I have just shown a simple example...you can easily modify the above
code to fit your requirement......Just try out packages which includes cursors, procedures and functions..etc..Remeber pl/sql supports overloading...i.e. you can
use the same function or procedure name in your application but with different no or type of arguments.