【MySQL速通篇003】MySQL视图，MySQL触发器，MySQL函数，MySQL存储过程(参数分类，存储过程的增删改查等)，SQL的动态执行，支持事务的存储过程，pymysql 2

import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='@123456', db='testdb', charset='utf8')
# 创建游标
cursor = conn.cursor()
# 调用存储过程p1
cursor.callproc('p1')
conn.commit()
print(cursor.fetchall())
cursor.close()
conn.close()
输出结果：
((1, '三年一班'), (2, '三年四班'), (3, '五年一班'), 
(4, '六年二班'), (5, '七年二班'), (6, 'teacher触发产生'),
(7, '触发产生'), (8, '触发产生'), (9, '调用了存储过程'),
(10, '调用了存储过程'))

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程的名' [AND ROUTINE_TYPE = 'PROCEDURE'];

delimiter //
create procedure p2(
  in n1 int,
  out n2 char(15)
)
begin 
  set n2 = "在存储过程中改变";
  SELECT * FROM class WHERE cid > n1;
end //
delimiter ;

import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='@123456', db='testdb', charset='utf8')
# 创建游标
cursor = conn.cursor()
# 调用存储过程,并且传参数
cursor.callproc('p2', (8,"开始值"))
print(cursor.fetchall())
# 拿到结果集
cursor.execute('select @_p2_0,@_p2_1')
print(cursor.fetchall())
cursor.close()
conn.close()
输出结果：
((9, '调用了存储过程'), (10, '调用了存储过程'))
((8, '在存储过程中改变'),)

set @_v1_0 = 8;
set @_v1_1 = "开始值";
call p2(@_v1_0, @_v1_1);
select @_v1_0, @_v1_1;
输出结果：
结果一               结果二
+---------------------+      +--------------------+
cid   caption       @_v1_0    @_v1_1
9   调用了存储过程     8 在存储过程中改变
10    调用了存储过程     +---------------------+
+---------------------+   

delimiter // 
create procedure p6()
begin
  -- 自定义变量用来存储每一行id值
  declare row_id int;
  declare row_num varchar(50);
  -- 如果done为false表示还没有终止，下面的游标还能继续执行
  declare done int default false;
  declare temp int
  -- 去A表中逐行取数据，相当于遍历A表中的每一行
  declare my_cursor cursor for select id, num from A;
  -- 游标终止的条件 ，检测当A表中没有数据done = ture就退出
  declare continue handler for not found set done = ture;
  -- 创建游标
  open my_cursor;
    -- 表示开启循环 loop表示循环
    loop_label:  LOOP
      fetch my_cursor into row_id, row_num;
      IF done THEN
        LEAVE loop_label; 
      END IF; 
        set temp = row_id + row_num;
        INSERT INTO B(num) VALUES(temp);
    -- 循环终止的条件
    END LOOP loop_label;
end //
delimiter ;

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 ;

delimiter \\
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
  SELECT
      num ;
  SET num = num + 1 ;
-- 结束的标识就是while不满足
END WHILE ;
END\\
delimiter ;

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 ;

delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\
CREATE PROCEDURE proc_sql ()
BEGIN
declare p1 int;
set p1 = 11;
    -- 必须要写，在下面using后才不会报错
set @p1 = p1;
    -- 预检测某个东西 SQL语句的合法性，prod为自定义的变量
PREPARE prod FROM 'select * from tb2 where nid > ?';
    --  @p1表示用p1的值替换上一行语句中的？号
EXECUTE prod USING @p1;
    -- 执行上面的已经格式化完成的SQL语句
DEALLOCATE prepare prod; 
END\\
delimiter ;

delimiter \\
create PROCEDURE p1(
-- p_return_code根据值的不同用来表示状态
OUT p_return_code tinyint
)
BEGIN 
-- 设置发生SQL异常退出
DECLARE exit handler for sqlexception 
BEGIN 
-- ERROR(错误时返回1)
set p_return_code = 1; 
-- 回滚到未发生错误时的初始状态
rollback; 
END; 
-- 设置发生SQL警告退出
DECLARE exit handler for sqlwarning 
BEGIN 
-- WARNING(警告时返回2)
set p_return_code = 2; 
-- 回滚到初始状态
rollback; 
END; 
-- START TRANSACTION立即启动一个事务,而不管当前的提交模式设置如何。
-- 无论当前的提交模式设置如何,以START transaction开始的事务必须通过
-- 发出显式COMMIT或ROLLBACK来结束。
START TRANSACTION; 
DELETE from tb1;
insert into tb2(name)values('seven');
-- 因为没有发生错误，就将更改的数据提交
COMMIT; 
-- SUCCESS(成功时返回0)
set p_return_code = 0; 
END\\
delimiter ;