Does anyone know how can you put queries in pl/sql?
like select * from emp inside a pl/sql block and then output the results in sqlplus
You would need to use an explicit or implicit cursor, then use DBMS_OUTPUT.put_line to print the result in SQL*.
Explicit cursor in an anonymous PL/SQL block:
CURSOR emp_cur IS
SELECT * FROM emp;
FOR rec IN emp_cur
DBMS_OUTPUT.put_line(rec.empno || ', ' || rec.ename);
Hope this helps.
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.
hmm isnt there any other way...? I thought ref cursor was for this or I read from somewhere but I dunno how does it work ...
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.
[Edited by CliffW on 04-28-2001 at 06:44 PM]
Click Here to Expand Forum to Full Width