首页  ·  知识 ·  数据库
PL/SQL中使用动态SQL编程
网友    Oracle  编辑:dezai   图片来源:网络
在PL/SQL程序设计过程中,会遇到很多必须使用动态sql的地方,oracle系统所提供的DMBS_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

(二)一般过程

对于一般的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个测试用的表

SQL> create table sun_part

2 (area number(3),

3 name varchar2(32)

4 )

5 partition by range (area)

6 (

7 partition part_530 values less than(531),

8 partition part_531 values less than(532),

9 partition part_532 values less than(533),

10 partition part_max values less than(999)

11 );

表已创建。

SQL> create table sun_part_his

2 (region number(3),

3 name varchar2(32),

4 deal_date date default sysdate

5 );

表已创建。

--第二步:插入几条测试数据

SQL> insert into sun_part values(530,"HZ");已创建 1 行。

SQL> insert into sun_part values(531,"JN");已创建 1 行。

SQL> insert into sun_part values(532,"QD");已创建 1 行。

SQL> insert into sun_part values(571,"HangZhou");已创建 1 行。

SQL> commit;提交完成。

SQL> select * from sun_part;

AREA NAME

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

530 HZ

531 JN

532 QD

571 HangZhou

SQL>

第三步:定义sun_demo过程

SQL> create or replace procedure sun_demo(p_area in varchar2) IS

2 v_sql varchar2(2000);

3

4 v_CursorID number;

5 v_CursorRET number;

6

7 v_rowid varchar2(20);

8 v_area number(3);

9 v_name varchar2(32);

10

11 begin

12 --判断p_area参数是否有效

13 if lower(p_area) not in ("530","531","532","max") then

14 raise_application_error(-20001,"Warning:Argument Invalid");

15 end if;

16

17 v_sql:="select rowid,area,name from sun_part partition(part_"||p_area||")";

18 v_CursorID:=DBMS_SQL.OPEN_CURSOR;

19

20 DBMS_SQL.PARSE(v_CursorID, v_sql, DBMS_SQL.NATIVE);

21

22 dbms_sql.define_column(v_CursorID, 1, v_rowid,20);

23 dbms_sql.define_column(v_CursorID, 2, v_area);

24 dbms_sql.define_column(v_CursorID, 3, v_name,32);

25

26 v_CursorRET:=DBMS_SQL.EXECUTE(v_CursorID);

27 loop

28 if dbms_sql.fetch_rows(v_CurSorID) = 0 then

29 dbms_sql.close_cursor(v_CurSorID);

30 exit;

31 end if;

32

33 dbms_sql.column_value(v_CurSorID,1,v_rowid);

34 dbms_sql.column_value(v_CurSorID,2,v_area);

35 dbms_sql.column_value(v_CurSorID,3,v_name);

36 insert into sun_part_his values(v_area,v_name,sysdate);

37 delete from sun_part where rowid=v_rowid;

38 end loop;

39 commit;

40 end sun_demo;

41 /

过程已创建。

SQL>

第四步:测试验证

SQL> desc sun_demo;

PROCEDURE sun_demo参数名称 类型 输入/输出默认值?

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

P_AREA VARCHAR2 IN

SQL> select * from sun_part_his;

未选定行

SQL> select * from sun_part;

AREA NAME

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

530 HZ

531 JN

532 QD

571 HangZhou

SQL> exec sun_demo("530");

PL/SQL 过程已成功完成。

SQL> select * from sun_part_his;

REGION NAME DEAL_DATE

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

530 HZ 29-5月 -06

SQL> select * from sun_part;

AREA NAME

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

531 JN

532 QD

571 HangZhou

SQL> exec sun_demo("532");

PL/SQL 过程已成功完成。

SQL> select * from sun_part_his;

REGION NAME DEAL_DATE

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

530 HZ 29-5月 -06

532 QD 29-5月 -06

SQL> select * from sun_part;

AREA NAME

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

531 JN

571 HangZhou

SQL>

--异常测试[建立sun_demo的异常判断,仅仅是为了演示]

SQL> exec sun_demo("021");

BEGIN sun_demo("021"); END;

*

ERROR 位于第 1 行:

ORA-20001: Warning:Argument Invalid

ORA-06512: 在"STUDY.SUN_DEMO", line 14

ORA-06512: 在line 1

(四)注意问题:

1,在整个程序的设计过程中,对游标的操作切不可有省略的部分,一旦省略其中某一步骤,则会程序编译过程既告失败,如在程序结尾处未对改游标进行关闭操作,则在再次调用过程时会出现错误.

2,dbms_sql除了可以做一般的select,insert,update,delete等静态的sql做能在过程中所做工作外,还能执行create等DDL操作,不过在执行该类操作时应首先显式赋予执行用户相应的系统权限,比如create table等.该类操作只需open cursor--->prase--->close cursor即能完成.

本文作者:网友 来源:网络
CIO之家 www.ciozj.com 微信公众号:imciow
   
免责声明:本站转载此文章旨在分享信息,不代表对其内容的完全认同。文章来源已尽可能注明,若涉及版权问题,请及时与我们联系,我们将积极配合处理。同时,我们无法对文章内容的真实性、准确性及完整性进行完全保证,对于因文章内容而产生的任何后果,本账号不承担法律责任。转载仅出于传播目的,读者应自行对内容进行核实与判断。请谨慎参考文章信息,一切责任由读者自行承担。
延伸阅读