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

三,用户自定义函数
用户自定义函数是接受参数,执行操作(例如复 杂计算)并将操作结果以值的形式返回的子程序. 返回值可以是单个标量值或结果集. SQL Server 2005支持3种类型的Transact-SQL用 户自定义函数:标量函数,内嵌表值函数和多语 句表值函数. 在SQL Server中使用用户自定义函数有以下优点:
CREATE PROCEDURE 定义本身可包括除下列 CREATE 语句以外的任何数量和类型的 SQL 语 句,存储过程中的任意地方都不能使用下列语句:
CREATE RULE CREATE DEFAULT CREATE FUNCTION CREATE TRIGGER CREATE PROCEDURE CREATE VIEW USE DATABASE
2.5 创建和使用存储过程
--创建不带参数的存储过程 if exists(select name from sysobjects where name='pro_name' and type='p') drop procedure pro_name Go create procedure pro_name 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=7369 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 ----使用存储过程 [exec] pro_name 注意:Sysobjects: 系统中的所有对象,包括:表,存储过程,触发器等等 Type ='p' 类型为存储过程,其他如:s—系统对象,u—用户表,v—变量,tr—触发器等等
2.7 加密存储过程
--使用with encryption语句对存储过程进行加密 ALTER procedure [dbo].[hh] with recompile,encryption as begin select count(*) 'count' from aa end
2.8 使用扩展存储过程
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中被分区表替代
--创建带输入参数的存储过程 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
--创建带输出参数的存储过程 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
使用外围配置器配置,如下图:
敲入 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.1,视图பைடு நூலகம்优点
简化查询 提供一种安全机制 视图掩码(对长的字段重新命名) 数据即时更新
1.2,视图的分类
标准视图:使用最频繁的视图,不存储任 何数据,不占用任何存储空间 索引视图:拥有唯一群集索引的视图被称 为索引视图,它存储真实索引数据,占用 一定的存储空间. 分区视图:现在用分区表进行替代
1.3,在Management Studio中创建视图
二,存储过程
存储过程是数据库系统中封装的代码模块, 它采用T-SQL语言来编写,经编译后存放在 数据库服务器中,具有很好的可重用性, 可用于高效地完成某些操作 存储过程可以充分利用服务器的高性能运 算能力,无需把大量的结果集送往客户端 进行处理,大大减少了网络数据传输的开 销,提高了应用程序访问数据库的速度和 效率
2.2,存储过程的分类
用户自定义的存储过程:最主要的存储过 程 系统存储过程:sp_前缀,系统预定义 扩展存储过程:保存在DLL动态链接库中并 从动态链接库中执行的C++程序代码,用于 扩展SQLSERVER2005性能,以字符xp_开 头,通常与其它系统存储过程一起使用通 过程序集调用.
2.3,存储过程的设计规则
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.4,创建存储过程的语法
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ { @parameter [ type_schema_name. ] data_type } [ OUTPUT ] ] [ ,...n ] AS { <sql_statement> }
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
视图,存储过程,函数,游标与触发器
学习目标
掌握使用视图 掌握使用存储过程 掌握使用自定义标量函数,表值函数 掌握使用游标 掌握使用触发器
一,视图的定义
视图由SELECT查询语句所定义的一个虚拟 表,是查看数据的一种非常有效的方式, 同真实的数据表一样,视图也包含一系列 带有名称的数据列和数据行,但视图与数 据表又有很大的不同,视图中的数据并不 真实的存在于数据库中.
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, dbo.EMP.COMM, 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)不能修改通过计算得到的列,有内置函数的列以及有聚合函数的列