DBMS_SQL包提供查询 PolarDB PostgreSQL版(兼容Oracle) 动态SQL的功能,可以在应用运行时构建查询。

PolarDB PostgreSQL版(兼容Oracle) 支持查询动态SQL,且以与Oracle兼容的方式来使用动态SQL。

表 1. DBMS_SQL函数/存储过程
函数/存储过程 类型 返回类型 说明
BIND_VARIABLE(c, name, value [, out_value_size ]) 存储过程 N/A 将值绑定到变量。
BIND_VARIABLE_CHAR(c, name, value [, out_value_size ]) 存储过程 N/A 将CHAR值绑定到变量。
BIND_VARIABLE_RAW(c, name, value [, out_value_size ]) 存储过程 N/A 将RAW值绑定到变量。
CLOSE_CURSOR(c IN OUT) 存储过程 N/A 关闭游标。
COLUMN_VALUE(c, position, value OUT [, column_error OUT [, actual_length OUT ]]) 存储过程 N/A 将列值返回到变量中。
COLUMN_VALUE_CHAR(c, position, value OUT [, column_error OUT [, actual_length OUT ]]) 存储过程 N/A 将CHAR列值返回到变量中。
COLUMN_VALUE_RAW(c, position, value OUT [, column_error OUT [, actual_length OUT ]]) 存储过程 N/A 将RAW列值返回到变量中。
DEFINE_COLUMN(c, position, column [, column_size ]) 存储过程 N/A 在SELECT列表中定义一列。
DEFINE_COLUMN_CHAR(c, position, column, column_size) 存储过程 N/A 在SELECT列表中定义CHAR列。
DEFINE_COLUMN_RAW(c, position, column, column_size) 存储过程 N/A 在SELECT列表中定义RAW列。
DEFINE_ARRAY(c,position,table_variable,cnt, lower_bnd) 存储过程 N/A 将需要获取行的列定义为数组。
DESCRIBE_COLUMNS 存储过程 N/A 定义用于保存游标结果集的列。
EXECUTE(c) 函数 INTEGER 执行游标。
EXECUTE_AND_FETCH(c [, exact ]) 函数 INTEGER 执行一个游标并获取一行。
FETCH_ROWS(c) 函数 INTEGER 从游标中获取的行数。
IS_OPEN(c) 函数 BOOLEAN 查看游标是否打开。
LAST_ROW_COUNT 函数 INTEGER 返回获取的累计行数。
OPEN_CURSOR 函数 INTEGER 打开游标。
PARSE(c, statement, language_flag) 存储过程 N/A 解析语句。

与Oracle版本的DBMS_SQL执行相比, PolarDB 的DBMS_SQL执行是部分执行。 PolarDB 仅支持上述表中列出的函数和存储过程。

下表中列出了DBM_SQL包允许使用的公共变量。

表 2. DBMS_SQL 公共变量
公共变量 数据类型 取值 说明
native INTEGER 1 与Oracle语法兼容。更多信息请参见 DBMS_SQL.PARSE
V6 INTEGER 2 与Oracle语法兼容。更多信息请参见 DBMS_SQL.PARSE
V7 INTEGER 3 与Oracle语法兼容。更多信息请参见 DBMS_SQL.PARSE

BIND_VARIABLE

存储过程 BIND_VARIABLE 用于将一个值和SQL命令中的IN或IN OUT绑定变量相关联。

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

参数

参数名称 描述
c 与带有绑定变量SQL命令相对应游标的ID。
name SQL命令中绑定变量的名称。
value 被分配的值。
out_value_size 如果 name 是一个IN OUT模式的变量,则定义输出值的最大长度。如果没有指定这个参数,则将当前value的长度默认为最大值长度。

示例

下面的匿名代码块使用绑定变量向表 emp 中插入一条记录。

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;
显示结果如下:
Number of rows processed: 1

BIND_VARIABLE_CHAR

存储过程 BIND_VARIABLE_CHAR 将一个CHAR类型的值和SQL命令中的IN或IN OUT绑定变量相关联。

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

参数

参数名称 描述
c 与带有绑定变量的SQL命令相对应的游标ID。
name SQL命令中绑定变量的名称。
value 被分配的类型为RAW的值。
out_value_size 如果 name 是一个IN OUT模式的变量,则定义输出值的最大长度。如果没有指定这个参数,则将当前值的长度默认为最大值长度。

BIND_VARIABLE_RAW

存储过程 BIND_VARIABLE_RAW 用于将一个类型为RAW的值和SQL命令中的IN或IN OUT绑定变量相关联。

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

参数

参数名称 描述
c 与带有绑定变量的SQL命令相对应的游标ID。
name SQL命令中绑定变量的名称。
value 被分配的类型为RAW的值。
out_value_size 如果 name 是一个IN OUT模式的变量,那么定义输出值的最大长度。如果没有指定这个参数,那么将当前值的长度默认为最大值长度。

CLOSE_CURSOR

存储过程 CLOSE_CURSOR 关闭一个已打开的游标。当关闭游标后,释放分配给游标的资源,并且不能再使用这个游标。

CLOSE_CURSOR(c IN OUT INTEGER)
            

参数

参数名称 描述
c 需要关闭游标的ID。

示例

以下示例,关闭了一个已打开的游标:
DECLARE
    curid           INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

COLUMN_VALUE

存储过程 COLUMN_VALUE 定义了一个变量,用于从游标中接收值。

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

参数

参数名称 描述
c 一个游标的ID,用于将数据返回给被定义变量。
position 在游标中返回数据的位置。在游标中的第一个位置值是1。
value 通过前面的FETCH操作在游标中接收返回数据的变量。
column_error 如果发生异常错误,这个参数表示与列相关的错误代码。
actual_length 在进行截断操作前,数据的实际长度。

示例

下面的代码显示一个匿名代码块的一部分。这部分代码的功能是通过存储过程 COLUMN_VALUE ,从游标中接收数据。

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

存储过程 COLUMN_VALUE_CHAR 定义一个变量,用于从游标中接收一个CHAR类型的值。

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

参数

参数名称 描述
c 游标的ID,用于向被定义的变量返回数据。
position 返回数据在游标内部的位置。在游标中的第一个位置是1。
value 类型为CHAR的变量,通过前面已经进行FETCH操作,从游标中接收数据。
column_error 如果有任何错误发生,这个参数表示与列相关的错误代码。
actual_length 在任何截断的操作前,数据的实际长度。

COLUMN_VALUE_RAW

存储过程 COLUMN_VALUE_RAW 定义一个变量,用于从游标中接收一个RAW型的值。

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

参数

参数名称 描述
c 游标的ID,用于向被定义变量返回数据。
position 返回数据在游标中位置。在游标中的第一个值是1。
value 类型为RAW的变量,通过前面已经进行FETCH操作,从游标中接收数据。
column_error 如果有任何错误发生,这个参数表示与列相关的错误代码。
actual_length 在任何截断的操作前,数据的实际长度。

DEFINE_COLUMN

存储过程 DEFINE_COLUMN 在SELECT列表中定义了一个列或者表达式,这个列或者表达式将在游标中返回和取出。

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

参数

参数名称 描述
c 与SELECT命令相关联的游标ID。
position 被定义的列或者表达式在SELECT列表中的位置。
column 在SELECT列表中位置为position的列或者表达式数据类型相等的变量。
column_size 返回数据的最大长度。如果列的数据类型是VARCHAR2, 那么必须指定column_size。长度超过column_size的返回数据将被截断为长度是column_size的字符串。

示例

下面显示了如何使用存储过程 DEFINE_COLUMN 定义表 emp 的列 empno ename hiredate sal comm

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;

下面显示了针对上个示例的另外一种实现方法,产生的结果完全一样。需要注意的是与数据类型的长度无关。列 empno sal comm 返回的数据长度分别等于 NUMBER(4) NUMBER(7,2) ,尽管 v_num 是被定义为 NUMBER(1) 。列 ename 将返回的数据长度可达到调用存储过程 DEFINE_COLUMN 中定义的长度参数,而不是对于 v_varchar 声明中类型 VARCHAR2(1) 。返回数据的实际长度是由存储过程 DEFINE_COLUMN 指定。

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

存储过程 DEFINE_COLUMN_CHAR 在SELECT列表中定义了一个CHAR类型的列或表达式,您在游标中返回并获取这个CHAR类型的列或表达式。

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

参数

参数名称 描述
c 与SELECT命令相关的游标ID。
position 被定义的列或者表达式在SELECT列表中的位置。
column 一个CHAR类型变量。
column_size 返回数据的最大长度,长度超过 column_size 的返回数据将被截断。

DEFINE_COLUMN_RAW

存储过程 DEFINE_COLUMN_RAW 在SELECT列表中定义了一个RAW类型的列或表达式,您在游标中返回并获取这个RAW类型的列或表达式。

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

参数

参数名称 描述
c 与SELECT命令相关的游标ID。
position 被定义的列或者表达式在SELECT列表中的位置。
column 一个RAW类型变量。
column_size 返回数据的最大长度,当返回数据的长度超过 column_size 时,将会被截断。

DEFINE_ARRAY

DEFINE_ARRAY 存储过程将需要获取行的列定义为数组。语法如下:
DEFINE_ARRAY (
   c           IN INTEGER,
   position    IN INTEGER,
   <table_variable>    IN <datatype>,
   cnt         IN INTEGER,
   lower_bnd   IN INTEGER);
参数
参数名称 描述
c 需要绑定数组的游标的ID。
position 列在数组中的相对位置。
table_variable 已定义为<datatype>类型的变量。其中,<datatype>类型的取值如下:
  • varchar2_table
  • clob_table
  • binary_float_table
  • binary_double_table
  • blob_table
  • date_table
  • number_table
  • timestamp_table
cnt 获取的行的数量。必须为大于0的整数。
lower_bnd 从此下限索引开始将查询结果复制到数组中。

示例

以下匿名代码块,创建了表 t ,并从表 t 中获取2行数据。
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,
                          n_tab1);
    FOR i IN 1 .. d LOOP
      dbms_output.put_line(n_tab1(i));
    END LOOP;
  dbms_sql.close_cursor(c);
END;
显示结果如下:
fetch rows is 2
2

DESCRIBE_COLUMNS

DESCRIBE_COLUMNS 存储过程用于描述游标返回的列。

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

参数

参数名称 描述
c 游标的ID 。
col_cnt 游标结果集中列的数量。
desc_tab 包含游标所返回的每列的描述的表。描述为DESC_REC类型,包含值请参见 下表
列名称 类型
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 用于执行一个已解析SQL语句或SPL代码块。

status INTEGER EXECUTE(c INTEGER)
            

参数

参数名称 描述
c 需要执行的SQL语句或SPL代码块的游标ID,其中SQL命令已解析。
status 如果SQL命令是 DELETE INSERT UPDATE ,那么这个参数代表已处理的记录数。如果是其它命令,那么这个参数没有意义。

示例

下面的匿名代码块向表 dept 中插入了一条记录。

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

函数 EXECUTE_AND_FETCH 用于执行一条已解析的SELECT命令,并且取回一条记录。

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

参数

参数名称 描述
c 执行SELECT命令对应的游标ID。
exact
  • 设置为FALSE时,不产生异常。默认值为FALSE。
  • 设置为TRUE时,当结果集中的记录数不等于1的时候,就会产生一个异常。
  • 设置为TRUE时,并且在结果集中没有记录,则会产生一个NO_DATE_FOUND的异常。
  • 设置为TRUE时,并且在结果集中有多条记录的情况下,则会产生TOO_MANY_ROWS的异常。
status
  • 如果成功取回一条记录,返回1。
  • 如果没有取回记录,返回0。
  • 如果产生异常的话,则不返回值。

示例

以下存储过程使用函数 EXECUTE_AND_FETCH 根据雇员的姓名来获取一条雇员记录。如果没有找到相应的雇员记录,或者找到多个具有相同的姓名的雇员,都会产生异常。

CREATE OR REPLACE PROCEDURE select_by_name(
    p_ename         emp.ename%TYPE
    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);
            
显示结果如下:
EXEC select_by_name('MARTIN')
Number    : 7654
Name      : MARTIN
Hire Date : 09/28/1981
Salary    : 1250
Commission: 1400
Department: SALES

FETCH_ROWS

函数 FETCH_ROWS 从一个游标中获取的行的数量。

status INTEGER FETCH_ROWS(c INTEGER)         

参数

参数名称 描述
c 用于获取行的游标ID。
status 如果成功获取行,返回1。如果没有获取到行,返回0。

示例

以下示例从表 emp 中取出记录,并且显示结果。

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('-----  ----------  ----------  --------  ' ||
        '--------');
        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;
显示结果如下:
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

函数 IS_OPEN 用于测试是否已打开一个游标。

status BOOLEAN IS_OPEN(c INTEGER)
            

参数

参数名称 描述
c 待测试游标的ID。
status 如果游标是打开状态,这个参数设为true。如果游标没有打开,那么这个值设为false。

LAST ROW COUNT

函数 LAST_ROW_COUNT 返回当前已取回记录的总数。

rowcnt INTEGER LAST_ROW_COUNT
            

参数

参数名称 描述
rowcnt 已取回记录的总数。

示例

在下面的示例中,使用函数 LAST_ROW_COUNT 查询已取回记录的总数。

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('-----  ----------  ----------  --------  ' ||
        '--------');
        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;
显示结果如下:
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

函数 OPEN_CURSOR 用于创建一个新的游标。您必须使用一个游标来解析和执行动态SQL语句。当打开游标后,可以以相同或不同的SQL语句来重复使用这个游标。您不需要通过关闭然后重新打开的方式实现对游标进行重用。

c INTEGER OPEN_CURSOR
            

参数

参数名称 描述
c 与新创建的游标相关联的游标ID。

示例

在下面的示例中创建了一个新的游标。

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

PARSE

存储过程 PARSE 用于解析一条SQL命令或SPL代码块。如果SQL语句是一条DDL命令,那么将立即执行这条命令,而不要求运行函数 EXECUTE

PARSE(c INTEGER, statement VARCHAR2, language_flag INTEGER)
            

参数

参数名称 描述
c 一个已打开游标的ID。
statement 已解析的SQL命令或SPL代码块。SQL命令不能以分号结束,而一个SPL代码块则要求以分号结束。
language_flag 这个参数是为了与Oracle语法兼容而提供的。使用DBMS_SQL.V6、DBMS_SQL.V7 或DBMS_SQL.native,实际上您可以忽略这个标志,所有的语法都假定是以 PolarDB PostgreSQL版(兼容Oracle) 的形式存在的。

示例

下面的匿名代码块创建了名称为 job 的表。需要注意的是DDL语句是由存储过程 PARSE 立即执行,不要求函数 EXECUTE 单独运行此步骤。

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;

以下代码块向表 job 中插入两条记录。

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);
            
显示结果如下:
Number of rows processed: 1
Number of rows processed: 1

以下的匿名代码块使用DBMS_SQL包执行了包含2条INSERT语句的代码块。需要注意的是在代码块结束的地方包含一个分号结束符,而在 OPEN_CURSOR 中的示例中,每个单独的INSERT语句没有分号结束符。

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);