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.
Sample Output:
need an explanation for this answer? contact us directly to get an explanation for this answer