SQL 資料庫進階語法介紹

預存程序(Stored Procedure)

• 事先寫好的 SQL 腳本
• 可以執行多句 SQL 語句
• 可以接受輸入參數並傳回多個輸出參數值

預存程序(Stored Procedure)建立語法

1. 設定分隔符號

• 「;」對MYSQL而言，相當於「，」
• MYSQL看到「;」，便會認為這個句子已結束
• 預存程序需要一個段落一起執行，而不是單句、單句分別執行。

DELIMITER $$

2. 建立預存程序

DELIMITER $$
CREATEPROCEDURE [SP名稱] ([參數名稱1] [型別1],[參數名稱2] [型別2])

BEGIN
[程式邏輯放這裡]
END
$$

3. 呼叫預存程序

CALL [SP名稱]

實作練習

建資料表以及資料請參考SQL 資料庫基本語法介紹

設計一個Procedure ，名稱為SearchCourseStudent，查詢每門課程的學生人數?

1. 建立預存程序

DELIMITER $$
CREATEPROCEDURE SearchCourseStudent ()
BEGINSELECT `CId`,COUNT(`sId`) as sum from sc 
GROUPBY `CId`;
END$$

2. 呼叫預存程序

CALL SearchCourseStudent ();

設計一個Procedure ，名稱為SearchStudent，使用者可以輸入課程編號作為參數，執行後，查詢該課程成績在 80 分以上的學生的學號和姓名

1. 建立預存程序

DELIMITER $$
CREATE PROCEDURE SearchStudent (IN `id` int)
BEGIN
SELECT a.Sid,b.Cid,b.score,a.Sname from student as a LEFT JOIN sc as b ON a.SId=b.sId 
WHERE (b.Cid=id) AND (b.score>=80);
END$$

2. 呼叫預存程序

SET@x=1;
CALL SearchStudent (@x);

設計一個Procedure ，名稱為AddStudent，傳入3個參數，分別為要新增的學生名稱、生日、性別 (資料型態同student資料表的結構)，以及1個傳出參數 flag (資料型態為int)。Procedure內先查詢該學生名稱是否存在資料庫內。若不存在，則flag設為0，並新增學生資料到student。若存在，則flag設為1（不執行新增）。

1. 建立預存程序

DELIMITER $$
CREATEPROCEDURE AddStudent(IN name char(10),IN sex char(10),IN age datetime,OUT flag int )
BEGINDECLARE a int; 
SET a =(SELECTcount(*) FROM student WHERE Sname = name);
if a=0THENset flag=0;
INSERTINTO student(Sname,sex,age) VALUES(name,sex,age);
end if;
if a<>0THENset flag=1;
end if;
END$$

2. 呼叫預存程序

• 學生名稱不存在

SET@x="小二",@y="男",@z=1990-01-01;
CALL AddStudent(@x, @y,@z,@flag);
SELECT@flag ;

• 學生名稱存在

SET@x="張三",@y="男",@z=1990-01-01;
CALL AddStudent(@x, @y,@z,@flag);
SELECT@flag ;