14.Explicit Cursors Using For

PL/SQL

Cursor : PLSQL creates a temporary area for the sql query, and that area is called cursor

Explicit Cursor using FOR:

FOr : It helps is open, process, and close the cursor automatically

syntax:
Declare
cursor cursor_name is select statement;
var cursor%rowtype; — to assign all columns
begin
FOR record_type_variable in Cursor_name
loop
statement(s);
end loop;
end;

example: Read data from dept and emp table

DECLARE
CURSOR DEPT_EMP_CUR IS SELECT D.DEPT_ID,
D.DEPT_NAME,
E.ENO EMPNO,
E.ENAME,
E.SAL,
E.JOB
FROM DEPT D,
EMP E
WHERE D.DEPT_ID = E.DEPT_ID;
LN_TAX NUMBER;

-- EMP_REC EMP_CUR%ROWTYPE;

BEGIN
FOR DEPT_EMP_REC IN DEPT_EMP_CUR
LOOP
LN_TAX:= DEPT_EMP_REC.SAL*10/100;

    DBMS_OUTPUT.PUT_LINE (DEPT_EMP_REC.DEPT_ID||' '||
                                  DEPT_EMP_REC.DEPT_NAME||' '||
                DEPT_EMP_REC.EMPNO||' '||
                DEPT_EMP_REC.ENAME||' '||
                DEPT_EMP_REC.SAL||' '||
                DEPT_EMP_REC.JOB||' '||
                    LN_TAX);

    begin
        UPDATE EMP SET TAX = LN_TAX 
                     WHERE ENO = DEPT_EMP_REC.EMPNO;
    exception
        when others then
        dbms_output.put_line ('Error in updation '||sqlerrm);
    end;


END LOOP;

END;

Task : Show invoice information along po and with some discount
ex. if invoice amount <100000 then 10% discount
if invoice amount between 1000000 and 200000 then 20% discount on invoice amount
otherwise discount is 30%

FOR can be used to select ‘select query’ at run time

DECLARE
/* CURSOR DEPT_EMP_CUR IS SELECT D.DEPT_ID,
D.DEPT_NAME,
E.ENO EMPNO,
E.ENAME,
E.SAL,
E.JOB
FROM DEPT D,
EMP E
WHERE D.DEPT_ID = E.DEPT_ID; */
LN_TAX NUMBER;

-- EMP_REC EMP_CUR%ROWTYPE;

BEGIN
FOR DEPT_EMP_REC IN (SELECT D.DEPT_ID,
D.DEPT_NAME,
E.ENO EMPNO,
E.ENAME,
E.SAL,
E.JOB
FROM DEPT D,
EMP E
WHERE D.DEPT_ID = E.DEPT_ID)

LOOP
     LN_TAX:= DEPT_EMP_REC.SAL*10/100;

    DBMS_OUTPUT.PUT_LINE (DEPT_EMP_REC.DEPT_ID||' '||
                                  DEPT_EMP_REC.DEPT_NAME||' '||
                DEPT_EMP_REC.EMPNO||' '||
                DEPT_EMP_REC.ENAME||' '||
                DEPT_EMP_REC.SAL||' '||
                DEPT_EMP_REC.JOB||' '||
                    LN_TAX);

    begin
        UPDATE EMP SET TAX = LN_TAX 
                     WHERE ENO = DEPT_EMP_REC.EMPNO;
    exception
        when others then
        dbms_output.put_line ('Error in updation '||sqlerrm);
    end;

END LOOP;

END;