MySQL預存程序使用

標籤：mysql   預存程序   procedure   

1. 目標

掌握如何建立預存程序

2. 文法

CREATE PROCEDURE sp_name([proc_parameter])

[characteristics...] routine_body

3. 說明

• CREATE PROCEDURE為用來建立預存程序的關鍵字；

• sp_name為預存程序的名稱；

• proc_parameter為指定預存程序的參數列表，參數列表的形式：[IN | OUT | INOUT] param_name type

1. IN：表示輸入參數，
2. OUT：表示輸出參數，
3. INOUT：表示既可以輸入也可以輸出；
4. param_name表示參數的名稱；
5. type表示參數的類型，該類型可以是MySQL資料庫中的任意類型。

• characteristics指定預存程序的特性，可以有以下幾種取值方式：

1. LANGUAGE SQL: 說明routine_body部分由SQL語句組成，當前系統支援的語言為SQL, SQL是LANGUAGE特性的唯一值；
2. [NOT] DETERMINISTIC: 指明預存程序執行的結果是否正確。DETERMINISTIC表示結果是確定的。每次執行預存程序時，相同的輸入會得到相同的輸出；而NOT DETERMINISTIC表示結果是不確定的，相同的輸入可能得到不同的輸入。如果沒有指定任意一個值，預設為NOT DETERMINISTIC。
3. {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}: 指明子程式使用SQL語句限制。
4. CONTAINS SQL表明子程式包含SQL語句，但是不包含讀寫資料的語句；
5. NO SQL表明子程式不包含SQL語句；
6. READS SQL DATA說明子程式包含讀寫資料的語句；
7. MODIFIES SQL DATA表明子程式包含寫資料的語句；
8. 預設情況下，系統會指定為CONTAINS SQL；
9. SQL SECURITY { DEFINER|INVOKER}: 指明誰有許可權來執行。DEFINER表示只有定義預存程序者才能執行；INVOKER表示擁有許可權的調用者可以執行。預設情況下，系統指定為DEFINER。
10. COMMENT ‘string‘: 注釋資訊，可以用來描述預存程序或者函數。

• routine_body是SQL代碼內容，可以用BEGIN...END來表示SQL代碼的開始與結束。

4. 樣本

1) 建立樣本資料庫

create database hr;use hr;

2) 建立樣本用到的表並插入範例資料

create table employees(employee_id int(11) primary key not null auto_increment,employee_name varchar(50) not null,employee_sex varchar(10) default '男',hire_date datetime not null default current_timestamp,employee_mgr int(11),employee_salary float default 3000,department_id int(11));

insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('David Tian','男',10,7500,1);insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Black Xie','男',10,6600,1);insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Moses Wang','男',10,4300,1);insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Rena Ruan','女',10,5300,1);insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Sunshine Ma','女',10,6500,2);insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Scott Gao','男',10,9500,2);insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Warren Si','男',10,7800,2);insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Kaishen Yang','男',10,9500,3);insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Simon Song','男',10,5500,3);insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Brown Guan','男',10,5000,3);insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Eleven Chen','女',10,3500,2);insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Cherry Zhou','女',10,5500,4);insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Klause He','男',10,4500,5);insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Maven Ma','男',10,4500,6);insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Stephani Wang','女',10,5500,7);insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Jerry Guo','男',10,8500,1);insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Gerardo Garza','男',10,25000,8);insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Derek Wu','男',10,5500,5);

3) 查看插入的範例資料

select * from employees;

4) 建立計算平均工資的預存程序

DELIMITER //create procedure calculate_emp_sal_avg_p()beginselect AVG(employee_salary) as average_salaryfrom employees;end//DELIMITER ;

說明

• DELIMETER //：該語句作用是將MySQL的結果結束符設定為//，因為MySQL預設的語句結束符為分號";"，為了避免與預存程序中SQL語句的結束符相衝突，需要使用DELIMETER改變預存程序的結束符，並以"END //" 結束預存程序。
• 預存程序定義完畢以後再使用"DELIMETER ; "恢複預設結束符。
• DELIMETER也可以指定其它符號為結束符。
  

5. 調用預存程序

預存程序是通過CALL語句進行調用的，文法如下:

• CALL sp_name([parameter[,...]])

CALL語句調用一個先前用CREATE PROCEDURE建立的預存程序，其中sp_name為預存程序名稱，parameter為預存程序參數。

CALL calculate_emp_sal_avg_p();

6. 查看預存程序

1) SHOW STATUS 語句查看預存程序

文法

• SHOW PROCEDURE STATUS [LIKE ‘pattern‘]

這個語句是一個MySQL的擴充，它返回子程式的特徵，如資料庫、名字、類型、建立者及建立日期和修改日期。

LIKE語句表示匹配預存程序的名稱；

2) SHOW CREATE 語句查看預存程序定義

文法

• SHOW CREATE PROCEDURE sp_name

這個語句是一個MySQL的擴充，類似於SHOW CREATE TABLE，它返回一個可用來重新建立已命名預存程序的確切字串。

3) 從information_schema.Routines表中查看預存程序

文法

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=‘sp_name‘;

• ROUTINE_NAME欄位中儲存的是預存程序或者函數的名稱；
• sp_name指預存程序或函數名稱；

如果您們在嘗試的過程中遇到什麼問題或者My Code有錯誤的地方，請給予指正，非常感謝！

連絡方式：[email protected]

著作權＠：轉載請標明出處！

MySQL預存程序使用