What is a stored procedure

A stored procedure is a set of SQL statements designed to perform specific functions. It is created by compilation and stored in the database. Users can invoke and execute the stored procedure by specifying its name and parameters, similar to methods or functions in programming languages.

1.1 Advantages of stored procedures
  1. Stored procedures encapsulate SQL statements to enhance reusability.
  2. Stored procedures can hide complex business logic and business logic;
  3. Stored procedures support taking parameters and returning the result of an operation.
1.2 Disadvantages of stored procedures
  1. The stored procedure can perform poorly if the database is replaced to re-store the procedure;
  2. Stored procedures are difficult to debug and extend;
  3. Stored procedures cannot be analyzed using Explain.

The syntax of the stored procedure is as follows:

[Delimiter declaration terminator]create procedureStored procedure name ([stored procedure parameter])begin
    sqllogicend[Statement terminator] [delimiter;]Copy the code
  1. Statement terminators are generally used in SQL logic if there are; Is the closing SQL statement, then delimiter is used to define the declaration statement terminator. So that MySQL can correctly find the correct end of the stored procedure.
  2. Stored procedure parameters can be absent. If the stored procedure needs to return a value, it needs to define the parameter. If the stored procedure needs to get a value from outside, it needs to define the input parameter.

Such as:

  1. Define a stored procedure that computes the sum of two numbers:
delimiter //
create procedure num_sum(in num1 int.in num2 int ,out result int)
begin
    set result=num1+num2;
end
//
delimiter ;
Copy the code

In this stored procedure, the input parameters are num1 and num2. The parameter name is preceded by in to indicate the input parameter, and the parameter name is followed by the parameter type. Result is the output parameter, and you need to prefix the parameter name with out. Call this stored procedure using the call keyword:

call num_sum(10.20.@result);
select @result;
Copy the code

If you need to define variables in a stored procedure, use the DECLARE keyword and the set keyword for each variable assignment:

delimiter //
create procedure num_sum_add2(in num1 int.in num2 int ,out result int)
begin
    declare n int ;
    set n=num1+num2;
    set result=n+2;
end
//
delimiter ;
Copy the code