存储过程

--存储过程(PROCEDURE)
/*
创建存储过程，存储过程是保存起来的可以接受
和返回用户提供的参数的 Transact-SQL 语句的集合。
可以创建一个过程供永久使用，或在一个会话中临时使用（
局部临时过程），或在所有会话中临时使用（全局临时过程）。
也可以创建在SQL启动时自动运行的存储过程。

存储过程的优级点:
1.允许模块化设计 单独修改(独立的),任意次调用
2.允许更快执行 存储过程比批代码执行速度快,存储过程在创建时
DBMS对其进行了分析、优化。首次执行后，在内存中
会存在，因此调用速度快
T-SQL语句每次都要从客户端重复发送，SQL每次执行
都要进行一次编译，优化。
3减少网络流量 数百行代码由一条代码（执行存储过程的代码）实现
不需要在网络中发送数百行代码
4安全 SQL事件探查器能跟踪到客户端发送到SQL中的T-SQL语句
如果使用存储过程，则只能探查到过程名。


存储过程类型：

1.系统存储过程
sp_databases 列出服务器上的所有数据库
sp_server_info 列出服务器信息，如字符集、版本和排列顺序
sp_stored_procedures 列出当前环境中的所有存储过程
sp_tables 列出当前环境中所有可以查询的对象
sp_start_job 立即启动自动化任务
sp_stop_job 停止正在运行的自动化任务
sp_password 添加或修改登录账户的密码
sp_configure 修改SQL 全局配置选项。
如果使用时不带选项，则显示当前服务器设置
sp_help 显示任意数据库对象的信息
sp_helptext 显示规则、默认值、未加密的存储过程、用户定义
的函数、触发器或视图的实际文本
*/
--2.用户自定义的存储过程
CREATE PROC procedure_name
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]

AS sql_statement

@parameter

过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。
用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2.100 个参数。

使用 @ 符号作为第一个字符来指定参数名称。
参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；
相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，
而不能用于代替表名、列名或其它数据库对象的名称。

VARYING

指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。
仅适用于游标参数。

default

参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。
默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，
那么默认值中可以包含通配符（%、_、[] 和 [^]）。

OUTPUT

表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。
使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image
参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。


-- A、不带参数的存储过程
create procedure proc_sales
as
select * from sales
exec proc_sales

-- B、带参数的存储过程
create proc proc_titles
@a varchar(20),@b int =9.99 --@b为带默认值的参数
as
select * from titles
where type=@a and price>@b
exec proc_titles @a='business'

-- C、有返回值的存储过程
create proc proc_titles_2
@a varchar(20),@b int output
as
select @b=sum(price)
from titles
where type=@a

declare @h int
exec proc_titles_2 'business',@h output --注意变量@h必须为output才可以得到存储过程的输出，否则@h的值为NULL
select @h

create proc proc_employee
@fname varchar(20)=null
as
if @fname is null
begin
print '请输入作者名'
return(1)
end
else
begin
if(select count(*) from employee where fname=@fname)=0
return(0)
else
return(2)
end
declare @h int
exec @h= proc_employee 'aa'
if @h=0
begin
print '没有该作者的相关信息'
end

select * from employee


create procedure p_title
@cnt int output, --输出参数
@cnt2 int output,
@type varchar(20)='business' --输入参数
as
select *
from titles
where type = @type
select @cnt=count(*)
from titles
where type = @type
select @cnt2=count(*)
from titles
if @cnt = 0
return 0
else
return 1

go
set nocount on

declare @mycnt int,@mycnt2 int,@retVal int
set @mycnt = 0
exec @retVal=p_title @mycnt output,@mycnt2 output,'aa'
if @retVal=0
print '没有你要查找的书籍'
else
print '你查找到: '+
convert(varchar(20),@mycnt) + ' 本书籍 '
+convert(varchar(20),@mycnt2)


--重新编译存储过程
--1>使用系统提供的存储过程 sp_recompile
--强制在下次执行proc时对其进行重新编译
/*
语法：
sp_recompile [ @objname = ] 'object'
参数：
[@objname =] 'object'
是当前数据库中的存储过程、触发器、
表或视图的限定的或非限定的名称。
object 是 nvarchar(776) 类型，无默认值。
如果 object 是存储过程或触发器的名称，
那么该存储过程或触发器将在下次运行时重新编译。
如果 object 是表或视图的名称，
那么所有引用该表或视图的存储过程都将在下次运行时重新编译
*/
--2>在创建时设置
create proc proc_name
with recompile
as
sql_statement
--3>在执行时候重新编译
exec proc_name with recompile
/*
为什么要重新编译存储过程？
1>在首次执行存储过程时，系统会自优化
2>当存储过程使用的表修改后，也会优化
3>当表中添加新索引后，只有重启SQL才会优化，如果不重启，则需手动优化
4>只有优化后，用户对表的索引的修改才能反映出来
*/

--修改存储过程
--alter proc proc_name
--.........
--和创建存储过程的语法相似

--删除存储过程
--drop proc proc_name


--处理错误信息 raiserror
/*
在存储过中，使用raiserror显示用户定义的错误信息
1>临时的信息
2>只能显示给用户
3>记错错误，指定严重级别

RAISERROR ( { msg_id | msg_str } { , severity , state }
[ , argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]

参数

msg_id: 在sysmessages系统表中指定的用户定义错误信息
msg_str: 用户定义的特定信息，最长255个字符
severity:与特定信息关联，由用户定义严重级别
0 -18 用户可使用级别
19-25 sysadmin成员预留的，并需要指定with log选项
20-25 致命错误
state: 错误状态 1-127之间
option: 是否将错误记录到服务器错误日志中
*/