Write a program in PL/SQL to show the uses of fetch one record at a time using fetch statement inside the loop
- pl/sql
- 2021-09-28
- mhanasmh00489829403
الأجوبة
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.
أسئلة مشابهة
القوائم الدراسية التي ينتمي لها السؤال
معلومات ذات صلة