關(guān)于DBMS_SQL的使用
??? 在PL/SQL程序設(shè)計(jì)過(guò)程中，會(huì)遇到很多必須使用動(dòng)態(tài)sql的地方，oracle系統(tǒng)所提供的DMBS_SQL包可以幫助你解決問(wèn)題。
?
?
(一)介紹
??? DBMS_SQL系統(tǒng)包提供了很多函數(shù)及過(guò)程,現(xiàn)在簡(jiǎn)要闡述其中使用頻率較高的幾種:
??? function open_cursor:打開(kāi)一個(gè)動(dòng)態(tài)游標(biāo),并返回一個(gè)整型;
??? procedure close_cursor(c in out integer)
:關(guān)閉一個(gè)動(dòng)態(tài)游標(biāo),參數(shù)為open_cursor所打開(kāi)的游標(biāo);
??? procedure parse(c in integer, statement in varchar2, language_flag in integer):對(duì)動(dòng)態(tài)游標(biāo)所提供的sql語(yǔ)句進(jìn)行解析,參數(shù)C表示游標(biāo),statement為sql語(yǔ)句,language-flag為解析sql語(yǔ)句所用oracle版本,一般有V6,V7跟native(在不明白所連database版本時(shí),使用native);
??? procedure define_column(c in integer, position in integer, column any datatype, [column_size in integer]):定義動(dòng)態(tài)游標(biāo)所能得到的對(duì)應(yīng)值,其中c為動(dòng)態(tài)游標(biāo),positon為對(duì)應(yīng)動(dòng)態(tài)sql中的位置(從1開(kāi)始),column為該值所對(duì)應(yīng)的變量,可以為任何類(lèi)型,column_size只有在column為定義長(zhǎng)度的類(lèi)型中使用如VARCHAR2,CHAR等(該過(guò)程有很多種情況,此處只對(duì)一般使用到的類(lèi)型進(jìn)行表述);
??? function execute(c in integer):執(zhí)行游標(biāo),并返回處理一個(gè)整型,代表處理結(jié)果(對(duì)insert,delete,update才有意義,而對(duì)select語(yǔ)句而言可以忽略);
??? function fetch_rows(c in integer):對(duì)游標(biāo)進(jìn)行循環(huán)取數(shù)據(jù),并返回一個(gè)整數(shù),為0時(shí)表示已經(jīng)取到游標(biāo)末端;
??? procedure column_value(c in integer, position in integer, value):將所取得的游標(biāo)數(shù)據(jù)賦值到相應(yīng)的變量,c為游標(biāo),position為位置,value則為對(duì)應(yīng)的變量;
??? procedure bind_variable(c in integer, name in varchar2, value):定義動(dòng)態(tài)sql語(yǔ)句(DML)中所對(duì)應(yīng)字段的值,c為游標(biāo),name為字段名稱(chēng),value為字段的值;
??? 以上是在程序中經(jīng)常使用到的幾個(gè)函數(shù)及過(guò)程,其他函數(shù)及過(guò)程請(qǐng)參照oracle所提供定義語(yǔ)句dbmssql.sql
?
(二)一般過(guò)程
??? 對(duì)于一般的select操作,如果使用動(dòng)態(tài)的sql語(yǔ)句則需要進(jìn)行以下幾個(gè)步驟:
??? open cursor--->parse--->define column--->excute--->fetch rows--->close cursor;
??? 而對(duì)于dml操作(insert,update)則需要進(jìn)行以下幾個(gè)步驟:
??? open cursor--->parse--->bind variable--->execute--->close cursor;
??? 對(duì)于delete操作只需要進(jìn)行以下幾個(gè)步驟:
??? open cursor--->parse--->execute--->close cursor;
?
(三)具體案例
??? 下面就本人所開(kāi)發(fā)系統(tǒng)中某一程序做分析
??? 該過(guò)程為一股票技術(shù)曲線(xiàn)計(jì)算程序,將數(shù)據(jù)從即時(shí)數(shù)據(jù)表中取出,并按照計(jì)算曲線(xiàn)的公式,對(duì)這些數(shù)據(jù)進(jìn)行計(jì)算,并將結(jié)果保存到技術(shù)曲線(xiàn)表中.
?
--**********************************
--procedure name:R_Ma_Main
--入口參數(shù)：PID股票代碼,PEND時(shí)間,pinterval時(shí)間間隔,totab目標(biāo)數(shù)據(jù)表
--調(diào)用函數(shù)：R_GetSql1,R_GetSql2
--功能：具體計(jì)算單支股票ma技術(shù)曲線(xiàn)
--時(shí)間：2001-06-20
--**********************************
create or replace procedure R_Ma_Main
? (
?? pid varchar2,
?? pend varchar2,
?? pinterval varchar2,
?? totab varchar2
? ) is?????????????????????
?
--定義數(shù)組
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;--時(shí)間數(shù)組
TempIndex Index_Type;--股票收盤(pán)價(jià)數(shù)組
TempMa Index_Type;--ma技術(shù)曲線(xiàn)數(shù)據(jù)
cursor1 integer;--游標(biāo)
cursor2 integer;--游標(biāo)
rows_processed integer;--執(zhí)行游標(biāo)返回
TempInter integer;--參與計(jì)算數(shù)值個(gè)數(shù)
TempVal integer;--計(jì)算時(shí)間類(lèi)型
TempSql varchar2(500);--動(dòng)態(tài)sql語(yǔ)句
MyTime varchar2(12);--時(shí)間
MyIndex number;--數(shù)值
MidIndex number;--中間變量
i integer := 999;
j integer;
begin
? TempInter := to_number(substr(pinterval,1,4));
? TempVal := to_number(substr(pinterval,5,2));
? TempSql := R_GetSql1(pid, pend, TempVal);--得到選擇數(shù)據(jù)的sql語(yǔ)句
? --得到當(dāng)天的即時(shí)數(shù)據(jù),并依次保存到數(shù)組中
? cursor1 := dbms_sql.open_cursor;? --創(chuàng)建游標(biāo)
? dbms_sql.parse(cursor1, TempSql, dbms_sql.native);? --解析動(dòng)態(tài)sql語(yǔ)句,取兩個(gè)字段,時(shí)間及價(jià)格,其中時(shí)間以14位的varchar2表示
? dbms_sql.define_column(cursor1, 1, MyTime, 12);? --分別定義sql語(yǔ)句中各字段所對(duì)應(yīng)變量
? 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;--按倒序的方法填入數(shù)組
????? end;
??? else
????? exit;
??? end if;
? end loop;
? dbms_sql.close_cursor(cursor1);
?
? --如果取得的數(shù)據(jù)量不夠計(jì)算個(gè)數(shù),則跳出程序
? 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;
? --依次對(duì)當(dāng)天數(shù)據(jù)計(jì)算ma值,并保存到ma數(shù)組中
? for j in i+TempInter..999 loop
??? MidIndex := MidIndex - TempIndex(j-TempInter) + TempIndex(j);
??? TempMa(j) := MidIndex/TempInter;
? end loop;??
? if TempVal < 6 then--如果計(jì)算的是分鐘跟天的ma技術(shù)曲線(xiàn)
??? 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);--插入數(shù)據(jù)
??? end loop;
??? end;
? end if;
? commit;
? dbms_sql.close_cursor(cursor2);
? --數(shù)據(jù)量不足跳出
? <<JumpLess>>
? null;
?
? --exception處理,無(wú)關(guān)本話(huà)題
end;
/
?
(四)個(gè)人觀(guān)點(diǎn)
??? 在使用dbms_sql系統(tǒng)包的過(guò)程中,其方法簡(jiǎn)單而又不失靈活,但還是需要注意一些問(wèn)題:
?
??? 1、在整個(gè)程序的設(shè)計(jì)過(guò)程中,對(duì)游標(biāo)的操作切不可有省略的部分,一旦省略其中某一步驟,則會(huì)程序編譯過(guò)程既告失敗,如在程序結(jié)尾處未對(duì)改游標(biāo)進(jìn)行關(guān)閉操作,則在再次調(diào)用過(guò)程時(shí)會(huì)出現(xiàn)錯(cuò)誤.
??? 2、dbms_sql除了可以做一般的select,insert,update,delete等靜態(tài)的sql做能在過(guò)程中所做工作外,還能執(zhí)行create等DDL操作,不過(guò)在執(zhí)行該類(lèi)操作時(shí)應(yīng)首先顯式賦予執(zhí)行用戶(hù)相應(yīng)的系統(tǒng)權(quán)限,比如create table等.該類(lèi)操作只需open cursor--->prase--->close cursor即能完成.
??? 以上為本人在工作中對(duì)dbms_sql的一點(diǎn)點(diǎn)看法,不到之處,請(qǐng)予指正.
??? 對(duì)于想更深了解dbms_sql的朋友,請(qǐng)閱讀dbmssql.sql文件.
?
?
附個(gè)Oracle自帶的流程說(shuō)明(強(qiáng)大啊)：
?
? --? The flow of procedure calls will typically look like this:
? --
? --????????????????????? -----------
? --??????????????????? | open_cursor |
? --????????????????????? -----------
? --?????????????????????????? |
? --?????????????????????????? |
? --?????????????????????????? v
? --???????????????????????? -----
? --????????? ------------>| parse |
? --???????? |?????????????? -----
? --???????? |???????????????? |
? --???????? |???????????????? |---------
? --???????? |???????????????? v???????? |
? --???????? |?????????? --------------? |
? --???????? |-------->| bind_variable | |
? --???????? |???? ^???? -------------?? |
? --???????? |???? |?????????? |???????? |
? --???????? |????? -----------|???????? |
? --???????? |???????????????? |<--------
? --???????? |???????????????? v
? --???????? |?????????????? query?---------- yes ---------
? --???????? |???????????????? |?????????????????????????? |
? --???????? |??????????????? no?????????????????????????? |
? --???????? |???????????????? |?????????????????????????? |
? --???????? |???????????????? v?????????????????????????? v
? --???????? |????????????? -------????????????????? -------------
? --???????? |----------->| execute |??????????? ->| define_column |
? --???????? |????????????? -------???????????? |??? -------------
? --???????? |???????????????? |------------??? |????????? |
? --???????? |???????????????? |??????????? |??? ----------|
? --???????? |???????????????? v??????????? |????????????? v
? --???????? |?????????? --------------???? |?????????? -------
? --???????? |?????? ->| variable_value |?? |? ------>| execute |
? --???????? |????? |??? --------------???? | |???????? -------
? --???????? |????? |????????? |??????????? | |??????????? |
? --???????? |?????? ----------|??????????? | |??????????? |
? --???????? |???????????????? |??????????? | |??????????? v
? --???????? |???????????????? |??????????? | |??????? ----------
? --???????? |???????????????? |<-----------? |----->| fetch_rows |
? --???????? |???????????????? |????????????? |??????? ----------
? --???????? |???????????????? |????????????? |??????????? |
? --???????? |???????????????? |????????????? |??????????? v
? --???????? |???????????????? |????????????? |??? --------------------
? --???????? |???????????????? |????????????? |? | column_value???????? |
? --???????? |???????????????? |????????????? |? | variable_value?????? |
? --???????? |???????????????? |????????????? |??? ---------------------
? --???????? |???????????????? |????????????? |??????????? |
? --???????? |???????????????? |<--------------------------
? --???????? |???????????????? |
? --????????? -----------------|
? --?????????????????????????? |
? --?????????????????????????? v
? --????????????????????? ------------
? --??????????????????? | close_cursor |
? --????????????????????? ------------
? --
? ---------------
?
?