Sql2008--函数，视图，存储过程

--创建用户自定义函数
--根据函数主体的定义方式，下列语句在函数内有效
--赋值语句
--TRY....CATCH语句以外的流控制语句
--定义局部数据变量和局部游标的DECLARE语句
--SELECT语句，其中的选择列包含为局部变量分配值的表达式
--游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标
--修改table局部变量的INSERT,UPDATE,DELETE语句
--调用扩展存储过程的EXECUTE语句
--语法
--create function ['用户定义函数所属架构的名字'.] ['函数名']
--([{@parameter_name [as] [type_schema_name.] parameter_data_type [=default] [readonly] }[,....]
--]
--)
--RETURNS TABLE
--[ WITH <function_option> [,....n]]
--[ as ]
--RETURN [(] SELECT_STMT[)]
--[;]

--创建示例
use AdventureWorks
go --首先判断该函数是否存在，如存在将其删除
if OBJECT_ID(N'Sales.ufn_SalesByStore',N'IF') is not null
drop function Sales.ufn_SalesByStore;
go --开始创建函数语句
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
returns table
as
return --选择分组语句
(
select  P.ProductID,P.Name,sum(SD.LineTotal) as 'YTD Total'
from Production.Product as P
 JOIN Sales.SalesOrderDetail as SD ON SD.ProductID = P.ProductID
 JOIN Sales.SalesOrderHeader as SH ON SH.SalesOrderID = SD.SalesOrderID
 where SH.CustomerID = @storeid
 Group By P.ProductID,P.Name
);
go
--调用
select * from Sales.ufn_SalesByStore(602);
go

--视图：是一个虚拟表。生成视图的表被称为基表。当数据量过大时，使用视图可以避免并发操作。简化数据操作
--视图并不存储在数据库中
--存储过程：存储过程封装儿了多条语句
--在以下情况儿下使用存储过程儿：1、包含输入参数和输出参数，执行多个批处理语句 2、包含多条SQL语句，用来控制或操作数据库
--3、向调用程序返回状态值，以指示成功或失败。如果失败，给出失败的原因
--4、存储过程的优点儿：1、具有安全特性，2、存储过程可以强制应用程序的安全性，3、存储过程允许模块化程序设计，4、存储过程
--可以减少通信流量。
--5、存储过程的种类：1、用户定义的存储过程儿，2、扩展存储过程，3、系统存储过程
--6、创建存储过程步骤：1、所有输入参数和返回的输出参数，2、执行数据库操作的编程语句，3、返回至调用过程以标识成功或失败的状态值
------4、捕获和处理潜在的错误所需的任何错误处理语句

--创建存储过程儿
use AdventureWorks
go
create procedure dbo.sp_who --创建存储过程语句
as
 select FirstName,LastName from Person.Contact
go
exec sp_who;--执行存储过程语句
exec dbo.sp_who;--执行存储过程语句
go
drop procedure dbo.sp_who;--删除存储过程语句
go

use AdventureWorks
go
execute  HumanResources.uspGetEmployees  @FirstName=N'Diane',@LastName=N'Margheim';
go

--使用存储过程应注意的问题
--1、从存储过程儿返回给定参数的值
--2、处理执行存储过程时的错误
--3、给变量赋值要用select 或 set
--4、建立存储过程要用 as
--5、执行存储过程无反应 1、死循环，2、重复执行存储过程，3、表空间不够

--处理存储过程时的错误
--存储过程中使用try...catch语句
create procedure insert_SelectedLab   --存储过程名称为insert_SelectedLab
as
  begin try --Try部分开始
 insert into SelectedLab --向SelectedLab表中添加null值
 values(null ,null)
  end try --结束try部分
  begin catch
 --cast(@@error as nvarchar) 是将@@error 的值转换为字符型
   select '错误发生，错误号为'+CAST(@@ERROR as nvarchar)
  end catch
 

-------存储过程中的程序控制语句
---1、IF.....ELSE条件选择语句
declare @q int,@r int ,@s int --声明变量
select @q = 24,@r = 15,@s = 6*3 --为变量赋值
IF @q > @r
 print '判别结果为：q > r'  --输出结果
ELSE IF @r > @s
 PRINT '判别结果为： r>s'   --输出结果
ELSE
 PRINT '判别结果为： s>r'   --输出结果
 
 --BEGIN....END语句块
 --CASE语句
 use school
 update student
 set NAME=
 case
  when NAME='李勇' then '李小勇'
  when NAME='王敏' then '王小敏'
  else '菜小小'
 end
select s.BIRTHDAY,s.ID,s.NAME
from student s 
order by BIRTHDAY

--搜索CASE表达式
use school
go
update student
set BIRTHDAY =
 case 
  when BIRTHDAY<'1990-01-01' then '1990-01-01'
  when BIRTHDAY>'1990-01-01' and BIRTHDAY <'1990-06-01' then '1990-06-01'
  when BIRTHDAY>'1990-06-01' and BIRTHDAY<'1991-01-01' then '1991-01-01'
  else '1999-01-01'
 end
select s.ID,s.NAME,s.BIRTHDAY
from student s
order by BIRTHDAY

--WHILE循环语句

use school
select s.ID,s.NAME,s.BIRTHDAY
into student_temp
from student s

select * from student_temp
go

use school
while(select MAX(CAST(birthday as CHAR)) from student_temp)<'1995-01-01'
 begin
  update student_temp
  set birthday = DATEADD(YYYY,1,birthday)
  if(select MAX(CAST(birthday as CHAR)) from student_temp) >'1995-01-01'
  break --跳出循环
  else
  continue --继续循环
 end
select *
from student_temp

--触发器：触发器是一种特殊的存储过程，当在表（视图）中插入，更新，删除记录时，会触发一个或一系列T_SQL语句，也就是说
--对某一个表（视图）的操作会触发某种条件，从而执行的一段程序。
--1、存储过程是可以通过存储过程名称而被直接调用
--2、触发器则是通过事件触发搪行。
--作用：为数据库提供了有效的监控和处理，能够确保数据的完整性。
--触发器的类型：1、after触发器，2、instead of 触发器
--1、after 触发器：要求只有在执行某一操作（insert,update,delete）之后，触发器才被触发。适用于数据表
--2、instead of 触发器：表示并不执行其所定义的操作（insert,update,delete）,而仅是执行触发本身，即不
-----去执行原来的SQL操作，而去运行触发器本身的SQL语句。
--触发器的工作原理
--每个触发器有两个特殊的表：插入表和删除表。这两个表是逻辑表，并且由系统管理的，它们存储在内存中，不是存储在数据库中，因此
--不允许用户直接对其修改。由于它们是动态驻留内存中的，当触发器工作完成后，这两个表也删除。这两个表主要保存因用户操作而被影
--响到的原数据值或新数据值
--插入（inserted）表和删除（deleted）表是存储在内存中，它是只读的用户不能修改这两个表中的数据

--1、添加数据--insert触发器：当触发insert触发器时，新的数据行会同时插入到建立触发器的表和inserted表。触发器通过检查inserted
----表来确定是否执行触发器动作
--2、修改数据--update触发器：修改数据时，需使用inserted和deleted表。当执行update触发器时，定义触发器的表会把原始行数据移入deleted
----表，更新的数据会移入inserted表，然后触发器会检查这两个表，并确定是否更新表。
--3、删除数据--delete触发器：触发delete触发器后，会将表中删除的数据放置到deleted表，触发器会检查deleted表来确定是否执行了触发器动作。

--创建触发器
create trigger trigger_StudentDetails --触发器名称
on StudentDetails  --在表studen上建立该触发器
after update --update触发器
as
begin
 if(select count(*) from Student,StudentDetails
  where StudentDetails.studid = Student.studid
 )=0
 print '错误'--显示信息：错误
 rollback tran --回滚该触发器
end

--使用触发器常见问题
--1、create trigger 必须是查询批次中的第一个语句：触发器规定：create trigger 语句必须是批处理中的第一个语句。
--2、返回错误信息的语句 RAISERROR({msg_id|msg_str}{,severity,state})
--3、raiserror('错误',16,1)

--触发器与约束的区别
--1、触发器可以实现数据的完整性，要确保数据的完整性。较为简单的完整性要求使用约束实现。复杂的使用触发器。
--而且触发器维护起来儿比较复杂。

--E-R图
--1、实体：客观存在并可相互区分的事物一般称为实体，用矩形表示
--2、属性：实体所具有的某一特性被称为属性，用椭圆形表示
--3、联系：实体与实体这间的关系：1对1，1对多，多对多，用菱形表示
--4、设计原则：先画出局部E-R图，再综合局部E-R图，产生出总体E-R图
--创建新的windows用户
--1、开始--运行  键入：%SystemRoot%\system32\compmgmt.msc /s 打开"计算机管理"程序
--使用REVOKE语句删除用户对存储过程的执行过程
--REVOKE EXECUTE ON PR_NAME FROM ZHAO;
--使用DROP语句删除存储过程，删除视图，删除表名
--DROP USER,LOGIN,PROC,VIEW