1.存储过程概述1.1理解含义:存储过程的英文是Stored Procedure.他的思想很简单,就是一组经过预先编译的SQL语句的封装.执行过程:存储过程预先存储在MySQL服务器上,需要执行的时候,客户端只需要向服务器端发出调用存储过程的命令,服务器端就可以把预先存储好的这一系列SQL语句全部执行好处:1.简化操作,提高了sql语句的重用性,减少了开发程序员的压力2.减少操作过程中的失误,提高效率3.减少网络传输量(客户端不需要把所有SQL语句通过网络发给服务器)4.减少了SQL语句暴露在网上的风险,也提高了数据查询的安全性和视图,函数的对比它和视图有着同样的优点,清晰,安全,还可以减少网络传输量,不过他和视图不同,视图是虚拟表,通常不对底层数据表直接操作,而存储过程时程序化的SQL,可以直接操作底层数据表,相比于面向集合的操作方式,能够实现一些更复杂的数据处理.一旦存储过程被创建出来,使用它就像使用函数一样简单,我们直接通过调用存储过程名即可,相较于函数,存储过程是没有返回值的1.2分类存储过程的参数类型可以是IN,OUT和INOUT,根据这点分类如下1.没有参数(无参数无返回)2.仅仅带IN类型(有参数无返回)3.仅仅带OUT类型(无参数有返回)4.即带IN又带OUT(有参数有返回)5.带INOUT(有参数有返回)注意:IN,OUT,INOUT都可以在一个存储过程中带多个2.存储过程的使用2.1语法分析CREATE PROCEDURE 存储过程名(IN | OUT | INOUT 参数名 参数类型,.....)[characteristics ...]BEGIN存储过程体END类似于JAVA的方法说明:1.参数前面的符号的意思IN:当前参数为输入参数,也就是表示入参存储过程只是读取这个参数的值,如果没有定义参数种类,默认就是IN,表示输入参数OUT:当前参数为输出参数,也就是表示出参执行完成之后,调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了.INOUT:当前参数既可以为输入参数,也可以为输出参数2.形参类型可以是MySQL数据库中的任意类型3.characteristics表示创建存储过程中对存储过程的约束条件,其取值信息如下LANGUAGE SQL | [NOT] DETERMINISTIC | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | SQL SECURITY {DEFINER| INVOKER} | COMMENT stringLANGUAGE SQL:说明存储过程执行体是由SQL语句组成的,当前系统支持的语言为SQL[NOT] DETERMINISTIC指明存储过程执行的结果是否确定。DETERMINISTIC 表示结果是确定的。每次执行存储过程时相同的输入会得到相同的输出。NOT DETERMINISTIC 表示结果是不确定的相同的输入可能得到不同的输出。如果没有指定任意一个值默认为 NOT DETERMINISTIC。{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}指明子程序使用 SQL 语句的限制。CONTAINS SQL 表示当前存储过程的子程序包含 SQL 语句但是并不包含读写数据的 SQL 语句NO SQL 表示当前存储过程的子程序中不包含任何 SQL 语句READS SQL DATA 表示当前存储过程的子程序中包含读数据的 SQL 语句MODIFIES SQL DATA 表示当前存储过程的子程序中包含写数据的 SQL 语句。默认情况下系统会指定为 CONTAINS SQL。SQL SECURITY {DEFINER | INVOKER}执行当前存储过程的权限即指明哪些用户能够执行当前存储过程。 DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程 INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。 如果没有设置相关的值则 MySQL 默认指定值为 DEFINER。COMMENTstring注释信息可以用来描述存储过程。4.存储过程体中可以有多条SQL语句,如果仅仅一条SQL语句,则可以先省略BEGIN和END,编写存储过程并不是一件简单的事情,可能存储过程中需要复杂的SQL语句5.需要设置新的结束标记DELIMITER 新的结束标记因为MySQL默认的语句结束符号为分号,为了避免与存储过程中SQL语句结束符相冲突,需要使用DELIMITER改变存储过程的结束符2.2代码举例不带IN也不带OUT的DELIMITER $ CREATE PROCEDURE select_all_data() BEGIN SELECT * FROM emps; END $存储过程的调用CALL select_all_data();带OUT参数的DELIMITER // CREATE PROCEDURE show_min_salary(OUT ms DOUBLE) BEGIN SELECT MIN(salary) INTO ms FROM emps; END // CALL show_min_salary(ms); SELECT ms;带IN参数的DELIMITER // CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20)) BEGIN SELECT salary FROM emps WHERE last_name empname; END // CALL show_someone_salary(Abel);带IN和OUT参数的DELIMITER // CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE) BEGIN SELECT salary INTO empsalary FROM emps WHERE last_name empname; END // CALL show_someone_salary2(Abel,ms); SELECT ms;带INOUT参数的DELIMITER // CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25)) BEGIN SELECT last_name INTO empname FROM emps WHERE employee_id (SELECT manager_id FROM emps WHERE last_name empname); END // SET empname Abel; CALL show_mgr_name(empname); SELECT empname;2.3如何调试在MySQL中,存储过程不像普通的编程语言那样有专门的集成开发环境,因此,你可以通过SELECT语句,把程序执行的中间结果查询出来,来调试一个SQL语句的正确性,调试成功之后,把SELECT语句后移到下一个SQL语句之后,再调试下一个SQL语句,这样逐步推进,就可以完成对存储过程中所有操作的调试了,当然,也可以把存储过程中的SQL语句复制出来,逐段单独调试.3.存储函数的使用3.1语法分析语法格式:CREATE FUNCTION 函数名(参数名 参数类型,.......)RETURNS 返回值类型[characteristics ...]BEGIN函数体 #函数体重肯定有RETURN语句END说明:1.参数列表:指定参数为IN,OUT,INOUT只对PROCEDURE是合法的,FUNCTION中总是默认为IN参数2.RETURNS type语句表示函数返回数据的类型RETURNS字句只能对FUNCTION做指定,对于函数而言这是强制的,它用来指定函数的返回类型,而且函数体必须包含一个RETURN value语句.3.characteristic创建函数时指定对函数的约束,取值与创建存储过程时相同4.函数体也可以用BEGIN...END来表示SQL代码的开始和结束,如果函数体只有一条语句,也可以省略BEGIN...END3.2调用存储函数在MySQL中,存储函数的使用方法与MySQL内部函数的使用方法是一样的.换言之,用户自己定义的存储函数与MySQL内部函数是一个性质的,区别在于,存储函数是用户自己定义的,而内部函数是MySQL的开发者定义的.SELECT 函数名(实参列表)3.3代码举例DELIMITER // CREATE FUNCTION email_by_id( p_employee_id INT ) RETURNS VARCHAR(25) READS SQL DATA BEGIN DECLARE v_email VARCHAR(25); SELECT email INTO v_email FROM emps WHERE employee_id p_employee_id; RETURN v_email; END // DELIMITER ;3.4对比存储函数和存储过程关键字调用语法返回值应用场景存储过程PROCEDURECALL存储过程()理解为有0个或多个一般用于更新存储函数FUNCTIONSELECT函数()只能是一个一般用于查询结果为一个值并返回时此外,存储函数可以放在查询语句中使用,存储过程不行,反之存储过程的功能更加强大,包括能够执行对表的操作(比如创建表,删除表等)和事务操作,这些功能是存储函数不具备的.4.存储过程和函数的查看,修改,删除4.1查看创建完之后,怎么知道我们创建的存储过程,存储函数是否成功了呢?MySQL存储了存储过程和函数的状态信息,用户可以使用SHOW STATUS语句或SHOW CREATE语句来查看,也可直接从系统的information_schema数据库中查询,这里介绍3种方法1.使用SHOW CREATE语句查看存储过程和函数的创建信息SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名举例:SHOW CREATE FUNCTION email_by_name;2.使用SHOW STATUS语句查看存储过程和函数的状态信息SHOW {PROCEDURE | FUNCTION} STATUS [LIKE pattern]这个语句返回子程序的特征,如数据库,名字,类型,创建者以及创建和修改日期[LIKE pattern] 匹配存储过程或函数的名称,可以省略,当省略不写时,会列出MySQL数据库中存在的所有存储过程或函数的信息举例:SHOW PROCEDURE STATUS LIKE show_max_salary;3.从information_schema.Routines表中查看存储过程和函数的信息MySQL中存储过程和函数的信息存储在Information_schema数据库下的Routines表中,可以通过查询该表的记录来查询存储过程和函数的信息,其基本语法形式如下:SELECT * FROM information_schema.RoutinesWHERE ROUTINE_NAME 存储过程或函数的名 [AND ROUTINE_TYPE {PROCEDURE| FUNCTION}];4.2修改修改存储过程或函数,不影响存储过程或函数功能,只能修改相关特性.使用ALTER语句实现ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名[characteristic...]4.3删除删除存储过程和函数.可以使用DROP语句,其语法结构如下DROP {PROCEDURE | FUNCTION} [IF EXIST] 存储过程或函数名5.关于存储过程使用的争议尽管存储过程有诸多优点,但是对于存储过程的使用,一直都存在着很多争议,比如有些公司对于大型项目要求使用存储过程,而有些公司在手册中明确禁止使用存储过程,为什么这些公司对存储过程的使用需求差别这么大呢?5.1优点1.存储过程可以一次编译多次使用,存储过程只在创建时进行编译,之后的使用都不需要重新编译,这就提升了SQL的执行效率2.可以减少开发工作量.将代码封装成模块,实际上是编程的核心思想之一,这样可以把复杂的问题拆解成不同的模块,然后模块之间可以重复使用,在减少开发工作量的同时,还能保证代码的结构清晰3.存储过程的安全性强.我们在设定存储过程的时候可以设置对用户的使用权限,这样就和视图一样具有较强的安全性.4.可以减少网络传输量,因为代码封装到存储过程中,每次使用只需要调用存储过程即可,这样就减少了网络传输量5.良好的封装性,在进行相对复杂的数据库操作时,原本需要使用一条一条的SQL语句,可能要连接多次数据库才能完成,现在变成了一次存储过程,只需要链接一次即可.5.2缺点基于上面这些优点,不少大公司都要求大型项目使用存储过程,比如微软,IBM等公司,但是国内的阿里并不推荐开发人员使用存储过程1.可移植性差.存储过程不能跨数据库移植,比如在MySQL,Oracle和SQL Servcer里编写的存储过程,在换成其他数据库时都需要重新编写2.调试困难,只有少数DBMS支持存储过程的调试,对于复杂的存储过程来说,开发和维护都不容易,虽然有一些第三方工具可以对存储过程进行调试,但要收费3.存储过程的版本管理很困难,比如数据表索引发生变化了,可能会导致存储过程失效,我们在开发软件的时候往往需要进行版本管理,但是存储过程本身没有版本控制,版本迭代更新的时候很麻烦4.它不适合高并发的场景,高并发场景需要减少数据库压力,有时数据库会采用分库分表的方式,而且对可扩展性要求很高,在这种情况下,存储过程会变得难以维护,增加数据库的压力,显然就不适用了 MySQL-存储过程与函数
1.存储过程概述1.1理解含义:存储过程的英文是Stored Procedure.他的思想很简单,就是一组经过预先编译的SQL语句的封装.执行过程:存储过程预先存储在MySQL服务器上,需要执行的时候,客户端只需要向服务器端发出调用存储过程的命令,服务器端就可以把预先存储好的这一系列SQL语句全部执行好处:1.简化操作,提高了sql语句的重用性,减少了开发程序员的压力2.减少操作过程中的失误,提高效率3.减少网络传输量(客户端不需要把所有SQL语句通过网络发给服务器)4.减少了SQL语句暴露在网上的风险,也提高了数据查询的安全性和视图,函数的对比它和视图有着同样的优点,清晰,安全,还可以减少网络传输量,不过他和视图不同,视图是虚拟表,通常不对底层数据表直接操作,而存储过程时程序化的SQL,可以直接操作底层数据表,相比于面向集合的操作方式,能够实现一些更复杂的数据处理.一旦存储过程被创建出来,使用它就像使用函数一样简单,我们直接通过调用存储过程名即可,相较于函数,存储过程是没有返回值的1.2分类存储过程的参数类型可以是IN,OUT和INOUT,根据这点分类如下1.没有参数(无参数无返回)2.仅仅带IN类型(有参数无返回)3.仅仅带OUT类型(无参数有返回)4.即带IN又带OUT(有参数有返回)5.带INOUT(有参数有返回)注意:IN,OUT,INOUT都可以在一个存储过程中带多个2.存储过程的使用2.1语法分析CREATE PROCEDURE 存储过程名(IN | OUT | INOUT 参数名 参数类型,.....)[characteristics ...]BEGIN存储过程体END类似于JAVA的方法说明:1.参数前面的符号的意思IN:当前参数为输入参数,也就是表示入参存储过程只是读取这个参数的值,如果没有定义参数种类,默认就是IN,表示输入参数OUT:当前参数为输出参数,也就是表示出参执行完成之后,调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了.INOUT:当前参数既可以为输入参数,也可以为输出参数2.形参类型可以是MySQL数据库中的任意类型3.characteristics表示创建存储过程中对存储过程的约束条件,其取值信息如下LANGUAGE SQL | [NOT] DETERMINISTIC | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | SQL SECURITY {DEFINER| INVOKER} | COMMENT stringLANGUAGE SQL:说明存储过程执行体是由SQL语句组成的,当前系统支持的语言为SQL[NOT] DETERMINISTIC指明存储过程执行的结果是否确定。DETERMINISTIC 表示结果是确定的。每次执行存储过程时相同的输入会得到相同的输出。NOT DETERMINISTIC 表示结果是不确定的相同的输入可能得到不同的输出。如果没有指定任意一个值默认为 NOT DETERMINISTIC。{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}指明子程序使用 SQL 语句的限制。CONTAINS SQL 表示当前存储过程的子程序包含 SQL 语句但是并不包含读写数据的 SQL 语句NO SQL 表示当前存储过程的子程序中不包含任何 SQL 语句READS SQL DATA 表示当前存储过程的子程序中包含读数据的 SQL 语句MODIFIES SQL DATA 表示当前存储过程的子程序中包含写数据的 SQL 语句。默认情况下系统会指定为 CONTAINS SQL。SQL SECURITY {DEFINER | INVOKER}执行当前存储过程的权限即指明哪些用户能够执行当前存储过程。 DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程 INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。 如果没有设置相关的值则 MySQL 默认指定值为 DEFINER。COMMENTstring注释信息可以用来描述存储过程。4.存储过程体中可以有多条SQL语句,如果仅仅一条SQL语句,则可以先省略BEGIN和END,编写存储过程并不是一件简单的事情,可能存储过程中需要复杂的SQL语句5.需要设置新的结束标记DELIMITER 新的结束标记因为MySQL默认的语句结束符号为分号,为了避免与存储过程中SQL语句结束符相冲突,需要使用DELIMITER改变存储过程的结束符2.2代码举例不带IN也不带OUT的DELIMITER $ CREATE PROCEDURE select_all_data() BEGIN SELECT * FROM emps; END $存储过程的调用CALL select_all_data();带OUT参数的DELIMITER // CREATE PROCEDURE show_min_salary(OUT ms DOUBLE) BEGIN SELECT MIN(salary) INTO ms FROM emps; END // CALL show_min_salary(ms); SELECT ms;带IN参数的DELIMITER // CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20)) BEGIN SELECT salary FROM emps WHERE last_name empname; END // CALL show_someone_salary(Abel);带IN和OUT参数的DELIMITER // CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE) BEGIN SELECT salary INTO empsalary FROM emps WHERE last_name empname; END // CALL show_someone_salary2(Abel,ms); SELECT ms;带INOUT参数的DELIMITER // CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25)) BEGIN SELECT last_name INTO empname FROM emps WHERE employee_id (SELECT manager_id FROM emps WHERE last_name empname); END // SET empname Abel; CALL show_mgr_name(empname); SELECT empname;2.3如何调试在MySQL中,存储过程不像普通的编程语言那样有专门的集成开发环境,因此,你可以通过SELECT语句,把程序执行的中间结果查询出来,来调试一个SQL语句的正确性,调试成功之后,把SELECT语句后移到下一个SQL语句之后,再调试下一个SQL语句,这样逐步推进,就可以完成对存储过程中所有操作的调试了,当然,也可以把存储过程中的SQL语句复制出来,逐段单独调试.3.存储函数的使用3.1语法分析语法格式:CREATE FUNCTION 函数名(参数名 参数类型,.......)RETURNS 返回值类型[characteristics ...]BEGIN函数体 #函数体重肯定有RETURN语句END说明:1.参数列表:指定参数为IN,OUT,INOUT只对PROCEDURE是合法的,FUNCTION中总是默认为IN参数2.RETURNS type语句表示函数返回数据的类型RETURNS字句只能对FUNCTION做指定,对于函数而言这是强制的,它用来指定函数的返回类型,而且函数体必须包含一个RETURN value语句.3.characteristic创建函数时指定对函数的约束,取值与创建存储过程时相同4.函数体也可以用BEGIN...END来表示SQL代码的开始和结束,如果函数体只有一条语句,也可以省略BEGIN...END3.2调用存储函数在MySQL中,存储函数的使用方法与MySQL内部函数的使用方法是一样的.换言之,用户自己定义的存储函数与MySQL内部函数是一个性质的,区别在于,存储函数是用户自己定义的,而内部函数是MySQL的开发者定义的.SELECT 函数名(实参列表)3.3代码举例DELIMITER // CREATE FUNCTION email_by_id( p_employee_id INT ) RETURNS VARCHAR(25) READS SQL DATA BEGIN DECLARE v_email VARCHAR(25); SELECT email INTO v_email FROM emps WHERE employee_id p_employee_id; RETURN v_email; END // DELIMITER ;3.4对比存储函数和存储过程关键字调用语法返回值应用场景存储过程PROCEDURECALL存储过程()理解为有0个或多个一般用于更新存储函数FUNCTIONSELECT函数()只能是一个一般用于查询结果为一个值并返回时此外,存储函数可以放在查询语句中使用,存储过程不行,反之存储过程的功能更加强大,包括能够执行对表的操作(比如创建表,删除表等)和事务操作,这些功能是存储函数不具备的.4.存储过程和函数的查看,修改,删除4.1查看创建完之后,怎么知道我们创建的存储过程,存储函数是否成功了呢?MySQL存储了存储过程和函数的状态信息,用户可以使用SHOW STATUS语句或SHOW CREATE语句来查看,也可直接从系统的information_schema数据库中查询,这里介绍3种方法1.使用SHOW CREATE语句查看存储过程和函数的创建信息SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名举例:SHOW CREATE FUNCTION email_by_name;2.使用SHOW STATUS语句查看存储过程和函数的状态信息SHOW {PROCEDURE | FUNCTION} STATUS [LIKE pattern]这个语句返回子程序的特征,如数据库,名字,类型,创建者以及创建和修改日期[LIKE pattern] 匹配存储过程或函数的名称,可以省略,当省略不写时,会列出MySQL数据库中存在的所有存储过程或函数的信息举例:SHOW PROCEDURE STATUS LIKE show_max_salary;3.从information_schema.Routines表中查看存储过程和函数的信息MySQL中存储过程和函数的信息存储在Information_schema数据库下的Routines表中,可以通过查询该表的记录来查询存储过程和函数的信息,其基本语法形式如下:SELECT * FROM information_schema.RoutinesWHERE ROUTINE_NAME 存储过程或函数的名 [AND ROUTINE_TYPE {PROCEDURE| FUNCTION}];4.2修改修改存储过程或函数,不影响存储过程或函数功能,只能修改相关特性.使用ALTER语句实现ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名[characteristic...]4.3删除删除存储过程和函数.可以使用DROP语句,其语法结构如下DROP {PROCEDURE | FUNCTION} [IF EXIST] 存储过程或函数名5.关于存储过程使用的争议尽管存储过程有诸多优点,但是对于存储过程的使用,一直都存在着很多争议,比如有些公司对于大型项目要求使用存储过程,而有些公司在手册中明确禁止使用存储过程,为什么这些公司对存储过程的使用需求差别这么大呢?5.1优点1.存储过程可以一次编译多次使用,存储过程只在创建时进行编译,之后的使用都不需要重新编译,这就提升了SQL的执行效率2.可以减少开发工作量.将代码封装成模块,实际上是编程的核心思想之一,这样可以把复杂的问题拆解成不同的模块,然后模块之间可以重复使用,在减少开发工作量的同时,还能保证代码的结构清晰3.存储过程的安全性强.我们在设定存储过程的时候可以设置对用户的使用权限,这样就和视图一样具有较强的安全性.4.可以减少网络传输量,因为代码封装到存储过程中,每次使用只需要调用存储过程即可,这样就减少了网络传输量5.良好的封装性,在进行相对复杂的数据库操作时,原本需要使用一条一条的SQL语句,可能要连接多次数据库才能完成,现在变成了一次存储过程,只需要链接一次即可.5.2缺点基于上面这些优点,不少大公司都要求大型项目使用存储过程,比如微软,IBM等公司,但是国内的阿里并不推荐开发人员使用存储过程1.可移植性差.存储过程不能跨数据库移植,比如在MySQL,Oracle和SQL Servcer里编写的存储过程,在换成其他数据库时都需要重新编写2.调试困难,只有少数DBMS支持存储过程的调试,对于复杂的存储过程来说,开发和维护都不容易,虽然有一些第三方工具可以对存储过程进行调试,但要收费3.存储过程的版本管理很困难,比如数据表索引发生变化了,可能会导致存储过程失效,我们在开发软件的时候往往需要进行版本管理,但是存储过程本身没有版本控制,版本迭代更新的时候很麻烦4.它不适合高并发的场景,高并发场景需要减少数据库压力,有时数据库会采用分库分表的方式,而且对可扩展性要求很高,在这种情况下,存储过程会变得难以维护,增加数据库的压力,显然就不适用了