一、?? 存儲過程簡介

Sql Server的存儲過程是一個被命名的存儲在服務器上的Transacation-Sql語句集合,是封裝重復性工作的一種方法,它支持用戶聲明的變量、條件執行和其他強大的編程功能。

存儲過程相對于其他的數據庫訪問方法有以下的優點：

?? （1）重復使用。存儲過程可以重復使用，從而可以減少數據庫開發人員的工作量。

??? （2）提高性能。存儲過程在創建的時候就進行了編譯，將來使用的時候不用再重新編譯。一般的SQL語句每執行一次就需要編譯一次，所以使用存儲過程提高了效率。

??? （3）減少網絡流量。存儲過程位于服務器上，調用的時候只需要傳遞存儲過程的名稱以及參數就可以了，因此降低了網絡傳輸的數據量。

??? （4）安全性。參數化的存儲過程可以防止SQL注入式的攻擊，而且可以將Grant、Deny以及Revoke權限應用于存儲過程。

??? 存儲過程一共分為了三類：用戶定義的存儲過程、擴展存儲過程以及系統存儲過程。

??? 其中，用戶定義的存儲過程又分為Transaction-SQL和CLR兩種類型。

??? Transaction-SQL 存儲過程是指保存的Transaction-SQL語句集合，可以接受和返回用戶提供的參數。

??? CLR存儲過程是指對.Net Framework公共語言運行時(CLR)方法的引用，可以接受和返回用戶提供的參數。他們在.Net Framework程序集中是作為類的公共靜態方法實現的。（本文就不作介紹了）

二、先建一個測試用的表? （很基礎的代碼有點基礎是可以看懂的）

???????

--創建測試books表 create table books (book_id int identity(1,1) primary key,book_name varchar(20),book_price float,book_auth varchar(10) ); --插入測試數據 insert into books (book_name,book_price,book_auth)values('論語',25.6,'孔子'),('天龍八部',25.6,'金庸'),('雪山飛狐',32.7,'金庸'),('平凡的世界',35.8,'路遙'),('史記',54.8,'司馬遷');

三、創建無參存儲過程有寫返回參數（返回結果集，至于為什么不使用游標返回，而是直接返回下面有介紹）

????????? sqlserver 創建存儲過程：

if (exists (select * from sys.objects where name = 'getAllBooks'))--判斷是否存在存儲過程drop proc getAllBooks -- 刪除 go create procedure getAllBooks(@rowcount INT OUTPUT) -- 創建存儲過程 as begin select * from books; SET @rowcount=@@rowcount end; go --調用,執行存儲過程 DECLARE @count INT EXECUTE getAllBooks @count OUTPUT PRINT @count

??????? java 代碼（使用jdbcTemplate獲取結果集）

public String StorageInfo(){String param2Value = (String) jdbcTemplate.execute(new CallableStatementCreator() {public CallableStatement createCallableStatement(Connection con) throws SQLException {String storedProc = "{CALL getAllBooks(?)}";// 調用的sqlCallableStatement cs = con.prepareCall(storedProc); // cs.setInt(1, 2);// 設置輸入參數的值 // cs.setString(2, "99");// 設置輸入參數的值cs.registerOutParameter(1, Types.JAVA_OBJECT);// 注冊輸出參數的類型return cs;}}, new CallableStatementCallback() {public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {cs.execute();//ResultSet rs = (ResultSet) cs.getObject(2);// 獲取游標一行的值ResultSet rs = cs.getResultSet(); // System.out.println("CallableStatementCallback-------------:"+cs);while(rs.next()){int id = rs.getInt(1);String book_name = rs.getString(2);String book_auth = rs.getString(4);System.out.println("id:"+id+" 書名："+book_name+" 作者："+book_auth);}return null;// 獲取輸出參數的值 }});System.out.println("天天-------------:"+param2Value);return param2Value;}

?

? 四、創建有參存儲過程沒寫返回參數（注意其中參數的區別，這個沒有寫返回out，sqlserver會自動返回）

??? sqlserver 創建有參存儲過程

--2.創建有參存儲過程 if exists(select * from sysobjects where name='getAllBooks') drop proc getAllBooks go --創建存儲過程輸入參數。 create proc getAllBooks @startId varchar(50) as begin(select * from books where book_id =@startId); end go declare @back varchar(2000) exec getAllBooks 2

??

java 實現? 注意：沒有注冊輸出參數

?

public String StorageInfo(){String param2Value = (String) jdbcTemplate.execute(new CallableStatementCreator() {public CallableStatement createCallableStatement(Connection con) throws SQLException {String storedProc = "{CALL getAllBooks(?)}";// 調用的sqlCallableStatement cs = con.prepareCall(storedProc);cs.setInt(1, 2);// 設置輸入參數的值 // cs.setString(2, "99");// 設置輸入參數的值 // cs.registerOutParameter(2, Types.JAVA_OBJECT);// 注冊輸出參數的類型return cs;}}, new CallableStatementCallback() {public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {cs.execute();//ResultSet rs = (ResultSet) cs.getObject(2);// 獲取游標一行的值ResultSet rs = cs.getResultSet(); // System.out.println("CallableStatementCallback-------------:"+cs);while(rs.next()){int id = rs.getInt(1);String book_name = rs.getString(2);String book_auth = rs.getString(4);System.out.println("id:"+id+" 書名："+book_name+" 作者："+book_auth);}return null;// 獲取輸出參數的值 }});System.out.println("天天-------------:"+param2Value);return param2Value;}

?

? 五、創建有參返回單個屬性值

--2.創建有參存儲過程 if exists(select * from sysobjects where name='getAllBooks') drop proc getAllBooks go --創建存儲過程輸入參數。 create proc getAllBooks @startId varchar(50), @data nvarchar(100) output as begin set @data = (select book_name from books where book_id =@startId);end go declare @back varchar(2000) exec getAllBooks 2 ,@back output select @back dat

?

? java 實現代碼

public String StorageInfo(){String param2Value = (String) jdbcTemplate.execute(new CallableStatementCreator() {public CallableStatement createCallableStatement(Connection con) throws SQLException {String storedProc = "{CALL getAllBooks(?,?)}";// 調用的sqlCallableStatement cs = con.prepareCall(storedProc);cs.setInt(1, 2);// 設置輸入參數的值 // cs.setString(2, "99");// 設置輸入參數的值cs.registerOutParameter(2, Types.VARCHAR);// 注冊輸出參數的類型return cs;}}, new CallableStatementCallback() {public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {cs.execute();//ResultSet rs = (ResultSet) cs.getObject(2);// 獲取游標一行的值ResultSet rs = cs.getResultSet();System.out.println("CallableStatementCallback-------------:"+cs.getString(2)); // while(rs.next()){ // int id = rs.getInt(1); // String book_name = rs.getString(2); // String book_auth = rs.getString(4); // System.out.println("id:"+id+" 書名："+book_name+" 作者："+book_auth); // }return null;// 獲取輸出參數的值 }});System.out.println("天天-------------:"+param2Value);return param2Value;}

?

六、下面來說說sqlserver 為什么不能返回游標

?? 這是我的代碼

--2.創建有參存儲過程 游標接收結果集if exists(select * from sysobjects where name='proc_find_stu') drop proc proc_find_stu go --創建存儲過程輸入參數。 create proc proc_find_stu @startId varchar(50), @data CURSOR VARYING OUTPUT as begin-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- print @overTimeHour; -- 1. 聲明游標: DECLARE CURSOR_PriceChangeRecord SET @data = CURSOR FORWARD_ONLY STATIC FOR (select * from books where book_id =@startId);OPEN @data return 1; end go declare @back CURSOR exec proc_find_stu 2,@back; select @back data

?

? 最開始頭兒給了一個在oracle上可以直接跑的存儲過程，在java程序里直接用jdbc來調用非常方便，沒什么問題。之后便是狂找資料，把oracle上用PL/SQL寫的存儲過程改寫成sqlserver上用Transact-SQL寫的存儲過程，改阿改，終于，在sqlserver的查詢分析器上可以執行了，本以為已經做到這一步了，在jdbc里直接調用還不是小菜一碟，沒想到呀，問題來了。
??? 頭兒給的這個存儲過程有一個輸出參數，是cursor（游標）型的，在jdbc里要用registerOutParameter這個方法在執行存儲過程之前注冊一下輸出參數類型， 對于oracle的jdbc驅動這個問題很好辦。直接寫registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);就OK了，看到oracle.jdbc.OracleTypes.CURSOR了吧，oracle正不錯，直接就給你提供了一個表示游標型的整型常量。整個調用過程如下：
???CallableStatement proc = null;
???proc = conn.prepareCall("{call PROC_FWMA_DATAARCHIVE}");
???proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
???proc.execute();???
可microsoft呢，根本沒有在它的jdbc驅動里提供類似的這樣一個整型常量（盡管sqlserver里的存儲過程是支持游標類型的輸出參數的），用標準jdbc里的數據類型Types.other也沒有，以運行，就拋異常了，什么mircosoft jdbc 不支持這樣的數據類型。咋辦呢，到處查資料呀，各種試建立臨時表等等之類然而卻沒啥用，最后想看看有沒有前輩碰到過這樣的問題，可是怎么碰到這種問題的人似乎很少呢，找了半天，終于看到外國人的論壇上有人提出和我一樣的問題，結果答案很打擊人，說是microsoft的jdbc就是不支持。可是這個輸出的結果是很重要的，那有沒有其他解決方法呢？再查sqlserver的聯機幫助，看到sqlserver的存儲過程還支持直接返回結果集，只要在存儲過程里寫select就可以了，哈哈，些個簡單的存儲過程，沒有輸出參數。再修改剛才段代碼為
???CallableStatement proc = null;
???proc = conn.prepareCall("{call PROC_FWMA_DATAARCHIVE}");??
???ResultSet rs = null;
???rs = proc.getResultSet();
很好，結果集就拿到了。問題解決了？NO，試驗用的是簡單的存儲過程，再用正式的那個復雜的帶事務操作的存儲過程，jdbc又傻了，如果執行存儲過程用的是execute()，它就只返回出結果集，而不能做存儲過程中的insert delete操作，如果用executeUpdate()執行，結果集就返回不出來了。
??? 在這個問題上整整卡了一天，后來不知道怎么想了想，突然發現在存儲過程中，我把要返回結果集的那句select是放在了事務操作的外面，會不會是這里有問題呢？馬上動手，把select塞到事務里面，再運行，終于OK了。
??? 猜測可能是如果將select放在事務外的話，它和事務是同級別的，如果用execute()執行的話，就只做了select，不做事務了。當然這只是猜測了，真的要弄明白，恐怕要寫email到微軟去問了，英語太差，就不丟人現眼了。哪位大牛幫忙問問？

?? 示例代碼：https://gitee.com/xdymemory00/sqlserver-CunChuGuoChengYujavaJiaoHu.git

?

轉載于:https://www.cnblogs.com/memoryXudy/p/7776910.html

總結

以上是生活随笔為你收集整理的SQLServer 的存储过程与java交互的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。