#第十五章、练习题

#存储过程练习
#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
);

DESC admin;

#创建存储过程insert_user()
DELIMITER //
CREATE PROCEDURE insert_user(IN uname VARCHAR(15),IN passwd VARCHAR(25))
CONTAINS SQL
BEGIN
		INSERT INTO admin(user_name,pwd)
		VALUES(uname,passwd);
END //

DELIMITER ;

#调用
SET @user_name := 'Tom';
SET @pwd := 'abc123';
CALL insert_user(@user_name,@pwd)
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;

#创建存储过程get_phone()：我做的
DELIMITER //
CREATE PROCEDURE get_phone(INOUT uid INT)
BEGIN
		SELECT name,phone FROM beauty WHERE id = uid;
END //

DELIMITER ;
#调用
SET @uid := 1;
CALL get_phone(@uid);

#老师讲的
DELIMITER //
CREATE PROCEDURE teach_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 teach_get_phone(2,@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 := '1991-12-11';
SET @birth2 := '1991-11-12';
CALL date_diff(@birth1,@birth2,@result);
SELECT @result;

#4、创建存储过程format_date()，实现传入一个日期，格式化成xx年xx月xx日并返回
#我做的：不对
DELIMITER //
CREATE PROCEDURE format_date(IN udate DATE,OUT result DATE)
BEGIN
		SELECT DATE_FORMAT(udate,'%y%m%d') INTO result;
END //

DELIMITER ;
#调用
SET @udate := '1989-05-18';
CALL format_date(@udate,@result);
SELECT @result;

#老师讲的
DELIMITER //
CREATE PROCEDURE teach_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 teach_format_date(CURDATE(),@str);
SELECT @str;

#5、创建存储过程beauty_limit()，根据传入的起始索引和条目数，查询女神表的记录
#我做的：正确
DELIMITER //
CREATE PROCEDURE beauty_limit(IN uid INT,IN num INT)
BEGIN
		SELECT * FROM beauty LIMIT uid,num; 
END //

DELIMITER ;
#调用
SET @uid := 1;
SET @num := 3;
CALL beauty_limit(@uid,@num);

#老师讲的
DELIMITER //
CREATE PROCEDURE teach_beauty_limit(IN start_index INT,IN size INT)
BEGIN
		SELECT * FROM beauty LIMIT start_index,size; 
END //

DELIMITER ;
#调用
CALL teach_beauty_limit(1,3);

#创建带inout模式参数的存储过程

#6、传入a和b两个值，最终a和b都翻倍并返回
#我做的
DELIMITER //
CREATE PROCEDURE multi(INOUT a INT,INOUT b INT)
BEGIN
		SELECT a*2,b*2 FROM DUAL;
END //

DELIMITER ;
#调用
SET @a := 2;
SET @b := 5;
CALL multi(@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 PROCEDURE STATUS LIKE 'multi';
#老师讲的add_double
SHOW CREATE PROCEDURE add_double;

SHOW PROCEDURE STATUS LIKE 'add_double';

#存储函数练习

#0、准备工作
USE test15_pro_func;

CREATE TABLE employees
AS
SELECT * FROM atguigudb.employees;

CREATE TABLE departments
AS
SELECT * FROM atguigudb.departments;

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

DELIMITER ;
#调用
SELECT get_count();

DESC employees;

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

DELIMITER ;
#调用
SET @lname = 'Abel';
SELECT ename_salary(@lname);

#3、创建函数dept_sal()，根据部门名，返回该部门的平均工资
DESC departments;
SELECT * FROM departments;

#我做的
DELIMITER //
CREATE FUNCTION dept_sal(dept_name VARCHAR(30))
RETURNS DOUBLE
BEGIN
		RETURN (SELECT AVG(salary)
						FROM employees
						WHERE department_id = (SELECT department_id
																	FROM departments
																	WHERE department_name = dept_name)
						GROUP BY department_id);
END //

DELIMITER ;
#调用
SET @dept_name = 'IT';
SELECT dept_sal(@dept_name);

#老师讲的
DELIMITER //
CREATE FUNCTION teach_dept_sal(dept_name VARCHAR(30))
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 dept_sal('IT');

#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);