Create procedure/function語句

? create procedure用來創(chuàng)建存儲過程螺捐，create function用來創(chuàng)建函數(shù)

? 函數(shù)與存儲過程最大的區(qū)別就是函數(shù)調(diào)用有返回值颠悬，調(diào)用存儲過程用call語句，而調(diào)用函數(shù)就直接引用函數(shù)名+參數(shù)即可
? Definer和sql security子句指定安全環(huán)境
? Definder是MySQL的特殊的訪問控制手段定血，當(dāng)數(shù)據(jù)庫當(dāng)前沒有這個用戶權(quán)限時赔癌，執(zhí)行存儲過程可能會報錯
? sql secuirty的值決定了調(diào)用存儲過程的方式，取值：definer（默認(rèn)）或者invoker definer:在執(zhí)行存儲過程前驗證definer對應(yīng)的用戶如：cdq@127.0.0.1是否存在澜沟，以及是否具有執(zhí)行存儲過程的權(quán)限灾票，若沒有則報錯 invoker:在執(zhí)行存儲過程時判斷inovker即調(diào)用該存儲過程的用戶是否有相應(yīng)權(quán)限，若沒有則報錯
? IN,OUT,INOUT三個參數(shù)前的關(guān)鍵詞只適用于存儲過程倔喂，對函數(shù)而言所有的參數(shù)默認(rèn)都是輸入?yún)?shù)
? IN輸入?yún)?shù)用于把數(shù)值傳入到存儲過程中铝条；OUT輸出參數(shù)將數(shù)值傳遞到調(diào)用者，初始值是NULL席噩；INOUT輸入輸出參數(shù)把數(shù)據(jù)傳入到存儲過程，在存儲過程中修改之后再傳遞到調(diào)用者

 mysql> delimiter //
 mysql> CREATE PROCEDURE simpleproc (IN param1 int,OUT param2 INT)
 -> BEGIN
 -> SELECT COUNT(*) INTO param2 FROM students where sid>param1;
 -> END//
 Query OK, 0 rows affected (0.03 sec)
 Delimiter ;
 mysql> CALL simpleproc(1, @a);
 Query OK, 0 rows affected (0.00 sec)
 mysql> SELECT @a;
 +------+
 | @a |
 +------+
 | 2 |
 +------+
 1 row in set (0.00 sec)

? Delimiter命令是改變語句的結(jié)束符贤壁，MySQL默認(rèn)的結(jié)束符為;號悼枢，由于procedure和function中的;號并不代表創(chuàng)建的結(jié)束，所以要替換成另外的結(jié)束符以便表示創(chuàng)建的結(jié)束
? rontine_body子句可以包含一個簡單的SQL語句脾拆，也可以包含多個SQL語句馒索，通過begin…end將這多個SQL語句包含在一起
? MySQL存儲過程和函數(shù)中也可以包含類似create和drop等DDL語句
? comment子句用來寫入對存儲過程和函數(shù)的注釋
? Language子句用來表示此存儲過程和函數(shù)的創(chuàng)建語言
? 存儲過程和函數(shù)被標(biāo)注為deterministic表明當(dāng)輸入相同的參數(shù)是會返回相同的結(jié)果，反之如果是not deterministic則表示相同參數(shù)不會是相同結(jié)果名船，默認(rèn)是not deterministic

 mysql> delimiter //
 mysql> create function simplefunc(param1 int)
 -> returns int
 -> begin
 -> update students set sex=1 where sid=param1;
 -> select count(*) into @a from students where sid>param1;
 -> return @a;
 -> end;
 -> //
 Query OK, 0 rows affected (0.00 sec)
 mysql> delimiter ;
 mysql> select simplefunc(1);
 +---------------+
 | simplefunc(1) |
 +---------------+
 | 2             |
+----------------+
 mysql> select * from students where sid=1;
 +------+-------+------+
 | sid | sname  | sex  |
 +------+-------+------+
 | 1   | a      | 1    | 
 +------+-------+------+


[root@localhost ~]# mysql -u cdq –p
delimiter //
CREATE PROCEDURE simpleproc (IN param1 int,OUT param2 INT)
BEGIN
SELECT COUNT(*) INTO param2 FROM students where sid>param1;
END//
delimiter ;


[root@localhost ~]# mysql -u root –p
mysql> call simpleproc(1,@a);
mysql> drop user cdq@localhost;
mysql> call simpleproc(1,@a);
ERROR 1449 (HY000): The user specified as a definer ('cdq'@'localhost') does not exist
mysql> alter procedure simpleproc sql security invoker;
mysql> call simpleproc(1,@a);

? 相關(guān)屬性短語只有咨詢含義绰上，并不是強(qiáng)制性的約束

? Contains sql表明此存儲過程或函數(shù)不包含讀或者寫數(shù)據(jù)的語句，這是默認(rèn)屬性
? NO SQL表示此存儲過程或函數(shù)不包含SQL語句
? Reads sql data表示此存儲過程包含諸如select的查詢數(shù)據(jù)的語句渠驼，但不包含插入或刪除數(shù)據(jù)的語句
? Modifies sql data表示此存儲過程包含插入或刪除數(shù)據(jù)的語句

drop procedure/function語句

? Drop procedure/function語句用來刪除指定名稱的存儲過程或函數(shù)

? If exists關(guān)鍵詞用來避免在刪除一個本身不存在的存儲過程或函數(shù)時蜈块，MySQL返回錯誤

 mysql> drop procedure simpleproc;
 Query OK, 0 rows affected (0.00 sec)
 mysql> drop procedure simpleproc;
 ERROR 1305 (42000): PROCEDURE part2_cn.simpleproc does not exist
 mysql> drop function if exists simplefunc;
 Query OK, 0 rows affected, 1 warning (0.01 sec)

Begin…end復(fù)合語句

? Begin…end語句通常出現(xiàn)在存儲過程、函數(shù)和觸發(fā)器中，其中可以包含一個或多個語句百揭，每個語句用;號隔開

標(biāo)簽語句

? 標(biāo)簽label可以加在begin…end語句以及l(fā)oop, repeat和while語句
? 語句中通過iterate和leave來控制流程爽哎，iterate表示返回指定標(biāo)簽位置，leave表示跳出標(biāo)簽

 mysql> delimiter //
 mysql> CREATE PROCEDURE doiterate(IN p1 int, OUT p2 int)
 -> BEGIN
 -> label1: LOOP
 -> SET p1 = p1 + 1;
 -> IF p1 < 10 THEN ITERATE label1; END IF;
 -> LEAVE label1;
 -> END LOOP label1;
 -> set p2=p1;
 -> END;
 -> //
 Query OK, 0 rows affected (0.00 sec)
 mysql> delimiter ;
 mysql> call doiterate(1,@a);
 Query OK, 0 rows affected (0.00 sec)
 mysql> select @a;
 +------+
 | @a |
 +------+
 | 10 |
 mysql> call doiterate(5,@a);
 Query OK, 0 rows affected (0.00 sec)
 mysql> select @a;
 +------+
 | @a |
 +------+
 | 10 | 
 +------+

Declare語句

? Declare語句通常用來聲明本地變量器一、游標(biāo)课锌、條件或者h(yuǎn)andler
? Declare語句只允許出現(xiàn)在begin … end語句中而且必須出現(xiàn)在第一行
? Declare的順序也有要求，通常是先聲明本地變量祈秕，再是游標(biāo)渺贤，然后是條件和handler

存儲過程中的變量

? 本地變量可以通過declare語句進(jìn)行聲明
? 聲明后的變量可以通過select … into var_list進(jìn)行賦值，或者通過set語句賦值请毛，或者通過定義游標(biāo)并使用fetch … into var_list賦值
? 通過declare聲明變量方法：

? 使用default指定變量的默認(rèn)值志鞍，如果沒有指定默認(rèn)值則初始值為NULL
? Type指明該變量的數(shù)據(jù)類型
? 聲明的變量作用范圍為被聲明的begin … end語句塊之間
? 聲明的變量和被引用的數(shù)據(jù)表中的字段名要區(qū)分開來

 delimiter //
 CREATE PROCEDURE sp1 (v_sid int)
 BEGIN
 DECLARE xname VARCHAR(5) DEFAULT 'bob';
 DECLARE xsex INT;
 SELECT sname, sex INTO xname, xsex
 FROM students WHERE sid= v_sid;
 SELECT xname,xsex;
 END;
 //
 delimiter ;

 mysql> call sp1(1);
 +-------+------+
 | xname | xsex |
 +-------+------+
 | a     | 1    |
 +-------+------+

流程控制語句

? MySQL支持if,case,iterate,leave,loop,while,repeat語句作為存儲過程和函數(shù)中的流程控制語句，另外return語句也是函數(shù)中的特定流程控制語句

流程控制case語句

? Case語句在存儲過程或函數(shù)中表明了復(fù)雜的條件選擇語句

? 第一個語句中case_value與后面各句的when_value依次做相等的對比获印，如果碰到相等的述雾，則執(zhí)行對應(yīng)的后面的statement_list，否則接著對比兼丰，如果都沒有匹配玻孟，則執(zhí)行else后面的statement_list
? 第二個語句中當(dāng)search_condition滿足true/1的結(jié)果時，則執(zhí)行對應(yīng)的statement_list鳍征，否則執(zhí)行else對應(yīng)的statement_list
? Statement_list可以包含一個或多個SQL語句

 delimiter //
 CREATE PROCEDURE exp_case(v_sid int)
 BEGIN
 DECLARE v INT DEFAULT 1;
 select sex into v from students where sid=v_sid;
 CASE v
 WHEN 0 THEN update students set sex=1 where sid=v_sid;
 WHEN 1 THEN update students set sex=0 where sid=v_sid;
 ELSE
 update students set sex=-1 where sid=v_sid;
 END CASE;
 END;
 //
 delimiter ;

 mysql> call exp_case(1);
 Query OK, 1 row affected (0.01 sec) 

 delimiter //
 CREATE PROCEDURE exp_case2(v_sid int)
 BEGIN
 DECLARE v INT DEFAULT 1;
 select sex into v from students where sid=v_sid;
 CASE
 WHEN v=0 THEN update students set sex=1 where sid=v_sid;
 WHEN v=1 THEN update students set sex=0 where sid=v_sid;
 ELSE
 update students set sex=-1 where sid=v_sid;
 END CASE;
 END;
 //
 delimiter ;

 mysql> call exp_case2(1);
 Query OK, 1 row affected (0.01 sec)

流程控制IF語句

? IF語句在存儲過程或函數(shù)中表明了基礎(chǔ)的條件選擇語句

? IF語句中如果search_condition滿足true/1的條件黍翎，則執(zhí)行對應(yīng)的statement_list，否則再判斷elseif中的search_condition是否滿足true/1的條件艳丛，如果都不滿足則執(zhí)行else中的statement_list語句
? Statement_list中可以包含一個或多個SQL語句

 DELIMITER //
 CREATE FUNCTION SimpleCompare(n INT, m INT)
 RETURNS VARCHAR(20)
 BEGIN
 DECLARE s VARCHAR(20);
 IF n > m THEN SET s = '>';
 ELSEIF n = m THEN SET s = '=';
 ELSE SET s = '<';
 END IF;
 SET s = CONCAT(n, ' ', s, ' ', m);
 RETURN s;
 END //
 DELIMITER ;

 mysql> select simplecompare(1,2);
 +--------------------+
 | simplecompare(1,2) |
 +--------------------+
 | 1 < 2              |
 +--------------------+
 1 row in set (0.02 sec)

 DELIMITER //
 CREATE FUNCTION VerboseCompare (n INT, m INT) ##嵌套if語句
 RETURNS VARCHAR(50)
 BEGIN
 DECLARE s VARCHAR(50);
 IF n = m THEN SET s = 'equals';
 ELSE
  IF n > m THEN SET s = 'greater';
  ELSE SET s = 'less';
  END IF;
 SET s = CONCAT('is ', s, ' than');
 END IF;
 SET s = CONCAT(n, ' ', s, ' ', m, '.');
 RETURN s;
 END //
 DELIMITER ;

 mysql> select verbosecompare(1,2);
 +---------------------+
 | verbosecompare(1,2) |
 +---------------------+
 | 1 is less than 2.   | 
 +---------------------+
 1 row in set (0.02 sec)

注：
如果報錯：ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
翻譯：這個函數(shù)沒有確定性匣掸，沒有SQL，或者在聲明中讀取SQL數(shù)據(jù)氮双，并且啟用了二進(jìn)制日志記錄(您可能想要使用不太安全的log bin trust函數(shù)creator變量)
這是我們開啟了 bin-log, 我們就必須指定我們的函數(shù)是否是
1 DETERMINISTIC 不確定的
2 NO SQL 沒有SQl語句碰酝，當(dāng)然也不會修改數(shù)據(jù)
3 READS SQL DATA 只是讀取數(shù)據(jù)，當(dāng)然也不會修改數(shù)據(jù)
4 MODIFIES SQL DATA 要修改數(shù)據(jù)
5 CONTAINS SQL 包含了SQL語句

其中在 function 里面戴差，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持送爸。如果我們開啟了 bin-log, 我們就必須為我們的 function 指定一個參數(shù)。
在 MySQL 中創(chuàng)建函數(shù)時出現(xiàn)這種錯誤的解決方法：
set global log_bin_trust_function_creators=TRUE;

流程控制iterate語句

? Iterate語句僅出現(xiàn)在loop,repeat,while循環(huán)語句中暖释，其含義表示重新開始此循環(huán)

? Label表示自定義的標(biāo)簽名

流程控制leave語句

? Leave語句表明退出指定標(biāo)簽的流程控制語句塊
? 通常會用在begin…end袭厂，以及l(fā)oop,repeat,while的循環(huán)語句中

? Label表明要退出的標(biāo)簽名

流程控制iterate和leave語句

 mysql> delimiter //
 mysql> CREATE PROCEDURE doiterate(IN p1 INT, OUT p2 int)
 -> BEGIN
 -> label1: LOOP
 -> SET p1 = p1 + 1;
 -> IF p1 < 10 THEN ITERATE label1; END IF;
 -> LEAVE label1;
 -> END LOOP label1;
 -> set p2=p1;
 -> END;
 -> //
 Query OK, 0 rows affected (0.00 sec)
 mysql> delimiter ;

流程控制loop語句

? Loop語句是存儲過程或函數(shù)中表達(dá)循環(huán)執(zhí)行的一種方式

? 其中的statement_list可以包含一個或多個SQL語句

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
 label1: LOOP
 SET p1 = p1 + 1;
 IF p1 < 10 THEN
 ITERATE label1;
 END IF;
 LEAVE label1;
 END LOOP label1;
 SET @x = p1;
END;

流程控制repeat語句

? repeat語句是存儲過程或函數(shù)中表達(dá)循環(huán)執(zhí)行的一種方式

? Repeat語句中statement_list一直重復(fù)執(zhí)行直到search_condition條件滿足
? Statement_list可以包含一個或多個SQL語句

 mysql> delimiter //
 mysql> CREATE PROCEDURE dorepeat(p1 INT)
 -> BEGIN
 -> SET @x = 0;
 -> REPEAT
 -> SET @x = @x + 1;
 -> UNTIL @x > p1 END REPEAT;
 -> END
 -> //
 Query OK, 0 rows affected (0.00 sec)
 mysql> delimiter ;
 mysql> CALL dorepeat(1000)//
 Query OK, 0 rows affected (0.00 sec)
 mysql> SELECT @x//
 +------+
 | @x   |
 +------+
 | 1001 |
 +------+
 1 row in set (0.00 sec)

流程控制while語句

? while語句是存儲過程或函數(shù)中表達(dá)循環(huán)執(zhí)行的一種方式

? 當(dāng)search_condition返回為true時，則循環(huán)執(zhí)行statement_list中的語句球匕，直到search_condition的結(jié)果返回為false
? Statement_list中可以包含一個或多個SQL語句

 DELIMITER //
 CREATE PROCEDURE dowhile()
 BEGIN
 DECLARE v1 INT DEFAULT 5;
 WHILE v1 > 0 DO
 update students set sex=-1 where sid=v1;
 SET v1 = v1 - 1;
 END WHILE;
 END;
 //
 DELIMITER ;

 mysql> call dowhile();
 Query OK, 1 row affected (0.00 sec)
 mysql> select * from students;
 +------+-------+------+
 | sid  | sname | sex  |
 +------+-------+------+
 | 1    | a     | -1   |
 | 2    | b     | -1   |
 | 3    | c     | -1   |
 +------+-------+------+

流程控制return語句

? Return語句用在函數(shù)中纹磺，用來終結(jié)函數(shù)的執(zhí)行并將指定值返回給調(diào)用者

? 在函數(shù)中必須要有至少一個return語句，當(dāng)有多個return語句時則表明函數(shù)有多種退出的方式

 delimiter //
 create function doreturn()
 returns int
 begin
 select sex into @a from students where sid=1;
 if @a=1 then return 1;
 elseif @a=0 then return 0;
 else return 999;
 end if;
 end;
 //
 delimiter ;
 mysql> select doreturn();
 +------------+
 | doreturn() |
 +------------+
 | 999 |
 +------------+

cursor游標(biāo)

? Cursor游標(biāo)用來聲明一個數(shù)據(jù)集
? 游標(biāo)的聲明必須在變量和條件聲明之后亮曹，在handler聲明之前

 CREATE PROCEDURE curdemo()
 BEGIN
 DECLARE done INT DEFAULT FALSE;
 DECLARE a CHAR(16);
 DECLARE b, c INT;
 DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
 DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 OPEN cur1;
 OPEN cur2;
 read_loop: LOOP
 FETCH cur1 INTO a, b;
 FETCH cur2 INTO c;
 IF done THEN
 LEAVE read_loop;
 END IF;
 IF b < c THEN
 INSERT INTO test.t3 VALUES (a,b);
 ELSE
 INSERT INTO test.t3 VALUES (a,c);
 END IF;
 END LOOP;
 CLOSE cur1;
 CLOSE cur2;
 END;

cursor游標(biāo)close語句

? Cursor close語句用來關(guān)閉之前打開的游標(biāo)

? 如果關(guān)閉一個未打開的游標(biāo)橄杨，則MySQL會報錯
? 如果在存儲過程和函數(shù)中未使用此語句關(guān)閉已經(jīng)打開的游標(biāo)秘症，則游標(biāo)會在聲明的begin…end語句塊執(zhí)行完之后自動關(guān)閉

cursor游標(biāo)declare語句

? Cursor declare語句用來聲明一個游標(biāo)和指定游標(biāo)對應(yīng)的數(shù)據(jù)集合，通常數(shù)據(jù)集合是一個select語句

? Select_statement代表一個select語句

cursor游標(biāo)fetch語句

? Cursor fetch語句用來獲取游標(biāo)指定數(shù)據(jù)集的下一行數(shù)據(jù)并將各個字段值賦予后面的變量

? 數(shù)據(jù)集中的字段需要和INTO語句中定義的變量一一對應(yīng)
? 數(shù)據(jù)集中的數(shù)據(jù)都fetch完之后讥珍，則返回NOT FOUND

cursor游標(biāo)open語句

? Open cursor語句用來打開一個之前已經(jīng)聲明好的游標(biāo)

Declare condition語句

? Declare condition語句命名特定的錯誤條件历极，而該特定錯誤可以在declare…h(huán)andler中指定處理方法

? Condition_value指定特定的錯誤條件，可以有以下兩種形式

? Mysql_err_code表示MySQL error code的整數(shù)
? SQLSTATE sqlstate_value表示MySQL中用5位字符串表達(dá)的語句狀態(tài)

? 比如在MySQL中1051error code表示的是unknown table的錯誤衷佃，如果要對這
個錯誤做特殊處理趟卸，可以用三種方法：

 DECLARE CONTINUE HANDLER FOR 1051
 BEGIN
 -- body of handler
 END; 

 DECLARE no_such_table CONDITION FOR 1051;
 DECLARE CONTINUE HANDLER FOR no_such_table
 BEGIN
 -- body of handler
 END;

 DECLARE no_such_table CONDITION FOR SQLSTATE '42S02';
 DECLARE CONTINUE HANDLER FOR no_such_table
 BEGIN
 -- body of handler
 END;

Declare handler語句

? Declare handler語句用來聲明一個handler來處理一個或多個特殊條件，當(dāng)其中的某個條件滿足時則觸發(fā)其中的statement語句執(zhí)行
? Statement可以是一個簡單SQL語句氏义，也可以是begin…end組成的多個語句

? Handler_action子句聲明當(dāng)執(zhí)行完statement語句之后應(yīng)該怎么辦

? Continue代表繼續(xù)執(zhí)行該存儲過程或函數(shù)
? Exit代表退出聲明此handler的begin…end語句塊
? Undo參數(shù)已經(jīng)不支持

Condition_value的值有以下幾種：

? Mysql_err_code表示MySQL error code的整數(shù)
? SQLSTATE sqlstate_value表示MySQL中用5位字符串表達(dá)的語句狀態(tài)
? Condition_name表示之前在declare…condition語句中聲明的名字
? SQLWARNING表示所有的警告信息锄列，即SQLSTATE中01打頭的所有錯誤
? NOT FOUND表示查完或者查不到數(shù)據(jù)，即SQLSTATE中02打頭的所有錯誤
? SQLEXCEPTION表示所有的錯誤信息

 DECLARE CONTINUE HANDLER FOR 1051
 BEGIN
 -- body of handler
 END;

 DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
 BEGIN
 -- body of handler
 END;

 DECLARE CONTINUE HANDLER FOR SQLWARNING
 BEGIN
 -- body of handler
 END;

 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
 BEGIN
 -- body of handler
 END;

? 當(dāng)condition發(fā)生但沒有聲明handler時惯悠，則存儲過程和函數(shù)依照如下規(guī)則處理

? 發(fā)生SQLEXCEPTION錯誤邻邮，則執(zhí)行exit退出
? 發(fā)生SQLWARNING警告，則執(zhí)行contine繼續(xù)執(zhí)行
? 發(fā)生NOT FOUND情況克婶，則執(zhí)行continue繼續(xù)執(zhí)行

 比如SQLSTATE '23000'表示主鍵沖突錯誤
 mysql> CREATE TABLE t (s1 INT, PRIMARY KEY (s1));
 Query OK, 0 rows affected (0.00 sec)
 mysql> delimiter //
 mysql> CREATE PROCEDURE handlerdemo ()
 -> BEGIN
 -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
 -> SET @x = 1;
 -> INSERT INTO test.t VALUES (1);
 -> SET @x = 2;
 -> INSERT INTO test.t VALUES (1);
 -> SET @x = 3;
 -> END;
 -> //
 Mysql> delimiter ;
 mysql> CALL handlerdemo();
 mysql> SELECT @x;
 +------+
 | @x |
 +------+
 | 3 |
 +------+
 1 row in set (0.00 sec)

 CREATE PROCEDURE curdemo()
 BEGIN
 DECLARE done INT DEFAULT FALSE;
 DECLARE a CHAR(16);
 DECLARE b, c INT;
 DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
 DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 OPEN cur1;
 OPEN cur2;
 read_loop: LOOP
 FETCH cur1 INTO a, b;
 FETCH cur2 INTO c;
 IF done THEN
 LEAVE read_loop;
 END IF;
 IF b < c THEN
 INSERT INTO test.t3 VALUES (a,b);
 ELSE
 INSERT INTO test.t3 VALUES (a,c);
 END IF;
 END LOOP;
 CLOSE cur1;
 CLOSE cur2;
 END;

操作

創(chuàng)建一個過程筒严，將男生和女生的學(xué)生數(shù)據(jù)分別存儲到男生表和女生表中。

create procedure proc1() as BeginInsert 
into students_male select * from students where gender=0;
Insert into students_female select * from students where gender=1;
End;

創(chuàng)建一個過程情萤，將每個學(xué)生的課程數(shù)鸭蛙，平均成績，及格課程數(shù)筋岛，非及格課程數(shù)都存放在單獨的表中

create procedure proc2() as BeginInsert 
into temp1 select sid,count(*),avg(score),sum(case when score>=60 then 1 else 0 end),
sum(case when score<60 then 1 else 0 end) from score group by sid;
End;

創(chuàng)建一個過程娶视，將學(xué)生sid作為輸入?yún)?shù)，結(jié)果展示出該學(xué)生的課程數(shù)和平均成績

create procedure proc3(IN st_id) as Begin
select sid,count(*),avg(score) from score where sid=st_id group by sid;
End;

創(chuàng)建一個函數(shù)睁宰，以學(xué)生sid作為輸入?yún)?shù)肪获，將該學(xué)生的課程數(shù)和平均成績存放在單獨的表中，并返回平均成績

create function func1(st_id int) as Begin
return int 
declare avg_score int;
insert into temp2 select sid,count(*),avg(score) from score where sid=st_id group by sid;
select avg(score) into avg_score from score where sid=st_id group by Sid;
return avg_score; 
End;

用游標(biāo)的方法實現(xiàn)創(chuàng)建一個函數(shù)柒傻，輸入?yún)?shù)是老師的id孝赫，函數(shù)返回該老師所教授的課程數(shù)量，并將這些學(xué)習(xí)這些課程的每個學(xué)生如果成績不及格红符，把學(xué)生的sid和對應(yīng)課程名字寒锚、成績insert到表A中，如果成績及格违孝，把學(xué)生的sid和對應(yīng)的課程名字、成績insert到表B中

 delimiter //
 Create function func3(v_teacher_id int)
 Returns int
 Begin
 Declare n_course int;
 Declare v_sid int default null;
 Declare v_course_name varchar(60);
 Declare v_score int;
 Declare cur1 cursor for select a.sid,b.course_name,a.score
 From score a inner join course b on a.coure_id=b.id
 Where b.teacher_id=v_teacher_id;
 declare continue handler for not found set v_sid=null;
 Select count(*) into n_course from course where teacher_id=v_teacher_id;
 Open cur1;
 Fetch cur1 into v_sid,v_course_name,v_score;
 While v_sid is not null do
 If v_score<60 then
 insert into A select v_sid,v_course_name,v_score;
 Else
 insert into B select v_sid,v_course_name,v_score;
 End if;
 Fetch cur1 into v_sid,v_course_name,v_score;
 End while;
 Close cur1;
 Return n_course;
 End;
 //
 Delimiter ;

create trigger語句

? create trigger語句用來創(chuàng)建一個觸發(fā)器泳赋，觸發(fā)器的作用是當(dāng)表上有對應(yīng)SQL語句發(fā)生時雌桑，則觸發(fā)執(zhí)行
? 觸發(fā)器創(chuàng)建時需要指定對應(yīng)的表名tbl_name

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

? Definer關(guān)鍵詞用來指定trigger的安全環(huán)境
? Trigger_time指定觸發(fā)器的執(zhí)行時間，BEFORE和AFTER指定觸發(fā)器在表中的每行數(shù)據(jù)修改前或者后執(zhí)行
? Trigger_event指定觸發(fā)該觸發(fā)器的具體事件
? INSERT當(dāng)新的一行數(shù)據(jù)插入表中時觸發(fā)祖今，比如通過執(zhí)行insert,load data,replace語句插入新數(shù)據(jù)
? UPDATE當(dāng)表的一行數(shù)據(jù)被修改時觸發(fā)校坑，比如執(zhí)行update語句時
? DELETE當(dāng)表的一行數(shù)據(jù)被刪除時觸發(fā)拣技，比如執(zhí)行delete,replace語句時
? 當(dāng)執(zhí)行insert into … on duplicate key update語句時，當(dāng)碰到重復(fù)行執(zhí)行update時耍目，則觸發(fā)update下的觸發(fā)器
? 從5.7.2版本開始膏斤，可以創(chuàng)建具有相同trigger_time和trigger_event的同一個表上的多個觸發(fā)器，默認(rèn)情況下按照創(chuàng)建的時間依次執(zhí)行邪驮，通過指定FOLLOWS/PRECEDES改變執(zhí)行順序莫辨，即FOLLOWS時表示新創(chuàng)建的觸發(fā)器后執(zhí)行，PRECEDES則表示新觸發(fā)器先執(zhí)行
? Trigger_body表示觸發(fā)器觸發(fā)之后要執(zhí)行的一個或多個語句毅访，在內(nèi)部可以引用涉及表的字段沮榜，OLD.col_name表示行數(shù)據(jù)被修改或刪除之前的字段數(shù)據(jù)，NEW.col_name表示行數(shù)據(jù)被插入或修改之后的字段數(shù)據(jù)

 delimiter //
 create trigger simple_trigger
 after update
 on students for each row
 begin
 insert into students_bak values(old.sid,old.sname,new.sname,old.sex,new.sex,now());
 end;
 //
 delimiter ;
 mysql> update students set sname='abc',sex=1;
 mysql> select * from students_bak;
 +------+----------+----------+--------+--------+---------------------+
 | sid  | oldsname | newsname | oldsex | newsex | tstamp              |
 +------+----------+----------+--------+--------+---------------------+
 | 1    | a        | abc      | -1     | 1      | 2017-03-28 16:28:42 |
 | 2    | b        | abc      | -1     | 1      | 2017-03-28 16:28:42 |
 | 3    | c        | abc      | -1     | 1      | 2017-03-28 16:28:42 |
 +------+----------+----------+--------+--------+---------------------+

Drop trigger語句

? Drop trigger語句用來刪除一個觸發(fā)器

DROP TRIGGER [ IF EXISTS ] [數(shù)據(jù)庫名] <觸發(fā)器名>

? If exists短語用來避免刪除不存在的觸發(fā)器時引發(fā)報錯
? 當(dāng)你執(zhí)行drop table時喻粹，表上的觸發(fā)器也被drop掉了

 mysql> Drop trigger simple_trigger;
 Query OK, 0 rows affected (0.00 sec)

操作

在score表上創(chuàng)建一個觸發(fā)器蟆融，當(dāng)有新的數(shù)據(jù)插入時，在score_bak表里記錄新插入的數(shù)據(jù)的所有字段信息守呜，并用tstamp字段標(biāo)注數(shù)據(jù)的插入時間

 Delimiter //
 create trigger trig1
 after insert on score
 For each row
 Begin
 Insert into score_bak(Sid,course_id,score,tstamp) values(new.sid,new.course_id,new.score,now());
 End;
 //
 Delimiter ;

在score表上創(chuàng)建一個觸發(fā)器型酥，當(dāng)有新的數(shù)據(jù)插入時，在score_avg表里記錄對應(yīng)學(xué)生的所有課程的平均成績（注意查乒，如果在score_avg表里已經(jīng)有了學(xué)生的記錄弥喉，需要update）

 Delimiter //
 create trigger trig2
 After insert on score
 For each row
 Begin
 Declare n int;
 Select count(*) into n from score_avg where sid=new.sid;
 If n=1 then
 update score_avg set avg_score=(select avg(score) from score where sid=new.sid) where sid=new.sid;
 Else
 insert into score_avg select sid,avg(score) from score where sid=new.sid group by sid;
 End if;
 End;
 //
 Delimiter ;

五宝穗、MYSQL存儲過程和函數(shù)

五、MYSQL存儲過程和函數(shù)