如何使用DBMS_SQL系统包_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

DBMS_SQL内置包提供了动态SQL的功能，可以在运行期间动态执行SQL语句。

DBMS_SQL子程序总览

子程序	说明
BIND_ARRAY Procedure	将数组类型的值绑定到变量。
BIND_VARIABLE Procedure	将给定值绑定到变量。
BIND_VARIABLE_CHAR Procedure	将给定的CHAR类型值绑定到变量。
BIND_VARIABLE_RAW Procedure	将给定的RAW类型值绑定到变量。
CLOSE_CURSOR Procedure	关闭游标。
COLUMN_VALUE Procedure	返回游标中给定位置的元素的值。
COLUMN_VALUE_CHAR Procedure	返回游标中给定位置的CHAR类型列的值。
COLUMN_VALUE_LONG Procedure	返回游标中给定位置的LONG类型列的值。
COLUMN_VALUE_RAW Procedure	返回游标中给定位置的RAW类型列的值。
DEFINE_ARRAY Procedure	定义要从游标中选择的数组。
DEFINE_COLUMN Procedure	定义要从游标中选择的列。
DEFINE_COLUMN_CHAR Procedure	定义要从游标中选择的CHAR类型列。
DEFINE_COLUMN_LONG Procedure	定义要从游标中选择的LONG类型列。
DEFINE_COLUMN_RAW Procedure	定义要从游标中选择的RAW类型列。
EXECUTE Function	执行给定的游标。
EXECUTE_AND_FETCH Function	执行给定的游标并获取行数据。
FETCH_ROWS Function	从给定的游标中获取行数据。
IS_OPEN Function	判断给定的游标是否打开。
LAST_ROW_COUNT Function	返回已获取行数的累积数量。
OPEN_CURSOR Function	返回新打开的游标的ID。
PARSE Procedure	解析给定的语句。
VARIABLE_VALUE Procedure	返回给定游标中命名变量的值。
VARIABLE_VALUE_CHAR Procedure	返回给定游标中CHAR类型的命名变量的值。
VARIABLE_VALUE_RAW Procedure	返回给定游标中RAW类型的命名变量的值。

DBMS_SQL数据类型

VARCHAR2A

TYPE varchar2a IS TABLE OF varchar2(32767);

VARCHAR2S

TYPE varchar2s IS TABLE OF varchar2(256);

BIND_ARRAY

该存储过程将数组类型的值绑定到变量。

语法

DBMS_SQL.BIND_ARRAY ( 
   c        IN INTEGER, 
   name     IN VARCHAR2, 
   value    IN ANYARRAY
 [,index1   IN INTEGER, 
   index2   IN INTEGER)] );

参数说明

参数	说明
c	待绑定值的游标ID。
name	语句中的数组名称。
value	待绑定的局部变量。
index1	（可选参数）标记数组范围下限的索引。默认值为-1。
index2	（可选参数）标记数组范围上限的索引。默认值为-1。

示例

该示例通过BIND_ARRAY插入了多行数据到目标表中。

CREATE TABLE test(a int, b varchar2, c numeric);

DECLARE
  c int;
  a int[];
  b varchar[];
  ca numeric[];
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c, 'insert into test values(:a, :b, :c)', DBMS_SQL.NATIVE);
  a := ARRAY[1, 2, 3];
  b := ARRAY['Alice', 'Bob', 'Cindy'];
  ca := ARRAY[5, 4, 3];

  DBMS_SQL.BIND_ARRAY(c, 'a', a);
  DBMS_SQL.BIND_ARRAY(c, 'b', b);
  DBMS_SQL.BIND_ARRAY(c, 'c', ca);
-- inserted rows: 3
  DBMS_OUTPUT.PUT_LINE('inserted rows: ' || DBMS_SQL.EXECUTE(c));
  DBMS_SQL.CLOSE_CURSOR(c);
END;

SELECT * FROM test ORDER BY 1, 2, 3;
 a |   b   | c
---+-------+---
 1 | Alice | 5
 2 | Bob   | 4
 3 | Cindy | 3
(3 rows)

BIND_VARIABLE

该存储过程将给定值绑定到变量。

语法

DBMS_SQL.BIND_VARIABLE (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN VARCHAR2,
   out_value_size IN INTEGER DEFAULT -1);

参数说明

参数	说明
c	待绑定值的游标ID。
name	语句中的变量名称。
value	待绑定的局部变量。
out_value_size	（可选参数）OUT变量的最大预期字节大小。默认值为-1。

示例

该示例展示了如何通过BIND_VARIABLE绑定变量。

CREATE TABLE test(id number, c VARCHAR2(30));
INSERT INTO test VALUES (1, 'bind_variable for varchar2');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  c_var  VARCHAR2(25);
BEGIN 
  c := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.PARSE(c, 
      'UPDATE test SET c = :n WHERE id = :id RETURNING c INTO :n',
      DBMS_SQL.NATIVE); 
  
  id_var := 1;
  c_var := 'bind_variable_varchar2';
  
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.BIND_VARIABLE(c, 'n', c_var, 25);
  
  ignore := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.VARIABLE_VALUE(c, 'n', c_var);
  
  -- VARCHAR returned is:bind_variable_varchar2
  DBMS_OUTPUT.PUT_LINE('VARCHAR returned is:' || c_var);
  DBMS_SQL.CLOSE_CURSOR(c);
END;

BIND_VARIABLE_CHAR

该存储过程将给定的CHAR类型值绑定到变量。

语法

DBMS_SQL.BIND_VARIABLE_CHAR (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN CHAR,
   out_value_size IN INTEGER DEFAULT -1);

参数说明

参数	说明
c	待绑定值的游标ID。
name	语句中的变量名称。
value	待绑定的CHAR类型局部变量。
out_value_size	（可选参数）OUT变量的最大预期字节大小。默认值为-1。

示例

该示例展示了如何绑定CHAR类型变量。

CREATE TABLE test(id number, c VARCHAR2(30));
INSERT INTO test VALUES (1, 'bind_variable for char');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  c_var  CHAR(25);
BEGIN 
  c := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.PARSE(c, 
      'UPDATE test SET c = :n WHERE id = :id RETURNING c INTO :n',
      DBMS_SQL.NATIVE); 
  
  id_var := 1;
  c_var := 'bind_variable_char';
  
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.BIND_VARIABLE_CHAR(c, 'n', c_var, 25);
  
  ignore := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.VARIABLE_VALUE(c, 'n', c_var);
  
  -- CHAR returned is:bind_variable_char
  DBMS_OUTPUT.PUT_LINE('CHAR returned is:' || c_var);
  DBMS_SQL.CLOSE_CURSOR(c);
END;

BIND_VARIABLE_RAW

该存储过程用于将给定的RAW类型值绑定到变量。

语法

DBMS_SQL.BIND_VARIABLE_RAW (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN RAW,
   out_value_size IN INTEGER DEFAULT -1);

参数说明

参数	说明
c	待绑定值的游标ID。
name	语句中的变量名称。
value	待绑定的RAW类型局部变量。
out_value_size	（可选参数）OUT变量的最大预期字节大小。默认值为-1。

示例

该示例展示了如何绑定RAW类型变量。

CREATE TABLE test(id number, c RAW(20));
INSERT INTO test VALUES (1, 'aaabbbccc');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  r_var  RAW(20);
BEGIN 
  c := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.PARSE(c, 
      'UPDATE test SET c = :n WHERE id = :id RETURNING c INTO :n',
      DBMS_SQL.NATIVE); 
  id_var := 1;
  r_var := 'aaaabbbbcccc';
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.BIND_VARIABLE_RAW(c, 'n', r_var, utl_raw.length(r_var));
  ignore := DBMS_SQL.EXECUTE(c);

  DBMS_SQL.CLOSE_CURSOR(c);
END;

SELECT * from test ORDER BY 1, 2;
 id |             c
----+----------------------------
  1 | \x616161616262626263636363
(1 row)

CLOSE_CURSOR

该存储过程用于关闭游标。

语法

DBMS_SQL.CLOSE_CURSOR (
   c  INOUT INTEGER);

参数说明

参数	说明
c	游标ID。

示例

该示例展示了存储过程中关闭游标的一般方法。

DECLARE
  c   NUMBER;
BEGIN 
  c := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.CLOSE_CURSOR(c);
END;

COLUMN_VALUE

该存储过程用于返回游标中给定位置的元素的值。

语法

DBMS_SQL.COLUMN_VALUE( 
   c         IN    INTEGER, 
   pos       IN    INTEGER, 
   value     INOUT ANYELEMENT
  [,column_error INOUT NUMBER]
  [,actual_length INOUT INTEGER]);

参数说明

参数	说明
c	待获取值的游标ID。
pos	游标中列的相对位置。
value	返回的指定列处的值。
column_error	（可选参数）兼容性提供参数。
actual_length	（可选参数）兼容性提供参数。

示例

该示例展示了如何获取游标中指定位置元素的值。

DECLARE
  c         INTEGER;
  processd  INTEGER;
  strval    VARCHAR2(100);
  intval    INTEGER;
  nrows     INTEGER DEFAULT 5;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c, 'select ''num'' || i, i from generate_series(1, :nrows) g(i)',
                 DBMS_SQL.NATIVE);
  dbms_sql.BIND_VARIABLE(c, 'nrows', nrows);
  dbms_sql.DEFINE_COLUMN(c, 1, strval);
  dbms_sql.DEFINE_COLUMN(c, 2, intval);
  processd := DBMS_SQL.EXECUTE(c);
  WHILE DBMS_SQL.FETCH_ROWS(c) > 0
  LOOP
    DBMS_SQL.COLUMN_VALUE(c, 1, strval);
    DBMS_SQL.COLUMN_VALUE(c, 2, intval);
    DBMS_OUTPUT.PUT_LINE('c1: ' || strval || ', c2: ' || intval);
    DBMS_OUTPUT.PUT_LINE('last count is: ' || DBMS_SQL.LAST_ROW_COUNT());
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(c);
END;

-- c1: num1, c2: 1
-- last count is: 1
-- c1: num2, c2: 2
-- last count is: 2
-- c1: num3, c2: 3
-- last count is: 3
-- c1: num4, c2: 4
-- last count is: 4
-- c1: num5, c2: 5
-- last count is: 5

COLUMN_VALUE_CHAR

该存储过程用于返回游标中给定位置的CHAR类型列的值。

语法

DBMS_SQL.COLUMN_VALUE_CHAR (
   c             IN  INTEGER,
   pos           IN  INTEGER,
   value         INOUT CHAR
  [,column_error INOUT NUMBER]
  [,actual_length INOUT INTEGER]);

参数说明

参数	说明
c	待获取值的游标ID。
pos	游标中列的相对位置。
value	返回的指定列处的值。
column_error	（可选参数）兼容性提供参数。
actual_length	（可选参数）兼容性提供参数。

示例

该示例展示了如何获取CHAR类型的游标数据。

CREATE TABLE test(id number, c CHAR(20));
INSERT INTO test VALUES (1, 'define_column_char');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  c_var  CHAR(30);
BEGIN 
  c := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.PARSE(c, 
      'SELECT c FROM test WHERE id = :id',
      DBMS_SQL.NATIVE); 
  id_var := 1;
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.DEFINE_COLUMN_CHAR(c, 1, c_var, 20);
  ignore := DBMS_SQL.EXECUTE_AND_FETCH(c);
  DBMS_SQL.COLUMN_VALUE_CHAR(c, 1, c_var);
  -- COLUMN c is: define_column_char
  DBMS_OUTPUT.PUT_LINE('COLUMN c is: ' || c_var);

  DBMS_SQL.CLOSE_CURSOR(c);
END;

COLUMN_VALUE_LONG

该存储过程用于返回游标中给定位置的LONG类型列的值。

语法

DBMS_SQL.COLUMN_VALUE_LONG (
   c             IN  INTEGER, 
   pos           IN  INTEGER, 
   length        IN  INTEGER, 
   off           IN  INTEGER, 
   value         INOUT VARCHAR2,
   value_length  INOUT INTEGER);

参数说明

参数	说明
c	待获取值的游标ID。
pos	游标中列的相对位置。
length	待获取的LONG值的字节数。
off	LONG类型属性的偏移量。
value	以VARCHAR2类型表示的列值。
value_length	实际返回值的字节数。

示例

该示例展示了如何获取游标中的LONG类型数据。

CREATE TABLE test(id NUMBER, doc LONG);
INSERT INTO test VALUES (1, 'This is test for dbms_sql.define_column_long and dbms_sql.column_value_long');

DECLARE
  c       NUMBER;
  ignore  NUMBER;
  id_var  NUMBER;
  doc_var VARCHAR2(10);
  pos     INTEGER := 0;
  str_len INTEGER := 8;
  returned_len INTEGER;
BEGIN 
  c := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.PARSE(c, 
      'SELECT doc FROM test WHERE id = :id',
      DBMS_SQL.NATIVE); 
  id_var := 1;
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.DEFINE_COLUMN_LONG(c, 1);
  ignore := DBMS_SQL.EXECUTE_AND_FETCH(c);
  LOOP
      DBMS_SQL.COLUMN_VALUE_LONG(c, 1, str_len, pos, doc_var, returned_len);
      EXIT WHEN returned_len = 0;
      DBMS_OUTPUT.PUT_LINE('doc is: ' || doc_var);
      pos := pos + returned_len;
  END LOOP;
END;

-- doc is: This is
-- doc is: test for
-- doc is:  dbms_sq
-- doc is: l.define
-- doc is: _column_
-- doc is: long and
-- doc is:  dbms_sq
-- doc is: l.column
-- doc is: _value_l
-- doc is: ong

COLUMN_VALUE_RAW

该存储过程用于返回游标中给定位置的RAW类型列的值。

语法

DBMS_SQL.COLUMN_VALUE_RAW (
   c               IN  INTEGER,
   pos             IN  INTEGER,
   value           INOUT RAW
  [,column_error INOUT NUMBER]
  [,actual_length INOUT INTEGER]);

参数说明

参数	说明
c	待获取值的游标ID。
pos	游标中列的相对位置。
value	返回的指定列处的值。
column_error	（可选参数）兼容性提供参数。
actual_length	（可选参数）兼容性提供参数。

示例

该示例展示了如何获取游标中的RAW类型数据。

CREATE TABLE test(id number, c RAW(20));
INSERT INTO test VALUES (1, 'aaabbbccc');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  r_var  RAW(10);
BEGIN 
  c := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.PARSE(c, 
      'SELECT c FROM test WHERE id = :id',
      DBMS_SQL.NATIVE); 
  id_var := 1;
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.DEFINE_COLUMN_RAW(c, 1, r_var, 10);
  ignore := DBMS_SQL.EXECUTE_AND_FETCH(c);
  DBMS_SQL.COLUMN_VALUE_RAW(c, 1, r_var);
  -- COLUMN c is: \x616161626262636363
  DBMS_OUTPUT.PUT_LINE('COLUMN c is: ' || r_var);

  DBMS_SQL.CLOSE_CURSOR(c);
END;

DEFINE_ARRAY

该存储过程用于定义要从给定游标中选择的数组。

语法

DBMS_SQL.DEFINE_ARRAY (
   c             IN INTEGER, 
   pos           IN INTEGER, 
   value         IN ANYARRAY,
   cnt           IN INTEGER, 
   lower_bnd     IN INTEGER);

参数说明

参数	说明
c	待定义值的游标ID。
pos	语句中列的相对位置。
value	待绑定的局部变量。
cnt	待获取的行数。
lower_bnd	指定的下限索引。

示例

该示例展示了如何定义游标中选择的数组。

DECLARE
  cur      INTEGER;
  processd INTEGER;
  a        INTEGER[];
  b        VARCHAR2[];
  c        NUMBER[];
BEGIN
  cur := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(cur, 'select i, ''num'' || i, i + 0.003 from generate_series(1, 5) g(i)',
                 DBMS_SQL.NATIVE);
  DBMS_SQL.DEFINE_ARRAY(cur, 1, a, 10, 1);
  DBMS_SQL.DEFINE_ARRAY(cur, 2, b, 13, 1);
  DBMS_SQL.DEFINE_ARRAY(cur, 3, c, 6, 1);

  processd := DBMS_SQL.EXECUTE(cur);
  LOOP
    processd := DBMS_SQL.FETCH_ROWS(cur);
    DBMS_SQL.COLUMN_VALUE(cur, 1, a);
    DBMS_SQL.COLUMN_VALUE(cur, 2, b);
    DBMS_SQL.COLUMN_VALUE(cur, 3, c);
    EXIT WHEN processd != 6;
  END LOOP;
  RAISE NOTICE 'a is: %', a;
  RAISE NOTICE 'b is: %', b;
  RAISE NOTICE 'c is: %', c;
  DBMS_SQL.CLOSE_CURSOR(cur);
END;

-- NOTICE:  a = {1,2,3,4,5}
-- NOTICE:  b = {Ahoj1,Ahoj2,Ahoj3,Ahoj4,Ahoj5}
-- NOTICE:  c = {1.003,2.003,3.003,4.003,5.003}

DEFINE_COLUMN

该存储过程用于定义要从给定游标中选择的列。

语法

DBMS_SQL.DEFINE_COLUMN (
   c              IN INTEGER,
   pos            IN INTEGER,
   col            IN ANYELEMENT,
   column_size    IN INTEGER DEFAULT -1);

参数说明

参数	说明
c	待定义值的游标ID。
pos	语句中列的相对位置。
col	待绑定的局部变量。
column_size	（可选参数）列值的最大预期大小。默认值为-1。

示例

该示例展示了如何定义游标中选择的列。

DECLARE
  c         INTEGER;
  processd  INTEGER;
  strval    VARCHAR2(100);
  intval    INTEGER;
  nrows     INTEGER DEFAULT 5;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c, 'select ''num'' || i, i from generate_series(1, :nrows) g(i)',
                 DBMS_SQL.NATIVE);
  dbms_sql.BIND_VARIABLE(c, 'nrows', nrows);
  dbms_sql.DEFINE_COLUMN(c, 1, strval);
  dbms_sql.DEFINE_COLUMN(c, 2, intval);
  processd := DBMS_SQL.EXECUTE(c);
  WHILE DBMS_SQL.FETCH_ROWS(c) > 0
  LOOP
    DBMS_SQL.COLUMN_VALUE(c, 1, strval);
    DBMS_SQL.COLUMN_VALUE(c, 2, intval);
    DBMS_OUTPUT.PUT_LINE('c1: ' || strval || ', c2: ' || intval);
    DBMS_OUTPUT.PUT_LINE('last count is: ' || DBMS_SQL.LAST_ROW_COUNT());
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(c);
END;

-- c1: num1, c2: 1
-- last count is: 1
-- c1: num2, c2: 2
-- last count is: 2
-- c1: num3, c2: 3
-- last count is: 3
-- c1: num4, c2: 4
-- last count is: 4
-- c1: num5, c2: 5
-- last count is: 5

DEFINE_COLUMN_CHAR

该存储过程用于定义要从给定游标中选择的CHAR类型列。

语法

DBMS_SQL.DEFINE_COLUMN_CHAR (
   c              IN INTEGER,
   pos            IN INTEGER,
   col            IN CHAR,
   column_size    IN INTEGER DEFAULT -1);

参数说明

参数	说明
c	待定义值的游标ID。
pos	语句中列的相对位置。
col	待绑定的局部变量。
column_size	（可选参数）列值的最大预期大小。默认值为-1。

示例

该示例展示了如何定义要从游标中选择的CHAR类型列。

CREATE TABLE test(id number, c CHAR(20));
INSERT INTO test VALUES (1, 'define_column_char');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  c_var  CHAR(30);
BEGIN 
  c := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.PARSE(c, 
      'SELECT c FROM test WHERE id = :id',
      DBMS_SQL.NATIVE); 
  id_var := 1;
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.DEFINE_COLUMN_CHAR(c, 1, c_var, 20);
  ignore := DBMS_SQL.EXECUTE_AND_FETCH(c);
  DBMS_SQL.COLUMN_VALUE_CHAR(c, 1, c_var);
  -- COLUMN c is: define_column_char
  DBMS_OUTPUT.PUT_LINE('COLUMN c is: ' || c_var);

  DBMS_SQL.CLOSE_CURSOR(c);
END;

DEFINE_COLUMN_LONG

该存储过程用于定义要从给定游标中选择的LONG类型列。

语法

DBMS_SQL.DEFINE_COLUMN_LONG (
   c         IN INTEGER,
   pos       IN INTEGER);

参数说明

参数	说明
c	待定义值的游标ID。
pos	语句中列的相对位置。

示例

该示例展示了如何定义要从游标中选择的LONG类型列。

CREATE TABLE test(id NUMBER, doc LONG);
INSERT INTO test VALUES (1, 'This is test for dbms_sql.define_column_long and dbms_sql.column_value_long');

DECLARE
  c       NUMBER;
  ignore  NUMBER;
  id_var  NUMBER;
  doc_var VARCHAR2(10);
  pos     INTEGER := 0;
  str_len INTEGER := 8;
  returned_len INTEGER;
BEGIN 
  c := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.PARSE(c, 
      'SELECT doc FROM test WHERE id = :id',
      DBMS_SQL.NATIVE); 
  id_var := 1;
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.DEFINE_COLUMN_LONG(c, 1);
  ignore := DBMS_SQL.EXECUTE_AND_FETCH(c);
  LOOP
      DBMS_SQL.COLUMN_VALUE_LONG(c, 1, str_len, pos, doc_var, returned_len);
      EXIT WHEN returned_len = 0;
      DBMS_OUTPUT.PUT_LINE('doc is: ' || doc_var);
      pos := pos + returned_len;
  END LOOP;
END;

-- doc is: This is
-- doc is: test for
-- doc is:  dbms_sq
-- doc is: l.define
-- doc is: _column_
-- doc is: long and
-- doc is:  dbms_sq
-- doc is: l.column
-- doc is: _value_l
-- doc is: ong

DEFINE_COLUMN_RAW

该存储过程用于定义要从给定游标中选择的RAW类型列。

语法

DBMS_SQL.DEFINE_COLUMN_RAW (
   c              IN INTEGER,
   pos            IN INTEGER,
   col            IN RAW,
   column_size    IN INTEGER DEFAULT -1);

参数说明

参数	说明
c	待定义值的游标ID。
pos	语句中列的相对位置。
col	待绑定的局部变量。
column_size	（可选参数）列值的最大预期大小。默认值为-1。

示例

该示例展示了如何定义要从游标中选择的RAW类型列。

CREATE TABLE test(id number, c RAW(20));
INSERT INTO test VALUES (1, 'aaabbbccc');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  r_var  RAW(10);
BEGIN 
  c := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.PARSE(c, 
      'SELECT c FROM test WHERE id = :id',
      DBMS_SQL.NATIVE); 
  id_var := 1;
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.DEFINE_COLUMN_RAW(c, 1, r_var, 10);
  ignore := DBMS_SQL.EXECUTE_AND_FETCH(c);
  DBMS_SQL.COLUMN_VALUE_RAW(c, 1, r_var);
  -- COLUMN c is: \x616161626262636363
  DBMS_OUTPUT.PUT_LINE('COLUMN c is: ' || r_var);

  DBMS_SQL.CLOSE_CURSOR(c);
END;

EXECUTE

该函数用于执行给定的游标。

语法

DBMS_SQL.EXECUTE (c IN INTEGER)
  RETURN INTEGER;

参数说明

参数	说明
c	待执行SQL语句的游标ID。

返回值

返回类型	说明
BIGINT	如果SQL命令是DELETE、INSERT或UPDATE，这个参数代表已处理的记录数。

示例

该示例展示了如何通过EXECUTE执行SQL语句。

CREATE TABLE test(id number, c CHAR(20));

DECLARE
  c       NUMBER;
  ret     NUMBER;
  id_var  NUMBER;
  c_var   CHAR(20);
BEGIN 
  c := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.PARSE(c,
     'INSERT INTO test VALUES (:id, :n) RETURNING c INTO :n',
     DBMS_SQL.NATIVE); 
  id_var := 3;
  c_var := 'bind_variable_char';
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.BIND_VARIABLE_CHAR(c, 'n', c_var, 18);
  ret := DBMS_SQL.EXECUTE(c);
  -- RESULT is: 1
  DBMS_OUTPUT.PUT_LINE('RESULT is: ' || ret);
  DBMS_SQL.CLOSE_CURSOR(c);
END;

SELECT * from test ORDER BY 1, 2;
 id |          c
----+----------------------
  3 | bind_variable_char
(1 row)

EXECUTE_AND_FETCH

该函数用于执行给定的游标并获取行数据。

语法

DBMS_SQL.EXECUTE_AND_FETCH (
   c        IN INTEGER,
   exact    IN BOOLEAN DEFAULT FALSE)
  RETURN INTEGER;

参数说明

参数	说明
c	待执行SQL语句的游标ID。
exact	（可选参数）如果实际匹配查询的行数不等于1，则设置为TRUE将引发异常。默认值为FALSE。

返回值

返回类型	说明
INTEGER	如果成功取回一行数据，返回1。如果没有取回数据，返回0。

示例

该示例展示了如何通过当前函数执行并获取SQL的结果。

CREATE TABLE test(id number, c RAW(20));
INSERT INTO test VALUES (1, 'aaabbbccc');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  r_var  RAW(10);
BEGIN 
  c := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.PARSE(c, 
      'SELECT c FROM test WHERE id = :id',
      DBMS_SQL.NATIVE); 
  id_var := 1;
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.DEFINE_COLUMN_RAW(c, 1, r_var, 10);
  ignore := DBMS_SQL.EXECUTE_AND_FETCH(c);
  DBMS_SQL.COLUMN_VALUE_RAW(c, 1, r_var);
  -- COLUMN c is: \x616161626262636363
  DBMS_OUTPUT.PUT_LINE('COLUMN c is: ' || r_var);

  DBMS_SQL.CLOSE_CURSOR(c);
END;

FETCH_ROWS

该函数用于从给定的游标中获取行数据。

语法

DBMS_SQL.FETCH_ROWS (c IN INTEGER)
  RETURN INTEGER;

参数说明

参数	说明
c	待获取数据的游标ID。

返回值

返回类型	说明
INTEGER	如果成功取回数据，返回1。否则返回0。

示例

该示例展示了如何获取EXECUTE执行之后的结果。

DECLARE
  c         INTEGER;
  processd  INTEGER;
  strval    VARCHAR2(100);
  intval    INTEGER;
  nrows     INTEGER DEFAULT 5;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c, 'select ''num'' || i, i from generate_series(1, :nrows) g(i)',
                 DBMS_SQL.NATIVE);
  dbms_sql.BIND_VARIABLE(c, 'nrows', nrows);
  dbms_sql.DEFINE_COLUMN(c, 1, strval);
  dbms_sql.DEFINE_COLUMN(c, 2, intval);
  processd := DBMS_SQL.EXECUTE(c);
  WHILE DBMS_SQL.FETCH_ROWS(c) > 0
  LOOP
    DBMS_SQL.COLUMN_VALUE(c, 1, strval);
    DBMS_SQL.COLUMN_VALUE(c, 2, intval);
    DBMS_OUTPUT.PUT_LINE('c1: ' || strval || ', c2: ' || intval);
    DBMS_OUTPUT.PUT_LINE('last count is: ' || DBMS_SQL.LAST_ROW_COUNT());
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(c);
END;

-- c1: num1, c2: 1
-- last count is: 1
-- c1: num2, c2: 2
-- last count is: 2
-- c1: num3, c2: 3
-- last count is: 3
-- c1: num4, c2: 4
-- last count is: 4
-- c1: num5, c2: 5
-- last count is: 5

IS_OPEN

该函数用于判断给定的游标是否打开。

语法

DBMS_SQL.IS_OPEN (c IN INTEGER)
  RETURN BOOLEAN;

参数说明

参数	说明
c	游标ID。

返回值

返回类型	描述
BOOLEAN	如果游标是打开状态，返回TRUE。否则，返回FALSE。

示例

该示例展示了如何判断游标的开关状态。

DECLARE
  c INTEGER;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  -- CURSOR is open? true
  DBMS_OUTPUT.PUT_LINE('CURSOR is open? ' || DBMS_SQL.IS_OPEN(c));

  DBMS_SQL.CLOSE_CURSOR(c);
  -- CURSOR is open? false
  DBMS_OUTPUT.PUT_LINE('CURSOR is open? ' || DBMS_SQL.IS_OPEN(c));
END;

LAST_ROW_COUNT

该函数用于返回已获取行数的累积数量。

语法

DBMS_SQL.LAST_ROW_COUNT 
   RETURN INTEGER;

返回值

返回值	说明
INTEGER	已经获取的数据行数。

示例

该示例首先通过FETCH_ROWS获取SELECT语句的结果，再通过LAST_ROW_COUNT获取数据的行数。

DECLARE
  c         INTEGER;
  processd  INTEGER;
  strval    VARCHAR2(100);
  intval    INTEGER;
  nrows     INTEGER DEFAULT 5;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c, 'select ''num'' || i, i from generate_series(1, :nrows) g(i)',
                 DBMS_SQL.NATIVE);
  dbms_sql.BIND_VARIABLE(c, 'nrows', nrows);
  dbms_sql.DEFINE_COLUMN(c, 1, strval);
  dbms_sql.DEFINE_COLUMN(c, 2, intval);
  processd := DBMS_SQL.EXECUTE(c);
  WHILE DBMS_SQL.FETCH_ROWS(c) > 0
  LOOP
    DBMS_SQL.COLUMN_VALUE(c, 1, strval);
    DBMS_SQL.COLUMN_VALUE(c, 2, intval);
  END LOOP;
  -- last count is: 5
  DBMS_OUTPUT.PUT_LINE('last count is: ' || DBMS_SQL.LAST_ROW_COUNT());
  DBMS_SQL.CLOSE_CURSOR(c);
END;

OPEN_CURSOR

该函数用于返回新打开的游标的ID。

语法

DBMS_SQL.OPEN_CURSOR (
   [security_level                 IN     INTEGER]
   [,treat_as_client_for_results   IN     BOOLEAN DEFAULT FALSE]) 
  RETURN INTEGER;

参数说明

参数	说明
security_level	（可选参数）兼容性提供参数。
treat_as_client_for_results	（可选参数）兼容性提供参数。

返回值

返回类型	说明
INTEGER	新创建的游标ID。

示例

该示例展示了如何打开一个新的游标。

DECLARE
  c INTEGER;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  -- CURSOR id is: 1
  DBMS_OUTPUT.PUT_LINE('CURSOR id is: ' || c);
END;

PARSE

该存储过程用于解析给定的语句。

语法

DBMS_SQL.PARSE (
   c                           IN   INTEGER,
   statement                   IN   VARCHAR2,
   language_flag               IN   INTEGER,
   edition                     IN   VARCHAR2 DEFAULT NULL,
   apply_crossedition_trigger  IN   VARCHAR2 DEFAULT NULL,
   fire_apply_trigger          IN   BOOLEAN DEFAULT TRUE,
   schema                      IN   VARCHAR2 DEFAULT NULL,
   container                   IN   VARCHAR2);

DBMS_SQL.PARSE (
   c                           IN   INTEGER, 
   statement                   IN   VARCHAR2a,
   lb                          IN   INTEGER, 
   ub                          IN   INTEGER,
   lfflg                       IN   BOOLEAN, 
   language_flag               IN   INTEGER,
   edition                     IN   VARCHAR2 DEFAULT NULL,
   apply_crossedition_trigger  IN   VARCHAR2 DEFAULT NULL,
   fire_apply_trigger          IN   BOOLEAN DEFAULT TRUE,
   schema                      IN   VARCHAR2 DEFAULT NULL,
   container                   IN   VARCHAR2);

DBMS_SQL.PARSE (
   c                           IN   INTEGER, 
   statement                   IN   VARCHAR2s,
   lb                          IN   INTEGER, 
   ub                          IN   INTEGER,
   lfflg                       IN   BOOLEAN, 
   language_flag               IN   INTEGER,
   edition                     IN   VARCHAR2 DEFAULT NULL,
   apply_crossedition_trigger  IN   VARCHAR2 DEFAULT NULL,
   fire_apply_trigger          IN   BOOLEAN DEFAULT TRUE,
   schema                      IN   VARCHAR2 DEFAULT NULL,
   container                   IN   VARCHAR2);

参数说明

参数	说明
c	待解析SQL语句的游标ID。
statement	待解析的SQL语句。
language_flag	指定SQL语句的行为。取值如下： DBMS_SQL.V6 DBMS_SQL.NATIVE DBMS_SQL.V7
lb	语句中元素的下边界。
ub	语句中元素的上边界。
lfflg	如果为TRUE，则在拼接时的每个元素后插入换行符。
edition	（可选参数）兼容性提供参数。
apply_crossediapply_crossedition_trigger	（可选参数）兼容性提供参数。
fire_apply_trigger	（可选参数）兼容性提供参数。
schema	（可选参数）兼容性提供参数。
container	（可选参数）兼容性提供参数。

示例

该示例展示了如何解析SQL语句数组。

CREATE TABLE test(a int, b varchar2, c numeric);

DECLARE
  c int;
  a int[];
  b varchar[];
  ca numeric[];
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c, 'insert into test values(:a, :b, :c)', DBMS_SQL.NATIVE);
  a := ARRAY[1, 2, 3];
  b := ARRAY['Alice', 'Bob', 'Cindy'];
  ca := ARRAY[5, 4, 3];

  DBMS_SQL.BIND_ARRAY(c, 'a', a);
  DBMS_SQL.BIND_ARRAY(c, 'b', b);
  DBMS_SQL.BIND_ARRAY(c, 'c', ca);
-- inserted rows: 3
  DBMS_OUTPUT.PUT_LINE('inserted rows: ' || DBMS_SQL.EXECUTE(c));
  DBMS_SQL.CLOSE_CURSOR(c);
END;

SELECT * FROM test ORDER BY 1, 2, 3;
 a |   b   | c
---+-------+---
 1 | Alice | 5
 2 | Bob   | 4
 3 | Cindy | 3
(3 rows)

VARIABLE_VALUE

该存储过程用于返回给定游标中命名变量的值。

语法

DBMS_SQL.VARIABLE_VALUE ( 
   c      IN   INTEGER, 
   name   IN   VARCHAR2,
   value  INOUT  ANYELEMENT);

参数说明

参数	说明
c	待获取值的游标ID。
name	待获取值的变量名称。
value	获取到的值。

示例

该示例展示了如何获取游标中命名变量的值。

CREATE TABLE test(id number, c VARCHAR2(30));
INSERT INTO test VALUES (1, 'bind_variable for varchar2');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  c_var  VARCHAR2(25);
BEGIN 
  c := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.PARSE(c, 
      'UPDATE test SET c = :n WHERE id = :id RETURNING c INTO :n',
      DBMS_SQL.NATIVE); 
  
  id_var := 1;
  c_var := 'bind_variable_varchar2';
  
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.BIND_VARIABLE(c, 'n', c_var, 25);
  
  ignore := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.VARIABLE_VALUE(c, 'n', c_var);
  
  -- VARCHAR returned is:bind_variable_varchar2
  DBMS_OUTPUT.PUT_LINE('VARCHAR returned is:' || c_var);
  DBMS_SQL.CLOSE_CURSOR(c);
END;

VARIABLE_VALUE_CHAR

该存储过程用于返回给定游标中CHAR类型的命名变量的值。

语法

DBMS_SQL.VARIABLE_VALUE_CHAR (
   c               IN  INTEGER,
   name            IN  VARCHAR2,
   value           INOUT CHAR);

参数说明

参数	说明
c	待获取值的游标ID。
name	待获取值的变量名称。
value	获取到的值。

示例

该示例展示了如何获取游标中CHAR类型命名变量的值。

CREATE TABLE test(id number, c VARCHAR2(30));
INSERT INTO test VALUES (1, 'bind_variable for char');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  c_var  CHAR(25);
BEGIN 
  c := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.PARSE(c, 
      'UPDATE test SET c = :n WHERE id = :id RETURNING c INTO :n',
      DBMS_SQL.NATIVE); 
  
  id_var := 1;
  c_var := 'bind_variable_char';
  
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.BIND_VARIABLE_CHAR(c, 'n', c_var, 25);
  
  ignore := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.VARIABLE_VALUE(c, 'n', c_var);
  
  -- CHAR returned is:bind_variable_char
  DBMS_OUTPUT.PUT_LINE('CHAR returned is:' || c_var);
  DBMS_SQL.CLOSE_CURSOR(c);
END;

VARIABLE_VALUE_RAW

该存储过程用于返回给定游标中RAW类型的命名变量的值。

语法

DBMS_SQL.VARIABLE_VALUE_RAW (
   c               IN  INTEGER,
   name            IN  VARCHAR2,
   value           INOUT RAW);

参数说明

参数	说明
c	待获取值的游标ID。
name	待获取值的变量名称。
value	获取到的值。

示例

该示例展示了如何获取游标中的RAW类型值。

CREATE TABLE test(id number, c RAW(20));
INSERT INTO test VALUES (1, 'aaabbbccc');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  r_var  RAW(10);
BEGIN 
  c := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.PARSE(c, 
      'SELECT c FROM test WHERE id = :id',
      DBMS_SQL.NATIVE); 
  id_var := 1;
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.DEFINE_COLUMN_RAW(c, 1, r_var, 10);
  ignore := DBMS_SQL.EXECUTE_AND_FETCH(c);
  DBMS_SQL.COLUMN_VALUE_RAW(c, 1, r_var);
  -- COLUMN c is: \x616161626262636363
  DBMS_OUTPUT.PUT_LINE('COLUMN c is: ' || r_var);

  DBMS_SQL.CLOSE_CURSOR(c);
END;