所有知识体系文章，GitHub已收录，欢迎老板们前来Star！ GitHub地址： https://github.com/Ziphtracks/JavaLearningmanual 存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升 当我们了了解存储过程是什么之后，就需要了解数据库中存在的这三种类型的数据库存储类型程序，如下： 注意： 其他的数据库提供了别的数据存储程序，包括包和类。目前MySQL不提供这种结构。 虽然目前的开发中存储程序我们使用的并不是很多，但是不一定就否认它。其实存储程序会为我们使用和管理数据库带来了很多优势： 这里我大致解释一下上述几种使用存储程序的优势： 我们要知道在Java语言中，我们使用数据库与Java代码结合持久化存储需要引入JDBC来完成。会想到JDBC，我们是否还能想起SQL注入问题呢？虽然使用PreparedStatement解决SQL注入问题，那就真的是绝对安全吗？不，它不是绝对安全的。 这时候分析一下数据库与Java代码的连接操作流程。在BS结构中，一般都是浏览器访问服务器的，再由服务器发送SQL语句到数据库，在数据库中对SQL语句进行编译运行，最后把结果通过服务器处理再返回浏览器。在此操作过程中，浏览器对服务器每发送一次对数据库操作的请求就会调用对应的SQL语句编译和执行，这是一件十分浪费性能的事情，性能下降 了就说明对数据库的操作效率低 了。 还有一种可能是，在这个过程中进行发送传输的SQL语句是对真实的库表进行操作的SQL语句，如果在发送传输的过程中被拦截了，一些不法分子会根据他所拦截的SQL语句推断出我们数据库中的库表结构，这是一个很大的安全隐患 。 关于可维护性的提高，这里模拟一个场景。通常数据库在公司中是由DBA来管理的，如果管理数据库多年的DBA辞职了，此时数据库会被下一任DBA来管理。这里时候问题来了，数据库中这么多的数据和SQL语句显然对下一任管理者不太友好。就算管理多年的DBA长时间不操作查看数据库也会忘记点什么东西。所以，我们在需要引入存储程序来进行SQL语句的统一编写和编译，为维护提供了便利 。（其实我觉得这个例子并不生动合理，但是为了大家能理解，请体谅！） 讲了很多存储程序的优势演变过程，其核心就是： 需要将编译好的一段或多段SQL语句放置在数据库端的存储程序中，以便解决以上问题并方便开发者直接调用。 存储过程时数据库的一个重要的对象，可以封装SQL语句集，可以用来完成一些较复杂的业务逻辑，并且可以入参（传参）、出参（返回参数），这里与Java中封装方式十分相似。 而且创建时会预先编译后保存，开发者后续的调用都不需要再次编译。 存储过程使用的优缺点其实在1.3中的优势中说到了。这里我简单罗列一下存储过程的优点与缺点。 英语好或者有能力的小伙伴可以去参考一下官方文档。如果不参考官方文档，没关系，我在下面也会详细讲述MySQL存储过程的各个知识点。 注意： SQL语句默认的结束符为 例如： 使用存储过程来查询员工的工资（无参） 注意： 如果在特殊的必要情况下，我们还可以通过 为什么我在这里提供了drop（删除）呢？ 是因为我们在使用的时候如果需要修改存储过程中的内容，我们需要先删除现有的存储过程后，再creat重新创建。 声明局部变量语法： 赋值语法： 注意： 局部变量的定义，在begin/end块中有效。 使用set为参数赋值 使用into接收参数 用户自定义用户变量，当前会话（连接）有效。与Java中的成员变量相似。 会话变量是由系统提供的，只在当前会话（连接）中有效。 语法： 这里我获取了一下所有的会话变量，大概有500条会话变量的记录。等我们深入学习MySQL后，了解了各个会话变量值的作用，可以根据需求和场景来修改会话变量值。 全局变量由系统提供，整个MySQL服务器内有效。 语法： 入参出参的语法我们在文章开头已经提过了，但是没有演示，在这里我将演示一下入参出参的使用。 语法： 出参in 使用出参in时，就是需要我们传入参数，在这里可以对参入的参数加以改变。简单来说in只负责传入参数到存储过程中，类似Java中的形参。 入参out 在使用out时，需要传入一个参数。而这个参数相当于是返回值，可以通过调用、接收来获取这个参数的内容。简单来说out只负责作返回值。 入参出参inout inout关键字，就是把in和out合并成了一个关键字使用。被关键字修饰的参数既可以出参也可以入参。 扩展： 扩展例子： 解释扩展例子： 查询员工表中id为1员工的年龄，exp2就可以为该员工的出生年月日，并以年为单位计算。 语法： 举例： 传入所查询的id参数查询工资标准（s<=6000为低工资标准；6000<s<=10000为中工资标准；10000<s<=15000为中上工资标准；s>=15000为高工资标准） 关于case语句，不仅仅在存储过程中可以使用，MySQL基础查询语句中也有用到过。相当于是Java中的switch语句。 语法： 举例： loop为死循环，需要手动退出循环，我们可以使用 可以把leave看成Java中的break；与之对应的，就有 语法： 注意：别名和别名控制的是同一个标签。 示例1： 循环打印1~10（leave控制循环的退出） 注意：该loop循环为死循环，我们查的110数字是i，在死循环中设置了当大于等于10时停止循环，也就是说先后执行了10次该循环内的内容，结果查询了10次，生成了10个结果（110）。 打印结果： 示例2： 循环打印1~10（iterate和leave控制循环） 注意：这里我们使用字符串拼接计数器结果，而条件如果用iterate就必须时 i < 10 了！ repeat循环类似Java中的do while循环，直到条件不满足才会结束循环。 语法： 示例： 循环打印1~10 while循环就与Java中的while循环很相似了。 语法： 示例： 循环打印1~10 至于流程控制的继续和结束，我们在前面已经使用过了。这里再列举一下。 leave：与Java中break;相似 iterate：与Java中的continue;相似 游标是可以得到某一个结果集并逐行处理数据。游标的逐行操作，导致了游标很少被使用！ 语法： 了解了游标的语法，我们开始使用游标。如下： 示例： 使用游标查询id、name和salary。 执行结果： 因为游标逐行操作的特点，导致我们只能使用游标来查询一行记录。怎么改善代码才可以实现查询所有记录呢？聪明的小伙伴想到了使用循环。对，我们试试使用一下循环。 我们使用循环之后，发现有一个问题，因为循环是死循环，我们不加结束循环的条件，游标会一直查询记录，当查到没有的记录的时候，就会抛出异常 如果我们想办法指定结束循环的条件该怎么做呢？ 这时候可以声明一个boolean类型的标记。如果为true时则查询结果集，为false时则结束循环。 上述代码你会发现并没有写完，它留下了一个很严肃的问题。当flag = false时候可以结束循环。但是什么时候才让flag为false啊？ 于是，MySQL为我们提供了一个 handler句柄语法： handler句柄可以用来捕获异常，也就是说在这个场景中当捕获到 终极版示例： 解决了多行查询以及结束循环问题。 执行结果： 在执行结果中，可以看出查询结果以多次查询的形式，分布显示到了每一个查询结果窗口中。 注意： 在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。 语法： 注意：异常情况可以写异常错误码、异常别名或SQLSTATE码。 handler操作： 异常情况列表： 注意： MySQL中各种异常情况代码、错误码、别名和SQLSTATEM码可参考官方文档： https://dev.mysql.com/doc/refman/5.6/en/server-error-reference.html 写法示例： 需求： 创建下个月的每天对应的表，创建的表格式为： 描述： 我们需要用某个表记录很多数据，比如记录某某用户的搜索、购买行为（注意，此处是假设用数据库保存），当每天记录较多时，如果把所有数据都记录到一张表中太庞大，需要分表，我们的要求是，每天一张表，存当天的统计数据，就要求提前生产这些表——每月月底创建下一个月每天的表！ 预编译： 执行： 通过数据库对象创建或删除表： 关于时间处理的语句： 代码： 在MySQL存储过程中，如果没有显示的定义characteristic，它会隐式的定义一系列特性的默认值来创建存储过程。 LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT ‘’ 如有死循环处理，可以通过下面的命令查看并杀死（结束） GitHub同步文章地址： https://github.com/Ziphtracks/JavaLearningmanual
MySQL存储过程
一、存储过程
1.1 什么是存储过程
1.2 数据库存储过程程序
1.3 为什么要使用存储程序
二、存储过程的使用步骤
2.1 存储过程的开发思想
2.2 存储过程的优缺点
2.3 MySQL存储过程的官方文档
https://dev.mysql.com/doc/refman/5.6/en/preface.html 2.3 存储过程的使用语法
create PROCEDURE 过程名( in|out|inout 参数名 数据类型 , ...) begin sql语句; end; call 过程名(参数值); in是定义传入参数的关键字。out是定义出参的关键字。inout是定义一个出入参数都可以的参数。如果括号内什么都不定义，就说明该存储过程时一个无参的函数。在后面会有详细的案例分析。;，所以在使用以上存储过程时，会报1064的语法错误。我们可以使用DELIMITER关键字临时声明修改SQL语句的结束符为//，如下：-- 临时定义结束符为"//" DELIMITER // create PROCEDURE 过程名( in|out 参数名 数据类型 , ...) begin sql语句; end// -- 将结束符重新定义回结束符为";" DELIMITER ; delimiter关键字将;结束符声明回来使用，在以下案例中我并没有这样将结束符声明回原来的;，在此请大家注意~# 声明结束符为// delimiter // # 创建存储过程(函数) create procedure se() begin select salary from employee; end // # 调用函数 call se() // # 删除已存在存储过程——se()函数 drop procedure if exists se // 三、存储过程的变量和赋值
3.1 局部变量
declare var_name type [default var_value];# set赋值 # 声明结束符为// delimiter // # 创建存储过程 create procedure val_set() begin # 声明一个默认值为unknown的val_name局部变量 declare val_name varchar(32) default 'unknown'; # 为局部变量赋值 set val_name = 'Centi'; # 查询局部变量 select val_name; end // # 调用函数 call val_set() // delimiter // create procedure val_into() begin # 定义两个变量存放name和age declare val_name varchar(32) default 'unknown'; declare val_age int; # 查询表中id为1的name和age并放在定义的两个变量中 select name,age into val_name,val_age from employee where id = 1; # 查询两个变量 select val_name,val_age; end // call val_into() // 3.2 用户变量
@val_namedelimiter // create procedure val_user() begin # 为用户变量赋值 set @val_name = 'Lacy'; end // # 调用函数 call val_user() // # 查询该用户变量 select @val_name // 3.3 会话变量
@@session.val_name# 查看所有会话变量 show session variables; # 查看指定的会话变量 select @@session.val_name; # 修改指定的会话变量 set @@session.val_name = 0; delimiter // create procedure val_session() begin # 查看会话变量 show session variables; end // call val_session() // 3.4 全局变量
@@global.val_name# 查看全局变量中变量名有char的记录 show global variables like '%char%' // # 查看全局变量character_set_client的值 select @@global.character_set_client // 3.5 入参出参
in|out|inout 参数名 数据类型 , ...in定义出参；out定义入参；inout定义出参和入参。delimiter // create procedure val_in(in val_name varchar(32)) begin # 使用用户变量出参(为用户变量赋参数值) set @val_name1 = val_name; end // # 调用函数 call val_in('DK') // # 查询该用户变量 select @val_name1 // delimiter // # 创建一个入参和出参的存储过程 create procedure val_out(in val_id int,out val_name varchar(32)) begin # 传入参数val_id查询员工返回name值(查询出的name值用出参接收并返回) select name into val_name from employee where id = val_id; end // # 调用函数传入参数并声明传入一个用户变量 call val_out(1, @n) // # 查询用户变量 select @n // delimiter // create procedure val_inout(in val_name varchar(32), inout val_age int) begin # 声明一个a变量 declare a int; # 将传入的参数赋值给a变量 set a = val_age; # 通过name查询age并返回val_age select age into val_age from employee where name = val_name; # 将传入的a与-和查询age结果字符串做拼接并查询出来(concat——拼接字符串) select concat(a, '-', val_age); end // # 声明一个用户变量并赋予参数为40 set @ages = '40' // # 调用函数并传入参数值 call val_inout('Ziph', @ages) // # 执行结果 # 40-18 四、存储过程中的流程控制
4.1 if 条件判断（推荐）
timestampdiff(unit, exp1, exp2)为exp2 – exp1得到的差值，而单位是unit。（常用于日期）select timestampdiff(year,’2020-6-6‘,now()) from emp e where id = 1;IF 条件判断 THEN 结果 [ELSEIF 条件判断 THEN 结果] ... [ELSE 结果] END IF delimiter // create procedure s_sql(in val_id int) begin # 声明一个局部变量result存放工资标准结果 declare result varchar(32); # 声明一个局部变量存放查询得到的工资 declare s double; # 根据入参id查询工资 select salary into s from employee where id = val_id; # if判断的使用 if s <= 6000 then set result = '低工资标准'; elseif s <= 10000 then set result = '中工资标准'; elseif s <= 15000 then set result = '中上工资标准'; else set result = '高工资标准'; end if; # 查询工资标准结果 select result; end // # 调用函数，传入参数 call s_sql(1); 4.2 case条件判断
# 语法一 CASE case_value WHEN when_value THEN 结果 [WHEN when_value THEN 结果] ... [ELSE 结果] END CASE # 语法二(推荐语法) CASE WHEN 条件判断 THEN 结果 [WHEN 条件判断 THEN 结果] ... [ELSE 结果] END CASE # 语法一 delimiter // create procedure s_case(in val_id int) begin # 声明一个局部变量result存放工资标准结果 declare result varchar(32); # 声明一个局部变量存放查询得到的工资 declare s double; # 根据入参id查询工资 select salary into s from employee where id = val_id; case s when 6000 then set result = '低工资标准'; when 10000 then set result = '中工资标准'; when 15000 then set result = '中上工资标准'; else set result = '高工资标准'; end case; select result; end // call s_case(1); # 语法二(推荐) delimiter // create procedure s_case(in val_id int) begin # 声明一个局部变量result存放工资标准结果 declare result varchar(32); # 声明一个局部变量存放查询得到的工资 declare s double; # 根据入参id查询工资 select salary into s from employee where id = val_id; case when s <= 6000 then set result = '低工资标准'; when s <= 10000 then set result = '中工资标准'; when s <= 15000 then set result = '中上工资标准'; else set result = '高工资标准'; end case; select result; end // call s_case(1); 4.3 loop循环
leave来退出循环iterate（继续循环）也可以看成Java的continue[别名:] LOOP 循环语句 END LOOP [别名] delimiter // create procedure s_loop() begin # 声明计数器 declare i int default 1; # 开始循环 num: loop # 查询计数器记录的值 select i; # 判断大于等于停止计数 if i >= 10 then leave num; end if; # 计数器自增1 set i = i + 1; # 结束循环 end loop num; end // call s_loop(); delimiter // create procedure s_loop1() begin # 声明变量i计数器 declare i int default 1; # 声明字符串容器 declare str varchar(256) default '1'; # 开始循环 num: loop # 计数器自增1 set i = i + 1; # 字符串容器拼接计数器结果 set str = concat(str, '-', i); # 计数器i如果小于10就继续执行 if i < 10 then iterate num; end if; # 计数器i如果大于10就停止循环 leave num; # 停止循环 end loop num; # 查询字符串容器的拼接结果 select str; end // call s_loop1(); 4.4 repeat循环
[别名:] REPEAT 循环语句 UNTIL 条件 END REPEAT [别名] delimiter // create procedure s_repeat() begin declare i int default 1; declare str varchar(256) default '1'; # 开始repeat循环 num: repeat set i = i + 1; set str = concat(str, '-', i); # until 结束条件 # end repeat 结束num 结束repeat循环 until i >= 10 end repeat num; # 查询字符串拼接结果 select str; end // call s_repeat(); 4.5 while循环
[别名] WHILE 条件 DO 循环语句 END WHILE [别名] delimiter // create procedure s_while() begin declare i int default 1; declare str varchar(256) default '1'; # 开始while循环 num: # 指定while循环结束条件 while i < 10 do set i = i + 1; set str = concat(str, '+', i); # while循环结束 end while num; # 查询while循环拼接字符串 select str; end // call s_while(); 4.6 流程控制语句（继续、结束）
leave 标签; iterate 标签; 五、游标与handler
5.1 游标
DECLARE 游标名 CURSOR FOR 查询语句 -- 打开语法 OPEN 游标名 -- 取值语法 FETCH 游标名 INTO var_name [, var_name] ... -- 关闭语法 CLOSE 游标名 delimiter // create procedure f() begin declare val_id int; declare val_name varchar(32); declare val_salary double; # 声明游标 declare emp_flag cursor for select id, name, salary from employee; # 打开 open emp_flag; # 取值 fetch emp_flag into val_id, val_name, val_salary; # 关闭 close emp_flag; select val_id, val_name, val_salary; end // call f(); delimiter // create procedure f() begin declare val_id int; declare val_name varchar(32); declare val_salary double; # 声明游标 declare emp_flag cursor for select id, name, salary from employee; # 打开 open emp_flag; # 使用循环取值 c:loop # 取值 fetch emp_flag into val_id, val_name, val_salary; end loop; # 关闭 close emp_flag; select val_id, val_name, val_salary; end // call f(); 1329：未获取到选择处理的行数。delimiter // create procedure f() begin declare val_id int; declare val_name varchar(32); declare val_salary double; # 声明flag标记 declare flag boolean default true; # 声明游标 declare emp_flag cursor for select id, name, salary from employee; # 打开 open emp_flag; # 使用循环取值 c:loop fetch emp_flag into val_id, val_name, val_salary; # 如果标记为true则查询结果集 if flag then select val_id, val_name, val_salary; # 如果标记为false则证明结果集查询完毕，停止死循环 else leave c; end if; end loop; # 关闭 close emp_flag; select val_id, val_name, val_salary; end // call f(); handler句柄。它可以帮我们解决此疑惑。declare continue handler for 异常 set flag = false;1329：未获取到选择处理的行数时，就将flag标记的值改为false。这样使用handler句柄就解决了结束循环的难题。让我们来试试吧！delimiter // create procedure f() begin declare val_id int; declare val_name varchar(32); declare val_salary double; # 声明flag标记 declare flag boolean default true; # 声明游标 declare emp_flag cursor for select id, name, salary from employee; # 使用handler句柄来解决结束循环问题 declare continue handler for 1329 set flag = false; # 打开 open emp_flag; # 使用循环取值 c:loop fetch emp_flag into val_id, val_name, val_salary; # 如果标记为true则查询结果集 if flag then select val_id, val_name, val_salary; # 如果标记为false则证明结果集查询完毕，停止死循环 else leave c; end if; end loop; # 关闭 close emp_flag; select val_id, val_name, val_salary; end // call f(); 5.2 handler句柄
DECLARE handler操作 HANDLER FOR 情况列表...(比如：异常错误情况) 操作语句
DECLARE exit HANDLER FOR SQLSTATE '3D000' set flag = false; DECLARE continue HANDLER FOR 1050 set flag = false; DECLARE continue HANDLER FOR not found set flag = false; 六、循环创建表
comp_2020_06_01、comp_2020_06_02、...PREPARE 数据库对象名 FROM 参数名EXECUTE 数据库对象名 [USING @var_name [, @var_name] ...]{DEALLOCATE | DROP} PREPARE 数据库对象名-- EXTRACT(unit FROM date) 截取时间的指定位置值 -- DATE_ADD(date,INTERVAL expr unit) 日期运算 -- LAST_DAY(date) 获取日期的最后一天 -- YEAR(date) 返回日期中的年 -- MONTH(date) 返回日期的月 -- DAYOFMONTH(date) 返回日 -- 思路：循环构建表名 comp_2020_06_01 到 comp_2020_06_30；并执行create语句。 delimiter // create procedure sp_create_table() begin # 声明需要拼接表名的下一个月的年、月、日 declare next_year int; declare next_month int; declare next_month_day int; # 声明下一个月的月和日的字符串 declare next_month_str char(2); declare next_month_day_str char(2); # 声明需要处理每天的表名 declare table_name_str char(10); # 声明需要拼接的1 declare t_index int default 1; # declare create_table_sql varchar(200); # 获取下个月的年份 set next_year = year(date_add(now(),INTERVAL 1 month)); # 获取下个月是几月 set next_month = month(date_add(now(),INTERVAL 1 month)); # 下个月最后一天是几号 set next_month_day = dayofmonth(LAST_DAY(date_add(now(),INTERVAL 1 month))); # 如果下一个月月份小于10，就在月份的前面拼接一个0 if next_month < 10 then set next_month_str = concat('0',next_month); else # 如果月份大于10，不做任何操作 set next_month_str = concat('',next_month); end if; # 循环操作(下个月的日大于等于1循环开始循环) while t_index <= next_month_day do # 如果t_index小于10就在前面拼接0 if (t_index < 10) then set next_month_day_str = concat('0',t_index); else # 如果t_index大于10不做任何操作 set next_month_day_str = concat('',t_index); end if; # 拼接标命字符串 set table_name_str = concat(next_year,'_',next_month_str,'_',next_month_day_str); # 拼接create sql语句 set @create_table_sql = concat( 'create table comp_', table_name_str, '(`grade` INT(11) NULL,`losal` INT(11) NULL,`hisal` INT(11) NULL) COLLATE='utf8_general_ci' ENGINE=InnoDB'); # 预编译 # 注意：FROM后面不能使用局部变量！ prepare create_table_stmt FROM @create_table_sql; # 执行 execute create_table_stmt; # 创建表 DEALLOCATE prepare create_table_stmt; # t_index自增1 set t_index = t_index + 1; end while; end// # 调用函数 call sp_create_table() 七、其他
7.1 characteristic
7.2 死循环处理
show processlist; kill id; 7.3 select语句中书写case
select case when 条件判断 then 结果 when 条件判断 then 结果 else 结果 end 别名, * from 表名; 7.4 复制表和数据
CREATE TABLE dept SELECT * FROM procedure_demo.dept; CREATE TABLE emp SELECT * FROM procedure_demo.emp; CREATE TABLE salgrade SELECT * FROM procedure_demo.salgrade; 7.5 临时表
create temporary table 表名( 字段名 类型 [约束], name varchar(20) )Engine=InnoDB default charset utf8; -- 需求：按照部门名称查询员工，通过select查看员工的编号、姓名、薪资。（注意，此处仅仅演示游标用法） delimiter $$ create procedure sp_create_table02(in dept_name varchar(32)) begin declare emp_no int; declare emp_name varchar(32); declare emp_sal decimal(7,2); declare exit_flag int default 0; declare emp_cursor cursor for select e.empno,e.ename,e.sal from emp e inner join dept d on e.deptno = d.deptno where d.dname = dept_name; declare continue handler for not found set exit_flag = 1; -- 创建临时表收集数据 CREATE temporary TABLE `temp_table_emp` ( `empno` INT(11) NOT NULL COMMENT '员工编号', `ename` VARCHAR(32) NULL COMMENT '员工姓名' COLLATE 'utf8_general_ci', `sal` DECIMAL(7,2) NOT NULL DEFAULT '0.00' COMMENT '薪资', PRIMARY KEY (`empno`) USING BTREE ) COLLATE='utf8_general_ci' ENGINE=InnoDB; open emp_cursor; c_loop:loop fetch emp_cursor into emp_no,emp_name,emp_sal; if exit_flag != 1 then insert into temp_table_emp values(emp_no,emp_name,emp_sal); else leave c_loop; end if; end loop c_loop; select * from temp_table_emp; select @sex_res; -- 仅仅是看一下会不会执行到 close emp_cursor; end$$ call sp_create_table02('RESEARCH');
记得给我一个Star哦！
本网页所有视频内容由 imoviebox边看边下-网页视频下载, iurlBox网页地址收藏管理器 下载并得到。
ImovieBox网页视频下载器 下载地址: ImovieBox网页视频下载器-最新版本下载
本文章由: imapbox邮箱云存储,邮箱网盘,ImageBox 图片批量下载器,网页图片批量下载专家,网页图片批量下载器,获取到文章图片,imoviebox网页视频批量下载器,下载视频内容,为您提供.
阅读和此文章类似的: 全球云计算
官方软件产品操作指南 (170)
