DBMS_SQL - PolarDB - Alibaba Cloud Documentation Center

The DBMS_SQL package supports dynamic SQL queries in PolarDB for PostgreSQL(Compatible with Oracle). You can build queries during runtime of the application.

PolarDB for PostgreSQL(Compatible with Oracle) offers native support for dynamic SQL statements and provides a method of using dynamic SQL statements that is compatible with Oracle databases.

Table 1. DBMS_SQL functions and stored procedures
Function or stored procedure	Type	Return value 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	Disables a cursor.
COLUMN_VALUE(c, position, value OUT [, column_error OUT [, actual_length OUT ]])	Stored procedure	N/A	Returns a column value into a variable.
COLUMN_VALUE_CHAR(c, position, value OUT [, column_error OUT [, actual_length OUT ]])	Stored procedure	N/A	Returns a CHAR column value into a variable.
COLUMN_VALUE_RAW(c, position, value OUT [, column_error OUT [, actual_length OUT ]])	Stored procedure	N/A	Returns a RAW column value into 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 the columns into which you want to fetch rows as arrays.
DESCRIBE_COLUMNS	Stored procedure	N/A	Defines columns to hold a cursor result set.
EXECUTE(c)	Function	INTEGER	Executes a cursor.
EXECUTE_AND_FETCH(c [, exact ])	Function	INTEGER	Executes a cursor and fetches a single row.
FETCH_ROWS(c)	Function	INTEGER	Fetches rows from the cursor.
IS_OPEN(c)	Function	BOOLEAN	Checks whether a cursor is enabled.
LAST_ROW_COUNT	Function	INTEGER	Returns the cumulative number of rows fetched.
OPEN_CURSOR	Function	INTEGER	Enables a cursor
PARSE(c, statement, language_flag)	Stored procedure	N/A	Parses a statement.

The DBMS_SQL package in PolarDB is partially implemented when compared to Oracle DBMS_SQL package. PolarDB only supports the functions and stored procedures that are listed in the preceding table.

The following table lists the public variables that are available in the DBMS_SQL package.

Table 2. DBMS_SQL public variables
Public variable	Data type	Value	Description
native	INTEGER	1	Used for compatibility with Oracle syntax. For more information, see DBMS_SQL.PARSE.
V6	INTEGER	2	Used for compatibility with Oracle syntax. For more information, see DBMS_SQL.PARSE.
V7	INTEGER	3	Used for compatibility with Oracle syntax. For more information, see DBMS_SQL.PARSE.

BIND_VARIABLE

The BIND_VARIABLE stored procedure is used to bind 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 which contains bind variables.
name	The name of the bind variable in the SQL statement.
value	The value to be assigned.
out_value_size	If `name` is an IN OUT variable, this parameter defines the maximum length of the output value. If this parameter is not specified, the length of the current value is the maximum length by default.

Examples

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;

A similar output is displayed:

Number of rows processed: 1

BIND_VARIABLE_CHAR

The BIND_VARIABLE_CHAR stored procedure is used to bind 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	The ID of the cursor for the SQL statement which contains bind variables.
name	The name of the bind variable in the SQL statement.
value	The value of type CHAR to be assigned.
out_value_size	If name is an IN OUT variable, this parameter defines the maximum length of the output value. If this parameter is not specified, the length of the current value is the maximum length by default.

BIND_VARIABLE_RAW

The BIND_VARIABLE_RAW stored procedure is used to bind 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	The ID of the cursor for the SQL statement which contains bind variables.
name	The name of the bind variable in the SQL statement.
value	The value of type CHAR to be assigned.
out_value_size	If `name` is an IN OUT variable, this parameter defines the maximum length of the output value. If this parameter is not specified, the length of the current value is the maximum length by default.

CLOSE_CURSOR

The CLOSE_CURSOR stored procedure is used to disable a cursor. When the cursor is disabled, resources allocated to the cursor are released and the cursor can no longer be used.

CLOSE_CURSOR(c IN OUT INTEGER)

Parameters


Parameter	Description
c	The ID of the cursor.

Examples

The following example shows how to disable a cursor.

DECLARE
    curid           INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
            .
            .
            .
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

COLUMN_VALUE

The COLUMN_VALUE stored procedure is used to define a variable to receive a value from a cursor.

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 that returns data to the variable being defined.
position	The position of the returned data within the cursor. The first value in the cursor is position 1.
value	The variable that receives the data returned in the cursor by a prior FETCH call.
column_error	If an error occurs, this parameter indicates the error code associated with the column.
actual_length	The actual length of the data before truncation.

Examples

The following example shows the portion of an anonymous block that receives the values from a cursor by using the COLUMN_VALUE stored procedure.

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,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 COLUMN_VALUE_CHAR stored procedure is used to define a variable to receive a CHAR value from a cursor.

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

Parameters


Parameter	Description
c	The ID of the cursor that returns data to the variable being defined.
position	The position of the returned data within the cursor. The first value in the cursor is position 1.
value	The variable of data type CHAR that receives the data returned in the cursor by a prior FETCH call.
column_error	If an error occurs, this parameter indicates the error code associated with the column.
actual_length	The actual length of the data before truncation.

COLUMN_VALUE_RAW

The COLUMN_VALUE_RAW stored procedure is used to define a variable to receive 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 that returns data to the variable being defined.
position	The position of the returned data within the cursor. The first value in the cursor is position 1.
value	The variable of data type RAW that receives the data returned in the cursor by a prior FETCH call.
column_error	If an error occurs, this parameter indicates the error code associated with the column.
actual_length	The actual length of the data before truncation.

DEFINE_COLUMN

The DEFINE_COLUMN stored procedure is used to define a column or expression in the SELECT list that is to be returned and fetched in a cursor.

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

Parameters


Parameter	Description
c	The ID of the cursor associated with the SELECT statement.
position	The position of the column or expression in the SELECT list that is being defined.
column	A variable that matches the data type of the column or expression in the specified position of the SELECT result set.
column_size	The maximum length of the returned data. The column_size parameter must be specified if the data type of the column is VARCHAR2. Returned data exceeding column_size is truncated to the maximum length specified by the column_size parameter.

Examples

The following example shows how to use 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 example shows an alternative to the preceding example that produces the exact same results. Note that the lengths of the data types are irrelevant. The empno, sal, and comm columns will still return data equivalent to NUMBER(4) and NUMBER(7,2), even though v_num is defined as NUMBER(1). The ename column will return data up to ten characters in length as defined by the length parameter in the DEFINE_COLUMN call. The length that is indicated by the data type VARCHAR2(1) declared for v_varchar is ignored. The actual size of the returned data is determined 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 DEFINE_COLUMN_CHAR stored procedure is used to define a CHAR column or expression in the SELECT list that is to be returned and fetched in a cursor.

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

Parameters


Parameter	Description
c	The ID of the cursor associated with the SELECT statement.
position	The position of the column or expression in the SELECT list that is being defined.
column	A CHAR variable.
column_size	The maximum length of the returned data. Returned data exceeding column_size is truncated to column_size characters.

DEFINE_COLUMN_RAW

The DEFINE_COLUMN_RAW stored procedure is used to define a RAW column or expression in the SELECT list that is to be returned and fetched in a cursor.

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

Parameters


Parameter	Description
c	The ID of the cursor associated with the SELECT statement.
position	The position of the column or expression in the SELECT list that is being defined.
column	A RAW variable.
column_size	The maximum length of the returned data. Returned data exceeding column_size is truncated to column_size characters.

DEFINE_ARRAY

The DEFINE_ARRAY stored procedure defines the columns into which you want to fetch rows as arrays. Syntax:

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 to be bound to the array.
position	The relative position of the column in the array.
table_variable	The variable that is declared as <datatype>. Valid values of <datatype>: varchar2_table clob_table binary_float_table binary_double_table blob_table date_table number_table timestamp_table
cnt	The number of rows fetched. It must be an integer that is greater than 0.
lower_bnd	Results are copied into the array, starting at this lower bound index.

Examples

The following anonymous block creates a table named t and fetches two rows of data from the t table.

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;

A similar output is displayed:

fetch rows is 2
1
2

DESCRIBE_COLUMNS

The DESCRIBE_COLUMNS stored procedure is used to describe the columns returned by a cursor.

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

Parameters


Parameter	Description
c	The ID of the cursor.
col_cnt	The number of columns in the cursor result set.
desc_tab	The table that contains a description of each column returned by the cursor. The descriptions are of type DESC_REC, and contain the values in the following table.


Column name	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

The EXECUTE function is used to execute 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	If the SQL statement is DELETE, INSERT, or UPDATE, this parameter indicates the number of records processed. This parameter is meaningless for other statements.

Examples

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 is used to execute a parsed SELECT statement and fetch one row.

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

Parameters


Parameter	Description
c	The ID of the cursor for the SELECT statement.
exact	If this parameter is set to FALSE, no exception occurs. Default value: FALSE. If this parameter is set to TRUE and the number of rows in the result set is not equal to 1, an exception occurs. If this parameter is set to TRUE and the result set contains no records, a NO_DATE_FOUND exception will occur. If this parameter is set to TRUE and the result set contains multiple records, a TOO_MANY_ROWS exception will occur.
status	If a row is fetched, 1 is returned. If no rows are fetched, 0 is returned. If an exception occurs, no value is returned.

Examples

The following stored procedure uses the EXECUTE_AND_FETCH function to fetch one employee by using the employee's name. If the employee is not found, or more than one employees with the same name are found, an exception will occur.

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;

A similar output is displayed:

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 is used to fetch a row from a cursor.

status INTEGER FETCH_ROWS(c INTEGER)

Parameters


Parameter	Description
c	The ID of the cursor used to fetch a row.
status	If a row is fetched, 1 is returned. If no rows are fetched, 0 is returned.

Examples

The following example fetches the 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,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;

A similar output is displayed:

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 is used to check whether the specified cursor is enabled.

status BOOLEAN IS_OPEN(c INTEGER)

Parameters


Parameter	Description
c	The ID of the cursor to be checked.
status	If the cursor is enabled, this parameter is set to TRUE. If the cursor is disabled, this parameter is set to FALSE.

LAST ROW COUNT

The LAST_ROW_COUNT function is used to return the total number of rows that are fetched.

rowcnt INTEGER LAST_ROW_COUNT

Parameters


Parameter	Description
rowcnt	The total number of fetched rows.

Examples

The following example uses the LAST_ROW_COUNT function to display the total number of rows fetched in the query.

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;

A similar output is displayed:

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
Number of rows: 14

OPEN_CURSOR

The OPEN_CURSOR function is used to create a cursor. A cursor must be used to parse and execute a dynamic SQL statements. After being enabled, a cursor can be re-used with the same or different SQL statements. without the need for you to disable and enable again the cursor.

c INTEGER OPEN_CURSOR

Parameters


Parameter	Description
c	The ID of the newly created cursor.

Examples

The following example shows how to create a new cursor.

DECLARE
    curid           INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
            .
            .
            .
END;

PARSE

The PARSE stored procedure is used to parse an SQL statement or SPL block. For a DDL statement, it is immediately executed and does not require calling the EXECUTE function.

PARSE(c INTEGER, statement VARCHAR2, language_flag INTEGER)

Parameters


Parameter	Description
c	The ID of an enabled cursor.
statement	The SQL statement or SPL block to be parsed. An SQL statement cannot end with a semicolon (;). An SPL block must end with a semicolon (;).
language_flag	Used for compatibility with Oracle syntax. Valid values: DBMS_SQL.V6, DBMS_SQL.V7, and DBMS_SQL.native. This flag is ignored, and all syntax is in PolarDB for PostgreSQL(Compatible with Oracle) form.

Examples

The following anonymous block creates a table named job. Note that DDL statements are immediately executed by the PARSE stored procedure and do not require calling the EXECUTE function.

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 code snippet 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;

A similar output is displayed:

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

The following anonymous block uses the DBMS_SQL package to execute a block that contains two INSERT statements. Note that the end of the block contains a terminating semicolon (;), while in the example of OPEN_CURSOR, each individual INSERT statement does not have a terminating 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;