头歌数据库实验六：存储过程

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

use demo;

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


#代码结束

desc s;

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

use demo;

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

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;

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

use demo;

#代码开始
#1、定义带参数存储过程：查询返回指定供应商的供应零件总数量。
drop procedure if exists proc_2;
delimiter //
create procedure proc_2(in isno char(2),OUT osqty INT)
begin
    select sum(qty) into osqty from spj where sno=isno;
end //



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

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

#代码结束

select @sqty1,@sqty2

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

use demo;

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

DELIMITER //
CREATE PROCEDURE proc_insert (
    p_sno CHAR ( 2 ),
    p_sname VARCHAR ( 10 ),
    p_status INT,
    p_city VARCHAR ( 10 )) BEGIN
    DECLARE
        v_count INT;
    SELECT
        COUNT(*) INTO v_count 
    FROM
        s 
    WHERE
        sno = p_sno;
    IF
        v_count > 0 THEN
        SELECT
            ( 供应商已存在，请重新插入！ );
        
        ELSEIF v_count = 0 THEN
        INSERT INTO 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、定义带参数存储过程：删除指定零件代码的供应信息，并返回删除的元组数。
drop procedure if exists delete_info;
delimiter //
create procedure delete_info(in p_pno char(7),out d_num int)
begin
delete from spj where pno = p_pno;
select row_count() into d_num;
end //

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

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

#代码结束

select @p_count1,@p_count2

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

use demo;

#代码开始
#1、定义带参数存储过程：修改指定代码项目的其它信息（所有信息由参数提供）。
DELIMITER //
CREATE PROCEDURE proc_update (
    p_jno CHAR ( 2 ),
    p_jname VARCHAR ( 10 ),
    p_city VARCHAR ( 10 )) BEGIN
    DECLARE
        p_count INT;
    SELECT
        COUNT(*) 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;