PL/SQL中使用动态SQL编程

 PL/SQL中使用动态SQL编程

在PL/SQL程序设计过程中，会遇到很多必须使用动态sql的地方，oracle系统所提供的DMBS_SQL包可以帮助你解决问题。
(一)介绍
DBMS_SQL系统包提供了很多函数及过程,现在简要阐述其中使用频率较高的几种:

function open_cursor:打开一个动态游标,并返回一个整型;

procedure close_cursor(c in out integer);关闭一个动态游标,参数为open_cursor所打开的游标;

procedure parse(c in integer, statement in varchar2, language_flag in integer):对动态游标所提供的sql语句进行解析,参数C表示游标,statement为sql语句,language-flag为解析sql语句所用oracle版本,一般有V6,V7跟native(在不明白所连database版本时,使用native);

procedure 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等(该过程有很多种情况,此处只对一般使用到的类型进行表述);

function execute(c in integer):执行游标,并返回处理一个整型,代表处理结果(对insert,delete,update才有意义,而对select语句而言可以忽略);

function fetch_rows(c in integer):对游标进行循环取数据,并返回一个整数,为0时表示已经取到游标末端;

procedure column_value(c in integer, position in integer, value):将所取得的游标数据赋值到相应的变量,c为游标,position为位置,value则为对应的变量;

procedure bind_variable(c in integer, name in varchar2, value):定义动态sql语句(DML)中所对应字段的值,c为游标,name为字段名称,value为字段的值;

以上是在程序中经常使用到的几个函数及过程,其他函数及过程请参照oracle所提供定义语句dbmssql.sql
(二)一般过程
   1.对于一般的select操作,如果使用动态的sql语句则需要进行以下几个步骤:
open cursor--->parse--->define column--->excute--->fetch rows--->close cursor;
    2.而对于dml操作(insert,update)则需要进行以下几个步骤:
open cursor--->parse--->bind variable--->execute--->close cursor;
    3.对于delete操作只需要进行以下几个步骤:
open cursor--->parse--->execute--->close cursor;

DBMS_SQL
   动态SQL定义过程
DBMS_DDL
   优化/编译/加密等数据库过程
DBMS_JOB
   定时任务调度过程
DBMS_FGA
   细粒度审计策略过程

例：
CREATE OR REPLACE
procedure dml_sql (the_rq varchar2) as
The_c1 Integer;
The_result Integer;--dml_sql_result
M_jls number;
The_xh varchar2(2);
Begin
The_xh:=lpad(ltrim(the_rq),2,'0');
The_C1 :=Dbms_sql.open_cursor;
Dbms_sql.parse(the_C1,'select count(*) from user_indexes
where table_name =''ZDCHB'''||the_xh,dbms_sql.v7);
Dbms_sql.define_column(the_c1,1,M_jls);
The_result:=Dbms_sql.execute(The_c1);
If dbms_sql.fetch_rows(cursor1) > 0 then
Dbms_sql.column_value(cursor1,1,M_jls);
End if;
Dbms_sql.close_cursor(The_C1);