mysql学习笔记之十二（存储过程和函数）

     存储过程和函数，一种数据库对象，用来实现将一组关于表操作的SQL语句代码当做一个整体来执行，也是与数据库对象表关联最紧密的数据库对象。在数据库系统中，当调用存储过程和函数时，则会执行这些对象中所设置的sql语句组从而实现相应的功能。一、存储过程和函数的相关概念    针对表的一个完整操作往往不是单条sql语句就可以实现，而是需要一组sql语句来实现。    例：        为了购买商品的订单处理：        （1）在生成订单之前，首先需要查看商品库存中是否有相应的商品        （2）如果库存中存在相应的商品，按着需要预定商品以便不将该商品卖给别人，并且删除库存数量以反映正确的库存量。        （3）如果商品库存中不存在相应的商品，就需要向供应商订货。    在具体应用中，一个完整的操作会包含多条sql语句，在执行过程中需要根据前面sql语句的执行结果有选择的执行后面的sql。为了解决该问题，mysql提供了数据库对象存储过程和函数。    存储过程和函数实际上就是事先经过编译并存储在数据库夅的一段sql语句集合。二、存储过程基本操作    创建        create procedure procedure_name([procedure_paramiter[,...]])                [characteristic...] routine_body        procedure_name:存储过程名称        procedure_paramter:存储过程参数        characteristic：存储过程特性        routine_body:存储过程的sql语法代码，可以使用begin...end来标志sql语句的开始和结束        注意：推崇的过程命名为procedure_xxx或者proce_xxx;        procedure_paramter中每一个参数的语法形式：            [IN|OUT|INOUT] paramter_name type        上述语句：每个参数有3部分组成，分别为输入输出类型，参数名，参数类型。        characteristic的取值：            LANGUAGE SQL                                |[NOT] DETERMINISTIC            |{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}            |SQL SECURITY {DEFINER|INVOKER}            |COMMENT 'string'        language sql :                存储过程的routine_body部分由sql语句组成。为mysql所默认的语句        [not]deterministic :                表示存储过程的执行结果是否确定。deterministic表示执行结果是确定的，即每次执行存储过程时，如果输入相同的参数将得到相同的输出；not deterministic，执行结果不确定，即相同的输入可能得到不同的输出，默认值为deterministic。        {contains sql|no sql|reads sql data|modifies sql data}:            contains sql : 包含sql语句            no sql ：不包含sql            reads sql data：包含读数据的语句            modifies sql data：包含写数据的语句            默认为contains sql        sql security {definer|invoke}            设置谁又权限来执行，如果值为definer,表示只有定义者自己才能够执行；如果值为invoker表示调用着可以执行。默认为definer        comment 'string'            表示注释语句        例：创建查询所有雇员工资的存储过程。            delimiter $$create procedure proce_employee_sale()comment '查询所有雇员的工资'beginselect sale from t_employee;end $$            delimiter;      查看        show procedure status \G        show create  procedure proce_name\G    修改        alter procedure procedure_name            [characteristic...]        修改时characteristic的取值为            |{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}            |SQL SECURITY {DEFINER|INVOKER}            |COMMENT 'string'    删除        drop procedure proce_name三、函数的基本操作    创建        create function function_name([function_paramter[,...]])            [characteristic...] routine_body            routine_body 可以使用begin_end来标志sql语句的开始和结束        例：创建某个雇员工资的函数            delimiter $$create function func_employee_sale(empno int)returns double(10,2)comment '查询某个雇员的工作'beginreturn (select sale from t_employee where t_employee.empno=empno);end$$            delimiter ;    查看        show function status\G        show create function func_name\G    修改        alter function function_name            [characteristic...]        修改时characteristic的取值为            |{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}            |SQL SECURITY {DEFINER|INVOKER}            |COMMENT 'string'       删除        drop function func_name四、存储过程和函数的表达式    操作变量        声明变量            declare var_name[,...] type [default value]            不指定default则为null            例：                declare employee_sale int default 1000        赋值变量            set var_name=expr[,...]            expr是关于变量的赋值表达式            select field_name[,...] into var_name[,...]                from table_name                    where condition            利用查询结果给参数赋值            注意：当将查询结果赋值给变量，该查询语句的返回结果只能是单行。            例：                declare employee_sale int default 1000                set employee_sale=3500;                select sale into employee_sale                    from t_employee                        where empno=7566;    操作条件        定义条件            通过declare来定义            declare condition_name condition for condition_value                condition_value:                    sqlstate[value] sqlstate_value|mysql_error_code            condition_name 表示所要定义的条件名称            condition_value 用来实现设置条件的类型            sqlstate_value  和mysql_error_code :用来设置条件的错误        定义处理程序            declare handler_type handler for condition_value[,...] sp_statement            handler_type:                continue                |exit                |undo            condition_value:                sqlstate[value] sqlstate_value                |condition_name                |sqlwarning                |not found                |sqlexception                |mysql_error_code    使用光标        声明光标            declare cursor_name cursor for select_statement;        打开光标            open cursor_name        使用光标            fetch cursor_name into var_name [,var_name]...        关闭光标            close cursor_name        例：            declare cursor_employee cursor for select sale from t_employee;             open cursor_employee            fetch cursor_employee into employee_sale;            close cursor_employee        注意：            在具体使用光标时，光标必须在处理程序之前且在变量和条件之后声明，并且最后一定要关闭光标。    使用流程控制        条件控制            通过if，case来实现条件控制语句，if语句具体进行条件控制时，根据是否满足条件，执行不同的语句；而对于case语句则可以实现更复杂的条件控制。            IF search_condition THEN statement_list                [ELSEIF search_condition THEN statement_list]...                [ELSE search_condition]            END IF            CASE case_value                WHEN when_value THEN statement_list                [WHEN when_value THEN statement_list]                [ELSE statement_list]            END CASE        循环控制            [begin_label:]                 LOOP                    statement_list                END LOOP             [end_label]            begin_label和end_label分别表示循环开始和结束，这两个标志必须相同，并且可以省略            对于循环语句，如果想退出正在执行的循环体，可以使用关键字leave                LEAVE label            [begin_label:]                WHILE search_condition DO                    statement_list                END WHILE            [end_label]            [begin_label:]                REPEAT search_condition DO                    statement_list                END REPEAT    通过上述介绍，了解了存储过程和函数的概念和定义，但是对于存储过程和函数的具体使用还是模模糊糊的。    存储过程和函数的区别：函数必须有返回值，而存储过程没有。存储过程的参数类型远远多于函数参数类型。    存储过程和函数的优缺点。    优点：             1、存储过程和函数允许标准组件式编程，提高了sql语句的重用性，共享性，和可移植性        2、存储过程和函数能够实现较快的执行速度，能够减少网络流量        3、存储过程和函数可以被作为一种安全机制来利用    缺点：        1、存储过程和函数的编写比单条sql语句复杂，需要用户具有更高的技能和更复杂的经验        2、在编写存储过程和函数时，需要创建这些数据库对象的权限