新公司网站建设方案,深圳教育 网站建设,东莞免费建站模板,济南 外贸网站建设一、 存储过程简介 Sql Server的存储过程是一个被命名的存储在服务器上的Transacation-Sql语句集合,是封装重复性工作的一种方法,它支持用户声明的变量、条件执行和其他强大的编程功能。 存储过程相对于其他的数据库访问方法有以下的优点#xff1a; #xff08;1#xff…一、 存储过程简介 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 rowcountrowcount
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;} 四、创建有参存储过程没写返回参数注意其中参数的区别这个没有写返回outsqlserver会自动返回 sqlserver 创建有参存储过程 --2.创建有参存储过程
if exists(select * from sysobjects where namegetAllBooks)
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 namegetAllBooks)
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 nameproc_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
