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

『貳』 求助db2如何執行動態SQL 並返回值

求助db2如何執行動態SQL 並返回值
大多數情況下，DB2資料庫管理程序在需要鎖時會隱式地獲取它們，事務不需要顯式地請求鎖，唯一有可能被事務顯式鎖定的資料庫對象就是表，可以使用下邊的語句。\r\nALTER TABLE [TableName] LOCKSIZE TABLE\r\n或\r\nLOCK TABLE [TableName] IN [SHARE|EXCLUSIVE] MODE

『叄』 sql動態查詢返回值的意義是什麼呢

@name只是返回一個標量值，它的有無並不影響sp那個擴展存儲過程的執行
如果你這個執行後，無論是否得到一個表集，都會將@name這個標量值輸出
當然需要在程序中額外的方法接收
當然如果你不想用這種方式，你就想用表集的形式接收也可以
sql語句拼接時，直接select name from即可，最後一句select @out_name去掉就可以了
沒有@name變數，相應的接收變數@out_name都去掉就好

這個sp_executesql存儲過程的輸出參數主要是為了解決拼接sql語句時，不能將拼接為字元的變數定義或輸出到外面的問題，舉個簡單的例子

比如你要查詢一個表的數量，那麼where條件是動態的，這時就必須使用動態拼接sql語句
而查詢得到的這個數量你需要賦值到一個變數中，用來進一步的計算或使用
這時，你用exec 執行的結果如何賦值到變數中呢？答案就是圖中所示的方法

『肆』 動態執行sql語句是否有返回值可以判斷是否成功

返回執行的上一個 Transact-SQL 語句的錯誤號。

Transact-SQL 語法約定

語法

@@ERROR

返回類型
integer

備注
如果前一個 Transact-SQL 語句執行沒有錯誤，則返回 0。

如果前一個語句遇到錯誤，則返回錯誤號。如果錯誤是 sys.messages 目錄視圖中的錯誤之一，則 @@ERROR 將包含 sys.messages.message_id 列中表示該錯誤的值。可以在 sys.messages 中查看與 @@ERROR 錯誤號相關的文本信息。

由於 @@ERROR 在每一條語句執行後被清除並且重置，因此應在語句驗證後立即查看它，或將其保存到一個局部變數中以備以後查看。

使用 TRY...CATCH 構造來處理錯誤。TRY...CATCH 構造也支持其他返回的錯誤信息多於 @@ERROR 的系統函數（ERROR_LINE、ERROR_MESSAGE、ERROR_PROCEDURE、ERROR_SEVERITY 和 ERROR_STATE）。TRY...CATCH 也支持 ERROR_NUMBER 函數，但不限制該函數在語句產生錯誤後立即在語句中返回錯誤號。有關詳細信息，請參閱 TRY...CATCH (Transact-SQL)。

示例
A. 用 @@ERROR 檢測一個特定錯誤
以下示例用 @@ERROR 在 UPDATE 語句中檢測約束檢查沖突（錯誤 #547）。

復制代碼
USE AdventureWorks;
GO
UPDATE HumanResources.EmployeePayHistory
SET PayFrequency = 4
WHERE EmployeeID = 1;
IF @@ERROR = 547
PRINT N'A check constraint violation occurred.';
GO

B. 用 @@ERROR 有條件地退出一個過程
在此示例中，IF...ELSE 語句在存儲過程中的 INSERT 語句後檢測 @@ERROR。@@ERROR 變數的值將決定發送給調用程序的返回代碼，以指示此過程的成功與失敗。

復制代碼
USE AdventureWorks;
GO
-- Drop the procere if it already exists.
IF OBJECT_ID(N'HumanResources.usp_DeleteCandidate', N'P') IS NOT NULL
DROP PROCEDURE HumanResources.usp_DeleteCandidate;
GO
-- Create the procere.
CREATE PROCEDURE HumanResources.usp_DeleteCandidate
(
@CandidateID INT
)
AS
-- Execute the DELETE statement.
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = @CandidateID;
-- Test the error value.
IF @@ERROR <> 0
BEGIN
-- Return 99 to the calling program to indicate failure.
PRINT N'An error occurred deleting the candidate information.';
RETURN 99;
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
PRINT N'The job candidate has been deleted.';
RETURN 0;
END;
GO

C. 與 @@ROWCOUNT 一同使用 @@ERROR
下面的示例用 @@ERROR 和 @@ROWCOUNT 驗證一條 UPDATE 語句的操作。為任何可能出現的錯誤而檢驗 @@ERROR 的值，而用 @@ROWCOUNT 保證更新已成功應用於表中的某行。