DBMS_SQL

• 建议使用dbms_sql.define_column及dbms_sql.column_value定义参数列。
• 当结果集大于work_mem设定值时会触发结果集临时下盘，但最大阈值不超过512MB。

• DBMS_SQL.OPEN_CURSOR

  该函数用来打开一个游标，是后续dbms_sql各项操作的前提。该函数不传入任何参数，内部自动递增生成游标ID，并作为返回值返回给integer定义的变量。

  DBMS_SQL.OPEN_CURSOR函数原型为：

  1 2 3

  DBMS_SQL.OPEN_CURSOR ( ) RETURN INTEGER;

• DBMS_SQL.CLOSE_CURSOR

  该函数用来关闭一个游标，是dbms_sql各项操作的结束。如果在存储过程结束时没有调用该函数，则该游标占用的内存仍然会保存，因此关闭游标非常重要。由于异常情况的发生会中途退出存储过程，导致游标未能关闭，因此建议存储过程中有异常处理，将该接口包含在内。

  DBMS_SQL.CLOSE_CURSOR函数原型为：

  1 2 3 4

  DBMS_SQL.CLOSE_CURSOR ( cursorid IN INTEGER ) RETURN INTEGER;

  表2 DBMS_SQL.CLOSE_CURSOR接口说明

  参数名称	描述
  cursorid	打算关闭的游标ID号

• DBMS_SQL.PARSE

  该函数用来解析给定游标的查询语句，被传入的查询语句会立即执行。目前仅支持SELECT查询语句的解析，且语句参数仅可通过text类型传递，长度不大于1G。

  DBMS_SQL.PARSE函数的原型为：

  1 2 3 4 5 6

  DBMS_SQL.PARSE ( cursorid IN INTEGER, query_string IN TEXT, label IN INTEGER ) RETURN BOOLEAN;

  表3 DBMS_SQL.PARSE接口说明

  参数名称	描述
  cursorid	执行查询语句解析的游标ID
  query_string	执行的查询语句
  language_flag	版本语言号，目前只支持1

• DBMS_SQL.EXECUTE

  该函数用来执行一个给定的游标。该函数接收一个游标ID，运行后获得的数据用于后续操作。目前仅支持SELECT查询语句的执行。

  DBMS_SQL.EXECUTE函数的原型为：

  1 2 3 4

  DBMS_SQL.EXECUTE( cursorid IN INTEGER, ) RETURN INTEGER;

  表4 DBMS_SQL.EXECUTE接口说明

  参数名称	描述
  cursorid	执行查询语句解析的游标ID

• DBMS_SQL.FETCHE_ROWS

  该函数返回符合查询条件的数据行数，每一次运行该接口都会获取到新的行数的集合，直到数据读取完毕获取不到新行为止。

  DBMS_SQL.FETCHE_ROWS函数的原型为：

  1 2 3 4

  DBMS_SQL.FETCHE_ROWS( cursorid IN INTEGER, ) RETURN INTEGER;

  表5 DBMS_SQL.FETCH_ROWS接口说明

  参数名称	描述
  curosorid	执行的游标ID

• DBMS_SQL.DEFINE_COLUMN

  该函数用来定义从给定游标返回的列，该接口只能应用于SELECT定义的游标中。定义的列通过查询列表的相对位置来标识，传入变量的数据类型决定了该列被定义的类型。

  DBMS_SQL.DEFINE_COLUMN函数的原型为：

  1 2 3 4 5 6 7

  DBMS_SQL.DEFINE_COLUMN( cursorid IN INTEGER, position IN INTEGER, column_ref IN ANYELEMENT, column_size IN INTEGER default 1024 ) RETURN INTEGER;

  表6 DBMS_SQL.DEFINE_COLUMN接口说明

  参数名称	描述
  cursorid	执行的游标ID
  position	动态定义列在查询中的位置
  column_ref	任意类型的变量，可根据变量类型选择适当的接口动态定义列
  column_size	定义的列的长度

• DBMS_SQL.DEFINE_COLUMN_CHAR

  该函数用来定义从给定游标返回的CHAR类型的列，该接口只能应用于SELECT定义的游标中。定义的列通过查询列表的相对位置来标识，传入变量的数据类型决定了该列被定义的类型。

  DBMS_SQL.DEFINE_COLUMN_CHAR函数的原型为：

  1 2 3 4 5 6 7

  DBMS_SQL.DEFINE_COLUMN_CHAR( cursorid IN INTEGER, position IN INTEGER, column IN TEXT, column_size IN INTEGER ) RETURN INTEGER;

  表7 DBMS_SQL.DEFINE_COLUMN_CHAR接口说明

  参数名称	描述
  cursorid	执行的游标ID
  position	动态定义列在查询中的位置
  column	需要定义的某类型的参数变量
  column_size	动态定义列长度

• DBMS_SQL.DEFINE_COLUMN_INT

  该函数用来定义从给定游标返回的INT类型的列，该接口只能应用于SELECT定义的游标中。定义的列通过查询列表的相对位置来标识，传入变量的数据类型决定了该列被定义的类型。

  DBMS_SQL.DEFINE_COLUMN_INT函数的原型为：

  1 2 3 4 5

  DBMS_SQL.DEFINE_COLUMN_INT( cursorid IN INTEGER, position IN INTEGER ) RETURN INTEGER;

  表8 DBMS_SQL.DEFINE_COLUMN_INT接口说明

  参数名称	描述
  cursorid	执行的游标ID
  position	动态定义列在查询中的位置

• DBMS_SQL.DEFINE_COLUMN_LONG

  该函数用来定义从给定游标返回的长列类型（非数据类型long）的列，该接口只能应用于SELECT定义的游标中。定义的列通过查询列表的相对位置来标识，传入变量的数据类型决定了该列被定义的类型。长列的大小限制为1G。

  DBMS_SQL.DEFINE_COLUMN_LONG函数的原型为：

  1 2 3 4 5

  DBMS_SQL.DEFINE_COLUMN_LONG( cursorid IN INTEGER, position IN INTEGER ) RETURN INTEGER;

  表9 DBMS_SQL.DEFINE_COLUMN_LONG接口说明

  参数名称	描述
  cursorid	执行的游标ID
  position	动态定义列在查询中的位置

• DBMS_SQL.DEFINE_COLUMN_RAW

  该函数用来定义从给定游标返回的RAW类型的列，该接口只能应用于SELECT定义的游标中。定义的列通过查询列表的相对位置来标识，传入变量的数据类型决定了该列被定义的类型。

  DBMS_SQL.DEFINE_COLUMN_RAW函数的原型为：

  1 2 3 4 5 6 7

  DBMS_SQL.DEFINE_COLUMN_RAW( cursorid IN INTEGER, position IN INTEGER, column IN BYTEA, column_size IN INTEGER ) RETURN INTEGER;

  表10 DBMS_SQL.DEFINE_COLUMN_RAW接口说明

  参数名称	描述
  cursorid	执行的游标ID
  position	动态定义列在查询中的位置
  column	RAW类型的参数变量
  column_size	列的长度

• DBMS_SQL.DEFINE_COLUMN_TEXT

  该函数用来定义从给定游标返回的TEXT类型的列，该接口只能应用于SELECT定义的游标中。定义的列通过查询列表的相对位置来标识，传入变量的数据类型决定了该列被定义的类型。

  DBMS_SQL.DEFINE_COLUMN_TEXT函数的原型为：

  1 2 3 4 5 6

  DBMS_SQL.DEFINE_COLUMN_CHAR( cursorid IN INTEGER, position IN INTEGER, max_size IN INTEGER ) RETURN INTEGER;

  表11 DBMS_SQL.DEFINE_COLUMN_TEXT接口说明

  参数名称	描述
  cursorid	执行的游标ID
  position	动态定义列在查询中的位置
  max_size	定义的TEXT类型的最大长度

• DBMS_SQL.DEFINE_COLUMN_UNKNOWN

  该函数用来处理从给定游标返回的未知数据类型的列，该接口仅用于类型不识别时的报错退出。

  DBMS_SQL.DEFINE_COLUMN_UNKNOWN函数的原型为：

  1 2 3 4 5 6

  DBMS_SQL.DEFINE_COLUMN_CHAR( cursorid IN INTEGER, position IN INTEGER, column IN TEXT ) RETURN INTEGER;

  表12 DBMS_SQL.DEFINE_COLUMN_UNKNOWN接口说明

  参数名称	描述
  cursorid	执行的游标ID
  position	动态定义列在查询中的位置
  column	动态定义的参数

• DBMS_SQL.COLUMN_VALUE

  该函数用来返回给定游标给定位置的游标元素值，该接口访问的是DBMS_SQL.FETCH_ROWS获取的数据。

  DBMS_SQL.COLUMN_VALUE函数的原型为：

  1 2 3 4 5 6

  DBMS_SQL.COLUMN_VALUE( cursorid IN INTEGER, position IN INTEGER, column_value INOUT ANYELEMENT ) RETURN ANYELEMENT;

  表13 DBMS_SQL.COLUMN_VALUE接口说明

  参数名称	描述
  cursorid	执行的游标ID
  position	动态定义列在查询中的位置
  column_value	定义的列的返回值

• DBMS_SQL.COLUMN_VALUE_CHAR

  该函数用来返回给定游标给定位置的游标CHAR类型的值，该接口访问的是DBMS_SQL.FETCH_ROWS获取的数据。

  DBMS_SQL.COLUMN_VALUE_CHAR函数的原型为：

  1 2 3 4 5 6 7 8

  DBMS_SQL.COLUMN_VALUE_CHAR( cursorid IN INTEGER, position IN INTEGER, column_value INOUT CHARACTER， err_num INOUT NUMERIC default 0, actual_length INOUT INTEGER default 1024 ) RETURN RECORD;

  表14 DBMS_SQL.COLUMN_VALUE_CHAR接口说明

  参数名称	描述
  cursorid	执行的游标ID
  position	动态定义列在查询中的位置
  column_value	返回值
  err_num	错误号。传出参数，须传入变量做参数。目前未实现，固定传出-1。
  actual_length	返回值的实际长度

• DBMS_SQL.COLUMN_VALUE_INT
  该函数用来返回给定游标给定位置的游标INT类型的值，该接口访问的是DBMS_SQL.FETCH_ROWS获取的数据。DBMS_SQL.COLUMN_VALUE_INT函数的原型为：

  1 2 3 4 5

  DBMS_SQL.COLUMN_VALUE_INT( cursorid IN INTEGER, position IN INTEGER ) RETURN INTEGER;

  表15 DBMS_SQL.COLUMN_VALUE_INT接口说明

  参数名称	描述
  cursorid	执行的游标ID
  position	动态定义列在查询中的位置

• DBMS_SQL.COLUMN_VALUE_LONG

  该函数用来返回给定游标给定位置的游标长列（非long/bigint整型）类型的值，该接口访问的是DBMS_SQL.FETCH_ROWS获取的数据。

  DBMS_SQL.COLUMN_VALUE_LONG函数的原型为：

  1 2 3 4 5 6 7 8 9

  DBMS_SQL.COLUMN_VALUE_LONG( cursorid IN INTEGER, position IN INTEGER, length IN INTEGER, off_set IN INTEGER, column_value INOUT TEXT， actual_length INOUT INTEGER default 1024 ) RETURN RECORD;

  表16 DBMS_SQL.COLUMN_VALUE_LONG接口说明

  参数名称	描述
  cursorid	执行的游标ID
  position	动态定义列在查询中的位置
  length	返回值的长度
  off_set	返回值的起始位置
  column_value	返回值
  actual_length	实际返回值的长度

• DBMS_SQL.COLUMN_VALUE_RAW

  该函数用来返回给定游标给定位置的游标RAW类型的值，该接口访问的是DBMS_SQL.FETCH_ROWS获取的数据。

  DBMS_SQL.COLUMN_VALUE_RAW函数的原型为：

  1 2 3 4 5 6 7 8

  DBMS_SQL.COLUMN_VALUE_RAW( cursorid IN INTEGER, position IN INTEGER, column_value INOUT BYTEA， err_num INOUT NUMERIC default 0, actual_length INOUT INTEGER default 1024 ) RETURN RECORD;

  表17 DBMS_SQL.COLUMN_VALUE_RAW接口说明

  参数名称	描述
  cursorid	执行的游标ID
  position	动态定义列在查询中的位置
  column_value	返回的列值
  err_num	错误号。传出参数，须传入变量做参数。目前未实现，固定传出-1。
  actual_length	返回值的实际长度，不能长于此值，否则截断

• DBMS_SQL.COLUMN_VALUE_TEXT

  该函数用来返回给定游标给定位置的游标TEXT类型的值，该接口访问的是DBMS_SQL.FETCH_ROWS获取的数据。

  DBMS_SQL.COLUMN_VALUE_TEXT函数的原型为：

  1 2 3 4 5

  DBMS_SQL.COLUMN_VALUE_TEXT( cursorid IN INTEGER, position IN INTEGER ) RETURN TEXT;

  表18 DBMS_SQL.COLUMN_VALUE_TEXT接口说明

  参数名称	描述
  cursorid	执行的游标ID
  position	动态定义列在查询中的位置

• DBMS_SQL.COLUMN_VALUE_UNKNOWN

  该函数用来返回给定游标给定位置的游标未知类型的值，该接口为类型不支持时的报错处理接口。

  DBMS_SQL.COLUMN_VALUE_UNKNOWN函数的原型为：

  1 2 3 4 5 6

  DBMS_SQL.COLUMN_VALUE_UNKNOWN( cursorid IN INTEGER, position IN INTEGER, COLUMN_TYPE IN TEXT ) RETURN TEXT;

  表19 DBMS_SQL.COLUMN_VALUE_UNKNOWN接口说明

  参数名称	描述
  cursorid	执行的游标ID
  position	动态定义列在查询中的位置
  column_type	返回的参数类型

• DBMS_SQL.IS_OPEN

该函数用来返回游标的当前状态：打开、解析、执行、定义。取值是为TRUE，关闭后为FALSE，未知时报错，其余默认为关闭。

1
2
3
4

DBMS_SQL.IS_OPEN(
cursorid                 IN    INTEGER
)
RETURN BOOLEAN;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

--在存储过程中操作raw数据
create or replace procedure pro_dbms_sql_all_02(in_raw raw,v_in int,v_offset int)
as 
cursorid int;
v_id int;
v_info bytea :=1;
query varchar(2000);
execute_ret int;
define_column_ret_raw bytea :='1';
define_column_ret int;
begin
drop table if exists pro_dbms_sql_all_tb1_02 ;
create table pro_dbms_sql_all_tb1_02(a int ,b blob);
insert into pro_dbms_sql_all_tb1_02 values(1,HEXTORAW('DEADBEEE'));
insert into pro_dbms_sql_all_tb1_02 values(2,in_raw);
query := 'select * from pro_dbms_sql_all_tb1_02 order by 1';
--打开游标
cursorid := dbms_sql.open_cursor();
--编译游标
dbms_sql.parse(cursorid, query, 1);
--定义列
define_column_ret:= dbms_sql.define_column(cursorid,1,v_id);
define_column_ret_raw:= dbms_sql.define_column_raw(cursorid,2,v_info,10);
--执行
execute_ret := dbms_sql.execute(cursorid);
loop 
exit when (dbms_sql.fetch_rows(cursorid) <= 0);
--获取值
dbms_sql.column_value(cursorid,1,v_id);
dbms_sql.column_value_raw(cursorid,2,v_info,v_in,v_offset);
--输出结果
dbms_output.put_line('id:'|| v_id || ' info:' || v_info);
end loop;
--关闭游标
dbms_sql.close_cursor(cursorid);
end;
/
--调用存储过程
call pro_dbms_sql_all_02(HEXTORAW('DEADBEEF'),0,1);

--删除存储过程
DROP PROCEDURE pro_dbms_sql_all_02;