DBMS_SQL

DBMS_SQL包提供了一个接口，可以使用动态SQL通过PL/pgSQL解析任何数据操作语言（DML）或数据定义语言（DDL）语句。

Vastbase支持了此内置包的部分数据类型和子程序。

数据类型

功能描述

在DBMS_SQL包中定义了一系列record类型、table类型的数据结构。如下数据结构方便在自定义函数或存储过程中进行变量的数据类型定义：

Record类型有：desc_rec、desc_rec2、desc_rec3、desc_rec4。
Table类型有：desc_tab、desc_tab2、desc_tab3、desc_tab4、number_table、varchar2_table、date_table、blob_table、clob_table、binary_double_table、binary_float_table。
常量类型：dbms_sql.native、dbms_sql.v6、dbms_sql.v7。

Record类型记录动态查询中单个列的详细信息，table类型是对应record类型的集合。通常同时搭配着DESCRIBE_COLUMNS等存储过程获取列信息。

desc_rec和desc_tab类型

功能描述

desc_rec记录类型保存动态查询中单个列的描述信息。desc_tab是一个表DESC_REC Record 类型。

该类型与DESCRIBE_COLUMNS存储过程搭配使用。

语法格式

desc_rec

TYPE desc_rec IS RECORD (
  col_type            BINARY_INTEGER := 0,
  col_max_len         BINARY_INTEGER := 0,
  col_name            VARCHAR2(32)   := '',
  col_name_len        BINARY_INTEGER := 0,
  col_schema_name     VARCHAR2(32)   := '',
  col_schema_name_len BINARY_INTEGER := 0,
  col_precision       BINARY_INTEGER := 0,
  col_scale           BINARY_INTEGER := 0,
  col_charsetid       BINARY_INTEGER := 0,
  col_charsetform     BINARY_INTEGER := 0,
  col_null_ok         BOOLEAN        := TRUE);

desc_tab

TYPE desc_tab IS TABLE OF desc_rec INDEX BY BINARY_INTEGER;

参数说明

col_type：动态查询中单个列数据类型对应的编号，编号如下：

Code	Data Type
1	VARCHAR2(size [BYTE \| CHAR])
1	NVARCHAR2(size)
2	NUMBER [ (p [, s]) ]
2	FLOAT [(p)]
8	LONG
12	DATE
100	BINARY_FLOAT
101	BINARY_DOUBLE
180	TIMESTAMP [(fractional_seconds_precision)]
181	TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
231	TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE
182	INTERVAL YEAR [(year_precision)] TO MONTH
183	INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
23	RAW(size)
24	LONG RAW
69	ROWID
208	UROWID [(size)]
96	CHAR [(size [BYTE \| CHAR])]
96	NCHAR[(size)]
112	CLOB
112	NCLOB
113	BLOB
114	BFILE
119	JSON

col_max_len：动态查询中单个列中数据类型长度。
col_name：动态查询中单个列名。
col_name_len：动态查询中单个列名字符长度。
col_schema_name：动态查询中单个列是自定义type类型返回自定义type所属的schema名，否则返回空。
col_schema_name_len：动态查询中单个列的数据类型所属schema名字长度。
col_precision：动态查询中单个列的数据类型精度，只有数字类型会存在精度，字符或时间等其他类型该值为0。
col_scale：动态查询中单个列的数据类型允许小数点后几位数。
col_charsetid：动态查询中单个列数据类型的字符集id，只有字符串类型存在字符集，其余数据类型字符集设置为0。目前只需要输出如下两种字符集类型即可：

字符集字符集id

GBK 852

UTF8 871
col_charsetform：动态查询中单个列数据类型的字符集，如果是字符串类型输出1，其余数据类型保持默认值0。
col_null_ok：动态查询中单个列的值是否为空。

字符集	字符集id
GBK	852
UTF8	871

desc_rec2和desc_tab2类型

功能描述

DESC_REC2是DESC_TAB2表类型和DESCRIBE_COLUMNS2存储过程的元素类型。

该类型与DESCRIBE_COLUMNS2存储过程搭配使用。

语法格式

desc_rec

TYPE desc_rec IS RECORD (
  col_type            BINARY_INTEGER := 0,
  col_max_len         BINARY_INTEGER := 0,
  col_name            varchar2(32767):= '',
  col_name_len        BINARY_INTEGER := 0,
  col_schema_name     VARCHAR2(32)   := '',
  col_schema_name_len BINARY_INTEGER := 0,
  col_precision       BINARY_INTEGER := 0,
  col_scale           BINARY_INTEGER := 0,
  col_charsetid       BINARY_INTEGER := 0,
  col_charsetform     BINARY_INTEGER := 0,
  col_null_ok         BOOLEAN        := TRUE);

desc_tab2

TYPE desc_tab2 IS TABLE OF desc_rec2 INDEX BY BINARY_INTEGER;

desc_rec3和desc_ta3类型

功能描述

DESC_REC3是DESC_TAB3表类型和DESCRIBE_COLUMNS3存储过程的元素类型。

该类型与DESCRIBE_COLUMNS3存储过程搭配使用。

语法格式

desc_rec3

TYPE desc_rec3 IS RECORD (
  col_type               binary_integer := 0,
  col_max_len            binary_integer := 0,
  col_name               varchar2(32767) := '',
  col_name_len           binary_integer := 0,
  col_schema_name        varchar2(32) := '',
  col_schema_name_len    binary_integer := 0,
  col_precision          binary_integer := 0,
  col_scale              binary_integer := 0,
  col_charsetid          binary_integer := 0,
  col_charsetform        binary_integer := 0,
  col_null_ok            boolean := TRUE,
  col_type_name          varchar2(32767)   := '',
  col_type_name_len      binary_integer := 0);

desc_rec3相比desc_rec2新增加了两个属性，分别是类型的名字和类型的长度。并且系统的数据类型不会进行填充，只有当是自定义type类型时候才会进行赋值。

desc_tab3

TYPE desc_tab3 IS TABLE OF desc_rec3 INDEX BY BINARY_INTEGER;

desc_rec4和desc_ta4类型

功能描述

DESC_REC4是DESC_TAB4表类型和DESCRIBE_COLUMNS4存储过程的元素类型。

该类型与DESCRIBE_COLUMNS4存储过程搭配使用。

语法格式

desc_rec4

TYPE desc_rec4 IS RECORD (
col_type               binary_integer := 0,
col_max_len            binary_integer := 0,
col_name               varchar2(32767) := '',
col_name_len           binary_integer := 0,
col_schema_name        varchar2(32) := '',
col_schema_name_len    binary_integer := 0,
col_precision          binary_integer := 0,
col_scale              binary_integer := 0,
col_charsetid          binary_integer := 0,
col_charsetform        binary_integer := 0,
col_null_ok            boolean := TRUE,
col_type_name          varchar2(32767)   := '',
col_type_name_len      binary_integer := 0);

desc_tab4

TYPE desc_tab4 IS TABLE OF desc_rec4 INDEX BY BINARY_INTEGER;

number_table、varcha2_table、date_table、blob_table、clob_table、binary_double_table、binary_float_table类型

功能描述

上述类型与bind_array存储过程一起调用，该存储过程根据传入动态SQL进行table变量进行绑定作用。

语法格式

DBMS_SQL.BIND_ARRAY ( 
  c          IN INTEGER, 
  name         IN VARCHAR2, 
  <table_variable>   IN <datatype> 
 [,index1        IN INTEGER, 
  index2        IN INTEGER)] );

常量类型

常量	值	描述
dbms_sql.native	1	指定程序所连接的数据库的一般行为。
dbms_sql.v6	0	指定Oracle V6 版本的行为。
dbms_sql.v7	2	指定Oracle V7 版本的行为。

子程序

功能描述

DBMS_SQL主要功能是提供用于执行动态SQL(DML和DDL)的方法。该内置包包含以下函数：

函数	描述
OPEN_CURSOR	打开一个cursor，并返回该cursor的id。
CLOSE_CURSOR	用于关闭一个cursor。
COLUMN_VALUE	用于将fetch结果中的指定列保存到返回参数中。
COLUMN_VALUE_CHAR	用于从游标接收CHAR值的变量。该函数仅在Vastbase G100 V2.2 Build 15(Patch No.4)版本开始支持。
COLUMN_VALUE_RAW	用于从游标接收RAW值的变量。该函数仅在Vastbase G100 V2.2 Build 15(Patch No.4)版本开始支持。
DEFINE_COLUMN	用于定义从给定游标中返回的列，必须与SELECT定义的游标结合使用。
DEFINE_COLUMN_RAW	定义 SELECT 列表中要在游标中返回和检索的 RAW 列或表达式。该函数仅在Vastbase G100 V2.2 Build 15(Patch No.4)版本开始支持。
DEFINE_COLUMN_CHAR	定义 SELECT 列表中要在游标中返回和检索的 CHAR 列或表达式。该函数仅在Vastbase G100 V2.2 Build 15(Patch No.4)版本开始支持。
EXECUTE	用于执行PARSE函数处理的SQL语句。
FETCH_ROWS	用于载入结果集中的一行结果。
PARSE	解析当前语句的语法。
RETURN_RESULT	用于将已执行语句的结果返回到客户端应用程序。
BIND_ARRAY	该存储过程根据传入动态SQL进行table变量进行绑定作用。
BIND_VARIABLE	该存储过程根据传入动态SQL进行变量的绑定。
DESCRIBE_COLUMNS DESCRIBE_COLUMNS2 DESCRIBE_COLUMNS3	该存储过程输出列的元数据信息。
DEFINE_ARRAY	定义要从给定游标中选择的集合，仅用于SELECT语句。
IS_OPEN	该函数用于判断游标是否打开。
FETCH_ROWS	该函数遍历DBMS_SQL.EXECUTE执行动态SQL返回结果数据集，DEFINE_COLUMN定义动态SQL返回的变量名、通过COLUMN_VALUE存储过程定义返回列的值。

示例

1、创建测试表并插入数据。

create table dbms_t1(id number,con varchar(20));
insert into dbms_t1 values(1.5,'aaa'),(2.0,'bbb'),(3,'ccc');

2、创建存储过程调用函数。

CREATE OR REPLACE function dbms_demo1() return text AS
    cursor_name INT;
    rows_processed INT;
            ret INT;
            rec text;
BEGIN
    cursor_name := DBMS_SQL.open_cursor();
            DBMS_SQL.PARSE(cursor_name,'select con from public.dbms_t1;',0);
            ret := DBMS_SQL.EXECUTE(cursor_name);
            loop
            if DBMS_SQL.FETCH_ROWS(cursor_name) > 0 then
                    select DBMS_SQL.COLUMN_VALUE(cursor_name,1,rec) into rec;
            else
                    exit;
            end if;
            end loop;
            DBMS_SQL.CLOSE_CURSOR(cursor_name);
            return rec;
END;
/

3、调用存储过程dbms_demo1()。

 select dbms_demo1();

当结果显示如下，则表示函数调用成功：

 dbms_demo1
------------
 ccc
(1 row)

4、创建测试表re_t1，re_t2并插入数据。

create table re_t1(c1 int,c2 varchar(32));
insert into re_t1 values(1,'111');
insert into re_t1 values(2,'222');
create table re_t2(c1 varchar(32),c2 int);
insert into re_t2 values('111',1);
insert into re_t2 values('222',2);

5、使用sys_refcursor类型作为输入。

create or replace procedure re_proc() as
rc1 sys_refcursor;
rc2 sys_refcursor;
begin
open rc1 for select * from re_t1;
dbms_sql.return_result(rc1);
open rc2 for select * from re_t2;
dbms_sql.return_result(rc2);
end;
/

6、调用存储过程re_proc()。

call re_proc();

当返回结果如下，则表示函数调用成功：

ResultSet #1

 c1 | c2
----+-----
  1 | 111
  2 | 222
(2 rows)

ResultSet #2

 c1  | c2
-----+----
 111 |  1
 222 |  2
(2 rows)

 re_proc
---------

(1 row)

7、使用integer类型作为输入参数。

create or replace procedure re_proc_int() as
id number;
id1 number:=dbms_sql.open_cursor();
id2 number:=dbms_sql.open_cursor();
begin
dbms_sql.parse(id1,'select * from public.re_t1',0);
id:=dbms_sql.execute(id1);
dbms_sql.return_result(id1);
dbms_sql.parse(id2,'select * from public.re_t2',0);
id:=dbms_sql.execute(id2);
dbms_sql.return_result(id2);
end;
/

8、调用存储过程re_proc_int()。

call re_proc_int();

当结果返回如下，则表示调用成功：

ResultSet #1

 c1 | c2
----+-----
  1 | 111
  2 | 222
(2 rows)

ResultSet #2

 c1  | c2
-----+----
 111 |  1
 222 |  2
(2 rows)

 re_proc_int
-------------

(1 row)

OPEN_CURSOR

语法格式

DBMS_SQL.OPEN_CURSOR (
   c    IN OUT INTEGER);

参数说明

c：需要打开的游标ID号。

CLOSE_CURSOR

语法格式

DBMS_SQL.CLOSE_CURSOR (
   c    IN OUT INTEGER);

参数说明

c：需要关闭的游标ID号。

COLUMN_VALUE

语法格式

DBMS_SQL.COLUMN_VALUE (
   c                 IN  INTEGER,
   position          IN  INTEGER,
   value             OUT <datatype> 
 [,column_error      OUT NUMBER] 
 [,actual_length     OUT INTEGER]);

参数说明

c：游标ID号。
position：光标中列的相对位置、语句中的第一列位置为1。
value：返回指定列的值，支持NUMBER类型。

Vastbase G100 V2.2 Build 15(Patch No.4)版本开始，该参数支持的数据类型为anyelement（表示函数接受任何数据类型）。

column_error：返回指定列值的任何错误代码。
actual_length：指定列的值在截断之前的实际长度。

COLUMN_VALUE_CHAR

语法格式

DBMS_SQL.COLUMN_VALUE_CHAR (
   c               IN  INTEGER,
   position        IN  INTEGER,
   column          OUT CHAR CHARACTER SET ANY_CS);

参数说明

c：游标ID号（指定将数据返回到所定义变量的游标的标识）。
position：用于指定所返回数据在游标中的位置。游标中的第一个值为位置 1。
column：返回指定的列的值，仅支持CHAR类型。

示例

1、创建测试表并插入数据。

create table emp(
empno number(10) primary key,
ename char(30),
job varchar2(30),
mgr varchar2(30),
hiredate number(10),
sal number(10),
comm number(10),
deptno number(10)
);

insert into emp values(1, '关羽羽', 'CLERK' ,'刘备备', 20011109, 2000, 1000, 3);
insert into emp values(2, 'SMITH', 'CLERK' ,'刘备备', 20120101, 2000, 800, 6);
insert into emp values(3, '刘备备', 'MANAGER' ,'宋祖英', 20080808, 9000, 4000, 3);
insert into emp values(4, 'TOM', 'ENGINEER' ,'Steve', 20050612, 3000, 1000, 1);
insert into emp values(5, 'Steve', 'MANAGER' ,'宋祖英', 20110323, 80000, 9000, 1);
insert into emp values(6, '张飞飞', 'CLERK' ,'刘备备', 20101010, 2000, 1000, 3);
insert into emp values(7, 'SCOTT', 'CLERK' ,'刘备备', 20071204, 2000, 1000, 3);
insert into emp values(8, '宋祖英', 'Boss' ,'无', 20060603, 2000, 1000, 8);
insert into emp values(9, '曹仁人', 'SALESMAN' ,'曹操操', 20120130, 2000, 1000, 2);
insert into emp values(10, '曹操操', 'MANAGER' ,'宋祖英',20090815, 2000, 1000, 2);
insert into emp values(11, '酱油哥', 'HAPI' ,'XXX',20090215, 3, 1, 2);

2、创建存储过程。

CREATE OR REPLACE PROCEDURE showemps (where_in IN VARCHAR2 := NULL)
IS
cur INTEGER := DBMS_SQL.OPEN_CURSOR;
rec emp%ROWTYPE;
fdbk INTEGER;
BEGIN
DBMS_SQL.PARSE (cur, 'SELECT empno, ename FROM emp ' || ' WHERE ' || NVL (where_in, '1=1'), DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN (cur, 1, rec.empno);
DBMS_SQL.DEFINE_COLUMN (cur, 2, rec.ename, 30);
fdbk := DBMS_SQL.EXECUTE (cur);
LOOP
EXIT WHEN DBMS_SQL.FETCH_ROWS (cur) = 0;
DBMS_SQL.COLUMN_VALUE(cur, 1, rec.empno);
DBMS_SQL.COLUMN_VALUE_CHAR(cur, 2, rec.ename);
DBMS_OUTPUT.PUT_LINE (TO_CHAR (rec.empno) || '=' || rec.ename);
END LOOP;
DBMS_SQL.CLOSE_CURSOR (cur);
END;
/

3、调用存储过程。

set serveroutput on;
call showemps(null);

返回结果为：

1=关羽羽
2=SMITH
3=刘备备
4=TOM
5=Steve
6=张飞飞
7=SCOTT
8=宋祖英
9=曹仁人
10=曹操操
11=酱油哥
 showemps
----------

(1 row)

4、清理环境。

drop table emp;
drop procedure showemps;

COLUMN_VALUE_RAW

语法格式

DBMS_SQL.COLUMN_VALUE_RAW (
   c               IN  INTEGER,
   position        IN  INTEGER,
   column           OUT RAW

参数说明

c：游标ID号（指定将数据返回到所定义变量的游标的标识）。
position：用于指定所返回数据在游标中的位置。游标中的第一个值为位置 1。
column：返回指定的列的值，仅支持RAW类型。

示例

1、创建测试表并插入数据。

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

2、调用存储过程。

set serveroutput on;

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);
DBMS_SQL.COLUMN_VALUE_RAW(c, 1, r_var);
DBMS_OUTPUT.PUT_LINE('COLUMN c is: ' || r_var);
DBMS_SQL.CLOSE_CURSOR(c);
END;
/

返回结果为：


COLUMN c is:
ANONYMOUS BLOCK EXECUTE

3、清理环境。

drop table test;

DEFINE_COLUMN

语法格式

DBMS_SQL.DEFINE_COLUMN (
   c              IN INTEGER,
   position       IN INTEGER,
   column         IN <datatype>
   [column_size   IN INTEGER]);

参数说明

c：游标ID号（指定将数据返回到所定义变量的游标的标识）。
position：正在定义的行中列的相对位置，语句中第一列的位置为1。
colum：被定义的列的值。此值的类型决定了要定义的列的类型。支持四种数据类型：CHAR，VARCHAR，NUMBER，DATE（oradate）。

Vastbase G100 V2.2 Build 15(Patch No.4)版本开始，该参数支持的数据类型为anyelement（表示函数接受任何数据类型）。

column_size：当column类型为VARCHAR时，支持配置该选项，用来设置列值的最大预期大小（以字节为单位）。

示例 DEFINE_COLUMN入参为date类型。

1、创建存储过程。

set serveroutput on;
create or replace procedure proc_1196474()
as
cursorid int;
v_id date;
query varchar(2000);
execute_ret int;
define_column_ret int;
begin
drop table if exists pro_dbms_sql_all_tb1_02 ;
create temp table pro_dbms_sql_all_tb1_02(a date ,b int);
create index on pro_dbms_sql_all_tb1_02(a);
insert into pro_dbms_sql_all_tb1_02 values('2022-01-01',3);
insert into pro_dbms_sql_all_tb1_02 values('2022-01-02',2);
drop table if exists pro_dbms_sql_all_tb1_03 ;
create temp table pro_dbms_sql_all_tb1_03(a date ,b int);
create index on pro_dbms_sql_all_tb1_03(a);
insert into pro_dbms_sql_all_tb1_03 values('2022-01-03',1);
query := 'select * from pro_dbms_sql_all_tb1_02 order by 2';
--打开游标
cursorid := dbms_sql.open_cursor();
--编译游标
dbms_sql.parse(cursorid, query, 1);
--定义列
select dbms_sql.define_column(cursorid,1,a) from pro_dbms_sql_all_tb1_03 into define_column_ret;
--执行
execute_ret := dbms_sql.execute(cursorid);
dbms_sql.fetch_rows(cursorid);
--获取值
dbms_sql.column_value(cursorid,1,v_id);
--输出结果
dbms_output.put_line('id:'|| v_id );
--关闭游标
dbms_sql.close_cursor(cursorid);
end;
/

2、调用存储过程。

call proc_1196474();

返回结果为：

id:2022-01-02 00:00:00
proc_1196474
--------------

(1 row)

DEFINE_COLUMN_CHAR

语法格式

DBMS_SQL.DEFINE_COLUMN_CHAR (
   c              IN INTEGER,
   position       IN INTEGER,
   column         IN CHAR CHARACTER SET ANY_CS);

参数说明

c：游标ID号（指定将数据返回到所定义变量的游标的标识）。
position：用于指定要定义的列或表达式在 SELECT 列表中的位置，语句中第一列的位置为1。
colum：被定义的列的值，仅支持CHAR类型。

示例

1、创建测试表并插入数据。

CREATE TABLE example_text_table (
id NUMBER,
text_data CLOB
);

-- 插入数据
INSERT INTO example_text_table (id, text_data) VALUES (1, 'This is a text example.');
INSERT INTO example_text_table (id, text_data) VALUES (2, 'Another text example with more content.');
INSERT INTO example_text_table (id, text_data) VALUES (3, 'Yet another text example.');

2、创建存储过程。

CREATE OR REPLACE PROCEDURE extract_clob_data IS
l_cursor_id NUMBER;
l_dummy NUMBER;
l_text_data VARCHAR2(32767);
BEGIN
-- 打开游标
l_cursor_id := dbms_sql.open_cursor;

-- 解析 SQL 语句
dbms_sql.parse(l_cursor_id, 'SELECT text_data FROM example_text_table', dbms_sql.native);

dbms_sql.define_column_char(l_cursor_id, 1, l_text_data);

-- 执行 SQL 语句
l_dummy := dbms_sql.execute(l_cursor_id);

-- 提取数据
WHILE dbms_sql.fetch_rows(l_cursor_id) > 0 LOOP
-- 提取 CLOB 数据并存储在 VARCHAR2 变量中
dbms_sql.column_value_char(l_cursor_id, 1, l_text_data);

-- 输出结果
dbms_output.put_line('Text Data: ' || l_text_data);
END LOOP;

-- 关闭游标
dbms_sql.close_cursor(l_cursor_id);
EXCEPTION
WHEN OTHERS THEN
IF dbms_sql.is_open(l_cursor_id) THEN
dbms_sql.close_cursor(l_cursor_id);
END IF;
RAISE;
END;
/

3、调用存储过程。

set serveroutput on;
call extract_clob_data();

返回结果为：

Text Data: This is a text example.
Text Data: Another text example with more content.
Text Data: Yet another text example.
 extract_clob_data
-------------------

(1 row)

4、清理环境。

drop table example_text_table;
drop procedure extract_clob_data;

DEFINE_COLUMN_RAW

语法格式

DBMS_SQL.DEFINE_COLUMN_RAW (
   c              IN INTEGER,
   position       IN INTEGER,
   column         IN RAW);

参数说明

c：游标ID号（指定将数据返回到所定义变量的游标的标识）。
position：用于指定要定义的列或表达式在 SELECT 列表中的位置，语句中第一列的位置为1。
colum：被定义的列的值，仅支持RAW类型。

示例

1、创建测试表并插入数据。

CREATE TABLE example_raw_table (
id NUMBER,
raw_data RAW(2000)
);

-- 插入数据
INSERT INTO example_raw_table (id, raw_data) VALUES (1, UTL_RAW.cast_to_raw('Hello, World!'));
INSERT INTO example_raw_table (id, raw_data) VALUES (2, UTL_RAW.cast_to_raw('Sample RAW Data'));
INSERT INTO example_raw_table (id, raw_data) VALUES (3, UTL_RAW.cast_to_raw('Oracle RAW Type Example'));

2、创建存储过程。

set serveroutput on;

DECLARE
l_cursor_id NUMBER;
l_dummy NUMBER;
l_raw_data RAW(2000);
l_raw_hex VARCHAR2(4000);
BEGIN
-- 打开游标
l_cursor_id := dbms_sql.open_cursor;

-- 解析 SQL 语句
dbms_sql.parse(l_cursor_id, 'SELECT raw_data FROM example_raw_table', dbms_sql.native);

-- 绑定输出变量
dbms_sql.define_column_raw(l_cursor_id, 1, l_raw_data);

-- 执行 SQL 语句
l_dummy := dbms_sql.execute(l_cursor_id);

-- 提取数据
WHILE dbms_sql.fetch_rows(l_cursor_id) > 0 LOOP
dbms_sql.column_value_raw(l_cursor_id, 1, l_raw_data);
-- 输出结果
dbms_output.put_line('RAW Data (Hex): ' || l_raw_data);
END LOOP;

-- 关闭游标
dbms_sql.close_cursor(l_cursor_id);
EXCEPTION
WHEN OTHERS THEN
IF dbms_sql.is_open(l_cursor_id) THEN
dbms_sql.close_cursor(l_cursor_id);
END IF;
RAISE;
END;
/

返回结果为：

RAW Data (Hex): 48656C6C6F2C20576F726C6421
RAW Data (Hex): 53616D706C65205241572044617461
RAW Data (Hex): 4F7261636C65205241572054797065204578616D706C65
ANONYMOUS BLOCK EXECUTE

4、清理环境。

drop table example_raw_table;

EXECUTE

语法格式

DBMS_SQL.EXECUTE (
   c   IN INTEGER)
  RETURN INTEGER;

参数说明

c：游标ID号。

FETCH_ROWS

语法格式

DBMS_SQL.FETCH_ROWS (
   c              IN INTEGER)
  RETURN INTEGER;

参数说明

c：ID号。

PARSE

语法格式

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：要解析的语句的游标ID号。
statement：要解析的SQL语句。
lb：语句元素的下限。
ub：语句中元素的上限。
lfflg：如果设置为TRUN，则在连接的每个元素之后插入换行符。
language_flag：指定 SQL 语句的行为。
edition：指定在以下条件下运行语句的版本：
- 如果是NULL该语句将在当前版本中运行。
- 如果指定了有效容器，则通过NULL表示该语句将在目标容器的默认版本中运行。
- 给定用户和执行语句的版本，用户必须USE对该版本具有特权。
以下一般条件适用。字符串的内容作为 SQL 标识符处理；如果版本的实际名称中存在特殊字符或小写字符，则必须用双引号将字符串的其余部分括起来，如果不使用双引号，则内容将大写。
apply_crossedition_trigger：指定要应用于指定 SQL 的正向交叉版本触发器的非限定名称。
fire_apply_trigger：指示apply_crossedition_trigger的内容本身是要执行的，还是只能作为用于选择其他触发器的指南。
schema：指定解析不合格对象名称的架构。如果NULL，则当前架构是有效用户的架构。
container：要在其中运行的目标容器的名称。

注意事项

dbms_sql.parse存储过程中，支持常量dbms_sql.native、dbms_sql.v7、dbms_sql.v6参数。

示例

parse函数标志位支持dbms_sql、dbms_sql.v6、dbms_sql.v7三种类型。

1、创建测试表。

drop table t_dbms_sql cascade;
CREATE TABLE t_dbms_sql(c1 int,c2 varchar(200));

2、调用parse函数。

SET SERVEROUTPUT ON;
DECLARE
c integer;
test_sql varchar2(200);
BEGIN
test_sql := 'INSERT INTO t_dbms_sql VALUES(1,''zhangsan'')';
c := dbms_sql.open_cursor;
dbms_sql.parse(c,test_sql,dbms_sql.native);
dbms_sql.parse(c,test_sql,dbms_sql.v6);
dbms_sql.parse(c,test_sql,dbms_sql.v7);
dbms_sql.parse(c,test_sql,0);
dbms_sql.parse(c,test_sql,1);
dbms_sql.parse(c,test_sql,2);
END;
/

RETURN_RESULT

语法格式

DBMS_SQL.RETURN_RESULT(
   rc           IN OUT      SYS_REFCURSOR, 
   to_client    IN          BOOLEAN           DEFAULT TRUE);

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

参数说明

rc：语句游标或引用游标。
to_client：将语句结果返回（或不返回）给客户端。如果是FALSE，则将其返回给直接调用者。

BIND_ARRAY

语法格式

DBMS_SQL.BIND_ARRAY ( 
   c                   IN INTEGER, 
   name                IN VARCHAR2, 
   <table_variable>    IN <datatype>);

参数说明

c : 代表执行dbms_sql.open_cursor返回的变量。
name：需要绑定的变量名。
<table_variable>：在dbms_sql中定义的table类型的数据结构。

示例

将table类型数据绑定到变量中赋值(number_table、varchar2_table、date_table、blob_table、clob_table、binary_double_table、binary_float_table)。

1、创建测试表。

DROP TABLE t_dbms_sql CASCADE;
CREATE TABLE t_dbms_sql(
c_number number,
c_varchar2 varchar2(1000),
c_date date,
c_clob clob,
c_binary_float binary_float,
c_binary_double binary_double,
c_blob  blob);

2、调用函数。

SET SERVEROUTPUT ON;
CREATE OR REPLACE FUNCTION func_dbms_sql() RETURNS VOID  AS
$$
DECLARE
--创建table类型参数
array_number dbms_sql.number_table;
array_varchar2 dbms_sql.varchar2_table;
array_date dbms_sql.date_table;
array_blob dbms_sql.varchar2_table;
array_clob dbms_sql.clob_table;
array_binary_double dbms_sql.binary_double_table;
array_binary_float dbms_sql.binary_float_table;
c integer;
test_sql varchar2(200);
e number;
BEGIN
--为number类型嵌套表赋值数不一致
array_number(1) := 1;
array_number(2) := 2.2;
array_number(3) := 33.3;
array_number(4) := 4;
array_number(5) := 5.5;
array_number(6) := 66.6;

--为varchar2类型嵌套表赋值
array_varchar2(1) := 'test1';
array_varchar2(2) := 'test2';
array_varchar2(3) := 'test3';
array_varchar2(4) := 'test4';
array_varchar2(5) := 'test5';
array_varchar2(6) := 'test6';

--为date类型嵌套表赋值
array_date(1) := '2011-01-01 11:01:01';
array_date(2) := '2012-02-02 12:02:02';
array_date(3) := '2013-03-03 13:03:03';
array_date(4) := '2014-04-04 14:04:04'; 
array_date(5) := '2015-05-05 15:05:05';
array_date(6) := '2016-06-06 16:06:06';
--为blob类型嵌套表赋值
array_blob(1) := '10101010';

array_blob(2) := '101010101';
array_blob(3) := '1010101010';
array_blob(4) := '10101010101';
array_blob(5) := '101010101010';
array_blob(6) := '1010101010101';

--为clob类型嵌套表赋值
array_clob(1) := 'clob1';
array_clob(2) := 'clob2';
array_clob(3) := 'clob3';
array_clob(4) := 'clob4';
array_clob(5) := 'clob5';
array_clob(6) := 'clob6';

--为binary_float类型嵌套表赋值
array_binary_float(1) := '1.111';
array_binary_float(2) := '22.2';
array_binary_float(3) := '3.333333';
array_binary_float(4) := '44.4';
array_binary_float(5) := '5.555555';
array_binary_float(6) := '66666.66666';
--为binary_double类型嵌套表赋值
array_binary_double(1) := '1.111';
array_binary_double(2) := '22.2';
array_binary_double(3) := '3.333333';
array_binary_double(4) := '44.4';
array_binary_double(5) := '5.555555';
array_binary_double(6) := '66666.66666';

c := dbms_sql.open_cursor();
test_sql := 'INSERT INTO t_dbms_sql VALUES(:c_number,:c_varchar2,:c_date,:c_clob,:c_binary_float,:c_binary_double,:c_blob)';
dbms_sql.parse(c,test_sql,dbms_sql.native);

--绑定变量
dbms_sql.bind_array(c,':c_number',array_number);
dbms_sql.bind_array(c,':c_varchar2',array_varchar2);
dbms_sql.bind_array(c,':c_date',array_date);
dbms_sql.bind_array(c,':c_blob',array_blob);
dbms_sql.bind_array(c,':c_clob',array_clob);
dbms_sql.bind_array(c,':c_binary_float',array_binary_float);
dbms_sql.bind_array(c,':c_binary_double',array_binary_double);
e := dbms_sql.execute(c);
DBMS_SQL.CLOSE_CURSOR(c);
END;
$$
LANGUAGE plpgsql;
select func_dbms_sql();

3、查询表中的数据。

select * from t_dbms_sql;

返回结果为：

 c_number | c_varchar2 |       c_date        | c_clob | c_binary_float | c_binary_double |     c_blob
----------+------------+---------------------+--------+----------------+-----------------+----------------
        1 | test1      | 2011-01-01 11:01:01 | clob1  |          1.111 |           1.111 | 10101010
      2.2 | test2      | 2012-02-02 12:02:02 | clob2  |           22.2 |            22.2 | 0101010101
     33.3 | test3      | 2013-03-03 13:03:03 | clob3  |        3.33333 |        3.333333 | 1010101010
        4 | test4      | 2014-04-04 14:04:04 | clob4  |           44.4 |            44.4 | 010101010101
      5.5 | test5      | 2015-05-05 15:05:05 | clob5  |        5.55555 |        5.555555 | 101010101010
     66.6 | test6      | 2016-06-06 16:06:06 | clob6  |        66666.7 |     66666.66666 | 01010101010101
(6 rows)

BIND_VARIABLE

语法格式

DBMS_SQL.BIND_VARIABLE (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN <datatype>);

参数说明

c : 代表执行dbms_sql.open_cursor返回的变量。
name：需要绑定的变量名。
<value>：常见的varchar2、number、date等数据类型值。

示例

bind_variable函数-将常量类型赋值给数值类型字段

1、创建测试表。

DROP TABLE t_dbms_sql CASCADE;
CREATE TABLE t_dbms_sql(
c_int2 int2,
c_int4 int4,
c_numeric numeric,
c_number number,
c_float4 float4,
c_float8 float8,
c_real real);

2、将常量类型赋值给数值类型字段。

SET SERVEROUTPUT ON;
--value参数赋值为常量类型
DECLARE
c number;
c_number number;
test_sql varchar2(100);
e number;
BEGIN
c := dbms_sql.open_cursor();
test_sql := 'INSERT INTO t_dbms_sql VALUES(:c_int2,:c_int4,:c_numeric,:c_number,:c_float4,:c_float8,:c_real)';
dbms_sql.parse(c,test_sql,dbms_sql.native);

--绑定变量
dbms_sql.bind_variable(c,':c_int2',dbms_sql.native);
dbms_sql.bind_variable(c,':c_int4',dbms_sql.v6);
dbms_sql.bind_variable(c,':c_numeric',dbms_sql.v7);
dbms_sql.bind_variable(c,':c_number',dbms_sql.native);
dbms_sql.bind_variable(c,':c_float4',dbms_sql.v6);
dbms_sql.bind_variable(c,':c_float8',dbms_sql.v7);
dbms_sql.bind_variable(c,':c_real',dbms_sql.native);
e := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
END;
/

3、查询表中数据。

select * from t_dbms_sql;

返回结果为：

 c_int2 | c_int4 | c_numeric | c_number | c_float4 | c_float8 | c_real
--------+--------+-----------+----------+----------+----------+--------
      1 |      0 |         2 |        1 |        0 |        2 |      1
(1 row)

DESCRIBE_COLUMNS，DESCRIBE_COLUMNS2，DESCRIBE_COLUMNS3

语法格式

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

DBMS_SQL.DESCRIBE_COLUMNS2 ( 
   c              IN  INTEGER, 
   col_cnt        OUT INTEGER, 
   desc_t         OUT DESC_TAB2);

DBMS_SQL.DESCRIBE_COLUMNS3 ( 
   c              IN  INTEGER, 
   col_cnt        OUT INTEGER, 
   desc_t         OUT DESC_TAB3);
   
DBMS_SQL.DESCRIBE_COLUMNS3 ( 
   c              IN  INTEGER, 
   col_cnt        OUT INTEGER, 
   desc_t         OUT DESC_TAB4);

参数说明

c : 代表执行dbms_sql.open_cursor返回的变量。
name：查询语句返回的列数量。
desc_t：输出表的元数据信息，参考desc_tab的table变量。

DEFINE_ARRAY

语法格式

DEFINE_ARRAY (
   c           IN INTEGER, 
   position    IN INTEGER,
   <table_variable>    IN <datatype> 
   cnt         IN INTEGER, 
   lower_bnd   IN INTEGER);

参数说明

c：已经打开的cursor名称。
position：指需要返回的字段在查询结果中处于第几列。
<table_variable>：接收返回结果需要的变量,与define_column不同的是此变量是table,而不是普通的字段类型。
cnt：表示一次可以返回的行数。
lower_bnd：指n_tab的index从哪个数值开始,此数值是递增的.在此例中index是从1开始的，一次得到9行结果集，则有n_tab(1)到n_tab(9),如果循环再得到新的结果集,则index继续增长。

IS_OPEN

语法格式

DBMS_SQL.IS_OPEN (c  IN INTEGER)  RETURN BOOLEAN;

参数说明

c : 代表执行dbms_sql.open_cursor返回的变量。

示例

判断游标是否已打开，已打开判断为true，未打开判断为false

1、定义存储过程。

SET SERVEROUTPUT ON;
CREATE OR  REPLACE PROCEDURE proc_dbms_sql(i integer) as
DECLARE
c integer;
BEGIN
IF i<3 THEN
c := dbms_sql.open_cursor();
raise notice '游标状态 is 打开 %',dbms_sql.is_open(c) ;
ELSE
raise notice '游标状态 is 关闭 %',dbms_sql.is_open(c) ;
END IF;
END;
/

2、调用is_open函数。

DECLARE
c integer;
BEGIN
--打开游标，判断为true
PERFORM proc_dbms_sql(2);
--未打开游标，判断为flase
PERFORM proc_dbms_sql(3);
END;
/

返回结果为：

NOTICE:  游标状态 is 打开 t
CONTEXT:  referenced column: proc_dbms_sql
SQL statement "SELECT proc_dbms_sql(2)"
PL/pgSQL function inline_code_block line 5 at PERFORM
NOTICE:  游标状态 is 关闭 f
CONTEXT:  referenced column: proc_dbms_sql
SQL statement "SELECT proc_dbms_sql(3)"
PL/pgSQL function inline_code_block line 7 at PERFORM
ANONYMOUS BLOCK EXECUTE

VastbaseG100

DBMS_SQL

数据类型

desc_rec和desc_tab类型

desc_rec2和desc_tab2类型

desc_rec3和desc_ta3类型

desc_rec4和desc_ta4类型

number_table、varcha2_table、date_table、blob_table、clob_table、binary_double_table、binary_float_table类型

常量类型

子程序

OPEN_CURSOR

CLOSE_CURSOR

COLUMN_VALUE

COLUMN_VALUE_CHAR

COLUMN_VALUE_RAW

DEFINE_COLUMN

DEFINE_COLUMN_CHAR

DEFINE_COLUMN_RAW

EXECUTE

FETCH_ROWS

PARSE

RETURN_RESULT

BIND_ARRAY

BIND_VARIABLE

DESCRIBE_COLUMNS，DESCRIBE_COLUMNS2，DESCRIBE_COLUMNS3

DEFINE_ARRAY

IS_OPEN

弹框头部