Simple use of MySQL stored procedures

First create a students table

The SQL script is as follows:

Create TableStudentsID int Primary KeyAuto_increment, ageint, namevarchar( -), Cityvarchar( -))character SetUTF8;Insert  intoStudentsValues(NULL, A,' Lisa ',' Hangzhou ');Insert  intoStudentsValues(NULL, -,' Rock ',' Shanghai ');Insert  intoStudentsValues(NULL, -,' Jack ',' Shenzhen ');Insert  intoStudentsValues(NULL, +,' Rose ',' Beijing ');

Stored procedures with no parameters

-- 查询学生个数dropprocedureifexists-- 替换分隔符    createprocedure select_students_count()         begin             selectcount(idfrom students;         end ;;delimiter ;

To execute a stored procedure:

call select_students_count();

Stored procedure with parameters

-- 根据城市查询总数delimiter ;;    createprocedure select_students_by_city_count(invarchar(255))        begin            selectcount(idfromwhere city = _city;        end;;delimiter ;

To execute a stored procedure:

call select_students_by_city_count('上海');

Stored procedure with output parameters

MySQL supports in (passed to stored procedures), out (outgoing from stored procedures), and inout (incoming and outgoing) parameters for stored procedures. The code for the stored procedure is in the begin and end statements, which are a series of SELECT statements that retrieve the value and then save it to the appropriate variable (via the INTO keyword)

-- 根据姓名查询学生信息，返回学生的城市delimiter ;;createprocedure select_students_by_name(    invarchar(255),    outvarchar(255-- 输出参数    int(11))    begin         selectfromwhereandinto _city;    end ;;delimiter ;

To execute a stored procedure:

set20;set'jack';call select_students_by_name(@_name, @_city, @_age);selectasas age;

Stored procedures with wildcard characters

delimiter ;;createprocedure select_students_by_likename(    invarchar(255))    begin        selectfromwherelike _likename;    end ;;delimiter ;

To execute a stored procedure:

call select_students_by_likename('%s%');call select_students_by_likename('%j%');

Adding, modifying, deleting with stored procedures increase

delimiter ;;createprocedure insert_student(    int,    varchar(255),    int,    varchar(255))    begin        insertinto students(idvalues(_id,_name,_age,_city);    end ;;delimiter ;

To execute a stored procedure:

call insert_student(5'张三'19'上海');

After execution, there is one more piece of data in the table, such as:

Modify

delimiter ;;createprocedure update_student(    int,    varchar(255),    int,    varchar(255))    begin        updatesetwhereid = _id;    end ;;delimiter ;

To execute a stored procedure:

call update_student(5'amy'22'杭州');

Delete

delimiter ;;createprocedure delete_student_by_id(    int)    begin        deletefromwhere id=_id;    end ;;delimiter ;

To execute a stored procedure:

call delete_student_by_id(5);

The record with ID 5 in the students table was successfully deleted. Such as:

Querying stored Procedures

To query all stored procedures:

selectfromwhere db='数据库名';

To query a stored procedure:

createprocedure 存储过程名;

Permanent update of this article address: https://github.com/nnngu/LearningNotes/blob/master/MySQL/01%20MySQL%20%E5%AD%98%E5%82%A8%E8%BF%87%E7% A8%8b%e7%9a%84%e7%ae%80%e5%8d%95%e4%bd%bf%e7%94%a8.md

Simple use of MySQL stored procedures