select in pl/sql

[pando]

Hi

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

[CliffW]

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:

DECLARE
CURSOR emp_cur IS
SELECT * FROM emp;
BEGIN
FOR rec IN emp_cur
LOOP
DBMS_OUTPUT.put_line(rec.empno || ', ' || rec.ename);
END LOOP;
END;
/


Hope this helps.

CliffW

[CliffW]

Here is an example with an implicit cursor (I had to look up the syntax because I almost always use explicit cursors):

DECLARE
somevariable scott.emp%ROWTYPE;
BEGIN
SELECT *
INTO somevariable
FROM scott.emp
WHERE empno = 1891;
DBMS_OUTPUT.put_line (somevariable.empno || ', ' || somevariable.ename);
END;
/

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.

CliffW

[pando]

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 ...

[CliffW]

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):

DECLARE
TYPE emp_curtype IS REF CURSOR;
emp_cur emp_curtype;
emp_rec scott.emp%ROWTYPE;
dept_rec scott.dept%ROWTYPE;
BEGIN
-- Attach Cursor Variable to one SELECT statement
OPEN emp_cur FOR SELECT * FROM scott.emp;
FETCH emp_cur INTO emp_rec;
DBMS_OUTPUT.put_line (emp_rec.ename);
-- Attach same Cursor Variable to another SELECT statement
OPEN emp_cur FOR SELECT * FROM scott.dept;
FETCH emp_cur INTO dept_rec;
DBMS_OUTPUT.put_line (dept_rec.deptno);
CLOSE emp_cur;
END;
/

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.

CliffW

[Edited by CliffW on 04-28-2001 at 06:44 PM]