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'));

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值