mysql学习笔记之十二（存储过程跟函数）_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 '查询所有雇员的工资'      begin       select 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 '查询某个雇员的工作'       begin        return (         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、在编写存储过程和函数时，需要创建这些数据库对象的权限