DBMS_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

(二)一般過程

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

(三)具體案例

下面就本人所開發系統中某一程式做分析

該過程為一股票技術曲線計算程式,将資料從即時資料表中取出,并按照計算曲線的公式,對這些資料進行計算,并将結果儲存到技術曲線表中.

--**********************************

--procedure name:R_Ma_Main

--入口參數：PID股票代碼,PEND時間,pinterval時間間隔,totab目标資料表

--調用函數：R_GetSql1,R_GetSql2

--功能：具體計算單支股票ma技術曲線

--時間：2001-06-20

--**********************************

create or replace procedure R_Ma_Main

(

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

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;

--依次對當天資料計算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;

/

(四)個人觀點

在使用dbms_sql系統包的過程中,其方法簡單而又不失靈活,但還是需要注意一些問題:

1,在整個程式的設計過程中,對遊标的操作切不可有省略的部分,一旦省略其中某一步驟,則會程式編譯過程既告失敗,如在程式結尾處未對改遊标進行關閉操作,則在再次調用過程時會出現錯誤.

2,dbms_sql除了可以做一般的select,insert,update,delete等靜态的sql做能在過程中所做工作外,還能執行 create等DDL操作,不過在執行該類操作時應首先顯式賦予執行使用者相應的系統權限,比如create table等.該類操作隻需open cursor--->prase--->close cursor即能完成.

以上為本人在工作中對dbms_sql的一點點看法,不到之處,請予指正.

對于想更深了解dbms_sql的朋友,請閱讀dbmssql.sql檔案.