MySQL stored procedures and loops

MySQL operation also has a circular statement operation, three standard cycle mode: While, Loop,repeat, plus a non-standard loop: goto [In C or C # appears to have a type of loop but generally not recommended! ]


The General format is:
Delimiter//   definition Terminator  
drop procedure if exists wk;  Delete if it exists
CREATE PROCEDURE name ([in | out | input] parameter)

Begin

While-loop-repeat-mysql-code

End//

delimiter;  Restores

Define variables:
DECLARE  count int default 1, define count as int type and set initial value to 1;
Set count=1; Modify the value of the count variable to 1
Set count=count+1; The equivalent of the self-added python
Print Value:
Select count;

Conditional statements:
If Mysql_condtion then;
Mysql_code
Else
Mysql_code
End If;

If Mysql_condtion then;
Mysql_code
End If;

Sample_1:while

While Mysql_condtion do
Mysql_code
End while;

Loop_name:loop
Mysql_code
End Loop;

Sample_3:repeat

Repeat Mysql_codeuntil mysql_condtionend repeat;

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

The stored procedure contains a series of executable SQL statements stored in MySQL, which can be executed by calling its name to execute a heap of SQL inside it.

Advantages of using Stored procedures:

#1. Used to replace the SQL statements written by the program, the implementation program and SQL decoupling # #. Based on the network transmission, the data volume of the alias is small, and the amount of direct SQL data is large.

Disadvantages of using Stored procedures:

#1. Programmer extension is not a convenient feature

Supplement: Three Ways to use the program in conjunction with the database

#方式一:    mysql: Stored procedure    Program: Call stored Procedure # Way two:    MySQL:    program: Pure SQL Statement # Way three:    MySQL:    Program: Class and Object, i.e. ORM (essentially or pure SQL statement)

For stored procedures, you can receive parameters with three types of parameters: #in          only used for incoming parameters with #out        only for return values #inout     can be passed in as well as return values

 
 
 No more nonsense about code 
 
 
 #loop loop 
 Delimiter//
 drop procedure if exists sum2; 
   CREATE PROCEDURE sum2 (args int) 
 begin 
 Declare sum int; 
 declare count int; 
 Set count=1; 
 Set sum=1;      
 Loop_name:loop 
 If Count>args then 
 leave loop_name; 
 End If; 
 Set Sum=sum+count; 
 Set count=count+1; 
 End Loop;  
 Select sum; Output 
 End//
 delimiter; 
 
 
 
 
 Delimiter//
 drop procedure if exists sum2; 
   CREATE PROCEDURE sum2 (args int) 
 begin 
 Declare sum int; 
 declare count int; 
 Set count=1; 
 Set sum=1; 
 Loop_name:loop-loop start 
 If Count>args then 
 leave loop_name;--the end loop when the condition is determined 
 End IF;
 set Sum=sum+count; 
 Set count=count+1; 
 End loop;--Loop end 
 Select sum;--output result 
 End//
 delimiter; 
 
 
 
 
 
 
 

DESC copy;
Copy_struct:id int priymary Key auto_increment
Sname varchar (+) NOT NULL

#无参 Stored Procedures
Delimiter//
drop procedure if exists LOOPCC;
CREATE PROCEDURE LOOPCC ()
Begin
declare i int;
Set i=0;
Repeat
Insert into copy (sname) values (now ());
Set i=i+1;
Until I>=20
End repeat;
End//
Delimiter



#有参 Stored Procedures
Delimiter//
drop procedure if exists sum3;
CREATE PROCEDURE sum3 (a int)
Begin
DECLARE sum int default 0;
declare i int default 1;
Repeat--cycle start
Set sum=sum+i;
Set i=i+1;
Until I>a end repeat; --End of cycle
Select sum; --Output results
end//
delimiter;
---Executing stored procedures
Call SUM3 (100);
Drop prodedure if exists sum3;





Desc Register_time;
Register_time struct:
ID int parmary Key auto_increment,
Name varchar (NOT NULL),
Register_time datetime NOT NULL,
Email varchar ($) NOT null default ' [email protected] '
Gender enum (' Male ', ' female ') default ' male '


#存储过程
Delimiter//
CREATE PROCEDURE P1 ()
BEGIN
DECLARE num INT;
SET num = 0;
While Num < 100000 do-loop start
Insert into Index_test (name,regiter_time) VALUES (' Li Zongjun ', now ());
SET num = num + 1;
END while; --End

END//
delimiter;






DESC copy;
Copy_struct:id int priymary Key auto_increment
Sname varchar (+) NOT NULL

#while Cycle
Delimiter//
drop procedure if exists wk;
CREATE PROCEDURE wk ()
Begin
declare i int;
Set I=1;
While I<8 do
Insert into copy (sname) VALUES (' Your uncle ');
Set i=i+1;
End while;
End//
delimiter;


DESC student;
Student_stuct:
Sid int Parmary Key auto_increment,
Gender enum (' Male ', ' female ') default ' male ',
class_id int NOT NULL,
Sname varchar (+) NOT NULL

#传参数查询 the specified range of data

Delimiter//
drop procedure if exists get_data;
CREATE PROCEDURE Get_data (in Id_value int)
Begin
SELECT * FROM student where sid>id_value;
End//

delimiter;

Call Get_data (4);
drop procedure Get_data;

CREATE TABLE EMP (
Eid int primary Key auto_increment,
ename varchar (20),
Sex enum (' Male ', ' female ') not null default ' male ',
Hire_time datetime Default Now (),
Salary varchar (+) NOT null default ' Egon brother look to give! ‘,
DEPARTMENT_FK int
);

INSERT into EMP (ENAME,DEPARTMENT_FK) VALUES (' Egon ', 2);

Find Employees

--Create a stored procedure

Delimiter//
drop procedure if exists get_emp_info;
CREATE PROCEDURE get_emp_info (Eid int)
Begin
SELECT * from EMP where Emp.eid=eid;
End//

delimiter;

Call Get_emp_info (1);
drop procedure Get_emp_info;

The same can be done in the begin to perform other actions to make it worthwhile to modify and query

Sample:

Begin
Update emp Set sex= ' female ' where eid=1;
End

Begin
Truncate EMP;
End

Begin
Delete from EMP;
End

　　
Use of Out parameters

Delimiter//
drop procedure if exists get_res;
CREATE PROCEDURE Get_res (out res int)
Begin
Select DEPARTMENT_FK from emp where eid=res;
Set res=5
End//

delimiter;

Set @res = 1;
Call Get_res (@res);
Select @res;

MySQL stored procedures and loops