這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)碛嘘P(guān)怎么理解MySQL存儲(chǔ)過程和觸發(fā)器，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

成都網(wǎng)站建設(shè)、成都做網(wǎng)站的關(guān)注點(diǎn)不是能為您做些什么網(wǎng)站，而是怎么做網(wǎng)站，有沒有做好網(wǎng)站，給創(chuàng)新互聯(lián)一個(gè)展示的機(jī)會(huì)來證明自己，這并不會(huì)花費(fèi)您太多時(shí)間，或許會(huì)給您帶來新的靈感和驚喜。面向用戶友好，注重用戶體驗(yàn)，一切以用戶為中心。

存儲(chǔ)過程(stored procedure SP)是MySQL 5.0 版本中的最大創(chuàng)新。他們是一些由MySQL服務(wù)器直接存儲(chǔ)和執(zhí)行的定制過程 或 函數(shù)。SP的加入把SQL語(yǔ)言擴(kuò)展成了一種程序設(shè)計(jì)語(yǔ)言，可以利用SP把一個(gè)客戶--服務(wù)器體系的數(shù)據(jù)庫(kù)應(yīng)用軟件中的部分邏輯保存起來供日后使用。

觸發(fā)器(trigger) 是在INSERT ,UPDATE 或 DELETE 命令之前或者之后對(duì)SQL命令或SP的自動(dòng)自動(dòng)調(diào)用。

----------------------------------------
//輸入都必須以 '$$'作為結(jié)束符號(hào)
delimiter $$

我們先來創(chuàng)建一個(gè)最簡(jiǎn)單的函數(shù),
函數(shù)的功能是寫入兩個(gè)數(shù)，得出 和:

Create FUNCTION addition(v1 int(11),v2 int(11))
RETURNS int(11)
BEGIN

return (v1+v2);

END$$

//把結(jié)束符號(hào)換回來
delimiter ;

讓我們來調(diào)用我們的函數(shù):
select addition(11,15) ;  結(jié)果 26 正確。

讓我們來查看一下數(shù)據(jù)庫(kù)中有那些函數(shù)
show function status;

如何來查看addition的代碼？
show create function addition;

現(xiàn)在讓我們來刪除那個(gè)函數(shù)：
drop function addition;

----------------------------------------

上面是小試牛刀。 現(xiàn)在開始我們來全面學(xué)習(xí)MYSQL中的存儲(chǔ)過程 和 觸發(fā)器

分3個(gè)類 FUNCTION , PROCEDURE ,TRIGGER 來學(xué)習(xí)研究。

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
                                             FUNCTION
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

語(yǔ)法 :

CREATE FUNCTION function_name (param1 datatype [,param2 datatype ,.....])
RETURNS datatype

BEGIN
commands;
END

---------------------------------------------------------------------------

SP 注釋

"--" 開始并且一直到這一行的結(jié)尾都是注釋

------------------------------------------

(1)FUNCTION 中的局部變量的定義 和 變量的賦值

* 變量的定義
語(yǔ)法 :
DECLARE varname1 datatype1 [DEFAULT value];
DECLARE varname1,varname2 .... datatype [DEFAULT value]; //多變量同類型的定義方式

$ 變量的定義要在BEGIN ... END  之間定義。
$ 如果在FUNCTION 體中定義了多個(gè)BEGIN ... END 嵌套，那么 變量的定義只能在當(dāng)前 BEGIN .. END 或則 子 BEGIN ... END 中有效。
$ 子類 BEGIN ... END 中的變量定義可以覆蓋父類 BEGIN ... END 中定義的變量。

* 對(duì)變量的賦值
對(duì)變量的賦值有兩種方法。

[1]直接給變量賦給常量，或則把其他的變量賦值給當(dāng)前變量
  set var = value;
 set var1 = value1,var2 = value2....;

[2]把SQL查詢結(jié)果賦值給變量
 SELECT var := value  //一種以SELECT 方法 把常量或其他變量賦值給當(dāng)前變量的方法
 SELECT nomalvalue INTO var  //又一種以SELECT 方法 把常量或其他變量賦值給當(dāng)前變量的方法
 SELECT value FROM TABLE .. INTO var;
 SELECT value1,value2 FROM TABLE .. INTO var1,var2;


SELECT INTO 命令是SELECT 命令的一種變體。 它上一以 INTO varname 結(jié)束整條命令。
要求，SELECT命令返回并且只能返回一條記錄。(不允許多條記錄)

example-001:

use wyd

delimiter $$

----------------------
create table person(
id int primary key auto_increment,
age int
) $$

----------------------
insert into person(age)values(12);
insert into person(age)values(34);
insert into person(age)values(42);
insert into person(age)values(13);
insert into person(age)values(2)$$

-----------------------
drop function addtion$$

----------------------

CREATE FUNCTION getage(person_id int)
RETURNS int
BEGIN
DECLARE person_age int default 0;
SELECT age FROM person WHERE id = person_id INTO person_age;

RETURN person_age;

END$$
----------------------
test the result:

select getage(1)$$ --> result = 12
select getage(2)$$ --> result = 34

運(yùn)行正常

------------------------------------------------

 

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

(2)FUNCTION 中的分支

[1] IF - THEN - ELSE 分支
 語(yǔ)法 :
 IF comdition THEN
  commands;
 END IF;
 -------------------------
 IF comdition THEN
  commands;
 ELSE
  commands
 END IF;
 -------------------------
嵌套 IF comdition THEN
  commands;
 ELSE  IF comdition THEN
   commands;
  [ELSE commands;]
  END IF;
 END IF;
 -------------------------
 
 
[2] CASE 分支
語(yǔ)法:
--------------------------------
CASE expression

WHEN value1 THEN commands;

WHEN value2 THEN commands;

.......

WHEN value_n THEN commands;
 
ELSE commands;

END CASE;
--------------------------------

example-002:

CREATE FUNCTION personstate (age int)
RETURNS varchar(30)
BEGIN
 DECLARE personstate varchar(30) DEFAULT "UNKNOWN";
 IF age < 0 THEN SET personstate = "UNBORN";
 ELSE
  SET personstate = "BORN";
 END IF;  
RETURN personstate;

END$$

-----------------
select personstate(-3)   ----> result = UNBORN;
select personstate(3)    ----> result =  BORN;



------------------------------------


 example-003:

DROP FUNCTION  personstate$$

CREATE FUNCTION personstate (age int)
RETURNS varchar(30)
BEGIN
 DECLARE personstate varchar(30) DEFAULT "UNKNOWN";
 IF age < 0 THEN SET personstate = "UNBORN";
 ELSE
  IF age >0 && age<=14 THEN SET personstate ="CHILD"; END IF;
  IF age >14 && age <=22 THEN SET personstate ="YANG"; END IF;
  IF age >22 && age<60 THEN SET personstate = "STRONG"; END IF;
IF age >60 THEN SET personstate = "OLD"; END IF;
 END IF;  
RETURN personstate;

END$$

---------

select personstate(-3)$$ result = UNBORN
select personstate(5)$$ result = CHILD
select personstate(16)$$ result = YANG
select personstate(28)$$ result = STRONG
select personstate(66)$$ result = OLD


TEST IS OK.

----------------------------------------
example-004:

DROP FUNCTION showIn$$

CREATE FUNCTION showIn( valueIn int)
RETURNS VARCHAR(50)
BEGIN

DECLARE str varchar(30) DEFAULT "UNKNOWN";

 CASE valueIn
 
 WHEN 1 THEN SET str = "you input is 1";
 WHEN 2 THEN SET str = "you input is 2";
 WHEN 3 THEN SET str = "you input is 3";
 WHEN 4 THEN SET str = "you input is 4";
 WHEN 5 THEN SET str = "you input is 5";
 
 ELSE SET str = "you input is not 1,2,3,4,5";
 
 END CASE;
RETURN str;
 
END$$

-------------------

select showIn(1)$$  result = you input is 1
select showIn(2)$$  result = you input is 2
select showIn(6)$$  result = you input is not 1,2,3,4,5

TEST IS OK

----------------------------------------

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

(3)FUNCTION 中的循環(huán)

[1] REPEAT-UNTIL 循環(huán)
[2] WHILE 循環(huán)
[3]  LOOP 循環(huán)


*　REPEAT-UTIL　循環(huán)

語(yǔ)法:
   [loopname:] REPEAT
    commands;
   UNTIL condition
   END REPEAT [loopname];
   
   說明：
   和 do {} while(condition)　語(yǔ)句的功能一樣。先運(yùn)行，后判斷。
   當(dāng)condition 為true的時(shí)候　放棄循環(huán)　
   
   
 * WHILE　循環(huán)
 
 　語(yǔ)法:
 
   [loopname :] WHILE condition DO
    commands;
   END WHILE [loopname];
 
   說明: 和　while(condition) {commands; }　語(yǔ)句功能一樣。 先判斷，后執(zhí)行。
   當(dāng)condition 為 false 的時(shí)候　放棄循環(huán)
   
 
 * LOOP 循環(huán)
 
 　語(yǔ)法:
   
   loopname: LOOP
    commands;
    IF condition THEN LEAVE loopname ; END IF;
   END LOOP loopname;
   
   說明　: 這是一個(gè)沒有條件判斷的循環(huán)?？梢哉J(rèn)為是一個(gè)死循環(huán)。
   除非執(zhí)行LEAVE　命令來跳出循環(huán)，否則循環(huán)將永遠(yuǎn)被執(zhí)行。
   
 
 * LEAVE
 
   語(yǔ)法:
   LEAVE loopname ;
   
   說明:
   LEAVE loopname 命令見是程序代碼的執(zhí)行流程跳出并且結(jié)束一個(gè)循環(huán)。
   LEAVE loopname 命令還可以用來提前退出BEGIN - END 語(yǔ)句塊。
   LEAVE loopname 命令相當(dāng)于 C 或則 JAVA中 跳出循環(huán)的 BREAK  命令
   
   
 * ITERATE
 
   語(yǔ)法:
   ITERATE loopname ;
   
   說明:
   TERATE loopname 命令是跳出當(dāng)次循環(huán)，接下來執(zhí)行下一次循環(huán)。
   TERATE loopname 命令只能在循環(huán)體內(nèi)運(yùn)行。
   TERATE loopname 命令相當(dāng)于 C 或則 JAVA中 跳出循環(huán)的 CONTINUE  命令
   
-------------------------

example-005:

  DROP FUNCTION getString$$

CREATE FUNCTION getString(number int(11))
  RETURNS VARCHAR(50)
  BEGIN
   declare str varchar(50) default '';
   declare i int default 0;
   
   myloop: REPEAT
    SET i = i+1;
    set str = concat(str,"*");
   
   UNTIL i>=number
   END REPEAT myloop;
  RETURN str;
  END $$
 
  -------------
  select getString(3)$$
  select getString(4)$$
  select getString(8)$$
 
  TEST IS OK
 
 

-------------------------

example-006:

  DROP FUNCTION getString$$
 
  CREATE FUNCTION getString(number int(11))
  RETURNS VARCHAR(50)
  BEGIN
 
   declare str varchar(50) default "";
   declare i int default 0;
   myloop: WHILE iset i = i+1;
    set str = concat(str,"@");
   END WHILE myloop;
   
   return str;
   
  END$$
 
  --------
  select getString(3)$$
  select getString(4)$$
  select getString(8)$$
 
  TEST IS OK
 
  -------------------------
  example-007:
 
  DROP FUNCTION getString$$
 
  CREATE FUNCTION getString(number int(11))
  RETURNS VARCHAR(50)
  BEGIN
   declare str varchar(50) default "";
   declare i int default 0;
   
   myloop:LOOP
    set i = i+1;
    IF i>number THEN LEAVE myloop; END IF;
   
    set str= concat(str,"# ");
   
   END LOOP myloop;
   
   RETURN str;
 
  END$$
 
  ---------
  select getString(3)$$
  select getString(4)$$
  select getString(8)$$
 
  TEST IS OK
 
  -----------------------------
  -----------------------------
 
  example-iterate :
 
  DROP FUNCTION getString$$
 
  CREATE FUNCTION getString(number int(11))
  RETURNS VARCHAR(50)
  BEGIN
   declare str varchar(50) default "";
   declare i int default 0;
   
   myloop:LOOP
    set i = i+1;
    IF i%2 = 0 THEN ITERATE myloop ; END IF;
    IF i>number THEN LEAVE myloop; END IF;
   
   
    set str= concat(str,"# ");
   
   END LOOP myloop;
   
   RETURN str;
 
  END$$
 
 
     
 
 
   
   &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
               基本語(yǔ)法規(guī)則   <摘錄mysql 5.0="" p="" 296-297="">
   &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

通過對(duì)FUNCTION的了解，我們已經(jīng)對(duì)SP的語(yǔ)法規(guī)則有了大體的了解。
在FUNCTION 中的變量定義規(guī)則，變量賦值規(guī)則，分支規(guī)則 和 循環(huán)規(guī)則 同樣也適用于 PROCEDURE。

現(xiàn)在我們來對(duì)SP的語(yǔ)法規(guī)則進(jìn)行規(guī)范的認(rèn)識(shí):

# 分號(hào) (;) 。 同一個(gè)SP可以包含任意多條SQL命令。這些命令必須用分號(hào)格開，就連分支和循環(huán)的控制結(jié)構(gòu)也必須用分號(hào)結(jié)束。

# BEGIN - END 。 沒有落在SP關(guān)鍵字之間(如 THEN 和 END IF 之間) 的多條SQL命令必須放在關(guān)鍵字BEGIN 和 END 之間。
 這就意味著由多條SQL命令構(gòu)成的SP的代碼都必須以BEGIN開始,以END結(jié)束。

# 換行符。 換行符在SP代碼中的語(yǔ)意效果與空格字符相同。這意味著把 IF-THEN-ELSE-END-IF 結(jié)構(gòu)連續(xù)寫在同一行或分開寫在多行上都是可以的。

# 變量 。 供SP內(nèi)部使用的局部變量 和 局部參數(shù)不加 "@" 前綴。 在SP內(nèi)允許使用普通的SQL變量，但是他們必須加上"@"前綴。
  (加"@"前綴的變量是普通全局變量。對(duì)變量疑問，可以參考 《MYSQL變量》 這個(gè)部分。)
 
# 字母大小寫情況。 SP 在定義 和調(diào)用時(shí)均不分字母大小寫情況。它寫成(比如說)shorten , SHORTEN , Shorten 的效果都是一樣的。

# 特殊字符。 在SP中避免使用特殊字符。 總之MYSQL對(duì)特殊字符的支持還不是很好。

# 注釋。 "--" 開始并且一直到這一行的結(jié)尾都是注釋





    &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
                                         查看和刪除SP的方法
    &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

(1) 查看FUNCTION
 show function status
 
 show CREATE FUNCTION functionname
 
 select routine_name ,routine_type,routine_schema,created from information_schema.routines where routine_schema = '你的數(shù)據(jù)庫(kù)名' and routine_type='FUNCTION'
 
 例如:
 select routine_name ,routine_type,routine_schema,created from information_schema.routines where routine_schema = 'WYD' and routine_type='function'
 
 
 DROP FUNCTION [IF EXISTS] function_name
 
(2) 查看PROCEDURE

 SHOW PROCEDURE STATUS
 
 show CREATE PROCEDURE functionname
 
 select routine_name ,routine_type,routine_schema,created from information_schema.routines where routine_schema = '你的數(shù)據(jù)庫(kù)名' and routine_type='PROCEDURE'
 
 例如:
 select routine_name ,routine_type,routine_schema,created from information_schema.routines where routine_schema = 'WYD' and routine_type='PROCEDURE'
 
 
 DROP PROCEDURE [IF EXISTS] procedure_name

(3) 查看所有SP
 desc information_schema.routines
 
 select routine_name ,routine_type,routine_schema,created from information_schema.routines
 
 



    &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
                                                    MYSQL變量
    &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
   
    MYSQL允許人們把簡(jiǎn)單的值(離散值,不是象SELECT查詢結(jié)果那樣的集合或列表)保存在變量里。在日常應(yīng)用里，需要用到MySQL變量的時(shí)候不多；但是對(duì)存儲(chǔ)過程來說，變量卻是非常重要的SQL元素。MySQL里的變量可以分為3類:
    $ 普通變量。
     這類變量的標(biāo)志是以字符@開頭，他們?cè)赟QL連接被關(guān)閉時(shí)將失去內(nèi)容。
   
    $ 系統(tǒng)變量和服務(wù)器變量。
     這類變量的內(nèi)容是MySQL服務(wù)器的工作狀態(tài)或?qū)傩裕麄兊臉?biāo)志是以"@@"字符串開頭。
   
    $ 存儲(chǔ)過程里的局部變量。
     這些變量是在存儲(chǔ)過程內(nèi)部聲明的，只在存儲(chǔ)過程內(nèi)有效。他們沒有統(tǒng)一的特殊標(biāo)志，但是變量名必須與數(shù)據(jù)表和數(shù)據(jù)列名區(qū)別。
     局部變量在使用前必須要用DECLARE命令對(duì)他們做出聲明。局部變量的內(nèi)容在過程或函數(shù)退出的時(shí)候丟失。
   
   普通全局變量的聲明和賦值:
   例子: set @varname = 3
   
    select @total :=count(*) from table_a
   
    select money from book where id =3 into @bookmoney
   
   查詢:
   例子: select @varname
   
   
   
   

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
                                                       PROCEDURE
    &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
   
    PROCEDURE [databasename.]procedure_name([IN or OUT or INOUT ] parametername datatype )
    BEGIN
   
     commands;
     
    END
   
   
    ----------------
   
    存儲(chǔ)過程的參數(shù):
    關(guān)鍵字 IN ,OUT ,INOUT 用來區(qū)分有關(guān)參數(shù)的用途是僅限制于輸入數(shù)據(jù)、僅限于輸出數(shù)據(jù) 還是 輸入輸出數(shù)據(jù)均可的。(默認(rèn)設(shè)置是IN)
   
   
    ----------------
   
    PROCEDURE局部變量的定義和局部變量的賦值
   
     :同F(xiàn)UNCTION的局部變量的定義和局部變量的賦值
     
   
    -----------------
    example-007:
    題目：寫一個(gè)加法的PROCEDURE, 輸入兩個(gè)數(shù)字。PROCEDURE輸出他們的和
   
    delimiter $$
   
    DROP PROCEDURE IF EXISTS p_addition
   
    CREATE PROCEDURE p_addition(IN v1 int, IN v2 int ,OUT sum int)
    BEGIN
     set sum = v1+v2;
    END$$
   
    ---------
    CALL p_addition(12,45,@sum)$$
   
    select @sum $$
   
    TEST IS OK
   
    ----------------------------------
   
    ----------------------------------
    example-008
   
    題目: 有一張student表，有學(xué)生名字段和總分字段。
    我們寫一個(gè)PROCEDURE，只要調(diào)用這個(gè)PROCEDURE ，輸入 學(xué)生名 和 總分。 它就幫助我們把該學(xué)生寫入數(shù)據(jù)表中。
   
    delimiter $$
   
    CREATE TABLE student(
    id int(11) primary key auto_increment,
    name varchar(50),
    score int(5)
    )$$
   
    -----
   
    DROP PROCEDURE IF EXISTS p_addStudent$$
   
    -----
   
    CREATE PROCEDURE p_addStudent(IN p_name VARCHAR(50) ,IN p_score INT)
    BEGIN
     insert into student(name,score)values(p_name,p_score);
   
    END$$
   
    -----
    CALL p_addStudent('Petter',199)$$
    CALL p_addStudent('Helen',209)$$
    CALL p_addStudent('Jacker',238)$$
   
    select * from student $$
   
    --------TEST IS OK
   
    --------------------------------------
    --------------------------------------
   
    example-009
   
    題目: 有一張person表 表中有多個(gè)字段。
    name ,age , state
    我們寫一個(gè)PROCEDURE，只要調(diào)用這個(gè)PROCEDURE ，輸入人名 和 年齡。 它就幫我們判斷state,并寫入數(shù)據(jù)庫(kù)。
    如果年齡<0 ，state = 'UNBORN'
0 <=如果年齡<12，state = 'CHILD'
12<=如果年齡<22，state = 'YANG'
22<=如果年齡<60，state = 'STRONG'
60<=如果年齡，state = 'OLD'
   
   
    delimiter $$
   
    drop table person$$
   
    create table person(
    id int(11) primary key auto_increment,
    name varchar(50),
    age int(3),
    state varchar(50)
    )$$
   
   
    DROP PROCEDURE IF EXISTS p_addPerson $$
   
   
    CREATE PROCEDURE p_addPerson(IN p_name varchar(50),IN p_age INT(3))
    BEGIN
     declare p_state varchar(50) default "UN_KNOW";
     
     IF p_age < 0 THEN SET p_state = "UNBORN";
 ELSE
  IF p_age >0   && p_age<12 THEN SET p_state ="CHILD";   END IF;
IF p_age >=12 && p_age<22 THEN SET p_state ="YANG";    END IF;
IF p_age >=22 && p_age<60 THEN SET p_state = "STRONG"; END IF;
IF p_age >=60              THEN SET p_state = "OLD";    END IF;
 END IF;
 
 INSERT INTO person(name,age,state) values(p_name,p_age,p_state) ;
   
    END$$
   
    ------------------
    CALL p_addPerson('Pet',11) $$
    CALL p_addPerson('Tom',21) $$
    CALL p_addPerson('Joy',74) $$
    CALL p_addPerson('Soy',-4) $$
   
    SELECT * from person $$
   
    ----- TEST IS OK ----
   
   
   
   
   
   
    --------------------------------------------------
    --------------------------------------------------
   
    example-010
   
    題目:有一張表 goods ，3個(gè)字段 id ,name,price. 表中有很多記錄。
    現(xiàn)在我們要寫一個(gè)PROCEDURE ，把里面的每個(gè)商品的價(jià)格都修改為原來的80%.
   
   
    delimiter $$
   
    drop table goods$$
   
    create table goods(
     id int(11) primary key auto_increment,
     name varchar(50),
     price float(6,2) default 0000.00
    )$$
   
   
    insert into goods(name,price)values('goods_01',77.56)$$
    insert into goods(name,price)values('goods_02',147.56)$$
    insert into goods(name,price)values('goods_03',156.36)$$
    insert into goods(name,price)values('goods_04',58.36)$$
    insert into goods(name,price)values('goods_05',458.68)$$
    insert into goods(name,price)values('goods_06',485.55)$$
    insert into goods(name,price)values('goods_07',785.22)$$
    insert into goods(name,price)values('goods_08',45.36)$$
    insert into goods(name,price)values('goods_09',47.36)$$
    insert into goods(name,price)values('goods_10',456.36)$$
    insert into goods(name,price)values('goods_11',654.85)$$
    insert into goods(name,price)values('goods_12',785.25)$$
   
   
    ------------
    DROP PROCEDURE IF EXISTS p_goods $$
   
    CREATE PROCEDURE p_goods()
    BEGIN
     
     DECLARE p_id INT DEFAULT 0;
     DECLARE p_id_min INT DEFAULT 0;
     DECLARE p_id_max INT DEFAULT 0;
     DECLARE p_id_current INT DEFAULT 0;
     DECLARE p_name_current VARCHAR(50) DEFAULT "UNKNOW";
     DECLARE p_price FLOAT(6,2) DEFAULT 0;
     
     select min(id),max(id) from goods into p_id_min ,p_id_max;
     
     SET p_id = p_id_min;
     
     goods_loop : LOOP
     
      select id,name,price from goods where id = p_id into p_id_current,p_name_current,p_price;
     
      IF p_id_current!=0 THEN
     
       set p_price = p_price * 0.8;
       
       update goods set price = p_price where id = p_id;
       
       set p_id_current=0;
       
      END IF;
     
      set p_id = p_id + 1;
     
      IF p_id > p_id_max THEN LEAVE goods_loop; END IF;
     END LOOP goods_loop;
   
   
    END $$
   
------------------------------------  
   
mysql> select * from goods;
   -> $$
+----+----------+--------+
| id | name     | price  |
+----+----------+--------+
|  1 | goods_01 |  77.56 |
|  2 | goods_02 | 147.56 |
|  3 | goods_03 | 156.36 |
|  4 | goods_04 |  58.36 |
|  5 | goods_05 | 458.68 |
|  6 | goods_06 | 485.55 |
|  7 | goods_07 | 785.22 |
|  8 | goods_08 |  45.36 |
|  9 | goods_09 |  47.36 |
| 10 | goods_10 | 456.36 |
| 11 | goods_11 | 654.85 |
| 12 | goods_12 | 785.25 |
+----+----------+--------+

----------------------------------------
CALL  p_goods() $$

----------------------------------------
mysql> CALL p_goods() $$
Query OK, 1 row affected (0.13 sec)

mysql>  select *from goods$$
+----+----------+--------+
| id | name     | price  |
+----+----------+--------+
|  1 | goods_01 |  62.05 |
|  2 | goods_02 | 118.05 |
|  3 | goods_03 | 125.09 |
|  4 | goods_04 |  46.69 |
|  5 | goods_05 | 366.94 |
|  6 | goods_06 | 388.44 |
|  7 | goods_07 | 628.18 |
|  8 | goods_08 |  36.29 |
|  9 | goods_09 |  37.89 |
| 10 | goods_10 | 365.09 |
| 11 | goods_11 | 523.88 |
| 12 | goods_12 | 628.20 |
+----+----------+--------+
12 rows in set (0.00 sec)

---------------------------------------
example-010 TEST IS OK  ,Finished

---------------------------------------
---------------------------------------

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
                                異常捕獲
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

SP里面的SQL命令在執(zhí)行的過程中可能會(huì)出錯(cuò)，所以MYSQL也像其他一些程序語(yǔ)言一樣向程序員提供一種利用 ‘異常處理器’來響應(yīng)和處理這類錯(cuò)誤的機(jī)制。

在一個(gè)BEGIN - END 語(yǔ)句塊里，對(duì)‘異常處理器’的定義必須出現(xiàn)在變量，光標(biāo)，出錯(cuò)條件的聲明之后。在其他SQL命令之前。

語(yǔ)法:

DECLARE type HANDLER FOR condition1[,condition2,condition3,.......] handler_action

下面對(duì)語(yǔ)法中的type , condition , handler_action 來進(jìn)行解釋:

<1>type(異常捕獲處理類型) ?？梢赃x擇的類型目前只有 CONTINUE 和 EXIT 兩種。(未來的MySQL版本可能會(huì)增加第3種選擇:UNDO)
CONTINUE : 如果當(dāng)前命令在執(zhí)行時(shí)發(fā)生錯(cuò)誤，繼續(xù)執(zhí)行下一條命令。
EXIT     : 如果當(dāng)前命令在執(zhí)行時(shí)發(fā)生錯(cuò)誤，跳出當(dāng)前的BEGIN - END 語(yǔ)句塊。

<2>condition (捕獲異常條件)。這里可以列出一個(gè)到多個(gè)捕獲異常條件。它們是異常處理器要捕捉的目標(biāo)。捕獲異常條件可以用以下幾種方式給出:

SQLSTATE 'errorcode'  單個(gè)SQL異常代碼，編號(hào)是errorcode
SQLWARNING   含蓋了SQLSTATE編號(hào)為01nnn的所有異常
NOT FOUND   含蓋了所有其他的(即SQLSTATE編號(hào)不是01 和 02開頭的)的異常
mysqlerrorcode   這個(gè)數(shù)字是MySQL異常的代碼而不是一個(gè)SQLSTATE異常的代碼
conditionname   用一個(gè)DECLARE CONDITION 命令定義的異常,conditionname是異常的名字

<3>handler_action 異常被拋出時(shí)要執(zhí)行的命令。它將在異常拋出后， CONTINUE or EXIT 執(zhí)行前運(yùn)行。
  因?yàn)檫@里只能放上一條命令，所以通常它是一個(gè)變量賦值命令。
 
 
-------------------------------------------------------------------
聲明異常捕獲條件(自定義異常)
所謂的"聲明異常捕獲" 就是給異常編碼定義一個(gè)簡(jiǎn)明易記的名字。
定義一定要在異常出現(xiàn)以前定義。定義出來的異常捕獲名可以用在出錯(cuò)的異常捕獲器定義中。

語(yǔ)法:
DECLARE condition_name CONDITION FOR {SQLSTATE sqlstate_code | MySQL_error_code};

例:
DECLARE foreign_key_error CONDITION FOR 1216;
DECLARE CONTINUE HANDLER FOR foreign_key_error MySQL_statements;

優(yōu)先級(jí):

當(dāng)同時(shí)使用MySQl錯(cuò)誤碼,標(biāo)準(zhǔn)SQLSTATE錯(cuò)誤碼,命名條件(SQLEXCEPTION)來定義錯(cuò)誤處理時(shí)，其捕獲順序是（只可捕獲一條錯(cuò)誤）:

MySQl錯(cuò)誤碼--->SQLSTATE錯(cuò)誤碼--->命名條件(SQLEXCEPTION)

具體的SQL_STATE 請(qǐng)參考

--------------------------------------------------------------------
異常的觸發(fā)

MYSQL中異常的出發(fā)只能靠執(zhí)行非法代碼來實(shí)現(xiàn)。 而不能如同Oracle，直接有"RAISE Exception"來實(shí)現(xiàn)的。

--------------------------------------------------------------------

SP中的打印語(yǔ)句。
我們?cè)贠racle中寫存儲(chǔ)過程，會(huì)很常用到一個(gè)打印函數(shù)“DBMS_OUTPUT.PUT_LINE('要打印的內(nèi)容');”
很可惜，在MySQL中沒有類似的函數(shù)。
但是我們可以通過變通來實(shí)現(xiàn)該功能。
利用 SELECT '我們想要讓計(jì)算機(jī)打印出來的內(nèi)容'  來實(shí)現(xiàn)。
語(yǔ)法：

SELECT "Content" as result;
SELECT CONCAT(A1,A2[,A3,A4,.....]) as result ;

寫一個(gè)例子：
example-011

delimiter $$

--------
DROP PROCEDURE IF EXISTS p_print $$

--------
CREATE PROCEDURE p_print()
BEGIN

DECLARE i int default 1;

myloop : LOOP

select concat("這是第",i,"次顯示數(shù)據(jù)") as printResult;

set i=i+1;
IF i>10 THEN   LEAVE myloop; END IF;

END LOOP myloop;

END$$

---------
call p_print()$$

----------------------------------------
example-012

寫一個(gè)循環(huán) ，我們來循環(huán)捕捉錯(cuò)誤。

delimiter $$
--------------
DROP PROCEDURE IF EXISTS p_exception $$
--------------
CREATE PROCEDURE p_exception()
BEGIN

DECLARE num int default 0;

DECLARE table_notfound_error CONDITION FOR 1146 ;

DECLARE CONTINUE HANDLER FOR table_notfound_error SELECT CONCAT("TABLE is not exit FOR ---",num) as message;

myloop:LOOP
set num = num + 1;
select * from exception; -- 1146 errorcode

IF num >= 10 THEN

LEAVE myloop;

END IF;

END LOOP myloop;

END$$

-----------------
TEST IS OK

-----------------------------------------------------
-----------------------------------------------------

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
                             游標(biāo)
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

說起游標(biāo)，我就想起了Oracle中的游標(biāo)。 如果你沒有學(xué)習(xí)過Oracle中的游標(biāo)，沒有關(guān)系。因?yàn)镸YSQL的游標(biāo)更簡(jiǎn)單。

游標(biāo)(CURSOR)：是構(gòu)建在MYSQL中，用來查詢數(shù)據(jù)，獲得記錄集合的指針。他可以讓開發(fā)者一次訪問結(jié)果集中一行。

MYSQL 中只有顯式游標(biāo) 這 一種游標(biāo)。

--------------------------------
游標(biāo)的使用方法(使用過程)。

(1)聲明游標(biāo)。
(2)打開游標(biāo)。
(3)從游標(biāo)中獲取記錄。
(4)關(guān)閉游標(biāo)。

---------------------------------

(1)聲明游標(biāo)。
語(yǔ)法 :

DECLARE cursorname CURSOR FOR "YOUR SQL";

--------

(2)打開游標(biāo)。

OPEN cursorname;

--------

(3)從游標(biāo)中獲取記錄。

FETCH cursorname INTO v1,v2,....;

在ORACEL 中，游標(biāo)中沒有數(shù)值的時(shí)候 %FOUND 就會(huì) 返回一個(gè) FALSE。
但是在MYSQL 中FETCH 到最后就會(huì)觸發(fā)一個(gè)1329號(hào)錯(cuò)誤 "No data to fetch".相應(yīng)的SQLSTATE 為 02000。
這個(gè)異常是無法避免的，所以我們都會(huì)用異常捕捉器來捕捉它。（可以直接聲明一個(gè)對(duì)應(yīng)的異常捕捉器，也可以聲明一個(gè) NOT FOUND 的異常捕捉器）

---------

(4)關(guān)閉游標(biāo)。

CLOSE cursorname 。

注釋: 其實(shí)這樣做 也就增加邏輯性。其實(shí)光標(biāo)會(huì)在BEGIN - END 塊結(jié)束的時(shí)候自動(dòng)關(guān)閉。所以很多程序員都不會(huì)手動(dòng)關(guān)閉游標(biāo)。

-----------------------

實(shí)例練習(xí):
example-013

題目，創(chuàng)建一個(gè)多字段的表 student ,有 id, name ,intime 三個(gè)字段。里面寫入有多行記錄。
用游標(biāo)來獲得里面的所有 記錄，并且 一行一行的輸出。

--------------
delimiter $$

--------------
DROP TABLE IF EXISTS student $$

--------------
CREATE TABLE student(
id int primary key auto_increment,
name varchar(50),
intime timestamp(14)
)$$

--------------

insert into student(name,intime) values('s-1','1999-08-25 12:30:30')$$
insert into student(name,intime) values('s-2','1999-08-25 12:30:30')$$
insert into student(name,intime) values('s-3','1999-08-25 12:30:30')$$
insert into student(name,intime) values('s-4','1999-08-25 12:30:30')$$
insert into student(name,intime) values('s-5','1999-08-25 12:30:30')$$
insert into student(name,intime) values('s-6','1999-08-25 12:30:30')$$
insert into student(name,intime) values('s-7','1999-08-25 12:30:30')$$
insert into student(name,intime) values('s-8','1999-08-25 12:30:30')$$
insert into student(name,intime) values('s-9','1999-08-25 12:30:30')$$

commit $$
---------------

DROP PROCEDURE IF EXISTS p_readcursor $$

---------------

CREATE PROCEDURE p_readcursor()
BEGIN

DECLARE p_id int default 0;
DECLARE p_name varchar(50) default "unknow";
DECLARE p_intime timestamp(14) default '0000-00-00 00:00:00';

DECLARE student_cursor CURSOR FOR select id,name,intime from student;

DECLARE EXIT HANDLER FOR 1329 SELECT "CURSOR IS END --> OK" as message;

OPEN student_cursor;

myloop: LOOP

FETCH student_cursor into p_id,p_name,p_intime;

IF p_id=100 THEN LEAVE myloop; END IF;

select p_id,p_name,p_intime ;

END LOOP myloop;

CLOSE student_cursor;

END $$

----------------
call p_readcursor()$$

-------TEST IS OK----------
---------------------------

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
  觸發(fā)器
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$


觸發(fā)器的用途是 在INSERT 、UPDATE 、DELETE命令之前 或則 之后自動(dòng)調(diào)動(dòng)SQL命令或SP。比如說，可以為每一個(gè)UPDATE操作測(cè)試被修改的數(shù)據(jù)是否滿足特定條件。
在MYSQL5.0里邊觸發(fā)器還很不完善。與SP相比，觸發(fā)器還遠(yuǎn)沒有成熟到可以用于實(shí)際應(yīng)用程序中的地步。根據(jù)MySQL在線文檔里的說法，MYSQL5.1版本中將提供更多觸發(fā)器的功能。
在5.1版本出來以前，觸發(fā)器只能完成一些很初級(jí)的任務(wù)。

(1)創(chuàng)建觸發(fā)器
(2)查詢數(shù)據(jù)庫(kù)中的觸發(fā)器
(3)刪除觸發(fā)器

------------------------------
(1) 創(chuàng)建觸發(fā)器

語(yǔ)法:

CREATE TRIGGER trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE
ON tablename [FOR EACH ROW]
BEGIN
commands;
END

注釋： * 最多可以為同一個(gè)數(shù)據(jù)表定義6個(gè)觸發(fā)器，分別為 INSERT , UPDATE 或 DELETE 命令的前 , 后各定義一個(gè)。
* 觸發(fā)器的名字在同一個(gè)數(shù)據(jù)庫(kù)中必須唯一。
* 觸發(fā)器代碼體要 以 BEGIN 開始， END 結(jié)束。

功能局限:
* 觸發(fā)器代碼里無法訪問任何數(shù)據(jù)表，就連觸發(fā)器為之定義的那個(gè)數(shù)據(jù)表也不能訪問。自然就不能使用 DELETE,UPDATE,INSERT 來修改數(shù)據(jù)庫(kù)表。
* MySQL沒有提供可以用來取消DELETE，UPDATE，INSERT命令的命令或語(yǔ)法元素。
* 在觸發(fā)器代碼里不能調(diào)用事務(wù)命令。

OLD and NEW

在觸發(fā)器代碼里，可以通過以下方式去訪問當(dāng)前記錄的各個(gè)字段。
OLD.columname 返回一條現(xiàn)有記錄在被刪除或修改之前的內(nèi)容(UPDATE,DELETE).
NEW.columname 返回一條新記錄或被修改記錄的新內(nèi)容(INSERT ,UPDATE).

---------------------------------
(2)查詢數(shù)據(jù)庫(kù)中的觸發(fā)器

暫時(shí)還沒相關(guān)命令來查看自定義的觸發(fā)器。(他們做地太差了，HOHO)

---------------------------------
(3)刪除觸發(fā)器

語(yǔ)法:
DROP TRIGGER [databasename.]triggername

注釋: 刪除trigger不支持 IF EXISTS 變體。

---------------------------------
做一個(gè)例子:

example-014

delimiter $$

--------------
DROP TABLE IF EXISTS student_score$$

--------------
CREATE TABLE student_score (
id int primary key auto_increment,
name varchar(50),
score int
)$$

--------------

DROP TRIGGER student_score_insert_before$$

--------------

CREATE TRIGGER student_score_insert_before
BEFORE INSERT ON student_score FOR EACH ROW
BEGIN

IF NEW.score<0 or="" new.score="">100 THEN
SET NEW.score = 0;
END IF;

END$$

--------------

Insert into student_score(name,score)values('ZhangSan',12)$$
Insert into student_score(name,score)values('LiSi',-12)$$
Insert into student_score(name,score)values('WangWu',112)$$

mysql> select * from student_score$$
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | ZhangSan |    12 |
|  2 | LiSi     |     0 |
|  3 | WangWu   |     0 |
+----+----------+-------+

筆記結(jié)束，祝賀大家學(xué)習(xí)愉快.

上述就是小編為大家分享的怎么理解MySQL存儲(chǔ)過程和觸發(fā)器了，如果剛好有類似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。