八、存储过程与自定义函数

存储程序可以分为存储过程和函数。在MySQL中，创建存储过程和函数使用的语句分别是CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用（引用函数名），也能返回标量值。存储过程也可以调用其他存储过程。

1>存储过程：

创建存储过程，需要使用CREATE PROCEDURE命令。语法如下：

CREATE PROCEDURE name(param)
	BEGIN
		body;
	END$$

name：代表参数的名字，param:参数。参数有 in|out|inout类型。in:输入参数，out:输出参数， inout:输入输出参数。

-- 查找所有的部门数量
DELIMITER $$
	CREATE PROCEDURE demo()
	BEGIN
		SELECT COUNT(1) FROM depts;
	END$$
DELIMITER ;
-- 调用过程
CALL demo();
-- 删除过程
DROP PROCEDURE demo;

-- 输出类型的参数 参数不能以@开头
DELIMITER $$
    CREATE PROCEDURE demo2(OUT num INT)
    BEGIN
    	SELECT COUNT(1) INTO num FROM depts;
    END$$
DELIMITER ;

-- 声明变量 @
CALL demo2(@num);
SELECT @num;

2>函数：

创建存储函数，需要使用CREATE FUNCTION语句，基本语法格式如 下

CREATE FUNCTION funname(parama) RETURNS type

注意，如果是8.0，需要设置可以

SET GLOBAL log_bin_trust_function_creators=TRUE;

DELIMITER $$
    CREATE FUNCTION fun_01() RETURNS INT
    BEGIN
        DECLARE _num INT;
        SELECT COUNT(*) INTO _num FROM grade;
        RETURN _num;
    END$$
DELIMITER ;

DELIMITER $$
    CREATE FUNCTION fun_02(_sid INT) RETURNS VARCHAR(50)
    BEGIN
        DECLARE _sno VARCHAR(50);
        DECLARE _subid INT;
        DECLARE _sco INT;
        SELECT s_no,sub_id,student_sco INTO _sno,_subid,_sco FROM score WHERE sco_id=_sid;
        RETURN CONCAT(_sno,'--',_subid,'--',_sco);
    END$$
DELIMITER ;

DELIMITER $$
    CREATE FUNCTION fun_03(_sid INT, _sno VARCHAR(50), _subid VARCHAR(50)) RETURNS VARCHAR(100)
    BEGIN
    	DECLARE _sco INT;
    	SELECT s_no,sub_id,IFNULL(student_sco,0) INTO _sno,_subid,_sco FROM score WHERE sco_id=_sid;
    	RETURN CONCAT(_sno,'--',_subid,'--',_sco);
    END$$
 DELIMITER ;

SELECT * FROM score;
SELECT fun_03(8,@sno,@subid);
SELECT CONCAT(@sno,@subid);

-- 传入条件 如果1 查询
DELIMITER $$
    CREATE FUNCTION fun_05(_cond INT,_id VARCHAR(20) ) RETURNS VARCHAR(100)
    BEGIN
        DECLARE _sco VARCHAR(50);
        IF _cond=1 THEN
        	SELECT g_name INTO _sco FROM grade WHERE g_id=_id ;
        ELSEIF _cond=2 THEN
       		SELECT s_name INTO _sco FROM student WHERE s_no=_id;
        ELSE
        	SELECT sub_name INTO _sco FROM subjects WHERE sub_id=_id;
        END IF;
    
    	RETURN CONCAT(_cond,'--',_id,'--',_sco);
    END$$
DELIMITER ;