黑马程序员---SQL进阶之事务、视图和存储过程
事务
语法步骤:
例:
BEGIN TRANSACTION /*--定义变量,用于累计事务执行过程中的错误--*/ DECLARE @errorSum INT SET @errorSum=0 --初始化为0,即无错误 /*--转账:张三的账户少1000元,李四的账户多1000元*/ UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='张三' SET @errorSum=@errorSum+@@error UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName='李四' SET @errorSum=@errorSum+@@error --累计是否有错误 If @errorSum>0 Begin rollback transaction select ‘失败’ End Else Begin commit transaction select ‘成功’ End
视图
存储过程类似于C#中的方法,它是SQL语句和控制流程语句的预编译集合。
存储过程存储在数据库中,可由程序通过一个调用执行,而且允许用户声明变量、编辑逻辑控制语句以及其他强大的编程功能。
存储过程可以包含逻辑控制语句和数据操作语句,它可以接收参数、输出参数、返回单个或多个结果集以及返回值。
存储过程在创建时在服务器上进行编译,执行起来比执行单个SQL语句快。
存储过程的优点:
执行速度更快 – 在数据库中保存的存储过程语句都是编译过的
允许模块化程序设计 – 类似方法的复用
提高系统安全性 – 防止SQL注入
减少网络流通量 – 只要传输 存储过程的名称
存储过程分为:系统存储过程和用户自定义存储过程
系统存储过程 |
说明 |
sp_databases |
列出服务器上的所有数据库。 |
sp_helpdb |
报告有关指定数据库或所有数据库的信息 |
sp_renamedb |
更改数据库的名称 |
sp_tables |
返回当前环境下可查询的对象的列表 |
sp_columns |
回某个表列的信息 |
sp_help |
查看某个表的所有信息 |
sp_helpconstraint |
查看某个表的约束 |
sp_helpindex |
查看某个表的索引 |
sp_stored_procedures |
列出当前环境中的所有存储过程。 |
sp_password |
添加或修改登录帐户的密码。 |
sp_helptext |
显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。 |
CREATE PROC[EDURE] 存储过程名
@参数1 数据类型 = 默认值 OUTPUT,
@参数n 数据类型 = 默认值 OUTPUT
AS
SQL语句
调用存储过程
EXEC 过程名 [参数]
下面一个例子是带输入输出参数的实现分页功能的存储过程:
create procedure [dbo].[proGetPageData2] –带输出参数的存储过程 @pageIndex int=1, @pageSize int=3, @pageCount int output, --总页数 @rowCount int output -- 总行数 as declare @sqlStr nvarchar(300),@sqlCount nvarchar(300) SET @sqlCount = 'SELECT @rowCount=COUNT(b_id),@pageCount=CEILING((COUNT(b_id)+0.0)/'+ CAST(@pageSize AS VARCHAR)+') FROM Book' print @sqlCount EXEC SP_EXECUTESQL @sqlCount,N'@rowCount INT OUTPUT,@pageCount INT OUTPUT',@rowCount OUTPUT,@pageCount OUTPUT set @sqlStr='select top '+str(@pageSize)+' * from Category where c_id not in(select top '+str((@pageIndex-1)*@pageSize)+' c_id from Category order by c_addtime)order by c_addtime' print @sqlStr EXEC(@sqlStr) declare @pc int declare @rc int exec [proGetPageData2] 1,3,@pc output,@rc output select @pc,@rc