~routine_body:SQL语句, DELIMITER // #将结束符定义为// BEGIN ..... ...... END
delimiter //
create procedure nbaf() begin select from team left join star on team.id = star.team_id union select from team right join star on team.id = star.team_id; end//
create procedure pf(in p_id int) begin select from team left join star on team.id = star.team_id where team.id = p_id union select from team right join star on team.id = star.team_id where team.id = p_id; end//
delimiter ;
call pf();
3.定义条件和处理程序
(1)定义条件:是事先定义程序执行过程中遇到的问题,处理程序定义了在遇到这些问题时应当采取的处理方式,并且保证存储过程中遇到警告或错误时能继续执行。 DECLARE condition_name CONDITION FOR [condition_type];
SQLSTATE[VALUE] sqlstate_value:包含5个字符的字符串错误值 condition_name :错误条件名称 SQLWARNING :匹配所有以01开头的SQLSTATE错误代码 NOT FOUND :匹配所有以02开头的SQLSTATE错误代码 SQLEXCEPTION :匹配所有除01,02开头外的SQLSTATE错误代码 mysql_error_code :匹配数值类型的错误代码
定义处理程序的几种方式
declare continue handler for SQLSTATE '42S02' set @info= 'NO_SUCH_TABLE' ; #info输出
declare continue handler for 1146 set @info= 'NO_SUCH_TABLE' ; #1146,捕获mysql_error_code
declare no_such_table condition for 1146; declare continue handler for NO_SUCH_TABLE set @info= 'ERROR' ; #先定义条件,然后调用
declare exit handler for SQLWARNING set @info= 'ERROR' ;
declare exit handler for NOT FOUND set @info= 'NO_SUCH_TABLE' ;
declare exit handler for SQLEXCEPTION set @info= 'ERROR' ;
4.光标的使用:数据量非常大时使用光标逐条查询
(1)光标的声明:在声明变量、条件后,声明处理程序之后
DECLARE cursor_name CURSOR FOR select_statement
(2)光标的使用 打开光标: OPEN cursor_name; FETCH cursor_name INTO var_name [,var_name].... 关闭光标: CLOSE curse_name
5.控制流程的使用:在编写存储过程中
(1)IF语句: IF expr_condition THEN statement_list [ELSEIF expr_contidion THEN statement_list] [ELSE statement_list] END IF //expr_condition 判断语句 statement_list SQL语句
(2)CASE语句: CASE case_expr WHEN when_value THEN statement_list [ WHEN when_value THEN statement_list] ............ [ELSE statement_list] END CASE;
(3) LOOP语句: [loop_table:] LOOP statement_list END LOOP [loop_tabel];
create procedure east() begin declare ep1 int default 0; declare ep2 int default 7; east_p:LOOP set ep1 = ep1 + 1; if ep1< 4 then select * from team left join star on team.id = star.team_id where team.id = ep1 union select * from team right join star on team.id = star.team_id where team.id = ep1;