黑马程序员---SQL进阶之事务、视图和存储过程

事务

事务指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)--也就是由多个sql语句组成，必须作为一个整体执行。

这些sql语句作为一个整体一起向系统提交，要么都执行、要么都不执行。

 

 语法步骤：

开始事务：BEGIN TRANSACTION

事务提交：COMMIT TRANSACTION

事务回滚：ROLLBACK TRANSACTION

 

判断某条语句执行是否出错：

全局变量@@ERROR；

@@ERROR只能判断当前一条T-SQL语句执行是否有错，为了判断事务中所有T-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

　　

视图

视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上。

视图在操作上和数据表没有什么区别，但两者的差异是其本质是不同:数据表是实际存储记录的地方，然而视图并不保存任何记录，它存储的实际上是查询语句。

相同的数据表，根据不同用户的不同需求，可以创建不同的视图（不同的查询语句）。

视图的优点：

筛选表中的行

防止未经许可的用户访问敏感数据

降低数据库的复杂程度

 

创建视图的语句

Create view view_name

　　as

　　　<select 语句>

使用视图语句(就像查询数据表一样)

select * from view_name

 

存储过程

存储过程类似于C#中的方法，它是SQL语句和控制流程语句的预编译集合。

存储过程存储在数据库中，可由程序通过一个调用执行，而且允许用户声明变量、编辑逻辑控制语句以及其他强大的编程功能。

存储过程可以包含逻辑控制语句和数据操作语句，它可以接收参数、输出参数、返回单个或多个结果集以及返回值。

存储过程在创建时在服务器上进行编译，执行起来比执行单个SQL语句快。

存储过程的优点：

执行速度更快 – 在数据库中保存的存储过程语句都是编译过的

允许模块化程序设计 – 类似方法的复用

提高系统安全性 – 防止SQL注入

减少网络流通量 – 只要传输 存储过程的名称

存储过程分为：系统存储过程和用户自定义存储过程

系统存储过程

由系统定义，存放在master数据库中

名称以“sp_”开头或”xp_”开头

例如：

系统存储过程	说明
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