1.
Create a cursor using PLSQL program to display employee name and salary ( using
%TYPE ).
DECLARE
CURSOR C IS SELECT ENAME, SAL FROM EMP;
VNAME [Link]%TYPE;
VSAL [Link]%TYPE;
BEGIN
OPEN C;
LOOP
FETCH C INTO VNAME, VSAL;
EXIT WHEN C%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(VNAME||' '||VSAL);
END LOOP;
CLOSE C;
END;
/
2. Create a cursor using PLSQL program to display employee name and job ( using
%ROWTYPE ).
DECLARE
CURSOR C IS SELECT * FROM EMP;
E_ROW C%ROWTYPE;
BEGIN
OPEN C;
LOOP
FETCH C INTO E_ROW;
EXIT WHEN C%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(E_ROW.ENAME||' '||E_ROW.JOB);
END LOOP;
CLOSE C;
END;
3. Create a cursor using PLSQL program to display employee names and jobs for the
given department number.
DECLARE
N NUMBER:=&N;
CURSOR C1 IS SELECT * FROM EMP WHERE DEPTNO=N;
E_ROW C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO E_ROW;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(E_ROW.ENAME||' '||E_ROW.JOB);
END LOOP;
CLOSE C1;
END;
/
4. Create a cursor using PLSQL program to display employee names and jobs who
are working as clerk in dept number 10.
DECLARE
CURSOR C1 IS SELECT * FROM EMP WHERE DEPTNO=10 AND JOB=’CLERK’;
E_ROW C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO E_ROW;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(E_ROW.ENAME||' '||E_ROW.JOB);
END LOOP;
CLOSE C1;
END;
5. CREATE A CURSOR USING PLSQL PROGRAM TO DISPLAY EMPLOYEE NAMES AND THEIR
JOBS WHOSE SALARY IS GRETAER THAN 2500.
DECLARE
CURSOR C1 IS SELECT * FROM EMP WHERE SAL>2500;
E_ROW C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO E_ROW;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(E_ROW.ENAME||' '||E_ROW.JOB||'
'||E_ROW.SAL);
END LOOP;
CLOSE C1;
END;
/
6. WRITE A PLSQL PROGRAM TO DEMONSTRATE FOR LOOP CURSOR
DECLARE
CURSOR C IS SELECT ENAME FROM EMP;
BEGIN
FOR I IN C
LOOP
DBMS_OUTPUT.PUT_LINE([Link]);
END LOOP;
END;
/
7. WRITE A PLSQL PROGRAM TO DEMONSTRATE PARAMETERIZED CURSORS
DECLARE
CURSOR C(X [Link]%TYPE) IS SELECT ENAME FROM EMP WHERE EMPNO = X;
VNAME [Link]%TYPE;
N NUMBER(5):=&N;
BEGIN
OPEN C(N);
LOOP
FETCH C INTO VNAME;
EXIT WHEN C%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(VNAME);
END LOOP;
CLOSE C;
END;
/
8. WRITE A PLSQL PROGRAM TO DEMONSTRATE PARAMETERIZED CURSOR USING FOR
LOOP
DECLARE
N NUMBER(5):=&N;
CURSOR C(X [Link]%TYPE) IS SELECT ENAME FROM EMP WHERE EMPNO = X;
BEGIN
FOR I IN C(N)
LOOP
DBMS_OUTPUT.PUT_LINE([Link]);
END LOOP;
END;
/
9. CREATE A CURSOR USING PLSQL PROGRAM TO COUNT THE NUMBER OF EMPLOYEES
WHO ARE WORKING IN DEPTNO 20.
DECLARE
N NUMBER(5);
CURSOR C IS SELECT COUNT(ENAME) FROM EMP WHERE DEPTNO=20;
BEGIN
OPEN C;
LOOP
FETCH C INTO N;
EXIT WHEN C%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(N);
END LOOP;
CLOSE C;
END;
/