一本一本久久a久久综合精品蜜桃,一本色道久久88—综合亚洲精品,狠狠精品久久久无码中文字幕

MySQL精講之五：存儲(chǔ)過程和函數(shù)

免費(fèi)學(xué)習(xí)推薦：mysql視頻教程

文章目錄

一、變量
- 系統(tǒng)變量
- 自定義變量
二、存儲(chǔ)過程
三、函數(shù)

一、變量

系統(tǒng)變量

系統(tǒng)變量分為全局變量和會(huì)話變量，是由系統(tǒng)提供的。

全局變量作用域：服務(wù)器每次啟動(dòng)將為所有的全局變量初始化,不能跨重啟。
會(huì)話變量作用域：僅針對(duì)當(dāng)前會(huì)話有效。

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

自定義變量

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

對(duì)比	作用域	定義和使用位置	語法
用戶變量	當(dāng)前會(huì)話	會(huì)話中的任何地方	必須加@符號(hào)，不用限定類型
局部變量	BEGIN…END中	只能在BEGIN…END中，且為第一句話	一般不加@符號(hào)，需要限定類型

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

二、存儲(chǔ)過程

定義：事先經(jīng)過編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中的一組sql語句的集合。

存儲(chǔ)過程的優(yōu)點(diǎn)：①提高代碼重用性 ②減少編譯與連接次數(shù) ③提高效率。

創(chuàng)建語法：

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

調(diào)用語法：
CALL 存儲(chǔ)過程名(實(shí)參列表)

刪除語法:
DROP PROCEDURE 存儲(chǔ)過程名;

查看語法:
SHOW CREATE PROCEDURE 存儲(chǔ)過程名;

存儲(chǔ)過程的參數(shù)別聊提供了3種參數(shù)：

IN：需要輸入，需要調(diào)用方傳入值。
OUT：可以輸出，可以作為返回值。
INOUT：可以輸入和輸出，既需要入?yún)⒂中枰祷刂怠?/li>

注意：

如果存儲(chǔ)過程只有一句話，可以省略BEGIN END。

存儲(chǔ)過程體中的每條sql語句的結(jié)尾要求必須加分號(hào)。

存儲(chǔ)過程的結(jié)尾可以使用delimiter重寫結(jié)束標(biāo)志，DELIMITER 結(jié)束標(biāo)記。

【空參存儲(chǔ)過程】# 案例:向admin表中插入5條記錄# 重置結(jié)尾符為$DELIMITER $# 創(chuàng)建存儲(chǔ)過程CREATE PROCEDURE myp1()BEGIN    INSERT INTO admin(username,PASSWORD)    VALUES('join1','000'),('join2','000'),('join3','000'),('join4','000'),('join5','000');END $# 調(diào)用存儲(chǔ)過程CALL myp1()$    【帶in模式的存儲(chǔ)過程】# 案例:創(chuàng)建存儲(chǔ)過程,實(shí)現(xiàn)根據(jù)女神名查詢對(duì)應(yīng)的男朋友信息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('柳巖')$# 案例:創(chuàng)建存儲(chǔ)過程,查看用戶是否登錄成功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$# 調(diào)用CALL myp3('john','8888')$    【帶out模式的存儲(chǔ)過程】# 案例:根據(jù)女神名,返回對(duì)應(yīng)的男神名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)$ # 不定義,直接使用用戶變量填充# 調(diào)用SELECT @bName$# 案例:根據(jù)女神名,返回對(duì)應(yīng)的男神名和男神魅力值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)$ # 不定義,直接使用用戶變量填充# 調(diào)用SELECT @bName,@userCP$【帶inout模式的存儲(chǔ)過程】# 案例：傳入a和b兩個(gè)值，最終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$

學(xué)習(xí)了存儲(chǔ)過程，嘗試完成下列習(xí)題吧

習(xí)題答案如下↓

【習(xí)題答案】# 習(xí)題1：創(chuàng)建存儲(chǔ)過程實(shí)現(xiàn)傳入用戶名和密碼,插入到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')$# 習(xí)題2：創(chuàng)建存儲(chǔ)過程或函數(shù)實(shí)現(xiàn)傳入女神編號(hào),返回女神名稱和電話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;# 習(xí)題3：創(chuàng)建存儲(chǔ)過程或函數(shù)實(shí)現(xiàn)傳入兩個(gè)女神的生日,返回大小CREATE PROCEDURE test_3(IN birth1 DATETIME,IN birth2 DATETIME,OUT result INT)BEGIN 	SELECT DATEDIFF(birth1,birth2) INTO result;END $CALL test_3('1998-1-1',NOW(),@result)$SELECT @result$# 習(xí)題4：創(chuàng)建存儲(chǔ)過程或函數(shù)實(shí)現(xiàn)傳入一個(gè)日期，格式化成 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 $#習(xí)題5：創(chuàng)建存儲(chǔ)過程或函數(shù)實(shí)現(xiàn)傳入女神名稱，返回：女神 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 $#習(xí)題6：創(chuàng)建存儲(chǔ)過程或函數(shù)，根據(jù)傳入的條目數(shù)和起始索引，查詢 beauty 表的記錄CREATE PROCEDURE test_6(IN startIndex INT,IN size INT)BEGIN 	SELECT * FROM beauty LIMIT startIndex,size;END $CALL test_6(3,5)$

三、函數(shù)

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

存儲(chǔ)過程可以有0個(gè)或多個(gè)返回；函數(shù)有且只有一個(gè)返回。

存儲(chǔ)過程適合做批量插入、批量更新；函數(shù)適合做處理數(shù)據(jù)后返回一個(gè)結(jié)果。

創(chuàng)建語法：
CREATE function 函數(shù)名(參數(shù)列表) RETURNS 返回類型
BEGIN
函數(shù)體(一定有return語句);
END 結(jié)束符

調(diào)用語法：
SELECT 函數(shù)名(參數(shù)列表)

查看函數(shù)：
SHOW CREATE FUNCTION my_f3;

刪除函數(shù)：
DROP FUNCTION my_f3;

【無參有返回】# 返回公司的員工個(gè)數(shù)CREATE FUNCTION my_f1() RETURNS INTBEGIN 	DECLARE n INT DEFAULT 0;# 定義變量 	SELECT COUNT(*) INTO n # 賦值 	FROM employees; 	RETURN n;END $SELECT my_f1()$  【有參有返回】# 根據(jù)員工名,返回工資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.根據(jù)部門名,返回該部門平均工資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')$

學(xué)習(xí)了mysql的函數(shù)，嘗試完成下列習(xí)題

答案：
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精講系列文章(更新中)
《MySQL精講之一：DQL數(shù)據(jù)查詢語句》
《MySQL精講之二：DML數(shù)據(jù)操作語句》
《MySQL精講之三：DDL數(shù)據(jù)定義語句》
《MySQL精講之四：TCL事務(wù)控制語句》
《MySQL精講之六：流程控制》? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?