Here is an example with an implicit cursor (I had to look up the syntax because I almost always use explicit cursors):
WHERE empno = 1891;
DBMS_OUTPUT.put_line (somevariable.empno || ', ' || somevariable.ename);
The implicit cursor can deal with only with queries that return a single row.
If I recall correctly, implicit cursors require two calls to the database -- one to check to see if only one row will be returned, and one to actually get the row. So I am reluctant to use them because this seems awfully inefficient. But maybe someone knows whether implicit cursors really do have a performance disadvantage over explicit cursors.
Well yes, Cursor Variables using REF TYPES are another way of placing SQL statements inside PL/SQL blocks. Here is an example of a Cursor Variable of a weak REF TYPE (someone please correct me if I am not using the terminology quite right):
TYPE emp_curtype IS REF CURSOR;
-- Attach Cursor Variable to one SELECT statement
OPEN emp_cur FOR SELECT * FROM scott.emp;
FETCH emp_cur INTO emp_rec;
-- Attach same Cursor Variable to another SELECT statement
OPEN emp_cur FOR SELECT * FROM scott.dept;
FETCH emp_cur INTO dept_rec;
You can use the weak type to associate the cursor with different SQL statements at different times in your PL/SQL block, as the example above shows. (OPEN emp_cur FOR SELECT . . . ) Is this what you had in mind?
Steven Feuerstein has an extensive discussion of this in Chapter 6 of his book.