存储过程浅入深出

一、定义

1、一种为了完成特定功能的一个或一组SQL语句集合。经编译后存储在服务器端的数据库中，可以利用存储过程来加速SQL语句的执行。
2、调用名称，传入参数，执行来完成特定功能。

3、分类：

• 系统存储过程：master数据库中，其他数据库中是可以直接调用，并且不必在前面加上数据库名，创建数据库时，这些系统存储过程在新的数据库中自动创建。
• 自定义存储过程：用户自己创建，特定功能而创建。可以传入参数，也可以有返回值，表明存储过程 执行是否成功。里面可以只是一个操作，也可以包括多个。

         执行：execute / exec 存储过程名 参数列表（多个参数，以逗号隔开）

  执行：
execute / exec 存储过程名 参数列表（多个参数，以逗号隔开）

优点：

• 提高应用程序的通用性和可移植性。多次调用，而不必重新再去编写，维护人员可以随时修改。
• 可以更有效的管理数据库权限。
• 提高执行SQL的速度。
• 减轻服务器的负担。

缺点：专门维护它，占用数据库空间。

二、应用

1.编写一个不带参数存储过程

--在hydata数据库中创建简单不带参的存储过程
create proc usp_select_STusers
 AS
 begin
	select * from STUsers where UserName like'%小高'    --查询STusers表
 end
 
 exec usp_select_STusers

2.编写一个带参数存储过程，实现两个数的和

 create proc usp_add_num
 @n1 int,
 @n2 int
 as
 begin
    select @n1+@n2
 end
 
 exec usp_add_num 100,300

3.创建一个带参数的存储过程、
--需要 DECLARE 声明：declare 变量名 变量类型 仅仅在定义它的 BEGIN...END 中有效，
--且在 BEGIN...END 中，只能放在第一句
 
create proc AddUserInfo
@UserName varchar(50),
@UserPwd varchar(50),
@Age int,
@DeptId int
as
begin
   declare @time datetime  --定义局部变量
   select @time ='2019-11-15'  --赋值
   insert into UserInfos(UserName,UserPwd,CreateTime,Age,DeptId) 
   values (@UserName,@UserPwd,@time,@Age,@DeptId);
   delete from UserInfos where UserId=17;
   select * from UserInfos
end
go

3.编写一个存储过程，实现根据指定的参数进行数据查询
@max
@min

create proc usp_select_score
as
begin
    select * from tableScore where tenglish between @max and @min
end

4、写一个简单的分页存储过程

create proc usp_select_table
@pagesize int=10,    --每页记录条数
@pageindex int=1,    --当前要查看第几页的记录
@count int output,    --总记录条数
@pagecout int output    --总页数
as
begin
    --1,编写查询语句，把用户要用的数据查询出来
    select STusers.name,STusers.id,STusers.age,STusers.sex,STusers.birthday
    from table    where stusers.id=11
 
    --2，计算总的记录数
    set @count=(select count(*) from stusers)
 
    --3，计算总的页数（ceiling向上取整）
    set @pagecount=ceiling(@count*1.0/pagesize)
end

5、编写一个分页存储过程，针对stusers表，通过ado.net调用该存储过程，实现分页

6、把刚才的事物转账，封装到一个存储过程中，通过ado.net调用该存储过程，实现分页

7、通过存储过程实现对stusers 表的增删改查 

三、变量的声明和使用

1. 用户变量：以"@"开始，形式为"@变量名"。用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效
2. 全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名  或者  set @@global.变量名，对所有客户端生效。只有具有super权限才可以设置全局变量
3. 会话变量：只对连接的客户端有效。
4. 局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量。declare语句专门用于定义局部变量。set语句是设置不同类型的变量，包括会话变量和全局变量

局部变量与用户变量的区分在于两点:

1.用户变量是以"@"开头的。局部变量没有这个符号。

2.定义变量不同。用户变量使用set语句，局部变量使用declare语句定义

3.作用范围。局部变量只在begin-end语句块之间有效。在begin-end语句块运行完之后，局部变量就消失了。

层次关系是：变量包括局部变量和用户变量。用户变量包括会话变量和全局变量。

Select和Set给变量赋值

• 定义的存储过程变量可以通过Set或者Select等关键字方法来进行赋值操作，
• 使用Set对存储过程变量赋值为直接赋值，
• 使用Select则一般从数据表中查找出符合条件的属性进行赋值操作。

例如，下面定义一个存储过程年龄字段@Age字段。

• Declare @Age int;
• 使用Set方式赋值的语句可写作为：Set @Age=32;

从表UserTable中查找出名字为张三的人的年龄，然后将之赋值给@Age变量，就得使用Select方式来赋值了，赋值方式如下：

Select @Age=Age FROM UserTable Where Name='张三'；