青海营销网站建设公司,网站安全检测可以检测哪些内容风险信息,穆棱市城乡建设局网站,高端品牌发布会一、存储过程
1、什么是存储过程
它是一组经过预先编译的SQL的封装它被存储在MySQL服务器上#xff0c;当需要执行它时#xff0c;客户端只需要向服务器发出调用命令#xff0c;就可以把这一系列预先存储好的SQL语句全部执行
2、存储过程的优缺点
优点 简化操作#xf…一、存储过程
1、什么是存储过程
它是一组经过预先编译的SQL的封装它被存储在MySQL服务器上当需要执行它时客户端只需要向服务器发出调用命令就可以把这一系列预先存储好的SQL语句全部执行
2、存储过程的优缺点
优点 简化操作一次编译多次使用提高了sql语句的重用性直接调之前写好的SQL语句减少出现失误的可能性减少了网络传输量客户端只需要发出调用指令即可不需要传输完整的SQL语句提高了安全性减少了SQL语句在网络中暴露的风险而且可以限制执行者权限 缺点阿里开发规范里面禁止使用存储过程主要就是和第一二点有关 可移植性差不可以跨数据库执行调试困难不适合高并发场景高并发场景下需要减少数据库压力有时数据库会采用分库分表的形式而且对可扩展性要求很高在这种情况下存储过程会变得难以维护增大了数据库的压力存储过程的版本管理很困难假设数据库索引发生变化可能导致存储过程失效
3、和视图、函数的差别
视图是虚拟表一般只负责查询数据不会通过视图对底层数据表进行操作。而存储过程是专门存储程序化的SQL的可以直接操作底层数据表函数是有返回值的存储过程没有返回值它只能将结果输出到某些OUT修饰的参数中
4、存储过程的创建
CREATE PROCEDURE 存储过程名称(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics...]
BEGIN存储过程体
END如何理解存储过程名称后面的形参列表
没有跟形参列表存储过程无参数无返回参数修饰符为IN当前参数为入参如果当前参数没有修饰符那就默认是入参参数修饰符为OUT当前参数为输出参数当执行完存储过程之后客户端可以读取这个参数的返回值参数修饰符为INOUT当前参数既可以是入参也可以是输出参数
characteristics表示创建存储过程时指定的特性
characteristic:COMMENT string| LANGUAGE SQL| [NOT] DETERMINISTIC| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER }LANGUAGE SQL表明存储过程执行体所用语言是SQL[NOT] DETERMINISTIC入参相同时如果存储过程执行后得到的结果不会发生改变。那么存储过程就是确定性的即DETERMINISTIC。否则就是不确定性NOT DETERMINISTIC。在没有显式指定DETERMINISTIC的情况下存储过程默认是不确定性NOT DETERMINISTIC{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }指明子程序使用SQL的限制 CONTAINS SQL当前存储过程的子程序包含SQL语句但是不包含读写数据的SQL语句NO SQL当前存储过程的子程序不包含SQL语句READS SQL DATA 当前存储过程的子程序包含读数据的SQL语句MODIFIES SQL DATA 当前存储过程的子程序包含写数据的SQL语句 SQL SECURITY { DEFINER | INVOKER }当前存储过程哪些用户可以执行。如果是DEFINER就是只有创建者才能执行。INVOKER就是拥有访问权限的用户就可以执行COMMENT string注释信息用于描述存储过程
5、使用DELIMITER设置新的结束标记 如果我们不将MySQL默认的语句结束符由;改为自定义的标记 那么下面的存储过程执行到SELECT语句结束后就不会继续往下走了这显然是错误的
所以我们在这里将$或者//定义为语句执行结束的标记 并且在执行完存储过程之后再将结束标记改回;
DELIMITER $CREATE PROCEDURE SELECT_all_emp()
BEGINSELECT * FROM t_emp;
END $DELIMITER ;6、存储过程的调用使用关键字CALL 1无参数类型的调用
DELIMITER $CREATE PROCEDURE select_AVG_salary()
BEGINSELECT AVG(salary) FROM t_emp;
END $DELIMITER $CALL select_AVG_salary();2带OUT类型的调用注意输出变量的类型需要和表中字段的类型相同 假设我们要查询当前员工表中最低的工资并且把将最低薪资通过参数ms输出
DELIMITER $CREATE PROCEDURE show_min_salary(OUT ms DOUBLE(8,2))
BEGINSELECT MIN(salary) INTO ms #使用INTO将最低薪资写入到ms变量FROM t_emp;
END $DELIMITER ;#调用使用自定义ms变量
CALLL show_min_salary(ms);#查看此变量值
SELECT ms;3带IN类型的调用注意输入变量的类型需要和表中字段的类型相同 假设我们要查询当前员工表中某个员工的薪资并使用IN参数empname输入员工姓名
DELIMITER $CREATE PROCEDURE show_someony_salary(IN empname VARCHAR(20))
BEGINSELECT salaryFROM t_emp WHERE last_name empname;
END $DELIMITER ;# 调用方式一直接传
CALL show_someony_salary(Decade);
# 调用方式二定义变量然后调用的时候传入变量
SET name Decade;
CALL show_someony_salary(name);4带IN和OUT类型的调用 假设我们要查询当前员工表中某个员工的薪资并使用IN参数empname输入员工姓名使用OUT参数empsalary输出薪资
DELIMITER $CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE(10,2))
BEGINSELECT salary FROM t_emp INTO empsalaryWHERE last_name empname;
END $DELIMITER ;# 调用
CALL show_someone_salary2(Decade, empsalary);
# 或者
SET empname Decade;
CALL show_someone_salary2(empname, empsalary);#查看此变量值
SELECT empsalary;5带INOUT类型的调用
DELIMITER $CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(20))
BEGINSELECT last_name INTO empnameFROM t_empwhere emp_id (SELECT manager_id FROM t_empwhere last_name empname);
END $DELIMITER ;# 调用
SET empname Decade;
CALL show_mgr_name(empname);# 查看输出参数
SELECT empname;二、存储函数
前面已经学习过一些系统提供的函数包括单行函数和聚合函数
1、存储函数的创建
CREATE FUNCTION 存储函数名称(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics...]
BEGIN函数体 #函数体中肯定有 return语句
END存储函数的参数默认是IN类型的也就是说它只有入参的说法Returns Type是必须要有的它指定了存储函数的返回值类型函数体中必须要包含 return语句characteristics的取值与上面存储过程的约束是一样的
2、使用SELECT进行存储函数的调用
SELECT 函数名(参数列表);3、如果创建存储函数时没有指明characteristics可能会出现you *might* want to use the less safe log_bin_trust_function_creators variable这个报错我们推荐以下两种方式进行解决
方式一加上必要的函数特性[NOT] DETERMINISTIC和{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
DELIMITER $CREATE FUNCTION show_email_by_name()
RETURNS VARCHAR(20)DETERMINISTICCONTAINS SQLREADS SQL DATA
BEGINRETURN (SELECT eamil FROM t_emp WHERE last_name Decade);
END $DELIMITER ;方式二执行SET GLOBAL log_bin_trust_function_creators 1;
4、存储函数也要用到DELIMITER下面我们就了解一下存储函数的几种使用方式 1不传参数
DELIMITER $CREATE FUNCTION show_email_by_name()
RETURNS VARCHAR(20)DETERMINISTICCONTAINS SQLREADS SQL DATA
BEGINRETURN (SELECT eamil FROM t_emp WHERE last_name Decade);
END $DELIMITER ;# 调用存储函数
SELECT show_email_by_name();2传入参数
SET GLOBAL log_bin_trust_function_creators 1;DELIMITER $CREATE FUNCTION show_email_by_name(empname VARCHAR(15))
RETURNS VARCHAR(20)
BEGINRETURN (SELECT eamil FROM t_emp WHERE last_name empname);
END $DELIMITER ;# 调用存储函数
SELECT show_email_by_name(Decade);
# 或者
SET empname Decade;
SELECT show_email_by_name(empname);5、存储函数和存储过程的比较
创建关键字存储函数是FUNCTION存储过程是PROCEDURE返回值存储函数只有一个返回值存储过程可以输出0个或者多个参数调用存储函数用SELECT存储过程用CALL使用场景存储函数用于查询存储过程用于更新数据存储函数可以放到查询语句中使用存储过程不可以
三、存储过程、函数的查看、修改与删除
1、存储过程、函数的查看 1查看存储过程和函数的创建信息
SHOW CREATE PROCEDURE 存储过程名称;SHOW CREATE FUNCTION 存储函数名称;2查看存储过程和函数的状态信息
SHOW PROCEDURE/FUNCTION STATUS; #会展示所有存储过程/函数SHOW PROCEDURE STATUS LIKE %模糊查询%;
SHOW FUNCTION STATUS LIKE %模糊查询%;3从information_schema.Routines查看存储过程和函数的状态信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME 存储过程名称 AND ROUTINE_TYPE PROCEDURE;SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME 存储函数名称 AND ROUTINE_TYPE FUNCTION;2、存储过程、函数的修改 存储过程、函数的修改不能修改过程体/函数体只能通过ALTER对特性characteristics进行修改
ALTER PROCEDURE/FUNCTION 存储过程或者函数的名称 [characteristics...];注意只能修改以下特性
COMMENT string
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }假设我们要修改上面的存储过程show_someone_salary2修改执行权限和备注
ALTER PROCEDURE show_someone_salary2
SQL SECURITY INVOKER
COMMENT 测试修改;3、存储过程、函数的删除
DROP PROCEDURE/FUNCTION IF EXISTS 存储过程/函数名称;如有错误欢迎指正
