ORACLE中DBMS_SQL的用法——http://www.2cto.com/database/201209/156873.html

最新推荐文章于 2024-01-24 07:11:03 发布

大巧不工

最新推荐文章于 2024-01-24 07:11:03 发布

阅读量805

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

37 篇文章 1 订阅

订阅专栏

ORACLE中DBMS_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;

www.2cto.com

例一：

create table test(n_id number, v_name varchar2(50), d_insert_date date);

alter table test add constraint pk_id primary key(n_id);

declare

v_cursor number;

v_sql varchar2(200);

v_id number;

v_name varchar2(50);

v_date date;

v_stat number;

begin

v_id := 1;

v_name := '测试 insert';

v_date := sysdate;

v_cursor := dbms_sql.open_cursor; --打开游标

v_sql := 'insert into test(n_id, v_name, d_insert_date) values(:v_id,:v_name,:v_date)';

dbms_sql.parse(v_cursor, v_sql, dbms_sql.native); --解析SQL

dbms_sql.bind_variable(v_cursor, ':v_id', v_id); --绑定变量

dbms_sql.bind_variable(v_cursor, ':v_name', v_name);

dbms_sql.bind_variable(v_cursor, ':v_date', v_date);

v_stat := dbms_sql.execute(v_cursor); --执行

dbms_sql.close_cursor(v_cursor); --关闭游标

commit;

end;

www.2cto.com

例二：

declare

v_cursor number;

v_sql varchar2(200);

v_id number;

v_name varchar2(50);

v_stat number;

begin

v_name := '测试 update';

v_id := 1;

v_cursor := dbms_sql.open_cursor;

v_sql := 'update test set v_name = :v_name, d_insert_date = :v_date where n_id = :v_id';

dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);

dbms_sql.bind_variable(v_cursor, ':v_name', v_name);

dbms_sql.bind_variable(v_cursor, ':v_date', sysdate);

dbms_sql.bind_variable(v_cursor, ':v_id', v_id);

v_stat := dbms_sql.execute(v_cursor);

dbms_sql.close_cursor(v_cursor);

commit;

end;

www.2cto.com

例三：

declare

v_cursor number;

v_sql varchar2(200);

v_id number;

v_stat number;

begin

v_id := 1;

v_sql := 'delete from test where n_id = :v_id';

v_cursor := dbms_sql.open_cursor;

dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);

dbms_sql.bind_variable(v_cursor, ':v_id', v_id);

v_stat := dbms_sql.execute(v_cursor);

dbms_sql.close_cursor(v_cursor);

commit;

end;

例四：

declare

v_cursor number;

v_sql varchar2(200);

v_id number;

v_name varchar2(50);

v_date varchar2(10);

v_stat number;

begin

v_sql := 'select n_id, v_name, to_char(d_insert_date, ''yyyy-mm-dd'') from test';

v_cursor := dbms_sql.open_cursor; --打开游标

dbms_sql.parse(v_cursor, v_sql, dbms_sql.native); --解析游标

dbms_sql.define_column(v_cursor, 1, v_id); --定义列

dbms_sql.define_column(v_cursor, 2, v_name, 50); --注意：当变量为varchar2类型时，要加长度 www.2cto.com

dbms_sql.define_column(v_cursor, 3, v_date, 10);

v_stat := dbms_sql.execute(v_cursor); --执行SQL

loop

exit when dbms_sql.fetch_rows(v_cursor) <= 0; --fetch_rows在结果集中移动游标，如果未抵达末尾，返回1。

dbms_sql.column_value(v_cursor, 1, v_id); --将当前行的查询结果写入上面定义的列中。

dbms_sql.column_value(v_cursor, 2, v_name);

dbms_sql.column_value(v_cursor, 3, v_date);

dbms_output.put_line(v_id || ':' || v_name || ':' || v_date);

end loop;

end;

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

PL/SQL中使用动态SQL 编程

在PL/SQL程序设计过程中，会遇到很多必须使用动态sql的地方，oracle系统所tb提供的DMBS_SQL包可以帮助你解决问题。

(一)介绍

DBMS_SQL系统包提供了很多函数及过程,现在简要阐述其中使用频率较高的几种:

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

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

www.2cto.com

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

(二)一般过程

www.2cto.com

对于一般的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;

(三)具体案例

下面就本人所开发系统中某一程序做分析

该过程为一股票技术曲线计算程序,将数据从即时数据表中取出,并按照计算曲线的公式,tb对这些数据进行计算,并将结果保存到技术曲线表中.

create or replace procedure R_Ma_Main

( www.2cto.com

pid varchar2,

pend varchar2,

pinterval varchar2,

totab varchar2

) is

--定义数组

type Date_type is table of varchar2(12) index by binary_integer;

type Index_type is table of number index by binary_integer;

TempDate Date_Type;--时间数组

TempIndex Index_Type;--股票收盘价数组

TempMa Index_Type;--ma技术曲线数据

cursor1 integer;--游标

cursor2 integer;--游标

rows_processed integer;--执行游标返回

TempInter integer;--参与计算数值个数

TempVal integer;--计算时间类型

TempSql varchar2(500);--动态sql语句

MyTime varchar2(12);--时间

MyIndex number;--数值

MidIndex number;--中间变量

i integer := 999;

j integer;

begin www.2cto.com

TempInter := to_number(substr(pinterval,1,4));

TempVal := to_number(substr(pinterval,5,2));

TempSql := R_GetSql1(pid, pend, TempVal);--得到选择数据的sql语句

--得到当天的即时数据,并依次保存到数组中

cursor1 := dbms_sql.open_cursor; --创建游标

dbms_sql.parse(cursor1, TempSql, dbms_sql.native); --解析动态sql语句,取两个字段,时间及价格,其中时间以14位的varchar2表示

dbms_sql.define_column(cursor1, 1, MyTime, 12); --分别定义sql语句中各字段所对应变量

dbms_sql.define_column(cursor1, 2, MyIndex);

rows_processed := dbms_sql.execute(cursor1);

loop

if dbms_sql.fetch_rows(cursor1) > 0 then

begin

dbms_sql.column_value(cursor1, 1, MyTime);

dbms_sql.column_value(cursor1, 2, MyIndex);

TempDate(i) := MyTime;

TempIndex(i) := MyIndex;

i := i - 1;--按倒序的方法填入数组

end;

else

exit;

end if;

end loop;

dbms_sql.close_cursor(cursor1);

--如果取得的数据量不够计算个数,则跳出程序

if i > 999-TempInter then

goto JumpLess;

end if;

--初始化中间变量

MidIndex := 0;

TempIndex(i) := 0;

for j in i..i+TempInter-1 loop

MidIndex := MidIndex + TempIndex(j);

end loop;

www.2cto.com

--依次对当天数据计算ma值,并保存到ma数组中

for j in i+TempInter..999 loop

MidIndex := MidIndex - TempIndex(j-TempInter) + TempIndex(j);

TempMa(j) := MidIndex/TempInter;

end loop;

if TempVal < 6 then--如果计算的是分钟跟天的ma技术曲线

begin

cursor2 := dbms_sql.open_cursor;

TempSql := 'insert into ' || totab || ' values(:r_no, :i_interval, :i_time, :i_index)';

dbms_sql.parse(cursor2, TempSql, dbms_sql.native);

for j in i+TempInter..999 loop

dbms_sql.bind_variable(cursor2, 'r_no', pid);

dbms_sql.bind_variable(cursor2, 'i_interval', pinterval);

dbms_sql.bind_variable(cursor2, 'i_time', TempDate(j));

dbms_sql.bind_variable(cursor2, 'i_index', TempMa(j));

rows_processed := dbms_sql.execute(cursor2);--插入数据

end loop;

end;

end if;

commit;

dbms_sql.close_cursor(cursor2);

--数据量不足跳出

<<JumpLess>>

null;

--exception处理,无关本话题

end;

www.2cto.com

(四)个人观点

在使用dbms_sql系统包的过程中,其方法简单而又不失灵活,但还是需要注意一些问题:

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

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

以上为本人在工作中对dbms_sql的一点点看法,不到之处,请予指正.

对于想更深了解dbms_sql的朋友,请阅读db mssql.sql文件.

www.2cto.com

附个 Oracle自带的流程说明(强大啊)：

-- The flow of procedure calls will typically look like this:

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

-- | open_cursor |

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

-- |

-- v

-- -----

-- ------------>| parse |