存储过程和存储函数 练习

#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 `name` VARCHAR(15), IN `password` VARCHAR(25))
BEGIN
INSERT INTO admin(user_name,pwd) VALUES(`name`,`password`);
END //
DELIMITER ;
CALL insert_user('huaqiang','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 b_id  INT,OUT b_phone VARCHAR(15))
BEGIN
SELECT phone INTO b_phone
FROM beauty
WHERE b_id=id;
END //
DELIMITER ;
SET @b_id := 1;
CALL get_phone(1,@b_phone);
SELECT @b_phone;
#3. 创建存储过程date_diff()，实现传入两个女神生日，返回日期间隔大小
DELIMITER //
CREATE PROCEDURE data_diff(IN d1 DATE, IN d2 DATE,OUT d INT)
BEGIN
SELECT DATEDIFF(d1,d2) INTO d # 返回天数 差
FROM DUAL;
END //
DELIMITER ;
CALL data_diff('2001-08-07','2001-08-08',@res);
SELECT @res;
#4. 创建存储过程format_date(),实现传入一个日期，格式化成xx年xx月xx日并返回
DELIMITER //
CREATE PROCEDURE format_date( IN d1 DATE,OUT  str VARCHAR(9))
BEGIN
SELECT DATE_FORMAT(d1,'%y年%m月%d日') INTO str;
END//
DELIMITER ;
CALL format_date('2001-08-07' ,@str);
SELECT @str;
#5. 创建存储过程beauty_limit()，根据传入的起始索引和条目数，查询女神表的记录
DELIMITER //
CREATE PROCEDURE beauty_limit(IN beg INT, IN size INT)
BEGIN 
SELECT * FROM beauty
LIMIT beg, size;
END //
DELIMITER ;
CALL beauty_limit(0,10);
#创建带inout模式参数的存储过程
DELIMITER //
CREATE PROCEDURE get_inout(INOUT  b_id INT)
BEGIN
SELECT COUNT(*) INTO b_id
FROM beauty
WHERE id=b_id;
END //
DELIMITER ;
DROP PROCEDURE get_into;
SET @b_id=100;
CALL get_inout(@b_id);
SELECT @b_id;
# 查看存储过程信息
SHOW CREATE PROCEDURE get_inout;
SHOW PROCEDURE STATUS LIKE 'get_inout';
#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 ;
#有参有返回
#2. 创建函数ename_salary(),根据员工姓名，返回它的工资
DESC employees;
DELIMITER //
CREATE FUNCTION ename_salary(`name` VARCHAR(25)) RETURNS DOUBLE
BEGIN 
RETURN (
SELECT salary FROM employees WHERE last_name=`name`
);
END //
DELIMITER ;
#3. 创建函数dept_sal() ,根据部门名，返回该部门的平均工资
DELIMITER //
CREATE FUNCTION dept_sal(dep_name VARCHAR(20)) RETURNS DOUBLE
BEGIN
RETURN (
SELECT AVG(salary)
FROM departments,employees
WHERE department_name=dep_name
AND departments.`department_id`=employees.`department_id`
GROUP BY department_name
);
END//
DELIMITER ;
DROP FUNCTION dept_sal;
DESC departments;
SELECT * FROM departments;
SELECT dept_sal('Administration');
#4. 创建函数add_float()，实现传入两个float，返回二者之和
DELIMITER //
CREATE FUNCTION add_float(f1 FLOAT,f2 FLOAT) RETURNS FLOAT
BEGIN
RETURN (SELECT f1+f2 ); 
 
END //
DELIMITER ;
SELECT add_float(1,2);