preface

In this chapter we will learn about stored procedures in databases, what stored procedures are, and how to create and invoke stored procedures in MySQL.

What is a stored procedure

In a database system, Stored Procedure is a set of SQL statements to complete a specific function. It is Stored in the database and lasts forever after compilation. The user calls the Stored Procedure and passes parameters to execute it.

Advantages and disadvantages of stored procedures

Advantages of stored procedures:

  1. Efficient execution Stored procedures are already compiled in the database and can be executed directly, unlike SQL statements that need to be compiled.
  2. Secure stored procedures pass in data through parameters, avoiding SQL injection attacks, and allowing stored procedures to be configured for use only by specific users.
  3. Reducing network traffic When invoking a stored procedure, you only need to pass the name and parameters of the called stored procedure instead of sending SQL statements. Therefore, the network traffic is reduced and the invocation speed is improved.

Disadvantages:

  1. The development and maintenance are troublesome, and the stored procedures are stored in the database. The development and maintenance need to be completed separately, which increases the development cost
  2. Debugging is cumbersome, development tools do not support debugging stored procedures
  3. Cache not supported
  4. No distributed support

Therefore, the proper use of stored procedures can improve the performance of the program, if a large number of abuse is detrimental to system development, need to use according to the situation.

Syntax for stored procedures

Create a view

Delimiter / / create procedure stored procedure name ([in | out | inout] parameter name type...). Begin SQL statement; end// delimiter ;Copy the code

Grammar description:

  1. Delimiter // sets the end symbol of SQL codes to //. Multiple SQL statements in stored procedures end with semicolons (;). If you define stored procedures with semicolons (;), it is confusing.
  2. [in | out | inout] before the parameters can be added in and out, and inout parameters set the type of the keyword: in on behalf of the input parameters, used for incoming data, the default out on behalf of the output parameters, is used to return data, similar to the effect of the return inout on behalf of the input and output can be

Delete the view

Drop PROCEDURE Name;Copy the code

Calling a stored procedure

Call stored procedure name (parameter)Copy the code

Code examples:

Drop procedure if exists pd_select_student; Delimiter // create PROCEDURE pd_select_student() begin SELECT * from tb_student; end// delimiter ; Call pd_select_student();Copy the code

Drop procedure if exists pd_select_student_by_name; delimiter // create procedure pd_select_student_by_name(s_name nvarchar(20)) begin select * from tb_student where stu_name = s_name; end// delimiter ; Call pd_select_student_by_name(' select_student_by_name ');Copy the code

Drop PROCEDURE if exists pd_update_student; delimiter // create procedure pd_update_student(s_id int,s_name varchar(20),s_gender varchar(20),s_age int) begin update  tb_student set stu_name = s_name,stu_gender=s_gender,stu_age=s_age where stu_id = s_id; end// delimiter ; Call pd_update_student(1,' update_student ',' girl ',18);Copy the code

Stored procedure with return value

A stored procedure can also return a value to the caller by:

  1. Define the output parameters with OUT
  2. Inside the stored procedure, use into to assign the query result to the output parameter
  3. Before calling a stored procedure, define variables as output parameters
Create PROCEDURE PD_get_count_by_age (min_age int,max_age int,out count int) begin -- Select count(*) into count from tb_student where stu_age between min_age and max_age; select count(*) from tb_student where stu_age between min_age and max_age; end// delimiter ; Set @stu_count = 0; Call pd_get_count_by_age(10,80,@stu_count); Select @stu_count 'number of people ';Copy the code

conclusion

Stored procedures can encapsulate multiple SQL statements, save them in the database, and be invoked repeatedly, which improves execution efficiency and security. As a common database technology in software development, a large number of stored procedures will also bring high maintenance costs to the project, so you need to use them according to the situation. If you find this article useful, please give it a thumbs up 🙂