How Can I Display SELECT Statement Results within a PL/SQL Block?

Displaying SELECT Statement Results within PL/SQL Blocks

This guide demonstrates how to display SELECT statement results within a PL/SQL block, mimicking the behavior of a standalone SELECT statement. A simple SELECT statement alone won't suffice; alternative methods are necessary.

Oracle 12.1 and Later

Oracle 12.1 and later versions support implicit result sets:

<code class="language-sql">DECLARE
    rc SYS_REFCURSOR;
BEGIN
    OPEN rc FOR SELECT * FROM dual;
    DBMS_SQL.RETURN_RESULT(rc);
END;
/</code>

Older Oracle Versions (Pre-12.1)

For earlier Oracle versions, utilize ref cursor bind variables:

<code class="language-sql">SET AUTOPRINT ON;

VARIABLE rc REFCURSOR;

BEGIN
    OPEN :rc FOR SELECT COUNT(*) FROM dual;
END;
/
PRINT rc;</code>

This binds the ref cursor variable to a SQL*Plus statement, enabling result display as if integrated within the PL/SQL block.

Summary

The approach to displaying SELECT results within PL/SQL varies slightly based on the Oracle version. However, the methods outlined above offer effective solutions for handling queries and data management within PL/SQL procedures. They provide flexibility for working with complex queries.

The above is the detailed content of How Can I Display SELECT Statement Results within a PL/SQL Block?. For more information, please follow other related articles on the PHP Chinese website!