Simple writing of MYSQL stored procedures and functions

What is a stored procedure

Simply put, it is a set of SQL statements that are powerful and can implement some relatively complex logical functions, similar to the methods in the JAVA language;

ps: Stored procedures are somewhat similar to triggers, both are a set of SQL sets, but stored procedures are actively called and are more powerful than triggers. Triggers are automatically called after something is triggered;

What are the characteristics

There are input and output parameters, variables can be declared, and there are control statements such as if/else, case, while, etc. By writing stored procedures, complex logical functions can be implemented;

Common features of functions: modularity, encapsulation, and code reuse;

Fast speed, only the first execution needs to go through the compilation and optimization steps, and subsequent calls can be executed directly, eliminating the above steps;

Creating a MySQL stored procedure

grammar

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body

CREATE PROCEDURE procedure name([[IN|OUT|INOUT] parameter name data type[,[IN|OUT|INOUT] parameter name data type…]]) [characteristics…] procedure body

DELIMITER //
 CREATE PROCEDURE myproc(OUT s int)
  BEGIN
   SELECT COUNT(*) INTO s FROM students;
  END
  //
DELIMITER ;

Delimiter

MySQL uses ";" as the delimiter by default. If no delimiter is declared, the compiler will treat the stored procedure as an SQL statement, so the compilation process will report an error. Therefore, you must use "DELIMITER //" to declare the current segment delimiter in advance, so that the compiler will treat the content between the two "//" as the stored procedure code and will not execute this code; "DELIMITER ;" means to restore the delimiter.

parameter

A stored procedure may have input, output, and input/output parameters as needed. If there are multiple parameters, use "," to separate them. MySQL stored procedure parameters are used in the definition of stored procedures. There are three types of parameters: IN, OUT, and INOUT:

• The value of the IN parameter must be specified when calling the stored procedure. The value of the parameter cannot be returned if it is modified during the stored procedure. It is the default value.
• OUT: The value can be changed inside the stored procedure and can be returned
• INOUT: Specified when calling, and can be changed and returned

Among them, the sp_name parameter is the name of the stored procedure; proc_parameter represents the parameter list of the stored procedure; the characteristic parameter specifies the characteristics of the stored procedure; the routine_body parameter is the content of the SQL code, and BEGIN...END can be used to mark the beginning and end of the SQL code.

Each parameter in proc_parameter consists of 3 parts. These three parts are input and output types, parameter names and parameter types. Its form is as follows:

[ IN | OUT | INOUT ] param_name type

Among them, IN represents input parameters; OUT represents output parameters; INOUT means it can be both input and output; the param_name parameter is the parameter name of the stored procedure; the type parameter specifies the parameter type of the stored procedure, which can be any data type of the MySQL database.

The characteristic parameter has multiple values. The values ​​are as follows:

LANGUAGE SQL: indicates that the routine_body part is composed of statements in the SQL language, which is also the default language of the database system.

[NOT] DETERMINISTIC: Indicates whether the execution result of the stored procedure is deterministic. DETERMINISTIC means the result is certain. Each time you execute a stored procedure, the same input will result in the same output. NOT DETERMINISTIC means that the result is non-deterministic and the same input may produce different outputs. By default, the results are non-deterministic.

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }: Specifies restrictions on the use of SQL statements by the subroutine. CONTAINS SQL means that the subprogram contains SQL statements but does not contain statements for reading or writing data; NO SQL means that the subprogram does not contain SQL statements; READS SQL DATA means that the subprogram contains statements for reading data; MODIFIES SQL DATA means that the subprogram contains statements for writing data. By default, CONTAINS SQL is specified.

SQL SECURITY { DEFINER | INVOKER }: Specifies who has permission to execute. DEFINER means that only the definer can execute it; INVOKER means that the caller can execute it. By default, the system-assigned privilege is DEFINER.

COMMENT 'string': Comment information.

Tip: When creating a stored procedure, the system specifies CONTAINS SQL by default, indicating that SQL statements are used in the stored procedure. However, if no SQL statements are used in the stored procedure, it is best to set it to NO SQL. Moreover, it is best to make simple comments on the stored procedure in the COMMENT section to make it easier to read the stored procedure code later.

[Example 1] The following creates a stored procedure named num_from_employee. The code is as follows:

CREATE PROCEDURE num_from_employee (IN emp_id INT, OUT count_num INT ) 
     READS SQL DATA 
     BEGIN 
       SELECT COUNT(*) INTO count_num 
       FROM employee 
       WHERE d_id=emp_id; 
     END

In the above code, the name of the stored procedure is num_from_employee; the input variable is emp_id; and the output variable is count_num. The SELECT statement queries the employee table for records whose d_id value is equal to emp_id, uses COUNT(*) to calculate the number of records with the same d_id value, and finally stores the calculation result in count_num. The execution results of the code are as follows:

mysql> DELIMITER && 
mysql> CREATE PROCEDURE num_from_employee
(IN emp_id INT, OUT count_num INT ) 
  -> READS SQL DATA 
  -> BEGIN 
  -> SELECT COUNT(*) INTO count_num 
  -> FROM employee 
  -> WHERE d_id=emp_id; 
  -> END && 
Query OK, 0 rows affected (0.09 sec) 
mysql> DELIMITER ;

After the code is executed, if no error message is reported, it means that the storage function has been created successfully. You can call this stored procedure in the future, and the SQL statements in the stored procedure will be executed in the database.

Note: The default statement terminator in MySQL is a semicolon (;). SQL statements in stored procedures must be terminated by a semicolon. To avoid conflicts, first set the MySQL terminator to && using "DELIMITER &&". Finally, use "DELIMITER ;" to restore the end character to a semicolon. This is the same as when creating a trigger.

function

In MySQL, the basic form of creating a stored function is as follows:

CREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body
Among them, the sp_name parameter is the name of the stored function; func_parameter represents the parameter list of the stored function; RETURNS type specifies the type of return value; the characteristic parameter specifies the characteristics of the stored function, and the value of this parameter is the same as that in the stored procedure. Please refer to the content of Section 14.1.1; the routine_body parameter is the content of the SQL code, and BEGIN...END can be used to mark the beginning and end of the SQL code.

func_parameter can consist of multiple parameters, each of which consists of a parameter name and a parameter type in the following form: param_name type

The param_name parameter is the parameter name of the stored function; the type parameter specifies the parameter type of the stored function, which can be any data type of the MySQL database.

[Example 2] The following creates a stored function named name_from_employee. The code is as follows:

CREATE FUNCTION name_from_employee (emp_id INT ) 
     RETURNS VARCHAR(20) 
     BEGIN 
       RETURN (SELECT name 
       FROM employee 
       WHERE num=emp_id ); 
     END

In the above code, the name of the stored function is name_from_employee; the parameter of the function is emp_id; and the return value is of VARCHAR type. The SELECT statement queries the employee table for the record whose num value is equal to emp_id and returns the value of the name field of the record. The execution results of the code are as follows:

mysql> DELIMITER && 
mysql> CREATE FUNCTION name_from_employee (emp_id INT ) 
  -> RETURNS VARCHAR(20) 
  -> BEGIN 
  -> RETURN (SELECT name 
  -> FROM employee 
  -> WHERE num=emp_id ); 
  -> END&& 
Query OK, 0 rows affected (0.00 sec) 
mysql> DELIMITER ;

The result shows that the stored function has been created successfully. The usage of this function is the same as that of MySQL internal functions.

Using variables

In stored procedures and functions, you can define and use variables. Users can use the DECLARE keyword to define variables. Then you can assign values ​​to the variables. The scope of these variables is the BEGIN...END program segment. This section will explain how to define variables and assign values ​​to them.

1. Defining variables

In MySQL, you can use the DECLARE keyword to define variables. The basic syntax for defining a variable is as follows:

DECLARE var_name[,...] type [DEFAULT value]

Among them, the DECLARE keyword is used to declare variables; the var_name parameter is the name of the variable, and multiple variables can be defined here at the same time; the type parameter is used to specify the type of the variable; the DEFAULT value clause sets the default value of the variable to value. When the DEFAULT clause is not used, the default value is NULL.

[Example 3] The following defines the variable my_sql, with a data type of INT and a default value of 10. The code is as follows:

DECLARE my_sql INT DEFAULT 10;

2. Assigning values ​​to variables

In MySQL, you can use the SET keyword to assign values ​​to variables. The basic syntax of the SET statement is as follows:

SET var_name = expr [, var_name = expr] ...

Among them, the SET keyword is used to assign values ​​to variables; the var_name parameter is the name of the variable; and the expr parameter is the assignment expression. A SET statement can assign values ​​to multiple variables at the same time, and the assignment statements for each variable are separated by commas.

[Example 4] The following assigns a value of 30 to the variable my_sql. The code is as follows:

SET my_sql = 30;

In MySQL, you can also use the SELECT...INTO statement to assign values ​​to variables. Its basic syntax is as follows:

SELECT col_name[,…] INTO var_name[,…] FROM table_name WEHRE condition
Among them, the col_name parameter indicates the name of the queried field; the var_name parameter is the name of the variable; the table_name parameter refers to the name of the table; and the condition parameter refers to the query condition.

[Example 5] The following query is made for the record with id 2 from the employee table, and the d_id value of the record is assigned to the variable my_sql. The code is as follows:

SELECT d_id INTO my_sql FROM employee WEHRE id=2;

Defining conditions and handlers

Defining conditions and handlers is to define in advance the problems that may be encountered during program execution. And solutions to these problems can be defined in the handler. This approach can predict possible problems in advance and propose solutions. This can enhance the program's ability to handle problems and avoid abnormal program stops. In MySQL, conditions and handlers are defined using the DECLARE keyword. This section will explain in detail how to define conditions and handlers.

1. Define the conditions

In MySQL, you can use the DECLARE keyword to define conditions. Its basic syntax is as follows:

DECLARE condition_name CONDITION FOR condition_value 
condition_value: 
   SQLSTATE [VALUE] sqlstate_value | mysql_error_code

The condition_name parameter indicates the name of the condition; the condition_value parameter indicates the type of condition; and the sqlstate_value parameter and the mysql_error_code parameter both indicate MySQL errors. For example, in ERROR 1146 (42S02), the sqlstate_value value is 42S02 and the mysql_error_code value is 1146.

[Example 6] The following defines the error "ERROR 1146 (42S02)" and names it can_not_find. It can be defined in two different ways, the code is as follows:

//Method 1: Use sqlstate_value 
DECLARE can_not_find CONDITION FOR SQLSTATE '42S02' ; 
//Method 2: Use mysql_error_code 
DECLARE can_not_find CONDITION FOR 1146;

2. Defining Handlers

In MySQL, you can use the DECLARE keyword to define handlers. Its basic syntax is as follows:

DECLARE handler_type HANDLER FOR 
condition_value[,...] sp_statement 
handler_type: 
  CONTINUE | EXIT | UNDO 
condition_value: 
  SQLSTATE [VALUE] sqlstate_value |
condition_name | SQLWARNING 
    | NOT FOUND | SQLEXCEPTION | mysql_error_code

The handler_type parameter specifies how to handle errors, and it has three possible values. The three values ​​are CONTINUE, EXIT and UNDO. CONTINUE means that no processing is performed when an error occurs and the execution continues; EXIT means that the execution is terminated immediately when an error occurs; UNDO means that the previous operation is withdrawn when an error occurs. MySQL does not currently support this processing method.

Note: Normally, if an error occurs during execution, you should stop executing the following statements immediately and undo the previous operations. However, MySQL does not currently support UNDO operations. Therefore, it is best to perform an EXIT action when an error is encountered. If the error type can be predicted in advance and handled accordingly, the CONTINUE operation can be performed.

The condition_value parameter indicates the error type and has six possible values. sqlstate_value and mysql_error_code have the same meaning as in the condition definition. condition_name is the name of the condition defined by DECLARE. SQLWARNING represents all sqlstate_value values ​​that begin with 01. NOT FOUND represents all sqlstate_value values ​​that begin with 02. SQLEXCEPTION represents all sqlstate_value values ​​that are not captured by SQLWARNING or NOT FOUND. sp_statement represents the execution statements of some stored procedures or functions.

[Example 7] Below are several ways to define handlers. The code is as follows:

//Method 1: Capture sqlstate_value 
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SET @info='CAN NOT FIND'; 
//Method 2: Capture mysql_error_code 
DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND'; 
//Method 3: Define the condition first, then call DECLARE can_not_find CONDITION FOR 1146; 
DECLARE CONTINUE HANDLER FOR can_not_find SET 
@info='CAN NOT FIND'; 
//Method 4: Use SQLWARNING 
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR'; 
//Method 5: Use NOT FOUND 
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND'; 
//Method 6: Use SQLEXCEPTION 
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

The above code shows 6 ways to define handlers.

The first method is to capture the sqlstate_value value. If the sqlstate_value is 42S02, the CONTINUE operation is executed and the "CAN NOT FIND" message is output.

The second method is to capture the mysql_error_code value. If the mysql_error_code value is 1146, the CONTINUE operation is executed and the "CAN NOT FIND" message is output.

The third method is to define the condition first and then call the condition. Here we first define the can_not_find condition, and execute the CONTINUE operation when an 1146 error occurs.

The fourth method is to use SQLWARNING. SQLWARNING captures all sqlstate_value values ​​that begin with 01, then executes the EXIT operation and outputs the "ERROR" message.

The fifth method is to use NOT FOUND. NOT FOUND captures all sqlstate_value values ​​that begin with 02, then executes the EXIT operation and outputs the "CAN NOT FIND" message.

The sixth method is to use SQLEXCEPTION. SQLEXCEPTION captures all sqlstate_value values ​​that are not captured by SQLWARNING or NOT FOUND, then performs an EXIT operation and outputs an "ERROR" message.

Summary of MySQL stored procedure writing

1. Create a stored procedure without parameters.

create procedure product()
begin
    select * from user;
end;

A simple stored procedure creation statement. The calling statement is:

call procedure();

##Note that if you write it in the command line, this way of writing will result in a syntax error, that is, select that sentence to end
mysql will explain it, and you should change the ending character first:

delimiter //
create procedure product()
begin
    select * from user;
end //

Finally change it back

delimiter ;

2. Create a stored procedure with parameters

The storage with parameters includes two parameters:
One is the incoming parameter;
One is the outgoing parameter;
For example, a stored procedure:

create procedure procedure2(
out p1 decimal(8,2),
out p2 decimal(8,2),
in p3 int
)
begin
select sum(uid) into p1 from user where order_name = p3;
select avg(uid) into p2 from user;
end ;

From the above SQL statements, we can see that p1 and p2 are used to retrieve and pass out values, while p3 must have a specific value passed in to call it.
See the specific calling process:

call product(); //No parameters
call procedure2(@userSum,@userAvg,201708); //with parameters

When used up, you can directly query the values ​​of userSum and userAvg:

select @userSum, @userAvg;

The results are as follows:

+----------+----------+
| @userSum | @userAvg |
+----------+----------+
| 67.00 | 6.09 |
+----------+----------+
1 row in set (0.00 sec)

3. Delete the stored procedure

One statement: drop procedure product; //No brackets after it

4. A complete stored procedure example:

-- Name: drdertotal 
-- Parameters : onumber = order number 
-- taxable = 0 if not taxable, 1 if taxable 
--ototal = order total variable 
 
create procedure ordertotal( 
in onumber int, 
in taxable boolean, 
out ototal decimal(8,2) 
) commit 'Obtain order total, optionally adding tax'  
begin 
  -- Declare variable for total 
  declare total decimal(8,2); 
  -- Declare tax percentage 
  declare taxrate int default 6; 
   
  --Get the order total 
  select Sum(item_price*quantity) 
  from orderitems 
  where order_num = onumber 
  into total; 
   
  --Is this taxable? 
  if taxable then 
    --Yes, so add tax rate to the total 
    select total+(total/100*taxrate) into total; 
  end if; 
   
  --Add finally, save to out variable 
  select total into ototal; 
end;

The above stored procedure is similar to the business processing of high-level languages. It is not difficult to understand it. Pay attention to the details of the writing.

commit keyword: It is not required, but if given, will be given in the results of show procedure status.

if statement: This example shows the basic usage of mysqlif statement. if statement also supports elseif and else clauses.

You can use show procedure status to list all stored procedures in detail, and you can add a

Like+specify the filtering mode to filter.