MySQL之存儲過程和函數的示例分析

這篇文章將為大家詳細講解有關MySQL之存儲過程和函數的示例分析，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

一、變量

系統變量

系統變量分為全局變量和會話變量，是由系統提供的。

• 全局變量作用域：服務器每次啟動將為所有的全局變量初始化,不能跨重啟。

• 會話變量作用域：僅針對當前會話有效。

【全局變量】 
作用域:服務器每次啟動將為所有的全局變量初始化,不能跨重啟# 1.查看所有的全局變量SHOW GLOBAL VARIABLES;# 2.查看部分全局變量SHOW GLOBAL VARIABLES LIKE '%char%';# 3.查看某個全局變量的值SELECT @@global.autocommit;# 查看是否自動提交SELECT @@global.tx_isolation;# 查看隔離級別#4.為某個指定的全局變量賦值SET @@global.autocommit=0;【會話變量】
作用域:僅針對當前會話有效.# 1.查看所有的會話變量SHOW SESSION VARIABLES;SHOW VARIABLES;#默認省略session# 2.查看部分會話變量SHOW SESSION VARIABLES LIKE '%char%';# 3.查看某個會話變量的值SELECT @@tx_isolation;SELECT @@session.tx_isolation;# 4.為某個指定的會話變量賦值SET @@session.autocommit=0;

自定義變量

自定義變量分為用戶變量和局部變量，是用戶自定義的。

【用戶變量】
作用域:針對當前會話有效,和會話變量的作用域相同# 聲明并初始化# 下面三種方式都可以SET @count=1; # set @count:=1;# select @count:=1;# 賦值SELECT COUNT(*) INTO @count FROM employees;# 查看用戶變量SELECT @count;【局部變量】
作用域:僅在定義的begin end中有效,應用在begin end中的第一句話# 聲明DECLARE 變量名 類型;# declare 變量名 類型 default 值;# 賦值SET 局部變量名=值;# 或set 局部變量名:=值;# select @局部變量名:=值# 使用SELECT 局部變量名;【案例】# 案例:聲明兩個變量并賦初值,求和,打印# 用戶變量SET @m=1;SET @n=2;SET @sum=@m+@n;SELECT @sum;# 局部變量[只能在begin...end中運行]DECLARE m INT DEFAULT 1;DECLARE n INT DEFAULT 2;DECLARE SUM INT;SET SUM=m+n;SELECT SUM;

二、存儲過程

定義：事先經過編譯并存儲在數據庫中的一組sql語句的集合。

存儲過程的優點：①提高代碼重用性 ②減少編譯與連接次數 ③提高效率。

創建語法：

CREATE PROCEDURE 存儲過程名(參數列表)
BEGIN
存儲過程體(一組合法的sql語句);
END 結束符

調用語法：
CALL 存儲過程名(實參列表)

刪除語法:
DROP PROCEDURE 存儲過程名;

查看語法:
SHOW CREATE PROCEDURE 存儲過程名;

存儲過程的參數別聊提供了3種參數：

• IN：需要輸入，需要調用方傳入值。

• OUT：可以輸出，可以作為返回值。

• INOUT：可以輸入和輸出，既需要入參又需要返回值。

注意：

• 如果存儲過程只有一句話，可以省略BEGIN END。

• 存儲過程體中的每條sql語句的結尾要求必須加分號。

• 存儲過程的結尾可以使用delimiter重寫結束標志，DELIMITER 結束標記。

【空參存儲過程】# 案例:向admin表中插入5條記錄# 重置結尾符為$DELIMITER $# 創建存儲過程CREATE PROCEDURE myp1()BEGIN
   INSERT INTO admin(username,PASSWORD)
   VALUES('join1','000'),('join2','000'),('join3','000'),('join4','000'),('join5','000');END $# 調用存儲過程CALL myp1()$



【帶in模式的存儲過程】# 案例:創建存儲過程,實現根據女神名查詢對應的男朋友信息CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))BEGIN
	SELECT bo.*
	FROM boys bo	RIGHT JOIN beauty b 
	ON bo.id=b.boyfriend_id	WHERE b.name=beautyName;END$CALL myp2('柳巖')$# 案例:創建存儲過程,查看用戶是否登錄成功CREATE PROCEDURE myp3(IN username VARCHAR(10),IN PASSWORD VARCHAR(10))BEGIN
	DECLARE result INT DEFAULT 0;# 聲明并初始化
	
	SELECT COUNT(*) INTO result# 賦值
	FROM admin	WHERE admin.username=username	AND PASSWORD=PASSWORD;
	
	SELECT IF(result,'成功','失敗');#打印變量END$# 調用CALL myp3('john','8888')$



【帶out模式的存儲過程】# 案例:根據女神名,返回對應的男神名CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))BEGIN
	SELECT bo.boyName INTO boyName #賦值
	FROM boys bo	INNER JOIN beauty b	ON b.boyfriend_id=bo.id	WHERE b.name=beautyName;END $CALL myp4('熱巴',@bName)$ # 不定義,直接使用用戶變量填充# 調用SELECT @bName$# 案例:根據女神名,返回對應的男神名和男神魅力值CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)BEGIN
	SELECT bo.boyName,bo.userCP INTO boyName,userCP  #賦值
	FROM boys bo	INNER JOIN beauty b	ON b.boyfriend_id=bo.id	WHERE b.name=beautyName;END $CALL myp5('熱巴',@bName,@usercp)$ # 不定義,直接使用用戶變量填充# 調用SELECT @bName,@userCP$【帶inout模式的存儲過程】# 案例：傳入a和b兩個值，最終a和b都翻倍并返回CREATE PROCEDURE myp6(INOUT a INT,INOUT b INT)BEGIN
	SET a=a*2;
	SET b=b*2;END $SET @m=10$SET @n=20$CALL myp6(@m,@n)$SELECT @m,@n$

學習了存儲過程，嘗試完成下列習題吧

習題答案如下↓

【習題答案】# 習題1：創建存儲過程實現傳入用戶名和密碼,插入到admin表中DELIMITER $CREATE PROCEDURE test_1(IN username VARCHAR(10), IN loginPwd VARCHAR(10))BEGIN
	INSERT INTO admin(admin.username,PASSWORD)
	VALUES(username,loginPwd);END $CALL test_1('admin','111')$# 習題2：創建存儲過程或函數實現傳入女神編號,返回女神名稱和電話CREATE PROCEDURE test_2(IN id INT,OUT NAME VARCHAR(20),OUT phone VARCHAR(20))BEGIN 
	SELECT b.name,b.phone INTO NAME,phone	FROM beauty b	WHERE b.id=id;END $CALL test_2(1,@n,@p)$SELECT @n,@p;# 習題3：創建存儲過程或函數實現傳入兩個女神的生日,返回大小CREATE PROCEDURE test_3(IN birth2 DATETIME,IN birth3 DATETIME,OUT result INT)BEGIN
	SELECT DATEDIFF(birth2,birth3) INTO result;END $CALL test_3('1998-1-1',NOW(),@result)$SELECT @result$# 習題4：創建存儲過程或函數實現傳入一個日期，格式化成 xx 年 xx 月 xx 日并返回CREATE PROCEDURE test_4(IN mydate DATETIME,OUT strdate VARCHAR(50))BEGIN
	SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strdate;END $CALL test_4(NOW(),@str)$SELECT @str $#習題5：創建存儲過程或函數實現傳入女神名稱，返回：女神 and 男神 格式的字符串CREATE PROCEDURE test_5(IN beautyName VARCHAR(20),OUT str VARCHAR(50))BEGIN
	SELECT CONCAT(beautyName,'and',IFNULL(boyName,'null')) INTO str	FROM boys bo	RIGHT JOIN beauty b	ON b.boyfriend_id=bo.id	WHERE b.name=beautyName;END $CALL test_5('熱巴',@str)$SELECT @str $#習題6：創建存儲過程或函數，根據傳入的條目數和起始索引，查詢 beauty 表的記錄CREATE PROCEDURE test_6(IN startIndex INT,IN size INT)BEGIN
	SELECT * FROM beauty LIMIT startIndex,size;END $CALL test_6(3,5)$

三、函數

存儲過程和函數的區別？

• 存儲過程可以有0個或多個返回；函數有且只有一個返回。

• 存儲過程適合做批量插入、批量更新；函數適合做處理數據后返回一個結果。

創建語法：
CREATE FUNCTION 函數名(參數列表) RETURNS 返回類型
BEGIN
函數體(一定有return語句);
END 結束符

調用語法：
SELECT 函數名(參數列表)

查看函數：
SHOW CREATE FUNCTION my_f3;

刪除函數：
DROP FUNCTION my_f3;

【無參有返回】# 返回公司的員工個數CREATE FUNCTION my_f1() RETURNS INTBEGIN
	DECLARE n INT DEFAULT 0;# 定義變量
	SELECT COUNT(*) INTO n # 賦值
	FROM employees;
	RETURN n;END $SELECT my_f1()$

【有參有返回】# 根據員工名,返回工資CREATE FUNCTION my_f2(empName VARCHAR(20)) RETURNS DOUBLEBEGIN
	SET @sal=0;# 定義用戶變量
	SELECT salary INTO @sal # 賦值
	FROM employees	WHERE last_name=empName;
	RETURN @sal;END $SELECT my_f2('Kochhar')$# 3.根據部門名,返回該部門平均工資CREATE FUNCTION my_f3(deptName VARCHAR(20)) RETURNS DOUBLEBEGIN
	DECLARE sal DOUBLE;
	SELECT AVG(Salary) INTO sal	FROM employees e	JOIN departments d	ON e.department_id=d.department_id	WHERE d.department_name=deptName;
	RETURN sal;END $SELECT my_f3('IT')$

學習了mysql的函數，嘗試完成下列習題

答案：
1、
CREATE FUNCTION test_1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM=num1+num2;
RETURN SUM;
END $
SELECT test_1(1,2)$
2、
CREATE FUNCTION test_2(jobName VARCHAR(20)) RETURNS INT
BEGIN
DECLARE number INT DEFAULT 0; # 定義變量
SELECT COUNT(employee_id) INTO number # 賦值
FROM employees e
JOIN jobs j ON e.job_id=j.job_id
WHERE j.job_title=jobName;
RETURN number;
END $
SELECT test_2(‘President’)$
3、
CREATE FUNCTION test_3(empName VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
DECLARE managerName VARCHAR(20) DEFAULT ‘’; # 定義變量
SELECT e1.last_name AS managerName INTO managerName # 賦值
FROM employees e1
WHERE e1.employee_id=(SELECT e2.manager_id FROM employees e2 WHERE e2.last_name=empName);
RETURN managerName;
END $
SELECT test_3(‘Kochhar’)$

關于“MySQL之存儲過程和函數的示例分析”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。