存储过程及流程控制语句

存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。

1、创建存储过程

对于存储过程，可以接收参数，其参数有三类：

in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当作返回值

有参数的存储过程

-- 创建存储过程
delimiter \\
create procedure p1(
    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
)
BEGIN
    DECLARE temp1 int;
    DECLARE temp2 int default 0;
    
    set temp1 = 1;

    set r1 = i1 + i2 + temp1 + temp2;
    
    set i3 = i3 + 100;

end\\
delimiter ;

-- 执行存储过程
set @t1 =4;
set @t2 = 0;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;

1. 结果集

delimiter //
                    create procedure p1()
                    begin
                        select * from v1;
                    end //
                    delimiter ;

2. 结果集+out值

delimiter //
                    create procedure p2(
                        in n1 int,
                        inout n3 int,
                        out n2 int,
                    )
                    begin
                        declare temp1 int ;
                        declare temp2 int default 0;

                        select * from v1;
                        set n2 = n1 + 100;
                        set n3 = n3 + n1 + 100;
                    end //
                    delimiter ;

3. 事务

delimiter \\
                        create PROCEDURE p1(
                            OUT p_return_code tinyint
                        )
                        BEGIN 
                          DECLARE exit handler for sqlexception 
                          BEGIN 
                            -- ERROR 
                            set p_return_code = 1; 
                            rollback; 
                          END; 
                         
                          DECLARE exit handler for sqlwarning 
                          BEGIN 
                            -- WARNING 
                            set p_return_code = 2; 
                            rollback; 
                          END; 
                         
                          START TRANSACTION; 
                            DELETE from tb1;
                            insert into tb2(name)values('seven');
                          COMMIT; 
                         
                          -- SUCCESS 
                          set p_return_code = 0; 
                         
                          END\\
                    delimiter ;

4. 游标

delimiter //
                    create procedure p3()
                    begin 
                        declare ssid int; -- 自定义变量1  
                        declare ssname varchar(50); -- 自定义变量2  
                        DECLARE done INT DEFAULT FALSE;


                        DECLARE my_cursor CURSOR FOR select sid,sname from student;
                        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
                        
                        open my_cursor;
                            xxoo: LOOP
                                fetch my_cursor into ssid,ssname;
                                if done then 
                                    leave xxoo;
                                END IF;
                                insert into teacher(tname) values(ssname);
                            end loop xxoo;
                        close my_cursor;
                    end  //
                    delimter ;

5. 动态执行SQL

delimiter \\
                    CREATE PROCEDURE p4 (
                        in nid int
                    )
                    BEGIN
                        PREPARE prod FROM 'select * from student where sid > ?';
                        EXECUTE prod USING @nid;
                        DEALLOCATE prepare prod; 
                    END\\
                    delimiter ;

2、删除存储过程

drop procedure proc_name;

3、执行存储过程

执行存储过程

-- 无参数
call proc_name()

-- 有参数，全in
call proc_name(1,2)

-- 有参数，有in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

pymysql执行存储过程

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()


print(result)

数据库操作

MariaDB [sqlexample]> delimiter $$;
MariaDB [sqlexample]> select * from class;
    -> select * from course$$;
+-----+--------------+
| cid | caption      |
+-----+--------------+
|   1 | 三年二班     |
|   2 | 三年三班     |
|   3 | 一年二班     |
|   4 | 二年九班     |
|   5 | 全栈二班     |
|   6 | 全栈二班     |
|   7 | uuu          |
|   8 | 'nnn'        |
|   9 | op           |
|  10 | ooo          |
|  11 | ooo          |
|  12 | ooo1         |
|  13 | ooo2         |
+-----+--------------+
13 rows in set (0.00 sec)

+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+
4 rows in set (0.00 sec)

MariaDB [sqlexample]> delimiter ;

MariaDB [sqlexample]> delimiter $$
MariaDB [sqlexample]> create procedure p1()
    -> BEGIN
    -> select * from class;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

MariaDB [sqlexample]> delimiter ;

MariaDB [sqlexample]> call p1();
+-----+--------------+
| cid | caption      |
+-----+--------------+
|   1 | 三年二班     |
|   2 | 三年三班     |
|   3 | 一年二班     |
|   4 | 二年九班     |
|   5 | 全栈二班     |
|   6 | 全栈二班     |
|   7 | uuu          |
|   8 | 'nnn'        |
|   9 | op           |
|  10 | ooo          |
|  11 | ooo          |
|  12 | ooo1         |
|  13 | ooo2         |
+-----+--------------+
13 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

MariaDB [sqlexample]> delimiter //
MariaDB [sqlexample]> create procedure p2(
    -> in i1 int,
    -> in i2 int,
    -> inout i3 int,
    -> out r1 int
    -> )
    -> BEGIN
    -> DECLARE temp1 int;
    -> DECLARE temp2 int default 0;
    -> set temp1 = 1;
    -> set r1 = i1 + i2 + temp1 + temp2;
    -> set i3 = i3 + 100;
    -> select * from student;
    -> end //
Query OK, 0 rows affected (0.00 sec)

MariaDB [sqlexample]> delimiter ;

stored_procedure.py

#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: stored_procedure
# time: 2021/10/27

import pymysql

conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample', charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
r1 = cursor.callproc('p2', args=(1, 22, 3, 4))
print(r1)
# 获取执行完存储的参数
r2 = cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
print(r2)

cursor.close()
conn.close()

/home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/venv/bin/python /home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/use_mysqlDB/stored_procedure.py
(1, 22, 3, 4)
1

Process finished with exit code 0

#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: stored_procedure
# time: 2021/10/27

import pymysql

conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample', charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
r1 = cursor.callproc('p2', args=(1, 22, 3, 4))
print(r1)
# 获取执行完存储的参数
r2 = cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
print(r2)
result = cursor.fetchall()
print(result)

cursor.close()
conn.close()

/home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/venv/bin/python /home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/use_mysqlDB/stored_procedure.py
(1, 22, 3, 4)
1
[{'@_p1_0': None, '@_p1_1': None, '@_p1_2': None, '@_p1_3': None}]

Process finished with exit code 0

#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: stored_procedure
# time: 2021/10/27

import pymysql

conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample', charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
r1 = cursor.callproc('p2', args=(1, 22, 3, 4))
print(r1)
# 获取执行完存储的参数
r2 = cursor.execute("select @_p2_0,@_p2_1,@_p2_2,@_p2_3")
print(r2)
result = cursor.fetchall()
print(result)

cursor.close()
conn.close()

/home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/venv/bin/python /home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/use_mysqlDB/stored_procedure.py
(1, 22, 3, 4)
1
[{'@_p2_0': 1, '@_p2_1': 22, '@_p2_2': 103, '@_p2_3': 24}]

Process finished with exit code 0

#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: stored_procedure
# time: 2021/10/27

import pymysql

conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample', charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
r1 = cursor.callproc('p2', args=(1, 22, 3, 4))
print(r1)
result1 = cursor.fetchall()
print(result1)

# 获取执行完存储的参数
r2 = cursor.execute("select @_p2_0,@_p2_1,@_p2_2,@_p2_3")
print(r2)
result2 = cursor.fetchall()
print(result2)

cursor.close()
conn.close()

/home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/venv/bin/python /home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/use_mysqlDB/stored_procedure.py
(1, 22, 3, 4)
[{'sid': 1, 'gender': '男', 'class_id': 1, 'sname': '马大狗'}, {'sid': 2, 'gender': '女', 'class_id': 1, 'sname': '钢蛋'}, {'sid': 3, 'gender': '男', 'class_id': 1, 'sname': '张三'}, {'sid': 4, 'gender': '男',
 'class_id': 1, 'sname': '张一'}, {'sid': 5, 'gender': '女', 'class_id': 1, 'sname': '张二'}, {'sid': 6, 'gender': '男', 'class_id': 1, 'sname': '张四'}, {'sid': 7, 'gender': '女', 'class_id': 2, 'sname': '
铁锤'}, {'sid': 8, 'gender': '男', 'class_id': 2, 'sname': '李三'}, {'sid': 9, 'gender': '男', 'class_id': 2, 'sname': '李一'}, {'sid': 10, 'gender': '女', 'class_id': 2, 'sname': '李二'}, {'sid': 11, 'gender
': '男', 'class_id': 2, 'sname': '李四'}, {'sid': 12, 'gender': '女', 'class_id': 3, 'sname': '如花'}, {'sid': 13, 'gender': '男', 'class_id': 3, 'sname': '刘三'}, {'sid': 14, 'gender': '男', 'class_id': 3, 
'sname': '刘一'}, {'sid': 15, 'gender': '女', 'class_id': 3, 'sname': '刘二'}, {'sid': 16, 'gender': '男', 'class_id': 3, 'sname': '刘四'}, {'sid': 17, 'gender': '女', 'class_id': 1, 'sname': '鸭蛋'}, {'sid'
: 18, 'gender': '女', 'class_id': 1, 'sname': '鸭蛋1'}, {'sid': 19, 'gender': '女', 'class_id': 1, 'sname': '鸭蛋2'}, {'sid': 20, 'gender': '女', 'class_id': 1, 'sname': '鸭蛋3'}]
1
[{'@_p2_0': 1, '@_p2_1': 22, '@_p2_2': 103, '@_p2_3': 24}]

Process finished with exit code 0

其他
1、条件语句

if条件语句

delimiter \\
CREATE PROCEDURE proc_if ()
BEGIN
    
    declare i int default 0;
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;

END\\
delimiter ;

2、循环语句

while循环

delimiter \\
CREATE PROCEDURE proc_while ()
BEGIN

    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;

END\\
delimiter ;

repeat循环

delimiter \\
CREATE PROCEDURE proc_repeat ()
BEGIN

    DECLARE i INT ;
    SET i = 0 ;
    repeat
        select i;
        set i = i + 1;
        until i >= 5
    end repeat;

END\\
delimiter ;

loop

BEGIN
    
    declare i int default 0;
    loop_label: loop
        
        set i=i+1;
        if i<8 then
            iterate loop_label;
        end if;
        if i>=10 then
            leave loop_label;
        end if;
        select i;
    end loop loop_label;

END

3、动态执行SQL语句

动态执行SQL

delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\
CREATE PROCEDURE proc_sql ()
BEGIN
    declare p1 int;
    set p1 = 11;
    set @p1 = p1;

    PREPARE prod FROM 'select * from tb2 where nid > ?';
    EXECUTE prod USING @p1;
    DEALLOCATE prepare prod; 

END\\
delimiter ;

发表于 2021-10-24 21:26 深秋、阅读(320) 评论(0) 收藏举报

公告