存儲(chǔ)過程

• 存儲(chǔ)過程是什么

  存儲(chǔ)過程是一組已經(jīng)編譯好的SQL語句
  

• 存儲(chǔ)過程優(yōu)點(diǎn)有什么

  安全
  性能高
  提高代碼復(fù)用性
  

• 創(chuàng)建存儲(chǔ)過程的語法

  DELIMITER $ # 不能加分號(hào)CREATE PROCEDURE 存儲(chǔ)過程名(IN|OUT|INOUT 參數(shù)名 參數(shù)類型)
  BEGIN存儲(chǔ)過程語句塊
  END;$DELIMITER ;
  

• 創(chuàng)建一個(gè)無參的存儲(chǔ)過程：打印 myemployees 庫中 employees 表中 employee_id = 168的first_name，并調(diào)用

  DELIMITER $CREATE PROCEDURE myprocedure1()
  BEGINSELECT first_nameFROM employeesWHERE employee_id = 168;
  END;$DELIMITER ;CALL myprocedure1();
  

• 創(chuàng)建一個(gè)IN參數(shù)存儲(chǔ)過程：打印傳遞進(jìn)來的參數(shù)

  DELIMITER $CREATE PROCEDURE myprocedure2(IN v INT)
  BEGINSELECT v;
  END;$DELIMITER ;SET @v = 10;CALL myprocedure2(@v);
  

• 創(chuàng)建一個(gè)IN參數(shù)和OUT參數(shù)存儲(chǔ)過程：傳遞來一個(gè)變量 invalue = 10，輸出一個(gè)變量 outvalue = invalue * 2

  DELIMITER $CREATE PROCEDURE myprocedure3(IN invalue INT,OUT outvalue INT)
  BEGINSELECT invalue * 2 INTO outvalue;
  END;$DELIMITER ;SET @invalue = 10;
  SET @outvalue = 0;CALL myprocedure3(@invalue,@outvalue);SELECT @outvalue;
  

• 創(chuàng)建一個(gè)INOUT參數(shù)存儲(chǔ)過程：傳遞進(jìn)來一個(gè)數(shù)，令其變?yōu)?0倍

  DELIMITER $CREATE PROCEDURE myprocedure4(INOUT inoutvalue INT)
  BEGINSET inoutvalue = inoutvalue * 10;
  END;$DELIMITER ;SET @value = 10;CALL myprocedure4(@value);SELECT @value;
  

• 創(chuàng)建一個(gè)IN參數(shù)存儲(chǔ)過程：輸入一個(gè)數(shù)，如果大于0顯示’greater than 0’，小于零顯示’less than 0’，等于0顯示’equals 0’

  DELIMITER $CREATE PROCEDURE myprocedure5(IN target INT)
  BEGINSELECTCASEWHEN target > 0 THEN 'greater than 0'WHEN target = 0 THEN 'equalse 0'ELSE 'less than 0'ENDAS result;
  END;$DELIMITER ;CALL myprocedure5(-1);
  CALL myprocedure5(0);
  CALL myprocedure5(1);
  

• 創(chuàng)建一個(gè)IN參數(shù)和OUT參數(shù)存儲(chǔ)過程：輸入一個(gè)數(shù)，如果等于1顯示’Hello’，等于2顯示’World’，否則顯示’Byebye’，結(jié)果同時(shí)保存到OUT參數(shù)中

  DELIMITER $CREATE PROCEDURE myprocedure6(IN target INT,OUT result VARCHAR(20))
  BEGINSELECTCASE targetWHEN target = 1 THEN 'Hello'WHEN target = 2 THEN 'World'ELSE 'ByeBye'ENDINTO result;
  END;$DELIMITER ;CALL myprocedure6(0,@ret1);
  CALL myprocedure6(1,@ret2);
  CALL myprocedure6(2,@ret3);SELECT @ret1,@ret2,@ret3;
  

• 創(chuàng)建一個(gè)無參的存儲(chǔ)過程：循環(huán)打印 myemployees 庫中 employees 表中 employee_id = 168的first_name3次

  DELIMITER $CREATE PROCEDURE myprocedure5()
  BEGINDECLARE times INT DEFAULT 0;WHILE times < 3 DOSELECT first_nameFROM employeesWHERE employee_id = 168;SET times = times + 1;END WHILE;
  END;$DELIMITER ;CALL myprocedure5();
  

• 創(chuàng)建一個(gè)IN參數(shù)存儲(chǔ)過程：輸入一個(gè)正整數(shù)，打印它這么多次"This is REPEAT UNITL"

  DELIMITER $CREATE PROCEDURE myprocedure6(IN times INT)
  BEGINREPEAT SELECT 'This is REPEAT UNTIL';SET times = times - 1;UNTIL times < 0 END REPEAT;
  END;$DELIMITER ;CALL procedure6(10);
  

• 創(chuàng)建一個(gè)OUT參數(shù)存儲(chǔ)過程：返回1x2x3x…x10的結(jié)果給一個(gè)OUT參數(shù)result，使用LOOP循環(huán)

  DELIMITER $CREATE PROCEDURE myprocedure7(OUT result INT)
  BEGINDECLARE times INT DEFAULT 1;SET result = 1;tt:LOOPSET times = times + 1;SET result = result * times;IF times > 10THEN LEAVE tt;END IF;END LOOP;
  END;$DELIMITER ;SET @result = 0;CALL myprocedure7(@result);SELECT @result;
  

• 查看有哪些存儲(chǔ)過程

  SHOW PROCEDURE STATUS;
  

• 查看存儲(chǔ)過程myprocedure1的創(chuàng)建語句

  SHOW CREATE PROCEDURE myprocedure1;
  

• 刪除當(dāng)前數(shù)據(jù)庫中本次練習(xí)創(chuàng)建的存儲(chǔ)過程

  DROP PROCEDURE myprocedure1;
  DROP PROCEDURE myprocedure2;
  DROP PROCEDURE myprocedure3;
  DROP PROCEDURE myprocedure4;
  DROP PROCEDURE myprocedure5;
  DROP PROCEDURE myprocedure6;
  DROP PROCEDURE myprocedure7;
  DROP PROCEDURE myprocedure8;
  DROP PROCEDURE myprocedure9;
  DROP PROCEDURE myprocedure10;
  

函數(shù)

• 函數(shù)是什么，與存儲(chǔ)過程的區(qū)別

  類似存儲(chǔ)過程，是一組編譯好的SQL語句
  

• 函數(shù)與存儲(chǔ)過程的區(qū)別

  函數(shù)必須有返回值和return語句，參數(shù)形式為  參數(shù)名  參數(shù)類型，取消了參數(shù)模式
  函數(shù)如果使用 SELECT，則必須配合INTO關(guān)鍵字，因?yàn)楹瘮?shù)不允許出現(xiàn)結(jié)果集
  

• 函數(shù)優(yōu)點(diǎn)是什么

  增強(qiáng)代碼復(fù)用性
  將執(zhí)行過程封裝起來，增強(qiáng)安全性
  因?yàn)槭且呀?jīng)編譯好的SQL語句，性能較高
  

• 創(chuàng)建函數(shù)的語法

  DELIMITER $CREATE FUNCTION 函數(shù)名(參數(shù)名 參數(shù)類型) RETURNS 返回值類型
  BEGIN函數(shù)體
  END;$DELIMITER ;
  

• 函數(shù)能不要返回值，不要return語句嗎

  不行，必須要有返回值和return語句
  

• 創(chuàng)建一個(gè)無參的函數(shù)：用于查找 myemployees 庫中 employees 表中 employee_id = 168的first_name

  DELIMITER $CREATE FUNCTION myfunction1() RETURNS VARCHAR(30)
  BEGINDECLARE ret varchar(30);SELECT first_name INTO retFROM employeesWHERE employee_id = 168;RETURN ret;
  END;$DELIMITER ;SELECT myfunction1();
  

• 創(chuàng)建一個(gè)含參函數(shù)：接收傳遞進(jìn)來的參數(shù)，返回這個(gè)參數(shù)與"-myfunction2"的拼接結(jié)果

  DELIMITER $CREATE FUNCTION myfunction2(invar VARCHAR(30)) RETURNS VARCHAR(30)
  BEGINRETURN CONCAT(invar,'-myfunction2');
  END;$DELIMITER ;SELECT myfunction2('abc');
  

• 創(chuàng)建一個(gè)含參函數(shù)：傳遞來一個(gè)變量 invalue = 10，返回 invalue * 2

  DELIMITER $CREATE FUNCTION myfunction3(invalue INT) RETURNS INT
  BEGINRETURN invalue * 2;
  END;$DELIMITER ;SET @invalue = 10;SELECT myfunction3(@invalue);
  

• 創(chuàng)建一個(gè)含參函數(shù)：輸入一個(gè)數(shù)，如果大于0返回’greater than 0’，小于零返回’less than 0’，等于0返回’equals 0’

  DELIMITER $CREATE FUNCTION myfunction4(invalue INT) RETURNS VARCHAR(30)
  BEGINRETURN CASEWHEN invalue > 0 THEN 'greater than 0'WHEN invalue = 0 THEN 'equals 0'WHEN invalue < 0 THEN 'less than 0'END;
  END;$DELIMITER ;SELECT myfunction4(-1);
  SELECT myfunction4(0);
  SELECT myfunction4(1);
  

• 創(chuàng)建一個(gè)含參函數(shù)：輸入一個(gè)數(shù)，如果等于1返回’Hello’，等于2返回’World’，否則返回’Byebye’

  DELIMITER $CREATE FUNCTION myfunction5(invalue INT) RETURNS VARCHAR(30)
  BEGINRETURN CASE invalueWHEN invalue = 1 THEN 'Hello'WHEN invalue = 2 THEN 'World'ELSE 'ByeBye'END;
  END;$DELIMITER ;SELECT myfunction5(1);
  SELECT myfunction5(2);
  SELECT myfunction5(3);
  

• 創(chuàng)建一個(gè)無參函數(shù)：返回1+2+…+100的結(jié)果【使用while循環(huán)】

  DELIMITER $CREATE FUNCTION myfunction6() RETURNS INT
  BEGINDECLARE times INT DEFAULT 0;DECLARE retsum INT DEFAULT 0;WHILE times < 101 DOSET retsum = retsum + times;SET times = times + 1;END WHILE;RETURN retsum;
  END;$DELIMITER ;SELECT myfunction6();
  

• 創(chuàng)建無參函數(shù)：返回1x2x3x…x10的結(jié)果【使用LOOP循環(huán)】

  DELIMITER $CREATE FUNCTION myfunction7() RETURNS INT
  BEGINDECLARE times INT DEFAULT 1;DECLARE retsum INT DEFAULT 1;tt:LOOPSET retsum = retsum * times;SET times = times + 1;IF times > 10THEN LEAVE;END IF;END LOOP;RETURN retsum;
  END;$DELIMITER ;
  

• 查看所有的函數(shù)

  SHOW FUNCTION STATUS;
  

• 查看函數(shù)myfunction1的創(chuàng)建語句

  SHOW CREATE FUNCTION myfunction1;
  

• 刪除本次練習(xí)所創(chuàng)建的所有函數(shù)

  DROP FUNCTION myfunction1;
  DROP FUNCTION myfunction2;
  DROP FUNCTION myfunction3;
  DROP FUNCTION myfunction4;
  DROP FUNCTION myfunction5;
  DROP FUNCTION myfunction6;
  DROP FUNCTION myfunction7;
  

觸發(fā)器

• 什么是觸發(fā)器

  一類特殊的事務(wù)，可以監(jiān)視某種DML操作，并觸發(fā)相關(guān)DML操作
  

• 創(chuàng)建觸發(fā)器的語法

  DELIMITER $CREATE TRIGGER 觸發(fā)器名字
  BEFORE|AFTER INSERT|UPDATE|DELETE ON 表名
  FOR EACH ROW
  BEGIN觸發(fā)器的DML語句
  END$DELIMITER ;
  

• 創(chuàng)建兩張表

  goods：

  商品主鍵	商品名	庫存
  1	電腦	28
  2	自行車	12

  orders：

  訂單主鍵	購買數(shù)量	商品外鍵
  1	3	2
  2	5	1

  CREATE TABLE IF NOT EXISTS goods (g_id INT,g_name VARCHAR(20),g_count INT,CONSTRAINT pk PRIMARY KEY(g_id),CONSTRAINT uk UNIQUE(g_name)
  );INSERT INTO goods(g_id,g_name,g_count) 
  VALUES (1,'電腦',28),(2,'自行車',12);CREATE TABLE IF NOT EXISTS orders  (o_id INT PRIMARY KEY,o_count INT,o_g INT,CONSTRAINT fk_goods_g_id FOREIGN KEY(o_g) REFERENCES goods(g_id)
  );INSERT INTO orders(o_id,o_count,o_g) 
  VALUES (1,3,2),(2,5,1);
  

• 創(chuàng)建觸發(fā)器，讓orders購買數(shù)量增多時(shí)，goods數(shù)量相應(yīng)減少

  DELIMITER $CREATE TRIGGER my_trigger
  AFTER UPDATE ON orders
  FOR EACH ROW
  BEGINUPDATE goodsSET g_count = g_count - (new.o_count - old.o_count)WHERE g_id = new.o_g;
  END;$DELIMITER ;
  

• 查看所有的觸發(fā)器

  SHOW TRIGGERS;
  

• 查看my_trigger的創(chuàng)建語句

  SHOW CREATE TRIGGERS my_trigger;
  

• 刪除my_trigger

  DROP TRIGGER my_trigger;