21 T-SQL存储过程

存储过程：能完成特定功能的一个或一组SQL语句集合。经编译后存储在服务器端的数据库中。

作用：利用存储过程来加速SQL语句的执行，通过调用存储过程的名称，传入参数来执行特定的功能

分类：存储过程分为系统存储过程和自定义存储过程

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

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

优点：

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

缺点：

• 需要人专门维护，占用数据库空间

存储过程的语法：

create proc 存储过程名
@userid int,
@username varchar(50)
as
begin
 --T-SQL 语句
end
go
--创建一个无参数的存储过程 create/alter/drop
create proc searchUserinfos
as
begin
  select userid,username,age from vUserinfos
  select * from DeptInfos
end
go
--调用存储过程
exec searchUserinfos
--创建一个带参数的存储过程
create proc AddUserInfo
@UserName varchar(50)
@UserPwd varchar(50)
@Age int
@DeptId int
as
begin
  declare @time datetime
  set @time='2021-1-9'
  insert into Userinfos(UserName,UserPwd,CreatTime,Age,DepId)
  values(@UserName,@UserPwd,@time,@Age,@DeptId)
  select * from UserInfos
end
--调用存储过程
exec AddUserInfo 'lqq','123123',22,3
--循环插入
--创建一个带参数的存储过程
create proc addOper
@num int --循环次数
@operID int  --操作员id 100002
as
begin
  while @num > 1000
    begin 
    INSERT INTO kbssacct.dbo.OPERATOR
    (OP_CODE, OP_CLS, OP_STATUS,
     EMPLOYEE_ID, INT_ORG, OPEN_DATE, CHANNELS,
     REMARK, CLOSE_DATE, EXEMPT_TIME, SPECIAL_RIGHTS,
     OPER_ORG, OPER_EXT_ATTR)
     VALUES (@operID + 1, N'0', N'0', N'测试操作员'+@operID, 7551, 20020419,
             N'@', N'', 0, N'1900-01-01 00:00:00.000', N'', 0, N'0');
     @num = @num + 1
    end
end
--调用存储过程
exec addOper 1000,100002