Oracle 動態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;

執行個體分析
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 pmhtmpunchkcpc@unionbill where ipno=iipno and dateno=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, 'ipno', j);
      dbms_sql.bind_variable(cid2, 'dateno', 2);
      icount := dbms_sql.execute(cursor2);--插入資料
    end loop;

    
     
  

     --關閉游標的不要忘記了
     if(dbms_sql.is_open(cid1)) then --如果動態游標仍然是開的
        dbms_sql.close_cursor(cid1); --關閉
     end if;
     ---記得關掉第二個動態游標
     if cur1%isopen then
        close cur1;
     end if;