SQL Server 第四章 存储过程（Procedure）,触发器（Trigger）,数据完整性（Data Integrity）

use electric
go

--变量
--局部变量的声明格式
--declare @局部变量名 数据类型
--局部变量赋值
declare @littlepage int
set @littlepage=18
print cast(@littlepage as varchar(255))

declare @little varchar(255)
select @little=warehouse from Stock
print @little

--
--存储过程
--

--if else 语法
--在电力抢修工程数据库中，如果stock表中存在库存量低于1的物资，就显示文本：the amount is not enough；否则显示所有物资信息。
if exists (select * from stock where amount < 1)
    print 'the amount is not enough'
else
    begin
        select * from stock
    end
    
--while语法
--将stock表中所有物资单价增加10%，直到有一个物资单价超过15000或单价总和超过50000为止。
while (select sum(unit) from stock) <50000
begin
    update stock set unit=unit*1.1
    if exists(select * from stock where unit>15000)
      break
    else
      continue
end
    
--wait delay/time 语法
WAITFOR DELAY '00:00:05'
   SELECT * FROM stock
WAITFOR TIME '11:15:20'
   SELECT * FROM salvaging

--case语法
/*用CASE语句格式一实现：在对stock表的查询中，当仓库号的值是“供电局1号仓库”、“供电局2号仓库”
、“供电局3号仓库”时分别返回“北京”、“上海”、“广州”，否则返回“未知”。*/
select mat_no,mat_name,speci,amount,unit,total, 
       warehouse=
       case warehouse
            when '供电局1#仓库' then '北京'
            when '供电局2#仓库' then '上海'
            when '供电局3#仓库' then '广州'
            else '未知'
       end
from stock

--创建一个简单的存储过程，用于返回stock表中的所有记录。
CREATE PROCEDURE exp1
AS
SELECT * FROM  stock

exec exp1

--创建一个存储过程，通过输入的仓库名称显示出该仓库的所有库存物资信息。 
create procedure exp2
@ckmc varchar(255)
as
select * from stock where warehouse=@ckmc

exec exp2 '供电局1#仓库'

--创建一个带输入参数的存储过程，向stock表中添加一个新的数据行。
create procedure exp3
@mat_no varchar(255),@mat_name varchar(255),@speci varchar(255),@warehouse varchar(255),@amount int,@unit decimal(8,2),@total decimal(8,2)
as
insert stock values(@mat_no,@mat_name,@speci,@warehouse,@amount,@unit,@total)

exec exp3 'm014','护套绝缘','BVV-130','供电局5#仓库',500,100.5,100020.00

-- 为了确保Create命令能成功执行，可以在Create Procedure之前执行如下语句：
IF EXISTS (SELECT name FROM sysobjects WHERE name='exp3' and type='P')
    DROP PROCEDURE exp3
GO 

--创建一个带默认参数的存储过程，通过传递的参数显示物资的名称、规格、项目名称、
--是否按期完工等信息，如果没有提供参数，则使用预设的默认值。
CREATE PROCEDURE exp4 
@mname varchar(50)='%绝缘%', @pno int=20100001
AS
SELECT mat_name,speci,prj_name,prj_status
FROM  stock, salvaging, out_stock
WHERE stock.mat_no=out_stock.mat_no
  and salvaging.prj_no=out_stock.prj_no
  and mat_name like @mname 
  and salvaging.prj_no=@pno
  
exec exp4 

--创建一个存储过程，求某个抢修工程领取物资的总数量。

create procedure exp5
@pn varchar(255),@sum int output
as
select @sum=sum(amount) from Out_Stock WHERE prj_no=@pn

DECLARE @total int 
EXECUTE exp5 '20100001', @total OUTPUT
PRINT '该项目领取物资总量为：'+ CAST(@total AS varchar(20))

--23页ppt
--创建一个存储过程，根据输入的工程部门及起始时间段，统计
--汇总该部门在对应时间段内所参与抢修的工程项目总数以及领
--取物资的总成本，并要求输出

CREATE PROCEDURE exp6
    @department varchar(50),@start_date datetime,@end_date datetime, @count_prj int OUTPUT, @sum_cost decimal(18,2) OUTPUT  
As
    SELECT @count_prj=COUNT(salvaging.prj_no),
    @sum_cost=SUM(Out_stock.amount*Stock.unit)
    FROM salvaging,out_stock,stock 
    WHERE out_stock.prj_no =salvaging.prj_no 
    and out_stock.mat_no =Stock.mat_no 
    and department =@department 
    and get_date between @start_date and @end_date

DECLARE @prjcounts int,@sumcosts decimal(18,2)
EXEC exp6 '工程2部','2011-1-1','2011-1-31',@prjcounts OUTPUT,@sumcosts OUTPUT
PRINT '该部门参与抢修工程项目'+ CAST(@prjcounts AS varchar(20))+'个，总成本为'+CAST(@sumcosts AS varchar(20))