????????洢??????????
?????????????????洢????????????????洢??????????????иù???????????ALTER ROUTINE?????????￡?
????DROP{PROCEDURE|FUNCTION} [IF EXISTS] sp_name
???????洢?????????? ???洢??????????????
????SHOW {PROCEDURE|FUNCTION} STATUS [LIKE ‘pattern’]
???????洢???????????????
????SHOW CREATE {PROCEDURE|FUNCTION} sp_name
?????????information_schema.Routines???洢?????????????
?????????????????????????????????????洢???????????????????????information_schema.Routines??????洢????????????????????????????????????
?????????????
?????洢?????????п???????????
?????????????
???????DECLARE?????????????????????????????÷?Χ?????BEGIN…END???У???????????????С?????????????д????????????????????κ???????????檔??????????????????????????????????????????DEFAULT????????
??????????????????????￡?
????DECLAREvar_name[??…] type [DEFAULT value]
???????磬???????DATE????????????????last_month_start:
????DECLARElast_month_start DATE;
????????????
??????????????????????????????????????????SET???????????????????????????????￡?
????SET var_name =expr[??var_name=expr]…
????????????????last_month_start??????????????￡?
????SETlast_month_start = DATE_SUB(CURRENT_DATE()??INTERVAL 1 MONTH);
????????????????????????????????????????????????????????У??????????￡?
????SELECTcol_name[??…] INTO var_name[??…] table_expr
???????3??????????????????????max_salary??
????DELIMITER $$
????CREATE FUNCTIONget_max_salary(param_deptno INT)
????RETURNS INT(7)
????DETERMINISTIC
????READS SQL DATA
????BEGIN
????DECLAREv_max_salary INT(7);
????SELECT IFNULL(MAX(SALARY)??0)INTO v_max_salary
????FROM EMP
????WHEREDEPTNO=param_deptno;
????RETURNv_max_salary;
????END$$
????DELIMITER ;
?????洢?????????е?????????
??????????????????ж?????С???????“?????????????”????????????????????????CONDITION??????????“????”????????о?“??”????????Щ??“????”???????“???????”?????????
???????????
??????С?????????????????????????????????????????
????http://dev.mysql.com/doc/refman/5.6/en/declare-condition.html
????DECLARE condition_name CONDITION FOR condition_value
????condition_value:
????mysql_error_code
????|SQLSTATE [VALUE] sqlstate_value
????DECLARE ...CONDITION????????????????????????????????????????????DECLARE ... HANDLER?н??д????
???????????????????α?cursor????????????handler??????
????condition_value????????????condition_name??????????????????????????????????????????????
????<1>mysql_error_code: ???MySQL???????????
??????????????0??0??????????????????????MySQL?????б?ο???
????SectionB.3?? “ServerError Codes and Messages”
????<2>SQLSTATE [VALUE] sqlstate_value:??????????????????SQL????
????????????“00”????????????“00”???????????????????????????????ο?????
??????????????????α??????????SIGNAL??RESIGNAL????????????????????????????漲???????α??????????в????????????????
???????????????????????????????????????????б?????????????1146??????????????????????????????????1146 ;ER_NO_SUCH_TABLE ;table ‘%s.%s’ does’t exist???????????????磬??????????????????????????е??????1051???????“unknown table”???????????????
??????????????????????????????????????????????
??????????????
????DECLARE handler_action HANDLER FORcondition_value[??…] sp_statement
????handler_ action:
????CONTINUE|EXIT|UNDO
????condition_value:
????SQLSTATE[VALUE] sqlstate_value
????|condition_name
????|SQLWARNING
????|NOT FOUND
????|SQLEXCEPTION
????|mysql_error_code
???????4???????????????????????????????????????????????
????<1>??emp(id??ename??deptno)???????????????????(1??’????’??1)??(1??’????’??1)??д?????????id???????н?????￡?
????DELIMITER $$
????CREATE PROCEDURE insert_emp()
????BEGIN
????SET @x=1;
????INSERT INTO emp(id??ename??deptno) VALUES(1??'????'??1);
????SET @X=2;
????INSERT INTO emp(id??ename??deptno) VALUES(1??'????'??1);
????SET @X=3;
????END $$
????DELIMITER ;
????CALL insert_emp();
????ERROR 1062 (23000): Duplicate entry '1' forkey 'PRIMARY'
????SELECT @X;
????->2
??????????н?????????????е?????emp.ename=?????????????????????????????????е?????????
????<1>??emp(id??ename??deptno)???????????????????(1??’????’??1)??(1??’????’??1)????????????id???????н?????￡?????????????????????
????DELIMITER $$
????CREATE PROCEDURE insert_emp()
????BEGIN
????DECLAREduplicate_entry CONDITION FOR SQLSTATE '23000';
????DECLARECONTINUE HANDLER FOR duplicate_entry
????BEGIN
????SELECT'?????????????';
????END;
????SET@x=1;
????INSERTINTO emp(id??ename??deptno) VALUES(1??'????'??1);
????SET@X=2;
????INSERTINTO emp(id??ename??deptno) VALUES(1??'????'??1);
????SET@X=3;
????END $$
????DELIMITER ;
????CALL insert_emp();
????->?????????????
????SELECT @x;
????->3
??????????????????????????????????????????????????????????д?????????????ж????CONTINUE?????????????????????
????handler_action??MySQL5.6?л?????CONTINUE??EXIT?????CONTINUE??????????????????EXIT???????????UNDO????????