视图、存储过程、函数、游标与触发器介绍

三、用户自定义函数
用户自定义函数是接受参数、执行操作（例如复 杂计算）并将操作结果以值的形式返回的子程序。 返回值可以是单个标量值或结果集。 SQL Server 2005支持3种类型的Transact-SQL用 户自定义函数：标量函数、内嵌表值函数和多语 句表值函数。 在SQL Server中使用用户自定义函数有以下优点：
2.1、使用存储过程的 存储过程的好处与特点 存储过程的
存储过程是已编译过的，并在服务器上注册和保存的代码 模块，因而比一般的程序语句执行起来速度更快，同时减 少了网络流量，节省了大量时间和数据流量。 存储过程可以使用控制语句和变量，并且在一个存储过程 中可以调用其他存储过程，使得用户可以进行模块化程序 设计，大大提高了用户设计程序的效率。 存储过程具有安全特性和所有权链接，可以执行所有的权 限管理，用户可以被授予执行存储过程的权限，但不拥有 直接对存储过程所引用对象的权限。 存储过程可以提高应用程序的安全性，防止SQL嵌入式攻 击 存储过程可以允许代码绑定，引用当前不存在的对象，这 些对象仅在存储过程执行时存在
允许模块化程序设计。 执行速度更快。 减少网络流量。
3.1 创建用户自定义函数
（1）标量函数 标量型函数返回一个确定类型的标量值其返回值类型为除TEXT、 NTEXT、 IMAGE、CURSOR、 TIMESTAMP、table 外的其它数据类型。函数体语句 定义在BEGIN-END语句内，其中包含了可以返回值的Transact-SQL 命令 创建标量函数的语法如下： CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ]]) RETURNS return_data_type [ WITH <function_option> [ ,...n ] ] [ AS ] BEGIN function_body RETURN scalar_expression END
1.2.1、标准视图
--创建带有部门编号的emp视图 SELECT dbo.EMP.EMPNO, dbo.EMP.ENAME, dbo.EMP.JOB, dbo.EMP.MGR, dbo.EMP.HIREDATE, dbo.EMP.SAL, M, dbo.EMP.DEPTNO, dbo.DEPT.DNAME FROM dbo.EMP INNER JOIN dbo.DEPT ON dbo.EMP.DEPTNO = dbo.DEPT.DEPTNO --通过视图修改数据 update v_emp_with_deptname set sal=800 where empno=7369 注意: (1)可以修改基于两个或两个以上基表的视图，但是每次修改只能影响一 个基表，不能同时修改。 (2)不能修改通过计算得到的列、有内置函数的列以及有聚合函数的列
2.6 存储过程的执行过程和重编译
存储过程在第 1 次执行时，要经过语法分析、解析、编译和执行共 4 个阶段， 当添加了新的索引或更新了某些列数值之后，存储过程将不自动执行优化， 可以强制在下次启动服务器前重编译该存储过程，以更新原有的执行计划。 可以有三种方式实现：
--1使用sp_recompile系统存储过程 exec sp_recompile hh --2创建存储过程时增加with recompile选项 ALTER procedure [dbo].[hh] with recompile as begin select count(*) 'count' from aa end --3 执行重新编译 exec hh with recompile
2.4、创建存储过程的语法
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ { @parameter [ type_schema_name. ] data_type } [ OUTPUT ] ] [ ,...n ] AS { <sql_statement> }
1.2.3、分区视图
--创建分区视图 --将表进行行分割，emp表分解为emp1和emp2 select * into emp1 from emp where empno<7782 go select * into emp2 from emp where empno>=7782 go CREATE VIEW v_emp_with_1_and_2 AS SELECT * FROM emp1 UNION ALL SELECT * FROM emp2 注意：分区视图在sqlserver2005中被分区表替代
1.2.2、索引视图
--创建各部门人数的视图 drop view v_countOfDept go create view v_countOfDept WITH SCHEMABINDING as SELECT EMP.deptno,count_big(*) empcount FROM dbo.EMP group by emp.deptno --创建聚合索引 CREATE UNIQUE CLUSTERED INDEX i_v_countOfDept_deptno ON v_countOfDept(deptno) 注意： （1）创建索引视图，必须拥有唯一聚合索引，如果创建聚合索引，带有聚合函数的基础视 图必须使用WITH SCHEMABINDING ，group by以及count_big函数 （2）使用索引视图能提高数据库效率 （3）如果视图引用任何非确定性函数，则不能在视图上创建聚集索引
2.7 加密存储过程
--使用with encryption语句对存储过程进行加密 ALTER procedure [dbo].[hh] with recompile,encryption as begin select count(*) 'count' from aa end
2.8 使用扩展存储过程
1.1、视图的优点
简化查询 提供一种安全机制 视图掩码（对长的字段重新命名） 数据即时更新
1.2、视图的分类
标准视图：使用最频繁的视图，不存储任 何数据，不占用任何存储空间 索引视图：拥有唯一群集索引的视图被称 为索引视图，它存储真实索引数据，占用 一定的存储空间。 分区视图：现在用分区表进行替代
--创建带输入参数的存储过程 if exists(select name from sysobjects where name='pro_name' and type='p') drop procedure pro_name Go create procedure pro_name @vempno int as declare @v_name varchar(10),@v_sal decimal(10,2) begin begin try select @v_name=ename,@v_sal=sal from emp where empno=@vempno if @v_sal<2500 print '工资超过2500' else print '工资少于2500' end try begin catch print '错误号:'+cast(@@error as varchar(10)) print '错误内容:'+error_message() end catch end ----使用存储过程 pro_name 7369
使用外围配置器配置，如下图：
敲入 exec xp_cmdshell "dir c:“ 结果是列出c盘的具体内容 相干的还有几个都是以xp_开头，具体可以查询msdn文档。 --比如使用xp_fileexist判断文件是否存在 use master declare @ret int exec xp_fileexist "e:\temp\web.config",@ret output print @ret
1.3、在Management Studio代码模块， 它采用T-SQL语言来编写，经编译后存放在 数据库服务器中，具有很好的可重用性， 可用于高效地完成某些操作 存储过程可以充分利用服务器的高性能运 算能力，无需把大量的结果集送往客户端 进行处理，大大减少了网络数据传输的开 销，提高了应用程序访问数据库的速度和 效率
2.2、存储过程的分类
用户自定义的存储过程：最主要的存储过 程 系统存储过程：sp_前缀，系统预定义 扩展存储过程：保存在DLL动态链接库中并 从动态链接库中执行的C++程序代码，用于 扩展SQLSERVER2005性能，以字符xp_开 头，通常与其它系统存储过程一起使用通 过程序集调用。
2.3、存储过程的设计规则
视图、存储过程、函数、游标与触发器
学习目标
掌握使用视图 掌握使用存储过程 掌握使用自定义标量函数、表值函数 掌握使用游标 掌握使用触发器
一、视图的定义
视图由SELECT查询语句所定义的一个虚拟 表，是查看数据的一种非常有效的方式， 同真实的数据表一样，视图也包含一系列 带有名称的数据列和数据行，但视图与数 据表又有很大的不同，视图中的数据并不 真实的存在于数据库中。
--创建带输出参数的存储过程 if exists(select name from sysobjects where name='pro_name' and type='p') drop procedure pro_name Go create procedure pro_name @vempno int,@v_name varchar(10) output,@v_sal decimal(10,2) out as begin begin try select @v_name=ename,@v_sal=sal from emp where empno=@vempno end try begin catch print '错误号:'+cast(@@error as varchar(10)) print '错误内容:'+error_message() end catch end ----使用存储过程 declare @v_name varchar(10) ,@v_sal decimal(10,2) exec pro_name 7369 ,@v_name output,@v_sal output if @v_sal<2500 print '工资超过2500' else print '工资少于2500' go