DBMS_SQL 包

支持在pl/sql中使用动态sql语句。

19.1 相关方法

DBMS_SQL包中所用到的结构定义如下:

TYPE binary_float_table IS TABLE OF FLOAT INDEX BY INTEGER;

TYPE blob_table IS TABLE OF BLOB INDEX BY INTEGER;

TYPE clob_table IS TABLE OF CLOB INDEX BY INTEGER;

type date_table IS TABLE OF DATETIME INDEX BY INTEGER;

TYPE desc_tab IS TABLE OF desc_rec INDEX BY INTEGER;

TYPE desc_tab2 IS TABLE OF desc_rec2 INDEX BY INTEGER;

TYPE desc_tab3 IS TABLE OF desc_rec3 INDEX BY INTEGER;

TYPE interval_day_to_second_Table IS TABLE OF INTERVAL DAY TO SECOND INDEX BY INTEGER;

TYPE interval_year_to_month_table IS TABLE OF INTERVAL YEAR TO MONTH INDEX BY INTEGER;

TYPE Int_Table IS TABLE OF INT INDEX BY INTEGER;

TYPE number_table IS TABLE OF NUMBER INDEX BY INTEGER;

TYPE time_table IS TABLE OF TIME INDEX BY INTEGER;

TYPE time_with_time_zone_table IS TABLE OF TIME WITH TIME ZONE INDEX BY INTEGER;

TYPE timestamp_table IS TABLE OF DATETIME INDEX BY INTEGER;

TYPE timestamp_with_ltz_table IS TABLE OF TIMESTAMP WITH LOCAL TIME ZONE INDEX BY INTEGER;

TYPE timestamp_with_time_zone_Table IS TABLE OF TIMESTAMP WITH TIME ZONE INDEX BY INTEGER;

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

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

DESC_REC的结构如下:

TYPE DESC_REC IS RECORD(

COL_TYPE INTEGER := 0,

COL_MAX_LEN INTEGER := 0,

COL_NAME VARCHAR2(128) := '',

COL_NAME_LEN INTEGER := 0,

COL_SCHEMA_NAME VARCHAR2(128) := '',

COL_SCHEMA_NAME_LEN INTEGER := 0,

COL_PRECISION INTEGER := 0,

COL_SCALE INTEGER := 0,

COL_CHARSETID INTEGER := 0,

COL_CHARSETFORM INTEGER := 0,

COL_NULL_OK BOOLEAN := TRUE);

使用DESCRIBE_COLUMNS函数可以将查询项的数据类型填充到DESC_REC中,其中服务器部分数据类型和DBMS_SQL中的数据类型的映射关系如下:

TYPECODE_VARCHAR 		INTEGER := 1;

TYPECODE_NUMBER 		INTEGER := 2;

TYPECODE_INT 			INTEGER := 2;

TYPECODE_SMALLINT 		INTEGER := 2;

TYPECODE_TINYINT 		INTEGER := 2;

TYPECODE_BIGINT 		INTEGER := 2;

TYPECODE_FLOAT 			INTEGER := 2;

TYPECODE_VARCHAR2 		INTEGER := 9;

TYPECODE_DATE 			INTEGER := 12;

TYPECODE_VARBINARY 		INTEGER := 23;

TYPECODE_RAW 			INTEGER := 95;

TYPECODE_CHAR 			INTEGER := 96;

TYPECODE_BLOB 			INTEGER := 113;

TYPECODE_BFILE 			INTEGER := 114;

TYPECODE_CLOB 			INTEGER := 112;

TYPECODE_TIME 			INTEGER := 170;

TYPECODE_TIME_TZ 		INTEGER := 171;

TYPECODE_TIMESTAMP 		INTEGER := 180;

TYPECODE_TIMESTAMP_TZ 	INTEGER := 181;

TYPECODE_TIMESTAMP_LTZ 	INTEGER := 232;

TYPECODE_INTERVAL_YM 	INTEGER := 182;
TYPECODE_INTERVAL_DS 	INTEGER := 183;

TYPECODE_REF 			INTEGER := 110;

TYPECODE_VARRAY 		INTEGER := 247;

DBMS_SQL包中包含的过程和函数如下详细介绍:

  1. BIND_ARRAY

按照SQL语句中变量名将变量值绑定到游标的索引表上。

语法如下:

PROCEDURE BIND_ARRAY (

	cURID IN INTEGER,

	name IN VARCHAR2,

	<table_variable> IN <datatype>

	[,index1 IN INTEGER,

	index2 IN INTEGER)]

);

参数详解

  • <datatype>

是DBMS_SQL包中索引表类型。如下所示:

  • binary_float_table
  • blob_table
  • clob_table
  • date_table
  • interval_day_to_second_Table
  • interval_year_to_month_table
  • Int_Table
  • number_table
  • time_table
  • time_with_time_zone_table
  • timestamp_table
  • timestamp_with_ltz_table
  • timestamp_with_time_zone_Table
  • varchar2_table
  • varchar2a
  1. BIND_VARIABLE

按照SQL语句中变量名将变量值绑定到游标的基本数据类型变量上。

语法如下:

PROCEDURE BIND_VARIABLE (

	c      IN INTEGER,

	name   IN VARCHAR2,

	value  IN  <datatype>

);

参数详解

  • C 游标。
  • NAME变量名称。
  • VALUE变量值。可以为任何类型,类型<datatype>是DBMS_SQL包中基本数据类型,如下所示:

◆FLOAT

◆CLOB

◆DATETIME

◆INTERVAL DAY TO SECOND

◆NUMBER

◆TIME

◆TIME WITH TIME ZONE

◆TIMESTAMP WITH TIME ZONE

◆VARCHAR

◆INTERVAL YEAR TO MONTH

◆INT

◆SMALLINT

◆TINYINT

◆BIGINT

◆VARBINARY

◆BLOB
  1. BIND_VARIABLE_RAW

绑定二进制数据类型变量,可通过OUT_VALUE_SIZE指定BINARY长度。

语法如下:

BIND_VARIABLE_RAW (

	c 		IN INTEGER,

	name 	IN VARCHAR2,

	value 	IN BINARY[,out_value_size IN INTEGER]

);
  1. BIND_VARIABLE_CHAR

绑定CHAR数据类型变量,可通过OUT_VALUE_SIZE指定CHAR长度。

语法如下:

BIND_VARIABLE_CHAR (

	c 		IN INTEGER,

	name 	IN VARCHAR2,

	value 	IN CHAR[,out_value_size IN INTEGER]

);
  1. BIND_VARIABLE_ROWID

绑定ROWID到游标的BIGINT数据类型变量上。

语法如下:

BIND_VARIABLE_ROWID (

	c 		IN INTEGER,

	name 	IN VARCHAR2,

	value 	IN BIGINT

);
  1. CLOSE_CURSOR

关闭所给定的游标。

语法如下:

DBMS_SQL.CLOSE_CURSOR (

	c IN OUT INTEGER

);
  1. COLUMN_VALUE

根据列的位置,返回游标中的列值,通常在FETCH_ROWS后被调用。<datatype>可以是DBMS_SQL中的基本数据类型和索引表类型。

语法如下:

DBMS_SQL.COLUMN_VALUE (

	c 				IN INTEGER,

	position 		IN INTEGER,

	value 			OUT <datatype>

	[,column_error 	OUT NUMBER]

	[,actual_length OUT INTEGER]

);

参数详解

  • C 游标。
  • POSITION 为对应动态sql中列的位置。
  • VALUE列值。
  1. COLUMN_VALUE_CHAR

根据列的位置,返回数据类型为CHAR的列值。

语法如下:

COLUMN_VALUE_CHAR (

	c 				IN INTEGER,

	position 		IN INTEGER,

	value 			OUT CHAR,

	[,column_error 	OUT NUMBER]

	[,actual_length OUT INTEGER]

);
  1. COLUMN_VALUE_RAW

根据列的位置,返回数据类型为BINARY的列值。

语法如下:

COLUMN_VALUE_RAW (

	c 				IN INTEGER,

	position 		IN INTEGER,

	value 			OUT BINARY,

	[,column_error 	OUT NUMBER]

	[,actual_length OUT INTEGER]);
  1. COLUMN_VALUE_ROWID

根据列的位置,返回数据类型为BIGINT的列值。

语法如下:

COLUMN_VALUE_ROWID (

	c 				IN INTEGER,

	position 		IN INTEGER,

	value 			OUT ROWID,

	[,column_error 	OUT NUMBER]

	[,actual_length OUT INTEGER]);
  1. DEFINE_ARRAY

定义索引表类型的接收列,用于接收fetch结果集,一次可获取多行数据。

语法如下:

DBMS_SQL.DEFINE_ARRAY (

	c IN INTEGER,

	position IN INTEGER,

	<table_variable> IN <datatype>

	cnt IN INTEGER,

	lower_bnd IN INTEGER

);
  1. DEFINE_COLUMN

定义基本数据类型接收列,用于接收fetch结果集,一次获取一行数据。

语法如下:

DBMS_SQL.DEFINE_COLUMN (

	c IN INTEGER,

	position IN INTEGER,

	column IN <datatype>

);

参数详解

  • C 游标。
  • POSITION为对应动态sql中的位置(从1开始)。
  • COLUMN 该值所对应的变量,可以为任何类型。
  1. EXECUTE

执行给定游标内的SQL语句。

语法如下:

DBMS_SQL.EXECUTE (

	c IN INTEGER

) RETURN INTEGER;

参数详解

  • C 游标。

返回值

1表示成功,0表示失败。处理结果只对insert,delete,update操作才有意义,而对select语句来说可以忽略。

  1. EXECUTE_AND_FETCH function

执行给定游标内的SQL语句,同时将fetch数据。

语法如下:

DBMS_SQL.EXECUTE_AND_FETCH (

	c 		IN INTEGER,

	exact 	IN BOOLEAN DEFAULT FALSE

)RETURN INTEGER;
  1. FETCH_ROWS

fetch指定游标中的数据,同时返回fetch的行数。为0时表示已经取到游标末端。

语法如下:

DBMS_SQL.FETCH_ROWS (

	c IN INTEGER

)RETURN INTEGER;
  1. IS_OPEN

判断游标是否打开,若打开则返回true,否则返回false。

语法如下:

DBMS_SQL.IS_OPEN (

	c IN INTEGER

)RETURN BOOLEAN;
  1. OPEN_CURSOR

打开一个游标,返回游标号。

语法如下:

DBMS_SQL.OPEN_CURSOR

RETURN INTEGER;
  1. PARSE

解析sql语句。

语法如下:

DBMS_SQL.PARSE (

	c 				IN INTEGER,

	statement 		IN VARCHAR2,

	language_flag 	IN INTEGER

);

参数详解

  • C 游标。
  • statement动态游标所提供的sql语句。
  • language_flag 该参数只为兼容oracle数据库,没有实际意义。
  1. TO_CURSOR_NUMBER

将一个外部游标转化为dbms_sql包内部的游标,返回游标号。

语法如下:

DBMS_SQL.TO_CURSOR_NUMBER(

	rc IN OUT SYS_REFCURSOR

)RETURN INTEGER;
  1. TO_REFCURSOR

将dbms_sql包内的游标转换为pl/sql游标。

语法如下:

DBMS_SQL.TO_REFCURSOR(

	cursor_number IN OUT INTEGER

)RETURN SYS_REFCURSOR;
  1. DESCRIBE_COLUMNS

获取查询项的描述信息,需要一个执行过查询操作的游标作为输入参数。

语法如下:

DBMS_SQL. DESCRIBE_COLUMNS (

	c 		IN INTEGER,

	COL_CNT OUT INTEGER,

	DESC_T 	OUT DESC_TAB

);
  1. VARIABLE_VALUE

获取变量的值,通常需要先执行bind_variable。

语法如下:

DBMS_SQL.VARIABLE_VALUE (

	c 		IN INTEGER,

	name 	IN VARCHAR2,

	value 	OUT NOCOPY <datatype>

);
  1. VARIABLE_VALUE_CHAR

根据返回数据类型为CHAR的变量值。

语法如下:

VARIABLE_VALUE_CHAR (

	c 		IN INTEGER,

	name 	IN VARCHAR2,

	value 	OUT CHAR

);
  1. VARIABLE_VALUE_RAW

根据返回数据类型为RAW的变量值。

语法如下:

VARIABLE_VALUE_RAW (

	c 		IN INTEGER,

	name 	IN VARCHAR2,

	value 	OUT BINARY

);
  1. VARIABLE_VALUE_ROWID

根据返回数据类型为BIGINT的变量值。

语法如下:

VARIABLE_VALUE_ROWID (

	c 		IN INTEGER,

	name 	IN VARCHAR2,

	value 	OUT BIGINT

);
  1. DEFINE_COLUMN_CHAR

定义VARCHAR数据类型接收列,用于接收FETCH结果集,一次获取一行数据。

语法如下:

DBMS_SQL.DEFINE_COLUMN_CHAR(

	c 			IN INTEGER,

	position 	IN INTEGER,

	column 		IN VARCHAR,

	column_size IN INTEGER

);

参数详解

  • C 游标。
  • POSITION 为对应动态sql中列的位置。
  • COLUMN 列值。
  • COLUMN_SIZE 可接收的最大列值。
  1. DEFINE_COLUMN_RAW

定义RAW数据类型接收列,用于接收FETCH结果集,一次获取一行数据。

语法如下:

DBMS_SQL.DEFINE_COLUMN_RAW(

	c 			IN INTEGER,

	position 	IN INTEGER,

	column 		IN RAW,

	column_size IN INTEGER

);

参数详解

  • C 游标。
  • POSITION 为对应动态sql中列的位置。
  • COLUMN 列值。
  • COLUMN_SIZE 可接收的最大列值。
  1. DEFINE_COLUMN_LONG

定义CLOB数据类型接收列,用于接收FETCH结果集,一次获取一行数据。

语法如下:

DBMS_SQL.DEFINE_COLUMN_LONG(

	c 			IN INTEGER,

	position 	IN INTEGER

);

参数详解

  • C 游标。
  • POSITION 为对应动态sql中列的位置。

19.2 创建字句

创建DBMS_SQL系统包。

语法如下:

void

	SP_UPDATE_DBMS_SQL_PACKAGES ()

返回值

举例说明

创建DBMS_SQL系统包。

SP_UPDATE_DBMS_SQL_PACKAGES();

19.3 举例说明

使用包内的过程和函数之前,如果还未创建过例子中的系统包,请先调用系统过程创建系统包。

SP_UPDATE_DBMS_SQL_PACKAGES();

SP_CREATE_SYSTEM_PACKAGES (1,'DBMS_OUTPUT');

例下面是利用DBMS_SQL动态执行语句的例子。

例 1.

--一次获取一行数据

create table t1(n1 number);

insert into t1 values(1.1),(2.1),(3.2),(4.3);

--非collection类型

declare

	c NUMBER;

	d1 NUMBER;

BEGIN

	c := dbms_sql.OPEN_CURSOR;--打开游标

	dbms_sql.PARSE(c, 'select N1 from sysdba.t1', '1');--解析sql语句

	dbms_sql.DEFINE_COLUMN(c, 1, d1);--定义列,将来可用column_value获取列值

	dbms_sql.EXECUTE(c);--执行语句

	dbms_sql.FETCH_ROWS(c);--获取结果集

	dbms_sql.COLUMN_VALUE(c, 1, d1);--获取列值

	dbms_sql.CLOSE_CURSOR(c);--关闭游标

	dbms_output.put_line(d1);

END;

输出 1.1

例 2.

--一次获取多行数据

DECLARE
	c NUMBER;

	d1 DBMS_SQL.NUMBER_TABLE;

BEGIN

	c := dbms_sql.OPEN_CURSOR;--打开游标

	dbms_sql.PARSE(c, 'select N1 from sysdba.t1', '1');--解析sql语句

	dbms_sql.DEFINE_ARRAY(c, 1, d1,3,1);--定义列,将来可用column_value获取列值

	dbms_sql.EXECUTE(c);--执行语句

	dbms_sql.FETCH_ROWS(c);--获取结果集

	dbms_sql.COLUMN_VALUE(c, 1, d1);--获取列值

	dbms_sql.CLOSE_CURSOR(c);--关闭游标

	dbms_output.put_line(d1(2));--输出d1中第二条列值

END;

输出 2.1

例 3.

--绑定参数

declare

	c NUMBER;

	d1 NUMBER;

BEGIN

		d1 :=9.12;

	c := dbms_sql.OPEN_CURSOR;--打开游标

	dbms_sql.PARSE(c, 'insert into SYSDBA.t1 values(:c1)','1');--解析sql语句

	dbms_sql.BIND_VARIABLE(C,'c1',d1);

	dbms_sql.EXECUTE(c);--执行语句

	bms_sql.CLOSE_CURSOR(c);--关闭游标

END;

SELECT * FROM T1;

输出:

行号 N1

---------- -----------------------

1 	1.100000000000000E+000

2 	2.100000000000000E+000

3 	3.200000000000000E+000

4 	4.300000000000000E+000

5 	9.119999999999999E+000



例 4.

--批量绑定

declare

	c NUMBER;

	d1 DBMS_SQL.NUMBER_TABLE;

BEGIN

	d1(1) :=12.3;

	d1(2) :=6.12;

	d1(3) :=8.12;

	d1(4) :=342.12;

  c := dbms_sql.OPEN_CURSOR;--打开游标

  dbms_sql.PARSE(c, 'insert into SYSDBA.t1 values(:c1)', '1');--解析sql语句

  dbms_sql.BIND_ARRAY(C,'c1',d1);

  dbms_sql.EXECUTE(c);--执行语句

  dbms_sql.CLOSE_CURSOR(c);--关闭游标

END;

SELECT * FROM T1;



输出:

行号 N1

---------- -------------------------

1 	1.100000000000000E+000

2 	2.100000000000000E+000

3 	3.200000000000000E+000

4 	4.300000000000000E+000

5 	9.119999999999999E+000

6 	1.230000000000000E+001

7 	6.120000000000000E+000

8 	8.119999999999999E+000

9 	3.421200000000000E+002

9 	rows got
微信扫码
分享文档
扫一扫
联系客服