微软认证考试70-461 Modify Data 数据修改 --24%比重--(1)

附注：微软认证考试70-461范围

1. Create Database Objects创建数据库对象 (24%)
2. Work with Data数据处理 (27%)
3. Modify Data数据修改 (24%)
4. Troubleshoot & Optimize故障排解及SQL优化 (25%)

本文是第三节Modify Data数据修改。

第一部分：Create and alter stored procedures (simple statements).May include but not limited to: write a stored procedure to meet a given set of requirements; branching logic; create stored procedures and other programmatic objects; techniques for developing stored procedures; different types of storeproc result; create stored procedure for data access layer; program stored procedures, triggers, functions with T-SQL. 创建修改存储过程(简易语句)。可能包含但不仅限于：能够写出符合既定需求的存储过程；批处理逻辑；创建存储过程及其它可编程对象；了解各种存储过程开发相关技术；了解存储过程不同的返回结果类型；为某数据访问层创建存储过程；用T-SQL编写存储过程，触发器和函数。

创建存储过程

没有参数的存储过程：

CREATE PROCEDURE 存储过程名称
AS
SELECT 语句
GO

执行方法：

EXECUTE 存储过程名称

或者

EXEC 存储过程名称

以下示例都将使用EXEC。

带参数的存储过程：

CREATE PROCEDURE my1
   @lastname varchar(40), 
   @firstname varchar(20) 
AS 
SELECT * FROM member
WHERE  firstname = @firstname
      AND lastname = @lastname
GO

执行方法：

不指名参数将按照声明的参数顺序传入。

EXEC my1 'lastname','firstname'

指名参数：

EXECUTE my1 @lastname = 'last', @firstname = 'first'
或者
EXECUTE my1 @firstname = 'first', @lastname = 'last'

如果在批处理块的第一行，可以直接使用存储过程名称

my1 'lastname','firstname'

或者

my1 @lastname = 'last', @firstname = 'first'

或者

my1 @firstname = 'first', @lastname = 'last'

创建存储过程时设置参数默认值

CREATE PROCEDURE my1
   @lastname varchar(40)='Li', 
   @firstname varchar(20)='Hui' 
AS 
SELECT * FROM member
WHERE  firstname = @firstname
      AND lastname = @lastname
GO

执行 EXEC my1相当于EXEC my1 @lastname='Li',@firstname='Hui'

创建带OUTPUT参数的存储过程

CREATE PROCEDURE my1
   @lastname varchar(40), 
   @firstname varchar(20),
   @testoutput varchar(100) OUTPUT
AS 
SELECT @testoutput=email FROM member
WHERE  firstname = @firstname
      AND lastname = @lastname and id=1

GO

执行

declare @test varchar(100)
exec my1 'Li','Hui',@test OUTPUT
select @test

使用表值参数

创建一个表值类型：

CREATE TYPE myTableType AS TABLE
( name VARCHAR(50)
, id INT );
GO

创建带表值参数的存储过程：

create PROCEDURE my1
@testtabletype myTableType readonly
AS
SELECT * FROM @testtabletype
GO

使用OUTPUT游标参数

ALTER PROCEDURE my1
   @testcursor cursor VARYING OUTPUT
AS 
SET @testcursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM member

OPEN @testcursor
GO

注意：VARYING指定作为输出参数支持的结果集。该参数由过程动态构造，其内容可能发生改变。仅适用于 cursor 参数。

使用：

DECLARE @MyCursor CURSOR
EXEC my1 @testcursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
   FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO

使用 WITH RECOMPILE 选项

CREATE PROCEDURE my1
   @lastname varchar(40), 
   @firstname varchar(20) 
WITH RECOMPILE
AS 
SELECT * FROM member
WHERE  firstname = @firstname
      AND lastname = @lastname
GO

WITH RECOMPILE选项将不会把执行计划存入缓存中。

使用WITH ENCRYPTION选项

此选面会对存储过程定义进行加密。当执行exec sp_helptext my1时会提示“对象my1是加密的”，从而不能够查看my1的定义。

执行语句：

SELECT c.id, c.text 
FROM syscomments c INNER JOIN sysobjects o
   ON c.id = o.id
WHERE o.name = 'my1'

结果：

id text
224719853 NULL

修改存储过程

ALTER PROCEDURE sp_my1
   @lastname varchar(40), 
   @firstname varchar(20) 
AS 
SELECT * FROM member
WHERE  firstname = @firstname
      AND lastname = @lastname
GO

删除存储过程

DROP PROCEDURE 存储过程名称

存储过程类型

1. 用户定义。用户定义的过程可在用户定义的数据库中创建，或者在除了 Resource 数据库之外的所有系统数据库中创建。
2. 临时。存储于tempdb 中。本地临时过程的名称以单个数字符号 (#) 开头；它们仅对当前的用户连接是可见的；当用户关闭连接时被删除。全局临时过程的名称以两个数字符号 (##) 开头，创建后对任何用户都是可见的，并且在使用该过程的最后一个会话结束时被删除。
   
3. 系统。物理上存储在内部隐藏的 Resource 数据库中，但逻辑上出现在每个系统定义数据库和用户定义数据库的sys 架构中。系统存储过程以sp_开头。
   

使用 EXECUTE AS 子句，存储过程可用于创建自定义权限集

以下示例使用 EXECUTE AS 为数据库操作创建自定义权限。 某些操作（如 TRUNCATE TABLE）没有可授予的权限。 通过将 TRUNCATE TABLE 语句合并到存储过程中并指定该过程作为一个有权修改表的用户执行，您可以将截断表的权限扩展至授予其对过程的 EXECUTE 权限的用户。

CREATE PROCEDURE dbo.TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE MyDB..MyTable;

附注：存储过程中可以使用批处理控件语句，如IF...ELSE...，TRY...CATCH,事务等。

参考： http://msdn.microsoft.com/zh-cn/library/aa258259(v=sql.80).aspx

http://msdn.microsoft.com/zh-cn/library/ms187926(v=sql.110).aspx