預存程序(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 ;
