【黑马程序员】两小时带你玩转MySQL数据库存储过程【配套源码+笔记】_哔哩哔哩_bilibili
一、语法
delimter $$
声明结束符。因为MySQL 默认使用“;”作为结束符,而在存储过程中,会使用“;”作为一段语句的结束,导致“;”使用冲突。
1. 语法结构
CREATE
[DEFINER = user]
PROCEDURE sp_name ( [ proc_parameter[...] ] )
[chrarcteristic ... ] routine_body
-- proc_parameter 参数部分,可以如下书写:
[ IN | OUT | INOUT ] param_name type
-- type 类型可以是 MySQL 支持的所有类型
-- routine_body(程序体)部分,可以书写合法的 SQL 语句
BEGIN ... END
简单演示:
delimiter $$ CREATE PROCEDURE hello_procedure() BEGIN select * from users; END $$
使用:
call hello_procedure();
2. 变量及赋值
类比一下 java 中的局部变量和成员变量的声明和使用
(1)局部变量
用户自定义,在 begin/end 块中有效
语法:
声明变量: declare var_name type [ default var_value ];
举例:declare nickname varchar(32);
-- set 赋值
delimiter $$ create procedure sp_var01() begin declare nickname varchar(32) default 'unkown'; set nickname = 'ZS'; select nickname; end $$
-- into 赋值
delimiter $$ create procedure sp_var_into() begin declare emp_name varchar(32) default 'unkown'; declare emp_no int default 0; select e.empno,e.ename into emp_no,emp_name from emp e where e.empno = 7839; select emp_no,emp_name ; end $$
(2)用户变量
用户自定义,当前会话(连接)有效
-- 赋值
delimiter $$ create procedure sp_var02() begin set @nickname = 'ZS'; end $$ call sp_var02() $$ select @nickname $$
(3)会话变量
由系统提供,当前会话(连接)有效
语法:
@@session.var_name
举例:
-- 查看会话变量 show session variables; -- 查看某会话变量 select @@session.unique_checks; -- 修改会话变量 set @@session.unique_checks = 0;
(4)全局变量
由系统提供,整个 mysql 服务器有效
语法:
@@global.var_name
举例:
-- 查看全局变量中变量名有char的记录 show global variables like '%char%'; -- 查看全局变量character_set_client的值 select @@global.character_set_client;
3. 入参出参
-- 语法
in | out | inout param_name type
(1)-- IN 类型演示
delimiter $$ create procedure sp_param01(in age int) begin set @user_age = age; end $$ call sp_param01(10) $$ select @user_age $$
(2)-- OUT 类型演示,只负者输出
delimiter $$ create procedure sp_param02(in loc varchar(64),out dedpt_no int(11)) begin select d.deptno into dept_no from dept d where d.loc = loc; end $$ -- 测试 set @dept_no = 100; call sp_param02('DALLAS',@dept_no); $$ select @dept_no; $$
(3)-- INOUT 类型
delimiter $$ create procedure sp_param03(inout name varchar) begin select name = concat('hello',name); end $$ -- 测试 set @username = '小妹'; call sp_param03(@username); select @user_name;
4. 流程控制-判断
(1)if
语法:
IF search_condition THEN statement_list
[ ELSEIF search_condition THEN statement_list ] ...
[ ELSE statement_list ]
END IF
前置知识点:timestampdiff( unit,exp1,exp2-) 取差值exp2-exp1 差值,单位是 unit
select timestampdiff( year,e.hiredate,now() ) from emp e where e.empno = '7499';
delimiter $$ create procedure sp_hire_if() begin declare result varchar(32); if timestampdiff(year,'2001-01-01',now()) > 40 then set result = '元老'; elseif timestampdiff(year,'2001-01-01',now()) > 35 then set result = '老员工'; else set result = '新手'; end if; select result; end $$
(2)case
语法一:(类比 java 的 switch)
CASE case_value
WHEN when_value THEN statement_list
[ WHEN when_value THEN statement_list ] ...
[ ELSE statement_list ]
END CASE
语法二:
CASE
WHEN search_condition THEN statement_list
[ WHEN search_condition THEN statement_list ] ...
[ ELSE search_condition ]
END CASE
5. 流程控制-循环
(1)loop
语法:
[ begin_label : ] LOOP
statement_list
END IOOP [ end_label ]
注意:
loop 是死循环,需要手动退出循环,可以把 leave 看成 java 中的 break;iterate(继续循环)看成 java 中的 continue。
leave 控制循环的退出
delimiter $$ create procedure sp_flow_loop() begin declare c_index int default 1; num_loop:loop select c_index; if c_index >= 10 then leave num_loop; end if; set c_index = c_index + 1; end loop num_loop; end $$
iterate + leave 控制循环
delimiter $$ create procedure sp_flow_loop02() begin declare c_index int default 1; declare result_str varchar(256) default '1'; cnt:loop set c_index = c_index + 1; set result_str = concat(result_str ,c_index); if c_index < 10 then iterate cnt; end if; leave cnt; end loop cnt; select result_str; end $$
(1)repeat
[ begin_label : ] REPEAT
statement_list
UNTIL search_condition -- 直到。。。为止
END REPEAT [ end_label ]
delimiter $$ create procedure sp_flow_repeat() begin declare c_index int default 1; declare result_str varchar(256) default '1'; count_lab:repeat set c_index = c_index + 1; set result_str = concat(result_str ,c_index); until c_idnex >= 10 end repeat count_lab; select result_str; end $$
(1)while
[ begin_label : ] WHILE search_condition DO
statement_list
END WHILE [ end_label ]
delimiter $$ create procedure sp_flow_while() begin declare c_index int default 1; declare result_str varchar(256) default '1'; while c_idnex < 10 do set c_index = c_index + 1; set result_str = concat(result_str ,c_index); end while; select result_str; end $$
6. 流程控制-退出、继续循环
leave 看成 java 中的 break;iterate(继续循环)看成 java 中的 continue。
7. 游标
类比 jdbc 的 ResultSet
-- 声明语法
DECLARE cursor_name CURSOR FOR select_statement
-- 打开语法
OPEN cursor_name
-- 取值语法
FETCH cursor_name INTO var_name [ ,var_name ] ...
-- 关闭语法
CLOSE cursor_name
需求:按照部门名称查询员工,通过 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; open emp_cursor; c_loop:loop fetch emp_cursor into emp_no ,emp_name ,emp_sal ; if exit_flag != 1 select emp_no ,emp_name ,emp_sal ; -- ITERATE c_loop; else then leave c_loop; end if; end loop c_loop; select @sex_res; close emp_cursor; end $$
注意 :在语法中,变量声明、handler 声明是必须按照先后顺序书写的,否则创建存储过程出错。
8. 存储过程中的 handler
DECLARE handler_action HANDLER
FOR condition_value [ , condition_value ] ...
statement
handler_action:{
CONTINUE
| EXIT
| UNDO
}
condition_value:{
mysql_error_code
| SQLSTATE [ VALUE ] sqlstate_value
| condition_name
| SQLWARING
| NOT FOUND
| SQLEXCEPTION
}
SQLWARING:Shorthand for the class of SQLSTATE values that begin whit '01'(以“01”开头的SQLSTATE值类的缩写)
NOT FOUND:Shorthand for the class of SQLSTATE values that begin whit '02'
SQLEXCEPTION:Shorthand for the class of SQLSTATE values that do not begin whit '00','01',or'02'(不以“00”、“01”、“02”开头的SQLSTATE值类的缩写)
对于游标遍历到尽头时的错误码处理,我们可以使用:
Error number: 1329 ; Symbol:ER_SP_PETCH_NO_DATA; SQLSTATE: 02000 Message: No data - zero rows fetched,selected, or processed