tag:blogger.com,1999:blog-7849367040589270673.post2373037451111578847..comments2024-03-21T22:50:39.997-07:00Comments on Obsessed with Oracle PL/SQL: Types of Cursors Available in PL/SQLSteven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-7849367040589270673.post-14727020579283259092016-10-12T05:56:45.262-07:002016-10-12T05:56:45.262-07:00Good point, Albert. Those closes are not necessary...Good point, Albert. Those closes are not necessary. In case anyone wants to check:<br /><br />DECLARE<br /> location_id_in INTEGER := 10;<br /><br /> TYPE refcursor IS REF CURSOR;<br /><br /> CURSOR all_in_one_cur<br /> IS<br /> SELECT l.city,<br /> CURSOR (<br /> SELECT d.department_name,<br /> CURSOR (<br /> SELECT e.last_name<br /> FROM employees e<br /> WHERE e.department_id =<br /> d.department_id)<br /> AS ename<br /> FROM departments d<br /> WHERE l.location_id = d.location_id)<br /> AS dname<br /> FROM locations l<br /> WHERE l.location_id = location_id_in;<br /><br /> department_cur refcursor;<br /> employee_cur refcursor;<br /> v_city locations.city%TYPE;<br /> v_dname departments.department_name%TYPE;<br /> v_ename employees.last_name%TYPE;<br />BEGIN<br /> OPEN all_in_one_cur;<br /><br /> LOOP<br /> FETCH all_in_one_cur INTO v_city, department_cur;<br /><br /> EXIT WHEN all_in_one_cur%NOTFOUND;<br /> LOOP<br /> FETCH department_cur INTO v_dname, employee_cur;<br /><br /> EXIT WHEN department_cur%NOTFOUND;<br /> LOOP<br /> FETCH employee_cur INTO v_ename;<br /><br /> EXIT WHEN employee_cur%NOTFOUND;<br /> DBMS_OUTPUT.put_line (<br /> v_city || ' ' || v_dname || ' ' || v_ename);<br /> END LOOP;<br /> END LOOP;<br /> END LOOP;<br /><br /> CLOSE all_in_one_cur;<br /><br /> IF department_cur%ISOPEN<br /> THEN<br /> DBMS_OUTPUT.put_line ('department_cur is still open');<br /> ELSE<br /> DBMS_OUTPUT.put_line ('department_cur is closed');<br /> END IF;<br /><br /> IF employee_cur%ISOPEN<br /> THEN<br /> DBMS_OUTPUT.put_line ('employee_cur is still open');<br /> ELSE<br /> DBMS_OUTPUT.put_line ('employee_cur is closed');<br /> END IF;<br />END;<br />/Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-24047990759950946582016-10-11T17:35:27.953-07:002016-10-11T17:35:27.953-07:00Hi Steven. One question. Why do you explicitly clo...Hi Steven. One question. Why do you explicitly close the ref cursors ? I'm guessing the CLOSE on the all in one cursor should automatically close the department and employee ref cursorsAnonymoushttps://www.blogger.com/profile/02715950010305678290noreply@blogger.com