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

最新推荐文章于 2024-09-11 23:43:48 发布
最新推荐文章于 2024-09-11 23:43:48 发布
阅读量685 收藏 1
点赞数 1
分类专栏： KingbaseES产品手册 文章标签： sql 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/arthemis_14/article/details/126548521
版权

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

Name

Type

Value

Description

NATIVE

INTEGER

1

兼容性常量

V6

INTEGER

0

兼容性常量

V7

INTEGER

2

兼容性常量

FOREIGN_SYNTAX

INTEGER

-1

兼容性常量

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语句在解析的同时立即执行。

参数说明

参数

描述

handle

解析语句的游标ID

query

要进行解析的SQL语句

flag

指定SQL语句的行为

使用说明

使用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  绑定到游标的基本数据类型参数说明

参数

描述

handle

游标ID

pos

绑定变量的位置

val

与游标中的变量绑定的值或本地变量。 datatype ADT (user-defined object types)

BINARY_DOUBLE

BINARY_FLOAT

BFILE

BLOB

BOOLEAN

CLOB CHARACTER SET ANY_CS

DATE

DSINTERVAL_UNCONSTRAINED

NESTED table

NUMBER

OPAQUE types

REF

TIME_UNCONSTRAINED

TIME_TZ_UNCONSTRAINED

TIMESTAMP_LTZ_UNCONSTRAINED

TIMESTAMP_TZ_UNCONSTRAINED

TIMESTAMP_UNCONSTRAINED

UROWID

VARCHAR2 CHARACTER SET ANY_CS

VARRAY

YMINTERVAL_UNCONSTRAINED

out_value_size

如果没有给定大小，则使用当前值的长度。只有当val类型为char，varchar，text时能指定该参数。

使用说明

如果变量是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<NULL>
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<NULL>
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类型参数说明

参数

描述

handle

游标ID

pos

绑定变量的位置

val

与游标中的变量绑定的值或本地变量。 datatype ADT (user-defined object types)

BINARY_DOUBLE

BINARY_FLOAT

BFILE

BLOB

BOOLEAN

CLOB CHARACTER SET ANY_CS

DATE

DSINTERVAL_UNCONSTRAINED

NESTED table

NUMBER

OPAQUE types

REF

TIME_UNCONSTRAINED

TIME_TZ_UNCONSTRAINED

TIMESTAMP_LTZ_UNCONSTRAINED

TIMESTAMP_TZ_UNCONSTRAINED

TIMESTAMP_UNCONSTRAINED

UROWID

VARCHAR2 CHARACTER SET ANY_CS

VARRAY

YMINTERVAL_UNCONSTRAINED

out_value_size

如果没有给定大小，则使用当前值的长度。只有当val类型为char，varchar，text时能指定该参数。

使用说明

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

示例

调用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<NULL>
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<NULL>
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<NULL>
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取出对应列的值。

参数说明

参数

描述

handle

游标ID

pos

列在定义行中的相对位置。语句在第一列位置为1。

val

定义的列的值。此值的类型决定了所定义列的类型。

<datatype>

BINARY_DOUBLE

BINARY_FLOAT

BFILE

BLOB

CLOB

CHARACTER SET ANY_CS

DATE

DSINTERVAL_UNCONSTRAINED

NUMBER

TIME_UNCONSTRAINED

TIME_TZ_UNCONSTRAINED

TIMESTAMP_LTZ_UNCONSTRAINED

TIMESTAMP_TZ_UNCONSTRAINED

TIMESTAMP_UNCONSTRAINED

UROWID

YMINTERVAL_UNCONSTRAINED

user-defined object types

collections (varrays and nested tables)

refs

opaque types

column_size

类型为CHAR,VARCHAR2,TEXT的列的预期列值的最大字节大小

示例

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。

参数说明

参数

描述

handle

游标ID

pos

列在定义行中的相对位置。语句在第一列位置为1。

val

定义的列的值。此值的类型决定了所定义列的类型。

<datatype>

CHAR

VARCHAR

TEXT

column_size

类型为CHAR,VARCHAR2,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调用的表中。

参数说明

参数

描述

handle

游标ID

pos

列在定义行中的相对位置。语句在第一列位置为1。

val

定义的列的值。此值的类型决定了所定义列的类型。

<datatype>

clob_table

binary_float_table

binary_double_table

blob_table

bfile_table

date_table

number_table

urowid_table

varchar2_table

time_table

time_with_time_zone_table

timestamp_table

timestamp_with_ltz_table

timestamp_with_time_zone_table

interval_day_to_second_table

interval_year_to_month_table

cnt

必须提取的行数

lower_bnd

从下限索引开始，将结果复制到集合中

示例

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语句，其他类型的语句返回值是不确定的）。

参数说明

参数

描述

handle

游标ID

返回值

返回已处理的行数。

使用说明

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判断是否检索到数据。

参数说明

参数

描述

handle

游标ID

返回值

返回实际获取的行数。

使用说明

如果该游标不是关联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;

功能描述

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

参数说明

参数

描述

handle

游标ID

exact

设置为TRUE，如果当查询行的数量与检索行的数量不同，则抛出异常。

返回值

返回指定的行数。

示例

参照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调用后的数据。

参数说明

参数

描述

handle

游标ID

pos

列在定义行中的相对位置。语句在第一列位置为1。

val

返回指定列处的值。

<datatype>

BINARY_DOUBLE

BINARY_FLOAT

BFILE

BLOB

CLOB CHARACTER SET ANY_CS

DATE

DSINTERVAL_UNCONSTRAINED

NUMBER

TIME_TZ_UNCONSTRAINED

TIME_UNCONSTRAINED

TIMESTAMP_LTZ_UNCONSTRAINED

TIMESTAMP_TZ_UNCONSTRAINED

TIMESTAMP_UNCONSTRAINED

UROWID

VARCHAR2 CHARACTER SET ANY_CS

YMINTERVAL_UNCONSTRAINED

user-defined object types

collections (varrays and nested tables)

refs

opaque types

示例

使用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调用后的数据。

参数说明

参数

描述

handle

游标ID

pos

列在定义行中的相对位置。语句在第一列位置为1。

val

返回指定列处的值。类型为

<table_type>

clob_table

binary_float_table

binary_double_table

blob_table

bfile_table

date_table

number_table

urowid_table

varchar2_table

time_table

time_with_time_zone_table

timestamp_table

timestamp_with_ltz_table

timestamp_with_time_zone_table

interval_day_to_second_table

interval_year_to_month_table

示例

使用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语句中绑定变量的值。

参数说明

参数

描述

handle

游标ID

pos

需要返回值的占位符的位置

val

返回指定位置的变量的值。

<datatype> BINARY_DOUBLE

BINARY_FLOAT

BFILE

BLOB

CLOB CHARACTER SET ANY_CS

DATE

DSINTERVAL_UNCONSTRAINED

NUMBER

TIME_TZ_UNCONSTRAINED

TIME_UNCONSTRAINED

TIMESTAMP_LTZ_UNCONSTRAINED

TIMESTAMP_TZ_UNCONSTRAINED

TIMESTAMP_UNCONSTRAINED

UROWID

VARCHAR2 CHARACTER SET ANY_CS

YMINTERVAL_UNCONSTRAINED

user-defined object types

collections (varrays and nested tables)

refs

opaque types

示例

使用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语句中绑定变量的值。

参数说明

参数

描述

handle

游标ID

pos

需要返回值的占位符的位置

val

数组选项返回指定位置的变量的值。

<table_type>

clob_table

binary_float_table

binary_double_table

blob_table

bfile_table

date_table

number_table

urowid_table

varchar2_table

time_table

time_with_time_zone_table

timestamp_table

timestamp_with_ltz_table

timestamp_with_time_zone_table

interval_day_to_second_table

interval_year_to_month_table

示例

使用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过程。

参数说明

参数

描述

handle

被描述列的游标ID号

col_cnt

select语句中列表中的列数

desc_t

返回描述表，表中记录了查询列表中每个列的描述

示例

对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，并释放占用的内存空间。

参数说明

参数

模式

描述

handle

IN

游标ID

handle

OUT

游标被设置为NULL。在调用CLOSE_CURSOR后，分配给游标的内存被释放，您不能再从该游标中获取数据。

示例

参照OPEN_CURSOR示例。

22.4.2.12. 判断游标是否打开

语法格式

DBMS_SQL.IS_OPEN (
    c              IN INTEGER)
    RETURN BOOLEAN;

功能描述

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

参数说明

参数

模式

描述

c

IN

游标ID

返回值说明

对于已打开但未关闭的任何游标编号返回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游标号。

参数说明

参数

描述

c

游标变量

返回值说明

返回从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游标一起使用。

参数说明

参数

描述

c

游标ID

返回值说明

返回从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)

参数说明

参数

描述

rc

游标变量或游标ID

to_client

预留参数，暂无意义

功能描述

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)

参数

描述

c

表示一个已经打开的游标ID，该游标直接或间接调用一个子程序，而子程序调用RETURN_RESULT过程隐式返回一个查询结果。

rc

表示一个SYS_REFCURSOR类型的游标变量或者一个已经打开的游标ID，接收一个RETURN_RESULT过程返回的结果。

功能描述

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函数代码表

SQL代码

SQL语句

SQL代码

SQL语句

SQL代码

SQL语句

01

CREATE TABLE

43

(NOT USED)

85

TRUNCATE TABLE

02

SET ROLE

44

(NOT USED)

86

(NOT USED)

03

INSERT

45

(NOT USED)

87

(NOT USED)

04

SELECT

46

(NOT USED)

88

(NOT USED)

05

UPDATE

47

(NOT USED)

89

(NOT USED)

06

DROP ROLE(USER)

48

(NOT USED)

90

SET CONSTRAINTS

07

DROP VIEW

49

(NOT USED)

91

CREATE FUNCTION

08

DROP TABLE

50

(NOT USED)

92

(NOT USED)

09

DELETE

51

(NOT USED)

93

DROP FUNCTION

10

CREATE VIEW

52

(NOT USED)

94

CREATE PACKAGE

11

(NOT USED)

53

(NOT USED)

95

(NOT USED)

12

CREATE ROLE(USER)

54

(NOT USED)

96

DROP PACKAGE

13

CREATE SEQUENCE

55

(NOT USED)

97

(NOT USED)

14

ALTER SEQUENCE

56

(NOT USED)

98

(NOT USED)

15

(NOT USED)

57

(NOT USED)

99

DROP PACKAGE BODY

16

DROP SEQUENCE

58

(NOT USED)

157

(NOT USED)

17

CREATE SCHEMA

59

CREATE TRIGGER

158

DROP DIRECTORY

18

(NOT USED)

60

(NOT USED)

159

(NOT USED)

19

(NOT USED)

61

DROP TRIGGER

160

(NOT USED)

20

CREATE INDEX

62

ANALYZE TABLE

161

(NOT USED)

21

DROP INDEX

63

(NOT USED)

162

(NOT USED)

22

(NOT USED)

64

(NOT USED)

163

CREATE OPERATOR

23

(NOT USED)

65

(NOT USED)

164

(NOT USED)

24

(NOT USED)

66

(NOT USED)

165

(NOT USED)

25

(NOT USED)

67

(NOT USED)

166

(NOT USED)

26

(NOT USED)

68

DROP PROCEDURE

167

DROP OPERATOR

27

EXPLAIN

69

(NOT USED)

168

(NOT USED)

28

GRANT

70

(NOT USED)

169

(NOT USED)

29

(NOT USED)

71

(NOT USED)

170

CALL METHOD

30

CREATE SYNONYM

72

(NOT USED)

171

(NOT USED)

31

DROP SYNONYM

73

(NOT USED)

172

(NOT USED)

32

(NOT USED)

74

(NOT USED)

173

(NOT USED)

33

SET TRANSACTION

75

(NOT USED)

174

(NOT USED)

34

PL/SQL EXECUTE

76

(NOT USED)

175

(NOT USED)

35

LOCK

77

CREATE TYPE

176

(NOT USED)

36

(NOT USED)

78

DROP TYPE

177

(NOT USED)

37

RENAME

79

(NOT USED)

178

(NOT USED)

38

COMMENT

80

(NOT USED)

179

(NOT USED)

39

(NOT USED)

81

(NOT USED)

180

(NOT USED)

40

(NOT USED)

82

(NOT USED)

181

(NOT USED)

41

(NOT USED)

83

DROP TYPE BODY

182

(NOT USED)

42

(NOT USED)

84

(NOT USED)

183

ALTER OPERATOR

示例

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安装包一并升级。通常，用户无须单独升级该插件。

沉舟侧畔千帆过_
关注
专栏目录
金仓数据库KingbaseES支持的Oracle扩展汇总
Kingbase_的博客
03-10 1934
正文 通常，异构数据库移植的工作量繁重。从Oracle向KingbasES移植的情况也如此。为降低移植工作量，KingbaseES在内部实现了大量的Oracle兼容特性。这些特性从语法或功能上对Oracle 提供了原生支持。因此，在移植过程中，Oracle 程序只需很少甚至不做任何改动就能在KingbaseES 环境中运行。 KingbaseES用户可通过设置相关的数据库兼容开关，部分或全部启用Oracle兼容特性。在实际应用中，用户可采用以下途径设置Oracle兼容开关： • 在数据库实例data 目录下
金仓数据库KingbaseES扩展简介
Kingbase_的博客
02-15 1200
一个扩展包通常需要一个包含创建该扩展的对象的SQL命令的脚本文件以及一个指定扩展本身的一些基本属性的控制文件。如果扩展包括C代码，通常还有一个C代码编译而成的共享库文件。控制文件和脚本默认被放在数据库的安装目录下的 Server/share/extension 目录中，C 编译的共享库文件则放在 Server/lib 中。 扩展常用的命令，请注意必须用超级用户执行： CREATE EXTENSION extension_name:此命令将安装一个名为extension_name的新扩展 ALTER EXTE
人大金仓数据库KingbaseES INSERT…SET…语法介绍
arthemis_14的博客
11-09 1037
KingbaseES、INSERT、MYSQL、人大金仓。
金仓数据库KingbaseES 数据插入更新操作
arthemis_14的博客
05-30 1771
数据库使用过程中，经常会遇到一种场景：业务系统对数据进行dml操作，当数据库中数据不存在时，将数据做为新记录插入到表中，当数据库中数据存在时，对现有数据进行更新操作。下面介绍KingbaseES中对上述的业务场景的应对方式。
数据库（DB、DBMS、SQL）
最新发布
b19839356939的博客
09-11 1859
数据库，SQL，DB,DBMS,RDBMS，表，DDL
人大金仓 金仓数据库KingbaseES 新兼容——last_insert_id()
arthemis_14的博客
08-18 507
③在last_insert_id(expr)调用后立即调用select last_insert_id()，select last_insert_id()的返回结果与前面last_insert_id(expr)的返回结果一致;调用函数插入元组，在函数内部调用last_insert_id()，返回一条语句中第一个成功插入元组使用自增列生成的值，在函数外部调用last_insert_id()，返回旧值；last_insert_id(expr)函数的调用会影响last_insert_id()函数的返回值，
金仓数据库 KingbaseES 插件参考手册 H
arthemis_14的博客
08-26 468
插件http是KingbaseES 的一个扩展插件。插件功能是提供数据库的http功能，允许在数据库内检索网页。插件名为 http插件版本 V1.4。
金仓数据库 KingbaseES 插件参考手册 pg_partman
arthemis_14的博客
08-30 1108
partman是pg的一个扩展，可帮助使用者更轻松地管理基于time或serialid的分区表。partman只需要几个选项，就可以设置分区。目前，触发器函数仅处理对父表的插入。仅在PG 11 本地分区中支持将值从一个分区移动到另一个分区的update。partman从版本3.0.1开始，支持PG10中引入的本地分区的方法。在本地分区中不再需要触发器函数，但自动子表创建本地分区不处理，partman会做子表自动创建。...
Liquibase集成金仓数据库KingBase
qq_38633651的博客
11-15 2162
Liquibase集成金仓数据库KingBase KingBase针对数据库Boolean类型做特殊处理，sql语句只能解析字符串样式的boolean类型，对应数据库字段类型为bit类型 在BooleanType.java中，修改如下方法 @Override public DatabaseDataType toDatabaseDataType(Database database) { String originalDefinition = StringUtil.trimToEmpty(getRa
金仓数据库 KingbaseES 插件DBMS_UTILITY
arthemis_14的博客
06-24 337
金仓数据库 KingbaseES 插件DBMS_UTILITY
金仓数据库 KingbaseES 插件参考手册 S(4)
arthemis_14的博客
08-31 1030
sys_spacequota可以对指定表空间的大小进行限额，一旦大于该限额，对此表空间里的数据进行insert, update, copy to,select into, create index操作将会被警告或终止（默认为警告，警告或者终止可以通过GUC参数`` spacequota.fullwarningtype = 0/1``来配置）。插件名为 sys_spacequota插件版本 V1.0。...
人大金仓数据库KingbaseES ODBC批量操作
arthemis_14的博客
12-12 1182
在ODBC中如果需要插入多条的数据（如向创建的表中插入一万条数据）等需要大量重复执行DML语句的情况下，会造成操作耗时长，数据库服务器IO的函数占比大等问题。而使用批量执行DML的方法，能够减少向数据库发送插入语句等DML语句报文，较少与服务器之间的IO交互，能提升插入效率，缩短执行时间。
KingbaseES数据库select详细用法
m0_61066945的博客
04-09 1447
SELECT语法的应用，包括条件过滤、聚合函数、排序、分组、子查询和连接查询。
KingbaseES数据库insert技巧
m0_61066945的博客
03-12 2195
快速插入万行数据、随机数、随机字符、随机日期等
KingbaseES insert all/first 功能介绍
lyu1026的博客
08-06 852
KingbaseES 内置了对于insert all / first 语法的支持。 一、数据准备 create table t1(product_id number, product_name varchar2(80),month number); create table t2(product_id number, product_name varchar2(80),month numb...
DBMS_SQL使用
刚木的博客
01-19 2182
一、简介 DBMS_SQL包提供一个接口，用于执行动态SQL（包括DDL 和DML）。 DBMS_SQL定义了一个实体叫游标ID，游标ID是一个PL/SQL整型数，通过游标ID，可以对游标进行操作。 DBMS_SQL包和本地动态SQL在功能上有许多重叠的地方，但是有的功能只能通过本地动态SQL实现，而有些功能只能通过DBMS_SQL实现。 二、主要函数 DBMS_SQL封装过程中的主要函数： 1、OPEN_CURSOR：返回新游标的ID值 2、PARSE：解析要执行的语句 3、BIND_VARIABLE：将
金仓数据库KingbaseES数据库管理员指南--15.3. 管理同义词
arthemis_14的博客
07-20 394
金仓数据库KingbaseES数据库管理员指南--15.3. 管理同义词
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值