ORACLE EXECUTE IMMEDIATE 用法

先轉載過來看，再整理吧~~~

ORACLE EXECUTE IMMEDIATE 用法
EXECUTE IMMEDIATE 代替了以前Oracle8i中DBMS_SQL package包.
它解析並馬上執行動態SQL語句或非運行時建立的PL/SQL塊.動態建立和執行SQL語句效能超前，EXECUTE IMMEDIATE的目標在於減小企業費用並獲得較高的效能，較之以前它相當容易編碼.儘管DBMS_SQL仍然可用，但是推薦使用EXECUTE IMMEDIATE,因為它獲的收益在包之上。
– 提示
1. EXECUTE IMMEDIATE將不會提交一個DML事務執行，應該顯式提交
如果通過EXECUTE IMMEDIATE處理DML命令，
那麼在完成以前需要顯式提交或者作為EXECUTE IMMEDIATE自己的一部分.
如果通過EXECUTE IMMEDIATE處理DDL命令,它提交所有以前改變的資料
2. 不支援返回多行的查詢,這種互動將用暫存資料表來儲存記錄(參照例子如下)或者用REF cursors.
3. 當執行SQL語句時，不要用分號，當執行PL/SQL塊時，在其尾部用分號.
4. 在Oracle手冊中，未詳細覆蓋這些功能。
下面的例子展示了所有用到Execute immediate的可能方面.希望能給你帶來方便.
5. 對於Forms開發人員,當在PL/SQL 8.0.6.3.版本中，Forms 6i不能使用此功能.
EXECUTE IMMEDIATE – 用法例子
1. 在PL/SQL運行DDL語句
Sql代碼

begin    execute immediate 'set role all'; end;

給動態語句傳值(USING 子句)
Sql代碼

declare    l_depnam varchar2(20) := 'testing';    l_loc     varchar2(10) := 'D?i';    begin    execute immediate 'insert into dept val?s   (:1, :2, :3)'      using 50, l_depnam, l_loc;    commit; end;

從動態語句檢索值(INTO子句)
Sql代碼

declare    l_cnt  varchar2(20); begin    execute immediate 'select count(1) from emp'      into l_cnt;    dbms_output.put_line(l_cnt); end;

動態調用常式.常式中用到的綁定變數參數必須指定參數類型.
黓認為IN類型,其它類型必須顯式指定
Sql代碼

declare    l_routin    varchar2(100) := 'gen2161.get_rowcnt';    l_tblnam    varchar2(20) := 'emp';    l_cnt       number;    l_status    varchar2(200); begin    execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'      using in l_tblnam, out l_cnt, in out l_status;   if l_status != 'OK' then       dbms_output.put_line('error');    end if; end;

將傳回值傳遞到PL/SQL記錄類型;同樣也可用%rowtype變數
Sql代碼

declare    type empdtlrec is record (empno   number(4),                             ename   varchar2(20),                             deptno   number(2));    empdtl empdtlrec; begin    execute immediate 'select empno, ename, deptno ' ||                     'from emp where empno = 7934'      into empdtl; end;

傳遞並檢索值.INTO子句用在USING子句前
Sql代碼

declare    l_dept     pls_integer := 20;    l_nam      varchar2(20);    l_loc      varchar2(20); begin    execute immediate 'select dname, loc from dept where deptno = :1'      into l_nam, l_loc      using l_dept ; end;

多行查詢選項.對此選項用insert語句填充暫存資料表，
用暫存資料表進行進一步的處理,也可以用REF cursors糾正此缺憾.
Sql代碼

declare    l_sal  pls_integer := 2000; begin    execute immediate 'insert into temp(empno, ename) '||'select empno, ename from emp '||'where sal > :1'      using l_sal;    commit; end;

    對於處理動態語句,EXECUTE IMMEDIATE 比以前可能用到的更容易並且更高效. 

當意圖執行動態語句時，適當地處理異常更加重要.應該關注於捕獲所有可能的異常.15:40 | 添加評論 | 固定連結 | 寫入日誌 | SQLoracle中insert into select的用法
/* Formatted on 2008/06/02 15:37 (Formatter Plus v4.8.7) */
INSERT INTO t_work
(f_recid, f_jobid, f_ruleid, f_jobtype, f_operid, f_opername,
f_b?time, f_assigntime, f_downtime, f_worktimes, f_finishtime)
SELECT SYS_GUID () AS f_recid, f_jobid, f_ruleid, f_jobtype, f_operid,
f_opername, f_b?time, f_assigntime,f_downtime,
DECODE (f_downtime, null,1, f_downtime + 1) as f_worktimes,
TO_CHAR (SYSDATE, ‘yyyy-MM-dd hh:mm:ss’) AS finishtime
FROM t_b?fer
WHERE f_jobid = ‘21’
說明將t_b?fer中的一條記錄添加到t_work表中。
其中b_b?fer中沒有的列有f_recid和t_finishtime;
f_worktimes需要加1。
SYS_GUID()的意思是產生一個32位的永不重複的字串。
DECODE(f_downtime, null,1, f_downtime + 1)的意思是如果f_downtime為空白（null）得到的值為1,否則為f_downtime+1。
TO_CHAR(SYSDATE, ‘yyyy-MM-dd hh:mm:ss’)為格式化時間格式;在表t_work中f_finishtime的資料類型為varchar2.
15:22 | 添加評論 | 固定連結 | 寫入日誌 | SQLPL/SQL開發中動態SQL的使用方法
原文來自：http://blog.chinaunix.net/u/19673/showart_272022.html

一般的PL/SQL程式設計中，在DML和事務控制的語句中可以直接使用SQL，但是DDL語句及系統控制語句卻不能在PL/SQL中直接使用，要想實現在PL/SQL中使用DDL語句及系統控制語句，可以通過使用動態SQL來實現。
　　首先我們應該瞭解什麼是動態SQL，在Oracle資料庫開發PL/SQL塊中我們使用的SQL分為：靜態SQL語句和動態SQL語句。所謂靜態SQL 指在PL/SQL塊中使用的SQL語句在編譯時間是明確的，執行的是確定對象。而動態SQL是指在PL/SQL塊編譯時間SQL語句是不確定的，如根據使用者輸入的參數的不同而執行不同的操作。編譯器對動態語句部分不進行處理，只是在程式運行時動態地建立語句、對語句進行文法分析並執行該語句。
　　Oracle中動態SQL可以通過本地動態SQL來執行，也可以通過DBMS_SQL包來執行。下面就這兩種情況分別進行說明：
　　一、本地動態SQL
　　本地動態SQL是使用EXECUTE IMMEDIATE語句來實現的。
　　1、本地動態SQL執行DDL語句：
　　需求：根據使用者輸入的表名及欄位名等參數動態建表。

create or replace procedure proc_test(table_name in varchar2, --表名field1 in varchar2, --欄位名datatype1 in varchar2, --欄位類型field2 in varchar2, --欄位名datatype2 in varchar2 --欄位類型) asstr_sql varchar2(500);beginstr_sql:=’create table ’||table_name||’(’||field1||’ ’||datatype1||’,’||field2||’ ’||datatype2||’)’;execute immediate str_sql; --動態執行DDL語句exceptionwhen others thennull;end ;

　　以上是編譯通過的預存程序代碼。下面執行預存程序動態建表。

SQL> execute proc_test(’dinya_test’,’id’,’number(8) not null’,’name’,’varchar2(100)’);PL/SQL procedure s?ssfully completedSQL> desc dinya_test;Name Type Nullable Default Comments ---- ------------- -------- ------- -------- ID NUMBER(8)NAME VARCHAR2(100) YSQL>

　　到這裡，就實現了我們的需求，使用本地動態SQL根據使用者輸入的表名及欄位名、欄位類型等參數來實現動態執行DDL語句。
　　2、本地動態SQL執行DML語句。
　　需求：將使用者輸入的值插入到上例中建好的dinya_test表中。

create or replace procedure proc_insert(id in number, --輸入序號name in varchar2 --輸入姓名) asstr_sql varchar2(500);beginstr_sql:=’insert into dinya_test val?s(:1,:2)’;execute immediate str_sql using id,name; --動態執行插入操作exceptionwhen others thennull;end ;

　　執行預存程序，插入資料到測試表中。

SQL> execute proc_insert(1,’dinya’);PL/SQL procedure s?ssfully completedSQL> select * from dinya_test;ID NAME1 dinya

　　在上例中，本地動態SQL執行DML語句時使用了using子句，按順序將輸入的值綁定到變數，如果需要輸出參數，可以在執行動態SQL的時候，使用RETURNING INTO 子句，如：

declarep_id number:=1;v_count number;beginv_string:=’select count(*) from table_name a where a.id=:id’;execute immediate v_string into v_count using p_id; end ;

　　更多的關於動態SQL中關於傳回值及為輸出輸入綁定變數執行參數模式的問題，請讀者自行做測試。
　　二、使用DBMS_SQL包
　　使用DBMS_SQL包實現動態SQL的步驟如下：A、先將要執行的SQL語句或一個語句塊放到一個字串變數中。B、使用DBMS_SQL包的 parse過程來分析該字串。C、使用DBMS_SQL包的bind_variable過程來綁定變數。D、使用DBMS_SQL包的execute函數來執行語句。
　　1、使用DBMS_SQL包執行DDL語句
　　需求：使用DBMS_SQL包根據使用者輸入的表名、欄位名及欄位類型建表。

create or replace procedure proc_dbms_sql(table_name in varchar2, --表名field_name1 in varchar2, --欄位名datatype1 in varchar2, --欄位類型field_name2 in varchar2, --欄位名datatype2 in varchar2 --欄位類型)asv_cursor number; --定義游標v_string varchar2(200); --定義字串變數v_row number; --行數beginv_cursor:=dbms_sql.open_cursor; --為處理開啟游標v_string:=’create table ’||table_name||’(’||field_name1||’ ’||datatype1||’,’||field_name2||’ ’||datatype2||’)’;dbms_sql.parse(v_cursor,v_string,dbms_sql.native); --分析語句v_row:=dbms_sql.execute(v_cursor); --執行語句dbms_sql.close_cursor(v_cursor); --關閉游標exceptionwhen others thendbms_sql.close_cursor(v_cursor); --關閉游標raise;end;

　　以上過程編譯通過後，執行過程建立表結構：

SQL> execute proc_dbms_sql(’dinya_test2’,’id’,’number(8) not null’,’name’,’varchar2(100)’);PL/SQL procedure s?ssfully completedSQL> desc dinya_test2;Name Type Nullable Default Comments ---- ------------- -------- ------- -------- ID NUMBER(8) NAME VARCHAR2(100) Y SQL>

　　2、使用DBMS_SQL包執行DML語句
　　需求：使用DBMS_SQL包根據使用者輸入的值更新表中相對應的記錄。
　　查看錶中已有記錄：

SQL> select * from dinya_test2;ID NAME1 Oracle2 CSDN3 ERPSQL>

　　建預存程序，並編譯通過：

create or replace procedure proc_dbms_sql_update(id number,name varchar2)asv_cursor number; --定義游標v_string varchar2(200); --字串變數v_row number; --行數beginv_cursor:=dbms_sql.open_cursor; --為處理開啟游標v_string:=’update dinya_test2 a set a.name=:p_name where a.id=:p_id’;dbms_sql.parse(v_cursor,v_string,dbms_sql.native); --分析語句dbms_sql.bind_variable(v_cursor,’:p_name’,name); --綁定變數dbms_sql.bind_variable(v_cursor,’:p_id’,id); --綁定變數v_row:=dbms_sql.execute(v_cursor);　　　　　　　　　　　--執行動態ＳＱＬdbms_sql.close_cursor(v_cursor); --關閉游標exceptionwhen others thendbms_sql.close_cursor(v_cursor); --關閉游標raise;end;

　　執行過程，根據使用者輸入的參數更新表中的資料：

SQL> execute proc_dbms_sql_update(2,’csdn_dinya’);PL/SQL procedure s?ssfully completedSQL> select * from dinya_test2;ID NAME1 Oracle2 csdn_dinya3 ERPSQL>

　　執行過程後將第二條的name欄位的資料更新為新值csdn_dinya。這樣就完成了使用dbms_sql包來執行DML語句的功能。
　　使用DBMS_SQL中，如果要執行的動態語句不是查詢語句，使用DBMS_SQL.Execute或 DBMS_SQL.Variable_Val?來執行，如果要執行動態語句是查詢語句，則要使用DBMS_SQL.define_column定義輸出變數，然後使用DBMS_SQL.Execute, DBMS_SQL.Fetch_Rows, DBMS_SQL.Column_Val?及DBMS_SQL.Variable_Val?來執行查詢並得到結果。
　　總結說明：

　　在Oracle開發過程中，我們可以使用動態SQL來執行DDL語句、DML語句、事務控制語句及系統控制語句。但是需要注意的是，PL/SQL塊中使用動態SQL執行DDL語句的時候與別的不同，在DDL中使用綁定變數是非法的（bind_variable(v_cursor,’:p_name’, name)），分析後不需要執行DBMS_SQL.Bind_Variable，直接將輸入的變數加到字串中即可。另外，DDL是在調用 DBMS_SQL.PARSE時執行的，所以DBMS_SQL.EXECUTE也可以不用，即在上例中的v_row:=dbms_sql.execute (v_cursor)部分可以不要。
15:14 | 添加評論 | 固定連結 | 寫入日誌 | SQL11月17日
ResultSet大全
開發的朋友，在操作資料庫的時候，相信絕大部分人都用過它。但它不僅僅就是一個結果集的純載體，僅僅只有儲存的功能。下面就簡略說一下它的功能。
　　 結果集大體上可以分為四類，它們各有特點，基本上都和Statement語句的建立方式有關。
　　 1、基本型（最基本，用得最廣泛）
　　 建立方式：無參數建立型
　　 Statement st = conn.CreateStatement
　　 ResultSet rs = Statement.excuteQ?ry(sqlStr);
　　 特點：這種最簡單，沒有特別的功能（不支援滾動、更新等等），只能用next()逐個單方向去讀取資料。
　　 2、滾動型
　　 建立方式：參數建立型
　　 Statement st = conn.createStatement(int resultSetType, int resultSetConcurrency)
　　 ResultSet rs = st.executeQ?ry(sqlStr)
　　 參數：
　　 resultSetType是設定ResultSet對象的類型可滾動
　　 resultSetConcurency是設定ResultSet對象能夠修改的
　　 具體的參數值見ResultSet.
　　 特點：（和具體參數的設定有關。）這種類型支援滾動擷取記錄，可以向前向後操作，類似於分頁功能的操作方式。如支援：next()、previous()、first()、absolute(int n)等等。
　　 3、更新型
　　 建立方式：（和2一樣，不過參數選取不同）
　　 Statement st = createstatement(Result.TYPE_SCROLL_INSENSITIVE,Result.CONCUR_UPDATABLE)
　　 特點：可以完成對資料庫的更新操作。更新的方法是，把ResultSet的遊標移動到你要更新的行，然後調用updateXXX()，再用updateRow()完成對資料庫的寫入。
　　 限制/要求：（1）對單表操作。（2）不包含join或者group by子句。
　　 4、保持型
　　 建立方式：
　　 Statement st=createStatement(int resultsetscrollable,int resultsetupdateable,int resultsetSetHoldability)
　　 ResultSet rs = st.excuteQ?ry(sqlStr);
　　 參數：只介紹第三個resultsetSetHoldability，表示在結果集提交後結果集是否開啟，其值為
　　 ResultSet.HOLD_CURSORS_OVER_COMMIT:表示修改提交時，不關閉資料庫。
　　 ResultSet.CLOSE_CURSORS_AT_COMMIT：表示修改提交時ResultSet關閉
　　 特點：一般情況下，在使用Statement執行完一個查詢，又去執行另一個查詢的時候，第一個查詢的結果集就會被關閉。該方式可以自由設定是否關閉結果集。
　　 限制/要求：（1）只是在JDBC3.0的驅動下才能成立。

可更新的結果集：

更新一行：

UpdateXXX（）è cancelRowUpdates（）èupdateRow（）èrowUpdated（）。

UpadateXXX（）方法只能修改當前行的資料，並不能修改資料庫中的資料，所以在調用updateXXX（）後，還要調用updateRow（）方法，用當前行中的新資料更新資料庫。

CancelRowUpdates（）方法用來放棄對當前行的修改，注意，要讓這個方法有效，必須在調用updteRow（）方法之前調用它。

rowUpdated（）方法來判斷當前行是否被更新。

插入行：

moveToInsertRow（）方法移動遊標到插入行，插入行是一個與可更新的結果集相聯絡的特殊的緩衝行。

moveToInsertRow（）èupdateXXX（）ègetXXX（）èinsertRow（）èrowInserted（）

insertRow（）將新行傳遞給資料庫，從而在新資料庫中真正插入一行資料

rowInserted（）方法來判斷當前行是否是插入行。

刪除一行：

deletRow（）從結果集中和資料庫中刪除一行，當遊標指向插入行的時候，不能調用這個方法。一個被刪除的行可能在結果集中留下一個空的位置，可以調用 rowDeleted（）方法來判斷一行是否被刪除。

可更新結果集的使用必須滿足下面3個條件：

1， 只能是針對資料庫中單張表單的查詢

2， 查詢語句中不能包含任何的join操作。

3， 查詢操作的表中必須有主鍵，而且在查詢的結果集中必須包含作為主鍵的欄位。

結果集中執行插入操作，還應該滿足下面兩個條件

1， 查詢操作必須選擇資料庫表中所有不可為空的列

2， 查詢操作必須選擇所有沒有預設值的列。

判斷ResultSet是否為空白

ResultSet rs=stmt.executeQ?ry(sql);
rs == null;
rs.size()這個方法沒有的
rs為ResultSet的引用，即使結果集裡有0條記錄，rs也不為空白，所以用rs == null來判斷結果集裡是否有0條記錄是錯誤的，而要用 rs.getRow(); 為0則表示沒有記錄–使用的時候也有問題
使用rs.next()是否為tr?來判斷，會使遊標前移一位，可能丟掉一條記錄，不合適
boolean hasRows = false;
下面的方法較合適
while (rs.next()){
hasRows = tr?;
//Do your st?f
}
if (!hasRows ){
//Empty result set
}
//You dont have to test for null result set
19:50 | 添加評論 | 固定連結 | 寫入日誌 | Java10月14日
把本地硬碟裡的圖片儲存到oracle資料庫裡blob的實現
轉自：黑色頭髮

http://heiseto?a.javaeye.com
資料庫是oracle，欄位是blob,代碼語言是java，上傳的可以是圖片也可以是其他大檔案，顯示在瀏覽器裡和儲存在硬碟上不寫在這篇文章裡，寫在下篇裡

import java.io.B?feredInputStream;import java.io.FileInputStream;import java.io.OutputStream;import java.sql.Blob;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import javax.swing.JFileChooser;import oracle.jdbc.driver.OracleConnection;p lic class Write{ p lic static void main(String[] args) {  try {   Writewatdb = new Write();   watdb.sendToDb();  } catch (SQLException e) {   e.printStackTrace();  } catch (ClassNotFoundException e) {   e.printStackTrace();  } } Connection conn; Statement stmt; ResultSet rs; int b?ferSize; p lic Write() throws SQLException, ClassNotFoundException  {  String connectString;  Class.forName("oracle.jdbc.driver.OracleDriver");  connectString = "jdbc:oracle:thin:@192.168.1.134:1521:zhpt";  conn = (OracleConnection)  DriverManager.getConnection(connectString, "dzjc","dzjc");  stmt = conn.createStatement(); } p lic void sendToDb()  {        int amount = 0;  OutputStream out = null;  B?feredInputStream in = null;  JFileChooser chooser = new JFileChooser();  int returnVal = chooser.showOpenDialog(null);  if (returnVal == JFileChooser.APPROVE_OPTION)  {   pathname = chooser.getSelectedFile().getAbsolutePath();   name = chooser.getSelectedFile().getName();   chooser = null;  }  else  {   System.out.println("沒有找到檔案。");   System.out.println("程式非正常結束...");   return;  }  try  {   conn.setAutoCommit(false);   stmt.executeUpdate("insert into dzjc_img(img) val?s (empty_blob())");   rs = stmt.executeQ?ry("select img from dzjc_img for update");   if (rs.next())    {    Blob blob = rs.getBlob("img");    out = ( (oracle.sql.BLOB) blob).getBinaryOutputStream();    b?ferSize = ( (oracle.sql.BLOB) blob).getB?ferSize();    in = new B?feredInputStream(new FileInputStream(pathname), b?ferSize);    byte[] b = new byte[b?ferSize];    int count = in.read(b, 0, b?ferSize);    while (count != -1)     {     out.write(b, 0, count);     amount += count;     System.out.println("處理了" + amount + "位元組.");     count = in.read(b, 0, b?ferSize);     System.out.println("處理了" + amount + "位元組,成功.");    }    out.close();    out = null;    in.close();    in = null;    conn.commit();   }  }  catch (Exception e)   {   e.printStackTrace();   try    {    conn.rollback();   }   catch (Exception ignored) {}  }  finally   {   if (out!=null) try{out.close();} catch(Exception igored){}   if (in!=null) try{in.close();} catch(Exception igored){}  } }}

可直接運行

資料庫就自己建吧，建個類型為blob型的就行了
18:24 | 添加評論 | 固定連結 | 寫入日誌 | Java把圖片從oracle資料庫的blob欄位中取出來，存在硬碟上
轉：

import java.io.B?feredOutputStream;import java.io.FileOutputStream;import java.io.InputStream;import java.sql.Blob;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import javax.swing.JFileChooser;import oracle.jdbc.driver.OracleConnection;p lic class Read { p lic static void main(String[] args) {  try {   Read rfdb = new Read ();   rfdb.readFromDB();  } catch (SQLException e) {   e.printStackTrace();  } catch (ClassNotFoundException e) {   e.printStackTrace();  } } Connection conn; Statement stmt; ResultSet rs; int b?ferSize; p lic Read ()throws SQLException, ClassNotFoundException  {  String connectString;  Class.forName("oracle.jdbc.driver.OracleDriver");  connectString = "jdbc:oracle:thin:@192.168.1.134:1521:zhpt";  conn = (OracleConnection)  DriverManager.getConnection(connectString, "dzjc","dzjc");  stmt = conn.createStatement(); } p lic void readFromDB()  {  String pathname, name;  int amount = 0;  InputStream in = null;  B?feredOutputStream out = null;  JFileChooser chooser = new JFileChooser();  int returnVal = chooser.showOpenDialog(null);  if (returnVal == JFileChooser.APPROVE_OPTION)   {   pathname = chooser.getSelectedFile().getAbsolutePath();   name = chooser.getSelectedFile().getName();   chooser =