mysql存储过程、存储函数（三）：存储过程

什么是mysql存储例程?
存储例程：是存储在数据库服务器中的一组sql语句，通过在查询中调用一个指定的名称来执行这些sql语句命令.

存储过程(stored procedure)、存储例程(store routine)、存储函数区别？
Mysql存储例程 ： 包含了存储过程和存储函数，它们被统称为存储例程。
存储过程：主要完成在获取记录或插入记录或更新记录或删除记录，即完成select insert delete update等的工作。

存储函数：只完成查询的工作，可接收输入参数并返回一个结果。

1、创建存储过程（CREATE PROCEDURE）：

语法

CREATE PROCEDURE sp_name (parameters)
[characteristics ...] routine_body

其中：

CREATE PROCEDURE为创建存储过程的关键字；

sp_name为存储过程的名称(唯一性，没有所谓的重载方法概念)；

parameters为参数列表；

characteristics指定存储过程的特性(该部分可以省略,即使用默认声明)；

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

名称	说明
LANGUAGE SQL	说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值
[NOT] DETERMINISTIC	指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为 NOT DETERMINISTIC。
{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}	指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句；NO SQL表明子程序不包含SQL语句；READS SQL DATA说明子程序包含读写读数据的SQL语句；MODIFIES SQL DATA表明子程序包含写数据的语句；默认情况下，系统会指定为CONTAINS SQL。
SQL SECURITY {DEFINER | INVOKER}	指明谁有权限来执行。DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER
COMMENT 'string'	注释信息,可以用来描述存储过程或存储函数。

注：
（1）这里需要注意的是DELIMITER//和DELIMITER;两句，DELIMITER是分割符的意思，因为MySQL默认以”;”为分隔 符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当 前段分隔符，这样MySQL才会将”;”当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
（2）存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用”,”分割开。
（3）过程体的开始与结束使用BEGIN与END进行标识。 

示例：

# 存储过程：设置机构的层级路径。
drop procedure if exists setOrgPath2;
delimiter $$
CREATE PROCEDURE setOrgPath2()
BEGIN
DECLARE currentOrgId varchar(60);
DECLARE have boolean default true;
DECLARE cur_path CURSOR FOR SELECT ORG_ID FROM ORG_INFO ;#申明游标，查询incidents
DECLARE CONTINUE HANDLER FOR NOT FOUND SET have=false;
OPEN cur_path;
FETCH cur_path INTO currentOrgId;
while have do
	update org_info w,(
		# 设置当前机构的 path路径
		SELECT  GROUP_CONCAT(_id  ORDER BY lvl DESC SEPARATOR '#')  org_path  FROM (
			SELECT   
				@r AS _id,   
				(SELECT @r := parent_org_id FROM org_info WHERE org_id = _id) AS parent_org_id,   
				@l := @l + 1 AS lvl   
			FROM   
				(SELECT @r := currentOrgId, @l := 0) vars,   
				org_info h   
			WHERE @r <> 0 and @r <> '' and @r IS NOT NULL
		) tmp
	) t SET w.path = t.org_path where w.org_id= currentOrgId;

	FETCH cur_path INTO currentOrgId;
END WHILE;
CLOSE cur_path;
select * from org_info;
END;
$$
delimiter ;

call setOrgPath2();

 

2、创建存储函数(CREATE FUNCTION)

语法：

CREATE FUNCTION func_name(params)
RETURNS type
[characteristics ...] routine_body

其中：

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

func_name ：为存储函数的名称(唯一性，没有所谓的重载方法概念)；

params：为参数列表；

RETURNS type语句 ：表示函数返回数据的类型，可以是mysql中的任意数据类型；

characteristics ：指定存储函数的特性(和存储过程一样)；

routine_body：是存储函数主体。

参数列表：IN、OUT、或INOUT只对PROCEDURE是合法的( FUNCTION中是默认IN参数，所以声明存储函数入参的时候不能声明IN，会报错，因为默认是IN)。

RETURNS子句只能对FUNCTION作指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

示例：

drop function  if exists getparentlist;
delimiter $$
create function getparentlist(orgid varchar(20),separatorStr varchar(10)) returns varchar(1000)   
begin   
    declare pid varchar(100);   					-- 声明单次循环上级机构号 的变量
    declare allpid varchar(1000) default orgid; -- 声明所有上级号的变量   
    while orgid is not null  do   
        set pid =(select parent_org_id as parentid from org_info where org_id = orgid);  
        if pid is not null and pid <> '0' then   
            set allpid = concat(pid, separatorStr, allpid);         
        end if;   
	    set orgid = pid;
    end while;   
    return allpid;  
end;
$$
delimiter ;

-- 调用存储函数，并查询上级机构列表
select getparentlist('100007',',');
select * from org_info where find_in_set(org_id,getparentlist('100007'));