Use the DBMS_SQL package for dynamic SQL statements - PolarDB - Alibaba Cloud - PolarDB

The DBMS_SQL package lets you build and execute dynamic SQL queries at runtime in PolarDB for PostgreSQL (Compatible with Oracle).

PolarDB for PostgreSQL (Oracle Compatible) supports dynamic SQL in a manner compatible with Oracle.

Table 1. DBMS_SQL Functions and Stored Procedures
Function/stored procedure	Type	Return type	Description
BIND_VARIABLE(c, name, value [, out_value_size ])	stored procedure	N/A	Binds a value to a variable.
BIND_VARIABLE_CHAR(c, name, value [, out_value_size ])	stored procedure	N/A	Binds a CHAR value to a variable.
BIND_VARIABLE_RAW(c, name, value [, out_value_size ])	stored procedure	N/A	Binds a RAW value to a variable.
CLOSE_CURSOR(c IN OUT)	stored procedure	N/A	Closes a cursor.
COLUMN_VALUE(c, position, value OUT [, column_error OUT [, actual_length OUT ]])	stored procedure	N/A	Assigns a column's value to a variable.
COLUMN_VALUE_CHAR(c, position, value OUT [, column_error OUT [, actual_length OUT ]])	stored procedure	N/A	Assigns a CHAR column's value to a variable.
COLUMN_VALUE_RAW(c, position, value OUT [, column_error OUT [, actual_length OUT ]])	stored procedure	N/A	Assigns a RAW column's value to a variable.
DEFINE_COLUMN(c, position, column [, column_size ])	stored procedure	N/A	Defines a column in the SELECT list.
DEFINE_COLUMN_CHAR(c, position, column, column_size)	stored procedure	N/A	Defines a CHAR column in the SELECT list.
DEFINE_COLUMN_RAW(c, position, column, column_size)	stored procedure	N/A	Defines a RAW column in the SELECT list.
DEFINE_ARRAY(c,position,table_variable,cnt, lower_bnd)	stored procedure	N/A	Defines a column for bulk fetching into an array.
DESCRIBE_COLUMNS	stored procedure	N/A	Describes the columns for a given cursor.
EXECUTE(c)	function	INTEGER	Executes a cursor.
EXECUTE_AND_FETCH(c [, exact ])	function	INTEGER	Executes a cursor and fetches one row.
FETCH_ROWS(c)	function	INTEGER	Returns the number of rows fetched from a cursor.
IS_OPEN(c)	function	BOOLEAN	Checks if a cursor is open.
LAST_ROW_COUNT	function	INTEGER	Returns the cumulative number of rows fetched.
OPEN_CURSOR	function	INTEGER	Opens a cursor.
PARSE(c, statement, language_flag)	stored procedure	N/A	Parses a statement.

Compared to Oracle, PolarDB offers a partial implementation of the DBMS_SQL package. PolarDB supports only the functions and stored procedures listed in the preceding table.

The following table lists the public variables available in the DBM_SQL package.

Table 2. DBMS_SQL public variables
Parameter	Type	Value	Description
native	INTEGER	1	Compatible with Oracle syntax. See DBMS_SQL.PARSE for details.
V6	INTEGER	2	Compatible with Oracle syntax. See DBMS_SQL.PARSE for details.
V7	INTEGER	3	Compatible with Oracle syntax. See DBMS_SQL.PARSE for details.

BIND_VARIABLE

The BIND_VARIABLE procedure assigns a value to an IN or IN OUT bind variable in an SQL statement.

BIND_VARIABLE(c INTEGER, name VARCHAR2,
  value { BLOB | CLOB | DATE | FLOAT | INTEGER | NUMBER |
          TIMESTAMP | VARCHAR2 }
  [, out_value_size INTEGER ])

Parameters

Parameter	Description
c	The ID of the cursor for the SQL statement that contains the bind variable.
name	The name of the bind variable in the SQL statement.
value	The value to assign.
out_value_size	`name`The maximum length of the output value for an bind variable. If omitted, this length defaults to the length of the current value.

Example

The following anonymous block uses bind variables to insert a row into the emp table.

DECLARE
    curid           INTEGER;
    v_sql           VARCHAR2(150) := 'INSERT INTO emp VALUES ' ||
                        '(:p_empno, :p_ename, :p_job, :p_mgr, ' ||
                        ':p_hiredate, :p_sal, :p_comm, :p_deptno)';
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    v_job           emp.job%TYPE;
    v_mgr           emp.mgr%TYPE;
    v_hiredate      emp.hiredate%TYPE;
    v_sal           emp.sal%TYPE;
    v_comm          emp.comm%TYPE;
    v_deptno        emp.deptno%TYPE;
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    v_empno    := 9001;
    v_ename    := 'JONES';
    v_job      := 'SALESMAN';
    v_mgr      := 7369;
    v_hiredate := TO_DATE('13-DEC-07','DD-MON-YY');
    v_sal      := 8500.00;
    v_comm     := 1500.00;
    v_deptno   := 40;
    DBMS_SQL.BIND_VARIABLE(curid,':p_empno',v_empno);
    DBMS_SQL.BIND_VARIABLE(curid,':p_ename',v_ename);
    DBMS_SQL.BIND_VARIABLE(curid,':p_job',v_job);
    DBMS_SQL.BIND_VARIABLE(curid,':p_mgr',v_mgr);
    DBMS_SQL.BIND_VARIABLE(curid,':p_hiredate',v_hiredate);
    DBMS_SQL.BIND_VARIABLE(curid,':p_sal',v_sal);
    DBMS_SQL.BIND_VARIABLE(curid,':p_comm',v_comm);
    DBMS_SQL.BIND_VARIABLE(curid,':p_deptno',v_deptno);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

Output:

Number of rows processed: 1

BIND_VARIABLE_CHAR

The BIND_VARIABLE_CHAR stored procedure binds a CHAR value to an IN or IN OUT bind variable in an SQL statement.

BIND_VARIABLE_CHAR(c INTEGER, name VARCHAR2, value CHAR
  [, out_value_size INTEGER ])

Parameters

Parameter	Description
c	Cursor ID for the SQL statement that contains the bind variable.
name	Name of the bind variable in the SQL statement.
value	The CHAR value to assign.
out_value_size	`name`The maximum length of the output value for a bind variable in mode. If this parameter is omitted, the maximum length defaults to the length of the parameter.

BIND_VARIABLE_RAW

BIND_VARIABLE_RAW binds a RAW value to an IN or IN OUT bind variable in an SQL statement.

BIND_VARIABLE_RAW(c INTEGER, name VARCHAR2, value RAW
  [, out_value_size INTEGER ])

Parameters

Parameter	Description
c	Cursor ID of the SQL statement containing the bind variable.
name	Name of the bind variable in the SQL statement.
value	The RAW value to assign.
out_value_size	`name`The maximum length of the output value for an IN OUT bind variable. If this parameter is omitted, the maximum length defaults to the length of the current value.

CLOSE_CURSOR

The CLOSE_CURSOR stored procedure closes an open cursor, releasing its allocated resources. A closed cursor cannot be used again.

CLOSE_CURSOR(c IN OUT INTEGER)

Parameters

Parameter	Description
c	The ID of the cursor to close.

Example

The following example closes an open cursor:

DECLARE
    curid           INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    -- ... parse, execute, fetch ...
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

COLUMN_VALUE

The COLUMN_VALUE stored procedure reads a value from the current row of a cursor into a variable.

COLUMN_VALUE(c INTEGER, position INTEGER, value OUT { BLOB |
  CLOB | DATE | FLOAT | INTEGER | NUMBER | TIMESTAMP | VARCHAR2 }
  [, column_error OUT NUMBER [, actual_length OUT INTEGER ]])

Parameters

Parameter	Description
c	The ID of the cursor.
position	The 1-based position of the column in the cursor's result set.
value	An OUT variable that stores the fetched column data.
column_error	The error code if a column-specific error occurs.
actual_length	The actual length of the column data before any truncation.

Example

The following example is an excerpt from an anonymous block that uses the COLUMN_VALUE stored procedure to read data from a cursor.

DECLARE
    curid           INTEGER;
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
            .
            .
            .
    LOOP
        v_status := DBMS_SQL.FETCH_ROWS(curid);
        EXIT WHEN v_status = 0;
        DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);
        DBMS_SQL.COLUMN_VALUE(curid,2,v_ename);
        DBMS_SQL.COLUMN_VALUE(curid,3,v_hiredate);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,5,v_comm);
        DBMS_OUTPUT.PUT_LINE(v_empno || '   ' || RPAD(v_ename,10) || '  ' ||
            TO_CHAR(v_hiredate,'yyyy-mm-dd') || ' ' ||
            TO_CHAR(v_sal,'9,999.99') || ' ' ||
            TO_CHAR(NVL(v_comm,0),'9,999.99'));
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

COLUMN_VALUE_CHAR

The stored procedure COLUMN_VALUE_CHAR retrieves a CHAR value from a cursor into a variable.

COLUMN_VALUE_CHAR(c INTEGER, position INTEGER, value OUT CHAR
  [, column_error OUT NUMBER [, actual_length OUT INTEGER ]])

Parameters

Parameter	Description
c	The cursor ID.
position	The 1-based position of the column in the cursor's result set.
value	The CHAR variable that receives the column data from the most recent fetch.
column_error	The resulting error code for the column.
actual_length	The actual length of the data before any truncation.

COLUMN_VALUE_RAW

The COLUMN_VALUE_RAW stored procedure retrieves a RAW value from a cursor.

COLUMN_VALUE_RAW(c INTEGER, position INTEGER, value OUT RAW
  [, column_error OUT NUMBER [, actual_length OUT INTEGER ]])

Parameters

Parameter	Description
c	The ID of the cursor.
position	The 1-based position of the column in the cursor's result set.
value	The RAW variable that receives the column data from the most recent fetch operation.
column_error	Returns the column's error code if an error occurs.
actual_length	The actual length of the data before truncation.

DEFINE_COLUMN

The DEFINE_COLUMN stored procedure defines a column or expression in the select list to be fetched from the cursor.

DEFINE_COLUMN(c INTEGER, position INTEGER, column { BLOB |
  CLOB | DATE | FLOAT | INTEGER | NUMBER | TIMESTAMP | VARCHAR2 }
  [, column_size INTEGER ])

Parameters

Parameter	Description
c	The cursor ID associated with the SELECT statement.
position	The 1-based position of the column or expression in the select list.
column	A variable with a data type that matches the column or expression at the specified in the select list.
column_size	The maximum expected length of the returned data. If the column data type is VARCHAR2, you must specify . Data that exceeds is truncated.

Example

The following example uses the DEFINE_COLUMN stored procedure to define the empno, ename, hiredate, sal, and comm columns of the emp table.

DECLARE
    curid           INTEGER;
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_ename,10);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_hiredate);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_sal);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_comm);
            .
            .
            .
END;

The following shows another implementation for the previous example, which produces the exact same result. Note that the length of the returned data is independent of the length of the data type. The lengths of the data returned for the empno, sal, and comm columns are determined by their respective NUMBER(4) and NUMBER(7,2) data types, even though v_num is defined as NUMBER(1). The length of the data returned for the ename column can be up to the length parameter defined in the DEFINE_COLUMN stored procedure, not the VARCHAR2(1) type in the declaration for v_varchar. The actual length of the returned data is specified by the DEFINE_COLUMN stored procedure.

DECLARE
    curid           INTEGER;
    v_num           NUMBER(1);
    v_varchar       VARCHAR2(1);
    v_date          DATE;
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_num);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_varchar,10);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_date);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_num);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_num);
            .
            .
            .
END;

DEFINE_COLUMN_CHAR

The stored procedure DEFINE_COLUMN_CHAR defines a CHAR column or expression in a SELECT list to retrieve from a cursor.

DEFINE_COLUMN_CHAR(c INTEGER, position INTEGER, column CHAR, column_size INTEGER)

Parameters

Parameter	Description
c	The cursor ID for the SELECT statement.
position	The 1-based position of the column or expression in the SELECT list.
column	A CHAR variable.
column_size	The maximum length of the returned data. Data exceeding column_size is truncated.

DEFINE_COLUMN_RAW

The DEFINE_COLUMN_RAW stored procedure defines a RAW column or expression in a SELECT list for retrieval from a cursor.

DEFINE_COLUMN_RAW(c INTEGER, position INTEGER, column RAW,
  column_size INTEGER)

Parameters

Parameter	Description
c	The cursor ID associated with the SELECT statement.
position	The position of the column or expression in the SELECT list.
column	A RAW variable.
column_size	The maximum length of the returned data. Data longer than column_size is truncated.

DEFINE_ARRAY

The DEFINE_ARRAY stored procedure defines a column in a select list to be fetched into an array. The syntax is as follows:

DEFINE_ARRAY (
   c           IN INTEGER,
   position    IN INTEGER,
   <table_variable>    IN <datatype>,
   cnt         IN INTEGER,
   lower_bnd   IN INTEGER);

Parameters

Parameter	Description
c	The ID of the cursor for the query.
position	The 1-based position of the column in the select list.
table_variable	A local variable declared with one of the following array data types: varchar2_table clob_table binary_float_table binary_double_table blob_table date_table number_table timestamp_table
cnt	The number of rows to fetch. This value must be a positive integer.
lower_bnd	The starting index in the array for storing the fetched rows.

Example

The following anonymous block creates a table named t, fetches two rows from the t table, and prints the values from the first column.

create table t as select i as a,2 * i as b,3 * i as c from generate_series(1,3) i;

DECLARE
  c      INTEGER;
  d      NUMBER;
  n_tab  dbms_sql.varchar2_Table;
  n_tab1  dbms_sql.varchar2_Table;
BEGIN
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c,
                 'select * from t',
                 dbms_sql.native);
  dbms_sql.define_array(c,1,n_tab,2,1);
  d := dbms_sql.execute(c);
  d := dbms_sql.fetch_rows(c);
  dbms_output.put_line('fetch rows is ' || d);
  dbms_sql.column_value(c,
                          1,
                          n_tab1);
    FOR i IN 1 .. d LOOP
      dbms_output.put_line(n_tab1(i));
    END LOOP;
  dbms_sql.close_cursor(c);
END;

Output:

fetch rows is 2
1
2

DESCRIBE_COLUMNS

The DESCRIBE_COLUMNS stored procedure describes the columns in a cursor's result set.

DESCRIBE_COLUMNS(c INTEGER, col_cnt OUT INTEGER, desc_t OUT
  DESC_TAB);

Parameters

Parameter	Description
c	The cursor ID.
col_cnt	The number of columns in the cursor's result set.
desc_tab	An OUT table of DESC_REC records describing each column in the result set. The following table describes the fields in each record.

Field	Type
col_type	INTEGER
col_max_len	INTEGER
col_name	VARCHAR2(128)
col_name_len	INTEGER
col_schema_name	VARCHAR2(128)
col_schema_name_len	INTEGER
col_precision	INTEGER
col_scale	INTEGER
col_charsetid	INTEGER
col_charsetform	INTEGER
col_null_ok	BOOLEAN

EXECUTE

EXECUTE runs a parsed SQL statement or SPL block.

status INTEGER EXECUTE(c INTEGER)

Parameters

Parameter	Description
c	The cursor ID of the parsed SQL statement or SPL block.
status	The number of rows processed for DELETE, INSERT, or UPDATE statements. This value is not meaningful for other statement types.

Example

The following anonymous block inserts a row into the dept table.

DECLARE
    curid           INTEGER;
    v_sql           VARCHAR2(50);
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    v_sql := 'INSERT INTO dept VALUES (50, ''HR'', ''LOS ANGELES'')';
    DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

EXECUTE_AND_FETCH

The EXECUTE_AND_FETCH function executes a parsed SELECT statement and fetches one row from the result set.

status INTEGER EXECUTE_AND_FETCH(c INTEGER
  [, exact BOOLEAN ])

Parameters

Parameter	Description
c	The cursor ID for the SELECT statement.
exact	If `FALSE` (default), no exception is raised. If `TRUE`, an exception is raised unless the result set has exactly one row. If `TRUE` and the result set is empty, a `NO_DATA_FOUND` exception is raised. If `TRUE` and the result set has more than one row, a `TOO_MANY_ROWS` exception is raised.
status	Returns `1` if a row is successfully fetched. Returns `0` if no row is fetched. Does not return a value if an exception is raised.

Example

The following stored procedure uses the EXECUTE_AND_FETCH function to retrieve an employee row by name. It raises an exception if it does not find exactly one matching employee.

CREATE OR REPLACE PROCEDURE select_by_name(
    p_ename         emp.ename%TYPE
)
IS
    curid           INTEGER;
    v_empno         emp.empno%TYPE;
    v_hiredate      emp.hiredate%TYPE;
    v_sal           emp.sal%TYPE;
    v_comm          emp.comm%TYPE;
    v_dname         dept.dname%TYPE;
    v_disp_date     VARCHAR2(10);
    v_sql           VARCHAR2(120) := 'SELECT empno, hiredate, sal, ' ||
                                     'NVL(comm, 0), dname ' ||
                                     'FROM emp e, dept d ' ||
                                     'WHERE ename = :p_ename ' ||
                                     'AND e.deptno = d.deptno';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.BIND_VARIABLE(curid,':p_ename',UPPER(p_ename));
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_hiredate);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_sal);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_comm);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_dname,14);
    v_status := DBMS_SQL.EXECUTE_AND_FETCH(curid,TRUE);
    DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);
    DBMS_SQL.COLUMN_VALUE(curid,2,v_hiredate);
    DBMS_SQL.COLUMN_VALUE(curid,3,v_sal);
    DBMS_SQL.COLUMN_VALUE(curid,4,v_comm);
    DBMS_SQL.COLUMN_VALUE(curid,5,v_dname);
    v_disp_date := TO_CHAR(v_hiredate, 'MM/DD/YYYY');
    DBMS_OUTPUT.PUT_LINE('Number    : ' || v_empno);
    DBMS_OUTPUT.PUT_LINE('Name      : ' || UPPER(p_ename));
    DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date);
    DBMS_OUTPUT.PUT_LINE('Salary    : ' || v_sal);
    DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm);
    DBMS_OUTPUT.PUT_LINE('Department: ' || v_dname);
    DBMS_SQL.CLOSE_CURSOR(curid);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee ' || p_ename || ' not found');
        DBMS_SQL.CLOSE_CURSOR(curid);
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Too many employees named, ' ||
            p_ename || ', found');
        DBMS_SQL.CLOSE_CURSOR(curid);
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:');
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
        DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:');
        DBMS_OUTPUT.PUT_LINE(SQLCODE);
        DBMS_SQL.CLOSE_CURSOR(curid);
END;

The output is as follows:

EXEC select_by_name('MARTIN')

Number    : 7654
Name      : MARTIN
Hire Date : 09/28/1981
Salary    : 1250
Commission: 1400
Department: SALES

FETCH_ROWS

The FETCH_ROWS function fetches rows from a cursor.

status INTEGER FETCH_ROWS(c INTEGER)

Parameters

Parameter	Description
c	The cursor ID from which to fetch rows.
status	Returns 1 if a row is successfully fetched, or 0 if no more rows are available.

Example

This example fetches rows from the emp table and displays the results.

DECLARE
    curid           INTEGER;
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_ename,10);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_hiredate);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_sal);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_comm);

    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('EMPNO  ENAME       HIREDATE    SAL       COMM');
    DBMS_OUTPUT.PUT_LINE('-----  ----------  ----------  --------  ' ||
        '--------');
    LOOP
        v_status := DBMS_SQL.FETCH_ROWS(curid);
        EXIT WHEN v_status = 0;
        DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);
        DBMS_SQL.COLUMN_VALUE(curid,2,v_ename);
        DBMS_SQL.COLUMN_VALUE(curid,3,v_hiredate);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,5,v_comm);
        DBMS_OUTPUT.PUT_LINE(v_empno || '   ' || RPAD(v_ename,10) || '  ' ||
            TO_CHAR(v_hiredate,'yyyy-mm-dd') || ' ' ||
            TO_CHAR(v_sal,'9,999.99') || ' ' ||
            TO_CHAR(NVL(v_comm,0),'9,999.99'));
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

The output is as follows:

EMPNO  ENAME       HIREDATE    SAL       COMM
-----  ----------  ----------  --------  --------
7369   SMITH       1980-12-17    800.00       .00
7499   ALLEN       1981-02-20  1,600.00    300.00
7521   WARD        1981-02-22  1,250.00    500.00
7566   JONES       1981-04-02  2,975.00       .00
7654   MARTIN      1981-09-28  1,250.00  1,400.00
7698   BLAKE       1981-05-01  2,850.00       .00
7782   CLARK       1981-06-09  2,450.00       .00
7788   SCOTT       1987-04-19  3,000.00       .00
7839   KING        1981-11-17  5,000.00       .00
7844   TURNER      1981-09-08  1,500.00       .00
7876   ADAMS       1987-05-23  1,100.00       .00
7900   JAMES       1981-12-03    950.00       .00
7902   FORD        1981-12-03  3,000.00       .00
7934   MILLER      1982-01-23  1,300.00       .00

IS_OPEN

The IS_OPEN function tests whether a cursor is open.

status BOOLEAN IS_OPEN(c INTEGER)

Parameters

Parameter	Description
c	The ID of the cursor to check.
status	Returns TRUE if the cursor is open, or FALSE if it is closed.

Last row count

The LAST_ROW_COUNT function returns the total number of rows that have been retrieved.

rowcnt INTEGER LAST_ROW_COUNT

Parameters

Parameter	Description
rowcnt	The total number of rows fetched.

Example

In the following example, use the LAST_ROW_COUNT function to query the total number of retrieved rows.

DECLARE
    curid           INTEGER;
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_ename,10);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_hiredate);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_sal);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_comm);

    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('EMPNO  ENAME       HIREDATE    SAL       COMM');
    DBMS_OUTPUT.PUT_LINE('-----  ----------  ----------  --------  ' ||
        '--------');
    LOOP
        v_status := DBMS_SQL.FETCH_ROWS(curid);
        EXIT WHEN v_status = 0;
        DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);
        DBMS_SQL.COLUMN_VALUE(curid,2,v_ename);
        DBMS_SQL.COLUMN_VALUE(curid,3,v_hiredate);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,5,v_comm);
        DBMS_OUTPUT.PUT_LINE(v_empno || '   ' || RPAD(v_ename,10) || '  ' ||
            TO_CHAR(v_hiredate,'yyyy-mm-dd') || ' ' ||
            TO_CHAR(v_sal,'9,999.99') || ' ' ||
            TO_CHAR(NVL(v_comm,0),'9,999.99'));
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Number of rows: ' || DBMS_SQL.LAST_ROW_COUNT);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

The output is as follows:

EMPNO  ENAME       HIREDATE    SAL       COMM
-----  ----------  ----------  --------  --------
7369   SMITH       1980-12-17    800.00       .00
7499   ALLEN       1981-02-20  1,600.00    300.00
7521   WARD        1981-02-22  1,250.00    500.00
7566   JONES       1981-04-02  2,975.00       .00
7654   MARTIN      1981-09-28  1,250.00  1,400.00
7698   BLAKE       1981-05-01  2,850.00       .00
7782   CLARK       1981-06-09  2,450.00       .00
7788   SCOTT       1987-04-19  3,000.00       .00
7839   KING        1981-11-17  5,000.00       .00
7844   TURNER      1981-09-08  1,500.00       .00
7876   ADAMS       1987-05-23  1,100.00       .00
7900   JAMES       1981-12-03    950.00       .00
7902   FORD        191-12-03  3,000.00       .00
7934   MILLER      1982-01-23  1,300.00       .00
Number of rows: 14

OPEN_CURSOR

The OPEN_CURSOR function opens a new cursor. You must use a cursor to parse and execute a dynamic SQL statement. You can reuse an open cursor with the same or different SQL statements without closing and reopening it.

c INTEGER OPEN_CURSOR

Parameters

Parameter	Description
c	The returned cursor ID.

Example

The following example creates a new cursor.

DECLARE
    curid           INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
            -- ... other operations ...
END;

PARSE

The PARSE stored procedure parses an SQL statement or an SPL block. If the SQL statement is a DDL statement, it is executed immediately without requiring a call to the EXECUTE function.

PARSE(c INTEGER, statement VARCHAR2, language_flag INTEGER)

Parameters

Parameter	Description
c	The ID of an open cursor.
statement	The SQL statement or SPL block to parse. An SQL statement must not end with a semicolon, whereas an SPL block must end with a semicolon.
language_flag	Provides compatibility with Oracle syntax. Valid values are `DBMS_SQL.V6`, `DBMS_SQL.V7`, or `DBMS_SQL.native`. You can ignore this parameter, as all statements are processed using the PolarDB for PostgreSQL (Compatible with Oracle) syntax.

Example

The following anonymous block creates a table named job. Note that the PARSE stored procedure immediately executes DDL statements, so a separate call to the EXECUTE function is not required.

DECLARE
    curid           INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid, 'CREATE TABLE job (jobno NUMBER(3), ' ||
        'jname VARCHAR2(9))',DBMS_SQL.native);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

The following block inserts two rows into the job table.

DECLARE
    curid           INTEGER;
    v_sql           VARCHAR2(50);
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    v_sql := 'INSERT INTO job VALUES (100, ''ANALYST'')';
    DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);
    v_sql := 'INSERT INTO job VALUES (200, ''CLERK'')';
    DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

The output is as follows:

Number of rows processed: 1
Number of rows processed: 1

The following anonymous block uses the DBMS_SQL package to execute an SPL block that contains two INSERT statements. Note that the SPL block must end with a semicolon. In contrast, in the OPEN_CURSOR example, the individual INSERT statements do not end with a semicolon.

DECLARE
    curid           INTEGER;
    v_sql           VARCHAR2(100);
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    v_sql := 'BEGIN ' ||
               'INSERT INTO job VALUES (300, ''MANAGER''); '  ||
               'INSERT INTO job VALUES (400, ''SALESMAN''); ' ||
             'END;';
    DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;