SQL存储过程、视图

存储过程：

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

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

CREATE PROCDUER|PROC <sproc name>

    [<parameter name>[schema.]<data type>[VARYING][=<default value>][OUT [PUT]][,

    [<parameter name>[schema.]<data type>[VARYING][=<default value>][OUT [PUT]][,

    ...]]

[WITH

    RECOMPILE|ENCRYPTION|[EXECUTE AS {CALLER|SELF|OWNER|<'user name'>}]

[FOR REPLICATION]

AS

    <code>|EXTERNAL NAME <assembly name>.<assembly class>

存储过程示例一：

执行存储过程方法一：

 

执行存储过程方法二：

 

      上面说过，存储过程可以定义返回值。

示例：

 

修改存储过程示例：

 

利用存储过程查找三个表内的信息示例：

 

示例：

 

示例二：

练习：

要求;写一个存储过程，买东西，自动添加小票进入。

1.我又没有这个商品？？？
2.你买的数量在我店里能不能够？？？

create database lianxi120
go
use lianxi120
go
CREATE table mendian
(
    scode int primary key identity(101,1),
    sname varchar(18),
    sshu int,
    sprice decimal(18,2),
    sgong int,
)
CREATE table gongying
(
    gcode int primary key identity(1,1),
    gname varchar(18),
    glian varchar(18),
    gtel decimal(18,0)
)
CREATE table xiaopiao
(
    pcode int primary key identity(11,1),
    pname varchar(18),
    pprice decimal(18,2),
    pshu int,
    pzong decimal(18,2),
    ptime datetime,
)
insert into mendian values('石油',20,70,1)
insert into mendian values('毒品',100,2000,2)
insert into mendian values('M4-S',70,16000,2)
insert into mendian values('天空套',1000,5,3)
insert into mendian values('iPhone7',7000,5,4)
insert into mendian values('兰博基尼',5,4500000,5)
insert into mendian values('绿茶婊',200,3000,5)

insert into gongying values('沙特王国','土喀拉耶鲁斯基',98876767675)
insert into gongying values('金三角','迪迦',09783356782)
insert into gongying values('DNF','马化腾',06783357524)
insert into gongying values('全球iPhone直营店','乔布斯',18666666666)
insert into gongying values('三里屯','Licuy',18678199999)

select*from mendian
select*from gongying

create proc chaoshi
as
begin
select*from mendian
select*from gongying
select*from xiaopiao
end
go
exec chaoshi

create proc guanli
@shangpin int, @shuliang int
as
begin
declare @sp int
--count（）：统计你查询出来的记录数 
select @sp=COUNT(*) from mendian where scode =@shangpin
if @sp =1
     begin 
       declare @sl int
       select @sl = sshu from mendian where scode =@shangpin
       if @sl>@shuliang
             begin
                  declare @sn varchar(18)
                  select @sn = sname from mendian where scode =@shangpin
                  declare @spr decimal(18,2)
                  select @spr = sprice from mendian where scode =@shangpin
                  declare @zong decimal(18,2)
                  set @zong =@spr * @shuliang
                  declare @time datetime
                  set @time=getdate()
                  insert INTO xiaopiao VALUES(@sn,@spr,@sl,@zong,@time)
                  update mendian SET sshu=@sl-@shuliang where scode=@shangpin
             end
             begin
                  declare @gy varchar(18)
                  select @gy=gname from gongying where gcode=(SELECT sgong from mendian where scode=@shangpin)
                  declare @gname varchar(18)
                  select @gname=glian from gongying where gcode=(SELECT sgong from mendian where scode=@shangpin)
                  declare @gtel decimal(18,0)
                  select @gtel=gtel from gongying where gcode=(SELECT sgong from mendian where scode=@shangpin)
                  print '商品数量不足，请联系供应商'+@gy+',联系人：'+@gname+',联系电话:'+cast(@gtel as varchar(18))
             end 
     end              
 else
     begin
           print'查无此商品'
     end
end 
GO

update mendian set sshu=100 where scode=103
select * from mendian
select * from xiaopiao
exec guanli 103,10