Mysql中的存储过程详细教程

参考资料 

https://yq.aliyun.com/articles/604202

http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html

昨天遇到一个需要删除涉及30多张表的记录的问题，经我的boss曹叔指点，采用存储过程来删除。

一、什么是存储过程？

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

二、存储过程的特性

    1.有输入输出参数，可以声明变量

    2.有条件语句 ，if/else ,case ,while 等

    3.使用存储过程，可以实现复杂的逻辑功能。

三、存储过程的缺点

不同数据库，语法差别很大，移植困难，换了数据库，需要重新编写；

不好管理，把过多业务逻辑写在存储过程不好维护，不利于分层管理，容易混乱，一般存储过程适用于个别对性能要求较高的业务，其它的必要性不是很大；

四、存储过程语法

相关概念转自：https://yq.aliyun.com/articles/604202

记录MYSQL存储过程中的关键语法：

DELIMITER //  声明语句结束符，用于区分;  可以自定义结束符号

CREATE PROCEDURE demo_in_parameter(IN p_in int) 声明存储过程  //demo_in_parameter 是名字，可自定义，p_in是参数  int是类型

BEGIN  .... END  存储过程开始和结束符号
SET @ p_in=1  变量赋值

DECLARE l_int int unsigned default 4000000; 变量定义

我编写的存储过程如下所示

DELIMITER //   CREATE DEFINER=`root`@`localhost` PROCEDURE `Cuixk_DeleteOrganizations`(IN orgID CHAR(16))   BEGIN   declare v char(16);   set v=orgID;   delete from OM_Organization where Pk_Organization=v;   delete from AM_App_Access_Control where Fk_Owner=v;   delete from AM_Email_Template where Fk_Owner=v;   delete from AM_Java_Api where Fk_Owner=v;   delete from AM_Java_App_Service where Fk_Owner=v;   delete from AM_Service_Container where fk_owner=v;   delete from AM_Service_Container_Port where Fk_Owner=v;   delete from AM_Sms_Template where Fk_Owner=v;   delete from AM_Web_App_Service where Fk_Owner=v;     delete from Crm_Membership where Fk_Owner=v;   delete from FM_Form where Fk_Owner=v;   delete from FM_Reference where Fk_Owner=v;   delete from FM_Reference_Detail where fk_owner=v;   delete from FM_Rl_Form where Fk_Owner=v;   delete from Folder where Fk_Owner=v;   delete from H_Wfeventlog where Fk_Owner=v;   delete from H_Wfinstance where Fk_Owner=v;   delete from OM_Authoritygroup where Fk_Owner=v;   delete from OM_Authoritygroup_Member where Fk_Owner=v;   delete from OM_Authorization where Fk_Owner=v;     delete from OM_Department where Fk_Owner=v;     delete from OM_Division where Fk_Owner=v;     delete from OM_Job_Assignment where Fk_Owner=v;     delete from OM_Job_Assignment_History where Fk_Owner=v;     delete from OM_Makepage where Fk_Owner=v;     delete from OM_Position where Fk_Owner=v;     delete from OM_Projectrole where Fk_Owner=v;     delete from OM_Projectteam where Fk_Owner=v;     delete from OM_Staff where Fk_Owner=v;      delete from PM_Bt_Wfprocess where Fk_Owner=v;     delete from PM_Rl_Wfprocess where Fk_Owner=v;     delete from R_Wfinstance where Fk_Owner=v;     delete from Sm_Servers where Fk_Owner=v;     delete from Sm_Server_Group where Fk_Owner=v;     delete from WfOfficeCalendar where Fk_Owner=v;   delete from WfOfficeDay where Fk_Owner=v;   delete from WfOfficeHours where Fk_Owner=v;   END   //

注：

（1）这里需要注意的是DELIMITER//和DELIMITER;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

（2）存储过程根据需要可能会有输入、输出、输入输出参数，我这里没有输出参数，入参是 orgId ，char 类型。如果有多个参数用","分割开。

（3）过程体的开始与结束使用BEGIN与END进行标识。

详情可参考我贴的两个连接。