MySQL之函数、视图、存储过程

MySQL视图、函数与存储过程

1. 函数

1.1 语法

-- 函数是需要有返回值的,只能返回一个值
CREATE FUNCTION funcation_name (param_name data_type [, param_name2 data_type]) RETURNS return_data_type
DETERMINISTIC -- 可选项，用于执行函数对于相同入参，是否总返回相同结果
NO SQL -- 可选项，用于执行函数是否访问SQL数据,有SQL和NO SQL两个值
COMMENT '函数注释'
BEGIN
    -- 函数体
    -- 返回结果
END

1.2 例子1

CREATE FUNCTION add_numbers(x INT, y INT) RETURNS INT
BEGIN
    DECLARE result INT;
    SET result = x + y;
    RETURN result;
END

-- 调用函数
SELECT add_numbers(1, 2); -- 返回3

1.3 例子2

-- 删除函数如果存在的话
DROP FUNCTION IF EXISTS GetTopLevelDepartmentId;

-- 创建一个函数,入参cur_dep_id为INT类型,返回值也为INT类型
-- 整个业务逻辑在BEGIN与END之间
CREATE FUNCTION GetTopLevelDepartmentId(cur_dep_id INT) RETURNS INT
BEGIN
    -- 定义INT类型变量 firstid
	DECLARE firstid INT;
	DECLARE parent_id INT;
    -- 定义INT类型变量 done 默认为FALSE
    DECLARE done INT DEFAULT FALSE;
	DECLARE tmp_dep_id INT;
	
	SET tmp_dep_id = cur_dep_id;
    -- WHILE ... DO循环
	WHILE NOT done DO
		SELECT id, SUPDEPID INTO firstid, parent_id
		FROM hrmdepartment WHERE id = tmp_dep_id;
        -- 条件判断语句
		IF parent_id <= 0 THEN
			RETURN firstid;
		ELSE
			SET tmp_dep_id = parent_id;
			SET done = FALSE;
		END IF;
	END WHILE;
	RETURN -1;
END


-- 函数 GetTopLevelDepartmentName
DROP FUNCTION IF EXISTS GetTopLevelDepartmentName;
CREATE FUNCTION GetTopLevelDepartmentName(cur_dep_id INT) RETURNS VARCHAR(1000)
BEGIN
	DECLARE dep_name VARCHAR(1000);
	DECLARE parent_id INT;
  DECLARE done INT DEFAULT FALSE;
	DECLARE tmp_dep_id INT;
	
	SET tmp_dep_id = cur_dep_id;
	WHILE NOT done DO
		SELECT DEPARTMENTNAME, SUPDEPID INTO dep_name, parent_id
		FROM hrmdepartment WHERE id = tmp_dep_id;
		IF parent_id <= 0 THEN
			RETURN dep_name;
		ELSE
			SET tmp_dep_id = parent_id;
			SET done = FALSE;
		END IF;
	END WHILE;
	RETURN dep_name;
END

1.4 条件判断

-- 在mysql的函数中条件判断
IF condition THEN
  -- 具体逻辑
END IF;

IF condition THEN
  -- 逻辑1
ELSE
  -- 逻辑2
END IF;

IF condition1 THEN
  -- 逻辑1
ELSEIF condition2 THEN
  -- 逻辑2
ELSE
  -- 逻辑3
END IF;

-- CASE条件判断
CASE expression
    WHEN value1 THEN
      -- 代码1
    WHEN value2 THEN
      -- 代码2
    ELSE
      -- 条件都不满足时,代码3
END CASE;

-- NULLIF函数, 用于判断两个表达式是否相等, 相等返回NULL,否则返回第一个表达式
NULLIF(expression1, expression2);
SELECT NULLIF(1,2); -- 返回1
SELECT NULLIF(2,2); -- 返回NULL

1.5 循环

-- while循环
WHILE condition DO
    -- 循环体
END WHILE;

-- repeat循环
REPEAT
  -- 循环体
  UNTIL condition END REPEAT;

-- loop循环
LOOP
  -- 循环体
  IF condition THEN
    -- 代码
  END IF;
  END LOOP;

-- 例子
DROP FUNCTION IF EXISTS test;
CREATE FUNCTION test() RETURNS INT
DETERMINISTIC
NO SQL
BEGIN
	DECLARE a INT DEFAULT 0;
	REPEAT
		SET a = a + 1;
		UNTIL a >= 10 END REPEAT;
	RETURN a;
END

SELECT test();

DROP FUNCTION IF EXISTS test2;
CREATE FUNCTION test2() RETURNS INT
DETERMINISTIC
NO SQL
BEGIN
	DECLARE a INT DEFAULT 0;
	LOOP
		SET a = a + 1;
		IF a >= 11 THEN
			RETURN a;
		END IF;
		END LOOP;
END
SELECT test2();

2. 视图

2.1 语法

CREATE OR REPLACE VIEW view_name AS
SELECT col1, col2, ...
FROM table_name
WHERE condition;

2.2 例子

-- 基于1.3中的例子2的函数创建视图
CREATE VIEW v_firstlevel_dept_new AS
SELECT
	a.id,
	a.DEPARTMENTNAME AS departmentname,
	GetTopLevelDepartmentId(a.id) AS firstid,
	GetTopLevelDepartmentName(a.id) AS firstdeptname
FROM
	hrmdepartment a 
WHERE
	a.CANCELED IS NULL OR a.CANCELED = 0;

-- 调用视图, 相当于一张数据库表
select * from v_firstlevel_dept_new;

3. 存储过程

3.1 语法

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE procedure_name()
BEGIN
    -- 存储过程的逻辑代码
END //
DELIMITER ;

-- 删除存储过程
DROP PROCEDURE IF EXISTS procedure_name;

-- 调用存储过程
CALL procedure_name();

3.2 例子

DROP PROCEDURE IF EXISTS GetTopLevelDepartment;
DELIMITER $$
CREATE PROCEDURE GetTopLevelDepartment(
    IN dept_id INT, -- 输入参数
	OUT child_id INT, -- 输出参数
	OUT child_name VARCHAR(1000), -- 输出参数
    OUT parent_dept_id INT, -- 输出参数
    OUT parent_dept_name VARCHAR(1000) -- 输出参数
)
BEGIN
	-- 定义变量
    DECLARE done INT DEFAULT FALSE;
	DECLARE v_count INT;
    DECLARE current_dept_id INT;
    DECLARE current_dept_name VARCHAR(1000);
	DECLARE root_id INT;
    
    IF (dept_id > 0) THEN
        -- 将参数dep_id赋值给变量current_dept_id
        SET current_dept_id = dept_id;
        
        SELECT COUNT(1) INTO v_count
        FROM hrmdepartment
        WHERE id = current_dept_id;
        IF v_count > 0 THEN
            SELECT id, DEPARTMENTNAME INTO current_dept_id, current_dept_name
            FROM hrmdepartment
            WHERE id = current_dept_id;
            SET child_id = current_dept_id;
            SET child_name = current_dept_name;
            -- while...do循环
            WHILE NOT done DO
                SELECT COUNT(1) INTO v_count
                FROM hrmdepartment
                WHERE id = current_dept_id;
                IF v_count > 0 THEN
                    SELECT id, DEPARTMENTNAME, SUPDEPID INTO current_dept_id, current_dept_name, root_id 
                    FROM hrmdepartment
                    WHERE id = current_dept_id;
                    IF root_id <= 0 THEN
                        SET parent_dept_id = current_dept_id;
                        SET parent_dept_name = current_dept_name;
                        SET done = TRUE;
                    ELSE
                        SET current_dept_id = root_id;
                        SET done = FALSE;
                    END IF;
                ELSE 
                    SET done = TRUE;
                END IF;
            END WHILE;
        END IF;
    END IF;
END $$
DELIMITER ;

-- 调用存储过程,
CALL GetTopLevelDepartment(334, @child_id, @child_name, @parent_dept_id, @parent_dept_name);
SELECT @child_id, @child_name, @parent_dept_id, @parent_dept_name;

-- 说明
可以使用 @输出参数变量 来获取存储过程的输出参数

-- 上面的存储过程是通过定义输入输出的变量来获取存储过程的返回值
-- 下面可以通过如下方式返回多个值
DROP PROCEDURE IF EXISTS GetTopLevelDepartment2222;
DELIMITER $$
CREATE PROCEDURE GetTopLevelDepartment2222(
    IN dept_id INT
)
BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE v_count INT;
    DECLARE child_id INT;
	DECLARE child_name VARCHAR(1000);
	DECLARE current_dept_id INT;
    DECLARE current_dept_name VARCHAR(1000);
	DECLARE root_id INT;
    
    IF (dept_id > 0) THEN
        SET current_dept_id = dept_id;
        
        SELECT COUNT(1) INTO v_count
        FROM hrmdepartment
        WHERE id = current_dept_id;
        IF v_count > 0 THEN
            SELECT id, DEPARTMENTNAME INTO current_dept_id, current_dept_name
            FROM hrmdepartment
            WHERE id = current_dept_id;
            SET child_id = current_dept_id;
            SET child_name = current_dept_name;
            WHILE NOT done DO
                SELECT COUNT(1) INTO v_count
                FROM hrmdepartment
                WHERE id = current_dept_id;
                IF v_count > 0 THEN
                    SELECT id, DEPARTMENTNAME, SUPDEPID INTO current_dept_id, current_dept_name, root_id 
                    FROM hrmdepartment
                    WHERE id = current_dept_id;
                    IF root_id <= 0 THEN
                        SELECT child_id,child_name,current_dept_id,current_dept_name; -- 返回结果
                        SET done = TRUE;
                    ELSE
                        SET current_dept_id = root_id;
                        SET done = FALSE;
                    END IF;
                ELSE 
                    SET done = TRUE;
                END IF;
            END WHILE;
        END IF;
    END IF;
END $$
DELIMITER ;

-- 调用存储过程
CALL GetTopLevelDepartment2222(334);