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

--创建带输入参数的存储过程 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
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)如果视图引用任何非确定性函数,则不能在视图上创建聚集索引
1.1,视图的优点
简化查询 提供一种安全机制 视图掩码(对长的字段重新命名) 数据即时更新
1.2,视图的分类
标准视图:使用最频繁的视图,不存储任 何数据,不占用任何存储空间 索引视图:拥有唯一群集索引的视图被称 为索引视图,它存储真实索引数据,占用 一定的存储空间. 分区视图:现在用分区表进行替代
2.7 加密存储过程
--使用with encryption语句对存储过程进行加密 ALTER procedure [dbo].[hh] with recompile,encryption as begin select count(*) 'count' from aa end
2.8 使用扩展存储过程
允许模块化程序设计. 执行速度更快. 减少网络流量.
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.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—触发器等等
使用外围配置器配置,如下图:
敲入 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.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中被分区表替代
2.4,创建存储过程的语法
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ { @parameter [ type_schema_name. ] data_type } [ OUTPUT ] ] [ ,...n ] AS { <sql_statement> }
1.3,在Management Studio中创建视图
二,存储过程
存储过程是数据库系统中封装的代码模块, 它采用T-SQL语言来编写,经编译后存放在 数据库服务器中,具有很好的可重用性, 可用于高效地完成某些操作 存储过程可以充分利用服务器的高性能运 算能力,无需把大量的结果集送往客户端 进行处理,大大减少了网络数据传输的开 销,提高了应用程序访问数据库的速度和 效率
--创建带输出参数的存储过程 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