MySQL variables, stored procedures, and functions

[TOC]

The foreword 0.

This article is a summary of notes and opinions after watching the Video of Shang Silicon Valley in MASTER Kong’s B station

Click the jump

1. The variable

In the stored procedures and functions of the MySQL database, variables can be used to store intermediate result data for queries or calculations, or to output final result data.

In the MySQL database, variables are divided into system variables and user-defined variables.

1.1 System Variables

Variables are defined by the system, not by users, and belong to the server level. During MySQL service startup and MySQL service instance generation, MySQL assigns values to system variables in MySQL server memory that define properties and characteristics of the current MySQL service instance.

System variables can be divided into global system variables (global) and session system variables (session).

1.1.1 Viewing All System Variables

View all system variables

show global variables;  # global
show session variables;  # session
show variables;  # Do not write Defaults to session
Copy the code

View some system variables

show variables like 'character_%';
show global variables like 'admin_%';
Copy the code

1.1.2 Viewing specified System Variables (`@ @`)

As the MySQL coding specification, system variables in MySQL start with two “@”, where “@@global” is only used to mark global system variables, and “@@session” is only used to mark session system variables. “@@” marks the session system variable first, and if the session system variable does not exist, it marks the global system variable.

Check the value of the specified system variable
SELECT@@global. Variable name;# check the value of the specified session variable
SELECT@@session. Variable name;# or
SELECT@@ Variable name;Copy the code

1.1.3 Changing the Value of a System Variable

Method 1: Modify the MySQL configuration file and then change the value of the MySQL system variable (this method requires restarting the MySQL service).
Method 2: When the MySQL service is running, run the set command to reset the values of system variables

	Assign to a system variable
	# Method 1:
	SET@@global. Variable name = variable value;# Method 2:
	SET GLOBALVariable name = variable value;Assign to a session variable
	# Method 1:
	SET@@session. Variable name = variable value;# Method 2:
	SET SESSIONVariable name = variable value;Copy the code

For example:

  Example global system variables
	set @@global.max_connections = 161;
	set global max_connections = 171;
	select @@global.max_connections;
	This is valid for the current database instance, and is invalid once the mysql service is restarted

	Example session system variables
	set @@session.character_set_client = 'utf8';
	set session character_set_client = 'utf8';
	# is valid for the current session, but is invalid once the session ends and a new connection is established
Copy the code

1.2 User-defined Variables

User variables are user-defined. As the MySQL coding specification, user variables in MySQL start with an “@”. According to different scope, it can be divided into session user variable and local variable.

Session user variables: Scoped like session variables, onlyThe current connectionThe session is valid.
Local variables: only inBEGIN 和 ENDStatement block. Local variables can only be inStored procedures and functionsThe use of.

1.2.1 Session User Variables

Definition of variables

1) Declaration and assignment of variables# Method 1: "=" or ":="
SET@user variable = value;SET@user variable := value;# Method 2: ":=" or INTO keyword
SELECT@user variable := expression [FROMDengZi];SELECTexpressionINTO@user variable [FROMDengZi]; 2) useselect@ the variable nameCopy the code

View the value of a user variable (view, compare, calculate, etc.)

SELECT@user variableCopy the code

For example,

SET @a = 1;

SELECT @a;
Copy the code

SELECT @num: =COUNT(*) FROM employees;

SELECT @num;
Copy the code

SELECT AVG(salary) INTO @avgsalary FROM employees;

SELECT @avgsalary;
Copy the code

SELECT @big;  When you view an undeclared variable, you get a NULL value
Copy the code

1.2.2 Local variables

Definition: You can define a local variable using the DECLARE statement
Scope: just define it in BEGIN… The END of the effective
Position: can only be placed in BEGIN… “END” and only in the first sentence

BEGIN
	Declare local variables
	DECLAREThe variable name1Variable data type [DEFAULTVariable default value];DECLAREThe variable name2The variable name3. Variable data type [DEFAULTVariable default value];# Assign values to local variables
	SETThe variable name1= value;SELECT 值 INTOThe variable name2 [FROMClause];Check the value of the local variable
	SELECTvariable1, the variable2, the variable3;
END
Copy the code

1. Define variables

DECLAREVariable name type [defaultValue];If there is no DEFAULT clause, the initial value is NULL
Copy the code

For example:

DECLAREmyparamINT　DEFAULT 100;
Copy the code

2. Variable assignment

Method 1: Generally used to assign simple values

SETVariable name = value;SETVariable name := value;Copy the code

Method 2: Generally used to assign field values in the table

SELECTField name or expressionINTOThe variable nameFROMTable;Copy the code

3. Use variables (view, compare, calculate, etc.)

SELECTLocal variable name;Copy the code

I won’t give you an example here, because it involves the use of stored procedures and functions, so I’ll give you an example after I finish explaining these two parts.

1.2.3 contrast

	scope	Define the location	grammar
Session user variable	The current session	Anywhere in the session	Add the @ sign without specifying the type
A local variable	Define it in BEGIN END	BEGIN the first sentence	Usually, you don’t need to add @, you need to specify the type

2. Stored procedures

MySQL supports stored procedures and functions since version 5.0. Stored procedures and functions can encapsulate complex SQL logic together, and applications need not pay attention to the complex SQL logic inside stored procedures and functions, but simply call stored procedures and functions.

2.1 Overview of Stored Procedures

Definition: The English word for Stored Procedure is Stored Procedure. The idea is simple: a package of pre-compiled SQL statements.

There has been a lot of controversy over the use of stored procedures. For example, some companies require the use of stored procedures for large projects, while others explicitly prohibit the use of stored procedures in their manuals. Why do these companies have such different requirements for the use of stored procedures?

2.1.1 the pros and cons

advantages

**1. Stored procedures can be compiled and used more than once. ** Stored procedures are compiled only at creation time and do not need to be recompiled for subsequent use, which improves the efficiency of SQL execution.

**2, can reduce the development workload. ** Packaging code into modules is actually one of the core ideas of programming. It allows complex problems to be broken down into different modules, which can then be reused, reducing the development workload and ensuring the structure of the code is clear.

**3. Strong security of stored procedures. ** When we set up the stored procedure, we can set the permission to use the user, which has strong security like the view.

**4, can reduce network transmission. ** Because the code is wrapped in a stored procedure, you only need to call the stored procedure each time you use it, which reduces network traffic.

**5, good encapsulation. ** When performing relatively complex database operations, what used to be a one-by-one SQL statement that might require multiple database connections is now a stored procedure that requires only one connection.
disadvantages

Based on these advantages, many large companies, such as Microsoft and IBM, require the use of stored procedures for large projects. But ali in China does not recommend developers to use stored procedures, why?

Ali development specification

[Mandatory] Do not use stored procedures. Stored procedures are difficult to debug, expand, and transplant.

Although stored procedures have benefits such as the above, the disadvantages are also obvious.

**1. Poor portability. ** Stored procedures cannot be ported across databases. For example, stored procedures written in MySQL, Oracle, or SQL Server need to be rewritten when they are moved to another database.

**2. Debugging is difficult. ** Only a few DBMSS support debugging of stored procedures. Complex stored procedures are not easy to develop or maintain. There are third-party tools that can debug stored procedures for a fee, though.

**3. Versioning stored procedures is difficult. ** For example, if the index of a data table changes, stored procedures may be invalidated. When we develop software, we often need to do version management, but the stored procedure itself does not have version control, and the iteration of version update is troublesome.

**4, it is not suitable for high concurrency scenarios. ** High concurrency scenarios need to reduce the pressure on the database. Sometimes the database will adopt a separate database and separate tables approach, and the scalability requirements are high. In this case, the stored procedures will become difficult to maintain, increasing the pressure on the database, obviously not suitable.

In short, stored procedures are both convenient and limited. Although attitudes to stored procedures vary from company to company, for us developers, mastering stored procedures is a must-have skill, no matter what.

2.1.2 classification

The parameter types of stored procedures can be IN, OUT, and INOUT. Based on this, the classification is as follows:

1, no parameters (no parameters, no return)
2, only IN type (if there is an argument, no return)
3, only OUT type (no argument, return)
4, with both IN and OUT (with arguments and returns)
5, with INOUT (with parameters and return)

Note: IN, OUT, and INOUT can all take more than one IN a stored procedure.

2.2 Creating and calling stored Procedures

2.2.1 Syntax analysis

Grammar:

CREATE PROCEDUREStored procedure name (IN|OUT| INOUT parameters of types,...). [characteristics ...]BEGINStored procedure bodyEND
Copy the code

Similar to the Java method:

Return type method name (parameter type parameter name,...) {method body; }Copy the code

Description:

1. The meaning of the symbol before the parameter

IN: The current parameter is the input parameter, that is, represents the input parameter;

The stored procedure simply reads the value of this parameter. If no parameter type is defined, the default is IN, which represents the input parameter.
OUT: The current parameter is the output parameter, that is, the output parameter.

After execution, the client or application calling the stored procedure can read the value returned by this parameter.
INOUT: The current parameter can be an input parameter or an output parameter.

The parameter type can be any type in the MySQL database.

Characteristics Indicates the constraints on the stored procedure specified during the creation of the stored procedure. The values are as follows:

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
Copy the code

LANGUAGE SQLThe execution body of a stored procedure consists of SQL statements. The system supports SQL.
[NOT] DETERMINISTIC: Indicates whether the result of the stored procedure execution is determined. DETERMINISTIC means the result is DETERMINISTIC. The same input produces the same output each time the stored procedure is executed. NOT DETERMINISTIC means that the result is indeterminate, the same input may get different outputs. If no value is specified, the default is NOT DETERMINISTIC.
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }: Specifies restrictions on the use of SQL statements by subroutines.
- CONTAINS SQL indicates that the subroutine of the current stored procedure CONTAINS SQL statements, but does not contain SQL statements that read or write data.
- NO SQL indicates that the subroutine of the current stored procedure does not contain any SQL statements.
- READS SQL DATA represents the SQL statement that contains read DATA in the subroutine of the current stored procedure;
- MODIFIES SQL DATA indicates that the subroutine of the current stored procedure contains SQL statements to write DATA.
- By default, the system specifies CONTAINS SQL.
SQL SECURITY { DEFINER | INVOKER }: Permission to execute the current stored procedure, which indicates which users can execute the current stored procedure.
- DEFINERIndicates that only the creator or definer of the current stored procedure can execute the current stored procedure.
- INVOKERIndicates that users who have access to the current stored procedure can execute the current stored procedure.
- If no associated values are set, MySQL specifies DEFINER by default.
COMMENT 'string': Comments information that can be used to describe stored procedures.

4. The stored procedure body can contain multiple SQL statements. If there is only one SQL statement, omit BEGIN and END

Writing a stored procedure is not simple, and it may require complex SQL statements.

1. BEGIN...END:BEGIN...ENDIt contains multiple statements, each beginning with (;). Sign is the end character. 2.DECLARE:DECLAREIt is used to declare variables in the location whereBEGIN...ENDThe variable is declared in the middle of a statement and needs to be declared before any other statement is used.3. SET: Assignment statement, used to assign a value to a variable.4. SELECT...INTO: Stores the result of a query from a table into a variable, i.e. assigning a value to a variable.Copy the code

5. You need to set a new end tag

DELIMITER New end tagCopy the code

MySQL’s default statement terminator is semicolon ‘; ‘. To avoid conflicts with SQL statement terminators in stored procedures, DELIMITER is used to change the stored procedure terminators.

For example, the “DELIMITER //” statement sets the MySQL terminator to // and ends the stored procedure with “END //”. Use “DELIMITER;” after the stored procedure is defined. Restores the default end character. DELIMITER can also specify other symbols as terminators.

When using the DELIMITER command, you should avoid the backslash (‘ \ ‘) character because the backslash is the MySQL escape character.

Example:

DELIMITER $

CREATE PROCEDUREStored procedure name (IN|OUT| INOUT parameters of types,...). [characteristics ...]BEGIN
	sqlstatements1; SQL statements 2;END $
Copy the code

2.2.2 Call Format

Stored procedures have multiple invocation methods. Stored procedures must be called using the CALL statement, and the stored procedure is related to the database. If you want to execute stored procedures in other databases, you need to specify the database name, for example, CALL dbname.procName.

CALLStored procedure name (argument list)Copy the code

Format:

1. Call in mode parameters:

CALL sp1('value');
Copy the code

2, Call out mode parameters:

SET @name;
CALL sp1(@name);
SELECT @name;
Copy the code

3, Call inout mode parameters:

SET @name= value;CALL sp1(@name);
SELECT @name;
Copy the code

2.2.3 Code examples

The preparatory work

# 0. Preparations
create database dbtest15;
use dbtest15;

create table employees 
as 
select * from atguigudb.employees;

create table departments
as 
select * from atguigudb.departments;

select * from employees;
select * from departments;
Copy the code

Type 1: empty parameter

Example 1: Create select_all_data() and check all data in the EMps table

delimiter $
create procedure select_all_data()
begin
		select * from employees;
end $
delimiter ;

call select_all_data();
Copy the code

Example 2: Create the stored procedure avg_employee_salary() that returns the average salary of all employees

delimiter //
create procedure avg_employee_salary()
begin
		select avg(salary) from employees;
end //
delimiter ;

call avg_employee_salary();
Copy the code

Example 3: Create the stored procedure show_max_salary() to view the highest salary value in the emps table.

delimiter //
create procedure show_max_salary()
begin
		select max(salary) from employees;
end //
delimiter ;

call show_max_salary();
Copy the code

Type 2: Bring out

Example 4: Create the stored procedure show_min_salary() and view the lowest salary in the EMps table. And output the minimum salary with the OUT parameter “ms”

delimiter //
create procedure show_min_salary(out ms double)
begin
   select min(salary) into ms from employees;
end //
delimiter ;
# call
call show_min_salary(@ms);
Check the value of the variable
select @ms;
Copy the code

Type 3: With in

Example 5: Create the stored procedure show_someone_salary(), check the salary of an employee IN the “emps” table, and enter the employee name with the IN parameter empname.

delimiter //
create procedure show_someone_salary(in empname varchar(20))
begin
   select salary from employees 
	 where last_name = empname; 
end//
delimiter ;

Call method 1:
call show_someone_salary('Abel');
# 2:
set @empname = 'Abel';
# set @empname := 'Abel'; := assignment symbol
call show_someone_salary(@empname);
Copy the code

Type 4: With in and out

Example 6: Create the stored procedure show_someone_salary2() and view the salary of an employee IN the “emps” table. Input the employee name with the IN parameter empname and output the employee salary with the OUT parameter empsalary.

delimiter //
create procedure show_someone_salary2(in empname varchar(20),out empsalary double)
begin
      select salary into empsalary from employees
      where last_name = empname;
end//
delimiter ;
# call
set @empname = 'Abel';
call show_someone_salary2(@empname,@empsalary);

select @empsalary;
Copy the code

Type 5: With Inout

Example 7: Create the stored procedure show_mgr_name() to query the name of an employee’s leader. Use the INOUT parameter empname to enter the employee’s name and output the leader’s name.

delimiter //
create procedure show_mgr_name(inout empname varchar(20))
begin
		select last_name into empname from employees
		where employee_id in 
		(select manager_id from employees where last_name = empname);
end//
delimiter ;

# call
set @empname = 'Abel';
call show_mgr_name(@empname);
select @empname;

Copy the code

3. Store functions

MySQL itself provides many functions, such as date functions, string functions and so on. These functions can be used for various data processing operations, greatly improve the user management efficiency of the database. MySQL supports custom functions. After they are defined, they can be called in the same way as the system functions predefined by MySQL.

3.1 Creating a Syntax

Syntax format:

CREATE FUNCTIONFunction name (parameter name Parameter type...)RETURNSReturn type [characteristics...]BEGINThe body of the functionThere must be a RETURN statement in the function body

END
Copy the code

Description:

Parameter list: Specifying arguments as IN, OUT, or INOUT is only legal for PROCEDURE. FUNCTION always defaults to IN arguments.

RETURNS type statement specifies the type of data returned by the function.

The RETURNS clause can only specify functions, which is mandatory for functions. It specifies the RETURN type of the function, and the function body must contain a RETURN value statement.

3, the constraints on the function specified when the function is created. The value is the same as when the stored procedure is created.

4, The function body can also use BEGIN… END represents the beginning and END of the SQL code. If the function body has only one statement, you can also omit BEGIN… The END.

Note:

If there is an error message “You might want to use the less safe log_bin_trust_function_creators variable” in the creators of the storage function, there are two troubleshooting methods:

Way 1: add the necessary function features “[NOT] DETERMINISTIC” and “{the CONTAINS SQL SQL | | NO READS SQL DATA | MODIFIES SQL DATA}”

Method 2:
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
Copy the code

3.2 Call Syntax

In MySQL, storage functions are used in the same way as internal MySQL functions. In other words, user-defined storage functions are identical to MySQL internal functions. The difference is that the storage functions are user-defined, while the internal functions are defined by the MySQL developers.

SELECTFunction name (argument list)Copy the code

3.3 Code Examples

Example 1: Create a storage function named email_by_name() with an empty parameter. This function queries the email address of an Abel and returns the data type as a string.

delimiter //
create function email_by_name()
returns varchar(25)
begin
		return	(select email from employees where last_name = 'Abel');
end //
delimiter ;

select email_by_name();
Copy the code

Example 2: Create a storage function with the name email_by_id() and the parameter emp_id. The function queries the email address of emp_id and returns a string of data.

delimiter //
create function email_by_id(emp_id int)
returns varchar(25)
begin
	   return(select email from employees where employee_id = emp_id);
end //
delimiter ;

select email_by_id(100);

set @emp_id = 102;
select email_by_id(@emp_id);
Copy the code

Example 3: Create the storage function count_by_id(). Dept_id is passed in. The function queries the number of employees in dept_id and returns an integer.

delimiter //
create function count_by_id(dept_id int)
returns int
begin
		return (select count(*) from departments where department_id = dept_id);
end //
delimiter ;

set @dept_id = 50;
select count_by_id(@dept_id);
Copy the code

4. View, modify, and delete stored procedures and functions

4.1 check the

MySQL stores the STATUS information of stored procedures and functions. Users can use the SHOW STATUS statement or SHOW CREATE statement to view the STATUS information, or directly query the STATUS information from the information_SCHEMA database of the system. Here are three methods.

1. Run the SHOW CREATE statement to view the creation information about stored procedures and functions

The basic syntax is as follows:

SHOW CREATE {PROCEDURE | FUNCTION} Name of the stored procedure or functionCopy the code

For example:

show create procedure show_mgr_name\G
Copy the code

2. Run the SHOW STATUS statement to view the STATUS of stored procedures and functions

The basic syntax is as follows:

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
Copy the code

This statement returns the characteristics of the subroutine, such as database, name, type, creator, and creation and modification dates.

[LIKE ‘pattern’] : matches the name of a stored procedure or function and can be omitted. When omitted, all stored procedures or functions that exist in the MySQL database are listed. For example, the code for the SHOW STATUS statement is as follows:

3. View information about stored procedures and functions from the information_schema.Routines table

Information about stored procedures and functions in MySQL is stored in the Routines table under the INFORMATION_SCHEMA database. You can query information about stored procedures and functions by querying the records in this table. The basic syntax is as follows:

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='Name of stored procedure or function' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];
Copy the code

Note: If a stored procedure has the same name as a function in the MySQL database, it is best to specify a ROUTINE_TYPE query condition to specify whether the procedure or function is being queried.

For example:

4.2 to modify

Modifying a stored procedure or function does not affect the functions of the stored procedure or function, but only changes related features. Use the ALTER statement.

ALTER {PROCEDURE | FUNCTION} Names of stored procedures or functions [characteristic...]Copy the code

Characteristic specifies the characteristics of a stored procedure or function. Its values are slightly different from those when the stored procedure or function is created.

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
Copy the code

CONTAINS SQL, indicating that the subroutine contains SQL statements, but not statements that read or write data.
NO SQL, indicating that the subroutine does not contain SQL statements.
READS SQL DATARepresents a statement in a subroutine that contains read data.
MODIFIES SQL DATA, indicating that the subroutine contains statements that write data.
SQL SECURITY { DEFINER | INVOKER }, indicating who has the authority to execute.
- DEFINER, indicating that only the definer himself can execute.
- INVOKER, indicating that the caller can execute.
COMMENT 'string'Is a comment information.

The ALTER PROCEDURE statement is used to modify stored procedures and the ALTER FUNCTION statement is used to modify stored functions. However, the structure of the two statements is the same, and all the arguments in the statement are the same.

For example:

alter procedure show_max_salary
sql security invoker 
comment 'Query maximum salary';

show procedure status like 'show_max_salary'\G
Copy the code

4.3 delete

To DROP a stored procedure or function, use the DROP statement.

DROP {PROCEDURE | FUNCTION} [IF EXISTS] The name of the stored procedure or functionCopy the code

IF EXISTS: IF a program or function is not stored, it prevents an error from occurring, generating a warning that is viewed with SHOW WARNINGS.

drop function count_by_id;
drop function if exists count_by_id;# (Suggestion)
Copy the code

5. Compare stored functions and stored procedures

	The keyword	Call syntax	The return value	Application scenarios
The stored procedure	PROCEDURE	CALL stored procedure ()	It means there are zero or more	Generally used to update
Storage function	FUNCTION	The SELECT function ()	It can only be one	Generally used when the query result is a value and returned

6. Examples of local variables

Example 1: Declare a local variable and assign the value to last_name and salary of the employees table where employee_id is 102

DELIMITER //
CREATE PROCEDURE set_value()
BEGIN
	DECLARE emp_name VARCHAR(25);
	DECLARE sal DOUBLE(10.2);
	
	SELECT last_name,salary INTO emp_name,sal
	FROM employees 
	WHERE employee_id = 102;
	
	SELECT emp_name,sal;
END //
DELIMITER ;
call set_value();
Copy the code

Example 2: Create the stored procedure “different_salary” to query the salary difference between an employee and his boss. Use the IN parameter emp_id to receive the employee ID, and use the OUT parameter dif_salary to output the salary difference result.

delimiter //
create procedure different_salary(in emp_id int.out dif_salary double)
begin
		declare emp_sal double;
		declare man_sal double;
		declare man_id int;
		
		select manager_id,salary into man_id,emp_sal from employees 
		where emp_id = employee_id;
		
		select salary into man_sal from employees where employee_id = man_id;
		
		set dif_salary = man_sal - emp_sal;
end//
delimiter ;

set @emp_id = 102;
call different_salary(@emp_id,@diff_sal);
select @diff_sal;
Copy the code

MySQL variables, stored procedures, and functions

MySQL variables, stored procedures, and functions

The foreword 0.

1. The variable

1.1 System Variables

1.1.1 Viewing All System Variables

1.1.2 Viewing specified System Variables (@ @)

1.1.3 Changing the Value of a System Variable

1.2 User-defined Variables

1.2.1 Session User Variables

1.2.2 Local variables

1.2.3 contrast

2. Stored procedures

2.1 Overview of Stored Procedures

2.1.1 the pros and cons

Ali development specification

2.1.2 classification

2.2 Creating and calling stored Procedures

2.2.1 Syntax analysis

2.2.2 Call Format

2.2.3 Code examples

3. Store functions

3.1 Creating a Syntax

3.2 Call Syntax

3.3 Code Examples

4. View, modify, and delete stored procedures and functions

4.1 check the

4.2 to modify

4.3 delete

5. Compare stored functions and stored procedures

6. Examples of local variables

Related Posts

Spring Boot @condition annotation, combination conditions you know

Lib /libpng16.so.16: undefined reference to ‘inflateValidate@ZLIB_1.2.9’

SpringBoot incorporates OpenApi best practices

1.1.2 Viewing specified System Variables (`@ @`)