mysql存储过程、存储函数练习题

准备数据

CREATE DATABASE test15_pro_func;
USE test15_pro_func;
CREATE TABLE admin(
id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(15) NOT NULL,
pwd VARCHAR(25) NOT NULL
);
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');

存储过程

#1. 创建存储过程insert_user(),实现传入用户名和密码，插入到admin表中
DELIMITER $
CREATE PROCEDURE insert_user(IN NAME VARCHAR(15),IN pwd VARCHAR(25))
BEGIN
	INSERT INTO admin(`user_name`,`pwd`) VALUES(NAME,pwd);
END $
DELIMITER ;
CALL insert_user('赵刚','123456');
SELECT * FROM `admin`
#2. 创建存储过程get_phone(),实现传入女神编号，返回女神姓名和女神电话
#参数名不能和字段名相同，不然分不出用的哪个，除非起别名区分如方式2
#方式1
DELIMITER $
CREATE PROCEDURE get_phone(IN NO INT)
BEGIN
	SELECT `NAME`,`phone` FROM `beauty` WHERE `id`=NO;
END $
DELIMITER ;
CALL get_phone(1);
#方式2
DELIMITER $
CREATE PROCEDURE get_phone1(IN id INT,OUT NAME VARCHAR(15),OUT phone VARCHAR(15))
BEGIN
	SELECT a.`NAME` ,a.`phone` INTO NAME,phone FROM `beauty` a WHERE a.`id`=id;
END $
DELIMITER ;
CALL get_phone1(1,@name,@phone);
SELECT @name,@phone;
#3. 创建存储过程date_diff()，实现传入两个女神生日，返回日期间隔大小
DELIMITER $
CREATE PROCEDURE date_diff(IN `birth1` DATE,IN `birth2` DATE,OUT result INT)
BEGIN
	SELECT DATEDIFF(birth1,birth2) INTO result;
END $
DELIMITER ;
SET @birth1='2022-11-11';
SET @birth2='2022-11-09';
CALL date_diff(@birth1,@birth2,@result);
SELECT @result;
#4. 创建存储过程format_date(),实现传入一个日期，格式化成xx年xx月xx日并返回
DELIMITER $
CREATE PROCEDURE  format_date(IN date1 DATE,OUT result VARCHAR(20))
BEGIN
	SELECT DATE_FORMAT(date1,'%y年%m月%d日') INTO result;
END $
DELIMITER ;
CALL format_date('2022-12-12',@result);
SELECT @result;
#5. 创建存储过程beauty_limit()，根据传入的起始索引和条目数，查询女神表的记录
DELIMITER $
CREATE PROCEDURE beauty_limit(IN startindex INT ,IN size INT)
BEGIN
 SELECT * FROM `beauty` LIMIT startindex , size;
END $
DELIMITER ;
CALL beauty_limit(1,2);
#创建带inout模式参数的存储过程
#6. 传入a和b两个值，最终a和b都翻倍并返回
#方式1
DELIMITER $
CREATE PROCEDURE add_double(INOUT a INT,INOUT b INT)
BEGIN
	SELECT a*2,b*2 INTO a,b;
END $
DELIMITER ;
SET @a=3,@b=3;
CALL add_double(@a,@b);
SELECT @a,@b;
#方式2
DELIMITER $
CREATE PROCEDURE add_double1(INOUT a INT,INOUT b INT)
BEGIN
	SET a=a*2,b=b*2;
	
END $
DELIMITER ;
SET @a=4,@b=3;
CALL add_double1(@a,@b);
SELECT @a,@b;
#7.查看题目6中存储过程的信息
SHOW CREATE PROCEDURE add_double;
SHOW PROCEDURE STATUS LIKE '%add_double%';
#8.根据传入的值实现累加计算
DELIMITER $
CREATE PROCEDURE sp1(IN n INT)
BEGIN
DECLARE i INT;
DECLARE num INT;
SET i=1;
SET num=0;
WHILE i<=n DO
	SET num=num+i;
	SET i=i+1;
END WHILE;
SELECT num;
END $
DELIMITER ;
CALL sp1(50);

准备数据

CREATE TABLE `departments` (
  `department_id` int NOT NULL DEFAULT '0',
  `department_name` varchar(30) CHARACTER SET utf8 NOT NULL,
  `manager_id` int DEFAULT NULL,
  `location_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



insert  into `departments`(`department_id`,`department_name`,`manager_id`,`location_id`) values (10,'Administration',200,1700),(20,'Marketing',201,1800);


CREATE TABLE `employees` (
  `employee_id` int NOT NULL DEFAULT '0',
  `first_name` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `last_name` varchar(25) CHARACTER SET utf8 NOT NULL,
  `email` varchar(25) CHARACTER SET utf8 NOT NULL,
  `phone_number` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `hire_date` date NOT NULL,
  `job_id` varchar(10) CHARACTER SET utf8 NOT NULL,
  `salary` double(8,2) DEFAULT NULL,
  `commission_pct` double(2,2) DEFAULT NULL,
  `manager_id` int DEFAULT NULL,
  `department_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



insert  into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`hire_date`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`) values (100,'Steven','Abel','SKING','515.123.4567','1987-06-17','AD_PRES',24000.00,NULL,NULL,10),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','1989-09-21','AD_VP',17000.00,NULL,100,90);

存储函数

如果创建函数报错，先执行
mysql> SET GLOBAL log_bin_trust_function_creators = 1;

#无参有返回
#1. 创建函数get_count(),返回公司的员工个数
DELIMITER $
CREATE FUNCTION get_count()
RETURNS INT
BEGIN
 RETURN (SELECT COUNT(1) FROM `employees`);
END $
DELIMITER ;
SELECT get_count();



#有参有返回
#2. 创建函数ename_salary(),根据员工姓名，返回它的工资
DELIMITER $
CREATE FUNCTION ename_salary(NAME VARCHAR(25))
RETURNS DOUBLE
BEGIN
 RETURN (SELECT `salary` FROM `employees` WHERE `last_name`=NAME);
END $
DELIMITER ;
SELECT ename_salary('Abel');

#3. 创建函数dept_sal() ,根据部门名，返回该部门的平均工资
DELIMITER $
CREATE FUNCTION dept_sal(NAME VARCHAR(30))
RETURNS DOUBLE
BEGIN
 RETURN (SELECT AVG(`salary`) FROM `employees` JOIN `departments` USING(`department_id`) WHERE `department_name`=NAME);
END $
DELIMITER ;
SELECT dept_sal('Administration');
#4. 创建函数add_float()，实现传入两个float，返回二者之和
DELIMITER $
CREATE FUNCTION add_float(a FLOAT,b FLOAT)
RETURNS FLOAT
BEGIN
RETURN (a+b);
END $
DELIMITER ;
SET @a=4,@b=4;
SELECT add_float(@a,@b);