Compare this with Employees_Cur_Static_Sql. The API is the same and the implementation is rather similar. However, the cursor is now a variable of type ref cursor rather than a regular cursor.
As the text of Employees_Cur_Ref_Cursor stands, there's no reason for using the ref cursor construct. You should imagine that the fetch statement is replaced with a call to a function with formal in-parameter of type ref cursor which returns Last_Names_Tab_t and which is invoked with the actual parameter c_employees. This function would encapsulate a re-usable transformation on rows with a given record signature which might be applied to data from two or more different actual tables. (To reinforce this, the formal in-parameter would be declared to be of a strong cursor type.) The select statement in question is known at compile time for the calling program, so there's no need for dynamic SQL, but it's not known at compile time for the generic transformation function.
Create the procedure Show_Rows_For_Dept_Ref_Cursor. And as above, use two concurrent sessions connected as programmer and observer and step through this test. This shows that the PL/SQL close statement when its argument is a ref cursor does a hard-close (ie does the actions corresponding to Dbms_Sql.Close_Cursor. You can see this in "slow motion" by compiling the package Wait and by activating the line Wait.Go in Employees_Cur_Ref_Cursor and running the test again. When Show_Rows_For_Dept_Ref_Cursor "hangs", query open_cursor_sql_text from the session connected as observer. Then do...
begin programmer.Wait.Finish; end;...from the from the session connected as observer. and query open_cursor_sql_text again. You'll see that the PL/SQL cursor cache has a cursor for select last_name from employees... after the fetch statement completes, and that this vanishes as soon as the close statement completes.
Note: The compiler does have enough information to implement the cost-saving pardigm for a ref cursor opened with static SQL. However, up Oracle9i, this optimization has not been implemented.