CURSORS:
********
-->A cursor is a temporary work area created in the system memory when a SQL statement is executed.
-->This temporary work area is used to store the data retrived from database and manipulate the data.
-->A cursor can hold morethan one row, but it can process only pne row at a time.
-->The set of rows in the cursor holds is called the active set.
-->In order to process SQL statements oracle will allocate an area of menory known as context area.
-->Cursor is a buffer area or Context area which is used to process multiple records and also record by record process.
-->It is not stored in database.
-->It is not Re-usable.
-->There are two types of cursors.
a)Implicit Cursor
b)Explicit Cursor
Implicit Cursor:
****************
-->SQL statements returns a single record is called implicit cursor.
-->Implicit cursor operations done by the system whenever "DML" operations are performed by user.
-->Open by the system
-->Fetch or retriecve the records by the system
-->close by the system.
-->It has a fixed name "SQL".
-->It gives the status of "DML" stmts in pl/sql Block.
-->It will not support cursor operations.
-->It supports %isopen %found,%notfound,%rowcount attributes.
-->These cursors will create memory automatically to process single row queries.
-->IMPLICIT CURSOR TELLING US THE STATUS OF LAST DML COMMAND WHETHER IT IS SUCCESSFULL OR NOT.
EX:
***
--pass the empno as input and get ename
DECLARE
LV_ENAME VARCHAR2(20);
BEGIN
SELECT ENAME INTO LV_ENAME
FROM EMP
WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE(LV_ENAME);
END;
/
--Implicit Cursor
--pass the ou name and get org id(return the org id)
declare
lv_ou_name hr_operating_units.name%type;
lv_org_id hr_operating_units.organization_id%type;
begin
lv_ou_name:='Vision Operations';
--Implicit Cursor
select organization_id
into lv_org_id
from hr_operating_units
where 1=1
and name=lv_ou_name;
dbms_output.put_line('Organization Id is -->'||lv_org_id);
end;
/
--Implicit Cursor
--pass the ou name and get org id(return the org id)
declare
lv_ou_name hr_operating_units.name%type;
lv_org_id hr_operating_units.organization_id%type;
begin
lv_ou_name:='Vision Operations1';
--dbms_output.put_line('lv_ou_name is -->'||lv_ou_name);
--Implicit Cursor
select organization_id
into lv_org_id
from hr_operating_units
where 1=1
and name=lv_ou_name;
dbms_output.put_line('Organization Id is -->'||lv_org_id);
end;
/
IMPLICIT Cursor Attributes:
***************************
-->It supports %isopen, %found,%notfound,%rowcount attributes.
-->%IS OPEN is a boolean attribute it evaluates true if the cursor is open else evaluates false.
-->%FOUND is a boolean attribute it returns TRUE if DML is success.
-->%NOT FOUND is a boolean attribute it returns TRUE if DML is failure.
-->%ROWCOUNT is number it returns no.of rows manipulated by DML statement.
%FOUND
DML OPERATIONS --SUCCESS --TRUE
DML OPERATION ==FAILURE --FLASE
%NOT FOUND
DML OPERATIONS --FAILURE --TRUE
DML OPERATION --SUCCESS --FLASE
simple program:
***************
begin
update emp1 set sal = sal + 2000
where empno = 790;
dbms_output.put_line(' Employ Updated ');
end;
-->In above program we are unable to find DML stmt Status.
-->Using Implicit cursor attributes it is possible to know the status.
/
begin
update emp1 set sal = sal + 1000
where empno = 790;
if sql%Notfound then
dbms_output.put_line('Unable to Update rows - No
such empno exists');
else
dbms_output.put_line(sql%rowcount||' Employees are updated sucessfully ');
--commit;
end if;
end;
/
--SQL%ROWCOUNT and SQL%NOTFOUND:
********************************
declare
vdept number(2) := &dept;
begin
update emp1 set sal = sal + 1000
where deptno = vdept;
if sql%Notfound then
dbms_output.put_line('Unable to Update rows - No
such dept exists');
else
dbms_output.put_line(sql%rowcount||' Employees are updated sucessfully ');
--commit;
end if;
end;
/
Using Implicit cursors:
-->pl/sql block Trapping Update stmt status.
declare
vdept number(2) := &dept;
begin
delete from emp1 where deptno = vdept;
if sql%rowcount > 3 then
dbms_output.put_line(' Invalid operation - cannot remove more than 3 employees ');
rollback;
else
dbms_output.put_line(sql%rowcount||' Employees are removed');
--commit;
end if;
end;
Note: Implicit cursor holds only recently executed DML statement status but not the previous DML statement status .
--SQL%ROWCOUNT:
***************
DECLARE
LV_ENAME EMP.ENAME%TYPE;
LV_SAL EMP.SAL%TYPE;
BEGIN
SELECT ENAME,SAL
INTO LV_ENAME,LV_SAL
FROM EMP
WHERE EMPNO=7839;
DBMS_OUTPUT.PUT_LINE('Number of rows Processed : '||SQL%ROWCOUNT);
END;
/
-->pl/sql block trapping Delete stmt status.
declare
vdept number(2) := &dept;
begin
delete from emp1 where deptno = vdept;
if sql%rowcount > 3 then
dbms_output.put_line(' Invalid operation - cannot remove more than 3 employees ');
rollback;
else
dbms_output.put_line(sql%rowcount||' Employees are removed');
-- commit;
end if;
end;
/
%FOUND,%NOTFOUND,%ROWCOUNT:
***************************
DECLARE
LV_TOT_CNT NUMBER;
BEGIN
UPDATE EMP1
SET SAL=SAL+200;
--Checking Implicit cursor data using %NOTFOUND attribute
if SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('None of the emplyee salaries are not updated');
--Checking Implicit cursor data using %FOUND attribute
elsif SQL%FOUND then
--getting rowcount using %ROWCOUNT Attribute
DBMS_OUTPUT.PUT_LINE('RECORDS ARE UPDATED SUCCESSFULLY');
LV_TOT_CNT:=SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('Total number of employees are updated : '||LV_TOT_CNT);
end if;
END;
/
Explicit Cursor:
****************
-->SQL statements return a multiple records is called Explicit Cursor.
-->Explicit cursor operations are done by user or developer.
-->declare the cursor
-->open the cursor
-->fetch the records by the cursor
-->close the cursor
Cursor operations:
******************
a)declare
b)open
c)fetch
d)close
DECLARE:
********
-->In declare section of the pl/sql block we are defining cusrors using following syntax.
syntax:
*******
cursor cursorname is select <column name> from tablename where condition;
OPEN:
*****
-->whenever we are opening the cursor then only oracle server fetch data from table into cursor memory area because whenever we are opening the cursor then only select statements are executed.
syntax:
*******
open cursorname;
-->the statement is used in executable section of the pl/sql block.
FETCH:
******
-->Using fetch statement we are fetching data from cursor into pl/sql variables.
syntax:
*******
fetch cursorname into variable1,variable2,...;
CLOSE:
******
-->when we are closing the cursor all resorces aallocated from cursor memory area automatically released.
syntax:
*******
close cursorname;
EX1:
****
DECLARE
CURSOR C1 IS SELECT ENAME,SAL FROM EMP;
LV_ENAME VARCHAR2(20);
LV_SAL VARCHAR2(10);
BEGIN
OPEN C1;
FETCH C1 INTO LV_ENAME,LV_SAL;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
CLOSE C1;
END;
/
EX2:
****
cursor cursorname is select <column name> from tablename where condition;
open cursorname;
fetch cursorname into variable1,variable2,...;
close cursorname;
/
DECLARE
CURSOR C1 IS SELECT ENAME,SAL FROM EMP;
LV_ENAME EMP.ENAME%TYPE;
LV_SAL EMP.SAL%TYPE;
BEGIN
OPEN C1;
FETCH C1 INTO LV_ENAME,LV_SAL;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
FETCH C1 INTO LV_ENAME,LV_SAL;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
FETCH C1 INTO LV_ENAME,LV_SAL;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
FETCH C1 INTO LV_ENAME,LV_SAL;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
FETCH C1 INTO LV_ENAME,LV_SAL;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
FETCH C1 INTO LV_ENAME,LV_SAL;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
FETCH C1 INTO LV_ENAME,LV_SAL;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
FETCH C1 INTO LV_ENAME,LV_SAL;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
FETCH C1 INTO LV_ENAME,LV_SAL;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
FETCH C1 INTO LV_ENAME,LV_SAL;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
FETCH C1 INTO LV_ENAME,LV_SAL;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
FETCH C1 INTO LV_ENAME,LV_SAL;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
FETCH C1 INTO LV_ENAME,LV_SAL;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
FETCH C1 INTO LV_ENAME,LV_SAL;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
CLOSE C1;
END;
/
EX3:
****
--TO FETCH MULTIPLE ROWS FROM EMP TABLE BY USING LOOPING STATEMENTS ?
--INFINITE LOOP
DECLARE
CURSOR C1 IS SELECT ENAME,SAL FROM EMP;
LV_ENAME VARCHAR2(20);
LV_SAL VARCHAR2(10);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO LV_ENAME,LV_SAL;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
END LOOP;
CLOSE C1;
END;
/
EX4:
****
--TO FETCH MULTIPLE ROWS FROM EMP TABLE BY USING LOOPING STATEMENTS USING EXIT?
-- it is an infinite loop.so that we need break a loop then we are using "EXIT" statement.
DECLARE
--cursor cursorname is select <column name> from tablename where condition;
CURSOR C1 IS SELECT ENAME,SAL FROM EMP;
LV_ENAME EMP.ENAME%TYPE;
LV_SAL EMP.SAL%TYPE;
BEGIN
--open cursorname;
OPEN C1;
LOOP
--fetch cursorname into variable1,variable2,...;
FETCH C1 INTO LV_ENAME,LV_SAL;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
END LOOP;
--close cursorname;
CLOSE C1;
END;
/
declare
cursor c1 is select ename,empno from emp;
--lv_ename varchar2(20);
--lv_empno number;
lv_emp_cur c1%rowtype; --cursor variable --cursor based record
begin
open c1;
loop
--fetch c1 into lv_ename,lv_empno;
fetch c1 into lv_emp_cur;
exit when c1%notfound;
dbms_output.put_line(lv_emp_cur.ename||','||lv_emp_cur.empno);
end loop;
close c1;
end;
/
DECLARE
CURSOR C1 IS SELECT * FROM EMP1;
I C1%ROWTYPE; --cursor based record
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.EMPNO||' '||I.ENAME||' '||I.JOB||' '||I.MGR||' '||I.HIREDATE||' '||I.SAL||' '||I.COMM||' '||I.DEPTNO);
END LOOP;
CLOSE C1;
END;
/
EX5:
*****
DECLARE
CURSOR C1 IS SELECT * FROM EMP;
I EMP%ROWTYPE; --table based record
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
IF I.SAL>2000 THEN
DBMS_OUTPUT.PUT_LINE('High Sal : '||I.SAL);
END IF;
END LOOP;
CLOSE C1;
END;
/
--update the emp table data based on below conditions by using cursor.
SA>=5000 -- UPDATE 1000
SAL>2000 AND SAL<=3000 --UPDATE 500
SAL>1000 AND SAL<=2000 --UPDATE 250
SAL<1000 --UPDATE 100
/
DROP TABLE EMP1;
/
CREATE TABLE EMP1 AS SELECT * FROM EMP;
/
SELECT * FROM EMP1;
/
EX6:
****
CREATE TABLE XX_EMP
AS
SELECT *
FROM EMP
WHERE 1=2;
/
CREATE TABLE XX_DEPT
AS
SELECT *
FROM DEPT
WHERE 1=2;
/
--TO TRANSFER THE DATA FROM ONE TABLE TO ANOTHER TABLE
DECLARE
CURSOR C1 IS SELECT * FROM EMP;
I EMP%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||I.JOB||','||I.MGR||','||I.HIREDATE||','||I.SAL||','||I.COMM||','||I.DEPTNO);
INSERT INTO XX_EMP
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
)
VALUES
(
I.EMPNO,
I.ENAME,
I.JOB,
I.MGR,
I.HIREDATE,
I.SAL,
I.COMM,
I.DEPTNO);
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('TOTAL ROWS INSERTED : '||C1%ROWCOUNT);
CLOSE C1;
END;
/
SELECT * FROM XX_EMP;
/
TRUNCATE TABLE XX_EMP;
/
TRUNCATE TABLE XX_DEPT;
/
--TO TRANSFER THE DATA FROM TWO TABLES DATA TO ANOTHER TABLE
DECLARE
CURSOR C1 IS SELECT * FROM EMP;
CURSOR C2 IS SELECT * FROM DEPT;
I EMP%ROWTYPE;
J DEPT%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('*********EMP DETAILS START ************');
OPEN C1;
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||I.JOB||','||I.MGR||','||I.HIREDATE||','||I.SAL||','||I.COMM||','||I.DEPTNO);
INSERT INTO XX_EMP
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
)
VALUES
(
I.EMPNO,
I.ENAME,
I.JOB,
I.MGR,
I.HIREDATE,
I.SAL,
I.COMM,
I.DEPTNO);
END LOOP;
DBMS_OUTPUT.PUT_LINE('TOTAL NUMBER OF ROWS INSERTED : '||C1%ROWCOUNT);
COMMIT;
CLOSE C1;
--DBMS_OUTPUT.PUT_LINE('TOTAL NUMBER OF ROWS INSERTED : '||C1%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE('*********EMP DETAILS END ************');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('*********DEPT DETAILS START ************');
OPEN C2;
LOOP
FETCH C2 INTO J;
EXIT WHEN C2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(J.DEPTNO||','||J.DNAME||','||J.LOC);
INSERT INTO XX_DEPT
(
DEPTNO,
DNAME,
LOC
)
VALUES
(
J.DEPTNO,
J.DNAME,
J.LOC);
END LOOP;
DBMS_OUTPUT.PUT_LINE('TOTAL NUMBER OF ROWS INSERTED : '||C2%ROWCOUNT);
CLOSE C2;
COMMIT;
--DBMS_OUTPUT.PUT_LINE('TOTAL NUMBER OF ROWS INSERTED : '||C2%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE('*********DEPT DETAILS END ************');
END;
/
CREATE TABLE XX_EMP1 AS SELECT * FROM EMP WHERE 1=2;
/
declare
cursor c1 is select * from emp;
i c1%rowtype;
begin
open c1;
loop
--fetch c1 into i;
fetch c1 into i;
exit when c1%notfound;
dbms_output.put_line(i.ename||','||i.sal);
insert into xx_emp1
(
empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno
)
values
(
i.empno,
i.ename,
i.job,
i.mgr,
i.hiredate,
i.sal,
i.comm,
i.deptno
);
end loop;
COMMIT;
DBMS_OUTPUT.PUT_LINE('TOTAL NUMBER OF ROWS INSERTED : '||C1%ROWCOUNT);
close c1;
end;
/
ROLLBACK;
/
TRUNCATE TABLE XX_EMP;
/
DECLARE
CURSOR C1 IS SELECT * FROM EMP;
I EMP%ROWTYPE;
LV_TOT_CNT NUMBER:=0;
LV_CNT NUMBER;
BEGIN
BEGIN
SELECT COUNT(*)
INTO LV_CNT
FROM EMP;
DBMS_OUTPUT.PUT_LINE('MAIN TOTAL COUNT :'||LV_CNT);
END;
OPEN C1;
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
LV_TOT_CNT:=LV_TOT_CNT+1;
INSERT INTO XX_EMP(EMPNO,ENAME,SAL) VALUES (I.EMPNO,I.ENAME,I.SAL);
END LOOP;
DBMS_OUTPUT.PUT_LINE('NUMBER OF ROWS NSERTED : '||LV_TOT_CNT);
CLOSE C1;
END;
/
DECLARE
CURSOR C1 IS SELECT * FROM EMP1;
I EMP1%ROWTYPE;
LV_SAL NUMBER;
LV_EMPNO NUMBER;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
IF I.SAL>=5000 THEN
DBMS_OUTPUT.PUT_LINE('BEFORE UPDATED SALARY'||I.EMPNO||','||I.SAL);
UPDATE EMP1
SET SAL=I.SAL+1000
WHERE EMPNO=I.EMPNO;
SELECT EMPNO,SAL INTO LV_EMPNO,LV_SAL FROM EMP1 WHERE EMPNO=I.EMPNO;
DBMS_OUTPUT.PUT_LINE('AFTER UPDATED SALARY'||LV_EMPNO||','||LV_SAL);
ELSIF I.SAL>2000 AND I.SAL<=3000 THEN
DBMS_OUTPUT.PUT_LINE('BEFORE UPDATED SALARY'||I.EMPNO||','||I.SAL);
UPDATE EMP1
SET SAL=I.SAL+500
WHERE EMPNO=I.EMPNO;
SELECT EMPNO,SAL INTO LV_EMPNO,LV_SAL FROM EMP1 WHERE EMPNO=I.EMPNO;
DBMS_OUTPUT.PUT_LINE('AFTER UPDATED SALARY'||LV_EMPNO||','||LV_SAL);
ELSIF I.SAL>1000 AND I.SAL<=2000 THEN
DBMS_OUTPUT.PUT_LINE('BEFORE UPDATED SALARY'||I.EMPNO||','||I.SAL);
UPDATE EMP1
SET SAL=I.SAL+250
WHERE EMPNO=I.EMPNO;
SELECT EMPNO,SAL INTO LV_EMPNO,LV_SAL FROM EMP1 WHERE EMPNO=I.EMPNO;
DBMS_OUTPUT.PUT_LINE('AFTER UPDATED SALARY'||LV_EMPNO||','||LV_SAL);
ELSIF I.SAL<1000 THEN
DBMS_OUTPUT.PUT_LINE('BEFORE UPDATED SALARY'||I.EMPNO||','||I.SAL);
UPDATE EMP1
SET SAL=I.SAL+100
WHERE EMPNO=I.EMPNO;
SELECT EMPNO,SAL INTO LV_EMPNO,LV_SAL FROM EMP1 WHERE EMPNO=I.EMPNO;
DBMS_OUTPUT.PUT_LINE('AFTER UPDATED SALARY'||LV_EMPNO||','||LV_SAL);
END IF;
END LOOP;
COMMIT;
CLOSE C1;
END;
/
EX7:
****
CREATE TABLE EMP1 AS SELECT * FROM EMP;
/
--Pl/sql Block calculates new commission for ALL employees using Explicit Cursors:
--Update
declare
cursor c1 is select empno,ename,comm from emp1
order by deptno;
veno emp1.empno%type;
vname emp1.ename%type;
vcomm emp1.comm%type;
begin
open c1;
IF c1%isopen THEN
dbms_output.put_line
(' Employ New Commission Report ');
loop
fetch c1 into veno,vname,vcomm;
exit when c1%notfound;
if vcomm is null then
vcomm := 3000;
elsif vcomm = 0 then
vcomm := 2500;
else
vcomm := vcomm + vcomm * .25;
end if;
update emp1 set comm = vcomm
where empno = veno;
dbms_output.put_line(c1%rowcount||' '||veno
||' '||vname||' '||vcomm);
end loop;
dbms_output.put_line(c1%rowcount ||' Employees are updated with new commission ');
close c1;
--commit;
ELSE
dbms_output.put_line(' Unable to open cursor... ');
END IF;
end;
/
--Delete
DECLARE
CURSOR C1 IS SELECT * FROM EMP1;
I EMP1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
DELETE FROM EMP1 WHERE EMPNO=I.EMPNO;
END LOOP;
commit;
DBMS_OUTPUT.PUT_LINE('NUMBER OF ROWS DELETED :'||C1%ROWCOUNT);
CLOSE C1;
END;
EX8:
****
--TO FETCH THE DATA FROM BASE TABLE (PO_HEADERS_ALL)
DECLARE
--cursor cursorname is select <column name> from tablename where condition;
CURSOR C1 IS select po_header_id,segment1 from po_headers_all where rownum<=1000;
lv_po_header_id varchar2(20);
lv_segment1 varchar2(20);
BEGIN
--open cursorname;
OPEN C1;
LOOP
--fetch cursorname into variable1,variable2,...;
FETCH C1 INTO lv_segment1,lv_po_header_id;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(lv_segment1||','||lv_po_header_id);
END LOOP;
--close cursorname;
CLOSE C1;
END;
/
--MULTI ROW SUBQUERY
DECLARE
CURSOR C1
IS
SELECT *
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM EMP);
I EMP%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.ENAME||','||I.JOB||','||I.SAL);
END LOOP;
CLOSE C1;
END;
/
--INLINE VIEW
DECLARE
--cursor cursorname is select <column name> from tablename where condition;
CURSOR C1 IS SELECT po_header_id,segment1 FROM (select ROWNUM R,po_header_id,segment1 from po_headers_all) WHERE R>40 AND R<=100;
lv_po_header_id number;
lv_segment1 varchar2(20);
BEGIN
--open cursorname;
OPEN C1;
LOOP
--fetch cursorname into variable1,variable2,...;
FETCH C1 INTO lv_segment1,lv_po_header_id;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(lv_segment1||','||lv_po_header_id);
END LOOP;
--close cursorname;
CLOSE C1;
END;
/
EX9:
****
--TO FETCH THE DATA FROM BASE TABLE (HR_OPERATING_UNITS)
DECLARE
--cursor cursorname is select <column name> from tablename where condition;
CURSOR C1 IS SELECT name,organization_id FROM hr_operating_units;
LV_NAME VARCHAR2(50);
lv_organization_id varchar2(20);
BEGIN
--open cursorname;
OPEN C1;
LOOP
--fetch cursorname into variable1,variable2,...;
FETCH C1 INTO LV_NAME,lv_organization_id;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_NAME||','||lv_organization_id);
END LOOP;
--close cursorname;
CLOSE C1;
END;
/
SELECT *
FROM PO_HEADERS_ALL PHA;
/
SELECT *
FROM PO_LINES_ALL PLA;
/
DECLARE
CURSOR C1 IS
SELECT E.ENAME,E.SAL,E.JOB,D.DNAME,D.LOC
FROM EMP E,
DEPT D
WHERE E.DEPTNO=D.DEPTNO;
I C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.ENAME||','||I.SAL||','||I.JOB||','||I.DNAME||','||I.LOC);
END LOOP;
CLOSE C1;
END;
/
DECLARE
CURSOR C1 IS
SELECT PHA.PO_HEADER_ID,PHA.TYPE_LOOKUP_CODE,PLA.PO_LINE_ID,PLA.ITEM_DESCRIPTION
FROM PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA
WHERE PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND ROWNUM<=100;
I C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.PO_HEADER_ID||','||I.TYPE_LOOKUP_CODE||','||I.PO_LINE_ID||','||I.ITEM_DESCRIPTION);
END LOOP;
CLOSE C1;
END;
/
DECLARE
CURSOR C1 IS SELECT * FROM DEPT;
CURSOR C2 IS SELECT * FROM EMP;
CURSOR C3 IS SELECT * FROM PO_HEADERS_ALL WHERE ROWNUM<=10;
I DEPT%ROWTYPE;
J EMP%ROWTYPE;
K PO_HEADERS_ALL%ROWTYPE;
LV_DEPT_CNT NUMBER;
LV_EMP_CNT NUMBER;
LV_PO_CNT NUMBER;
BEGIN
BEGIN
SELECT COUNT(*)
INTO LV_DEPT_CNT
FROM DEPT;
DBMS_OUTPUT.PUT_LINE('TOATAL DEPT COUNT :'||LV_DEPT_CNT);
END;
OPEN C1;
DBMS_OUTPUT.PUT_LINE('DEPT DETAILS PROGRAM START :'||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.DEPTNO||','||I.DNAME||','||I.LOC);
END LOOP;
DBMS_OUTPUT.PUT_LINE('DEPT DETAILS PROGRAM END :');
CLOSE C1;
BEGIN
SELECT COUNT(*)
INTO LV_EMP_CNT
FROM EMP;
DBMS_OUTPUT.PUT_LINE('TOATAL EMP COUNT :'||LV_EMP_CNT);
END;
OPEN C2;
DBMS_OUTPUT.PUT_LINE('EMP DETAILS PROGRAM START :'||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
LOOP
FETCH C2 INTO J;
EXIT WHEN C2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(J.EMPNO||','||J.ENAME||','||J.SAL);
END LOOP;
DBMS_OUTPUT.PUT_LINE('EMP DETAILS PROGRAM END :');
CLOSE C2;
BEGIN
SELECT COUNT(*)
INTO LV_PO_CNT
FROM PO_HEADERS_ALL;
DBMS_OUTPUT.PUT_LINE('TOATAL PO COUNT :'||LV_PO_CNT);
END;
OPEN C3;
DBMS_OUTPUT.PUT_LINE('PO DETAILS PROGRAM START :'||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
LOOP
FETCH C3 INTO K;
EXIT WHEN C3%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(K.PO_HEADER_ID||','||K.SEGMENT1||','||K.TYPE_LOOKUP_CODE);
END LOOP;
DBMS_OUTPUT.PUT_LINE('PO DETAILS PROGRAM END :');
CLOSE C3;
END;
/
BY USING WHILE LOOP:
======================
DECLARE
CURSOR C1 IS SELECT ENAME,SAL FROM EMP;
Lv_ENAME VARCHAR2(10);
Lv_SAL NUMBER(10);
BEGIN
OPEN C1;
FETCH C1 INTO Lv_ENAME,Lv_SAL;------------LOOP START FROM 1st ROW
WHILE(C1%FOUND)
LOOP
DBMS_OUTPUT.PUT_LINE(Lv_ENAME||','||Lv_SAL);
FETCH C1 INTO Lv_ENAME,Lv_SAL;------------LOOP CONTINUE UPTO LAST ROW
END LOOP;
CLOSE C1;
END;
/
EXPLICIT CURSOR ATTRIBUTES:
***************************
-->explicit cusrsor having following attributes
1)%notfound
2)%found
3)%isopen
4)%rowcount
-->all the above cursor attributes using along with cursor name only.
syntax:
********
cursorname%attributename
NOTE:
*****
-->except %rowcount all other cursor attribute records boolean value return eithere true or false
-->where as %rowcount returns number datatype.
%notfound:
**********
-->this attribute returns true when cursor does not have data after fetching records from cursor.
EX:
***
DECLARE
CURSOR C1 IS SELECT ENAME,SAL FROM EMP;
LV_ENAME VARCHAR2(20);
LV_SAL VARCHAR2(10);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO LV_ENAME,LV_SAL;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
END LOOP;
CLOSE C1;
END;
%FOUND:
********
-->Returns true if a succesful fetch has been executed
-->Returns false if no row was fetched
EX:
***
DECLARE
CURSOR C1 IS SELECT * FROM EMP;
I EMP%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO I;
IF C1%FOUND THEN
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME);
ELSE
EXIT;
END IF;
END LOOP;
CLOSE C1;
END;
%ISOPEN
*******
-->Returns true if the cursor is open
-->Returns false if the cursor is closed
EX:
***
DECLARE
CURSOR C1 IS SELECT * FROM EMP;
I EMP%ROWTYPE;
BEGIN
OPEN C1;
IF C1%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('CURSOR IS OPENED');
LOOP
FETCH C1 INTO I;
IF C1%FOUND THEN
DBMS_OUTPUT.PUT_LINE(I.ENAME||','||I.SAL||','||I.JOB);
ELSE
EXIT;
END IF;
END LOOP;
CLOSE C1;
IF NOT C1%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('CURSOR IS CLOSED');
END IF;
END IF;
END;
/
DECLARE
CURSOR C1 IS SELECT * FROM EMP;
I EMP%ROWTYPE;
BEGIN
--OPEN C1;
IF C1%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('CURSOR IS OPENED');
LOOP
FETCH C1 INTO I;
IF C1%FOUND THEN
DBMS_OUTPUT.PUT_LINE(I.ENAME||','||I.SAL||','||I.JOB);
ELSE
EXIT;
END IF;
END LOOP;
CLOSE C1;
IF NOT C1%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('CURSOR IS CLOSED');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('CURSOR IS MOT OPENED');
END IF;
END;
/
DECLARE
CURSOR C_GL_LEDGERS IS SELECT * FROM XX_GL_LEDGERS WHERE SHORT_NAME LIKE 'Vision%';
BEGIN
IF C_GL_LEDGERS%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('CURSOR IS OPEN');
ELSE
DBMS_OUTPUT.PUT_LINE('CURSOR IS NOT OPENED');
END IF;
FOR REC_LEDGERS IN C_GL_LEDGERS
LOOP
IF C_GL_LEDGERS%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('CURSOR IS OPEN');
ELSE
DBMS_OUTPUT.PUT_LINE('CURSOR IS NOT OPENED');
END IF;
END LOOP;
IF C_GL_LEDGERS%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('CURSOR IS OPEN');
ELSE
DBMS_OUTPUT.PUT_LINE('CURSOR IS NOT OPENED');
END IF;
END;
/
%ROWCOUNT:
**********
-->Returns number of rows fetched by the cursor
DECLARE
CURSOR C1 IS SELECT * FROM EMP;
I EMP%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME);
--DBMS_OUTPUT.PUT_LINE(C1%ROWCOUNT||' : '||'Total number of employees are: ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total number of employees are: '||C1%ROWCOUNT);
CLOSE C1;
END;
CURSOR WITH FOR LOOP:
*********************
-->In cursor for loop no need to open,fetch and close the cursor.
-->for loop is executed in executable section of the pl/sql block.
-->Improves performance.
syntax:
*******
for indexvariable in cursorname
loop
< Exec stmts >;
End loop;
Advantage:
**********
No need to
1. open cursor
2. fetch rows
3. check for end of rows
4. close cursor
5. declare variables
EX:
***
BY USING FOR LOOP:
******************
DECLARE
CURSOR C1 IS SELECT * FROM EMP;
BEGIN
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME);
END LOOP;
END;
/
--TO FETCH THE DATA FROM OE_ORDER_HEADERS_ALL
DECLARE
CURSOR C1 IS SELECT * FROM OE_ORDER_HEADERS_ALL WHERE ROWNUM<=100;
BEGIN
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT_LINE(I.ORDER_NUMBER||','||I.ORG_ID||','||I.ORDERED_DATE);
END LOOP;
END;
/
--Cursor Using Scalar Query:
declare
cursor c3 is select empno, ename, job, sal,
(select min(sal) from emp where job = E.job) Lopay,
(select max(sal) from emp where job = E.job) Hipay
from emp E order by job;
begin
dbms_output.put_line(' Job wise Salary Limits ');
dbms_output.put_line(' Empno Emp name Job Salary Lopay Hipay ');
for k in c3 loop
dbms_output.put_line(k.empno||' '||k.ename
||' '||k.job||' '||k.sal||' '||k.lopay||' '||k.hipay);
end loop;
end;
/
declare
cursor c1 is select * from emp;
begin
for i in c1
loop
if i.sal>=5000 then
dbms_output.put_line(i.empno||','||i.ename||','||i.sal);
elsif i.sal>2000 and i.sal<=3000 then
dbms_output.put_line(i.empno||','||i.ename||','||i.sal);
elsif i.sal>1000 and i.sal<=2000 then
dbms_output.put_line(i.empno||','||i.ename||','||i.sal);
else
dbms_output.put_line(i.empno||','||i.ename||','||i.sal);
end if;
end loop;
end;
DROP TABLE XX_EMP;
/
CREATE TABLE XX_EMP AS SELECT * FROM EMP WHERE 1=2;
/
DECLARE
CURSOR EMP_CUR IS SELECT * FROM EMP;
LV_COUNT NUMBER;
BEGIN
for EMP_REC in EMP_CUR
loop
INSERT INTO XX_EMP
VALUES
(
EMP_REC.EMPNO,
EMP_REC.ENAME,
EMP_REC.JOB,
EMP_REC.MGR,
EMP_REC.HIREDATE,
EMP_REC.SAL,
EMP_REC.COMM,
EMP_REC.DEPTNO
);
--DBMS_OUTPUT.PUT_LINE(EMP_CUR%ROWCOUNT);
LV_COUNT :=EMP_CUR%ROWCOUNT;
End loop;
DBMS_OUTPUT.PUT_LINE('NO OF ROWS INSERTED : '||LV_COUNT);
END;
/
SELECT * FROM XX_EMP;
/
DROP TABLE XX_SUPP_INV_CREATION;
/
CREATE TABLE XX_SUPP_INV_CREATION AS select invoice_id,invoice_num,vendor_id,invoice_currency_code,invoice_amount,payment_currency_code
from ap_invoices_all WHERE 1=2
/
SELECT *
FROM XX_SUPP_INV_CREATION;
/
DECLARE
CURSOR INV_CREA IS
select invoice_id,invoice_num,vendor_id,invoice_currency_code,invoice_amount,payment_currency_code
from ap_invoices_all WHERE ROWNUM<=5000;
LV_COUNT NUMBER :=0;
LV_STG_CNT NUMBER;
BEGIN
FOR INV_IDX IN INV_CREA
LOOP
INSERT INTO XX_SUPP_INV_CREATION
(
INVOICE_ID,
INVOICE_NUM,
VENDOR_ID,
INVOICE_CURRENCY_CODE,
INVOICE_AMOUNT,
PAYMENT_CURRENCY_CODE
)
VALUES
(
INV_IDX.INVOICE_ID,
INV_IDX.INVOICE_NUM,
INV_IDX.VENDOR_ID,
INV_IDX.INVOICE_CURRENCY_CODE,
INV_IDX.INVOICE_AMOUNT,
INV_IDX.PAYMENT_CURRENCY_CODE
);
LV_COUNT :=LV_COUNT+1;
END LOOP;
COMMIT;
select COUNT(*)
INTO LV_STG_CNT
from ap_invoices_all
WHERE ROWNUM<=5000;
DBMS_OUTPUT.PUT_LINE('TOTAL STGING TABLE COUNT : '||LV_STG_CNT);
DBMS_OUTPUT.PUT_LINE('TOTAL RECORDS ARE INSERTED :'||LV_COUNT);
END;
/
truncate table xx_emp;
/
DROP TABLE XX_PO_HEADERS;
/
CREATE TABLE XX_PO_HEADERS AS SELECT PO_HEADER_ID,TYPE_LOOKUP_CODE,SEGMENT1,AGENT_ID,VENDOR_ID FROM PO_HEADERS_ALL WHERE 1=2;
SELECT * FROM XX_PO_HEADERS;
/
DECLARE
CURSOR C_PO_CUR
IS
SELECT PO_HEADER_ID,TYPE_LOOKUP_CODE,SEGMENT1,AGENT_ID,VENDOR_ID FROM PO_HEADERS_ALL;
LV_COUNT NUMBER :=0;
BEGIN
FOR XX_PO_CUR IN C_PO_CUR
LOOP
INSERT INTO XX_PO_HEADERS
(
PO_HEADER_ID,
TYPE_LOOKUP_CODE,
SEGMENT1,
AGENT_ID,
VENDOR_ID
)
VALUES
(
XX_PO_CUR.PO_HEADER_ID,
XX_PO_CUR.TYPE_LOOKUP_CODE,
XX_PO_CUR.SEGMENT1,
XX_PO_CUR.AGENT_ID,
XX_PO_CUR.VENDOR_ID
);
LV_COUNT :=LV_COUNT+1;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('NO OF ROWS INSERTED INTO XX_PO_HEADERS TABLE : '||LV_COUNT);
END;
/
SELECT * FROM XX_PO_HEADERS;
/
DECLARE
CURSOR C1 IS SELECT * FROM EMP;
LV_COUNT NUMBER :=0;
BEGIN
FOR I IN C1
LOOP
LV_COUNT :=LV_COUNT+1;
INSERT INTO XX_EMP
(
EMPNO,
ENAME,
SAL
)
VALUES
(
I.EMPNO,
I.ENAME,
I.SAL
);
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('TOTAL NUMBER OF RECORDS ARE INSERTED :'||LV_COUNT);
END;
/
DROP TABLE XX_GL_LEDGERS;
/
CREATE TABLE XX_GL_LEDGERS
AS
SELECT *
FROM GL_LEDGERS;
/
DECLARE
CURSOR C_GL_LEDGERS IS SELECT * FROM XX_GL_LEDGERS WHERE SHORT_NAME LIKE 'Vision%';
LV_COUNT NUMBER :=0;
BEGIN
FOR REC_LEDGER IN C_GL_LEDGERS
LOOP
UPDATE XX_GL_LEDGERS
SET DESCRIPTION='XX_'||REC_LEDGER.DESCRIPTION
WHERE 1=1
AND LEDGER_ID=REC_LEDGER.LEDGER_ID;
LV_COUNT :=LV_COUNT+1;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('NUMBER OF ROWS UPDATED : '||LV_COUNT);
END;
/
--TO VERIFY THE DATA IN TABLE
SELECT * FROM XX_GL_LEDGERS WHERE SHORT_NAME LIKE 'Vision%';
/
DECLARE
CURSOR C_GL_LEDGERS IS SELECT * FROM XX_GL_LEDGERS WHERE SHORT_NAME LIKE 'Vision%';
LV_COUNT NUMBER :=0;
BEGIN
FOR XX_GL_LEDGER IN C_GL_LEDGERS
LOOP
DELETE FROM XX_GL_LEDGERS
WHERE LEDGER_ID=XX_GL_LEDGER.LEDGER_ID;
LV_COUNT :=LV_COUNT+1;
END LOOP;
--COMMIT;
DBMS_OUTPUT.PUT_LINE('NO OF ROWS DELETED : '||LV_COUNT);
END;
/
--2nd method
--CUSOR FOR LOOP USING SUBQUERIES
--We can also eliminate declare section of the cursor using for loop in this case we are using select statements in place of cursorname in cursor for loop.
BEGIN
FOR I IN (SELECT * FROM EMP)
LOOP
DBMS_OUTPUT.PUT_LINE(I.ENAME||','||I.SAL);
END LOOP;
END;
/
TRUNCATE TABLE XX_EMP;
/
TRUNCATE TABLE XX_DEPT;
/
BEGIN
FOR I IN (SELECT * FROM EMP)
LOOP
INSERT INTO XX_EMP
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
)
VALUES
(
I.EMPNO,
I.ENAME,
I.JOB,
I.MGR,
I.HIREDATE,
I.SAL,
I.COMM,
I.DEPTNO
);
END LOOP;
FOR J IN (SELECT * FROM DEPT)
LOOP
INSERT INTO XX_DEPT
(
DEPTNO,
DNAME,
LOC
)
VALUES
(
J.DEPTNO,
J.DNAME,
J.LOC
);
END LOOP;
END;
/
TRUNCATE TABLE XX_SUPP_INV_CREATION;
/
DECLARE
LV_COUNT NUMBER :=0;
BEGIN
FOR INV_IDX IN (select invoice_id,invoice_num,vendor_id,invoice_currency_code,invoice_amount,payment_currency_code
from ap_invoices_all WHERE ROWNUM<=5000)
LOOP
LV_COUNT :=LV_COUNT+1;
INSERT INTO XX_SUPP_INV_CREATION
(
INVOICE_ID,
INVOICE_NUM,
VENDOR_ID,
INVOICE_CURRENCY_CODE,
INVOICE_AMOUNT,
PAYMENT_CURRENCY_CODE
)
VALUES
(
INV_IDX.INVOICE_ID,
INV_IDX.INVOICE_NUM,
INV_IDX.VENDOR_ID,
INV_IDX.INVOICE_CURRENCY_CODE,
INV_IDX.INVOICE_AMOUNT,
INV_IDX.PAYMENT_CURRENCY_CODE
);
END LOOP;
DBMS_OUTPUT.PUT_LINE('TOTAL RECORDS ARE INSERTED :'||LV_COUNT);
END;
/
TRUNCATE TABLE XX_PO_HEADERS;;
/
DECLARE
LV_COUNT NUMBER :=0;
BEGIN
FOR I IN (SELECT PO_HEADER_ID,TYPE_LOOKUP_CODE,SEGMENT1,AGENT_ID,VENDOR_ID FROM PO_HEADERS_ALL)
LOOP
INSERT INTO XX_PO_HEADERS
(
PO_HEADER_ID,
TYPE_LOOKUP_CODE,
SEGMENT1,
AGENT_ID,
VENDOR_ID
)
VALUES
(
I.PO_HEADER_ID,
I.TYPE_LOOKUP_CODE,
I.SEGMENT1,
I.AGENT_ID,
I.VENDOR_ID
);
LV_COUNT :=LV_COUNT+1;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('NO OF ROWS INSERTED : '||LV_COUNT);
END;
/
SELECT * FROM XX_PO_HEADERS;
/
DECLARE
LV_COUNT NUMBER :=0;
BEGIN
FOR REC_LEDGER IN (SELECT * FROM XX_GL_LEDGERS WHERE SHORT_NAME LIKE 'Vision%')
LOOP
UPDATE XX_GL_LEDGERS
SET DESCRIPTION='XX_'||REC_LEDGER.DESCRIPTION
WHERE 1=1
AND LEDGER_ID=REC_LEDGER.LEDGER_ID;
LV_COUNT :=LV_COUNT+1;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('NUMBER OF ROWS UPDATED : '||LV_COUNT);
END;
/
--TESTING
SELECT * FROM XX_GL_LEDGERS WHERE SHORT_NAME LIKE 'Vision%';
/
DECLARE
LV_COUNT NUMBER :=0;
BEGIN
FOR XX_GL_LEDGER IN (SELECT * FROM XX_GL_LEDGERS WHERE SHORT_NAME LIKE 'Vision%')
LOOP
DELETE FROM XX_GL_LEDGERS
WHERE LEDGER_ID=XX_GL_LEDGER.LEDGER_ID;
LV_COUNT :=LV_COUNT+1;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('NO OF ROWS DELETED : '||LV_COUNT);
END;
/
--TESTING
SELECT * FROM XX_GL_LEDGERS WHERE SHORT_NAME LIKE 'Vision%';
/
MUTIPLE CURSOR WITH FOR LOOP:
****************************
DECLARE
CURSOR C1 IS SELECT * FROM DEPT;
CURSOR C2 IS SELECT * FROM EMP;
BEGIN
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT_LINE(I.DEPTNO||','||I.DNAME||','||I.LOC);
--END LOOP;
FOR J IN C2
LOOP
DBMS_OUTPUT.PUT_LINE(J.EMPNO||','||J.ENAME||','||J.JOB);
END LOOP;
END LOOP;
END;
/
DECLARE
CURSOR C1 IS SELECT * FROM DEPT;
CURSOR C2 IS SELECT * FROM EMP;
BEGIN
DBMS_OUTPUT.PUT_LINE('DEPT DETAILS');
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT_LINE(I.DEPTNO||','||I.DNAME||','||I.LOC);
END LOOP;
DBMS_OUTPUT.PUT_LINE('EMP DETAILS');
FOR J IN C2
LOOP
DBMS_OUTPUT.PUT_LINE(J.EMPNO||','||J.ENAME||','||J.JOB);
END LOOP;
END;
/
declare
cursor c1 is
select aia.INVOICE_NUM,aia.INVOICE_ID,aia.INVOICE_AMOUNT,aps.VENDOR_NAME,aps.segment1,assa.VENDOR_SITE_CODE,hou.name
from ap_invoices_all aia,
ap_suppliers aps,
ap_supplier_sites_all assa,
hr_operating_units hou
where 1=1
and aia.VENDOR_ID=aps.VENDOR_ID
and aps.VENDOR_ID=assa.VENDOR_ID
and aia.VENDOR_SITE_ID=assa.VENDOR_SITE_ID
and hou.organization_id=aia.org_id
and rownum<=100;
--and invoice_num='ERS-9163-109073';
begin
for i in c1
loop
dbms_output.put_line(i.INVOICE_NUM||','||i.INVOICE_ID||','||i.INVOICE_AMOUNT||','||i.VENDOR_NAME);
end loop;
end;
/
declare
cursor c1 is
select poh.PO_HEADER_ID,pol.PO_LINE_ID,pol.LINE_NUM,pol.ITEM_DESCRIPTION
from po_headers_all poh,
po_lines_all pol
where poh.po_header_id=pol.po_header_id
and poh.po_header_id=3533;
begin
for i in c1
loop
dbms_output.put_line(i.PO_HEADER_ID||','||i.PO_LINE_ID||','||i.LINE_NUM||','||i.ITEM_DESCRIPTION);
end loop;
end;
/
PARAMETER CURSOR:
*****************
-->Passing a parameter in cursor is called parameter cursor.
-->At this cursor it will allow a user to pass the data extracted from 1 cursor to the other cursor thru arguments.
-->Parameter specification is provided at Declaration of cursor and when the cursor gets opened.
syntax:
*******
cursor cursorname(parametername datatype) is select * from tablename where columnname=parametername;
OPEN CURSORNAME (VALUE/VARIBLE/EXPRESSION)
--normal explicit cursor
DECLARE
CURSOR C1 IS SELECT ENAME FROM EMP;
I C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.ENAME);
END LOOP;
CLOSE C1;
END;
/
DECLARE
CURSOR C1 IS SELECT ENAME,JOB FROM EMP WHERE DEPTNO=10;
CURSOR C2 IS SELECT ENAME,JOB FROM EMP WHERE DEPTNO=20;
LV_ENAME VARCHAR2(20);
LV_JOB VARCHAR2(20);
BEGIN
OPEN C1;
DBMS_OUTPUT.PUT_LINE('********DEPT10 DETAILS************');
LOOP
FETCH C1 INTO LV_ENAME,LV_JOB;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_JOB);
END LOOP;
CLOSE C1;
OPEN C2;
DBMS_OUTPUT.PUT_LINE('*************DEPT20 DETAILS**********');
LOOP
FETCH C2 INTO LV_ENAME,LV_JOB;
EXIT WHEN C2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_JOB);
END LOOP;
CLOSE C2;
END;
/
declare
lv_ename varchar2(20);
lv_deptno number;
cursor c1 is select ename from emp where deptno=lv_deptno;
begin
dbms_output.put_line('emp from deptno 10 details');
lv_deptno:=10;
open c1;
loop
fetch c1 into lv_ename;
exit when c1%notfound;
dbms_output.put_line(lv_ename);
end loop;
close c1;
dbms_output.put_line('emp from deptno 20 details');
lv_deptno:=20;
open c1;
loop
fetch c1 into lv_ename;
exit when c1%notfound;
dbms_output.put_line(lv_ename);
end loop;
close c1;
end;
/
declare
lv_ename varchar2(20);
--lv_deptno number;
cursor c1(P_DEPTNO NUMBER) is select ename from emp where deptno=P_deptno;
begin
dbms_output.put_line('emp from deptno 10 details');
open c1(10);
loop
fetch c1 into lv_ename;
exit when c1%notfound;
dbms_output.put_line(lv_ename);
end loop;
close c1;
dbms_output.put_line('emp from deptno 20 details');
open c1(20);
loop
fetch c1 into lv_ename;
exit when c1%notfound;
dbms_output.put_line(lv_ename);
end loop;
close c1;
end;
/
--Parameter cursor
DECLARE
CURSOR C1(P_JOB VARCHAR2) IS SELECT * FROM EMP where job=P_JOB;
I EMP%ROWTYPE;
BEGIN
OPEN C1('CLERK');
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||I.JOB);
END LOOP;
CLOSE C1;
OPEN C1('MANAGER');
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||I.JOB);
END LOOP;
CLOSE C1;
END;
/
DECLARE
cursor C1(P_JOB VARCHAR2) is select ENAME,JOB from EMP where JOB=P_JOB;
LV_ENAME VARCHAR2(20);
LV_JOB VARCHAR2(20);
BEGIN
DBMS_OUTPUT.PUT_LINE('MANAGER DETAILS DATA');
OPEN C1('MANAGER');
IF C1%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('CURSOR IS OPENED');
LOOP
FETCH C1 INTO LV_ENAME,LV_JOB;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_JOB);
END LOOP;
DBMS_OUTPUT.PUT_LINE('TOTAL ROWS :'||C1%ROWCOUNT);
CLOSE C1;
DBMS_OUTPUT.PUT_LINE('CLERK DETAILS DATA');
OPEN C1('CLERK');
LOOP
FETCH C1 INTO LV_ENAME,LV_JOB;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_JOB);
END LOOP;
DBMS_OUTPUT.PUT_LINE('TOTAL ROWS :'||C1%ROWCOUNT);
CLOSE C1;
DBMS_OUTPUT.PUT_LINE('SALESMAN DETAILS DATA');
OPEN C1('SALESMAN');
LOOP
FETCH C1 INTO LV_ENAME,LV_JOB;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_JOB);
END LOOP;
DBMS_OUTPUT.PUT_LINE('TOTAL ROWS :'||C1%ROWCOUNT);
CLOSE C1;
ELSE
DBMS_OUTPUT.PUT_LINE('CURSOR IS NOT OPENED');
END IF;
END;
/
--parameter cursor for parent and child tables by using simple loop
DECLARE
CURSOR C1 IS SELECT * FROM DEPT;
CURSOR C2(P_DEPTNO NUMBER) IS SELECT * FROM EMP WHERE DEPTNO=P_DEPTNO;
I DEPT%ROWTYPE;
J EMP%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('===========================');
DBMS_OUTPUT.PUT_LINE(I.DEPTNO||','||I.DNAME||','||I.LOC);
DBMS_OUTPUT.PUT_LINE('==========================');
OPEN C2(I.DEPTNO);
LOOP
FETCH C2 INTO J;
EXIT WHEN C2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(J.EMPNO||','||J.ENAME||','||J.JOB||','||J.MGR||','||J.HIREDATE||','||J.SAL||','||J.COMM||','||J.DEPTNO);
END LOOP;
CLOSE C2;
END LOOP;
CLOSE C1;
END;
/
--parameter cursor for parent and child tables by using for loop
DECLARE
CURSOR C1 IS SELECT * FROM DEPT;
CURSOR C2(P_DEPTNO NUMBER) IS SELECT * FROM EMP WHERE DEPTNO=P_DEPTNO;
BEGIN
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT_LINE('===============================');
DBMS_OUTPUT.PUT_LINE(I.DEPTNO||','||I.DNAME||','||I.LOC);
DBMS_OUTPUT.PUT_LINE('===============================');
FOR J IN C2(I.DEPTNO)
LOOP
DBMS_OUTPUT.PUT_LINE(J.DEPTNO||','||J.EMPNO||','||J.ENAME||','||J.JOB);
END LOOP;
END LOOP;
END;
/
DECLARE
CURSOR C1 IS SELECT * FROM PO_HEADERS_ALL WHERE ROWNUM<=20;
CURSOR C2(P_PO_HEADER_ID NUMBER) IS SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=P_PO_HEADER_ID;
BEGIN
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT_LINE('===============================');
DBMS_OUTPUT.PUT_LINE(I.PO_HEADER_ID||','||I.SEGMENT1);
DBMS_OUTPUT.PUT_LINE('===============================');
FOR J IN C2(I.PO_HEADER_ID)
LOOP
DBMS_OUTPUT.PUT_LINE(J.PO_HEADER_ID||','||J.PO_LINE_ID||','||J.ITEM_DESCRIPTION);
END LOOP;
END LOOP;
END;
/
DECLARE
CURSOR C1 IS SELECT * FROM PO_HEADERS_ALL WHERE PO_HEADER_ID=3533;
CURSOR C2(P_PO_HEADER_ID NUMBER) IS SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=P_PO_HEADER_ID;
BEGIN
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT_LINE('===============================');
DBMS_OUTPUT.PUT_LINE(I.PO_HEADER_ID||','||I.SEGMENT1);
DBMS_OUTPUT.PUT_LINE('===============================');
FOR J IN C2(I.PO_HEADER_ID)
LOOP
DBMS_OUTPUT.PUT_LINE(J.PO_HEADER_ID||','||J.PO_LINE_ID||','||J.LINE_NUM||','||J.ITEM_DESCRIPTION);
END LOOP;
END LOOP;
END;
/
DECLARE
CURSOR C1(P_DEPTNO NUMBER,P_JOB VARCHAR2)
IS
SELECT E.ENAME,E.SAL,E.JOB,D.DEPTNO,D.DNAME
FROM EMP E,
DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND D.DEPTNO=P_DEPTNO
AND E.JOB=NVL(P_JOB,E.JOB);
BEGIN
DBMS_OUTPUT.PUT_LINE('ENAME JOB SAL DEPTNO DNAME');
FOR I IN C1(20,NULL)
LOOP
DBMS_OUTPUT.PUT_LINE(I.ENAME||' '||I.JOB||' '||I.SAL||' '||I.DEPTNO||' '||I.DNAME);
END LOOP;
END;
/
DECLARE
L_ORG_CODE VARCHAR2(20) :='V1';
LV_ITEM VARCHAR2(100):='CM65305';
--LV_ITEM VARCHAR2(100):='';
CURSOR C1(P_ORG_CODE VARCHAR2,P_ITEM VARCHAR2) IS
SELECT MSIB.SEGMENT1 ITEM_NUMBER,
MSIB.DESCRIPTION,
MSIB.INVENTORY_ITEM_ID,
MSIB.ENABLED_FLAG,
MP.ORGANIZATION_CODE
FROM MTL_SYSTEM_ITEMS_B MSIB,
MTL_PARAMETERS MP
WHERE MSIB.ORGANIZATION_ID=MP.ORGANIZATION_ID
AND MP.ORGANIZATION_CODE=P_ORG_CODE
AND MSIB.SEGMENT1=NVL(P_ITEM,MSIB.SEGMENT1);
BEGIN
DBMS_OUTPUT.PUT_LINE('ITEM DETAILS START :'||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
FOR I IN C1(L_ORG_CODE,LV_ITEM)
LOOP
DBMS_OUTPUT.PUT_LINE(I.ITEM_NUMBER||','||I.DESCRIPTION||','||I.INVENTORY_ITEM_ID||','||I.ENABLED_FLAG||','||I.INVENTORY_ITEM_ID);
END LOOP;
DBMS_OUTPUT.PUT_LINE('ITEM DETAILS END :');
END;
/
No comments:
Post a Comment