数据库：实验六存储过程

一、增加供应商相关列sqty

任务要求

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

use demo;

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

alter table s add sqty int default 0;


#代码结束

desc s;

二、定义、调用简单存储过程

任务要求

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

注意：定义存储过程前使用DELIMITER 命令将结束命令修改为其他字符。

use demo;

#代码开始
#1、定义简单存储过程：计算所有供应商供应零件总数量并修改供应商相关列sqty。
drop procedure if exists proc_1;
delimiter $$
create procedure proc_1()
begin
    update s set sqty=(select sum(qty) from spj where spj.sno=s.sno);
end$$


#2、调用存储过程。
call proc_1();


#代码结束

select * from s;

三、定义、调用带参数存储过程（1）

任务要求

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

以供应商S1为参数，调用存储过程，将结果存入@sqty1
以供应商S2为参数，调用存储过程，将结果存入@sqty2
注意：
1、定义存储过程前使用DELIMITER 命令将结束命令修改为其他字符。
2、在多次执行创建存储过程代码评测时，可能会出现以下错误：
ERROR 1304 (42000) at line 7: PROCEDURE proc_1 already exists
此时，在创建存储过程前使用以下语句删除已有的存储过程。
drop procedure if exists proc_1;

use demo;

#代码开始
#1、定义带参数存储过程：查询返回指定供应商的供应零件总数量。
drop procedure if exists statistics;
DELIMITER $$
CREATE PROCEDURE statistics(IN in_sno CHAR(20),OUT out_sqty INT)
BEGIN
	SELECT SUM(qty) INTO out_sqty FROM spj WHERE sno = in_sno;
END $$
DELIMITER ;



#2、调用带参数存储过程。
#以供应商S1为参数，调用存储过程，将结果存入@sqty1
CALL statistics('S1',@sqty1);

#以供应商S2为参数，调用存储过程，将结果存入@sqty2
CALL statistics('S2',@sqty2);

#代码结束

select @sqty1,@sqty2

四、定义、调用带参数存储过程（2）

任务要求

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

注意：定义存储过程前使用DELIMITER 命令将结束命令修改为其他字符。

use demo;

#代码开始
#1、定义带参数存储过程：插入一个指定供应商信息 ('S6','泰欣',40,'十堰')（所有信息由参数提供）。
DELIMITER $$
drop procedure if exists s_insert;
CREATE PROCEDURE s_insert(
	s_sno CHAR(20),
	s_sname CHAR(20),
	s_status INT,
	s_city CHAR(20)
)
BEGIN
	DECLARE s_count INT;
	SELECT COUNT(*) INTO s_count FROM s WHERE sno = s_sno;
  IF s_count >0 
		THEN SELECT ( '供应商已存在，请重新插入！' );
  ELSEIF s_count = 0 
		THEN INSERT INTO s ( sno, sname, STATUS, city )VALUES( s_sno, s_sname, s_status, s_city);
  END IF;
END $$
DELIMITER ;


#2、调用带参数存储过程。
CALL s_insert('S6','泰欣',40,'十堰');
SELECT * FROM s WHERE sno = 'S6';
#代码结束

五、定义、调用带参数存储过程（3）

任务要求

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

以零件代码P5为参数，调用存储过程，将结果存入@p_count1
以零件代码P6为参数，调用存储过程，将结果存入@p_count2
注意：定义存储过程前使用DELIMITER 命令将结束命令修改为其他字符。

use demo;

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

DELIMITER $$
CREATE PROCEDURE s_delete(IN in_sno CHAR(20),OUT d_sum INT)
BEGIN
	DELETE FROM spj WHERE pno = in_sno;
	SELECT ROW_COUNT() INTO d_sum;
END $$
DELIMITER ;


#2、调用带参数存储过程。
#以零件代码P5为参数，调用存储过程，将结果存入@p_count1
CALL s_delete('P5',@p_count1);

#以零件代码P6为参数，调用存储过程，将结果存入@p_count2
CALL s_delete('P6',@p_count2);

#代码结束
select @p_count1,@p_count2

六、定义、调用带参数存储过程（4）

任务要求

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

修改工程项目代码 J7(jno) 的信息为： 汽车制造厂 (jname) 十堰 (city)
注意：定义存储过程前使用DELIMITER 命令将结束命令修改为其他字符。

use demo;

#代码开始
#1、定义带参数存储过程：修改指定代码项目的其它信息（所有信息由参数提供）。
drop PROCEDURE if exists s_update;
DELIMITER $$
CREATE PROCEDURE s_update(
	s_sno CHAR(20),
	s_sname CHAR(20),
	s_city CHAR(20)
)
BEGIN
	DECLARE s_count INT;
	SELECT COUNT(*) INTO s_count FROM j WHERE jno = s_sno;
  IF s_count = 0 
		THEN SELECT ( '没有你要修改的数据' );
  ELSEIF s_count > 0 
		THEN UPDATE j SET jname = s_sname,city = s_city WHERE j.jno = s_sno;
  END IF;
END $$
DELIMITER ;



#2、调用带参数存储过程。
# 修改工程项目代码 J7(jno) 的信息为： 汽车制造厂 (jname)  十堰 (city)
CALL s_update ( 'J7', '汽车制造厂', '十堰' );

#代码结束

select * from j where jno = 'J7';