MySQL笔记-09存储过程和函数

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

# 语法
     CREATE PROCEDUE 存储过程名(参数列表)
     BEGIN 
        存储过程体(一组合法的SQL语句)
     END
# 注意：
	/*
		1、参数列表包含3部分：参数模式、参数名、参数类型
			举例：
			IN stuname VARCHAR(20)
			参数模式：
                IN: 该参数可以作为输入，也就是说该参数需要调用方传入值
                OUT: 该参数可以作为输出，也就是该参数可以作为返回值
                INOUT: 该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
        2、如果存储过程体仅仅只有一句话，则可以省略BEGIN END
        3、存储过程中的每条SQL语句结尾，要求必须加分号;
        4、存储过程的结尾可以使用 DELIMITER 重新设置
        	语法：
        	DELIMITER 结束标记（如$$，可自定义）
        	DELIMITER $$
	*/

1
2
3
4
5
6
7
8

#语法
	CALL 存储过程名 (实参列表);
	
# 带OUT参数的调用
SET @m = 10;
SET @n = 20;
CALL 存储过程名(@m， @n);
SELECT @m, @n;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

# 1、空参列表
	# 案例：插入到admin表中5条记录
        USE girls;
        SELECT * FROM admin;

        DELIMITER $$
        CREATE PROCEDURE myp1()
        BEGIN
            INSERT INTO admin(username, `password`)
            VALUES('john1', '0000'),('lily', '0000'),('rose', '0000'),('jack', '0000'),('tom', '0000');
        END $$
        
        # 调用
        CALL myp1()$$ 
    
# 2、创建带IN模式参数的存储过程
	# 案例1：创建存储过程实现：根据女神名，查询对应的男神信息
		CREATE PROCEDURE myp2(IN beautyName VARCHAR(20)) 
		BEGIN
			SELECT bo.*
			FROM boys bo
			RIGHT JOIN beauty b
			ON bo.id = b.boyfriend_id
			WHERE b.name = beautyName;
		END $$
		
		# 调用
    	CALL myp2('柳岩')$$
    
    # 案例2：创建存储过程实现，用户是否登录成功
    	CREATE PROCEDURE myp3(IN username VARCHAR(20), IN PASSWORD VARCHAR(20))
        BEGIN 
			DECLARE result INT DEFAULT 0;  # 声明并初始化
            
			SELECT COUNT(*) INTO result  # 赋值
            FROM admin
            WHERE admin.username = username
            AND admin.password = PASSWORD;
            
            SELECT IF (result > 0, '成功', '失败');  # 使用
        END $$
        
        # 调用
        CALL myp3('张三', '8888')$$
     
# 3、创建带OUT模式的存储过程
	# 案例1：根据女神名，返回对应的男神名
		CREATE PROCEDURE myp4(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
		BEGIN
			SELECT bo.boyName INTO boyName
            FROM boys bo
            JOIN beauty b
            ON bo.id = b.boyfriend_id
            WHERE b.name = beautyName;
        END $$
        
        # 调用
        SET @bName$$
        CALL myp4('小昭',@bName)$$
        SELECT @bName$$
        
    # 案例2：根据女神名，返回对应的男神名和男神魅力值
    	CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT)
		BEGIN
			SELECT bo.boyName, bo.userCP INTO boyName, userCP
    		FROM boys bo
    		JOIN beauty b
    		ON bo.id = b.boyfriend_id
    		WHERE b.name = beautyName;
		END
		
		# 调用
		CALL myp5('小昭', @nName, @userCP);
		SELECT @bName, @userCP;

# 4、创建带INOUT模式参数的存储过程
	# 案例1：传入a和b两个值，最终a和b都翻倍并返回
        CREATE PROCEDURE myp6(INOUT a INT, INOUT b INT)
        BEGIN
            SET a = a * 2;
            SET b = b * 2;
        END
        
        # 调用
        SET @m = 10;
        SET @n = 20;
        CALL myp6 (@m, @n);
        SELECT @m, @n;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

# 一、创建存储过程，实现传入用户名和密码，插入到admin表中
	CREATE PROCEDURE teast_pro1(IN username VARCHAR(20), IN loginPwd VARCHAR(20))
	BEGIN
		INSERT INTO admin(admin.username, password)
		VALUES(username, loginPwd);
	END
    
    # 调用
    CALL teast_pro1('admin', '0000');
    SELECT * FROM admin;
    
# 二、创建存储过程或函数，实现传入女神编号，返回女神名称和女神电话
	CREATE PROCEDURE test_pro2(IN id INT, OUT name VARCHAR(20), OUT phone VARCHAR(20))
    BEGIN
		SELECT b.name, b.phone INTO name, phone
        FROM beauty b
        WHERE b.id = id;
    END
    
    # 调用
    CALL test_pro2(1, @n, @p);
	SELECT @n, @p;
	
# 三、创建存储过程或函数，实现传入两个女神生日，返回大小
	CREATE PROCEDURE test_pro3 (IN birth1 DATETIME, IN birth2 DATETIME, OUT result INT)
	BEGIN
		SELECT DATEDIFF(birth1, birth2) INTO result;
	END
    
    # 调用
    CALL test_pro3('1998-1-1', NOW(), @result);
    SELECT @result;	
    
# 四、创建存储过程或函数，实现传入一个日期，格式化成xxxx年xx月xx日并返回
	CREATE PROCEDURE test_pro4(IN mydate DATETIME, OUT strdate VARCHAR(50))
	BEGIN
		SELECT DATE_FORMAT(mydate, '%Y年%m月%d日') INTO strdate;
	END
    
	# 调用
    CALL test_pro4(NOW(), @strdate);
    SELECT @strdate;    

# 五、创建存储过程或函数，实现传入女神名称，返回：女神 and 男神 格式的字符串
	CREATE DEFINER=`root`@`localhost` PROCEDURE `test_pro5`(IN bName VARCHAR(20), OUT result VARCHAR(50))
	BEGIN
		SELECT CONCAT(bName, ' and ', boyName) INTO result
		FROM boys bo
		RIGHT JOIN beauty b
		ON bo.id = b.boyfriend_id
		WHERE b.name = bName;
	END
	
    # 调用
	CALL test_pro5('柳岩', @result);
	SELECT @result;
    
# 六、创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录
	CREATE PROCEDURE test_pro6 (IN startIndex INT, IN size INT)
	BEGIN
		SELECT *
		FROM beauty
		LIMIT startIndex, size;
	END  

	# 调用
    CALL test_pro6(4, 2);

1
2

# 语法
DROP PROCEDURE 存储过程名;   # 仅支持一次删除1个

1
2

# 语法
SHOW CREATE PROCEDURE 存储过程名;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

# 语法
CREATE FUNCTION 函数名 (参数列表) RETURNS 返回类型
BEGIN
	函数体
	RETURN 值;
END

#注意：
/*
	1、参数列表包含2部分：参数名，参数类型
	2、函数体：必须包含RETURN语句，如果没有，会报错；
		如果RETURN语句没有放在函数体的最后，不会报错，但不建议（没有意义）
	3、函数体中只有1句话时，可以省略BEGIN ……END语句
	4、使用DELIMITER语句设置结束标记
*/

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

# 语法
SELECT 函数名(参数列表);

#——————————————————————案例演示——————————————————
# 1、无参有返回
	# 案例：返回公司的员工个数
	CREATE FUNCTION myf1() RETURNS INT
	BEGIN
		DECLARE c INT DEFAULT 0;		# 定义变量
		SELECT COUNT(*) INTO c
		FROM employees;
		RETURN c;
	END

	# 调用
	SELECT myf1();
	
# 2、有参有返回
	# 案例1：根据员工名，返回ta的工资
	CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
	BEGIN
        SET @sal = 0;		# 定义用户变量
        SELECT salary INTO @sal
        FROM employees
        WHERE last_name = empName;
	
    	RETURN @sal;
	END
	
	# 调用
	SELECT myf2('Kochhar');
	
	# 案例2：根据部门名，返回该部门的平均工资
    CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS double
    BEGIN
        DECLARE sal DOUBLE;
        SELECT AVG(salary) INTO sal
        FROM employees e
        JOIN departments d
        ON e.department_id = d.department_id
        WHERE d.department_name = deptName;

        RETURN sal;
    END	
    
    # 调用
    SELECT myf3('IT');

1

SHOW CREATE FUNCTION myf3;

1

DROP FUNCTION myf3;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# 一、创建函数，实现传入两个float，返回二者之和
	CREATE FUNCTION test_fun1(num1 FLOAT, num2 FLOAT) RETURNS float
	BEGIN
		DECLARE sum FLOAT DEFAULT 0;
		SET sum = num1 + num2;
		RETURN sum;
	END

	# 调用
	SELECT test_fun1(1, 2);