实验六 存储过程，altertable

实验六 存储过程

第1关：增加供应商相关列sqty

use demo;

#代码开始

#在S表中增加一列供应零件总数量（sqty），默认值为0。

altertable s add sqty intdefault0;

#代码结束

desc s;

第2关：定义、调用简单存储过程

use demo;

#代码开始

#1、定义简单存储过程：计算所有供应商供应零件总数量并修改供应商相关列sqty。

DELIMITER //

createprocedure proc\_1()--in p\_sno char(2)--

begin

   update s set sqty =(selectsum(qty)from spj where spj.sno = s.sno);

end//

#2、调用存储过程。

call proc\_1();

#代码结束

select\*from s;

第3关：定义、调用带参数存储过程（1）

use demo;

#代码开始

#1、定义带参数存储过程：查询返回指定供应商的供应零件总数量。

dropprocedureifexists proc\_b;

DELIMITER $$

createprocedure proc\_b(IN p\_sno char(2),OUT p\_sqty int)

begin

    selectsum(qty)into p\_sqty from spj where sno = p\_sno;

end$$

#2、调用带参数存储过程。

#以供应商S1为参数，调用存储过程，将结果存入@sqty1

call proc\_b('S1', @sqty1);

-- set @sqty1 = 1100;

-- select @sqty1;

#以供应商S2为参数，调用存储过程，将结果存入@sqty2

call proc\_b('S2', @sqty2);

-- set @sqty2 = 2000;

-- select @sqty2;

#代码结束

select @sqty1,@sqty2

第4关：定义、调用带参数存储过程（2）

use demo;

#代码开始

#1、定义带参数存储过程：插入一个指定供应商信息 ('S6','泰欣',40,'十堰')（所有信息由参数提供）。

DELIMITER $$

CREATEPROCEDURE proc\_insert (

p\_sno CHAR ( 2 ), -- 供应商编号

p\_sname VARCHAR ( 10 ), -- 供应商名称

p\_status INT, -- 供应商状态

p\_city VARCHAR ( 10 ) -- 供应商城市

)

BEGIN

DECLARE v\_count INT; -- 声明一个变量用于存储供应商数量

SELECTCOUNT(\*) INTO v\_count FROM s WHERE sno = p\_sno; -- 查询是否存在相同编号的供应商

IF v\_count \>0THEN-- 如果存在，提示错误信息

SELECT'供应商已存在，请重新插入！';

ELSEIF v\_count =0THEN-- 如果不存在，插入新的供应商信息

INSERTINTO s (sno, sname, status, city) VALUES (p\_sno, p\_sname, p\_status, p\_city);

END IF;

END$$

#2、调用带参数存储过程。

CALL proc\_insert ('S6','泰欣',40,'十堰'); -- 调用存储过程，传入参数

SELECT\*FROM s WHERE sno ='S6'; -- 查询插入的结果

#代码结束

第5关：定义、调用带参数存储过程（3）

use demo;

#代码开始

#1、定义带参数存储过程：删除指定零件代码的供应信息，并返回删除的元组数。

DELIMITER $$

dropprocedureifexists proc\_delete$$

createprocedure proc\_delete(IN p\_pno char(4),OUT p\_count int)

begin

    deletefrom spj where pno = p\_pno;

    -- set p\_count = row\_count();

    selectrow\_count()into p\_count;

end$$

#2、调用带参数存储过程。

#以零件代码P5为参数，调用存储过程，将结果存入@p\_count1

call proc\_delete('P5', @p\_count1);

#以零件代码P6为参数，调用存储过程，将结果存入@p\_count2

call proc\_delete('P6', @p\_count2);

#代码结束

select @p\_count1,@p\_count2

第6关：定义、调用带参数存储过程（4）

use demo;

#代码开始

#1、定义带参数存储过程：修改指定代码项目的其它信息（所有信息由参数提供）。

DELIMITER $$

CREATEPROCEDURE proc\_update (

p\_jno CHAR ( 2 ), -- 项目编号

p\_jname VARCHAR ( 10 ), -- 项目名称

p\_city VARCHAR ( 10 ) -- 项目城市

)

BEGIN

DECLARE p\_count INT; -- 声明一个变量用于存储项目数量

SELECTCOUNT(\*) INTO p\_count FROM j WHERE jno = p\_jno; -- 查询是否存在指定编号的项目

IF p\_count \> 0 THEN-- 如果存在，更新项目信息

UPDATE j SET jname = p\_jname, city = p\_city WHERE j.jno = p\_jno;

END IF;

END$$

#2、调用带参数存储过程。

# 修改工程项目代码 J7(jno) 的信息为： 汽车制造厂 (jname) 十堰 (city)

CALL proc\_update ('J7', '汽车制造厂', '十堰');

#代码结束

select \* from j where jno = 'J7';