select in pl/sql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: select in pl/sql

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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

  2. #2
    Join Date
    Mar 2000
    Location
    Kanab, Utah, 84741
    Posts
    10
    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

  3. #3
    Join Date
    Mar 2000
    Location
    Kanab, Utah, 84741
    Posts
    10
    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

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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 ...

  5. #5
    Join Date
    Mar 2000
    Location
    Kanab, Utah, 84741
    Posts
    10
    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
  •  



Click Here to Expand Forum to Full Width