存儲過程和觸發器——MySQL

從MySQL5.0版本開始就對存儲過程和觸發器進行了支持，在MySQL進行學習前，先查看您所使用的版本吧，方法有：

1.$mysql -V  //linux終端下

2.select version();  //mysql下

3. mysql --help | grep Distrib  //linux終端下

在了解您所使用的版本支持情況下再下一步

存儲過程  sql語句執行的時候要先編譯，然后執行。存儲過程（Stored Procedure）是一組為了完成特定功能的SQL語句集，經編譯后存儲在數據庫中。用戶通過指定存儲過程的名字并給出參數（如果該存儲過程帶有參數）來執行它。存儲過程是數據庫中的一個重要對象，任何一個設計良好的數據庫應用程序都應該用到存儲過程。
  一、存儲過程介紹
  存儲過程是由流控制和SQL語句書寫的過程，這個過程經編譯和優化后存儲在數據庫服務器中，應用程序使用時只要調用即可。在ORACLE中，若干個有聯系的過程可以組合在一起構成程序包。
  存儲過程是利用SQL Server所提供的Tranact-SQL語言所編寫的程序。Tranact-SQL語言是SQL Server提供專為設計數據庫應用程序的語言，它是應用程序和SQL Server數據庫間的主要程序式設計界面。它好比Oracle數據庫系統中的Pro-SQL和Informix的數據庫系統能夠中的Informix- 4GL語言一樣。這類語言主要提供以下功能，讓用戶可以設計出符合引用需求的程序：
  1)、變量說明
  2)、ANSI兼容的SQL命令(如Select,Update….)
  3)、一般流程控制命令(if…else…、while….)
  4)、內部函數
  二、使用存儲過程有以下的優點：
  * 存儲過程的能力大大增強了SQL語言的功能和靈活性。存儲過程可以用流控制語句編寫，有很強的靈活性，可以完成復雜的判斷和較復雜的運算。
  * 可保證數據的安全性和完整性。
  # 通過存儲過程可以使沒有權限的用戶在控制之下間接地存取數據庫，從而保證數據的安全。
  # 通過存儲過程可以使相關的動作在一起發生，從而可以維護數據庫的完整性。
  * 再運行存儲過程前，數據庫已對其進行了語法和句法分析，并給出了優化執行方案。這種已經編譯好的過程可極大地改善SQL語句的性能。由于執行SQL語句的大部分工作已經完成，所以存儲過程能以極快的速度執行。
  * 可以降低網絡的通信量。
  * 使體現企業規則的運算程序放入數據庫服務器中，以便：
  # 集中控制。
  # 當企業規則發生變化時在服務器中改變存儲過程即可，無須修改任何應用程序。企業規則的特點是要經常變化，如果把體現企業規則的運算程序放入應用程序中，則當企業規則發生變化時，就需要修改應用程序工作量非常之大（修改、發行和安裝應用程序）。如果把體現企業規則的運算放入存儲過程中，則當企業規則發生變化時，只要修改存儲過程就可以了，應用程序無須任何變化。
   三、存儲過程的書寫格式：
  CREATE PROCEDURE [擁有者.]存儲過程名[;程序編號]
  [(參數#1,…參數#1024)]
  [WITH
  {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
  ]
  [FOR REPLICATION]
  AS 程序行

其中存儲過程名不能超過128個字。每個存儲過程中最多設定1024個參數
  (SQL Server 7.0以上版本),參數的使用方法如下：
  @參數名數據類型 [VARYING] [=內定值] [OUTPUT]
  每個參數名前要有一個“@”符號,每一個存儲過程的參數僅為該程序內部使用,參數的類型除了IMAGE外，其他SQL Server所支持的數據類型都可使用。

  output：表示此參數是可傳回的
  with {recompile|encryption}
  recompile:表示每次執行此存儲過程時都重新編譯一次
  encryption:所創建的存儲過程的內容會被加密

實例：

建立2張表格employee和department，他們以員工ID建立關系

1. 創建2表：

create table employee (
     employee_id int(5) primary key not null,
     employee_name varchar(20),
     employee_salary int(5));

create table department (
     dept_id int(3),
     dept_name varchar(20),
     employee_id int(5),
     constraint fk_employee_id foreign key(employee_id) references employee(employee_id));

2. 給employee創建添加記錄的存儲過程

 在這之前，需要設置一下分隔符，以免后面使用;時就終止了創建

delimiter //
create procedure add_employee(in id int,in name varchar(20),in salary int)  /*參數輸入的形式，in：輸入,若為輸出，則為out*/
  begin
  insert into employee values(id,name,salary);  #輸入的數據插入到表格中
  select * from employee;
  end//  #這里就以//結束程序的錄入

3.給department創建添加記錄的存儲過程

create procedure insert_dept(in id int,in name varchar(20),in emp_id int)
  begin
  insert into department values(id,name,emp_id);
  select * from department;
  end;//

4. 調用存儲過程完成數據錄入

call add_employee(2,'Jason',6500);//
call insert_dept(1,'STE',2);//

5.創建一個給指定部門員工加薪的存儲過程

create procedure raise (in department_id int,in add_salary_amount int)
  begin
  update employee set employee_salary=employee_salary+add_salary_amount
     where employee_id in ( select employee_id from department where department_id=dept_id);
  commit;
  end;//

call raise(1,200);//

觸發器  觸發器（trigger）是個特殊的存儲過程，它的執行不是由程序調用，也不是手工啟動，而是由個事件來觸發，比如當對一個表進行操作（ insert，delete， update）時就會激活它執行。觸發器經常用于加強數據的完整性約束和業務規則等。觸發器可以從DBA_TRIGGERS，USER_TRIGGERS數據字典中查到。

一觸發器介紹
觸發器是一種特殊的存儲過程，它在插入，刪除或修改特定表中的數據時觸發執行，它比數據庫本身標準的功能有更精細和更復雜的數據控制能力。數據庫觸發器有以下的作用：
* 安全性。可以基于數據庫的值使用戶具有操作數據庫的某種權利。
# 可以基于時間限制用戶的操作，例如不允許下班后和節假日修改數據庫數據。
# 可以基于數據庫中的數據限制用戶的操作，例如不允許股票的價格的升幅一次超過10%。
* 審計。可以跟蹤用戶對數據庫的操作。
# 審計用戶操作數據庫的語句。
# 把用戶對數據庫的更新寫入審計表。
* 實現復雜的數據完整性規則。 #實現非標準的數據完整性檢查和約束。觸發器可產生比規則更為復雜的限制。與規則不同，觸發器可以引用列或數據庫對象。例如，觸發器可回退任何企圖吃進超過自己保證金的期貨。
# 提供可變的缺省值。
* 實現復雜的非標準的數據庫相關完整性規則。觸發器可以對數據庫中相關的表進行連環更新。#在修改或刪除時級聯修改或刪除其它表中的與之匹配的行。
# 在修改或刪除時把其它表中的與之匹配的行設成NULL值。
# 在修改或刪除時把其它表中的與之匹配的行級聯設成缺省值。

# 觸發器能夠拒絕或回退那些破壞相關完整性的變化，取消試圖進行數據更新的事務。當插入一個與其主健不匹配的外部鍵時，這種觸發器會起作用。

* 同步實時地復制表中的數據。
* 自動計算數據值，如果數據的值達到了一定的要求，則進行特定的處理。例如，如果公司的帳號上的資金低于5萬元則立即給財務人員發送警告數據。

二觸發器語法
create  trigger 觸發器名觸發時間觸發事件
on 表名
[for each row]
pl/sql 語句
其中：
觸發器名：觸發器對象的名稱。由于觸發器是數據庫自動執行的，因此該名稱只是一個名稱，沒有實質的用途。
觸發時間：指明觸發器何時執行，該值可取：
before---表示在數據庫動作之前觸發器執行；
after---表示在數據庫動作之后出發器執行。
觸發事件：指明哪些數據庫動作會觸發此觸發器：
insert：數據庫插入會觸發此觸發器；
update：數據庫修改會觸發此觸發器；
delete：數據庫刪除會觸發此觸發器;

表名：數據庫觸發器所在的表。

三.使用觸發機制時的限制
當你在使用觸發機制時你必須要知道它有如下的使用限制:
l 不能在臨時表中創建觸發機制
l 觸發機制必須在當前的表所在的數據庫中創建
l 不能在視圖中創建觸發機制
l 當表被刪除以后所有與之相關的觸發機制會被自動地刪除

實例：

6. 記錄員工薪資變更日志

這時就可以對salary創建一個觸發器，記錄變更信息，先創建一個記錄日志的表格：

create table salary_adjust_log(
  employee_id int(5),
  old_salary int(4),
  new_salary int(4),
  changedata datetime);//   #使用datetime格式記錄變更時間

創建觸發器：

create trigger update_salary
  after update on employee
  for each row       #對表格每一行執行
  begin
  insert into salary_adjust_log
    values(new.employee_id,  /*new:表示更新后的記錄，old：更新前的記錄，在oracle中語法是 :new.employee_id,:old employee_salary，多了個冒號*/
      old.employee_salary,  
      new.employee_salary,
      now());   #使用now()函數記錄時間
  end;//