Detailed explanation of MySql view trigger stored procedure

--delimiter //
  
-- before or after defines the operation before or after the operation (insert or other) -- on represents the trigger operation after the operation occurs on that table -- CREATE TRIGGER t1 BEFORE INSERT on teacher for EACH row
--BEGIN
-- INSERT into course(cname) VALUES('Ultraman');
--END //
--delimiter;
 
 
-- insert into teacher(tname) VALUES('triggertest111')
--
--delimiter // 
-- CREATE TRIGGER t1 BEFORE INSERT on student for EACH row
--BEGIN
-- INSERT into teacher(tname) VALUES('Ultraman');
--END //
--delimiter;
 
 
-- insert into student(gender,sname,class_id) VALUES('男','1小刚111',3);
-- Delete trigger -- drop trigger t1;
 
 
-- NEW and OLD represent new and old data to make them consistent -- delimiter //
-- create TRIGGER t1 BEFORE insert on student for each row
--BEGIN
 
--Here new specifies the newly inserted data, and old is usually used for delete -- insert into teacher(tname) VALUES(NEW.sname);
-- end //
--delimiter;
insert into student(gender,sname,class_id) VALUES('男','蓝大蟹',3);

 call p2(5,2)<br data-filtered="filtered"><br data-filtered="filtered"><em id="__mceDel"> cursor.callproc('p1',(5,2)) in pymysql</em>

-- 2 with parameter in parameter -- delimiter //
-- create PROCEDURE p2(
-- in n1 int,
-- in n2 int
-- )
--BEGIN
-- select * from student where sid<n1;
-- 
-- end //<br data-filtered="filtered"><br data-filtered="filtered"> call p2(5,2)<br data-filtered="filtered"><br data-filtered="filtered"><em id="__mceDel"> cursor.callproc('p1',(5,2)) in pymysql</em>

-- 3 out parameter When using out as a parameter in a stored procedure, the variable can be called externally -- There is no return in the stored procedure. If you want to call a variable externally, you need to use out
--delimiter //
-- create PROCEDURE p3(
-- in n1 int,
--out n2 int
-- )
--BEGIN
--set n2=444444;
-- select * from student where sid<n1;
-- 
-- end //
--
--delimiter;
--
-- set @v1=999 is equivalent to creating a variable at the session level -- set @v1=999; 
-- call p3(5,@v1);
-- select @v1; #By passing a variable in and then monitoring this variable, you can monitor whether the stored procedure is executed successfully -- in pymsyql --  
-- cursor.callproc('p3',(5,2))
-- r2=cursor.fetchall()
-- print(r2)
--
-- The stored procedure contains the out keyword. If you want to get the return value, cursor.execute('select @_p3_0,@_p3_1')
-- # Among them, 'select @_p3_0,@_p3_1' is a fixed writing method select @_stored procedure name_input parameter index position-- cursor.execute('select @_p3_0,@_p3_1')
-- r3=cursor.fetchall()
-- print(r3)
--

delimiter //
 create procedure p5(
 in n1 int,
 out n2 int
 )
 begin
 1 Statement if abnormal execution occurs (
   set n2=1;
   rollback;
  ）
  2 Start transaction Buyer account - 100
        Selling account +100
        commit
  3 End set n2=2
   end //
   
   delimiter ;
   
 In this way, you can detect errors through n2 and roll back. The following is the detailed code delimiter //
  create procedure p6(
  out code TINYINT
  )
  begin
   Declare that if a SQLException is encountered, execute the following operation DECLARE exit HANDLER for SQLEXCEPTION
   begin
    --error
      set code=1;
      rollback;
   end;
   START TRANSACTION;
       delete from tb1;
       insert into tb2(name)values('slkdjf')
   commit;
   ---success
   code=2
   end //
delimiter ;

delimiter //
create procedure p7()
 begin
    declare row_id int;
     declare row_num int;
     declare done int DEFAULT FALSE;
     Declare a cursor declare my_cursor cursor for select id,num from A;
     Declare that if there is no data, set done to True
     declare continue handler for not found set done=True;
     
     
     open my_cursor; open cursor xxoo; LOOP start loop called xxoo
            fetch my_cursor into row_id,row_num;
              if done then if done is True leave the loop leave xxoo;
              end if;
              set temp=row_id+row_num;
       insert into B(number)VALUES(temp);
            end loop xxoo; close the loop close my_cursor;
    end //
     
delimiter ;
 
 
The above code is converted into Python 
for row_id,row_num in my_cursor:
  Check if there is any data in the loop, if not, break
    break
    insert into B(num) values(row_id+row_num)

delimiter \\
create procedure p6(
 in nid int)
 begin
  1 Precompile (pre-check) the legitimacy of a certain sql statement 2 sql=format tpl+arg
    3 Execute SQL
 
  set @nid=nid
    prepare prod from 'select * from student where sid>?'
    EXECUTE prod using @nid;
    deallocate prepare prod
  end \\
  delimiter ;