4 Replies Latest reply: Feb 11, 2013 5:57 PM by 9423755 RSS

    Is there any functional point to the explicit cursor RETURN clause?

    9423755
      Hi

      An explicit cursor may be declared and specified with or without the RETURN clause.
      Feuerstein gives an example of the declaration and specification of a cursor with (and without) the RETURN clause, but doesn't (at least as far as I can make out) tell us what difference, functionally the inclusion of the RETURN clause makes.
      Nor am I able to discern this from reading the documentation or googling on the 'net.

      Consider the following code:
      create or replace procedure test
      is
          e_rec employees%rowtype;
          cursor c1 is select * from employees;
          cursor c2 return employees%rowtype
                    is select * from employees;
      begin
          open c1;
          -- fetch a single row
          fetch c1 into e_rec;
          close c1;
          -- fetch a single row    
          open c2;
          fetch c2 into e_rec;
          close c2;
          dbms_output.put_line('Done.');
      end;
      /
      show errors
      Is there any functional difference between C1 and C2? Both of them are being used in exactly the same way (i.e. to fetch a row into a varaible). Can C2 be used in a way tha C1 can't, or vice versa?
      Is the point of the RETURN clause simply and only so that we can declare a cursor in a package and alongside it, publically specify what type the cursor returns?

      Many thanks,
      Jason
        • 1. Re: Is there any functional point to the explicit cursor RETURN clause?
          Solomon Yakobson
          With return clause it is called strong cursor. Without it - a weak cursor. It is all documented Creating Cursor Variables.

          SY.
          • 2. Re: Is there any functional point to the explicit cursor RETURN clause?
            9423755
            Thanks Solomon, but I don't think you answered my question (you answered a question about cursor variables; I asked a question about explicit cursors).
            When I say
            cursor c1 is select * from employees;
            cursor c2 return employees%rowtype
                          is select * from employees;
            I am declaring explicit cursors. I'm not declaring cursor variables. My understanding is that
            a) strong-ness and weak-ness make sense in the context of a cursor variable (REF CURSOR or SYS_REFCURSOR), because
            b) A cursor variable may point to different queries at run time.

            An explicit cursor (as in C1 and C2 above) has the query that it points to fixed at compile time (or am I wrong?). If that is the case, does strong-ness or weak-ness of the cursor have any relevance?
            I must be missing something, because this seems an entirely pointless use of the clause RETURN.
            • 3. Re: Is there any functional point to the explicit cursor RETURN clause?
              rp0428
              >
              Is there any functional difference between C1 and C2? Both of them are being used in exactly the same way (i.e. to fetch a row into a varaible). Can C2 be used in a way tha C1 can't, or vice versa?
              >
              No - not if by 'functional difference' you mean at runtime or how the cursor is used then for the example code you provided there is not difference.
              Is the point of the RETURN clause simply and only so that we can declare a cursor in a package and alongside it, publically specify what type the cursor returns?
              >
              Yes - that is ONE point. That makes it a STRONG cursor - meaning that Oracle at COMPILE time (not execution time) can determine what the return type of the cursor MUST BE.

              Then at runtime if you try to use a query for the cursor that returns a different return type Oracle will complain. Remember, at runtime you could open a cursor for a statement stored in a VARCHAR2 string. When Oracle doesn't know what the projection will be until it parses that statement. For a STRONGLY TYPED cursor (e.g. your explict one using RETURN) it will then raise an exception if the projection does not match or if the return type does not match.
              >
              An explicit cursor (as in C1 and C2 above) has the query that it points to fixed at compile time (or am I wrong?). If that is the case, does strong-ness or weak-ness of the cursor have any relevance?
              I must be missing something, because this seems an entirely pointless use of the clause RETURN.
              >
              No - that is wrong. The query can be fixed later and normally isn't parsed until runtime.

              See the PL/SQ Language doc
              http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#BABDHAED
              >
              A session cursor that is constructed and managed by PL/SQL is an implicit cursor. A session cursor that you construct and manage is an explicit cursor.

              Note:

              Generally, PL/SQL parses an explicit cursor only the first time the session opens it and parses a SQL statement (creating an implicit cursor) only the first time the statement runs.
              >
              Notice that last line. That first parse doesn't happen until you open and parse the statement and that is at runtime.
              >
              Explicit Cursors
              An explicit cursor is a session cursor that you construct and manage. You must declare and define an explicit cursor, giving it a name and associating it with a query (typically, the query returns multiple rows). Then you can process the query result set in either of these ways:

              •Open the explicit cursor (with the OPEN statement), fetch rows from the result set (with the FETCH statement), and close the explicit cursor (with the CLOSE statement).

              •Use the explicit cursor in a cursor FOR LOOP statement (see "Query Result Set Processing With Cursor FOR LOOP Statements").
              >
              Then see the examples in that section
              >
              Example 6-5 Explicit Cursor Declaration and Definition

              DECLARE
              CURSOR c1 RETURN departments%ROWTYPE; -- Declare c1

              CURSOR c2 IS -- Declare and define c2
              SELECT employee_id, job_id, salary FROM employees
              WHERE salary > 2000;
              >
              Note the first example declares the cursor and the return type. The cursor would be opened in the BEGIN/END block and the query to be used would specified then (again, possibly at runtime). Oracle would raise an exception if you try to open the cursor for a query that does not have the same return type.

              Strongly typed cursors are 'strong' in that they allow Oracle to know ahead of time what the datatype is going to be. A 'weak' cursor is weak because it could be associated with different result datatype at runtime. The familiar example is a ref cursor variable that can be opened for queries of the EMP table or the DEPT table and then closed and reopened for the other table. You can't do that with STRONG types.
              • 4. Re: Is there any functional point to the explicit cursor RETURN clause?
                9423755
                Thanks rp, for taking the time to respond, but...I'm sorry but I'm none the wiser.

                My understanding is that there are

                1. Implicit cursors (for rec in (select * from employees) loop ... end loop)
                2. Explicit cursors
                cursor c1 is select * from employees;
                cursor c2 return employees%rowtype
                              is select * from employees;
                3. Cursor variables (aka REF CURSORs /SYS_REFCURSORs)

                Also, my understanding is that
                a)cursor variables CAN have their associated sql statements changed at runtime.
                b) explicit cursors (c1 and c2) can NOT have their associated sql statements changed at runtime. If they could, they would be cursor variables, no? Or, have I not got to that point in Feuerstein's book yet? How would you change C1 at runtime to point to a different sql statement?

                Many thanks,
                Jason