PL23 - PLSQL Tutorial - Explicit cursors in PL/SQL blocks (Theory)

[dicnduck2]

thumbs up if you're watching this in 2012 :)

thumb up if your watching this whilst leaning on your left arm

[luisrhdzr]

DECLARE

CURSOR EMPS IS SELECT LAST_NAME , SALARY FROM HR.EMPLOYEES WHERE SALARY > 4500;

VENAME HR.EMPLOYEES.LAST_NAME%TYPE;

VSALARY HR.EMPLOYEES.SALARY%TYPE;

BEGIN

 IF NOT EMPS%ISOPEN THEN OPEN EMPS; END IF; LOOP FETCH EMPS INTO VENAME, VSALARY; EXIT WHEN EMPS%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('Row # '|| EMPS%ROWCOUNT || ' Name : ' || VENAME || ' Salary : ' || VSALARY); END LOOP ; CLOSE EMPS;

END;

/

[luisrhdzr]

DECLARE

CURSOR EMPS IS SELECT LAST_NAME FROM HR.EMPLOYEES WHERE DEPARTMENT_ID=80;

VENAME HR.EMPLOYEES.LAST_NAME%TYPE;

BEGIN IF NOT EMPS%ISOPEN THEN OPEN EMPS; END IF; LOOP FETCH EMPS INTO VENAME; EXIT WHEN EMPS%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('Row number : '|| EMPS%ROWCOUNT || ' = ' || VENAME); END LOOP ; CLOSE EMPS;

END;

/

[ORABEN]

Program to try

DECLARE V_ENAME EMPLOYEES.ENAME%TYPE; V_SALARY EMPLOYEES.SALARY%TYPE; CURSOR EMPCUR IS SELECT ENAME, SALARY FROM EMPLOYEES WHERE SALARY > 4500 ORDER BY SALARY DESC;

BEGIN OPEN EMPCUR; LOOP FETCH EMPCUR INTO V_ENAME, V_SALARY; EXIT WHEN EMPCUR%NOTFOUND; DBMS_OUTPOUT.PUT_LINE( 'V_ENAME ' ¦¦ ' : ' ¦¦ TO_CHAR(V_SALARY)); END LOOP; CLOSE EMPCUR;

END;

/