轉自：http://jonescheng.iteye.com/blog/183620

Oracle 動态SQL學習筆記

部落格分類：

• [随筆分類]Oracle資料庫

SQL Oracle C C++ C#  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;

執行個體分析

create  or  replace  procedure p_oneproduc  is

--變量定義

iid int;

icount int;

strtblname varchar2(100);

ssql varchar2(1024);

serrmsg varchar2(1024);

cid1 number;--動态遊标光标編号

cid2 number;--動态光标編号

iipno number;--從動态光标中取出值的存放變量

idateno number;--從動态光标中取出值的存放變量

i number;

cursor cur1 is select id,table_name from userisit where status=2;--定義一個光标

--定義結束

begin

 select count(*) into icount from uservisit where status=2;//查詢使用者通路表将記錄彙總統計存入icount中

 if icount>0 then   

    open cur1;//打開cur1光标

      fetch cur1 into iid,strtblname; //取出光标的值并指派給iid,strtblname，相應的變量和select變量同序

      exit when cur1%notfound; //如果記錄為空時退出 cur%notfound是cur的一個變量，當記錄為空時為true

      --重頭戲，，動态SQL開始(具體可以參考前面的方法說明)

      ssql:='select distinct ipno,dateno from '||strtblname; --定義一個SQL語句，後面用動态SQL進行執行

      cid1:=dbms_sql.open_cursor; --定義一個動态光标用來執行前面定義的SQL語句

      dbms_sql.Parse(cid1,ssql,dbms_sql.v7); --分析SQL語句

      dbms_sql.Define_Column(cid1,1,iipno); --定義要取出的字段值,1表示第一個字段要取出，即Select語句的ipno

      dbms_sql.Define_Column(cid1,2,idateno);--同上說明

      icount:=dbms_sql.execute(cid1); --執行Sql語句，這裡icount得到一個執行的結果

      --執行SQL語句可以和普通光标一樣進行取值操作

      i:=0;

      loop

        If dbms_sql.fetch_rows(cid1) > 0 then --如果動态光标的記錄數大于0，則進行取值操作

          begin

            dbms_sql.column_value(cid1,1,iipno); --取出值

            dbms_sql.column_value(cid1,2,idateno);--同上

            select count(*) into icount from [email protected] where ipno=iipno anddateno=idateno;

            if icount>0 then

              ssql:='update FIRSTCDAY_'||idateno||'@unionbill set isreach=1 where ipno='||iipno;

              execute immediate ssql;//動态立即執行一個SQL語句

            end if

           end

        end if

      end loop

        exception when others then

           null;

        end;

   --運用動态光标執行一組插入操作 （純碎是為了記錄動态插入中的指派的用法，無邏輯可言）

     ssql:= 'insert uservisit(ipno,dateno) values(:iipno,:idateno)'; --定義一個SQL語句，後面用動态SQL進行執行

     cid2:=dbms_sql.open_cursor; --定義一個動态光标用來執行前面定義的SQL語句

     dbms_sql.Parse(cid2,ssql,v7);

     for j in 1..999 loop

      dbms_sql.bind_variable(cid2, 'iipno', j);

      dbms_sql.bind_variable(cid2, 'idateno', 2);

      icount := dbms_sql.execute( cid2 );--插入資料

    end loop;

     --關閉光标的不要忘記了

     if(dbms_sql.is_open(cid1)) then --如果動态光标仍然是開的

        dbms_sql.close_cursor(cid1); --關閉

     end if;

     ---記得關掉第二個動态光标

     if cur1%isopen then

        close cur1;

     end if;

動态遊标執行個體練習 declare

  v_curId   integer;

  v_sql     varchar2(1000);

  v_well_id varchar2(100);

  v_ret     number(3);

  v_id      varchar2(10);

begin

  v_id  := 'abcdefgh';

  v_sql := 'SELECT ID FROM TEST3 WHERE ID = LOWER('||chr(39)||v_id||chr(39)||')';

  v_curId := DBMS_SQL.OPEN_CURSOR;

  DBMS_SQL.PARSE(v_curId, v_Sql, DBMS_SQL.V7);

  DBMS_SQL.DEFINE_COLUMN(v_curId, 1, v_well_id, 128);

  v_ret := DBMS_SQL.EXECUTE(v_curId);

  loop

    if DBMS_SQL.FETCH_ROWS(v_curId) = 0 then

      exit;

    end if;

    DBMS_SQL.COLUMN_VALUE(v_curId, 1, v_well_id);

    DBMS_OUTPUT.PUT_LINE(v_well_id);

  end loop;

  DBMS_SQL.CLOSE_CURSOR(v_curid);

end;