【2017年整理】实验28了解存储过程.docVIP

实验28 了解存储过程 28.1目标 了解存储过程的功能、分类和使用原则 28.2背景知识 存储过程是SQL Server的数据库对象。存储过程的存在独立于表，它存放在服务器上，供客户端调用。存储过程是大型、复杂、高性能要求的数据库应用系统所必需要的技术。 28.3实验内容 1.存储过程的功能 SQL Server中的存储过程与其它编程语言中的过程类似，是指封装了可重用代码的模块或例程。存储过程可以接受输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言（DDL）和数据操作语言（DML）语句，然后返回输出参数。存储过程存放在服务器上，供客户端调用，可以使用T-SQL语言中的EXECUTE语句来运行存储过程。 使用存储过程的好处如下： （1）存储过程已在服务器注册。 （2）存储过程具有安全特性（例如权限）和所有权链接，以及可以附加到它们的证书，用户可以被授予权限来执行存储过程而不必直接对存储过程中引用的对象具有权限。 （3）存储过程可以强制应用程序的安全性，参数化存储过程有助于保护应用程序不受SQL Injection攻击（SQL Injection是一种攻击方法，它可以将恶意代码插入到以后将传递给SQL Server供分析和执行的字符串中，然后将执行并遭到攻击）。 （4）存储过程允许模块化程序设计，存储过程一旦创建，以后即可在程序中调用任意多次。这可以改进应用程序的可维护性，并允许应用程序统一访问数据库。 （5）存储过程是命名代码，允许延迟绑定，这提供了一个用于简单代码演变的间接级别。 （6）存储过程可以减少网络通信流量，一个需要数百行T-SQL代码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码。 2.存储过程的分类 在SQL Server 2005中，存储过程可以分为三大类：用户定义的存储过程、扩展存储过程、系统存储过程。 1）用户定义的存储过程：用户定义的存储过程又分为两种类型：T-SQL和CLR。 （1）T-SQL 存储过程是指保存的T-SQL语句集合，可以接受和返回用户提供的参数。 （2）CLR存储过程是指对.NET Framework公共语言运行时（CLR）方法的引用，可以接受和返回用户提供的参数。它们在.NET Framework程序集中是作为类的公共静态方法实现的。 2）扩展存储过程：扩展存储过程允许使用编程语言（例如C）创建自己的外部例程。扩展存储过程是指SQL Server的实例可以动态加载和运行的DLL。扩展存储过程直接在SQL Server的实例的地址空间中运行，可以使用SQL Server扩展存储过程API完成编程。 说明：CLR集成提供了更为可靠和安全的替代方法来编写扩展存储过程。 3）系统存储过程：SQL Server2005中的许多管理活动都是通过一种特殊的存储过程执行的，这种存储过程被称为系统存储过程。例如，sys.sp_changedbowner就是一个系统存储过程。从物理意义上讲，系统存储过程存储在源数据库中，并且带有sp_前缀。从逻辑意义上讲，系统存储过程出现在每个系统定义数据库和用户定义数据库的sys构架中。在SQL Server 2005中，可将GRANT、DENY和REVOKE权限应用于系统存储过程。SQL Server支持在SQL Server和外部程序之间提供一个接口以实现各种维护活动的系统存储过程。这些扩展存储程序使用xp_前缀。 3.使用原则 几乎所有可以写成批处理的T-SQL代码都可以用来创建存储过程。存储过程的设计规则如下： （1）CREATE PROCEDURE定义自身可以包括任意数量和类型的SQL语句，但以下语句除外： CREATE AGGREGATE 、CREATE RULE、CREATE DEFAULT、CREATE SCHEMA、CREATE或ALTER FUNCTION、CREATE或ALTER TRIGGER、CREATE或ALTER PROCEDURE、CREATE或ALTER VIEW、SET PARSEONLY、SET SHOWPLAN_ALL、SET SHOWPLAN_TEXT、SET SHOWPLAN_XML、USE database_name。 （2）其它数据库对象均可在存储过程中创建。可以引用在同一存储过程中创建的对象，只要引用时已经创建了该对象即可。 （3）可以在存储过程内引用临时表。 （4）如果在存储过程内创建本地临时表，则临时表仅为该存储过程而存在，退出该存储过程后，临时表将消失。 （5）如果执行的存储过程将调用另一个存储过程，则被调用的存储过程可以访问由第一个存储过程创建的所有对象，包括临时表在内。 （6）如果执行对远程SQL Server 2005实例进行更改的远程存储过程，则不能回滚这些更改。远