MySQL学习笔记——存储过程与函数(2)

目录

二、存储函数

1、存储函数的使用

1.1 语法分析

1.2 调用存储函数

1.3 代码举例

2、对比存储函数和存储过程

3、存储过程和函数的查看、修改、删除

3.1 查看

3.2 存储过程和函数的修改

3.3 存储过程和函数的删除

4、关于存储过程使用的争议

4.1 优点

4.2 缺点

---

二、存储函数

1、存储函数的使用

1.1 语法分析

说明：  

1.2 调用存储函数

关键字：SELECT

SELECT 函数名(实参列表)

1.3 代码举例

注意：

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

DELIMITER //

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

DELIMITER ;

#调用：

SELECT email_by_name();

举例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) RETRUNS VARCHAR(25) 
DETERMINISTIC 
CONTAINS SQL 
READS SQL DATA

BEGIN
    RETURN (SELECT email FROM employees where employee_id=emp_id)
END //

DELIMITER ;

#调用

#方式1
SELECT email_by_id(101);

#方式2
SET @emp_id:=102
SELECT email_by_id(@emp_id);

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

#创建函数前执行此语句也可以解决报错问题
//SET GLOBAL log_bin_trust_function_creators=1;


#声明函数
DELIMITER //

CREATE FUNCTION count_by_id(dept_id INT) RETURN INT

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

DELIMITER ;

#调用：

SET @dept_id:=50;
SELECT count_by_id(@dept_id);

2、对比存储函数和存储过程

3、存储过程和函数的查看、修改、删除

3.1 查看

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

SHOW CREATE PROCEDURE show_mgr_name;
SHOW CREATE FUNCTION count_by_id;

 使用SHOW STATUS预计查看存储过程和函数的状态信息

SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS LIKE 'email_by_id';

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

SELECT * FROM information_schema.Routines 
WHERE ROUTINE_NAME='email_by_id' AND ROUTINE_TYPE='FUNCTION'; //TYPE里的单引号要大写,TYPE用于存储函数和存储过程重名，要确定是FUNCTION还是PROCEDURE

3.2 存储过程和函数的修改

只修改相关特性：

3.3 存储过程和函数的删除

4、关于存储过程使用的争议

4.1 优点

4.2 缺点