二十四、DBMS_SQL
1、概述
1)
在整个程序的设计过程中,对游标的操作切不可有省略的部分,一旦省略其中某一步骤,则会程序编译过程既告失败,如在程序结尾处未对改游标进行关闭操作,则在再次调用过程时会出现错误.
2)
dbms_sql除了可以做一般的select,insert,update,delete等静态的sql做能在过程中所做工作外,还能执行create等DDL操作,不过在执行该类操作时应首先显式赋予执行用户相应的系统权限,比如create table等.该类操作只需open cursor--->prase--->close cursor即能完成.
3)一般过程
对于一般的select操作,如果使用动态的sql语句则需要进行以下几个步骤:
open cursor--->parse--->define column--->excute--->fetch rows--->close cursor;
而对于dml操作(insert,update)则需要进行以下几个步骤:
open cursor--->parse--->bind variable--->execute--->close cursor;
对于delete操作只需要进行以下几个步骤:
open cursor--->parse--->execute--->close cursor;
2、包的组成
1)、OPEN_CURSOR
作用:打开一个动态游标,并返回一个整型,返回新游标的整型ID值。
2)、PARSE
作用:对动态游标所提供的sql语句进行解析编译。
语法:DBMS_SQL.parse(c in integer,statement in varchar2,language_flag in integer)
其中,参数C表示游标(可以设为C=DBMS_SQL.open_cursor),statement为sql语句,language_flag为解析sql语句所用oracle版本,一般有V6,V7跟native(在不明白所连database版本时,使用
BMS_SQL.native)。
3)、BIND_VARIABLE
作用:将给定的数量与特定的变量相连接,定义动态sql语句(DML)中所对应字段的值,c为游标,name为字段名称,value为字段的值;
语法:bind_variable(c in integer, name in varchar2, value):
4)、DEFINE_COLOUMN
作用:定义动态游标所能得到的对应值,定义字段变量,其值对应于指定游标中某个位置元素的值 (仅用于SELECT语句)
语法:DBMS_SQL.define_column(c in integer,position in integer,column any datatype,[column_size in integer])
其中,c为动态游标,positon为对应动态sql中的位置(从1开始),column为该值所对应的变量,可以为任何类型,column_size只有在column为定义长度的类型中使用如VARCHAR2,CHAR等(该过程有很多种情况,此处只对一般使用到的类型进行表述);
5)、EXECUTE
作用:执行游标,并返回处理一个整型,代表处理结果(对insert,delete,update才有意义,而对select语句而言可以忽略);
语法:function execute(c in integer):
6)、EXECUTE_AND_FETCH
作用:执行指定的游标并取记录
7)、FETCH_ROWS
作用:从指定的游标中取出记录,循环取数据,并返回一个整数,为0时表示已经取到游标末端;
语法:function fetch_rows(c in integer):
8)、COLUMN_VALUE
作用:返回游标中指定位置的元素,将所取得的游标数据赋值到相应的变量,c为游标,position为位置,value则为对应的变量;
语法:column_value(c in integer, position in integer, value):
9)、IS_OPEN
作用:当指定的游标状态为OPEN时返回真值
10)、CLOSE_CURSOR
作用:关闭一个动态游标,参数为open_cursor所打开的游标,并释放内存。
语法:DBMS_SQL.close_cursor(c in out integer)
11)、LAST_ERROR_POSITION
作用:返回出错SQL语句的字节偏移量
12)、LAST_ROW_ID
作用:返回最后一条记录的ROWID
13)、LAST_SQL_FUNCTION_CODE
作用:返回语句的SQL FUNCTION CODE