SQL储存过程练习题

仓库号城市面积
WH1北京3700
WH2上海5000
WH3广州2000
WH4武汉4000
WH5北京3460
WH6珠海3400
WH7珠海5200
WH8广州6300
仓库号职工号工资
WH2E11220
WH1E32210
WH2E44250
WH3E61230
WH1E72250
WH1E81700
WH2E103000
WH3E114500
WH6E122300
WH2E133400
WH3E144500
WH6E154000
WH2E183600
职工号供应商号订购数量订购日期
E3S7672010-12-4
E1S4732010-4-1
E7S4762010-4-2
E6S67720010-1-21
E3S47920010-10-15
E1S6802009-2-1
E3S6902010-3-12
E3S3912009-3-2
E10S3932010-10-26

|:—|:—|:—|
|S3|振华电子厂| 西安|
|S4|华通电子公司| 北京|
|S6|607|厂郑州|
|S7|爱华电子厂| 北京|

  1. 基于以上的数据库，创建一个名为myproc的存储过程。该存储过程的功能是从职工表中查询哪些仓库的职工人数在3人以上，列出这些仓库的基本信息。
    1
    2
    3
    4
    5
    6
    create proc myproc
    as
    select * from ck where 仓库号 in
    (select 仓库号 from zg
    group by 仓库号
    having count(职工号)>3)
    1
    exec myproc
  2. 定义具有参数的存储过程。在以上数据库中，创建一个名为Myproc2的存储过程，该存储过程的功能是向仓库表中插入一条记录，新记录的值由参数提供。
    1
    2
    3
    4
    create proc Myproc2
    @ckh char(20),@city char(20),@mji int
    as
    insert into ck values(@ckh,@city,@mji)
    1
    exec Myproc2 'WH33','新会','3000'
  3. 定义带返回值的存储过程。在以上数据库中，创建一个名为Myproc3的存储过程，该存储过程的功能是从表中根据供应商号查询对应的供应商名和地址。
    1
    2
    3
    4
    alter proc Myproc3
    @gysh char(20)
    as
    select 供应商名,地址 from gys where 供应商号[email protected]
    1
    exec Myproc3 'S3'
1
2
3
4
5
Create table  雇员表(
雇员编号 char(10),
雇员姓名 char(20),
年龄 int
)
1
2
3
ALTER TABLE 雇员表
ADD  CONSTRAINT  PK_EMP
PRIMARY KEY （雇员编号）
1
2
3
ALTER TABLE 工作表
ADD  CONSTRAINT  PK_JOB
PRIMARY KEY （工作编号）
1
2
3
4
5
Create table  雇员表(
雇员编号 char(10) not null,
雇员姓名 char(20),
年龄 int
)
1
2
3
ALTER TABLE 雇员表
ADD  CONSTRAINT  PK_EMP
PRIMARY KEY (雇员编号)
1
2
3
ALTER TABLE 雇员表
ADD  CONSTRAINT  check_age
check (年龄>=18 and 年龄<=60)
1
2
3
4
5
6
7
create proc p6
@bianhao char(10)
as
delete from 雇员表
where 雇员编号[email protected]
exec  p6 'q1'
select * from 雇员表
1
select * from ck
1
2
3
4
5
6
create proc p7
@chengshi char(20),@geshu int output
as
select @geshu=COUNT(仓库号)
from ck
where 城市[email protected]
1
2
3
declare @jieshou int
exec p7 '珠海',@jieshou output
print @jieshou
1
2
3
4
5
create proc p8
@ckhao char(10),@city char(10),@mianji float
as
insert into ck
values (@ckhao,@city,@mianji)
1
exec p8 'WH10','增城',1000
1
select * from ck

存储过程使用事务

实例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Create Procedure  MyProcedure
AS
Begin
Set    NOCOUNT    ON;
Set XACT_ABORT ON;
begin   tran  ok  --开始一个事务OK
delete  from  rxqz  where qz=   'rx015 ' --删除数据
save   tran  bcd   --保存一个事务点命名为bcd
update  sz  set   name='李丽s' where name= '李丽'--修改数据
if  @@error<>0  --判断修改数据有没有出错
begin --如果出错
rollback   tran  bcd  -- 回滚事务到BCD 的还原点
commit   tran  ok  --提交事务
end
else  --没有出错
commit  tran ok --提交事务
End