SQL查询的艺术学习笔记--存储过程与函数

--SQL存诸过程与函数
--SQL存储过程定义：是由SQL语句和控制流语句构成的语句串。可带输入和输出参数，
--可返回结果集，同时可以调用另一存储过程


--SQL中流控制语句
--begin ...end 语句 顺序执行语句
--语法：
begin
      statement block
statement--声明，陈述 
end
--begin...end 为顺序执行语句   
--if...else语句
--语法：
if boolean_expresstion
   statement
[else [if boolean_expresstion] statement]  
use seldata
select * from student
if (select min(mark) from student where sno='9704') >90
    print '学生9704成绩全部优秀'
else
if (select min(mark) from student where sno='9704')>60
    print '学生9704成绩全部合格'
else
    print '学生9704成绩不合格'   
    
while  break continue 语句 条件循环语句 
--语法
while boolean_expression
      statement
--复制表
select * into stunew
from student where sno='9705'  
select * from stunew
select AVG(mark) from stunew 


while (select avg(mark) from stunew)<75
begin
update stunew set mark=mark*1.1
if (select avg(mark) from stunew)>=75
break
end
--while语句和if结合使用
select * from stunew
select AVG(mark) from stunew


--declare语句
--declare用来定义一个局部变量 可用select语句为该变量赋值。
--变量必须以@开头接标识符 如果是@@则表示为全局变量
--语法：
declare @variable_name data_type
[,@variable_name data_type]....
--@variable_name是一个合法的标识符
--data_type 系统数据类型 也可以是用户定义数据类型（create type 数据类型 数据规则 ）
--select 语句局部变量赋值语法：
select @variable_name ={expression|(select_statement)}
       [,@variable={expression|(select_statement)}....]
       [from clause]
       [where clause]
--clause 子句    
select * from student
declare @vgood int
select @vgood=min(mark)
from student
where sno<'9704'
if @vgood>60
print '成绩合格'
else
print '不合格'
select @vgood
--全局变量是以@@变量名开始 @variable局部变量定义和使用只在当前过程中有效


--Gogo label语句
--Goto  label 无条件将语句的执行顺序跳转到用户定义标号（label）处
--Goto label语法：
lable:
go to label
--例输出五个hello word
declare @ct1 smallint, 
        @ct2 smallint
select @ct1=1,
       @ct2=5
restlabel:
print 'Hello word!'
select @ct1=@ct1+1
while @ct1<=@ct2
goto restlabel
--使用set试试
declare @ct1 smallint,
        @ct2 smallint
        set  @ct1=1 
        set   @ct2=5
reslabel:
        print 'Hello word'
        print @ct1 --可用 select @ct1 替换
        set @ct1=@ct1+1
        while @ct1<=@ct2
        goto reslabel
--set 语句只能单独赋值


--RETURN语句
--Return用于无条件退出一个查询和过程
--语法：
Return [int_expression]
declare @a char(1)
set @a=1


while @a<=5
set @a=@a+1
print '当前值为:'+@a
return
print '最后值是'+@a


--WaitFor语句
--定义在某个时间执行一个语句，定时计划任务？
--语法：
waitfor [delay 'time' | time 'time']
--delay 'time' time=HH:MM:SS
--time  'time' time-具体时间 如am 2:00


--禁用所有触发器：
exec sp_MSforeachtable 'alter table ? disable trigger all'
--启用所有触发器：
exec sp_MSforeachtable 'alter table ?enable trigger all'
                


--print语句
--语法：
print {string_expr|@local_variable|@global_variable}
--输出 字符串    本地变量           全局变量


--注释
--语法：
/* text_of_comment */
--两种方式：一种是--
--          一种是/*.....*/


--SQL存储过程和函数
--SQL存储过程包括：系统存储过程  用户自定义存过程
--系统存储过程包括：
--目录存储过程
--复制类存储过程
--安全管理类存储过程
--分布式查询存储过程


--Create procedure 创建存储过程
--Create Procedure 授权语法：
grant create procedure  username
--使和create procedure创建存储过程语法
create procedure procedure_name[;version number]
[
{@parameter data_type}
[varying] [=default value] [output]
][,...n]
[with
{recompile|encryption|recompile,encrption}]
[for replication]
as sql_statement[....n]
--词汇解释：
--procedure：程序       parameter:参数    varying:不同的，变化的
--recompile: 重新编译   encryption:加密   replication: 复制，回答 回应
--statement: 声明，陈述 清单 报表      cursor:光标 游标，指针
--说明创建存储过程语法：
/* 
procedure_name:存储过程名称   version number：存储过程版本号
@parameter:存储过程参数名，用户须定义参数值，或给出所有的参数值。参数可做存储过程中变量名
参数不能用作列名，表名，其他数据库对象名  
Data_type:参数的数据类型 如参数为游标（cursor）则参数必须指定为：varing和output
varing:指定由output参数支持的结果集，仅用于流标型参数
default value:指定参数的缺省值
output:表明该参数为返回参数，output参数可向调用者返回信息，该参数是可变的，但text类不能做参数
recompile:指明SQL不保存该存储过程执行计划，每次执行均会重新编译，如果不指定则在执行：create procedure
语句时编译存储过程，每次调用使用同一计划。
encryption:为syscomments表中存储过程条目加密，防止用户看到编译后的诘句？
for replication:标明该存储过程保能在复制过程中执行
sql_statement:包含于存储过程中任何数量和类型的SQL语句。
*/
--创建一个简单的存储过程：
use seldata
create procedure cp_01
as 
select * from teacher
execute cp_01  
--execute 执行存储过程
create procedure ust_01
@depart char(10),@avgage int output, @maxage int output
with recompile
as 
select * from teacher where dname=@depart
select @maxage=MAX(age) from teacher  where dname=@depart
select @avgage=AVG(age) from teacher where dname=@depart
if @avgage<=30
select '年龄结构'='年龄结构偏年轻','平均年龄'=@avgage
if @avgage>30 and @avgage<=40
select '年龄结构'='年龄结构3210','平均年龄'=@avgage
if @avgage>40
select '年龄结构'='年龄结构1230','平均年龄'=@avgage
--option(recompile)


declare @avgage1 int ,@maxage1 int 
execute ust_01  '计算机' , @avgage1 output,@maxage1 output
select @avgage1,@maxage1
exec sp_recompile N'teacher';
drop procedure ust_01
--使和execute语句调用存储过程
--语法如下：
[[exec[ute]]
{[@return_status=]--变量，存储返回值
{procedure_name[;number]|@procedure_name_ver}
[[@parameter=]{value|@variable[output]|[default]]
[,....n]
[with recompile]
--@return_status可选变量，用来存储返回的值
--@procedure_name_var为变量名，用来代表存储过程的名字
--存储过程传递参数
use seldata
go
create procedure ptop;2 
--直接指明版本号：ptop;2为该存储过程的第二个版本
@spex char(2),
@maxage int output,
@avgage int output
with recompile 
as 
set @maxage=(select max(age) from teacher where sex=@Spex)
set @avgage=(select avg(age) from teacher where sex=@spex);


drop procedure ptop 
--删除存储过程
exec sp_recompile  N'teacher';
--执行teacher表存储过程在下一次执行时重新编译
declare @mage int,@aage int
execute ptop   男, @mage output, @aage output
--'男' 为传递给@sex参数值 也可以写成@spex='男',但后面也只能这样写。
--所以可以按顺序放置传递参数值
select '最大年龄'=@mage,'平均年龄'=@aage
--注意存储过程一旦编译后，每次调用都不会改变创建的存储过程？


--修改存储过程：
alter procedure ptop
@spex char(2),
@maxage int,
@avgage int
as 
set @maxage=(select MAX(age) from teacher where sex=@spex)
set @avgage=(select AVG(age) from teacher where sex=@spex)
--关于with compile作用不明显，或者是我理解不到？加些参数后，在执行存储过程时并没有重新编译
--理解错误？当我们修改源码后，无论加与不加recompile 我们都需要重新编译的。系统存储的是根据源码编译后的存储过程？


--使用create function 创建自定义函数
--自定义函数返回单一的值和表
--根把自定函数返回值类型的不同，分为以下三类函数
--标量函数：（scalar function):返回一个确定类型的标量值  scalar:标量的，梯状的，等级
--内联表值型函数：（inline table-valued functions)以表的型式返回一个返回值，返回一个参数化的表，或参数化的视图
--inline:内联的，联机的  
--多声明表值型函数（Multi_statement Table_valued Function)标量函数和内联表值类型函数的结合体。含Begin-End语句
--函数体，该函数体包括插入语句。向返回表中插入值。
--Multi:多  statement:声明 陈述 报表 清单


--创建标量型函数（scalar function)
--返回确定类型标量值语法：
create function [owner_name,] function_name
([{@parameter_name [as] scalar_parameter_data_type[=default]}[,...n]})
--参数名                --标量参数值类型 
returns scalar_return_data_type
[with {encryption|schemabinding]
--encryption:加密  schema：模式，计划 图解 概要   binding:装订 捆绑，粘合 约束的 捆绑的
[as]
begin
   function_body
   returns scalar_expression
end
--语句说明：
--function_name:标量函数名   
--@parameter_name：自定义函数参数名，作用于整个自定义函数。
--scalar_parameter_data_type:标量型参数类型，任意有效的SQL数据类型。不能为自定义数据类型，表或游标
--scalar_return_data_type:返回值的数据类型，任意有效的SQL数据类型。不能为自宝义数据类型，表或游标
--encryption：加密选项
--schema binding:计划绑定 将自定义函数绑定至引用的数据库对象
--function_body：指定的SQL语句，决定自定义函数返回值
--scalar_expression:指定的自定义函数返回的标量值表达式
--创建标量型自定义函数实例及调用：
create function YWCF
(@workdate datetime,@nowdate datetime,@pwage decimal)
--定义自定义函数参数类型
returns decimal
--返回值数据类型
as 
begin
return((year(@nowdate)-Year(@workdate))*@pwage)
end
--调用函数
select dbo.YWCF ('2000-9-21',getdate(),12) as '工龄工资'
--注意：调用时需加dbo.function_naem 


--创建内联表值型用户自定义函数
--语法：
create function [owner_name.] function_name    
([{@parameter_name [as] scalar_parameter_data_type[=default]}[,....n]})
returns table
--这里返回的是表
[with {encryption|schemabinding}]
as 
return [()select-stmt[] ]
--select-stmt:单个slect语句。确定返回表的数据
create function csfc(@csname varchar(20))
returns table
as 
return(select s.*from student as s,course as c 
where s.cno=c.cno and c.cno in
(select cno from course where cname=@csname))
--调用该自定义函数返回的表
select * from csfc('计算机软件基础')
--select * from table '参数值'


--创建多声明表值型用户自定义函数
--语法：
create function [owner_name.] function_name
([{@parameter_name [as] scalar_parameter_data_type[=default]}[,...n]] )
returns@return_varible table ({column_definition|table_constraint}[,...n])
--definition:定义   constraint:约束 强制
--@return_varible 为表类型变量
[with {encryption|schemabinding}]
[as]
begin
funtion_body
return
end
--创建实例
create function tifcf(@depart varchar(20))
returns @tif table
(
tfname varchar(8),
tsex char(2),
tname varchar(20),
tcno int primary key,
tcname varchar(20),
tftime int)
---定义表？
as
begin
insert @tif
select t.tname,t.sex,t.dname,t.cno,c.cname,c.ctime
from teacher as t ,course as c
where c.cno=t.cno
and t.cno in (select cno from teacher where dname=@depart)
return
end


drop function tifcf
--调用多声明表值类型函数
select * from tifcf('计算机')
select * from course
select * from teacher


--使用sp_helptext查看存储过程和自定义函数源代码
--语法：sp_helptext procedure_name|function_name
sp_helptext ptop


--使用sp_rename重命名存储过程和自定义函数
--语法：sp_rename old_procedure_name|old function-name , new_name
sp_rename ptop ,ppop
--注意新旧名之间用逗号隔开
--删除存储过程和自定义函数
drop procedure|function  procedure_name|function_name