MySQL视图、函数与存储过程
1. 函数
1.1 语法
CREATE FUNCTION funcation_name ( param_name data_type [ , param_name2 data_type] ) RETURNS return_data_type
DETERMINISTIC
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 ) ;
1.3 例子2
DROP FUNCTION IF EXISTS GetTopLevelDepartmentId;
CREATE FUNCTION GetTopLevelDepartmentId( cur_dep_id INT ) RETURNS INT
BEGIN
DECLARE firstid INT ;
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 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
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 条件判断
IF condition THEN
END IF ;
IF condition THEN
ELSE
END IF ;
IF condition1 THEN
ELSEIF condition2 THEN
ELSE
END IF ;
CASE expression
WHEN value1 THEN
WHEN value2 THEN
ELSE
END CASE ;
NULLIF ( expression1, expression2) ;
SELECT NULLIF ( 1 , 2 ) ;
SELECT NULLIF ( 2 , 2 ) ;
1.5 循环
WHILE condition DO
END WHILE ;
REPEAT
UNTIL condition END REPEAT ;
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 例子
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
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
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 ) ;