08 存储过程

/* 存储过程 Procedure 将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来, 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时, 只需调用execute,即可自动完成命令。 存储过程的优点 1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译， 而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时）， 可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 3.存储过程可以重复使用,可减少数据库开发人员的工作量 4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权 create proc proc_name @parameter data_type[=default] [output] with recompile|encryption for replication as sql_statement 参数：支持2100个参数，output指明为输出参数 with recompile 不缓存，重新运行需要重新编译 with encryption 加密存储过程文本 for replication 不能在订阅服务器上执行为复制创建的存储过程 */ 1、一些常用的系统存储过程 sp_databases 列出所有数据库 sp_helpdb 报告数据库信息 sp_renamedb 重命名数据库 sp_tables 当前环境下可查询的对象 sp_columns 某个表列的信息 sp_help 查看某个表的所有信息 sp_helpconstraint查看表约束 sp_helpindex 查看表索引 sp_stored_procedures 查看存储过程 sp_password 添加或修改登录密码 sp_helptext 显示对象文本 2、用户自定义存储过程 create proc pr01 @last int , @this int as declare @l int,@t int set @l=@last set @t=@this if @t>@l begin print '今天晚上去香格里拉逍遥一下！' end else if @t=@l begin print '今天晚上还是老实的回家吃饭！' end else print '这个日志没有办法过，钱啊前....' go pr01 8600, 9300 go pr01 9600 , 9600 go pr01 6900,6599.999 go --带输出参数的存储过程 drop proc pr02 go create proc pr02 @ba int,@ad int,@re int output as set @re=@ba+@ad if @re >8000 begin print '谢谢你关注我们公司！希望以后有合作的机会！'+ convert(varchar(10),@re) end else if @re between 3000 and 8000 begin print '请您明天到公司面试，祝你好运！' end else print '请明天直接到人事部报到！' go declare @res int exec pr02 6500,3500,@res output go /* 存储过程的一些应用 */ --根据条件查询数据 create proc proc_person @id int=1,@name varchar(50) output as select @name=name from person where id=@id go declare @name varchar(50) exec proc_person 10,@name output --exec proc_person default,@name output print @name --分页 页大小 pageSize,当前页currentPage，总页数pageCount，总记录数recordCount create proc proc_page$person @pageSize int, @currentPage int, @pageCount int output, @recordCount int output as select @recordCount=count(id) from person select @pageCount=ceiling(@recordCount*1.0/@pageSize) from person select top (@pageSize) * from person where id not in (select top ((@currentPage-1)*@pageSize) id from person) go declare @pageCount int,@recordCount int exec proc_page$person 3,3,@pageCount output,@recordCount output print @pageCount print @recordCount go drop procedure proc_page$person