Q:

Write a program in PL/SQL to show the uses of fetch one record at a time using fetch statement inside the loop

0

Write a program in PL/SQL to show the uses of fetch one record at a time using fetch statement inside the loop.

All Answers

need an explanation for this answer? contact us directly to get an explanation for this answer

DECLARE
    CURSOR cur1 IS
      SELECT first_name,
             last_name,
             job_id
      FROM   employees
      WHERE  Regexp_like (job_id, 'S[TA]_MAN')
      ORDER  BY last_name;
    emp_firstname employees.first_name%TYPE; 
    emp_lastname  employees.last_name%TYPE; 
    emp_jobid     employees.job_id%TYPE; 
    CURSOR cur2 IS
      SELECT *
      FROM   employees
      WHERE  Regexp_like (job_id, '[ACADFIMKSA]_M[ANGR]')
      ORDER  BY job_id;
    all_employees employees%ROWTYPE; 
BEGIN
    OPEN cur1;
    LOOP -- Fetches 2 columns into variables
        FETCH cur1 INTO emp_firstname, emp_lastname, emp_jobid;
        EXIT WHEN cur1%NOTFOUND;
        dbms_output.Put_line(Rpad(emp_firstname, 25, ' ')
                             ||Rpad(emp_lastname, 25, ' ')
                             || emp_jobid);
    END LOOP;
    CLOSE cur1;
    dbms_output.Put_line('-------------------------------------');
    OPEN cur2;
    LOOP -- Fetches entire row into the v_employees record
        FETCH cur2 INTO all_employees;
        EXIT WHEN cur2%NOTFOUND;
        dbms_output.Put_line(Rpad(all_employees.first_name, 25, ' ')
                             ||Rpad(all_employees.last_name, 25, ' ')
                             || all_employees.job_id);
    END LOOP;
    CLOSE cur2;
END;
/

Sample Output:

SQL> /
Gerald                   Cambrault                SA_MAN
Alberto                  Errazuriz                SA_MAN
Adam                     Fripp                    ST_MAN
Payam                    Kaufling                 ST_MAN
Kevin                    Mourgos                  ST_MAN
Karen                    Partners                 SA_MAN
John                     Russell                  SA_MAN
Shanta                   Vollman                  ST_MAN
Matthew                  Weiss                    ST_MAN
Eleni                    Zlotkey                  SA_MAN
-------------------------------------
Shelley                  Higgins                  AC_MGR
Nancy                    Greenberg                FI_MGR
Michael                  Hartstein                MK_MAN
John                     Russell                  SA_MAN
Karen                    Partners                 SA_MAN
Alberto                  Errazuriz                SA_MAN
Gerald                   Cambrault                SA_MAN
Eleni                    Zlotkey                  SA_MAN

PL/SQL procedure successfully completed.

need an explanation for this answer? contact us directly to get an explanation for this answer

total answers (1)

Similar questions


need a help?


find thousands of online teachers now