MySQL stored procedures

Basic Query

First create a Students student table, add fields and insert data as follows

To create a stored procedure with no parameters

See the number of students

DROP PROCEDURE IF EXISTS select_students_count;  

DELIMITER;; CREATE PROCEDURE ' Select_students_count ' () BEGIN    select count (ID) from students; END;;D Elimiter;

Executing stored procedures

Call Select_students_count ();

Stored procedure with parameters

Based on Total city queries

DELIMITER;; CREATE PROCEDURE ' Select_students_by_city_count ' (in   _city varchar (225)) BEGIN    select count (ID) from students where city = _city; END;;D Elimiter;

Executing stored procedures

Call Select_students_by_city_count (' Hangzhou ');

Stored procedure with output parameters

Student information by name, return to student's city

DELIMITER;; CREATE PROCEDURE ' Select_students_by_name ' (   in _name varchar (225),   --Input parameters out   _city varchar (225),  - -Output parameter   inout _age int (one)       --Input and output parameters) BEGIN SELECT city from  students where name = _name and age = _age into _city; END;;D Elimiter;

Executing stored procedures

Set @_age = 20;set @_name = ' Jack '; call Select_students_by_name (@_name, @_city, @_age); Select @_city as City, @_age as age ;

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, as seen previously, as a series of SELECT statements that retrieve values and then save to the appropriate variable ( by specifying the INTO keyword)

Stored procedures with wildcard characters

Wildcard, with the corresponding wildcard character when assigning values to a parameter value

DROP PROCEDURE IF EXISTS select_students_by_likename;

DELIMITER;; CREATE PROCEDURE ' Select_students_by_likename ' (in   _likename varchar (225)  ) BEGIN    Set _likename = '%s% ' ;  --Default value     SELECT * from  students where name is like _likename; END;;D Elimiter;

Executing stored procedures

Call Select_students_by_likename ('%s% ');

Using stored procedures for pruning

New

New Student Information

DROP PROCEDURE IF EXISTS insert_student;

DELIMITER;; CREATE PROCEDURE ' insert_student ' (    _id int,    _name varchar (225),    _age int,    _city varchar (225)) BEGIN    INSERT into students (id,name,age,city) VALUES (_id,_name,_age,_city); END;;D Elimiter;

Executing stored procedures

Call Insert_student (5, ' Zhang San ', 19, ' Shanghai ');

One more piece of data in the table when you're done.

Modify

Update student information based on student ID

DROP PROCEDURE IF EXISTS update_student;

DELIMITER;; CREATE PROCEDURE ' update_student ' (    _id int,    _name varchar (225),    _age int,    _city varchar (225)) BEGIN    UPDATE  Students SET name=_name, Age=_age, city=_city where ID = _id; END;;D Elimiter;

Executing stored procedures

Call Update_student (5, ' Amy ', 22, ' Hangzhou ');

Delete

Delete A student record based on the ID

DROP PROCEDURE IF EXISTS delete_student_by_id;

DELIMITER;; CREATE PROCEDURE ' delete_student_by_id ' (    _id int) BEGIN    Delete from students where id = _id; END;;D Elimiter;

Executing stored procedures

Call DELETE_STUDENT_BY_ID (5);

The record with ID 5 in the students table is deleted.

MySQL stored procedures