對于一般的select操作,如果使用動(dòng)態(tài)的sql語句則需要進(jìn)行以下幾個(gè)步驟:
open   cursor---> parse---> define   column---> excute---> fetch   rows---> close   cursor;
而對于dml操作(insert,update)則需要進(jìn)行以下幾個(gè)步驟:
open   cursor---> parse---> bind   variable---> execute---> close   cursor;
對于delete操作只需要進(jìn)行以下幾個(gè)步驟:
open   cursor---> parse---> execute---> close   cursor;

例一：
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;  --打開游標(biāo)
   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);  --執(zhí)行
   dbms_sql.close_cursor(v_cursor);   --關(guān)閉游標(biāo)
   commit;
end;

例二：

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;

例三：

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;              --打開游標(biāo)
    dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);  --解析游標(biāo)
    dbms_sql.define_column(v_cursor, 1, v_id);         --定義列
    dbms_sql.define_column(v_cursor, 2, v_name, 50);   --注意：當(dāng)變量為varchar2類型時(shí)，要加長度
    dbms_sql.define_column(v_cursor, 3, v_date, 10);
    v_stat := dbms_sql.execute(v_cursor);        --執(zhí)行SQL
    loop
        exit when dbms_sql.fetch_rows(v_cursor) <= 0;  --fetch_rows在結(jié)果集中移動(dòng)游標(biāo)，如果未抵達(dá)末尾，返回1。
        dbms_sql.column_value(v_cursor, 1, v_id);   --將當(dāng)前行的查詢結(jié)果寫入上面定義的列中。
        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中使用動(dòng)態(tài)SQL編程

    在PL/SQL程序設(shè)計(jì)過程中，會(huì)遇到很多必須使用動(dòng)態(tài)sql的地方，oracle系統(tǒng)所tb提供的DMBS_SQL包可以幫助你解決問題。
(一)介紹

    DBMS_SQL系統(tǒng)包提供了很多函數(shù)及過程,現(xiàn)在簡要闡述其中使用頻率較高的幾種:

    function open_cursor:打開一個(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所打開的游標(biāo);

    procedure parse(c in integer, statement in varchar2, language_flag in integer):對動(dòng)態(tài)游標(biāo)所提供的sql語句進(jìn)行解析,參數(shù)C表示游標(biāo),statement為sql語句,language-flag為解析sql語句所用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)所能得到的對應(yīng)值,其中c為動(dòng)態(tài)游標(biāo),positon為對應(yīng)動(dòng)態(tài)sql中的位置(從1開始),column為該值所對應(yīng)的變量,可以為任何類型,column_size只有在column為定義長度的類型中使用如VARCHAR2,CHAR等(該過程有很多種情況,此處只對一般使用到的類型進(jìn)行表述);

    function execute(c in integer):執(zhí)行游標(biāo),并返回處理一個(gè)整型,代表處理結(jié)果(對insert,delete,update才有意義,而對select語句而言可以忽略);

    function fetch_rows(c in integer):對游標(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則為對應(yīng)的變量;

    procedure bind_variable(c in integer, name in varchar2, value):定義動(dòng)態(tài)sql語句(DML)中所對應(yīng)字段的值,c為游標(biāo),name為字段名稱,value為字段的值;

    以上是在程序中經(jīng)常使用到的幾個(gè)函數(shù)及過程,其他函數(shù)及過程請參照oracle所提供定義語句dbmssql.sql

(二)一般過程

    對于一般的select操作,如果使用動(dòng)態(tài)的sql語句則需要進(jìn)行以下幾個(gè)步驟:
    open cursor--->parse--->define column--->excute--->fetch rows--->close cursor;
    而對于dml操作(insert,update)則需要進(jìn)行以下幾個(gè)步驟:
    open cursor--->parse--->bind variable--->execute--->close cursor;
    對于delete操作只需要進(jìn)行以下幾個(gè)步驟:
    open cursor--->parse--->execute--->close cursor;

(三)具體案例

    下面就本人所開發(fā)系統(tǒng)中某一程序做分析
    該過程為一股票技術(shù)曲線計(jì)算程序,將數(shù)據(jù)從即時(shí)數(shù)據(jù)表中取出,并按照計(jì)算曲線的公式,tb對這些數(shù)據(jù)進(jìn)行計(jì)算,并將結(jié)果保存到技術(shù)曲線表中.
--**********************************
--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ù)曲線
--時(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;--股票收盤價(jià)數(shù)組
TempMa Index_Type;--ma技術(shù)曲線數(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í)間類型
TempSql varchar2(500);--動(dòng)態(tài)sql語句
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語句

  --得到當(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語句,取兩個(gè)字段,時(shí)間及價(jià)格,其中時(shí)間以14位的varchar2表示
  dbms_sql.define_column(cursor1, 1, MyTime, 12);  --分別定義sql語句中各字段所對應(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; 

  --依次對當(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ù)曲線
    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處理,無關(guān)本話題
end;
/

(四)個(gè)人觀點(diǎn)

    在使用dbms_sql系統(tǒng)包的過程中,其方法簡單而又不失靈活,但還是需要注意一些問題:
    1、在整個(gè)程序的設(shè)計(jì)過程中,對游標(biāo)的操作切不可有省略的部分,一旦省略其中某一步驟,則會(huì)程序編譯過程既告失敗,如在程序結(jié)尾處未對改游標(biāo)進(jìn)行關(guān)閉操作,則在再次調(diào)用過程時(shí)會(huì)出現(xiàn)錯(cuò)誤.
    2、dbms_sql除了可以做一般的select,insert,update,delete等靜態(tài)的sql做能在過程中所做工作外,還能執(zhí)行create等DDL操作,不過在執(zhí)行該類操作時(shí)應(yīng)首先顯式賦予執(zhí)行用戶相應(yīng)的系統(tǒng)權(quán)限,比如create table等.該類操作只需open cursor--->prase--->close cursor即能完成.

    以上為本人在工作中對dbms_sql的一點(diǎn)點(diǎn)看法,不到之處,請予指正.
    對于想更深了解dbms_sql的朋友,請閱讀dbmssql.sql文件.
 
 
附個(gè)Oracle自帶的流程說明(強(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 |
  --                      ------------ 
  --
  ---------------


借助別人的力量，我將走得更遠(yuǎn)。

tb