存储过程和触发器简介

存储过程：

存储过程（stored procedure）有时也称为sproc。存储过程存储于数据库中而不是在单独的文件中，有输入参数、输出参数以及返回值等。

在数据库中，创建存储过程和创建其他对象的过程一样，除了它使用的AS关键字外。存储过程的基本语法如下：

create proc hah

as

begin

end

go

exec hah

触发器：

在 deleted 和 inserted 表保存了可能会被用户更改的行的旧值或新值。

对于INSERT 操作，inserted保留新增的记录，deleted无记录

对于DELETE 操作，inserted无记录，deleted保留被删除的记录

对于UPDATE操作，inserted保留修改后的记录，deleted保留修改前的记录

例题：

create database chaoshiguanlixitong
go
use chaoshiguanlixitong
go
create table gongyingshang
(
    gcode int primary key identity(1001,1),
    gname varchar(20),
    gtel varchar(20),
    gadd varchar(50)
)
create table cangku 
(
    ccode int primary key identity(100001,1),
    cname varchar(20),
    cshu int,
    cjin decimal(18,2),
    cgcode int
)
create table chaoshi
(
    cccode int primary key,
    ccname varchar(20),
    ccshu int,
    cshou decimal(18,2)
)
go
insert into gongyingshang values('供应商a','12345678901','地址a')
insert into gongyingshang values('供应商b','12345678902','地址b')
insert into gongyingshang values('供应商c','12345678903','地址c')
insert into gongyingshang values('供应商d','12345678904','地址d')
insert into gongyingshang values('供应商e','12345678905','地址e')
go
insert into cangku values('商品a',1000,18.5,1001)
insert into cangku values('商品b',2000,14.5,1002)
insert into cangku values('商品c',3000,12.5,1003)
insert into cangku values('商品d',4000,17.5,1004)
insert into cangku values('商品e',5000,10.5,1005)
go
insert into chaoshi values(100001,'商品a',100,100.5)
insert into chaoshi values(100002,'商品b',200,50.5)
insert into chaoshi values(100003,'商品c',50,200.5)
insert into chaoshi values(100004,'商品d',400,1000.5)
insert into chaoshi values(100005,'商品e',1000,80)
go
--1.跟苹果公司建立了合作，标号为1006，电话是88888888，地址是美国
insert into gongyingshang values('苹果','88888888','美国')
go
--2.新进iPhone6s和iPad5 各十部，进价3000,3500，拿到超市各两部，售价4000,4500
insert into cangku values('iPhone6s',8,3000,1006)
insert into cangku values('iPad5',8,3500,1006)
go
insert into chaoshi values(100006,'iPhone6s',2,4000)
insert into chaoshi values(100007,'iPad5',2,4500)
go
--3.卖掉可口可乐10瓶，
--（需要判断超市的货够不够，若不够加上仓库的货看看够不够，
--若够就卖掉，拿2瓶放在超市，若不够有多少卖掉多少，通知可口可乐公司进货）
insert into gongyingshang values('可口可乐','9999999999','美国')
insert into cangku values ('可口可乐',0,1,1007)
insert into chaoshi values(100008,'可口可乐',3,5)
go
declare @a int
declare @b int 
declare @c int
select @a = ccshu from chaoshi where cccode = 100008
select @b = cshu from cangku where ccode = 100008
set @c = @a + @b
if @a < 10
begin
    if @c < 12
    begin
        print '卖光了没货了要货'
        update cangku set cshu = 0 where ccode = 100008
        update chaoshi set ccshu = 0 where cccode = 100008
    end    
    else
    begin
        print '够了可以卖'
        update cangku set cshu = (@a + @b - 12) where ccode = 100008
        update chaoshi set ccshu = 2 where cccode = 100008
    end    
end
else
begin
    print '够了直接卖'
    update chaoshi set ccshu = @a - 10 where cccode = 100008
end
go
--4.跟1001公司关系破裂，所有有关产品全部退货
delete from chaoshi where cccode in (select ccode from cangku where cgcode = 1001)
delete from cangku where cgcode = 1001
delete from gongyingshang where gcode = 1001
go
--5.100003号商品新进30个
update cangku set cshu = (select cshu from cangku where ccode = 100003) + 30 where ccode = 100003
--6.打印小票
create table xiaopiao 
(
    cccccode int primary key identity(1000001,1),
    xname varchar(20),
    xnum int,
    danjia decimal(18,2),
    zongjia decimal(18,2)
)
go
create proc dayin
@shu int,
@hao int
as
begin
    declare @a int
    declare @b int
    declare @c varchar(20)
    declare @d decimal(18,2)
    declare @e decimal(18,2)
    select @a = ccshu from chaoshi where cccode = @hao
    select @b = cshu from cangku where ccode = @hao
    select @c = ccname from chaoshi where cccode = @hao
    select @d = cshou from chaoshi where cccode = @hao
    set @e = @d * @shu
    if @a + @b < @shu
    begin
        print '货不够'
    end
    else
    begin
        insert into xiaopiao values(@c,@shu,@d,@e)
    end
end
go
exec dayin 100,100002
select * from xiaopiao