MySQL stored procedure advanced SQL statement summary

This article brings you relevant knowledge about mysql, which mainly introduces the relevant content about advanced SQL statements of stored procedures. Let’s take a look at it together. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

MySQL advanced SQL statements (stored procedures)

1. Overview of stored procedures

1.1 What is a stored procedure

A stored procedure is a set of SQL statements designed to accomplish a specific function.
During the use of stored procedures, commonly used or complex tasks are written in advance using SQL statements and stored with a specified name. This process is compiled and optimized and stored in the database server. When you need to use this stored procedure, you just need to call it. Stored procedures are faster and more efficient in execution than traditional SQL.

1.2 Advantages of stored procedures

1. After execution once, the generated binary code will reside Buffer to improve execution efficiency.
2. A collection of SQL statements plus control statements, with high flexibility.
3. It is stored on the server side and reduces the network load when called by the client.
4. Can be called multiple times and can be modified at any time without affecting client calls.
5. Can complete all database operations and control the information access permissions of the database.

2. Create, call and view stored procedures

2.1 Create stored procedures

delimiter $$                 #将语句的结束符号从分号；临时改为两个$$（可以自定义）
create procedure proc ()     #创建存储过程，过程名为proc，不带参数
-> begin                     #过程体以关键字begin开始
-> select * from store_info; #过程体语句
-> end $$                    #过程体以关键字end结束

delimiter ;                   #将语句的结束符号恢复为分号

Example:

delimiter $$create procedure proc01 ()-> bengin
-> create table student(id int,name char(10),age int);-> insert into student values(1,'zhangsan',18);-> insert into student values(2,'lisi',18);-> select * from student;-> end $$delimiter ;

##2.2 Calling and viewing stored procedures

call proc;               #调用存储过程



show create procedure [数据库.]存储过程名;  #查看某个存储过程的具体信息

show create procedure proc;
show create procedure proc\G

show procedure status [like '%proc%'] \G

Example:

call proc01;   

 show create procedure proc01;
 show create procedure proc01\G       #查看存储过程的具体信息
 
 show procedure status like '%Proc01%'\G

##2.3 Delete stored procedures

 #存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。
drop procedure if exists proc;
 #仅当存在时删除，不添加If EXISTS 时，如果指定的过程不存在，则产生一个错误。

Example:

drop procedure if exists proc01;

3. Parameters of the stored procedure

IN input parameters
• : Indicates that the caller passes a value to the process. (The incoming value can be a literal or a variable)
OUT output parameter
• : Indicates that the procedure passes the value to the caller. (Multiple values ​​can be returned) (The outgoing value can only be a variable)
INOUT input and output parameters
• : It means that the caller passes the value to the process, and the process passes the value to the caller. Out value. (The value can only be a variable)
Note: Variable names cannot contain underscores.

delimiter $$create procedure proc2(in stuname char(20))  #参数为stuname，数据类型一定要与下面的where语句后字段的数据类型相同-> begin
-> select * from student where name = stuname;-> end $$delimiter ;call proc2('zhangsan');       #调用存储过程，并传入参数‘zhangsan’

4. Control statements of stored procedures

4.1 Conditional statement: if-then-else … end if

 delimiter $$  
 create procedure proc03(in innum int)   #创建存储过程proc03，参数为innum，类型为int 
 -> begin 
 -> declare var int;         #定义变量var为int类型 
 -> set var=innum*2;         #变量var的值等于传入的参数值乘2
 -> if var>=10 then          #当var的值大于10时，id值会加1，否则减1
 -> update ss set id=id+1;  
 -> else 
 -> update ss set id=id-1;  
 -> end if;  
 -> end $$
  
 delimiter ;
 
 call proc03(8);    #调用存储过程，并传入参数8
 
 call proc03(3);    #调用存储过程，并传入参数3

##4.2 Loop statement: while ·· ·· end while

 delimiter $$                   #修改默认结束符为$$
 create procedure proc05()      #创建存储过程proc04
 -> begin                       #过程体以关键字begin开始
 -> declare var int(10);        #定义变量var为int类型
 -> set var=0;                  #var的起始值为0
 -> while var insert into ss values(var);  
 -> set var=var+1;              #每次循环后var值自增1
 -> end while;                  #结束while循环
 -> end $$                      #创建存储过程结束
 
 delimiter ;                    #重新修改默认结束符为原始的;
 
 call proc05;                   #调用存储过程proc04

Recommended learning: mysql video tutorial

The above is the detailed content of MySQL stored procedure advanced SQL statement summary. For more information, please follow other related articles on the PHP Chinese website!