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

nainai1

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

• Create Database Objects创建数据库对象 (24%)
• Work with Data数据处理 (27%)
• Modify Data数据修改 (24%)
• 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 存储过程名称


存储过程类型

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

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

CREATE PROCEDURE dbo.TruncateMyTableWITH EXECUTE AS SELFAS 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

plantegg

不知该说些什么。。。。。。就是谢谢

Rainie999

人生不能像做菜、把所有的料都准备好才下锅!

心心失意

听君一席话，省我十本书!

longpan

走过了年少，脚起了水泡

sdxh0506

如果跟导师讲不清楚，那么就把他搞胡涂吧!

gdx

此地禁止大小便，违者没收工具。