mysql存储过程和函数知识点

1、创建存储过程

        语法格式：

CREATE PROCEDURE sp_name ([proc_paramenter]) [characteristics] routine_body

        CREATE PROCEDURE：用来创建存储过程的关键字；

        sp_name：存储过程的名称；

        proc_parameter：指定存储过程的参数列表，形式：[IN|OUT|INOUT] param_name type

        IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；

        param_name表示参数名称；

        type表示参数类型，该类型可以是mysql数据库中任意类型。

        characteristics：指定存储过程的特性。

        routine_body：是SQL代码内容，可以用BEGIN......END来表示SQL代码的开始和结束。

        DELIMITER //：语句作用是将MySQL的结束符设置为//，因为MySQL默认的语句结束符合为';'，为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER 改变存储过程的结束符，并以“END //”结束存储过程。存储过程定义完毕后在使用“DELIMITER ;”恢复默认结束符。也可以指定其他符号为结束符。

2、创建存储函数

        语法格式：

CREATE FUNCTION func_name([func_parameter]) RETURNS type [characteristics] routine_body

        CREATE FUNCTION ：用来创建存储函数的关键字；

        func_name：存储函数的名称；

        func_parameter：指定存储函数的参数列表，形式：[IN|OUT|INOUT] param_name type

        IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；

        param_name表示参数名称；

        type表示参数类型，该类型可以是mysql数据库中任意类型。

        RETURNS type：表示函数返回数据类型。

        characteristics：指定存储函数的特性。

        routine_body：是SQL代码内容，可以用BEGIN......END来表示SQL代码的开始和结束。

3、变量的使用

        3.1、定义变量

                语法格式：

DECLARE var_name[,var_name]... data_type [DEFAULT value];

                var_name：为局部变量的名称。

                DEFAULT value：给变量提供默认值，值除了可以被声明一个常数外，还可以被指定为一个表达式。如果没有该子句，初始值为NULL。

        3.2、为变量赋值

                1）、语法格式：

SET var_name=expr[,var_name=expr]...;

                2）、MYSQL中还可以通过SELECT...INTO为一个或多个变量赋值：

SELECT col_name[,col_name,....] INTO var_name[,var_name,....] table_expr;

                        SELECT语法把选定的列直接存储到对应位置的变量。

                        col_name表示字段名称。

                        var_name表示定义的变量名称。

                        table_expr表示查询条件表达式，包括表名称和WHERE子句。

4、定义条件和处理程序

        4.1、定义条件

DECLARE condition_name CONDITION FOR [condition_type];

                condition_type：SQLSTATE [VALUE] sqlstate_value | mysql_error_code;

                condition_name：参数表示条件名称；

                condition_type：表示条件的类型；

                sqlstate_value与mysql_error_code都可以表示mysql的错误，sqlstate_value表示长度为5的字符串类型错误代码，mysql_error_code为数值类型错误代码。

        4.2、定义处理程序

                语法格式：

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement;

                handler_type：CONTINUE|EXIT|UNDO

                condition_value：SQLSTATE [VALUE] sqlsate_value

|                condition_name|SQLWARNING|NOTFOUND|SQLEXCEPTION|mysql_error_code

5、光标的使用（MYSQL中光标只能在存储过程和函数中使用）

        5.1、声明光标

DECLARE cursor_name CURSOR FOR select_statement;

                cursor_name：光标的名称。

                select_statement：表示SELECT语句的内容，返回一个用于创建光标的结果集。

        5.2、打开光标

OPEN cursor_name{光标名称}

        5.3、使用光标

FETCH cursor_name INTO var_name[,var_name]...{参数名称}；

                var_name必须在声明光标之前就定义好。

        5.4、关闭光标

CLOSE cursor_name{光标名称}

6、流程控制的使用

        6.1、IF语句

IF expr_condition THEN statement_list

[ELSEIF expr_condition THEN statement_list]

[ELSE statement_list]

END IF

        6.2、CASE语句

                6.2.1、CASE case_expr

WHEN when_value THEN statement_list

[WHEN when_value THEN statement_list]

[ELSE statement_list]

END CASE

                6.2.2、CASE

WHEN expr_condition THEN statement_list

[WHEN expr_condition THEN statement_list]

[ELSE statement_list]

END CASE

        6.3、LOOP语句

[loop_label:] LOOP

statement_list

END LOOP [loop_label]

                loop_label：表示LOOP语句的标注名称，该参数可以省略。

                statement_list：需要循环执行的语句。

        6.4、LEAVE语句

                LEAVE语句用来退出任何被标注的流程控制构造。

LEAVE label;

        6.5、ITERATE语句

ITERATE label;

                ITERATE 语句将执行顺序转到语句段开头处，只可以出现在LOOP、REPEAT和WHILE语句内。意思为“再次循环”，label参数表示循环的标志，ITERATE语句必须跟在循环标志前面。

        6.6、REPEAT语句

                REPEAT语句创建一个带条件判断的循环过程，每次执行完毕之后，会对条件表达式j进行判断，如果表达式为真，则循环结束；否则重复执行循环中的语句。

[repeat_label:] REPEAT

statement_list

UNTIL expr_condition

END REPEAT [repeat_label]

        6.7、WHILE语句

                WHILE语句创建一个带条件判断的循环过程，与REPEAT不同，WHILE在执行语句执行时，先对z指定的表达式进行判断，如果为真，则执行循环内的语句，否则推出循环。

[while_label:] WHILE expr_condition DO

statement_list

END WHILE [while_label]

7、调用存储过程和函数

        7.1、调用存储过程

CALL sp_name([parameter[,...]])

        7.2、调用存储函数

                调用存储函数和MYSQL内部函数调用方法一样。

8、查看存储过程和函数

        8.1、SHOW STATUS 语句查看存储过程和存储函数

SHOW {PROCEDURE|FUNCTION} STATUS {LIKE 'pattern'}

        8.2、SHOW CREATE 语句查看存储过程和函数定义

SHOW CREATE {PROCEDUR|FUNCTION} sp_name

        8.3、从infomation_schema.Routines 表中查看存储过程和函数的信息

SELECT * FROM infomation_schema.Routines WHERE ROUTINE_NAME='sp_name'

9、修改存储过程和函数

ALTER {PROCEDURE|FUNCTION} sp_name {characteristic}

10、删除存储过程和函数

DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name

11、存储过程和存储函数区别

        1）、函数必须有返回值，而过程可以没有。
        2）、函数可以在SELECT语句中使用，而过程不能。
        3）、函数不能修改表的数据（除非使用MODIFY或ALTER权限），而过程可以。

12、存储过程和存储函数共同点

        1）、都可以提高代码复用性。
        2）、都可以提高数据库操作的效率。
        3）、都可以通过参数传递数据。
        4）、都可以包含事务控制。

13、注意事项

        1）、存储过程和函数的修改可能影响到依赖它们的应用程序，因此在修改时需要谨慎。
        2）、在执行存储过程或函数时，如果发生错误，可能会回滚整个事务，除非在过程中显式使用BEGIN和COMMIT来管理事务。