Implicit Statement Results in PL/SQL

Implicit statement results in PL/SQL refer to returning the results of a query automatically from a PL/SQL block, procedure, or function without explicitly using cursors or OUT parameters.

Implicit Statement Results, introduced in Oracle 12c, helps developers simplify their code by returning query results directly for them to work with other applications or even client-side APIs. This article will illustrate implicit statement results in detail with necessary syntax, examples, and outputs.

Implicit Statement Results in PL/SQL

Implicit statement results in PL/SQL provide a way to return query results directly from a PL/SQL block, procedure, or function without the need for explicit cursors or OUT parameters. This feature, introduced in Oracle 12c, simplifies the process of handling query results and enhances code readability and maintainability.

By using implicit statement results, developers can streamline their code and efficiently pass query results to applications or client-side APIs.

Syntax:

DECLARE
  v_cursor SYS_REFCURSOR;
BEGIN
  -- Open a cursor for a query
  OPEN v_cursor FOR <SELECT query>;

  -- Return the result set using DBMS_SQL.RETURN_RESULT
  DBMS_SQL.RETURN_RESULT(v_cursor);
END;
/

Explanation:

• v_cursor: A SYS_REFCURSOR variable that holds the result of the query.
  
• OPEN v_cursor FOR <SELECT query>: Opens the cursor and executes the query, storing the result set in the cursor.
  
• DBMS_SQL.RETURN_RESULT(v_cursor): Automatically returns the result set of the query to the client application without explicit fetching.

Syntax with Multiple Result Sets

DECLARE
  v_cursor1 SYS_REFCURSOR;
  v_cursor2 SYS_REFCURSOR;
BEGIN
  -- Open first cursor for the first query
  OPEN v_cursor1 FOR SELECT * FROM employees;
  DBMS_SQL.RETURN_RESULT(v_cursor1);

  -- Open second cursor for the second query
  OPEN v_cursor2 FOR SELECT * FROM departments;
  DBMS_SQL.RETURN_RESULT(v_cursor2);
END;
/

Explanation:

v_cursor1 and v_cursor2: Each SYS_REFCURSOR stores the result of a separate query.

Both cursors are returned sequentially, with the result sets sent back to the client.

Examples of Implicit Statement Results in PL/SQL

The PL/SQL block demonstrates how to return a single result set implicitly. Within the FOR loop, the query selects employee_id, first_name, and last_name from the employees table where department_id is 10.

Returning a Single Result Set

The DBMS_SQL.RETURN_RESULT procedure sends this result set directly to the client tool, such as SQL*Plus or SQL Developer, which automatically displays the results without needing to explicitly handle cursors.

Query:

BEGIN
   -- Returning result set implicitly
   FOR result IN (SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10) 
   LOOP
      -- Output each row (SQL*Plus or similar tool will automatically display the result set)
      DBMS_SQL.RETURN_RESULT(result);
   END LOOP;
END;
/

Output:

Explanation:

The PL/SQL below executes a query within the FOR loop for the employee_id, first_name, and last_name of employees in the employees table whose department_id is 10. By default, DBMS_SQL.RETURN_RESULT sends the result set to the client tool through SQL*Plus or SQL Developer and displays it without explicitly defining and opening a cursor.

Returning Multiple Result Sets

This PL/SQL block demonstrates how to return multiple result sets within a single block. It first retrieves and returns employee data for department_id 20, followed by returning department data for location_id 1700.

The DBMS_SQL.RETURN_RESULT procedure is called twice—once for each query—to send both result sets sequentially to the client tool for display.

Query:

BEGIN
   -- First result set
   FOR result IN (SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 20)
   LOOP
      DBMS_SQL.RETURN_RESULT(result);
   END LOOP;

   -- Second result set
   FOR result IN (SELECT department_id, department_name FROM departments WHERE location_id = 1700)
   LOOP
      DBMS_SQL.RETURN_RESULT(result);
   END LOOP;
END;
/

Output:

Explanation:

• This PL/SQL block uses implicit statement results to return two result sets.
  
• The first result set returns the employees from the employees table whose department_id is 20, fetching into employee_id, first_name, and last_name, returning the result via DBMS_SQL.RETURN_RESULT.
  
• Then it selects the department_id and department_name from the departments table for location_id 1700 and returns this second result set in the same way. The client tool will then display both result sets, one after the other.

Using the get_next_result() procedure

The get_next_result() procedure can be conceptually viewed as one that iterates through the various result sets returned by a single execution in those databases supporting multiple result sets, like Oracle with dynamic SQL and some APIs.

Though the latter is not available in Oracle PL/SQL, the same could be emulated using cursors and dynamic SQL.

Query:

DECLARE
    -- Define cursors for multiple result sets
    CURSOR c1 IS
        SELECT employee_id, first_name FROM employees;
        
    CURSOR c2 IS
        SELECT department_id, department_name FROM departments;
        
    -- Record types for fetching results
    emp_record c1%ROWTYPE;
    dept_record c2%ROWTYPE;
    
BEGIN
    -- Open and process the first cursor
    OPEN c1;
    LOOP
        FETCH c1 INTO emp_record;
        EXIT WHEN c1%NOTFOUND;
        -- Process each row from the first result set
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.employee_id);
        DBMS_OUTPUT.PUT_LINE('First Name: ' || emp_record.first_name);
    END LOOP;
    CLOSE c1;
    
    -- Open and process the second cursor
    OPEN c2;
    LOOP
        FETCH c2 INTO dept_record;
        EXIT WHEN c2%NOTFOUND;
        -- Process each row from the second result set
        DBMS_OUTPUT.PUT_LINE('Department ID: ' || dept_record.department_id);
        DBMS_OUTPUT.PUT_LINE('Department Name: ' || dept_record.department_name);
    END LOOP;
    CLOSE c2;
    
END;
/

Output:

Explanation:

• The following PL/SQL block demonstrates how to handle more than one result set through cursors. Two cursors are initialized inside
  
• c1 returns the employee_id and first_name from the employees table c2 returns the department_id and department_name from the departments table Both of them have an associated record type
  
• emp_record in case of c1 and dept_record in case of c2 for storing the retrieved data.
  
• BEGIN In the BEGIN section, the code opens cursor c1 and fetches each row in order, printing the details of the employee, including ID and first name.
  
• Once all the rows are processed, it closes the cursor.

Using Implicit Statement Results in PL/SQL Blocks

Next, we will illustrate - through examples and explanations - how to work with implicit statement results within PL/SQL blocks.Implicit statement results can be returned from an anonymous PL/SQL block.

To do this, you need to create a cursor for your query and then use the DBMS_SQL.RETURN_RESULT to return the results.

Query:

BEGIN
  DECLARE
    v_cursor SYS_REFCURSOR;
  BEGIN
    -- Open a cursor for the SELECT query
    OPEN v_cursor FOR SELECT employee_id, first_name, last_name FROM employees;

    -- Return the result set to the client
    DBMS_SQL.RETURN_RESULT(v_cursor);
  END;
END;
/

Output:

Explanation:

• This PL/SQL block uses SYS_REFCURSOR to return two result sets. The first query opens a cursor, v_cursor1, that selects all rows in the employees table. Its result is then returned using DBMS_SQL.RETURN_RESULT.
  
• The second query opens another cursor, v_cursor2, for selecting all rows from the departments table, and its result is also returned. Both result sets will return and appear consecutively in the client tool, say, SQL*Plus or SQL Developer.
  
• In this way, more than one result set can return under a single PL/SQL block.

Handling Multiple Result Sets

Returning Multiple Result Sets You can return more than one result set in a single PL/SQL block by calling DBMS_SQL.RETURN_RESULT more than once. Each result set is returned in succession.

Query:

BEGIN
  DECLARE
    v_cursor1 SYS_REFCURSOR;
    v_cursor2 SYS_REFCURSOR;
  BEGIN
    -- Open first cursor for employees
    OPEN v_cursor1 FOR SELECT employee_id, first_name FROM employees;
    DBMS_SQL.RETURN_RESULT(v_cursor1);

    -- Open second cursor for departments
    OPEN v_cursor2 FOR SELECT department_id, department_name FROM departments;
    DBMS_SQL.RETURN_RESULT(v_cursor2);
  END;
END;
/

Explanation:

Multiple Cursors: Two SYS_REFCURSOR variables (v_cursor1 and v_cursor2) are used to hold the results of two separate queries.

Sequential Return: Each result set is returned in sequence using DBMS_SQL.RETURN_RESULT.

Conclusion

Implicit statement results in PL/SQL make it a lot easier and faster to return query results from PL/SQL blocks, procedures, and functions. This is because you don't have the overhead of explicit cursors and OUT parameters-and most importantly, it cleans up your code and is more efficient.

This comes in handy if you are working with applications that require multiple result sets, such as reporting tools or client applications using JDBC or OCI. Implicit statement results improve code readability and reduces complexity. Implicit statement results provide a complete modern approach toward returning data from PL/SQL blocks in Oracle 12c onwards.