存储过程浅入深出
一、定义
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 表的增删改查
三、变量的声明和使用
- 用户变量:以"@"开始,形式为"@变量名"。用户变量跟mysql客户端是绑定的,设置的变量,只对当前用户使用的客户端生效
- 全局变量:定义时,以如下两种形式出现,set GLOBAL 变量名 或者 set @@global.变量名,对所有客户端生效。只有具有super权限才可以设置全局变量
- 会话变量:只对连接的客户端有效。
- 局部变量:作用范围在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='张三';