ホームページ >データベース >mysql チュートリアル >MySQL 講義 5: ストアドプロシージャとストアド関数

MySQL 講義 5: ストアドプロシージャとストアド関数

coldplay.xixi転載: 2021-02-26 09:11:211884ブラウズ

無料学習の推奨事項: mysql ビデオチュートリアル

# #記事ディレクトリ

##1. 変数

システム変数
##カスタム変数
- 2. ストアドプロシージャ
- 3. 関数
1. 変数

システム変数

システム変数は、システムによって提供される

グローバル変数

と セッション変数に分かれています。

グローバル変数のスコープ: サーバーが起動するたびに、すべてのグローバル変数が初期化されるため、再起動をまたぐことはできません。

【全局变量】 
作用域:服务器每次启动将为所有的全局变量初始化,不能跨重启# 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;

カスタム変数

ユーザー変数

と ローカル変数 に分かれています。これはユーザー定義です。

比較

スコープ定義と使用場所現在のセッションセッション内の任意の場所に BEGIN…END は BEGIN…END にのみ含めることができ、最初の文になります

構文ユーザー変数

@記号を追加する必要があり、タイプを制限する必要はありませんローカル変数

通常は追加されません@ 記号、タイプを修飾する必要があります

構文			ユーザー変数
@記号を追加する必要があり、タイプを制限する必要はありません		ローカル変数
通常は追加されません@ 記号、タイプを修飾する必要があります		【用户变量】作用域:针对当前会话有效,和会话变量的作用域相同# 声明并初始化# 下面三种方式都可以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=a2; SET b=b2;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 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$# 习题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精讲系列文章(更新中)* 《MySQL精讲之一：DQL数据查询语句》《MySQL精讲之二：DML数据操作语句》《MySQL精讲之三：DDL数据定义语句》《MySQL精讲之四：TCL事务控制语句》《MySQL精讲之六：流程控制》更多相关免费学习推荐：mysql教程(视频)

【用户变量】
作用域:针对当前会话有效,和会话变量的作用域相同# 声明并初始化# 下面三种方式都可以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 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$# 习题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精讲系列文章(更新中)
《MySQL精讲之一：DQL数据查询语句》
《MySQL精讲之二：DML数据操作语句》
《MySQL精讲之三：DDL数据定义语句》
《MySQL精讲之四：TCL事务控制语句》
《MySQL精讲之六：流程控制》

更多相关免费学习推荐：mysql教程(视频)

以上がMySQL 講義 5: ストアドプロシージャとストアド関数の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。

sql mysql Float count select 局部变量全局变量 int 变量作用域实参 number function 作用域 default 数据库

声明：

この記事はcsdn.netで複製されています。侵害がある場合は、admin@php.cn までご連絡ください。

前の記事：MySQL 講義 4: TCL トランザクション制御ステートメント次の記事：MySQL 講義 4: TCL トランザクション制御ステートメント

続きを見る

MySQL 講義 5: ストアド プロシージャとストアド関数

関連記事

MySQL 講義 5: ストアドプロシージャとストアド関数