sql動態查詢

『壹』 PL/sql開發中動態SQL的使用方法

內容摘要 在PL/SQL開發過程中 使用SQL PL/SQL可禪仔以實現大部份的需求 但是在某些特殊的情況下 在PL/SQL中使用標準的SQL語句或DML語句不能實現自己的需求 比如需要動態建表或某個不確定的操作需要動態正宏執行 這就需要使用動態SQL來實現 本文通過幾個實例來詳細的講解動態SQL的使用 本文適宜讀者范圍 Oracle初級 中級 系統環境 OS windows Professional (英文版)Oracle 正文 一般的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語句來實現的 本地動態SQL執行DDL語句 需求 根據用戶輸入的表名及欄位名等參數動態建表 create or replace procere proc_test(table_name in varchar 表名field in varchar 欄位名datatype in varchar 欄位類型field in varchar 欄位名datatype in varchar 欄位類型) asstr_sql varchar ( );beginstr_sql:= create table ||table_name|| ( ||field || ||datatype || ||field || ||datatype || ) ;execute immediate str_sql; 動態執行DDL語句exceptionwhen others thennull;end ;以上是編譯通過的存儲過程代碼 下面執行存儲過程動態建表 SQL> execute proc_test( dinya_test id number( ) not null name varchar ( ) );PL/SQL procere successfully pletedSQL> desc dinya_test;Name TypeNullable Default Comments ID NUMBER( )NAME VARCHAR ( ) YSQL>到這里 就實現了我們的需求 使用本地動態SQL根據用戶輸入的表名及欄位名 欄位類型等參數來實現動態執行DDL語句 本地動態SQL執行DML語句 需求 將用戶輸入的值插入到上例中建好的dinya_test表中 create or replace procere proc_insert(id in number 輸入序號name in varchar 輸入姓名) asstr_sql varchar ( );beginstr_sql:= insert into dinya_test values(: : ) ;execute immediate str_sql using id name; 動態執行插入操作exceptionwhen others thennull;end ;執行存儲過程 插入數據到測試表中 SQL> execute proc_insert( dinya );PL/SQL procere successfully pletedSQL> select * from dinya_test;IDNAME dinya在上例中 本地動態SQL執行DML語句時使用了using子句 按順序將輸入的值綁定到變數 如果需要輸出參數 可以在執行動態SQL的時候 使用RETURNING INTO 子句 如 declarep_id number:= ;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函數來執行語句 使用DBMS_SQL包執行DDL語句 需求 使用DBMS_SQL包根據用戶輸入的表名 欄位名及欄位類型建表 create or replace procere proc_dbms_sql(table_name in varchar 表名field_name in varchar 欄位名datatype in varchar 欄位類型field_name in varchar 欄位名datatype in varchar 欄位類型)asv_cursor number; 定義游標v_string varchar ( ); 定義字元串變數v_row number; 行數beginv_cursor:=dbms_sql open_cursor; 為處理打開游標v_string:= create table ||table_name|| ( ||field_name || ||datatype || ||field_name || ||datatype || ) ;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_test id number( ) not null name varchar ( ) );PL/SQL procere successfully pletedSQL> desc dinya_test ;Name TypeNullable Default Comments ID NUMBER( )NAME VARCHAR ( ) YSQL> 使用DBMS_SQL包執行DML語句 需求 使用DBMS_SQL包根據用戶輸入的值更新表中相對應的記錄 查看錶中已有記錄 SQL> select * from dinya_test ;ID NAME Oracle CSDN ERPSQL>建存儲過程 並編譯通過 create or replace procere proc_dbms_sql_update(id number name varchar )asv_cursor number; 定義游標v_string varchar ( ); 字元串變數v_row number; 行數beginv_cursor:=dbms_sql open_cursor; 為處理打開游標v_string:= update dinya_test 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); 執行動態SQLdbms_sql close_cursor(v_cursor); 關閉游標exceptionwhen others thendbms_sql close_cursor(v_cursor); 關閉游標raise;end;執行過程 根據用戶輸入的參數更新表中的數據 SQL> execute proc_dbms_sql_update( csdn_dinya );PL/SQL procere successfully pletedSQL> select * from dinya_test ;ID NAME Oracle csdn_dinya ERPSQL>執行過程後將第二條的name欄位的數據更新為新值csdn_dinya 這樣就完成了使用dbms_sql包來執行DML語句的功能 使用DBMS_SQL中 如果要執行的動態語句不是查詢語句 使用DBMS_SQL Execute或DBMS_SQL Variable_Value來執行 如果要執行動態語句是查詢語句 則要使用DBMS_SQL define_column定義輸出變數 然後使用DBMS_SQL Execute DBMS_SQL Fetch_Rows DBMS_SQL Column_Value及DBMS_SQL Variable_Value來執行查詢並得到結果 總結說明 在Oracle開發過程中 我們可以使用動態SQL來執行DDL語句 DML語句 事務控制語句及系統控制語句 但是需要注意的是 PL/SQL塊中使用動態SQL執行DDL語句的時候與別的不同 在DDL中使用綁定變數是非法的（bind_variable(v_cursor :p_name name)） 分析後不需要執行DBMS_SQL Bind_Variable 直接將輸入的變數加到字元串中即可 另外 DDL是在調用DBMS_SQL lishixin/Article/program/SQLServer/201311/22089

『貳』 動態SQL的使用

在介紹動態SQL前我們先看看什麼是靜態SQL

靜態SQL

靜態 SQL 語句一般用於嵌入式 SQL 應用中，在程序運行前，SQL 語句必須是確定的，例如 SQL 語句中涉及的列名和表名必須是存在的。靜態 SQL 語句的編譯是在應用程序運行前進行的，編譯的結果會存儲在資料庫內部。而後程序運行時，資料庫將直接執行編譯好的 SQL 語句，降低運行時的開銷。

動態SQL

動態 SQL 語句是在應用程序運行時被編譯和執行的，例如，使用 DB2 的互動式工具 CLP 訪問資料庫時，用戶輸入的 SQL 語句是不確定的，因此 SQL 語句只能被動態地編譯。動態 SQL 的應用較多，常見的 CLI 和 JDBC 應用程序都使用動態 SQL。

動態SQL作用

動態SQL執行方法

使用EXEC(EXECUTE的縮寫)命令和使用SP_EXECUTERSQL。

EXEC命令執行

語法

註：EXECUTE 命令有兩個用途，一個是用來執行存儲過程，另一個是執行動態SQL

不帶參數示例

在變數@SQL中保存了一個字元串，該字元串中包含一條查詢語句，再用EXEC調用保存在變數中的批處理代碼，我們可以這樣寫SQL：

EXEC ('SELECT * FROM Customers')

結果如下：

與我們直接執行SELECT * FROM Customers一樣。

帶參數示例

還是上面的示例，我們換一種寫法

DECLARE @SQL AS VARCHAR(100);

DECLARE @Column AS VARCHAR(20);

SET @Column = '姓名'

SET @SQL = 'SELECT ' + @Column + ' FROM Customers'

EXEC (@SQL)

結果如下：

SP_EXECUTERSQL執行

語法

注意：SP_EXECUTERSQL是繼EXEC後另一種執行動態SQL的方法。使用這個存儲過程更加安全和靈活，因為它支持輸入和輸出參數。注意的是，與EXEC不同的是,SP_EXECUTERSQL只支持使用Unicode字元串作為其輸入的批處理代碼。

示例

構造了一個對Customers表進行查詢的批處理代碼，在其查詢過濾條件中使用一個輸入參數@CusID

DECLARE @SQL AS NVARCHAR(100);

SET @SQL=N'SELECT * FROM Customers

WHERE 客戶ID=@CusID;'

EXEC SP_EXECUTESQL

@STMT=@SQL,

@PARMS=N'@CusID AS INT',

@CusID=1;

結果如下：

代碼中將輸入參數取值指定為1，但即使採用不同的值在運行這段代碼，代碼字元串仍然保存相同。這樣就可以增加重用以前緩存過的執行計劃的機會

『叄』 動態實現sql查詢條件替換

<select id="queryEmp" resultType="cn.test.entity.Emp">
select * from emp where 1=1
<if test="deptNo!=null">
and deptno=#{deptNO}
</if>
<if test="deptName!=null">
and deptno=#{deptName}
</if>
</select>

註：<if test="deptNo!=null">中 的deptNo是指實體類中的屬性或欄位；
choose：
<select id="queryEmp" resultType="cn.test.entity.Emp">
select * from emp where 1=1
<choose>
<when test="deptNo!=null">
and deptno=#{deptNo}
</when>
<when test="deptName!=null">
and deptname=#{deptName}
</when>
<otherwise>
and personnum>#{personNum}
</otherwise>
</choose>
</select>

『肆』 動態SQL是什麼什麼是靜態SQL，動態SQL的動態體現在哪裡

首先，所謂SQL的動態和靜態，是指SQL語句在何時被編譯和執行，二者都是用在SQL嵌入式編程中的，這里所說的嵌入式是指將SQL語句嵌入在高級語言中，而不是針對於單片機的那種嵌入式編程。
在某種高級語言中，如果嵌入了SQL語句，而這個SQL語句的主體結構已經明確，例如在Java的一段代碼中有一個待執行的SQL「select * from t1 where c1>5」，在Java編譯階段，就可以將這段SQL交給資料庫管理系統去分析，資料庫軟體可以對這段SQL進行語法解析，生成資料庫方面的可執行代碼，這樣的SQL稱為靜態SQL，即在編譯階段就可以確定資料庫要做什麼事情。
而如果嵌入的SQL沒有明確給出，如在Java中定義了一個字元串類型的變數sql：String sql;，然後採用preparedStatement對象的execute方法去執行這個sql，該sql的值可能等於從文本框中讀取的一個SQL或者從鍵盤輸入的SQL，但具體是什麼，在編譯時無法確定，只有等到程序運行起來，在執行的過程中才能確定，這種SQL叫做動態SQL。例如每一種資料庫軟體都有能夠執行SQL語句的界面，那個界面接收的SQL就是動態SQL，因為資料庫廠商在做這個界面時，並不知道用戶會輸入哪些SQL，只有在該界面執行後，接收了用戶的實際輸入，才知道SQL是什麼。
另外還要注意一點，在SQL中如果某些參數沒有確定，如"select * from t1 where c1>? and c2<?"，這種語句是靜態SQL，不是動態SQL，雖然個別參數的值不知道，但整個SQL的結構已經確定，資料庫是可以將它編譯的，在執行階段只需將個別參數的值補充進來即可。

『伍』 如何用SQL代碼將動態查詢結果賦值給變數

1、首先最基本的賦值就是默認值了。