5.SQL数据库存储过程操作

本篇文章旨在帮助大家熟练掌握SQL数据库存储过程操作，加深大家对SQL数据库存储过程的理解.

参考表格属性

见第一篇文章
2.SQL查询语句的操作和练习

仓库管理类的操作

1）插入一个新的供应商元组

CREATE PROC 插入供应商
@GYSNO char(8),
@GYSNAME Varchar(40),
@GYSdz Varchar(40) 
AS
INSERT 供应商 VALUES (@GYSNO,@GYSNAME,@GYSdz)

运行

EXEC 插入供应商 S10,土拨鼠工厂,青岛

2）给指定城市的所有仓库增加X m²的面积

CREATE PROC 面积加
@MJ int,
@DZ Varchar(40)
AS
UPDATE 仓库
SET 面积=面积+@MJ
WHERE 城市=@DZ片

运行

EXEC 面积加 55,上海

3）检索某职工发给某供应商的订购单信息\

CREATE PROC 检索
@ZGNO char(8),
@GYSNO char(8)
AS
SELECT *
FROM 订购单
WHERE 职工号=@ZGNO AND 供应商号=@GYSNO

运行

EXEC 检索 E3,S3

4）检索在某城市工作并向某供应商发了订购单的职工号

CREATE PROC 检索职工
@DZ Varchar(40),
@GYSNO char(8)
AS
SELECT 职工号
FROM 仓库 JOIN 职工
ON 仓库.仓库号=职工.仓库号
WHERE 城市=@DZ AND 
职工号 IN 
(SELECT 职工号 FROM 订购单 WHERE 供应商号=@GYSNO)

运行

EXEC 检索职工 上海,S4

产品销售类的操作

a)查询单价范围在X元到Y元范围内的产品信息

CREATE PROC 查询单价
@min money,
@max money
AS
SELECT *
FROM 产品
WHERE 单价>=@min AND 单价<=@max

运行

EXEC 查询单价 5,15

b)查询在某日期之后签订的订购单的客户信息（客户的名称、联系人和电话号码）、订单号和订购日期

CREATE PROC 日期之后
@time datetime
AS
SELECT 订单号,订购日期,客户.客户名称,客户.联系人,客户.电话号码
FROM 订购单 JOIN 客户
ON 订购单.客户号=客户.客户号
WHERE 订购日期 >@time

运行

EXEC 日期之后 '2008-04-05'

c)更新操作，将某产品的订购日期统一修改为一个指定日期

CREATE PROC 修改日期
@name varchar(40),
@time datetime
AS
UPDATE 订购单
SET 订购日期=@time
WHERE 订单号 IN
(SELECT 订单号 FROM 订购单明细 WHERE 产品号 IN
(SELECT 产品号 FROM 产品 WHERE 产品名称=@name))

运行

EXEC 修改日期 面包,'2998-04-05'

进阶!自动生成编号

在简单练习了SQL的存储过程的操作以后,我们来练习一些高阶程序,做一些更强的功能.

首先,定义一个自动编号数据库

CREATE DATABASE 自动编号

建立一个BOOK表

USE 自动编号
CREATE TABLE BOOK
(BNO char(5)PRIMARY KEY CHECK(BNO LIKE '[0-9][0-9][0-9][0-9][0-9]'),
BNAME Varchar(40))

再创建一个出库单的表

CREATE TABLE 出库单
(出库单号 char(9)PRIMARY KEY CHECK(出库单号 LIKE '[E][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
产品编号 char(4),
数量 int,
单价 money)

提示,两个表中的数据,随意加入几条即可.

我们令出库单号的规则为：诸如"E06110001" ，其中E是固定字符，0611是系统日期（年月），0001是该月的订单批次（依次累加）

那么我们根据需求编写自动生成出库单号的存储过程

方法1

CREATE PROC AUTO_入库
@ROW_ID CHAR(9) OUTPUT
AS
DECLARE @OLDNUM INT,@NEWNUM INT
SELECT @OLDNUM=COUNT(*) FROM 出库单
IF @OLDNUM=0  
  SET @NEWNUM=1
ELSE
  BEGIN
     SELECT @OLDNUM=MAX(STR(SUBSTRING(出库单号,6,9))) FROM 出库单
     SELECT @NEWNUM=@OLDNUM+1
  END
SET @ROW_ID='E'+REPLACE(STR(MONTH(GETDATE()),2),' ','0')+REPLACE(STR(DAY(GETDATE()),2),' ','0')+REPLACE(STR(@NEWNUM,4),' ','0')
DECLARE @ROW_ID CHAR(9)

EXEC AUTO_入库 @ROW_ID OUTPUT
INSERT 出库单  VALUES(@ROW_ID ,'0001',300,5)

方法2

CREATE PROC 自动入库
@NN char(4),
@N int,
@M money
AS
DECLARE @OLDNUM INT,@NEWNUM INT
SELECT @OLDNUM=COUNT(*) FROM 出库单
IF @OLDNUM=0  
 SET @NEWNUM=1
ELSE
  BEGIN
     SELECT @OLDNUM=MAX(STR(SUBSTRING(出库单号,6,9))) FROM 出库单
     SELECT @NEWNUM=@OLDNUM+1
  END
INSERT 出库单
VALUES('E'+REPLACE(STR(MONTH(GETDATE()),2),' ','0')+REPLACE(STR(DAY(GETDATE()),2),' ','0')+REPLACE(STR(@NEWNUM,4),' ','0'),@NN,@N,@M)