剖析SQLsever之sqlsever存储过程_01

1、前言

以下介绍，大部分来自度娘。
https://baike.baidu.com/item/%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B/1240317?fr=aladdin

1.1、概念

存储过程，Stored Procedure，是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程将常用的或者负责的工作（即完成特定功能）预先用SQL语句集写好存放在数据库中，当日后需要执行此功能时，只需要用execute调用改sql语句集即可自动完成命令。

（是不是有一种定义全局函数的感觉呢？）

1.2、种类

• 1系统存储过程
  主要存储再master数据库中，以sp_开头，用来进行系统的各项设定.取得信息.相关管理工作。

常见系统存储过程：

exec sp_databases; --查看数据库
exec sp_tables;        --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句 经常用到这句话来查看存储过程，like sp_helptext sp_getLoginInfo.
exec sp_rename student, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;

• 2本地存储过程
  用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。

• 3临时存储过程
  分为两种存储过程：

• • 一是本地临时存储过程，以井字号(#)作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它;
• • 二是全局临时存储过程，以两个井字号(##)号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。

• 4远程存储过程
  在SQL Server2005中，远程存储过程(Remote Stored Procedures)是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。

• 5扩展存储过程
  扩展存储过程(Extended Stored Procedures)是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。

.例如：查看数据库所在机器操作系统参数

exec master..xp_msver 

2.基本语法

2.1、创建存储过程参数讲解

1、procedure_name：
新存储过程的名称，须符合语法规则，对于数据库及其所有者必须唯一。

• 创建局部临时过程：在前面加上一个#，例如：#procedure_name
• 创建全局临时过程：在前面加上两个#，例如：##procedure_name

2、;number
可选整数，用来对同名的过程分组。

3、@parameter
过程中的参数，以@开头，每个过程的参数仅用于该过程本身（类似于局部变量），两个@@开头为全局变量。在create创建存储过程时，可以声明一个或多个参数，用户必须在执行过程中提供每个所声明参数的值（除非参数定义了默认值），最多可有2100个参数。

4、data_type
参数的数据类型，包括text、ntext、int、image、nvarchar等等
cursor只能用作output参数，当数据类型为cursor时，必须同时指定varying和output关键字。

5、VARYING
指定作为输出参数支持的结果集，仅适用于游标参数。
6、default
参数的默认值，默认值必须是常量或null，党对参数值使用like关键字时，那么默认值中可以包含通配符（%、_、[ ] 和 [^]）。
7、OUTPUT
表明参数是返回参数。可将参数返回给EXEC[UTE] 调用过程。
text、ntext和imag参数可用作OUTPUT参数。使用OUTPUT关键字的输出参数可以是游标占位符。
8、,…n
表示可指定n个参数（最多2100个）。
9、

10、FOR REPLICATION
指定不能在订阅服务器上之行为复制创建的存储过程。
11、AS
制定过程中要执行的操作
12、sql_statement
过程中要包含的任意数目和类型的Transact-SQL语句。

2.2、调用存储过程

存储过程可以在三种环境下被调用：

• command命令下，基本语法为：exec sp_name [参数名]；

EXEC sp_name 参数1，参数2，……

EXEC sp_name 参数1,=值1，参数2=值2……

• SQL环境下，基本语法为：call sp_name [参数名]；
• PL/SQL环境下，基本语法为：begin sp_name [参数名] end；

2.3、删除存储过程

• 1.基本语法：

drop procedure sp_name

• 2.注意事项
  (1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

2.4、其他常用命令

• 1.show procedure status
  显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等
• 2.show create procedure sp_name
  显示某一个mysql存储过程的详细信息
• 3、exec sp_helptext sp_name
  显示你这个sp_name这个对象创建文本

3、变量

以下大多来自于链接：https://www.cnblogs.com/lideng/archive/2013/04/11/3014407.html

3.1、变量声明-declare

在sql中我们使用关键词declare定义变量，并且使用标记符号（@）表示是变量。
局部变量是@开头，全局变量是@@开头的。

Declare @Local_Var data_type

--@Local_Var 是定义的变量名
-- data_type变量的数据类型

--举例子：

-- declare @a=1 varchar(20) --语法错误
declare @b varchar(20)=2
print @b --在窗口中打印出变量
---------
2

3.2、变量赋值–set/select

--2.2.、变量的赋值--set/select
declare @a varchar(20)
set @a=3			--赋值
print 'set='+@a
select @a=4
print 'select='+@a	--赋值

-----------
set=3
select=4

3.3、变量运算–set/select

加减法

--2.3、变量运算
set @a=@a+1
print 'set @a=@a+1='+@a
select @a=@a+1
print 'select @a=@a+1='+@a
--以下两种运算方式仅有select可以
select @a=(select 1+5)
print @a
select @a=(select 1-@a)
print @a

-----------
set @a=@a+1=5
select @a=@a+1=6
6
-5

乘除法

--乘除法
set @a=3
print @a
select @a=2*@a
--set @a=2*@a
print '*='+@a
select @a=@a/2
--set @a=@a/2
print '/='+@a

---------
3
*=6
/=3

模运算

--模%运算
declare @b int
set @b=(10%3)
print @b
select @b=(10%2)
print @b

---------
1
0

3.4、set/select区别

1、表达式返回多个值

--表达式返回多个值时，使用 SET 赋值
declare @name varchar(128)
set @name=(select username from userinfo)
print @name

/*
--出错信息为：
消息 512，级别 16，状态 1，第 79 行
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
*/

-- 表达式返回多个值时，使用 SELECT 赋值
declare @name varchar(20)
select @name= userName from userinfo
print @name --结果集中最后一个 username 列的值
-----
输出结果：
admin02

2、表达式未返回值

--表达式未返回值时，使用 SET 赋值
declare @name varchar(20)
set @name='jack'
set @name= (select username from userinfo where username='not')
print @name  --Null值

结果
--------

--表达式未返回值时，使用 SELECT 赋值
declare @name varchar(20)
set @name='jack'
select @name=username from userinfo where username='not'
print @name  --jack,保存原来的值

结果
-------
jack

4、流程控制

例子参考：
https://www.cnblogs.com/BrokenIce/p/5713225.html
https://www.cnblogs.com/lideng/archive/2013/04/12/3016583.html

关于流程控制我在网上看见的大致分为三类：顺序、选择、循环

顺序的话，类似于我们上面执行的都是顺序结构，即按照语句顺序执行。

4.1、选择

4.1.1、if-[else if]-if

--if
declare @id int
set @id=5
if(@id=1)
	print 'right'
else if(@id=0)
	print 'error'
else
	print 'default'
------
输出结果：
default

4.1.2、case-when-then

declare @today int,@week nvarchar(10)
set @today=3
set @week=case 
	when @today=1 then 'monday'
	when @today=2 then 'Tuesday'
	else 'default'
end

print @week

---
default

4.2、循环-while

计算1到100的和

--计算1到100的和
declare @a int,@sum int
set @a=0
set @sum=0
while @a<=100 --循环开始
begin
	set @sum=@sum+@a
	set @a=@a+1
end
print @sum

-------
5050