MySQL stored procedure learning summary

stored procedure

Stored procedures are similar to functions. Defining a set of sql statements can avoid writing the same sql statements repeatedly, and directly call the written stored procedures to execute these sql. At the same time, the stored procedures are stored and executed in the MySQL server, which can reduce the data transmission between the client and the server

Create stored procedure

Format:

create procedure proc_name(proc_param)
begin
	routine_body 
end;

Among them,
proc_name: name of the stored procedure;

proc_param: parameter (composed of three parts), which can have multiple parameters,
Format (IN|OUT|INOUT param_name type),
Parameter 1: IN is the input type, OUT is the output type, INOUT is the input / output type, one of the three options;
Parameter 2: param_name is the parameter name;
Parameter 3: type is the parameter type;

routine_body: represents the sql statement in the stored procedure

[example] using the stored procedure, when a student record is deleted from the student table, the student's score is deleted from the grade table, and the number of people in the class table is updated at the same time

create procedure proc_delete_name(IN sid int)
begin
	declare cid int;	//Define variables
	select class_id into cid from student whrer id=sid; //Query the class id corresponding to the student and store it in the variable
	delete from student where id=sid;  //Delete the record of the student in the student table
	delete from grade where student_id=sid;  //Delete the student's record in the transcript
	update class set student_count=student_count-1 where id=cid;
end;  //Update the number of people in the class table

Declare means to create a variable (declare name type). There are two ways to assign values to variables:

  1. Usage method: set name=expr;
  2. Assignment using query statement: select col_name into name from table_name where condition;

cursor

When multiple results are found in the query statement in the stored procedure, the cursor (cursor) can be used to read the records in the query results one by one

1 declaration cursor
Format:

declare cursor_name CURSOR
for select_statement;

Where, cursor_name is the name of the cursor, select_statement is a query statement

2 open cursor
In MySQL, open is used to open the cursor. The format is:

open cursor_name;

3 using the cursor
fetch is used in MySQL to use the cursor. Format:

fetch cursor_name into var_name;

Where, var_name is the parameter name. Save the query result in the cursor into the parameter. There can be multiple parameters.

[example] query student data and output it

create procedure proc_query_student(IN sid int,OUT sname varchar(5),OUT cid int)
begin
	declare tmp_name varchar(5);  //Define variable tmp_name
	declare tmp_cid int;	//Define variable tmp_cid
	declare done int default 0;  //Define end flag
	//Create a cursor to query the student's name and class id
	declare cur_student CURSOR for select name,class_id from student where id=sid;
	//When the data in the cursor is read, done changes to 1
	declare continue handler for not found set done=1;
	open cur_student;	//open cursor 
	select done;		//Print done
	FETCH cur_student into tmp_name,tmp_cid;  //Place name and class in the cursor_ The ID is stored in the variable
	select done;		//Print done
	close cur_student;  //Close cursor
	set sname=tmp_name,cid=tmp_cid;  //Output value
end;

4 close cursor
Use close to close the cursor. Format:

close cur_name;

Process control

Control statements can also be used in stored procedures, including IF statements, CASE statements, LOOP statements, LEAVE statements, ITERATE statements, REPEAT statements, and WHILE statements

1 IF statement
[example] if the input age is greater than 20, the output age + 1 is equal to 20, the output age + 2, otherwise the output age + 3

create procedure proc_test_if(IN age int,OUT output int)
begin
	IF age>20 then set output=age+1;
	  ELSEIF age=20 then set output=age+2;
		ELSE set output=age+3;
	end if;
end;

2 CASE statement
[example] input age, if equal to 20, output age + 1, equal to 30, output age + 2, equal to 30, output age + 3

create procedure proc_test_loop(IN age int,OUT output int)
begin
	case age
		when 20 then set output=age+1;
		when 30 then set output=age+2;
		when 40 then set output=age+3;
end;

3 LOOP statement
Loop can implement a loop, and you need to jump out of the loop through leave
[example] from 1 to 100

create procedure proc_test_loop(IN input int,OUT output int)
begin
	set @count=0;	//System variable, set to 0
	add_num:LOOP	//Create a loop loop with the flag name add_num
		set @count=@count+1;
		select @count;	//Print count
		IF @count=100 then	 //Jump out of loop when count is added to 100
			LEAVE add_num;
		end if;
	end LOOP add_num;	//End cycle
end;

4. Leave statement
Leave is mainly used to jump out of the loop. Format: leave label
label is the cycle flag name

5 ITERATE statement
iterate is also mainly used to jump out of the loop, which means to jump out of this loop and enter the next loop
Format: iterate label
label is the cycle flag name

6 REPEAT statement
repeat stands for meeting certain conditions and jumping out of the loop
Format:

begin_label:repeat
	statement_list;
	until search_condition
end repeat begin_label

Where, begin_label is the flag name; statement_list is a circular body; search_condition is the condition to end the loop

[example] from 1 to 100

create procedure proc_test_loop(IN input int,OUT output int)
begin
	set @count=0;	//System variable, set to 0
	add_num:REPEAT	//Create a REPEAT loop with the flag name add_num
		SET @count=@count+1;
		UNTIL @count=100
	end REPEAT add_num;	//End cycle
end;

7 WHILE statement
while is also a conditional loop statement. while is a statement that executes in a loop when certain conditions are met
Format:

begin_label:WHILE search_condition DO
	statement_list;
end WHILE begin_label

Where, begin_label is the flag name; search_condition is the condition for starting the cycle;
statement_list is a circular body;

[example] from 1 to 100

create procedure proc_test_loop(IN input int,OUT output int)
begin
	set @count=0;	//System variable, set to 0
	add_num:WHILE @count<100 DO	//Execute when count is less than 100
		SET @count=@count+1;
	end WHILE add_num;	//End cycle
end;

Viewing stored procedures

There are three ways to view stored procedures

1. Mode I
Use show procedure status like 'pattern';
Where pattern is the name of the matching stored procedure, which can be omitted

You can query the stored procedure name, creation time, modification time and other information

2. Mode II
Use show create procedure proc_name; The creation statement of the stored procedure can be queried

3. Mode III
From information_ Query in the schema.routes table:
SELECT * FROM information_schema.Routines where ROUTINE_NAME='proc_name';
Where proc_name is the name of the stored procedure or function, and fuzzy query can also be supported

Delete stored procedure

Format: drop procedure proc_name;

Tags: MySQL

Posted on Mon, 20 Sep 2021 01:23:14 -0400 by Kibeth