存储过程

存储过程

前言

创建：CREATE PROCEDURE 存储名 AS
执行：EXEC 存储名 @order_total OUTPUT 
删除：DROP PROCEDURE 存储名

全局变量

全局变量名称	描述
@@CURSOR_ROWS	返回连接上打开的上一个游标中的当前限定行的数目，确定当其被调用时检索了游标符合条件的行数。
@@FETCH_STATUS	返回针对连接当前打开的任何游标发出的上一条游标 FETCH语句的状态。
@@ROWCOUNT	返回受上一语句影响的行数。
@@SERVERNAME	返回运行 SQL Server 的本地服务器的名称。
@@OPTIONS	返回有关当前 SET 选项的信息。
@@LOCK_TIMEOUT	返回当前会话的当前锁定超时设置（毫秒）。
@@IDENTITY	返回上次插入的标识值。
@@ERROR	返回执行的上一个 Transact-SQL 语句的错误号，如果前一个 Transact-SQL 语句执行没有错误，则返回 0。
@@CONNECTIONS	返回 SQL Server 自上次启动以来尝试的连接数。

例子1

​ 先创建一张示例表order[order_num,order_name,order_count,order_date,
order_price,order_total]接受一个订单号@order_num和一个判断是否加营业额
税的按钮[0/1]@taxable，返回订单总额@order_total

创建存储过程

CREATE PROCEDURE ordertotal
	-- 传入局部变量@  @@全局变量
	@order_num INT,   --订单号
	@taxable BIT,   --按钮0/1
	--这个是合计结果输出的变量  用output
	@order_total MONEY OUTPUT    
AS
BEGIN
	-- 声明变量  用来存放计算的结果
	DECLARE @total MONEY;    --合计结果
	DECLARE @taxrate INT;    --营业税%
	SET @taxrate=6；         --指定营业税6%
	-- 数据集
	SELECT @total=SUM(item_price*quantity)
	FROM orderitems
	WHERE order_num=@order_num
	--判断是否需要营业税
	IF @taxable=1
		-- 计算合计＋税
		SET @total=@total+(@total/100*@taxrate);
	-- 查询结果
	SELECT @order_total=@total;
END；

测试–调用存储过程

-- 声明变量
DECLARE @order_total MONEY
-- 传入订单号和按钮1  需要营业税
EXEC ordertotal 20005,1,@order_total OUTPUT
SELECT @order_total

-- 声明变量
DECLARE @order_total MONEY
-- 传入订单号和按钮1  不需要营业税
EXEC ordertotal 20005,0,@order_total OUTPUT
SELECT @order_total

例子2

创建一个带两个参数的存储过程

示例1

create proc usp_numble
@num1 int,
@num2 int
as
begin
	select @num1*@num2
end
--调用
exec usp_numble 100,200

示例2

--创建student表
select * from student
insert into student(stu_id,stu_name,stu_age,stu_sex) values('007','泫雅','32','女');
insert into student(stu_id,stu_name,stu_age,stu_sex) values('008','千珏','17','男');
insert into student(stu_id,stu_name,stu_age,stu_sex) values('009','李钰琪','26','女');
insert into student(stu_id,stu_name,stu_age,stu_sex) values('010','剑姬','35','男');
insert into student(stu_id,stu_name,stu_age,stu_sex) values('011','貂蝉','25','女');
insert into student(stu_id,stu_name,stu_age,stu_sex) values('012','阚清子','31','女');
insert into student(stu_id,stu_name,stu_age,stu_sex) values('013','胡歌','38','男');
insert into student(stu_id,stu_name,stu_age,stu_sex) values('014','万喜','30','男');
insert into student(stu_id,stu_name,stu_age,stu_sex) values('015','李沁','28','女');

--创建usp_query存储过程
create proc usp_query
@stu_age nvarchar(10),
@stu_sex nvarchar(10)
as
begin
	-- update student set stu_age='25' where stu_age>@stu_age and stu_sex=@stu_sex
	select * from student where stu_age>@stu_age and stu_sex=@stu_sex
end

--测试
exec usp_query '20','女'
-----------------------------------
--带输出参数
create proc usp_show_student
@stu_age nvarchar(255),
@recorddata int output  --输出参数
as
begin
	select * from student where stu_age>@stu_age
	-- 将查询结果传递给@recorddata
	set @recorddata=(select count(*) from student where stu_age>@stu_age)
end

-- 调用
declare @re int
exec usp_show_student @stu_age='24',@recorddata=@re output
print @re
----------------------------------
--写一个分页查询
create proc get_student_page
@pagesize int=5,  --每页记录条数
@pageindex int=1,  --当前要查看第几页的记录
@recordcount int output,   --总的记录条数
@pagecount int output    --总的页数
as
begin
	select 
		s.stu_id,s.stu_name,s.stu_age,s.stu_sex
	from (select *,rn=ROW_NUMBER() over(order by stu_id asc) from student) as s
	where s.rn between (@pageindex-1)*@pagesize+1 and @pagesize*@pageindex
	-- 计算总的条数
	set @recordcount=(select count(*) from student)
	-- 计算总页数 ceiling()向上取整
	set @pagecount=ceiling(@recordcount*1.0/@pagesize)
end

--调用分页查询
declare @rc int,@pc int
exec get_student_page @pagesize=3,@pageindex=5,@recordcount=@rc output,@pagecount=@pc output
print @rc
print @pc

例子3

​ 程序员工资表：ProWage[字段id(int)、PName(char 10)、Wage(int)]创建一个存储过程给员工加薪。例如：如果有百分之五十的人薪水不到6000，给所有人加薪，每次加200元，直到有一半以上的人工资大于6000元。

• 建表：

--检验数据库是否存在，如果为真，删除此数据库--
IF exists(SELECT * FROM sysdatabases WHERE name='Wage')
  DROP DATABASE Wage
GO
CREATE DATABASE Wage
GO
 
--建数据表--
USE Wage
GO
CREATE TABLE ProWage  --程序员工资表
(
  ID int identity(1,1) primary key,  --工资编号
  PName  CHAR(10) NOT NULL ,     --程序员姓名
  Wage  int NOT NULL    --工资
)
GO
--插入数据--
INSERT INTO ProWage(PName,Wage)VALUES('伏羲',4900)
INSERT INTO ProWage(PName,Wage)VALUES('清月',5200)
INSERT INTO ProWage(PName,Wage)VALUES('婉儿',4800)
INSERT INTO ProWage(PName,Wage)VALUES('初一',6500)
INSERT INTO ProWage(PName,Wage)VALUES('蓝天',5700)
INSERT INTO ProWage(PName,Wage)VALUES('清风',4800)
INSERT INTO ProWage(PName,Wage)VALUES('月神',5500)
INSERT INTO ProWage(PName,Wage)VALUES('楚天',5200)

• 创建存储过程

--1、创建存储过程--
if exists (select * from sysobjects where name='Sum_wage')
drop procedure Sum_wage
GO
create procedure Sum_wage 
@PWage int,
@AWage int,
@total int
as 
while (1=1)
begin
if (select count(*) from ProWage)>2*(select count(*) from ProWage where Wage>=@PWage)
update ProWage set @total=@total+@AWage,Wage=Wage+@AWage
else
break
end
print'一共加薪：'+convert(varchar,@total)+'元'
print'加薪后的程序员工资列表：'
select * from ProWage
--调用存储过程1--
exec Sum_wage @PWage=2000,@AWage=100,@total=0
exec Sum_wage @PWage=2200,@AWage=100,@total=0
exec Sum_wage @PWage=3000,@AWage=100,@total=0
exec Sum_wage @PWage=4000,@AWage=100,@total=0
exec Sum_wage @PWage=5000,@AWage=100,@total=0
exec Sum_wage @PWage=6000,@AWage=100,@total=0
 
--2、创建存储过程2--
if exists (select * from sysobjects where name='Avg_wage')
drop procedure Avg_wage
GO
create procedure Avg_wage 
@PWage int,
@AWage int,
@total int
as 
while (1=1)
begin
if ((select Avg(Wage) from ProWage)<=@PWage)
update ProWage set @total=@total+@AWage,Wage=Wage+@AWage
else
break
end
print'一共加薪：'+convert(varchar,@total)+'元'
print'加薪后的程序员工资列表：'
select * from ProWage
--调用存储过程--
exec Avg_wage @PWage=6000,@AWage=200,@total=0
exec Avg_wage @PWage=5500,@AWage=200,@total=0

以上是存储过程的实例，详细介绍请戳我！！

最后更新： 2020年03月14日 10:46

原始链接： https://yesong17.github.io/2020/02/28/%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B/

Ys

赏

× 请我吃大餐~