-
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
-
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
-
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
-
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):
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]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|