###### tags: `SQL`
# SQL 資料庫進階語法介紹
## 預存程序(Stored Procedure)
- 事先寫好的 SQL 腳本
- 可以執行多句 SQL 語句
- 可以接受輸入參數並傳回多個輸出參數值
## 預存程序(Stored Procedure)建立語法
1. 設定分隔符號
> - 「;」對MYSQL而言，相當於「，」
> - MYSQL看到「;」，便會認為這個句子已結束
> - 預存程序需要一個段落一起執行，而不是單句、單句分別執行。
```SQL=
DELIMITER $$
```
2. 建立預存程序
```SQL=
DELIMITER $$
CREATE PROCEDURE [SP名稱] ([參數名稱1] [型別1],[參數名稱2] [型別2])
BEGIN
[程式邏輯放這裡]
END
$$
```
3. 呼叫預存程序
```SQL=
CALL [SP名稱]
```
## 實作練習
建資料表以及資料請參考[SQL 資料庫基本語法介紹](https://hackmd.io/_59zppw_QFi25Y9RLufvIg)
### 設計一個Procedure ，名稱為SearchCourseStudent，查詢每門課程的學生人數?
1. 建立預存程序
```SQL=
DELIMITER $$
CREATE PROCEDURE SearchCourseStudent ()
BEGIN
SELECT `CId`,COUNT(`sId`) as sum from sc
GROUP BY `CId`;
END$$
```
2. 呼叫預存程序
```SQL=
CALL SearchCourseStudent ();
```
### 設計一個Procedure ，名稱為SearchStudent，使用者可以輸入課程編號作為參數，執行後，查詢該課程成績在 80 分以上的學生的學號和姓名
1. 建立預存程序
```SQL=
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. 呼叫預存程序
```SQL=
SET @x=1;
CALL SearchStudent (@x);
```
### 設計一個Procedure ，名稱為AddStudent，傳入3個參數，分別為要新增的學生名稱、生日、性別 (資料型態同student資料表的結構)，以及1個傳出參數 flag (資料型態為int)。Procedure內先查詢該學生名稱是否存在資料庫內。若不存在，則flag設為0，並新增學生資料到student。若存在，則flag設為1（不執行新增）。
1. 建立預存程序
```SQL=
DELIMITER $$
CREATE PROCEDURE AddStudent(IN name char(10),IN sex char(10),IN age datetime,OUT flag int )
BEGIN
DECLARE a int;
SET a =(SELECT count(*) FROM student WHERE Sname = name);
if a=0 THEN
set flag=0;
INSERT INTO student(Sname,sex,age) VALUES(name,sex,age);
end if;
if a<>0 THEN
set flag=1;
end if;
END$$
```
2. 呼叫預存程序
- 學生名稱不存在
```SQL=
SET @x="小二",@y="男",@z=1990-01-01;
CALL AddStudent(@x, @y,@z,@flag);
SELECT @flag ;
```
- 學生名稱存在
```SQL=
SET @x="張三",@y="男",@z=1990-01-01;
CALL AddStudent(@x, @y,@z,@flag);
SELECT @flag ;
```
Sign in with Wallet
Connect another wallet