果洛wap网站建设哪家好,wordpress菜单栏图标,视频加字幕软件app,h5开发网站优点【第十九部分】存储过程与存储函数 文章目录【第十九部分】存储过程与存储函数19. 存储过程与存储函数19.1 存储过程19.2 创建、调用存储过程19.2.1 不带参数19.2.2 IN 类型19.2.3 OUT类型19.2.4 IN和OUT类型同时使用19.2.5 INOUT类型19.3 存储函数19.4 创建、调用存储函数19.5…【第十九部分】存储过程与存储函数 文章目录【第十九部分】存储过程与存储函数19. 存储过程与存储函数19.1 存储过程19.2 创建、调用存储过程19.2.1 不带参数19.2.2 IN 类型19.2.3 OUT类型19.2.4 IN和OUT类型同时使用19.2.5 INOUT类型19.3 存储函数19.4 创建、调用存储函数19.5 存储函数和存储过程区别19.6 存储过程和存储函数的查看、删除19.6.1 查看19.6.2 删除总结19. 存储过程与存储函数
19.1 存储过程
定义: 就是一组经过预先编译的 SQL 语句的封装。
执行过程存储过程预先存储在 MySQL 服务器上需要执行的时候客户端只需要向服务器端发出调用存储过程的命令服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。
与视图相比:相同的优点清晰、安全还可以减少网络传输量; 不同是视图是虚拟表,一般只是用来进行展示,不对底层数据进行直接的操作,而存储过程是程序化的 SQL,直接操作底层数据表,可以做更多复杂的数据出力
存储过程类似函数,将常用的SQL语句进行封装,便于下次的复用
优点:
简化操作提高了SQL语句的复用减少了开发程序员的压力减少操作过程中的失误提高效率减少网络传输量客户端不需要把所有的 SQL 语句通过网络发给服务器减少了 SQL 语句暴露在网上的风险也提高了数据查询的安全性
缺点
可移植性差存储过程不能跨数据库移植比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程在换成其他数据库时都需要重新编写。调试困难存储过程的版本管理很困难比如数据表索引发生变化了可能会导致存储过程失效。它不适合高并发的场景 19.2 创建、调用存储过程
存储过程的参数类型可以分为以下几种情况:
没有参数IN类型OUT类型IN和OUT类型INOUT类型 IN当前参数为输入参数也就是表示入参 存储过程只是读取这个参数的值。如果没有定义参数种类默认就是 IN表示输入参数。 OUT当前参数为输出参数也就是表示出参 执行完成之后调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。 INOUT当前参数既可以为输入参数也可以为输出参数。
传入或者输出参数最好先去查看所查询的表的结构,数据类型一一对应上,避免出现其他错误
关于在创建过程中指定对存储过程的约束条件 [NOT] DETERMINISTIC : DETERMINISTIC表示结果是确定的。每次执行存储过程时相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的相同的输入可能得到不同的输出。ONTAINS SQL: 当前存储过程的子程序包含SQL语句但是并不包含读写数据的SQL语句NO SQL存储过程的子程序中不包含任何SQL语句READS SQL DATA 当前存储过程的子程序中包含读数据的SQL语句MODIFIES SQL DATA: 当前存储过程的子程序中包含写数据的SQL语句SQL SECURITY DEFINER :当前存储过程的创建者或者定义者才能执行当前存储过程SQL SECURITY INVOKER : 当前存储过程的访问权限的用户能够执行当前存储过程COMMENT 添加注释信息 语法
# DELIMITER 自定义设置结束标记符号
# 为什么需要去设置结束标记符号,因为MySQL默认的语句结束符号为分号 ; 为了避免与存储过程中SQL语句结束符相冲突需要使用DELIMITER改变存储过程的结束符。DELIMITER //
CREATE PROCEDURE 存储过程名字(IN|OUT|INOUT 参数名 参数类型,参数名 参数类型 ...)
[约束条件]
BEGIN存储过程体;
END //
DELIMITER ;
# 最后将其恢复原来的结束标记符 DELIMITER ; 19.2.1 不带参数
# 创建存储过程返回各个部门的平均工资
DELIMITER //
CREATE PROCEDURE dep_avg_salary()
BEGINSELECT department_id, AVG(salary) 各部门平均工资FROM employeesWHERE department_id IS NOT NULLGROUP BY department_id;
END //
DELIMITER ;# 调用存储过程
CALL dep_avg_salary()19.2.2 IN 类型
# 查询输入员工id,查询他的名字和薪资
DELIMITER //
CREATE PROCEDURE e_info(IN emp_id INT)
BEGINSELECT last_name,salary FROM employeesWHERE employee_id emp_id;
END //
DELIMITER ;# 调用存储过程
CALL e_info(100)19.2.3 OUT类型
# 输出公司薪资最高的员工
DELIMITER //
CREATE PROCEDURE best_salary_emp(OUT e_name VARCHAR(25))
BEGIN SELECT last_name INTO e_nameFROM employeesWHERE salary (SELECT MAX(salary)FROM employees);
END //
DELIMITER ;# OUT类型调用的方式有些不同,加一个输出名
CALL best_salary_emp(e_name)
SELECT e_name 19.2.4 IN和OUT类型同时使用
# 输入指定部门,输出该部门下薪资最低的员工
DELIMITER //
CREATE PROCEDURE select_lowest_salary(IN dep_id INT,OUT e_name VARCHAR(25))
BEGIN SELECT last_name INTO e_nameFROM employeesWHERE department_id dep_id AND salary (SELECT MIN(salary)FROM employeesWHERE department_id dep_id);
END //
DELIMITER ;CALL select_lowest_salary(20,e_name)
SELECT e_name19.2.5 INOUT类型
# 查询某个员工领导的姓名并用INOUT参数,输入员工姓名输出领导的姓名
DELIMITER //
CREATE PROCEDURE show_mgr_name(INOUT emp_name VARCHAR(25))
BEGINSELECT last_name INTO emp_nameFROM employeesWHERE employee_id (SELECT manager_idFROM employeesWHERE last_name emp_name);
END //
DELIMITER ;# 调用inout模式的参数
# 先设置一个参数进行传入
SET emp_name Fay
CALL show_mgr_name(emp_name)
SELECT emp_name19.3 存储函数 FUNCTION中总是默认为IN参数 RETURNS 返回值类型 该语句表示函数返回数据的类型RETURNS子句只能对FUNCTION做指定对存储函数而言这是强制的。 函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句也可以省略BEGIN…END。 关于在创建过程中指定对存储函数的约束条件 [NOT] DETERMINISTIC : DETERMINISTIC表示结果是确定的。每次执行存储函数时相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的相同的输入可能得到不同的输出。ONTAINS SQL: 当前存储函数的子程序包含SQL语句但是并不包含读写数据的SQL语句NO SQL存储函数的子程序中不包含任何SQL语句READS SQL DATA 当前存储函数的子程序中包含读数据的SQL语句MODIFIES SQL DATA: 当前存储函数的子程序中包含写数据的SQL语句SQL SECURITY DEFINER :当前存储函数的创建者或者定义者才能执行当前存储函数SQL SECURITY INVOKER : 当前存储函数的访问权限的用户能够执行当前存储函数COMMENT 添加注释信息 19.4 创建、调用存储函数
# 语法
# 一定要有返回值
DELIMITER //
CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[约束条件]
BEGINRETURN(函数体); #函数体中肯定有 RETURN 语句
END //
DELIMITER ;使用存储函数
首次创建存储函数失败的话需要加约束条件
# 创建存储函数total_department(),传入部门id函数返回该部门的人数返回类型为整型DELIMITER //
CREATE FUNCTION total_department(d_id INT)
RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT 查询部门总人数
BEGINRETURN(SELECT COUNT(*) FROM employeesWHERE department_id d_id);
END //
DELIMITER ;SELECT total_department(80)19.5 存储函数和存储过程区别
类型关键字调用语法返回值应用场景特点存储过程PROCEDURECALL 存储过程()理解为有0个或多个一般用于更新能够执行对表的操作比如创建表删除表等和事务操作存储函数FUNCTIONSELECT 函数()只能是一个一般用于查询结果为一个值并返回时存储函数可以放在查询语句中使用存储过程不行19.6 存储过程和存储函数的查看、删除
19.6.1 查看
# 查看创建信息
1. SHOW CREATE {PROCEDURE | FUNCTION} \G 存储过程名或函数名# 查看所有的存储过程或者存储函数的状态可以用like过滤信息
2. SHOW {PROCEDURE | FUNCTION} STATUS [LIKE xxx]# information_schema.Routines表中查看存储过程和函数的信息
3. SELECT * FROM information_schema.RoutinesWHERE ROUTINE_NAME存储过程或函数的名 19.6.2 删除
DROP {PROCEDURE | FUNCTION} 存储过程名 | 存储函数名;总结
以上就是今天要讲的内容希望对大家有所帮助
