Scribd
Upload
26 views4 pages

CURSORS_example

The document provides examples of using explicit cursors in PL/SQL to fetch employee data from a database. It demonstrates various methods, including fetching specific records based on salary criteria, using records to retrieve multiple fields, and employing cursor for loops for streamlined data access. Each example concludes with successful execution messages and outputs relevant employee information.

Uploaded by

grojamani
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
26 views4 pages

CURSORS_example

The document provides examples of using explicit cursors in PL/SQL to fetch employee data from a database. It demonstrates various methods, including fetching specific records based on salary criteria, using records to retrieve multiple fields, and employing cursor for loops for streamlined data access. Each example concludes with successful execution messages and outputs relevant employee information.

Uploaded by

grojamani
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

CURSORS : EXPLICIT CURSOR

SQL> DECLARE
ENO EMP.EMPNO%TYPE;
NAME EMP.ENAME%TYPE;
SALARY EMP.SAL%TYPE;
CURSOR C1 IS SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>5000;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO ENO,NAME,SALARY;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(ENO||' '||NAME||' '||SALARY);
END LOOP;
END;

7839 KING 6000


PL/SQL procedure successfully completed.

CURSORS AND RECORDS


SQL> DECLARE
REC EMP%ROWTYPE;
CURSOR C1 IS SELECT * FROM EMP WHERE SAL<4000;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO REC;
IF C1%FOUND THEN
DBMS_OUTPUT.PUT_LINE(REC.EMPNO||' '||REC.ENAME||' '||REC.JOB||' '||REC.SAL);
ELSE
EXIT;
END IF;
END LOOP;
CLOSE C1;
END;

7369 SMITH CLERK 1800


7499 ALLEN SALESMAN 2600
7521 WARD SALESMAN 2250
7566 JONES MANAGER 3975
7654 MARTIN SALESMAN 2250
7698 BLAKE MANAGER 3850
7782 CLARK MANAGER 3450
7844 TURNER SALESMAN 2500
7876 ADAMS CLERK 2100
7900 JAMES CLERK 1950
7934 MILLER CLERK 2300

PL/SQL procedure successfully completed.

CURSOR FOR LOOP

SQL> DECLARE

CURSOR CR IS SELECT * FROM EMP WHERE DEPTNO=20;


BEGIN

FOR I IN CR LOOP

DBMS_OUTPUT.PUT_LINE(I.ENAME||' '||I.JOB||' '||I.SAL||' '||I.DEPTNO);

END LOOP;
END;

SMITH CLERK 1800 20


JONES MANAGER 3975 20
SCOTT ANALYST 4000 20
ADAMS CLERK 2100 20
FORD ANALYST 4000 20

PL/SQL procedure successfully completed.


SQL> DECLARE
2 DNO EMP.DEPTNO%TYPE:=&DEPTNO;
3 CURSOR A IS
4 SELECT *FROM EMP WHERE DEPTNO=DNO;
5 B A%ROWTYPE;
6 BEGIN
7 OPEN A;
8 DBMS_OUTPUT.PUT_LINE('************************');
9 LOOP
10 FETCH A INTO B;
11 EXIT WHEN A%NOTFOUND;
12 DBMS_OUTPUT.PUT_LINE('EMPNO IS'||B.EMPNO);
13 DBMS_OUTPUT.PUT_LINE('ENAME IS'||B.ENAME);
14 DBMS_OUTPUT.PUT_LINE('JOB IS'||B.JOB);
15 DBMS_OUTPUT.PUT_LINE('HIREDATE IS'||B.HIREDATE);
16 DBMS_OUTPUT.PUT_LINE('SAL IS'||B.SAL);
17 DBMS_OUTPUT.PUT_LINE('DEPT NO IS'||B.DEPTNO);
18 DBMS_OUTPUT.PUT_LINE('****************************');
19 END LOOP;
20 CLOSE A;
21 END;
22
23 /
Enter value for deptno: 20
old 2: DNO EMP.DEPTNO%TYPE:=&DEPTNO;
new 2: DNO EMP.DEPTNO%TYPE:=20;
************************
EMPNO IS7369
ENAME ISSMITH
JOB ISCLERK
HIREDATE IS17-DEC-80
SAL IS1800
DEPT NO IS20
****************************
EMPNO IS7566
ENAME ISJONES
JOB ISMANAGER
HIREDATE IS02-APR-81
SAL IS3975
DEPT NO IS20
****************************
EMPNO IS7788
ENAME ISSCOTT
JOB ISANALYST
HIREDATE IS19-APR-87
SAL IS4000
DEPT NO IS20
****************************
EMPNO IS7876
ENAME ISADAMS
JOB ISCLERK
HIREDATE IS23-MAY-87
SAL IS2100
DEPT NO IS20
****************************
EMPNO IS7902
ENAME ISFORD
JOB ISANALYST
HIREDATE IS03-DEC-81
SAL IS4000
DEPT NO IS20
****************************

PL/SQL procedure successfully completed.

You might also like