生活随笔
收集整理的這篇文章主要介紹了
DBMS_SQL包使用
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
DBMS_SQL package 學習
?這個包提供了一種使用動態sql來訪問數據庫的方法。
第一步：打開游標
使用函數 function open_cursor return integer;
定義變量 Cur_1 integer; --返回的新游標的ID值
語句是Cur_1 := Dbms_Sql.Open_Cursor;
第二步：解析要執行的語句
使用過程procedure parse(c in integer, statement in varchar2,??????????????????language_flag in integer);
語句是Dbms_Sql.Parse(Cur_1, ’sql語句’, Dbms_Sql.V7);
第三步：定義字段變量，其值對應于指定游標中某個位置元素的值（僅用于SELECT語句）
使用過程procedure define_column(c in integer, position in integer, column in number);
語句是Dbms_Sql.Define_Column(Cur_1, 1, column);
定義第一列為column，可重復定義多個列；
第四步：執行指定游標
使用過程function execute(c in integer) return integer;--返回行數
語句是Rows_1 := Dbms_Sql.EXECUTE(Cur_1);
第五步：從指定的游標中取出記錄
使用過程function fetch_rows(c in integer) return integer;--返回行數
采用If Dbms_Sql.Fetch_Rows(Cur_1) > 0 then 判斷是否取到數據了
然后返回游標中指定位置的元素，使用過程procedure column_value(c in integer, position in integer, value out number);
語句是Dbms_Sql.Column_Value(Cur_1, 1, column);
把游標中的第一列的值賦值給column，可重復賦值多個列；
要加上end if；
最后關閉游標
語句是Dbms_Sql.Close_Cursor(Cur_1);
通常運用DBMS_SQL包一般分為幾步：
1. open cursor： 打開cursor
2. parse cursor：解析你要執行的
SQL語句
3. bind variable：如果要執行的SQL語句中包含變量，在此就需要綁定變量
4. execute：執行SQL語句
5. close cursor：在執行后關閉此cursor.
如果你還需要返回執行SQL的結果集，還需要使用define_column,define_array等方法。
下面根據不同情況進行詳細展示：
在做展示之前，先準備一些基礎數據
Sql代碼??
create?table?demo?(a?number,b?number,c?number);?? begin?? ??for?i?in?1?..?15?loop?? ????insert?into?demo?? ????values?? ??????(round(dbms_random.value,?2)?*?100,?? ???????round(dbms_random.value,?2)?*?100,?? ???????round(dbms_random.value,?2)?*?100);?? ??end?loop;?? ??commit;?? end;??
?
基礎數據完成之后，下面開始對一些具體情況進行分析：
1.執行一般的select語句
?? 首先先介紹最常用情況：
Sql代碼??
create?or?replace?procedure?define_column(no?in?number)?is?? ??cursor_name??????integer?:=?dbms_sql.open_cursor;??? ??row_process??????integer;?? ??v_b?number;?? begin?? ???? ??dbms_sql.parse(cursor_name,?? ?????????????????'select?*?from?demo?where?a=?:no',?? ?????????????????dbms_sql.native);?? ???? ??dbms_sql.bind_variable(cursor_name,?'no',?no);?? ??/*如果需要返回查詢語句的結果,則必須在exec之前使用define_column函數定義返回字段;define_column函數的第一個參數是最初定義的cursor?name,第二個參數是指需要返回的字段在查詢結果中處于第幾列,在此例中返回的字段是查詢結果中的第二列,即b列;第三個參數就是接收返回結果需要的變量*/?? ??dbms_sql.define_column(cursor_name,?2,?v_b);?? ???? ??row_process?:=?dbms_sql.execute(cursor_name);?? ??loop?? ????if?dbms_sql.fetch_rows(cursor_name)?>?0?then?? ???????? ??????dbms_sql.column_value(cursor_name,?2,?v_b);?? ??????dbms_output.put_line('B?is?'?||?v_b);?? ????else?? ??????exit;?? ????end?if;?? ??end?loop;?? ???? ??dbms_sql.close_cursor(cursor_name);?? exception?? ??when?others?then?? ????dbms_sql.close_cursor(cursor_name);?? end;??
?
2.使用define_array方法得到查詢結果
??? 前面已經分析了如何使用define_column方法得到查詢結果，但有時我們想要一次得到多行查詢結果，此時我們就需要使用define_array方法，此方法常用于DML操作，稍后會有例子對此介紹，現在先來看一下如果使用define_array.
Sql代碼??
create?or?replace?procedure?define_array?is?? ??c??????NUMBER;?? ??d??????NUMBER;?? ??/*DBMS_SQL.NUMBER_TABLE類型實際就是type?NUMBER_TABLE?is?table?of?number?index?by?binary_integer;*/?? ??n_tab??DBMS_SQL.NUMBER_TABLE;?? ??n_tab1?DBMS_SQL.NUMBER_TABLE;?? ??indx???NUMBER?:=?1;?? BEGIN?? ??c?:=?DBMS_SQL.OPEN_CURSOR;?? ??DBMS_SQL.PARSE(c,?? ?????????????????'select?*?from?demo?where?rownum<13?order?by?1',?? ?????????????????DBMS_SQL.NATIVE);?? ??/*在此需要特別介紹一下define_array函數的第一個參數是已經打開的cursor名稱,?第二個參數是指需要返回的字段在查詢結果中處于第幾列,第三個參數就是接收返回結果需要的變量,與define_column不同的是此變量是table,而不是普通的字段類型;第四個參數表示一次可以返回的行數;第五個參數是指n_tab的index從哪個數值開始,此數值是遞增的.在此例中index是從1開始的，一次得到9行結果集，則有n_tab(1)到n_tab(9),如果循環再得到新的結果集,則index繼續增長n_tab(10)....*/?? ??DBMS_SQL.DEFINE_ARRAY(c,?1,?n_tab,?9,?indx);?? ??DBMS_SQL.DEFINE_ARRAY(c,?2,?n_tab1,?9,?indx);?? ??d?:=?DBMS_SQL.EXECUTE(c);?? ??loop?? ????d?:=?DBMS_SQL.FETCH_ROWS(c);?? ????dbms_output.put_line('fetch?rows?is?'?||?d);?? ????EXIT?WHEN?d?<?9;?? ????DBMS_SQL.COLUMN_VALUE(c,?1,?n_tab);?? ????DBMS_SQL.COLUMN_VALUE(c,?2,?n_tab1);?? ????for?i?in?1?..?d?loop?? ??????dbms_output.put_line(n_tab(i)?||?','?||?n_tab1(i));?? ????end?loop;?? ??END?LOOP;?? ??DBMS_SQL.CLOSE_CURSOR(c);?? EXCEPTION?? ??WHEN?OTHERS?THEN?? ????IF?DBMS_SQL.IS_OPEN(c)?THEN?? ??????DBMS_SQL.CLOSE_CURSOR(c);?? ????END?IF;?? END;??
?
3.使用variable_value顯示DML后的返回結果(單條記錄)
?? 以上我們介紹了如何使用DBMS_SQL包來處理數據查詢，如果我們把查詢語句更換成DML語句，則可以完成各種DML操作。
? 在PL/SQL中我們可以使用returning方法返回DML操作結果，在DBMS_SQL包中可不可以實現呢？答案當然是可以，用variable_value方法就可以實現。下面就分別用兩個例子來展示如何實現，一個是返回單條記錄，另一個是返回多條記錄。
（1）返回單條記錄
Sql代碼??
procedure?single_insert(c1?in?number,?c2?in?number,?r?out?number)?is?? ?? ??cursor_name?number?:=?dbms_sql.open_cursor;?? ??n???number;?? begin?? ??dbms_sql.parse(cursor_name,?? ?????????????????'insert?into?demo?values?(:a,:b)?returning?:a*:b?into?:r',?? ?????????????????dbms_sql.native);?? ??dbms_sql.bind_variable(cursor_name,?'a',?c1);?? ??dbms_sql.bind_variable(cursor_name,?'b',?c2);?? ??dbms_sql.bind_variable(cursor_name,?'r',?r);?? ??n?:=?dbms_sql.execute(cursor_name);?? ???? ??dbms_sql.variable_value(cursor_name,?'r',?r);?? ??dbms_output.put_line(r);?? ??dbms_sql.close_cursor(cursor_name);?? exception?? ??when?others?then?? ????dbms_sql.close_cursor(cursor_name);?? end;??
?
（2）返回多條記錄
結合define_array使用，可以更好的完成DML操作。
Sql代碼??
create?or?replace?package?DBMS_SQL_DEMO?as?? ?? ??procedure?multi_insert;?? ??? end;?? /?? ?? create?or?replace?package?body?DBMS_SQL_DEMO?as?? ?? ??procedure?multi_insert_priv(c1?in?dbms_sql.Number_Table,?? ??????????????????????????????c2?in?dbms_sql.Number_Table,?? ??????????????????????????????r??out?dbms_sql.Number_Table)?is?? ??? ????cursor_name?number?:=?dbms_sql.open_cursor;?? ????n???????????number;?? ??begin?? ????dbms_sql.parse(cursor_name,?? ???????????????????'insert?into?demo?values?(:a,:b)?returning?:a*:b?into?:r',?? ???????????????????dbms_sql.native);?? ?????? ????dbms_sql.bind_array(cursor_name,?'a',?c1);?? ????dbms_sql.bind_array(cursor_name,?'b',?c2);?? ????dbms_sql.bind_array(cursor_name,?'r',?r);?? ????n?:=?dbms_sql.execute(cursor_name);?? ?????? ????dbms_sql.variable_value(cursor_name,?'r',?r);?? ????dbms_sql.close_cursor(cursor_name);?? ??exception?? ????when?others?then?? ??????dbms_sql.close_cursor(cursor_name);?? ??end;?? ?? ??procedure?multi_insert?is?? ????c1??????????dbms_sql.Number_Table;?? ????c2??????????dbms_sql.Number_Table;?? ????cursor_name?number?:=?dbms_sql.open_cursor;?? ????n???????????number;?? ????r???????????dbms_sql.Number_Table;?? ????indx????????number?:=?1;?? ????d???????????number;?? ??begin?? ????dbms_sql.parse(cursor_name,?'select?*?from?demo',?dbms_sql.native);?? ????dbms_sql.define_array(cursor_name,?1,?c1,?5,?indx);?? ????dbms_sql.define_array(cursor_name,?2,?c2,?5,?indx);?? ????n?:=?dbms_sql.execute(cursor_name);?? ????loop?? ??????d?:=?dbms_sql.fetch_rows(cursor_name);?? ??????exit?when?d?=?0;?? ??????dbms_sql.column_value(cursor_name,?1,?c1);?? ??????dbms_sql.column_value(cursor_name,?2,?c2);?? ??????multi_insert_priv(c1,?c2,?r);?? ??????for?i?in?1?..?r.count?loop?? ????????dbms_output.put_line(r(i));?? ??????end?loop;?? ????end?loop;?? ??exception?? ????when?others?then?? ??????dbms_sql.close_cursor(cursor_name);?? ??end;?? ?? end;?? / ?
總結
以上是生活随笔為你收集整理的DBMS_SQL包使用的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。
