Fetching With a Cursor
DECLARE
v_jobid employees.job_id%TYPE; -- variable for job_id
v_lastname employees.last_name%TYPE; -- variable for last_name
CURSOR c1 IS
SELECT last_name,
job_id
FROM employees
WHERE Regexp_like(job_id,'S[HT]_CLERK');
v_employees employees%ROWTYPE; -- record variable for row
CURSOR c2 IS
SELECT *
FROM employees
WHERE Regexp_like(job_id,'[ACADFIMKSA]_M[ANGR]');
BEGIN
OPEN c1; -- open the cursor before fetching
LOOP
FETCH c1 INTO v_lastname,v_jobid; -- fetches 2 columns into variables
EXIT WHEN c1%NOTFOUND;
dbms_output.Put_line(Rpad(v_lastname,25,' ')
||v_jobid);
END LOOP;
CLOSE c1;
dbms_output.Put_line('-------------------------------------');
OPEN c2;
LOOP
FETCH c2 INTO v_employees; -- fetches entire row into the v_employees record
EXIT WHEN c2%NOTFOUND;
dbms_output.Put_line(Rpad(v_employees.last_name,25,' ')
||v_employees.job_id);
END LOOP;
CLOSE c2;
END;
/
Output :-
OConnell SH_CLERK
Grant SH_CLERK
Nayer ST_CLERK
Mikkilineni ST_CLERK
Landry ST_CLERK
Markle ST_CLERK
Bissot ST_CLERK
Atkinson ST_CLERK
Marlow ST_CLERK
Olson ST_CLERK
Mallin ST_CLERK
Rogers ST_CLERK
Gee ST_CLERK
Philtanker ST_CLERK
Ladwig ST_CLERK
Stiles ST_CLERK
Seo ST_CLERK
Patel ST_CLERK
Rajs ST_CLERK
Davies ST_CLERK
Matos ST_CLERK
Vargas ST_CLERK
Taylor SH_CLERK
Fleaur SH_CLERK
Sullivan SH_CLERK
Geoni SH_CLERK
Sarchand SH_CLERK
Bull SH_CLERK
Dellinger SH_CLERK
Cabrio SH_CLERK
Chung SH_CLERK
Dilly SH_CLERK
Gates SH_CLERK
Perkins SH_CLERK
Bell SH_CLERK
Everett SH_CLERK
McCain SH_CLERK
Jones SH_CLERK
Walsh SH_CLERK
Feeney SH_CLERK
-------------------------------------
Hartstein MK_MAN
Higgins AC_MGR
Greenberg FI_MGR
Russell SA_MAN
Partners SA_MAN
Errazuriz SA_MAN
Cambrault SA_MAN
Zlotkey SA_MAN
PL/SQL procedure successfully completed.
Posts
0 comments:
Post a Comment