實驗六 存儲過程

第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';