金仓数据库 KingbaseES 插件参考手册（22. dbms_sql）

22. dbms_sql

22.1. 插件dbms_sql简介

dbms_sql是KingbaseES 的plsql扩展插件提供的系统包。

DBMS_SQL包内定义了一系列的过程和函数，专门用于动态SQL语句的操作。

• 插件名为 plsql

• 插件版本 V1.0

22.2. 插件dbms_sql加载方式

plsql插件为初始化数据库实例时默认创建，因此dbms_sql系统包为系统内置包。

22.3. 插件dbms_sql的参数配置

无需配置任何参数。

22.4. 插件dbms_sql使用方法

22.4.1. DBMS_SQL包预定义类型

22.4.1.1. DBMS_SQL常量

22.4.1.2. DBMS_SQL集合类型

TYPE bfile_table IS TABLE OF BFILE INDEX BY BINARY_INTEGER;

TYPE desc_tab IS TABLE OF desc_rec INDEX BY BINARY_INTEGER;

TYPE binary_double_table IS TABLE OF BINARY_DOUBLE INDEX BY BINARY_INTEGER;

TYPE binary_float_table IS TABLE OF BINARY_FLOAT INDEX BY BINARY_INTEGER;

TYPE blob_table IS TABLE OF BLOB INDEX BY BINARY_INTEGER;

TYPE clob_table IS TABLE OF CLOB INDEX BY BINARY_INTEGER;

TYPE date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER;

TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

TYPE time_table IS TABLE OF TIME_UNCONSTRAINED INDEX BY BINARY_INTEGER;

TYPE time_with_time_zone_table IS TABLE OF TIME_TZ_UNCONSTRAINED INDEX BY BINARY_INTEGER;

TYPE timestamp_table IS TABLE OF TIMESTAMP_UNCONSTRAINED INDEX BY BINARY_INTEGER;

TYPE timestamp_with_ltz_table IS TABLE OF TIMESTAMP_LTZ_UNCONSTRAINED INDEX BY BINARY_INTEGER;

TYPE timestamp_with_time_zone_table IS TABLE OF TIMESTAMP_TZ_UNCONSTRAINED INDEX BY BINARY_INTEGER;

TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;

TYPE varchar2a IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;

22.4.2. DBMS_SQL包子程序

22.4.2.1. 打开新游标

语法格式

DBMS_SQL.OPEN_CURSOR (
    treat_as_client    BOOL  DEFAULT FALSE)
RETURN INTEGER;

功能描述

打开一个新游标，返回游标的ID。

参数说明

treat_as_client如果为TRUE，则允许过程将自己设置为客户端；如果为FALSE，将返回结果返回给客户端应用程序。该语句的执行结果可以通过GET_NEXT_RESULT方法获取。

返回值

返回新游标的游标ID号。

使用说明

当不再需要此游标时，必须通过调用close_cursor过程来显式关闭它。 可以使用游标来重复运行相同的SQL语句。当重用游标时，在解析新的SQL语句时重用相应的游标数据区域的内容。在重用游标之前，没有必要关闭和重新打开它。

示例

\set SQLTERM /
DECLARE
    c integer;
BEGIN
    c := dbms_sql.open_cursor();
    dbms_sql.close_cursor(c);
END;
/
------------------
ANONYMOUS BLOCK

22.4.2.2. 解析语句

语法格式

DBMS_SQL.PARSE (
handle             INTEGER,
query              TEXT,
flag               INTEGER);

DBMS_SQL.PARSE (
handle             INTEGER,
query              CLOB,
flag               INTEGER);

DBMS_SQL.PARSE (
handle             INTEGER,
query              VARCHAR2A,
lb INTEGER,
ub INTEGER,
lfflag BOOLEAN,
flag INTEGER);

DBMS_SQL.PARSE (
handle             INTEGER,
query              VARCHAR2S,
lb INTEGER,
ub INTEGER,
lfflag BOOLEAN,
flag INTEGER);

功能描述

对游标中的语句进行解析。DDL语句在解析的同时立即执行。

参数说明

使用说明

使用DBMS_SQL动态运行DDL语句可能会导致程序停止响应。例如，对包中的过程的调用会导致包被锁定，直到执行返回到用户端为止。任何导致锁冲突的操作（例如在第一个锁被释放之前动态的尝试删除包）都会停止程序的运行。

使用不同flag参数，则存在不同的数据库行为。

query的类型可以是TEXT,CLOB,VARCHAR2A,VARCHAR2S(参考DBMS_SQL预定义集合类型)

示例

parse接口有多个重载方式，如可传入text或集合，示例如下。

\set SQLTERM /
DECLARE
    c integer;
BEGIN
    c := dbms_sql.open_cursor();
    dbms_sql.parse(c, 'create table if not exists dbmssql_table1(i int, J TEXT)', dbms_sql.native);
    dbms_sql.close_cursor(c);
END;
/
select count(*) from sys_class where relname='dbmssql_table1';
/

--------------------
ANONYMOUS BLOCK
 count
-------
    1
(1 row)

\set SQLTERM /
DECLARE
    c integer;
    sql_string dbms_sql.varchar2s;
BEGIN
    c := dbms_sql.open_cursor();
    sql_string(1) := 'create table if not exists ';
    sql_string(2) := 'dbmssql_table2(i int, J TEXT)';
    dbms_sql.parse(c, sql_string, 1 ,2, true, dbms_sql.native);
    dbms_sql.close_cursor(c);
END;
/
select count(*) from sys_class where relname='dbmssql_table2';
/

--------------------
ANONYMOUS BLOCK
 count
-------
    1
(1 row)

22.4.2.3. 绑定变量

22.4.2.3.1. 绑定到游标的基本数据类型

语法格式

DBMS_SQL.BIND_VARIABLE (
    handle     INTEGER,
    pos        INTEGER,
    val        ANYELEMENT
    [,out_value_size IN INTEGER]);

DBMS_SQL.BIND_VARIABLE (
    handle     INTEGER,
    placeholder   TEXT,
    val        ANYELEMENT
    [,out_value_size IN INTEGER]);

功能描述

将一个值或一个集合与游标定义中的占位符绑定，可以通过占位符的位置绑定，也可以通过占位符的名称绑定。

参数说明

表 22.4.1  绑定到游标的基本数据类型参数说明

使用说明

如果变量是IN或IN/OUT变量或IN集合，则给定的绑定值必须对该变量或数组类型有效。OUT变量的值将被忽略。

SQL语句的绑定变量或集合由它们的名称标识。当将值绑定到变量或绑定数组时，语句中标识它的字符串必须包含一个前导冒号，如下所示

select emp_name from emp where sal > :x;

对于本例，对应的bind调用类似于

bind_variable(cursor_name, ':1', 3500);
or
bind_variable(cursor_name, 1, 3500);

示例

通过参数位置和参数名称两种绑定方式，来对一个DML语句做动态sql操作，示例如下。

\set SQLTERM /
create table if not exists dbmssql_table1(i int, J TEXT);
/
insert into dbmssql_table1 values (1, 'bbbb');
/
DECLARE
    c integer;
    r int;
    x int := 1;
    y varchar2(100) := 'bbbb';
BEGIN
    c := dbms_sql.open_cursor();
    dbms_sql.parse(c, 'delete from dbmssql_table1 where i = :xx and j = :yy', dbms_sql.native);
    --通过参数位置绑定
    dbms_sql.bind_variable(c, 1, x);
    dbms_sql.bind_variable(c, 2, y);
    r := dbms_sql.execute(c);
    dbms_sql.close_cursor(c);
    raise notice 'd%', c;
END;
/
----------------------------
NOTICE:  d
ANONYMOUS BLOCK

\set SQLTERM /
create table if not exists dbmssql_table1(i int, J TEXT);
/
insert into dbmssql_table1 values (1, 'bbbb');
/
DECLARE
    c integer;
    r int;
    x int := 1;
    y varchar2(100) := 'bbbb';
BEGIN
    c := dbms_sql.open_cursor();
    dbms_sql.parse(c, 'delete from dbmssql_table1 where i = :xx and j = :yy', dbms_sql.native);
    --通过参数名称绑定(可以加':'，也可以不加)
    dbms_sql.bind_variable(c, 'xx', x);
    dbms_sql.bind_variable(c, ':yy', y);
    r := dbms_sql.execute(c);
    dbms_sql.close_cursor(c);
    raise notice 'd%', c;
END;
/
----------------------------
NOTICE:  d
ANONYMOUS BLOCK

22.4.2.3.2. 绑定到游标的CHAR类型

语法格式

DBMS_SQL.BIND_VARIABLE_CHAR (
    handle     INTEGER,
    pos        INTEGER,
    val        ANYELEMENT,
    out_value_size IN INTEGER);

DBMS_SQL.BIND_VARIABLE_CHAR (
    handle     INTEGER,
    placeholder   TEXT,
    val        ANYELEMENT,
    out_value_size IN INTEGER);

功能描述

将一个值或一个集合与游标定义中的占位符绑定，可以通过占位符的位置绑定，也可以通过占位符的名称绑定。

参数说明

表 22.4.2  绑定到游标的CHAR类型参数说明

使用说明

该函数只能绑定字符串类型的变量，且必须指定长度；如果指定长度大于实际长度，则取实际长度，否则取指定长度。

示例

调用BIND_VARIABLE_CHAR子程序，且指定字符串长度的示例如下。

\set SQLTERM /
create table if not exists dbmssql_table1(i int, J TEXT);
/
insert into dbmssql_table1 values (1, 'bbbb');
/
DECLARE
    c integer;
    r int;
    x int := 1;
    y varchar2(100) := 'bbbbaaaa';
BEGIN
    c := dbms_sql.open_cursor();
    dbms_sql.parse(c, 'delete from dbmssql_table1 where i = :xx and j = :yy', dbms_sql.native);
    --通过参数位置绑定
    dbms_sql.bind_variable(c, 1, x);
    dbms_sql.bind_variable_char(c, 2, y, 4);
    r := dbms_sql.execute(c);
    raise notice 'r = % ', 1;
    dbms_sql.close_cursor(c);
    raise notice 'd%', c;
END;
/
----------------------------
NOTICE:  r = 1
NOTICE:  d
ANONYMOUS BLOCK

\set SQLTERM /
create table if not exists dbmssql_table1(i int, J TEXT);
/
insert into dbmssql_table1 values (1, 'bbbb');
/
DECLARE
    c integer;
    r int;
    x int := 1;
    y varchar2(100) := 'bbbbaaaa';
BEGIN
    c := dbms_sql.open_cursor();
    dbms_sql.parse(c, 'delete from dbmssql_table1 where i = :xx and j = :yy', dbms_sql.native);
    --通过参数名称绑定(可以加':'，也可以不加)
    dbms_sql.bind_variable(c, 'xx', x);
    dbms_sql.bind_variable_char(c, ':yy', y, 4);
    r := dbms_sql.execute(c);
    dbms_sql.close_cursor(c);
    raise notice 'd%', c;
END;
/
----------------------------
NOTICE:  d
ANONYMOUS BLOCK

22.4.2.3.3. 绑定到游标的关联数组类型

语法格式

DBMS_SQL.BIND_ARRAY (
    handle     INTEGER,
    pos        INTEGER,
    val        ANYELEMENT);

DBMS_SQL.BIND_ARRAY (
    handle     INTEGER,
    placeholder   TEXT,
    val        ANYELEMENT);

功能描述

将dbms_sql预定义的关联数组与游标定义中的占位符绑定，可以通过占位符的位置绑定，也可以通过占位符的名称绑定。

dbms_sql将拆分出关联数组中的元素，以所有元素的值作为参数批量执行dml语句。

参数说明

使用说明

为了绑定范围，表必须包含指定范围的元素——tab(index1)和tab(index2)，但是范围不必是密集的。Index1必须小于等于index2。在绑定中使用tab(index1)和tab(index2)之间的所有元素。

如果没有在bind调用中指定索引，并且语句中的两个不同绑定指定包含不同数量元素的表，然后实际使用的元素数量是所有表之间的最小数量。如果指定索引，也会出现这种情况——为所有表选择两个索引之间的最小范围。

不是查询中的所有绑定变量都必须是数组绑定。有些可以是常规绑定，在表达式求值中对集合的每个元素使用相同的值。

批量select, insert, update 和 delete可以通过将多个调用绑定到一个调用来提高应用程序的性能。此过程允许使用DBMS_SQL包预定义的PL/SQL TABLE类型。

示例

使用bind_array接口来做批量insert操作，示例如下。

\set SQLTERM /
create table if not exists dbmssql_table1(i int, J TEXT);
/
DECLARE
    c integer;
    r int;
    x dbms_sql.number_table;
    y dbms_sql.varchar2_table;
BEGIN
    c := dbms_sql.open_cursor();
    x(1) := 1;
    x(2) := 2;
    x(3) := 3;
    x(4) := 4;
    y(1) := 'aaaa';
    y(2) := 'bbbb';
    y(3) := 'cccc';
    y(4) := 'dddd';
    dbms_sql.parse(c, 'insert into dbmssql_table1 values (:a, :b);', dbms_sql.native);
    dbms_sql.bind_array(c, 'a', x);
    dbms_sql.bind_array(c, 'b', y);
    r := dbms_sql.execute(c);
    dbms_sql.close_cursor(c);
    raise notice 'd%', c;
END;
/
----------------------------
NOTICE:  d
ANONYMOUS BLOCK

select * from dbmssql_table1;
/
----------------------------
 i |  j
---+------
 1 | aaaa
 2 | bbbb
 3 | cccc
 4 | dddd
(4 rows)

22.4.2.3.4. 绑定到游标的包中类型

语法格式

DBMS_SQL.BIND_VARIABLE_PKG (
    handle     INTEGER,
    pos        INTEGER,
    val        ANYELEMENT);

DBMS_SQL.BIND_VARIABLE_PKG (
    handle     INTEGER,
    placeholder   TEXT,
    val        ANYELEMENT);

功能描述

同BIND_VARIABLE，BIND_VARIABLE和BIND_VARIABLE_PKG都可以用来绑定包中或者非包中定义的类型。

22.4.2.4. 定义类型接收列值

22.4.2.4.1. 定义基本数据类型接收单行列值

语法格式

DBMS_SQL.DEFINE_COLUMN (
    handle     IN INTEGER,
    pos        IN INTEGER,
    val        ANYELEMENT
    [,column_size    IN INTEGER]);

功能描述

为select游标定义被选择的列，定义后可通过column_value取出对应列的值。

参数说明

示例

define_column接口在parse接口后调用，为select游标定义被选择的列，如下所示。

\set SQLTERM ;
drop table t2;
create table t2(id int,name int);
insert into t2 values (1,2);

\set SQLTERM /
DECLARE
        c integer;
        n int;
        result int;
        dc int;
BEGIN
        c := dbms_sql.open_cursor();
        dbms_sql.parse(c, 'select * from t2', dbms_sql.native);
        dbms_sql.define_column(c, 1, n);
        dbms_sql.define_column(c, 2, result);
        dbms_sql.close_cursor(c);
END;
/
----------------------------
ANONYMOUS BLOCK

\set SQLTERM ;
drop table t2;
create table t2(id int,name text);
insert into t2 values (1,'zs');

--define_column接口可以定义预期列值的最大字节大小
\set SQLTERM /
DECLARE
        c integer;
        n int;
        result text;
        dc int;
BEGIN
        c := dbms_sql.open_cursor();
        dbms_sql.parse(c, 'select * from t2', dbms_sql.native);
        dbms_sql.define_column(c, 1, n);
        dbms_sql.define_column(c, 2, result, 2);
        dbms_sql.close_cursor(c);
END;
/
----------------------------
ANONYMOUS BLOCK

22.4.2.4.2. 定义CHAR类型接收单行列值

语法格式

DBMS_SQL.DEFINE_COLUMN (
    handle     IN INTEGER,
    pos        IN INTEGER,
    val        ANYELEMENT,
    column_size    IN INTEGER);

功能描述

为select游标定义被选择的列，定义后可通过column_value取出对应列的值，其中列类型必须是CHAR,VARCHAR,TEXT。

参数说明

示例

define_column_char可以指定预期列值的最大字节大小，如下所示。

\set SQLTERM ;
drop table t2;
create table t2(id int,name text);
insert into t2 values (1,'zs');

\set SQLTERM /
DECLARE
        c integer;
        n int;
        result varchar2(10);
        dc int;
BEGIN
        c := dbms_sql.open_cursor();
        dbms_sql.parse(c, 'select * from t2', dbms_sql.native);
        dbms_sql.define_column(c, 1, n);
        dbms_sql.define_column_char(c, 2, result, 2);
        dbms_sql.close_cursor(c);
END;
/
----------------------------
ANONYMOUS BLOCK

22.4.2.4.3. 定义关联数组类型接收单行列值

语法格式

DBMS_SQL.DEFINE_ARRAY (
   handle      IN INTEGER,
   pos           IN INTEGER,
   val           IN ANYELEMENT,
   cnt         IN INTEGER,
   lower_bnd   IN INTEGER);

功能描述

定义一个集合类型接收某一列的值。这个过程允许您从单个SELECT语句批量提取行。 FETCH_ROWS调用后，会将许多行存入PL/SQL聚合对象中。 当你获取这些行时，它们将被赋值到DBMS_SQL缓存区中，直到运行一个COLUMN_VALUE调用，这时这些行将被复制到作为参数传递给COLUMN_VALUE调用的表中。

参数说明

示例

define_array接口定义关联数组类型接收单行列值，如下所示。

\set SQLTERM ;
drop table t2;
create table t2(id int,name text);
insert into t2 values (1,'zs');

\set SQLTERM /
DECLARE
    c integer;
    n dbms_sql.number_table;
    result dbms_sql.varchar2_table;
    dc int;
BEGIN
    c := dbms_sql.open_cursor();
    dbms_sql.parse(c, 'select * from t2', dbms_sql.native);
    dbms_sql.define_array(c, 1, n, 1, 1);
    dbms_sql.define_array(c, 2, result, 1, 1);
    dbms_sql.close_cursor(c);
END;
/
----------------------------
ANONYMOUS BLOCK

22.4.2.5. 执行游标

语法格式

DBMS_SQL.EXECUTE (
    handle INTEGER)
    RETURN INTEGER;

功能描述

执行给定的游标，返回已处理的行数（仅对INSERT、UPDATE和DELETE语句，其他类型的语句返回值是不确定的）。

参数说明

返回值

返回已处理的行数。

使用说明

TO_CURSOR_NUMBER函数返回的DBMS_SQL 游标的执行方式与已经执行的DBMS_SQL游标相同。

示例：

\set SQLTERM /
declare
    c NUMBER;
    r NUMBER;
BEGIN
    c := DBMS_SQL.OPEN_CURSOR();
    DBMS_SQL.PARSE(c, 'create table if not exists tx (i int)', DBMS_SQL.NATIVE);
    r := DBMS_SQL.EXECUTE(c);
    DBMS_SQL.close_cursor(c);
END;
/

22.4.2.6. 检索游标

语法格式

DBMS_SQL.FETCH_ROWS (
    handle        INTEGER)
    RETURN INTEGER;

功能描述

从给定游标中获取数据，并且返回实际获取的行数。只要还有行需要提取，就可以重复调用FETCH_ROWS。这些行被检索到缓冲区中，如果需要读取数据则需要调用COLUMN_VALUE函数来读取。不能采用NO_DATA_FOUND或游标属性%NOTFOUND判断是否检索到数据。

参数说明

返回值

返回实际获取的行数。

使用说明

如果该游标不是关联select语句，调用该方法则报错“fetch out of sequence”。

示例

FETCH_ROWS接口用于检索游标，如下所示。

set serverout on
\set SQLTERM /
drop table t1;
/
create table t1(i int);
/
insert  into t1 values(1),(2),(3);
/
declare
        c NUMBER;
        d NUMBER;
begin
        c := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(c, 'SELECT * FROM t1', DBMS_SQL.NATIVE);
        d := DBMS_SQL.EXECUTE_AND_FETCH(c);
        dbms_output.put_line('d:'||d);
        d := DBMS_SQL.FETCH_ROWS(c);
        dbms_output.put_line('d:'||d);
        DBMS_SQL.CLOSE_CURSOR(c);
end;
/
-----------------------
ANONYMOUS BLOCK
d:1
d:1

22.4.2.7. 执行游标，并检索游标

语法格式

DBMS_SQL.EXECUTE_AND_FETCH (
    handle          INTEGER,
    exact           BOOL DEFAULT FALSE)
    RETURN INTEGER;

功能描述

执行游标，检索数据，返回实际检索行的数量。

参数说明

返回值

返回指定的行数。

示例

参照FETCH_ROWS示例。

22.4.2.8. 返回游标中指定类型列值:

22.4.2.8.1. 返回游标基础数据类型的列值:

语法格式

DBMS_SQL.COLUMN_VALUE (
    handle           INTEGER,
    pos              INTEGER,
    val              IN OUT ANYELEMENT );

功能描述

用于访问给定游标，给定位置，指定为基本数据类型的列值。此过程用于获取fetch_rows调用后的数据。

参数说明

示例

使用dbms_sql接口函数从dbmssql_table1表中获取结果集，示例如下。

\set SQLTERM /
create table if not exists dbmssql_table1 (i int, j TEXT);
/
insert into dbmssql_table1 values (1, 'foo');
insert into dbmssql_table1 values (2, 'bar');
/
DECLARE
    c integer;
    n int;
    i int;
    j text := 'who';
BEGIN
    c := dbms_sql.open_cursor();
    dbms_sql.parse(c, 'select i, j from dbmssql_table1', dbms_sql.native);
    dbms_sql.define_column(c, 1, i);
    dbms_sql.define_column(c, 2, j);
    n := dbms_sql.execute(c);
    LOOP
        exit when dbms_sql.fetch_rows(c) <= 0;
        dbms_sql.column_value(c, 1, i);
        dbms_sql.column_value(c, 2, j);
        raise notice 'i = %, j = %', i, j;
        raise notice 'last_row_count = %', dbms_sql.last_row_count();
    END LOOP;
    dbms_sql.close_cursor(c);
END;
/
---------------------------
NOTICE:  i = 1, j = foo
NOTICE:  last_row_count = 1
NOTICE:  i = 2, j = bar
NOTICE:  last_row_count = 2
ANONYMOUS BLOCK

22.4.2.8.2. 返回游标关联数组类型的列值

语法格式

DBMS_SQL.COLUMN_VALUE (
    handle           INTEGER,
    pos              INTEGER,
    val              IN OUT ANYELEMENT );

功能描述

用于访问给定游标，给定位置，指定为关联数组类型的列值。此过程用于获取fetch_rows调用后的数据。

参数说明

示例

使用dbms_sql接口函数从dbmssql_table1表中获取结果集，并将结果集赋值到关联数组中，示例如下。

\set SQLTERM /
create table if not exists dbmssql_table1 (i int, j TEXT);
/
insert into dbmssql_table1 values (1, 'foo');
insert into dbmssql_table1 values (2, 'bar');
/
DECLARE
    c integer;
    n int;
    i dbms_sql.number_table;
    j dbms_sql.varchar2_table;
    r int;
BEGIN
    c := dbms_sql.open_cursor();
    dbms_sql.parse(c, 'select i, j from dbmssql_table1', dbms_sql.native);

    dbms_sql.define_array(c, 1, i, 3, 1);
    dbms_sql.define_array(c, 2, j, 3, 1);

    n := dbms_sql.execute(c);
    n := dbms_sql.fetch_rows(c);

    dbms_sql.column_value(c, 1, i);
    dbms_sql.column_value(c, 2, j);

    for r in i.first..i.last LOOP
        raise notice 'i(%) = %, j(%) = %', r, i(r), r, j(r);
    END LOOP;
    dbms_sql.close_cursor(c);
END;
/
---------------------------
NOTICE:  i(1) = 1, j(1) = foo
NOTICE:  i(2) = 2, j(2) = bar
ANONYMOUS BLOCK

22.4.2.8.3. 返回游标CHAR类型的列值

语法格式

DBMS_SQL.COLUMN_VALUE_CHAR (
    handle           INTEGER,
    pos              INTEGER,
    val              IN OUT ANYELEMENT );

功能描述

同COLUMN_VALUE，COLUMN_VALUE和COLUMN_VALUE_CHAR都可以用来返回CHAR类型或者非CHAR的类型的值。

22.4.2.9. 返回游标中给定变量的值

22.4.2.9.1. 返回游标基础数据类型的变量值

语法格式

DBMS_SQL.VARIABLE_VALUE (
    handle          IN   INTEGER,
    pos             IN   INTEGER,
    val             IN OUT ANYELEMENT);

功能描述

这个过程返回给定游标的命名变量的值，它用于返回PL/SQL块或带有RETURNING短语的DML语句中绑定变量的值。

参数说明

示例

使用variable_value接口函数获取绑定变量的值，示例如下。

\set SQLTERM /
create table if not exists dbmssql_table1 (i int, j TEXT);
/
insert into dbmssql_table1 values (1, 'foo');
insert into dbmssql_table1 values (2, 'bar');
/
DECLARE
    c integer;
    r int;
    x text := 'bar';
    y varchar2;
BEGIN
    c := dbms_sql.open_cursor();
    dbms_sql.parse(c, 'select * from dbmssql_table1 where j = :xx', dbms_sql.native);
    dbms_sql.bind_variable(c, 1, x);
    r := dbms_sql.execute(c);
    dbms_sql.variable_value(c, 1, y);
    raise notice 'y1 = %', y;
    dbms_sql.close_cursor(c);
END;
/
---------------------------
NOTICE:  y1 = bar
ANONYMOUS BLOCK

22.4.2.9.2. 返回游标关联数组类型的变量值

语法格式

DBMS_SQL.VARIABLE_VALUE (
    handle          IN   INTEGER,
    pos             IN   INTEGER,
    val             IN OUT ANYELEMENT);

功能描述

这个过程返回给定游标的命名变量的值，它用于返回PL/SQL块或带有RETURNING短语的DML语句中绑定变量的值。

参数说明

示例

使用variable_value接口函数获取绑定变量的值，示例如下。

\set SQLTERM /
create table if not exists dbmssql_table1 (i int, j TEXT);
/
insert into dbmssql_table1 values (1, 'foo');
insert into dbmssql_table1 values (2, 'bar');
/
DECLARE
    c integer;
    r int;
    i int;
    x dbms_sql.varchar2_table;
    y dbms_sql.varchar2_table;
BEGIN
    c := dbms_sql.open_cursor();
    x(1) := 'foo';
    x(2) := 'bar';
    dbms_sql.parse(c, 'select * from dbmssql_table1 where j = :xx', dbms_sql.native);
    dbms_sql.bind_array(c, 1, x);
    r := dbms_sql.execute(c);
    dbms_sql.variable_value(c, 1, y);
    for i in y.first..y.last LOOP
        raise notice 'y(%) = %', i, y(i);
    END LOOP;
    dbms_sql.close_cursor(c);
END;
/
---------------------------
NOTICE:  y(1) = foo
NOTICE:  y(2) = bar
ANONYMOUS BLOCK

22.4.2.9.3. 返回游标CHAR类型的变量值

语法格式

DBMS_SQL.VARIABLE_VALUE_CHAR (
    handle          IN   INTEGER,
    pos             IN   INTEGER,
    val             IN OUT ANYELEMENT);

功能描述

同VARIABLE_VALUE，VARIABLE_VALUE和VARIABLE_VALUE_CHAR都可以用来返回CHAR类型或者非CHAR的类型的值。

22.4.2.9.4. 返回游标包中类型的变量值

语法格式

DBMS_SQL.VARIABLE_VALUE_PKG (
    handle          IN   INTEGER,
    pos             IN   INTEGER,
    val             IN OUT ANYELEMENT);

功能描述

同VARIABLE_VALUE，VARIABLE_VALUE和VARIABLE_VALUE_PKG都可以用来返回包中类型或者非包中类型的值。

22.4.2.10. 获取查询项的描述信息

22.4.2.10.1. 获取查询项的描述信息，为DESC_TAB类型

语法格式

DBMS_SQL.DESCRIBE_COLUMNS (
    handle         INTEGER,
    col_cnt        OUT INTEGER,
    desc_t         OUT DBMS_SQL.DESC_TAB);

功能描述

这个过程返回描述查询列表的所有列信息，需要经过调用DBMS_SQL包中OPEN_CURSOR过程和PARSE过程。

参数说明

示例

对select语句做PARSE操作后，可以使用DESCRIBE_COLUMNS接口函数获取相关表的表结构信息，示例如下。

set serverout on
\set SQLTERM ;
drop table t1;
create table t1(id int,name varchar(50));
\set SQLTERM /
CREATE OR REPLACE PROCEDURE pr1 IS
        v3 DBMS_SQL.DESC_TAB;
        v4 int;
        col_num int;
        cursor_id int;
BEGIN
        cursor_id :=DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(cursor_id, 'SELECT * FROM t1', DBMS_SQL.NATIVE);
        DBMS_SQL.DESCRIBE_COLUMNS(cursor_id,v4,v3);
        col_num := v3.first;
        dbms_output.put_line('col_num:'||col_num);
        WHILE col_num IS NOT NULL LOOP
        dbms_output.put_line(v3(col_num).col_max_len);
        col_num := v3.next(col_num);
        dbms_output.put_line('col_num:'||col_num);
        END LOOP;
        DBMS_SQL.CLOSE_CURSOR(cursor_id);
END;
/
call pr1();
/
-------------------------------
CALL
col_num:1
4
col_num:2
50
col_num:

22.4.2.11. 关闭游标

语法格式

DBMS_SQL.CLOSE_CURSOR (
    handle    IN OUT INTEGER);

功能描述

用于关闭游标ID，并释放占用的内存空间。

参数说明

示例

参照OPEN_CURSOR示例。

22.4.2.12. 判断游标是否打开

语法格式

DBMS_SQL.IS_OPEN (
    c              IN INTEGER)
    RETURN BOOLEAN;

功能描述

检查指定的游标是否已经打开。

参数说明

返回值说明

对于已打开但未关闭的任何游标编号返回TRUE，对于NULL游标编号返回FALSE，请注意，CLOSE_CURSOR过程会将传递给它的游标变量设为NULL。

示例

is_open接口用于判断游标是否打开，如下所示。

\set SQLTERM ;
create table if not exists dbmssql_table1(i int, J TEXT);
\set SQLTERM /
DECLARE
        c integer;
        r int;
BEGIN
        c := dbms_sql.open_cursor();
        dbms_sql.parse(c, 'select i, j from dbmssql_table1', dbms_sql.native);

        if dbms_sql.is_open(c) then
                dbms_sql.close_cursor(c);
                raise notice 'A opened, now close it';
        end if;
END;
/
-----------------------------------
NOTICE:  A opened, now close it
ANONYMOUS BLOCK

22.4.2.13. 返回累计检索出来的行的数量

语法格式

DBMS_SQL.LAST_ROW_COUNT
    RETURN INTEGER;

功能描述

这个函数返回所获取的行数的累计计数。

返回值说明

返回所获取的行数的累计计数。

使用说明

在FETCH_ROWS或EXECUTE_AND_FETCH调用之后调用这个函数。如果在EXECUTE调用之后马上调用此函数，则返回的值为0。

示例

通过last_row_count接口可以获取当前处理的行数，示例如下。

set serverout on
\set SQLTERM ;
drop table t2;
create table t2(id int,name int);
\set SQLTERM /
DECLARE
    c integer;
    r int;
    a int := 10;
    b int := 20;
BEGIN
    c := dbms_sql.open_cursor();
    dbms_sql.parse(c, 'insert into t2 values (:1, :2)', dbms_sql.native);
    dbms_sql.bind_variable(c, '1', a);
    dbms_sql.bind_variable(c, '2', b);
    r := dbms_sql.execute(c);
    dbms_output.put_line( 'r:' || r);
    dbms_output.put_line( dbms_sql.last_row_count);
    dbms_sql.close_cursor(c);
END;
/

----------------------------
ANONYMOUS BLOCK
r:1
1

22.4.2.14. 游标转换

22.4.2.14.1. 将游标变量转换为DBMS_SQL包内部游标

语法格式

DBMS_SQL.TO_CURSOR_NUMBER(
    c INOUT refcursor)
    RETURN INTEGER

功能描述

该函数接受一个打开的强类型或弱类型ref游标，并将其转换为DBMS_SQL游标号。

参数说明

返回值说明

返回从REF游标转换而来的DBMS_SQL中可管理的游标ID。

使用说明

1. 必须打开传入的REF游标，否则会引发错误。

2. 在将REF游标转换为DBMS_SQL游标号之后，任何本地动态SQL操作不再可以访问REF游标。

3. 这个子程序返回的DBMS_SQL游标的执行方式与已经执行的DBMS_SQL游标相同。

示例

游标变量转换为DBMS_SQL包内部游标，示例如下。

\set SQLTERM ;
create table if not exists dbmssql_table1 (i int, j TEXT);
\set SQLTERM /
DECLARE
    v_cur int;
    i int;
    j text;
    v_count int;
    cur refcursor;
BEGIN
    open cur for select i, j from dbmssql_table1;
    v_cur := dbms_sql.TO_CURSOR_NUMBER(cur);

    dbms_sql.define_column(v_cur, 1, i);
    dbms_sql.define_column(v_cur, 2, j);

    loop
        v_count := dbms_sql.fetch_rows(v_cur);
        exit when v_count <= 0;
        dbms_sql.column_value_char(v_cur, 1, i);
        dbms_sql.column_value(v_cur, 2, j);
        raise notice 'i = %, j = %', i, j;
    END LOOP;

    dbms_sql.close_cursor(v_cur);
END;
/

----------------------------
NOTICE:  i = 1, j = foo
NOTICE:  i = 2, j = bar
ANONYMOUS BLOCK

22.4.2.14.2. 将DBMS_SQL包内的游标转换为本地动态SQL游标

语法格式

DBMS_SQL.TO_REFCURSOR(
    c INTEGER)
    RETURN refcursor

功能描述

将使用DBMS_SQL包打开、解析、执行过的游标ID转换为本地动态SQL可管理的REF游标（弱类型游标），可供本地动态SQL使用。 此子程序只与select游标一起使用。

参数说明

返回值说明

返回从DBMS_SQL游标ID转换而来的PL/SQL REF游标。

使用说明

1. 通过cursor_name传入的游标必须被打开、解析和执行，否则会引发错误。

2. 将cursor_name转换为REF游标后，任何DBMS_SQL操作都不能访问cursor_number。

3. 在使用DBMS_SQL将cursor_name转换为REF游标之后，IS_OPEN检查cursor_name是否打开会导致错误。

示例

DBMS_SQL包内的游标经过execute操作后，可使用to_refcursor转换为本地动态SQL游标，示例如下。

\set SQLTERM ;
create table if not exists dbmssql_table1 (i int, j TEXT);
\set SQLTERM /
DECLARE
    v_cur number;
    sql_string varchar2(1024);
    i int;
    j text;
    v_count int;
    cur refcursor;
    v_name varchar2(60);
BEGIN
    v_name := 'zs';
    v_cur := dbms_sql.open_cursor();
    sql_string := 'select i, j from dbmssql_table1';
    dbms_sql.parse(v_cur, sql_string, dbms_sql.native);

    v_count := dbms_sql.execute(v_cur);

    cur := dbms_sql.to_refcursor(v_cur);
    loop
        fetch cur into i, j;
        exit when cur%notfound;
        raise notice 'i = %, j = %', i, j;
    END LOOP;
    close cur;
END;
/

----------------------------
NOTICE:  i = 1, j = foo
NOTICE:  i = 2, j = bar
ANONYMOUS BLOCK

22.4.2.15. 返回语句结果集

语法格式

DBMS_SQL.RETURN_RESULT(
    rc IN refcursor,
    to_client IN BOOLEAN DEFAULT TRUE)

DBMS_SQL.RETURN_RESULT(
    rc IN INTEGER,
    to_client IN BOOLEAN DEFAULT TRUE)

参数说明

功能描述

DBMS_SQL.RETURN_RESULT过程让子程序隐式地将查询结果返回给客户机程序（它间接调用子程序）或子程序的直接调用者。 直接调用者执行递归语句，在递归语句中返回该语句结果。 在DBMS_SQL.RETURN_RESULT调用后，该语句结果只有接收者可以访问它。

使用说明

1. 目前只能返回SQL查询，不支持通过远程过程调用返回语句结果。

2. 一旦使用该方法后，除了返回它的客户端或直接调用者外，就不能再访问它了。

3. 当客户端执行的语句或任何中间递归语句是SQL查询并引发错误时，不能返回语句结果。

4. 返回的ref游标可以是强类型的，也可以是弱类型的。

5. 返回的查询可以部分获取。

6. 要从PL/SQL中的递归语句检索返回的语句结果，可以使用DBMS_SQL执行递归语句。

示例

通过return_result接口将结果集返回到客户端，示例如下。

\set SQLTERM ;
create table if not exists dbmssql_table1 (i int, j TEXT);
\set SQLTERM /
DECLARE
    cur1 refcursor;
    cur2 int;
    i_ret int;
BEGIN
    OPEN cur1 for select * from dbmssql_table1;
    dbms_sql.return_result(cur1);

    cur2 := dbms_sql.open_cursor(TRUE);
    dbms_sql.parse(cur2, 'select * from dbmssql_table1;',dbms_sql.native);
    i_ret := dbms_sql.execute(cur2);
    dbms_sql.return_result(cur2);
END;
/

----------------------------
 i |  j
---+-----
 1 | foo
 2 | bar
(2 rows)

 i |  j
---+-----
 1 | foo
 2 | bar
(2 rows)

ANONYMOUS BLOCK

22.4.2.16. 接收RETURN_RESULT过程的一个返回结果，并返回给客户端应用程序

语法格式

DBMS_SQL.GET_NEXT_RESULT(
    c IN INTEGER,
    rc OUT refcursor)

功能描述

GET_NEXT_RESULT过程获取RETURN_RESULT过程返回的一个查询结果，并返回给接收者。GET_NEXT_RESULT与RETURN_RESULT返回结果的顺序相同。

使用说明

1. 在检索语句结果的游标之后，调用者必须在不再需要游标时正确关闭该游标。

2. 所有未检索返回语句的游标将在递归语句的游标关闭后关闭。

3. 要打开游标并获得它的游标ID，请调用DBMS_SQL.OPEN_CURSOR方法,DBMS_SQL.OPEN_CURSOR有一个可选的参数treat_as_client。 当此参数为FALSE（默认参数）时，打开此游标（用来调用子程序）的调用方不会被视为客户端接受查询结果的子程序，相反，这些查询结果在较上层返回给客户机。 如果treat_as_client为TRUE，调用者将被视为客户端。

示例

使用游标变量和dbms_sql游标获取RETURN_RESULT过程返回的结果集，示例如下。

\set SQLTERM ;
create table if not exists dbmssql_table1 (i int, j TEXT);
\set SQLTERM /
--将游标的结果集返回给get_results的调用者
CREATE OR REPLACE PROCEDURE get_results(p_id IN NUMBER DEFAULT NULL) AS
    cur1 refcursor;
    cur2 refcursor;
BEGIN
    IF p_id IS NOT NULL THEN
        OPEN cur1 for select * from dbmssql_table1;
        dbms_sql.return_result(cur1);
    END IF;

    OPEN cur2 for select * from dbmssql_table1;
    dbms_sql.return_result(cur2);

END;
/
--使用游标变量获取RETURN_RESULT过程返回的结果集
DECLARE
    c integer;
    c1 integer;
    sqlstmt varchar2(1024);
    rc refcursor;
    i_ret int;
    i int;
    j text;
BEGIN
    c := dbms_sql.open_cursor(TRUE);
    sqlstmt := 'begin get_results(1);end;';
    dbms_sql.parse(c, sqlstmt,dbms_sql.native);
    i_ret := dbms_sql.execute(c);
    loop
        BEGIN
            --使用rc接收c的结果集
            dbms_sql.get_next_result(c,rc);
        exception
            when no_data_found then
                exit;
        END;
        loop
            fetch rc into i, j;
            exit when rc%NOTFOUND;
            raise notice 'i = %, j = %', i, j;
        end loop;

    end loop;
    dbms_sql.close_cursor(c);
END;
/

----------------------------
NOTICE:  i = 1, j = foo
NOTICE:  i = 2, j = bar
NOTICE:  i = 1, j = foo
NOTICE:  i = 2, j = bar
ANONYMOUS BLOCK

--使用dbms_sql游标获取RETURN_RESULT过程返回的结果集
\set SQLTERM /
DECLARE
    c integer;
    c1 integer;
    sqlstmt varchar2(1024);
    rc refcursor;
    i_ret int;
    i int;
    j text;
BEGIN
    c := dbms_sql.open_cursor(TRUE);
    sqlstmt := 'begin get_results(1);end;';
    dbms_sql.parse(c, sqlstmt,dbms_sql.native);
    i_ret := dbms_sql.execute(c);

    loop
        BEGIN
            dbms_sql.get_next_result(c,c1);
        exception
            when no_data_found then
                exit;
        END;
        dbms_sql.define_column(c1, 1, i);
        dbms_sql.define_column(c1, 2, j);

        LOOP
            exit when dbms_sql.fetch_rows(c1) <= 0;
            dbms_sql.column_value(c1, 1, i);
            dbms_sql.column_value(c1, 2, j);
            raise notice 'i = %, j = %', i, j;
        END LOOP;

    end loop;

    dbms_sql.close_cursor(c1);
END;
/

----------------------------
NOTICE:  i = 1, j = foo
NOTICE:  i = 2, j = bar
NOTICE:  i = 1, j = foo
NOTICE:  i = 2, j = bar
ANONYMOUS BLOCK

22.4.2.17. 返回SQL语句错误发生处的字节偏移量

语法格式

DBMS_SQL.LAST_ERROR_POSITION()
    RETURN INTEGER

功能描述

这个函数返回发生错误的SQL语句文本中的字节偏移量。SQL语句中的第一个字符位于位置0。

返回值说明

返回发生错误的SQL语句文本中的字节偏移量。

使用说明

调用该函数在PARSE调用之后，在调用任何其他DBMS_SQL过程或函数之前。

示例

在异常块中使用LAST_ERROR_POSITION接口获取语句错误位置，示例如下。

\set SQLTERM ;
create table if not exists dbmssql_table1 (i int, j TEXT);
\set SQLTERM /
DECLARE
    v_cur int;
    sql_string varchar2(1024);
    v_count int;
    error_offset number;
    b int := 1;
BEGIN
    sql_string := 'select i, j, k from dbmssql_table1';
    v_cur := dbms_sql.open_cursor();
    dbms_sql.parse(v_cur, sql_string, dbms_sql.native);

    v_count := dbms_sql.execute(v_cur);

    v_count := dbms_sql.fetch_rows(v_cur);

    exception
        when others then
            error_offset := DBMS_SQL.LAST_ERROR_POSITION();
            raise notice 'error_offset:%',error_offset;

    dbms_sql.close_cursor(v_cur);
END
/
----------------------------
NOTICE:  error_offset:13
ANONYMOUS BLOCK

22.4.2.18. 返回该语句的SQL函数代码

语法格式

DBMS_SQL.LAST_SQL_FUNCTION_CODE
    RETURN INTEGER;

功能描述

返回该语句的SQL函数代码。

返回值说明

返回该语句的SQL函数代码。

使用说明

1. 该函数在未执行任何语句时调用返回默认值0。

2. 在PARSE语句之后该函数返回语句的SQL函数代码。

3. 同一个session中，如果之前执行的PLSQL过程已调用一次PARSE语句，且当前PLSQL过程未调用OPEN_CURSOR函数， 该函数返回上一次PARSE的值；调用OPEN_CURSOR后，PARSE语句前调用，返回默认值0。

表 22.4.3  SQL函数代码表

示例

LAST_SQL_FUNCTION_CODE接口在不同情况下调用，获取值可能不同，示例如下。

\set SQLTERM /
create table if not exists dbmssql_table1 (i int, j TEXT);
/
\set SQLTERM /
DECLARE
    sql_code int;
    c int;
    vc int;
BEGIN
    c := dbms_sql.open_cursor;
    sql_code := dbms_sql.LAST_SQL_FUNCTION_CODE();
    raise notice '%',sql_code;
    dbms_sql.parse(c, 'insert into dbmssql_table1 values (1,''xxx'')', dbms_sql.native);
    vc := dbms_sql.execute(c);
    sql_code := dbms_sql.LAST_SQL_FUNCTION_CODE();
    raise notice '%',sql_code;
    sql_code := dbms_sql.LAST_SQL_FUNCTION_CODE();
    raise notice '%',sql_code;
    dbms_sql.close_cursor(c);
END;
/
\set SQLTERM ;

----------------------------
NOTICE:  0
NOTICE:  3
NOTICE:  3
ANONYMOUS BLOCK

set serverout on
\set SQLTERM /
DECLARE
    sql_code int;
    c int;
    vc int;
BEGIN
    sql_code := dbms_sql.LAST_SQL_FUNCTION_CODE();
    raise notice '%',sql_code;
    c := dbms_sql.open_cursor;
    sql_code := dbms_sql.LAST_SQL_FUNCTION_CODE();
    raise notice '%',sql_code;
    dbms_sql.parse(c, 'insert into t1 values (1,''xx'')', dbms_sql.native);
    vc := dbms_sql.execute(c);
    sql_code := dbms_sql.LAST_SQL_FUNCTION_CODE();
    raise notice '%',sql_code;
    sql_code := dbms_sql.LAST_SQL_FUNCTION_CODE();
    dbms_output.put_line(sql_code);
    raise notice '%',sql_code;
END;
/
\set SQLTERM ;
----------------------------
NOTICE:  3
NOTICE:  0
NOTICE:  3
NOTICE:  3
ANONYMOUS BLOCK

22.4.2.19. 其他示例

在一个匿名块分别对create语句、truncate语句，以及select语句做动态sql操作，示例如下。

set serverout on
\set SQLTERM /
DECLARE
    cnt integer;
    count integer := 0;
    descs DBMS_SQL.DESC_TAB;
    hander integer;
    res int;
    result_name text;
    my_id int := 10;
    my_name text := 'hello world';
    my_text text;
BEGIN
    hander := dbms_sql.open_cursor();
    dbms_sql.parse(hander,'create table if not exists test_tmp(id int,sname text)',dbms_sql.native);
    count=dbms_sql.execute(hander);
    dbms_output.put_line( 'count:'||count);

    dbms_sql.parse(hander,'truncate table test_tmp',dbms_sql.native);
    count=dbms_sql.execute(hander);
    dbms_output.put_line( 'count:'||count);

    dbms_sql.parse(hander, 'select * from test_tmp', dbms_sql.native);
    dbms_sql.define_column(hander, 1, res);
    dbms_sql.define_column(hander, 2, result_name);
    count = dbms_sql.execute_and_fetch(hander);

    if count > 0 then
        dbms_sql.column_value(hander, 1, res);
        dbms_sql.column_value(hander, 2, result_name);
        dbms_output.put_line( dbms_sql.last_row_count);
        dbms_sql.describe_columns(hander, cnt, descs);
        dbms_output.put_line( 'describe_columns: ' || dbms_sql.last_row_count);
    end if;

    WHILE dbms_sql.fetch_rows(hander) > 0
    LOOP
        dbms_sql.column_value(hander, 1, res);
        dbms_sql.column_value(hander, 2, result_name);
        dbms_output.put_line(dbms_sql.last_row_count);
    END LOOP;

    dbms_sql.close_cursor(hander);
END;
/

------------------------------
ANONYMOUS BLOCK
count:0
count:0

22.5. 插件dbms_sql卸载方法

无法卸载。

22.6. 插件dbms_sql升级方法

dbms_sql随着KingbaseES安装包一并升级。通常，用户无须单独升级该插件。