【手把手教你玩MySQL】MySQL存储过程与存储函数

最新推荐文章于 2024-04-23 20:29:25 发布
最新推荐文章于 2024-04-23 20:29:25 发布
阅读量120 收藏 1
点赞数
分类专栏： mysql 文章标签： sql 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_40990306/article/details/130350615
版权

下载安装Mysql查看这个地址：https://blog.csdn.net/qq_40990306/article/details/120448462?spm=1001.2014.3001.5501
导入数据库表内容查看这个地址：

https://blog.csdn.net/qq_40990306/article/details/130294773?spm=1001.2014.3001.5501

#存储过程与存储函数

#0.准备工作

CREATE DATABASE dbtest15;

USE dbtest15;

CREATE TABLE employees
AS
SELECT * 
FROM atguigudb.`employees`;

CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;

SELECT * FROM employees;

SELECT * FROM departments;

#1. 创建存储过程

#类型1：无参数无返回值

#举例1：创建存储过程select_all_data()，查看 employees 表的所有数据

DELIMITER $

CREATE PROCEDURE select_all_data()
BEGIN
	SELECT * FROM employees;
END $

DELIMITER ;

#2. 存储过程的调用

CALL select_all_data();

#举例2：创建存储过程avg_employee_salary()，返回所有员工的平均工资

DELIMITER //

CREATE PROCEDURE avg_employee_salary()
BEGIN 
	SELECT AVG(salary) FROM employees;
	
END //

DELIMITER ;

#调用
CALL avg_employee_salary();


#举例3：创建存储过程show_max_salary()，用来查看“emps”表的最高薪资值。

DELIMITER //

CREATE PROCEDURE show_max_salary()
BEGIN
	SELECT MAX(salary)
	FROM employees;
END //

DELIMITER ;

#调用
CALL show_max_salary();

#类型2：带 OUT
#举例4：创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资
#通过OUT参数“ms”输出

DESC employees;

DELIMITER //

CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
	SELECT MIN(salary) INTO ms
	FROM employees;
END //

DELIMITER ;

#调用

CALL show_min_salary(@ms);

#查看变量值
SELECT @ms;

#类型3：带 IN
#举例5：创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，
#并用IN参数empname输入员工姓名。

DELIMITER //

CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
	SELECT salary FROM employees
	WHERE last_name = empname;
END //

DELIMITER ;

#调用方式1
CALL show_someone_salary('Abel');
#调用方式2
SET @empname := 'Abel';
CALL show_someone_salary(@empname);


SELECT * FROM employees WHERE last_name = 'Abel';

#类型4：带 IN 和 OUT
#举例6：创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，
#并用IN参数empname输入员工姓名，用OUT参数empsalary输出员工薪资。

DELIMITER //

CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DECIMAL(10,2))
BEGIN
	SELECT salary INTO empsalary
	FROM employees
	WHERE last_name = empname;
END //

DELIMITER ;

#调用
SET @empname = 'Abel';
CALL show_someone_salary2(@empname,@empsalary);

SELECT @empsalary;


#类型5：带 INOUT
#举例7：创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员工姓名，
#输出领导的姓名。

DESC employees;

DELIMITER $

CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN

	SELECT last_name INTO empname
	FROM employees
	WHERE employee_id = (
				SELECT manager_id
				FROM employees
				WHERE last_name = empname
				);
	
END $

DELIMITER ;

#调用
SET @empname := 'Abel';
CALL show_mgr_name(@empname);

SELECT @empname;

#2.存储函数

# 举例1：创建存储函数，名称为email_by_name()，参数定义为空，
#该函数查询Abel的email，并返回，数据类型为字符串型。

DELIMITER //

CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
	DETERMINISTIC
	CONTAINS SQL
	READS SQL DATA
BEGIN
	RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //

DELIMITER ;

#调用
SELECT email_by_name();

SELECT email,last_name FROM employees WHERE last_name = 'Abel';

#举例2：创建存储函数，名称为email_by_id()，参数传入emp_id，该函数查询emp_id的email，
#并返回，数据类型为字符串型。

#创建函数前执行此语句，保证函数的创建会成功
SET GLOBAL log_bin_trust_function_creators = 1;

#声明函数

DELIMITER //

CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)

BEGIN
	RETURN (SELECT email FROM employees WHERE employee_id = emp_id);

END //

DELIMITER ;


#调用
SELECT email_by_id(101);

SET @emp_id := 102;
SELECT email_by_id(@emp_id);


#举例3：创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的
#员工人数，并返回，数据类型为整型。

DELIMITER //

CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT

BEGIN
	RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
	
END //

DELIMITER ;

#调用
SET @dept_id := 50;
SELECT count_by_id(@dept_id);


#3. 存储过程、存储函数的查看

#方式1. 使用SHOW CREATE语句查看存储过程和函数的创建信息

SHOW CREATE PROCEDURE show_mgr_name;

SHOW CREATE FUNCTION count_by_id;

#方式2. 使用SHOW STATUS语句查看存储过程和函数的状态信息

SHOW PROCEDURE STATUS;

SHOW PROCEDURE STATUS LIKE 'show_max_salary';

SHOW FUNCTION STATUS LIKE 'email_by_id';

#方式3.从information_schema.Routines表中查看存储过程和函数的信息

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='email_by_id' AND ROUTINE_TYPE = 'FUNCTION';

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='show_min_salary' AND ROUTINE_TYPE = 'PROCEDURE';

#4.存储过程、函数的修改
ALTER PROCEDURE show_max_salary
SQL SECURITY INVOKER
COMMENT '查询最高工资';

#5. 存储过程、函数的删除

DROP FUNCTION IF EXISTS count_by_id;

DROP PROCEDURE IF EXISTS show_min_salary;
#第存储过程与存储函数练习


#0.准备工作
CREATE DATABASE test15_pro_func;

USE test15_pro_func;

#1. 创建存储过程insert_user(),实现传入用户名和密码，插入到admin表中
CREATE TABLE admin(
id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(15) NOT NULL,
pwd VARCHAR(25) NOT NULL

);

DELIMITER $
CREATE PROCEDURE insert_user(IN user_name VARCHAR(15),IN pwd VARCHAR(25))
BEGIN
	INSERT INTO admin(user_name,pwd)
	VALUES (user_name,pwd);

END $

DELIMITER ;

#调用

CALL insert_user('Tom','abc123');

SELECT * FROM admin;


#2. 创建存储过程get_phone(),实现传入女神编号，返回女神姓名和女神电话

CREATE TABLE beauty(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(15) NOT NULL,
phone VARCHAR(15) UNIQUE,
birth DATE
);

INSERT INTO beauty(NAME,phone,birth)
VALUES
('朱茵','13201233453','1982-02-12'),
('孙燕姿','13501233653','1980-12-09'),
('田馥甄','13651238755','1983-08-21'),
('邓紫棋','17843283452','1991-11-12'),
('刘若英','18635575464','1989-05-18'),
('杨超越','13761238755','1994-05-11');

SELECT * FROM beauty;


DELIMITER //

CREATE PROCEDURE get_phone(IN id INT,OUT NAME VARCHAR(15),OUT phone VARCHAR(15))
BEGIN
	SELECT b.name,b.phone INTO NAME,phone
	FROM beauty b
	WHERE b.id = id;
END //

DELIMITER ;

#调用

CALL get_phone(3,@name,@phone);
SELECT @name,@phone;

#3. 创建存储过程date_diff()，实现传入两个女神生日，返回日期间隔大小

DELIMITER //

CREATE PROCEDURE date_diff(IN birth1 DATE,IN birth2 DATE,OUT sum_date INT)
BEGIN
	SELECT DATEDIFF(birth1,birth2) INTO sum_date;

END //

DELIMITER ;

#调用

SET @birth1 = '1992-10-30';
SET @birth2 = '1992-09-08';

CALL date_diff(@birth1,@birth2,@sum_date);

SELECT @sum_date;


#4. 创建存储过程format_date(),实现传入一个日期，格式化成xx年xx月xx日并返回

DELIMITER //

CREATE PROCEDURE format_date(IN my_date DATE,OUT str_date VARCHAR(25))
BEGIN
	SELECT DATE_FORMAT(my_date,'%y年%m月%d日') INTO str_date;

END //

DELIMITER ;

#调用
CALL format_date(CURDATE(),@str);
SELECT @str;

#5. 创建存储过程beauty_limit()，根据传入的起始索引和条目数，查询女神表的记录

DELIMITER //

CREATE PROCEDURE beauty_limit(IN start_index INT,IN size INT)
BEGIN
	SELECT * FROM beauty LIMIT start_index,size;
END //

DELIMITER ;

#调用
CALL beauty_limit(1,3);


#创建带inout模式参数的存储过程
#6. 传入a和b两个值，最终a和b都翻倍并返回

DELIMITER //

CREATE PROCEDURE add_double(INOUT a INT,INOUT b INT)
BEGIN
	SET a = a * 2;
	SET b = b * 2;

END //

DELIMITER ;

#调用
SET @a = 3,@b = 5;
CALL add_double(@a,@b);

SELECT @a,@b;


#7. 删除题目5的存储过程

DROP PROCEDURE IF EXISTS beauty_limit;


#8. 查看题目6中存储过程的信息

SHOW CREATE PROCEDURE add_double;

SHOW PROCEDURE STATUS LIKE 'add_%';

#存储函数的练习

#0. 准备工作
USE test15_pro_func;

CREATE TABLE employees
AS
SELECT * FROM atguigudb.`employees`;

CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;


SET GLOBAL log_bin_trust_function_creators = 1;

#无参有返回
#1. 创建函数get_count(),返回公司的员工个数

DELIMITER $

CREATE FUNCTION get_count()
RETURNS INT

BEGIN
	RETURN (SELECT COUNT(*) FROM employees);
END $

DELIMITER ;

#调用
SELECT get_count();


#有参有返回
#2. 创建函数ename_salary(),根据员工姓名，返回它的工资

DELIMITER $

CREATE FUNCTION ename_salary(emp_name VARCHAR(15))
RETURNS DOUBLE

BEGIN
	RETURN (SELECT salary FROM employees WHERE last_name = emp_name);
END $

DELIMITER ;

#调用
SELECT ename_salary('Abel');


#3. 创建函数dept_sal() ,根据部门名，返回该部门的平均工资

DELIMITER //

CREATE FUNCTION dept_sal(dept_name VARCHAR(15))
RETURNS DOUBLE

BEGIN
	RETURN (SELECT AVG(salary)
		FROM employees e JOIN departments d
		ON e.department_id = d.department_id
		WHERE d.department_name = dept_name);

END //

DELIMITER ;

#调用

SELECT * FROM departments;

SELECT dept_sal('Marketing');


#4. 创建函数add_float()，实现传入两个float，返回二者之和

DELIMITER //

CREATE FUNCTION add_float(value1 FLOAT,value2 FLOAT)
RETURNS FLOAT

BEGIN
	RETURN (SELECT value1 + value2 );

END //

DELIMITER ;

# 调用
SET @v1 := 12.2;
SET @v2 = 2.3;
SELECT add_float(@v1,@v2);
Architect_Lee
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL查看表占用空间大小
王卫东 博客
01-14 1万+
通过SQL查看MySQL表占用空间大小
mysql语句查看数据库表所占容量空间大小
jialiu111111的博客
03-06 7930
SHOW TABLE STATUS LIKE '表名';查询结果中的'Data_free'字段的值就是碎片大小。六. 查看所有产生碎片的表。查看某个表的碎片大小。
mysql怎么查询表的大小
a657281084的博客
04-07 3496
在mysql中有一个information_schema数据库，这个数据库中装的是mysql的元数据，包括数据库信息、数据库中表的信息等。collation_character_set_applicability表：相当于collations表和character_sets表的前两个字段的一个对比，记录了字符集之间的对照信息。tables表：这个表里存储了所有数据库中的表的信息，包括每个表有多少个列等信息。schemata表：这个表里面主要是存储在mysql中的所有的数据库的信息。
[MySQL]存储过程与函数
emmmm.....
10-30 2324
1. 存储过程概述2. 创建存储过程3. 调用存储过程4. 存储过程的调试 5. 存储函数6. 存储函数和存储过程的对比 7. 存储过程和函数的查看、修改、删除 7.1 查看 7.1.1 SHOW CREATE 7.1.2 SHOW STATUS 7.1.3 information_schema.Routines表中查看存储过程和函数的信息 7.2 修改 7.3 删除 8. 存储过程与函数的优缺点 5.1 存储函数语法 5.2 调用存储函数
关于mysql 查看表大小的方法浅谈
xiaoweids的博客
06-03 73
本文主要介绍了mysql 查看表大小的方法实践，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧1.查看所有数据库容量大小12345678byby2.查看所有数据库各表容量大小12345678by3.查看指定数据库容量大小例：查看mysql库容量大小：代码如下：12345674.查看指定数据库各表容量大小*例：查看mysql库各表容量大小123456789by。
手把手教你玩转CSS33D技术
02-25
要玩转css3的3d，就必须了解几个词汇，便是透视(perspective)、旋转(rotate)和移动(translate)。透视即是以现实的视角来看屏幕上的2D事物，从而展现3D的效果。旋转则不再是2D平面上的旋转，而是三维坐标系的旋转，就...
手把手教你FPGA与RT以及Host端通信
01-19
本文将以cRIO-9068为例,带大家了解整个编程以及实现过程。一、FPGA、RT以及主机数据流之间的通信概览在一个含FPGA的Real-TIme系统中，FPGA负责采集信号，然后将采集的数据传输至RT端进行监控、处理，同时在主机进行...
手把手教你学dsp2812,手把手教你学dsp2812pdf下载,C,C++
09-10
手把手教你学2812，很全面的一本书。电子版，适合初学者学习，
mysql基础和进阶(手把手教你写sql).zip
08-12
记录自己亲自搭建mysql，学习mysql的基础和进阶过程代码，分享给需要的小伙伴
MySQL 备份与恢复 手把手教学视频
08-05
MySQL 备份与恢复 手把手教学视频
mysql(16-课后小练习)
缘友一世的博客
05-14 457
每天进步一点，进步一点！！感谢自己 👍👍👍👍👍👍👍🌠🌠🌠🌠🌠🌠🌠 文章目录🐋🐋变量、流程控制与游标 🐋🐋变量、流程控制与游标 #无参有返回 #1. 创建函数get_count(),返回公司的员工个数 DELIMITER $ CREATE FUNCTION get_count() RETURNS INT DETERMINISTIC# 函数特性 CONTAINS SQL BEGIN #定义局部变量 DECLARE coe INT DEFAULT 0; SELECT COUNT(*) INTO co
Mysql8.0习题系列（七）:存储函数（一篇学会存储函数，超详细）
阿里云专家博主、数据科学领域优质创作者、统计er在读
03-30 3044
Mysql8.0习题系列 软件下载地址 提取码：7v7u 数据下载地址 提取码：e6p9 文章目录Mysql8.0习题系列准备工作1. 创建函数get_count(),返回公司的员工个数2. 创建函数ename_salary(),根据员工姓名，返回它的工资3. 创建函数dept_sal() ,根据部门名，返回该部门的平均工资4. 创建函数add_float()，实现传入两个float，返回二者之和 mysql创建函数基本语法如下： delimiter $ create function 函数名(参数)
存储过程和存储函数 练习
weixin_56847199的博客
10-27 346
存储过程和存储函数练习
mysql 函数案例
qq_43621698的博客
05-29 714
创建一个函数，以部门号为参数，返回该部门的人数和平均工资 —emp表结构 —emp表数据 delimiter // CREATE DEFINER=`root`@`localhost` FUNCTION `fun1`(deptno1 int) RETURNS varchar(30) CHARSET utf8mb4 begin declare a int; declare b decimal(7,2); declare c varchar(30); select count(*),avg(sal) fro.
第13章：存储过程和存储函数
jbkjhji的博客
05-17 852
创建存储函数，名称为email_by_id()，参数传入emp_id，该函数查询emp_id的email，并返回，数据类型为字符串型。创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，并返回，数据类型为整型。创建存储函数，名称为email_by_name()，参数定义为空，该函数查询Abel的email，并返回，数据类型为字符串型。存储过程直接操作底层数据库，存储过程创建出来后，直接调用存储过程名就可以，存储过程没有返回值。MySQL默认结束是分号’;
PL/SQL 创建一个存储过程，以部门号为参数返回该部门的人数和平均工资（保留两位小数）。
马心宇的博客
07-04 1万+
CREATE OR REPLACE PROCEDURE proc_return_deptinfo( p_deptno employees.department_id%TYPE, p_avgsal OUT employees.salary%TYPE, p_count OUT NUMBER) AS BEGIN SELECT avg(salary),count(*) INTO p_avgsal,p_c...
创建存储过程，以部门编号为参数，查询并返回该部门平均工资，以及该部门中比该部门平均工资高的员工信息
热门推荐
it_taojingzhan的博客
10-04 1万+
create or replace procedure proc_show_emp(p_deptno employees.departmentid%type) as   v_sal employees.salary%type; begin   select avg(salary)     into v_sal     from employees    where department
创建存储过程，以部门编号为参数返回该部门的人数及平均工资（返回一个值可以通过函数来实现，但如果要返回多个值，需要使用out或in out模式参数实现）
it_taojingzhan的博客
10-04 1万+
create or replace procedure proc_return_deptinfo(p_deptno employees.departmentid%type,                                                  p_avgsal out employees.salary%type,                         
MySQL生成日期序列与表关联的 SQL 查询
最新发布
lcl17779740668的博客
04-23 763
MySQL生成日期序列与表关联的SQL查询
mysql cluster 视频_手把手教你用Mysql-Cluster-7.5搭建数据库集群
06-09
关于Mysql-Cluster-7.5搭建数据库集群的视频，我不确定是否有可用的手把手教程视频，但是我可以向你介绍一些相关的资源，希望对你有所帮助。 首先，你可以在YouTube和B站等视频网站上搜索相关的教程视频。在这些...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值