1. The background

This section covers stored procedures and functions.

2. Knowledge

2.1 concept

A stored procedure is a collection of MULTIPLE SQL statements. That is, multiple statements are executed at a time and SQL statements are processed in batches.

Stored procedures and functions are very similar, with different concepts and different methods. In practice, however, it is used to execute one batch (multiple) of SQL at a time.

2.2 Stored Procedures

Create the stored procedure by setting the MySQL terminator // with DELIMITER, since MySQL’s default terminator is a semicolon (;). This is to avoid conflict. After writing the stored procedure, change it back to a semicolon. Example:

DELIMITER //
CREATE PROCEDURE ppp()
BEGIN
  SELECT * FROM book;
END //
DELIMITER ;
Copy the code

Calling a stored procedure

CALL ppp(); 
Copy the code

Deleting a stored procedure

DROP PROCEDURE ppp;
Copy the code

2.3 the function

Create a function

DELIMITER //
CREATE FUNCTION fun1() RETURNS CHAR(50)
  RETURN (SELECT bookName from book);
//
DELIMITER ;
Copy the code

Note before executing: MySQL does not allow (trust) function creation by default.

SET GLOBAL log_bin_trust_function_creators = 1;
Copy the code

Calling a function is the same as a normal function call

SELECT fun1();
Copy the code

Delete function

DROP FUNCTION fun1;
Copy the code

3. The extension

Generally speaking, the maintenance cost of stored procedures is still very high. It is indeed difficult for ordinary companies without A DBA. It is recommended to put business logic in the business layer. There is also some discussion online about using stored procedures.

Why does alibaba Java development manual prohibit the use of stored procedures? www.zhihu.com/question/57…

END