/* PL/SQL EXAMPLE FOR LOOP. ADDITIONAL : "REVERSE" LOOP */

SET SERVEROUTPUT ON

DECLARE CTR NUMBER DEFAULT 0;
BEGIN
FOR I IN 1..6
--FOR I IN REVERSE 1..6
LOOP
CTR:=CTR+2;
DBMS_OUTPUT.PUT_LINE('CTR : '||CTR);
--DBMS_OUTPUT.PUT_LINE('I : '||I);
END LOOP;
END;
/

/*Q : COUNT NUMBER OF DIGITS */

DECLARE
NUM INT DEFAULT '123456789';
CTR INT DEFAULT '0';
BEGIN
NUM:=NVL('&NUM',NUM); -- NVL FUNC CHECKS FOR NULL VALUE AND REPLACES IT WITH THE 2ND ARGUMENT
DBMS_OUTPUT.PUT_LINE('NUM : '|| NUM);
WHILE NUM>0
LOOP
NUM:=FLOOR(NUM/10);
CTR:=CTR+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('NO. OF DIGITS : '||CTR);
END;
/

/* PL/SQL EXAMPLES */

/* NOTE : SET SERVEROUTPUT ON (TO BE ABLE TO DISPLAY DOPL STATEMENTS) */

SET SERVEROUTPUT ON

DECLARE MARKS NUMBER DEFAULT 50;
BEGIN
MARKS:=&MARKS;
IF MARKS >= 80 AND MARKS <= 100 THEN
DBMS_OUTPUT.PUT_LINE('GRADE "A" : MARKS '||MARKS);
ELSIF MARKS >= 70 AND MARKS <= 79 THEN
DBMS_OUTPUT.PUT_LINE('GRADE "B" : MARKS '||MARKS);
ELSIF MARKS >= 60 AND MARKS <= 69 THEN
DBMS_OUTPUT.PUT_LINE('GRADE "C" : MARKS '||MARKS);
ELSIF MARKS >= 41 AND MARKS <= 59 THEN
DBMS_OUTPUT.PUT_LINE('GRADE "D" : MARKS '||MARKS);
ELSE
DBMS_OUTPUT.PUT_LINE('GRADE "FAIL" : MARKS '||MARKS);
END IF;
END;
/


/* PROCEDURE */

-- SYNTAX
CREATE PROCEDURE PRCRTRV ( parameter )
AS
BEGIN
...
END;

-- EXAMPLE (*OUT* MODE)
CREATE PROCEDURE PRCRTRV ( MYUSN NUMBER, CLGCD OUT NUMBER )
AS
BEGIN
SELECT CLG_CODE INTO CLGCD FROM STUDENT WHERE USN = MYUSN;
END;

DECLARE
USN NUMBER;
CLGCODE NUMBER;
BEGIN
USN:=707
PRCRTRV(USN, CLGCODE);
DOPL(CLGCODE);
END;

-- EXAMPLE (*IN OUT* MODE)
CREATE OR REPLACE PROCEDURE PRCRTRV(PARAMTR IN OUT NUMBER)
AS
BEGIN
SELECT CLG_CODE INTO PARAMTR FROM STUDENT WHERE USN = PARAMTR;
END;

DECLARE NUM NUMBER;
BEGIN
NUM:=707;
PRCRTRV(NUM);
DOPL(NUM);
END;


----------- DEFAULT STUDENT TABLE---------
create table student(usn number(5), s_name varchar(20), clg_code number(5), marks number(4));
insert into student values('001', 'abc', '111', '100');
insert into student values('002', 'def', '112', '99');
insert into student values('003', 'ghi', '113', '98');
insert into student values('004', 'jkl', '111', '99');



--- Differentiating Age Groups---

18-30
30-40
40-60



create table tab1(
age number
);


create table tab2(
age number
);


create table tab3(
age number
);


create or replace procedure prcInsert(Age Number)
as
begin
if Age>=10 and Age<30
then
insert into tab1 values(Age);
elsif Age>=30 and Age<40
then
insert into tab2 values(Age);
else
insert into tab3 values(Age);
end if;
end;



----------FUNCTION-----------

create or replace function converter(dollar number)
return number
as
rupees number;
begin
rupees:= dollar*64;
return rupees;
end;






------------- 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 u 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;



DECLARE CURSOR C1 IS SELECT * FROM STUDENT where usn > 3;
ABC STUDENT%ROWTYPE;
BEGIN
for abc in c1
LOOP
DBMS_OUTPUT.PUT_LINE(ABC.s_name);
end loop;
END;


DECLARE TYPE T IS
TABLE OF STUDENT%ROWTYPE;
TEST1 T;
BEGIN
SELECT * BULK COLLECT INTO TEST1 FROM STUDENT WHERE USN > 3;
DBMS_OUTPUT.PUT_LINE(TEST1.S_NAME||SQL%ROWCOUNT);
END;