作业讲解.sql

主要介绍了sql的基础教程

--创建名为proc price_pro，功能：返回用户指定类图书的数量及其平均价格，默认为“business”类图书
create proc price_pro (@count int output,
                       @avgpri money output,
                       @type varchar(30)='business')
as
select @count=count(*),@avgpri=avg(price)
from titles
where type=@type
----------------------------------------------------------------------------------------------------------------
--调用存储过程一
declare @num int,@avg_price money
exec price_pro @num output, @avg_price output
select @num
select @avg_price
----------------------------------------------------------------------------------------------------------------
--调用存储过程二
declare @num int,@avg_price money
exec price_pro   @type='trad_cook',@avgpri=@avg_price output,@count=@num output
select @num as 'typecount'
select @avg_price 'type avgprice'
----------------------------------------------------------------------------------------------------------------


--1、创建一个存储过程，接受玩具ID并显示该玩具的名和价格编写过程
use GlobalToyz
go

if exists(select name from sysobjects where name='toy_mes' and type='p')
drop proc toy_mes
go
--创建存储过程
create proc toy_mes @toyid char(6)
as
  select 玩具名,价格 from 玩具 
  where 玩具号=@toyid
go
--调用存储过程
exec toy_mes '000002'

--第二种方法
--创建存储过程
create proc toy_nameprice_b  @toyid varchar(6)
as
 declare @text  varchar(100)
 select @text = 玩具名 + ',价格:' +cast(价格 as varchar)
 from 玩具
 where 玩具号 = @toyid
 print @text
--调用存储过程
exec toy_nameprice_b '000001'

--第三种方法
if exists(select name from sysobjects where name='toy_mes' and type='p')
drop proc toy_mes
go
--创建存储过程
create proc toy_mes @toyid char(6),
                    @toyname varchar(20) output,
                    @price money output
as
  select @toyname=玩具名,@price=价格 from 玩具 
  where 玩具号=@toyid
go
--调用存储过程
declare @name varchar(20),@pri money
exec toy_mes '000002',@toyname=@name output,@price=@pri output
select @name as '玩具名称',@pri as '玩具价格' 
----------------------------------------------------------------------------------------------------------------------

--2、创建一个称为prcCharges存储过程，返回指定定单号的装运费用和包装费用
if exists(select name from sysobjects where name='prcCharges' and type='p')
drop proc prcCharges
go
--创建存储过程
create proc prcCharges @orderid char(6)
as
select  运输价格,包装价格
  from 定单 where 定单号=@orderid
--调用存储过程
exec prcCharges '000001'
----------------------------------------------------------------------------------------------------------------------

--3、输入一个玩具品牌号，年、月份，得到该品牌的玩具在这个时间的总销售量
if exists(select name from sysobjects where name='toy_sales' and type='p')
drop proc toy_sales
go
--创建存储过程
create proc toy_sales @brandid char(6),
                      @year int,
                      @month smallint,
                      @sum int output
as
  if @month=''or @month=0
    begin
      select @sum=sum(销售数量) from 月销售情况 a,玩具 b
      where a.玩具号=b.玩具号 and b.商标=@brandid and 年份=@year
    end
  else
    select @sum=销售数量 from 月销售情况 a,玩具 b
    where a.玩具号=b.玩具号 and b.商标=@brandid 
          and 年份=@year
          and 月份=@month
--调用存储过程
declare @sumsales int
exec toy_sales @brandid='001',@year=2000,@month=2,@sum=@sumsales output
select @sumsales  
--等效查询
select sum(销售数量) from 月销售情况
where 玩具号 in(select 玩具号 
from 玩具 where 商标='001')
and 年份=2000 and 月份=2   
----------------------------------------------------------------------------------------------------------------------

--4、创建一个过程，接受一个订单的详细情况，并把这个信息输入至订单详情表。
--注：订单金额通过计算获得
 -- 如：订单详情为
 --     订单号'000026'
--      玩具号'00001'
 --     数量 10
 --     包装 Y
 --     包装号 002
 --     信息 生日快乐
 --     玩具价格  数量*单价
--需检查相应玩具的库存是否充足，如库存量不够，则就给出相应的提示，并拒绝新记录的增加，否则增加新的订单信息，并修改相应的库存量
if exists(select name from sysobjects where name='order_detail' and type='p')
drop proc order_detail
go
--创建存储过程
create proc order_detail @orderid char(6),
                         @toyid char(6),
                         @num int,
                         @pack char(1),
                         @packid char(3),
                         @info varchar(256)
as
if not exists (select * from 定单
               where 定单号=@orderid)
begin
  print'没有该定单记录，请先添加定单记录'
  return -1  --返回非0值,表示错误
end

if not exists (select * from 玩具 
          where 玩具号=@toyid and 数量>=@num)
begin
  raiserror('库存不足',16,1)
  return -1
end

declare @price money
select @price=价格 from 玩具
where 玩具号 = @toyid

--插入订单详情表
insert into 定单详情
values (@orderid,@toyid,@num,@pack,@packid,@info,@num*@price)
if @@error<>0
  return -1

--更新库存
update 玩具 set 数量 = 数量 - @num
where 玩具号 = @toyid
if @@error <> 0 
   return -1
else
   return  0 

--调用存储过程
exec order_detail '000010','000001',10,'y','002','happy birthday'

----------------------------------------------------------------------------------------------------------------------

--4、根据给定时间段，统计在这段时间内的销售情况（统计订单详情表，弱每给出终止时间，则默认为当前系统时间）
--  玩具号    销售数量   状态
--注：状态为
--    销售数量在4以上，则为‘A’
--    销售数量在3之间，则为‘B’
--    销售数量在3以下，则为‘C’
if exists(select name from sysobjects where name='sale_detail' and type='p')
drop proc sale_detail
go
--创建存储过程
create proc  sale_detail
@start_dt datetime,
@end_dt  datetime =null  --不要用: getdate(),参数必须是常量
as
declare @enddate datetime
if @end_dt is null  
select  @enddate = getdate()
--或者 select  @enddate = isnull(@end_dt,getdate())
if @enddate <=  @start_dt
begin
 raiserror ('时间段不符!起始时间不得大于终止时间',16,1)
 return 1  
end 

select 玩具号,sum(购买数量) 销售数量,
  状态 =  case 
       when sum(购买数量) >4 then 'A'
       when sum(购买数量) between 3 and 4 then 'B'
       else 'C' end
from 定单详情  a,定单 b
where a.定单号 = b.定单号 
and 定单日期 between  @start_dt and @enddate
group by  玩具号
--调用存储过程
exec sale_detail '2000-1-1'
----------------------------------------------------------------------------------------------------------------------

--5、创建一个称为prcCharges存储过程，它返回某个定单号的装运费用和包装费用
if exists(select name from sysobjects where name='prcCharges' and type='p')
drop proc prcCharges
go
--创建存储过程
create proc prcCharges @orderid char(6),
                       @shipcharge money output,
                       @packcharge money output
as
  select @shipcharge=运输价格,@packcharge=包装价格
  from 定单 where 定单号=@orderid
go
--调用存储过程
declare @shipchar money,@packchar money
exec prcCharges '000001',@shipcharge=@shipchar output,@packcharge=@packchar output
select @shipchar as '运输价格',@packchar as'包装价格' 
----------------------------------------------------------------------------------------------------------------------

--6、创建一个存储过程，它接收定单号并返回该定单的总费用（装运费+包装费+所购玩具费用）(调用prccharges获得装运费及包装费)
if exists(select name from sysobjects where name='ord_charge' and type='p')
drop proc ord_charge
go
--创建存储过程
create proc ord_charge @orderid varchar(6)
as
declare @shipchar money,@packchar money,@total money
exec prcCharges @orderid,@shipchar output,@packchar output
select @total = @shipchar + @packchar 
  + sum(玩具价格)
from 定单详情
where 定单号 = @orderid
select @total
print cast(@total  as varchar)
--调用存储过程
exec ord_charge  '000001'
----------------------------------------------------------------------------------------------------------------------

--7、创建一个过程prcnewid，返回一个品牌新代号（为最大品牌号+1）
if exists(select name from sysobjects where name='prcnewid' and type='p')
drop proc prcnewid
go
--创建存储过程
create proc prcnewid @newid varchar(10) output
as
 declare @maxbrand int
 select @maxbrand = cast(max(商标号) as int)
 from  商标

 select @maxbrand = @maxbrand  + 1 
 select @newid=
 case
   when @maxbrand<10 then '00'+cast(@maxbrand as varchar)
   when @maxbrand between 10 and 99 then '0'+cast(@maxbrand as varchar)
   else cast(@maxbrand as varchar)
   end
--调用存储过程
declare @nid varchar(10)
exec  prcnewid @nid  output
select @nid
----------------------------------------------------------------------------------------------------------------------

--8、创建一个过程prcinsert，添加一个品牌信息（代号通过调用PRCNEWID产生），若添加成功，则返回0否则返回1
if exists(select name from sysobjects where name='prcinsert' and type='p')
drop proc prcinsert
go
--创建存储过程
create proc prcinsert @brandname varchar(50)
as
 declare @brandid int
 exec  prcnewid @brandid output
 insert  into 商标 values (@brandid,@brandname)
if @@error = 0
  return 0 
else 
  return 1
--调用存储过程
declare @return int
exec @return=prcinsert '米奇'
print @return