存储过程和函数（自定义函数）

储存过程和函数：

• 存储过程：

                  分类：1. 存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：

                             2. ①没有参数（无参数无返回） ②仅仅带 IN 类型（有参数无返回）

                                 ③仅仅带 OUT 类型（无参数有返回） ④既带 IN 又带 OUT（有参数有返回）

                                 ⑤带 INOUT（有参数有返回）

                  注意：IN、OUT、INOUT 都可以在一个存储过程中带多个。

              LANGUAGE SQL ：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。

          调用格式:

#存储过程与存储函数
#准备工作
CREATE DATABASE dbtest5;
​
USE dbtest5;
​
CREATE TABLE employees
AS 
SELECT *
FROM atguigudb .employees;
​
CREATE TABLE departments
AS 
SELECT * FROM atguigudb.departments;
​
​
#创建储存过程
#无参数返回值
#举例；创建储存过程select_all_data()，查看emps 表的所有数据
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
    SELECT *FROM employees;
END$
DELIMITER ;
#存储过程的调用
 CALL select_all_data();
​
​
#创建存储过程avg_employee_salay()，并返回数据类型为字符串型。
delimiter //
CREATE PROCEDURE avg_employee_salay()
BEGIN 
  SELECT AVG(salary) FROM employees;
END //
delimiter ;
#调用
CALL  avg_employee_salay()
​
​
#创建存储函数过程show_max_salary，用来查看employees表的最高薪资
delimiter //
CREATE PROCEDURE show_max_salary()
BEGIN 
SELECT MAX(salary)
FROM employees;
END //
delimiter;
CALL show_max_salary();
​
​
#带 OUT
#举例4：创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms”输出
DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN 
        SELECT MIN(salary) INTO MS   #把min值写进ms里面
              FROM employees;
END //
DELIMITER;
#调用
CALL show_min_salary(@ms) #@表示用户定义的一个变量
#查看变量值
SELECT @ms;
​
​
#IN
#创建存储过程show_someone_salary()，查看“employees;”表的某个员工的薪资，并用IN参数empname输入员工姓名。
DELIMITER//
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
      SELECT salary FROM employees
            WHERE last_name=employees;
END //
DELIMITER;
​
#调用方式1
CALL show_someone_salary('Abel'); # last_name 里面数据想调用工资就调用哪个
#调用方式2
SET @empname :='Abel';
CALL show_someone_salary(@empname);
​
​
#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 empsalry
         FROM employees
         WHERE last_name=empname;
 END //
delmiter ;
​
#调用
SET @empname='abel';
CALL show_someone_salary2(@empname,@empsalary);
SELECT @empsalary;
​
​
#带INOUT
#举例7：创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员
#  工姓名，输出领导的姓名。
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;

• 储存函数（自定义函数）:

#储存函数（自定义函数）
​
#创建存储函数，名称为email_by_name()，参数定义为空，该函数查询Abel的email，并返回，数据类型为
# 字符串型。
delimiter //
 CREATE FUNCTION emailj_by_name()
 RETURNS VARCHAR (25)
 CONTAINS SQL
 READS SQL DATA
 BEGIN
 RETURN (SELECT email FROM employees WHERE last_name='Abel');
 END //
 delimiter;
#调用
SELECT emailj_by_name();
SELECT email,last_name FROM employees WHERE last_name='Abel';
​
​
#创建存储函数，名称为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(100);  想查询哪个 括号里面写哪个传值
​
SET @emp_id=102;
SELECT email_by_id(@emp_id);
​
​