使用DBMS_SQL子程式實現動態SQL的文法參考-雲原生資料庫 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;