第八讲2-TransactSQL语言应用课件

提纲存储过程第八讲2Transact-SQL语言应用要求：掌握存储过程的定义和调用方法，掌握事物基本概念。事务部份参考课本第七章安全控制事务一、存储过程

1.存储过程概述存储过程是一组已被编译在一起的，存储在服务器上的，执行某种功能的预编译SQL语句。存储过程可以：接受输入参数并以输出参数的形式将多个值返回至调用过程或批处理。包含执行数据库操作（包括调用其它过程）的编程语句。向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）他是一种封装重复任务操作的方法。使用存储过程的好处：1.允许模块化程序设计。2.允许更快执行。3.减少网络流量。4.可作为安全机制使用。一、存储过程

1.存储过程概述存储过程分为5类，分别是系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程，不同类型的存储过程具有不同的作用。本地存储过程：本地存储过程是指在每个用户自己的数据库中创建的存储过程，这种存储过程只针对用户创建的普通数据库，其前缀不能使用SP-前缀。在数据库实际开发中使用的都是本地存储过程。一、存储过程

2.创建存储过程，三种方法使用CREATEPROCEDURE在创建存储过程的时候需要考虑下列因素：1）存储过程可以参考表、视图、临时表中的数据。2）如果存储过程创建了临时表，那么该临时表只能用于该存储过程，并且当存储过程执行完毕时，临时表自动删除。3）在一个批处理中，CREATEPROCEDURE不能与其他的SQL语句混合使用4）在CREATEPROCEDURE语句的定义中，可以包含任意数量和类型的T-SQL语句，但不能使用下面的对象创建语句：CREATEDEFAULT、CREATEPROCEDURE、CREATERULE、CREATETRIGGER、CREATEVIEW5）存储过程可以嵌套使用，最多可以嵌套32层一、存储过程

2.创建存储过程，三种方法使用CREATEPROCEDURE语法格式：CREATEPROC[EDURE]procedure_name[;number]

[{@parameterdata_type}

[VARYING][=default][OUTPUT]

][,...n]

[WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASsql_statement[...n]

一、存储过程

2.创建存储过程，三种方法存储过程关键字procedure_name：新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。@parameter：过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。使用@符号作为第一个字符来指定参数名称。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。data_type：参数的数据类型。所有数据类型（包括text、ntext和image）均可以用作存储过程的参数。VARYING：指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。Default：参数的默认值。OUTPUT：表明参数是返回参数。

AS：指定过程要执行的操作。sql_statement：过程中要包含的任意数目和类型的Transact-SQL语句。但有一些限制，比如不能使用数据定义语句。N：是表示此过程可以包含多条Transact-SQL语句。一、存储过程

2.创建存储过程，三种方法存储过程创建示例例：下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使用任何参数。USEpubsIFEXISTS(SELECTnameFROMsysobjectsWHEREname='au_info_all'ANDtype='P')DROPPROCEDUREau_info_allGOCREATEPROCEDUREau_info_allASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_idGO例：USEpubsGOCREATEPROCEDUREshow-book-messageASSELECTtitle，notesFROMtitlesGO一、存储过程

3.调用存储过程语法：[[EXEC[UTE]]

{

[@return_status=]

{procedure_name[;number]|@procedure_name_var

}

[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]]

[,...n]

[WITHRECOMPILE]输入参数的使用①在调用存储过程时，如果需要传递参数，有两种方法：第一种方法是根据参数定义时的声明顺序为参数赋值，第二种方法是根据参数名称分别用赋值运算符赋值。一、存储过程3.调用存储过程输入参数的使用①在调用存储过程时，如果需要传递参数，有两种方法。(1)根据定义时的声明顺序为参数赋值的格式如下：Parameter-value1|DEFAULT[Parameter-value2|DEFAULT……]这种情况下，传递参数的顺序必须和定义时的参数顺序一致。对于使用默认值的参数，可以使用关键字DEFAULT说明。对于使用默认值的参数，也可将其省略不写，但省略的顺序只能是从左向右。(2)根据参数名称给输入参数赋值的格式如下：@Parameter_name1=[Parameter-value|DEFAULT][@Parameter_name2=[Parameter-value|DEFAULT]……]在这种格式下，参数名称和参数值一一对应，同时出现。所以参数的顺序可以任意排列。3.调用存储过程输入参数的使用在调用存储过程时，如果需要传递参数，有两种方法：第一种方法是根据参数定义时的声明顺序为参数赋值，第二种方法是根据参数名称分别用赋值运算符赋值。use学生课程goexecmyproc;1use学生课程godeclare@wchar(10)set@w='myproc;2'exec@w'95003','4',65gouse学生课程goexecmyproc;3@ssex='男',@sage=default,@sdept='cs'WITHRECOMPILEgo一、存储过程3.调用存储过程输出参数的使用存储过程可以把值返回到调用他的应用程序或者返回到有输出参数的客户机程序中，为了使用输出参数，必须在CREATEPROCEDURE语句和EXECUTE语句中指定关键字OUTPUT。例：USE学生课程GOCREATEPROCEDUREdbo.mathadd@m1int,@m2int,@resultintOUTPUTASSET@result=@m1+@m2GO例：USE学生课程GODECLARE@answerintEXECUTEmathadd8,12,@answerOUTPUTSELECT'Thisresultis:',@answer一、存储过程二、安全控制（了解）

1.安全控制概述非法使用数据库的情况①编写合法程序绕过DBMS及其授权机制②直接或编写应用程序执行非授权操作③通过多次合法查询数据库从中推导出一些保密数据计算机系统中，安全措施是一级一级层层设置 计算机系统的安全模型数据库安全性控制的常用方法①用户标识和鉴定②存取控制③视图④审计⑤密码存储

2.用户标识与鉴别(Identification&Authentication)系统提供的最外层安全保护措施用户标识&口令：系统核对口令以鉴别用户身份;易被窃取

3.存取控制存取控制机制组成：定义用户权限&合法权限检查用户权限定义和合法权限检查机制一起组成了DBMS安全子系统常用存取控制方法：①自主存取控制(DiscretionaryAccessControl，简称DAC):C2级，灵活②强制存取控制(MandatoryAccessControl，简称MAC):B1级，严格二、安全控制

3.自主存取控制方法通过SQL的GRANT语句和REVOKE语句实现用户权限组成：数据对象、操作类型定义用户存取权限：定义用户可以在哪些数据库对象上进行哪些类型的操作。定义存取权限称为授权关系数据库系统中存取权限ALLPRIVILEGESSELECT，INSERT，UPDATE，REFERENCES属性列数据SELECT，INSERT，UPDATE，DELETE，REFERENCES，ALLPRIVILEGES基本表和视图数据CREATEINDEX索引CREATEVIEW视图模式CREATETABLE，ALTERTABLE基本表CREATESCHEMA模式数据库操作类型对象对象类型二、安全控制授权GRANT语句的一般格式：

GRANT<权限>[,<权限>]...[ON<对象类型><对象名>]TO<用户>[,<用户>]...[WITHGRANTOPTION];①语义：将对指定操作对象的指定操作权限授予指定的用户②发出者：DBA、数据库对象创建者(属主Owner)、拥有该权限的用户③按受权限的用户：一个或多个具体用户、PUBLIC（全体用户）④WITHGRANTOPTION子句:指定：可以再授予、没有指定：不能传播、不允许循环授权[例1]把查询Student表权限授给用户U1GRANTSELECTONTABLEStudentTOU1;[例2]把对Student表和Course表的全部权限授予用户U2和U3GRANTALLPRIVILIGESONTABLEStudent,CourseTOU2,U3;

3.自主存取控制方法二、安全控制授权[例3]把对表SC的查询权限授予所有用户GRANTSELECTONTABLESCTOPUBLIC;[例4]把查询Student表和修改学生学号的权限授给用户U4

GRANTUPDATE(Sno),SELECTONTABLEStudentTOU4;对属性列的授权时必须明确指出相应属性列名

3.自主存取控制方法二、安全控制[例5]把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户

GRANTINSERTONTABLESCTOU5WITHGRANTOPTION;执行例5后，U5不仅拥有了对表SC的INSERT权限，还可以传播此权限：[例6]GRANTINSERTONTABLESCTOU6WITHGRANTOPTION;同样，U6还可以将此权限授予U7：[例7]GRANTINSERTONTABLESCTOU7;

但U7不能再传播此权限。权限的传播

3.自主存取控制方法二、安全控制不能INSERT关系SCU7U6能INSERT关系SCU6U5能INSERT关系SCU5DBA不能UPDATE属性列Student.SnoU4DBA不能SELECT关系StudentU4DBA不能SELECT关系SCPUBLICDBA不能ALL关系CourseU3DBA不能ALL关系StudentU3DBA不能ALL关系CourseU2DBA不能ALL关系StudentU2DBA不能SELECT关系StudentU1DBA能否转授权允许的操作类型数据库对象名被授权用户名授权用户名授权

3.自主存取控制方法执行[例1]到[例7]语句后学生-课程数据库中的用户权限定义表二、安全控制回收权限授予的权限可以由DBA或其他授权者用REVOKE语句收回REVOKE语句的一般格式为：REVOKE<权限>[,<权限>]...[ON<对象类型><对象名>]FROM<用户>[,<用户>]...;[例8]把用户U4修改学生学号的权限收回REVOKEUPDATE(Sno)ONTABLEStudentFROMU4;[例9]收回所有用户对表SC的查询权限REVOKESELECTONTABLESCFROMPUBLIC;[例10]把用户U5对SC表的INSERT权限收回REVOKEINSERTONTABLESCFROMU5CASCADE;将用户U5的INSERT权限收回的时候必须级联（CASCADE）收回系统只收回直接或间接从U5处获得的权限

3.自主存取控制方法二、安全控制执行［例8］到［例10］后，学生-课程数据库中的用户权限定义表不能SELECT关系StudentU4DBA不能ALL关系CourseU3DBA不能ALL关系StudentU3DBA不能ALL关系CourseU2DBA不能ALL关系StudentU2DBA不能SELECT关系StudentU1DBA能否转授权允许的操作类型数据库对象名被授权用户名授权用户名回收权限DBA：拥有所有对象的所有权限，不同的权限授予不同的用户用户：拥有自己建立的对象的全部的操作权限GRANT：授予其他用户被授权的用户：若有“继续授权”许可，可再授予所有授予出去的权力在必要时又都可用REVOKE语句收回授权机制小结

3.自主存取控制方法二、安全控制数据库角色：被命名的一组与数据库操作相关的权限，角色是权限的集合可以为一组具有相同权限的用户创建一个角色；简化授权的过程①角色的创建：CREATEROLE<角色名>②给角色授权：GRANT<权限>［，<权限>］…ON<对象类型>对象名TO<角色>［，<角色>］…③将一个角色授予其他的角色或用户：GRANT<角色1>［，<角色2>］…TO<角色3>［，<用户1>］…［WITHADMINOPTION］④角色权限的收回：REVOKE<权限>［，<权限>］…ON<对象类型><对象名>FROM<角色>［，<角色>］…

3.自主存取控制方法二、安全控制数据库角色：

3.自主存取控制方法[例11]通过角色来实现将一组权限授予一个用户。步骤如下：(1)创建一个角色R1:CREATEROLER1；(2)使用GRANT语句，使角色R1拥有Student表的SELECT、UPDATE、INSERT权限GRANTSELECT，UPDATE，INSERTONTABLEStudentTOR1(3)将这个角色授予王平，张明，赵玲。使他们具有角色R1所包含的全部权限

GRANTR1TO王平，张明，赵玲；(4)可以一次性通过R1来回收王平的这3个权限

REVOKER1FROM王平；二、安全控制数据库角色：[例12]角色的权限修改

GRANTDELETEONTABLEStudentTOR1[例13]

REVOKESELECTONTABLEStudentFROMR1；

3.自主存取控制方法自主存取控制缺点：可能存在数据的“无意泄露”原因：这种机制仅仅通过对数据的存取权限来进行安全控制，而数据本身并无安全性标记解决：对系统控制下的所有主客体实施强制存取控制策略二、安全控制

4.强制存取控制方法主体是系统中的活动实体：DBMS所管理的实际用户、代表用户的各进程客体是系统中的被动实体，是受主体操纵的：文件、基表、索引、视图敏感度标记（Label：绝密（TopSecret）、机密（Secret）、可信（Confidential）、公开（Public）主体的敏感度标记称为许可证级别（ClearanceLevel）客体的敏感度标记称为密级（ClassificationLevel）强制存取控制规则：(1)仅当主体的许可证级别大于或等于客体的密级时，该主体才能读取相应的客体(2)仅当主体的许可证级别等于客体的密级时，该主体才能写相应的客体修正规则：主体的许可证级别<=客体的密级→主体能写客体规则的共同点：禁止了拥有高许可证级别的主体更新低密级的数据对象保证更高程度的安全性；用户能不能直接感知或进行控制；适用于对数据有严格而固定密级分类的部门:军事部门、政府部门二、安全控制三、事务和锁1.事务的概念所谓的事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位。事务回滚：是指如果事务在执行过程中被迫中断，则服务器会自动恢复到数据被修改前的状态，这种自动恢复机制称为回滚。事务操作的4个属性：原子性、一致性、隔离性、持久性1.事务的概念显式事务：显式事务可以显式地在其中定义事务的启动和结束。BEGINTRANSACTION:为连接标记显式事务的起始点。COMMITTRANSACTION:如果没有遇到错误，可使用该语句成功结束事务。该事务中的所有数据修改在数据库中都将永久有效。占的资源将被释放。ROLLBACKTRANSACTION:用来清除遇到错误的事务。该事务修改的所有数据都返回到事务开始时的状态。事务占用的资源将被释放。通过下列语句进入隐式事务模式：Setimplicit_transactionon通过下列语句关闭隐式事务模式：Setimplicit_transactionoff事务的分类：显式事务、隐式事务、自动事务三、事务和锁2.并发控制一个实例：考虑飞机订票系统中的一个活动序列①甲售票点（甲事务）读出某航班的机票余额A，设A=16②乙售票点（乙事务）读出同一航班的机票余额，也为16③甲售票点卖出一张机票，修改余额A←A-1，所以A为15，把A写回数据库。④乙售票点也卖出一张机票，修改余额A←A-1，所以A为15，把A写回数据库。结果明明卖出两张票，数据库中机票的余额只减少1。这种情况称为数据库的不一致性。这种不一致性是由并发操作引起的。三、事务和锁2.并发控制锁基本的封锁类型有两