預存程序(Stored Procedure)
- 事先寫好的 SQL 腳本
- 可以執行多句 SQL 語句
- 可以接受輸入參數並傳回多個輸出參數值
預存程序(Stored Procedure)建立語法
- 設定分隔符號
- 「;」對MYSQL而言,相當於「,」
- MYSQL看到「;」,便會認為這個句子已結束
- 預存程序需要一個段落一起執行,而不是單句、單句分別執行。
DELIMITER $$
- 建立預存程序
DELIMITER $$
CREATEPROCEDURE [SP名稱] ([參數名稱1] [型別1],[參數名稱2] [型別2])
BEGIN
[程式邏輯放這裡]
END
$$
- 呼叫預存程序
CALL [SP名稱]
實作練習
建資料表以及資料請參考SQL 資料庫基本語法介紹
設計一個Procedure ,名稱為SearchCourseStudent,查詢每門課程的學生人數?
- 建立預存程序
DELIMITER $$
CREATEPROCEDURE SearchCourseStudent ()
BEGINSELECT `CId`,COUNT(`sId`) as sum from sc
GROUPBY `CId`;
END$$
- 呼叫預存程序
CALL SearchCourseStudent ();

設計一個Procedure ,名稱為SearchStudent,使用者可以輸入課程編號作為參數,執行後,查詢該課程成績在 80 分以上的學生的學號和姓名
- 建立預存程序
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$$
- 呼叫預存程序
SET@x=1;
CALL SearchStudent (@x);

設計一個Procedure ,名稱為AddStudent,傳入3個參數,分別為要新增的學生名稱、生日、性別 (資料型態同student資料表的結構),以及1個傳出參數 flag (資料型態為int)。Procedure內先查詢該學生名稱是否存在資料庫內。若不存在,則flag設為0,並新增學生資料到student。若存在,則flag設為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$$
- 呼叫預存程序
- 學生名稱不存在
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 ;
