Stored procedures in MySQL

Stored procedures in MySQL

Overview
Stored procedures are supported starting from MySQL version 5.0.

If we need to write a complex set of SQL statements to achieve certain user needs, then we can write this complex set of SQL statements in the database in advance, and call JDBC to execute this set of SQL statements. . The set of SQL statements written in the database is called a stored procedure.

Stored procedure: (PROCEDURE) is a collection of SQL statements that have been compiled in advance and stored in the database. Calling stored procedures can simplify a lot of work for application developers, reduce data transmission between the database and the application server, and is very beneficial to improving the efficiency of data processing.

It is code encapsulation and reuse at the database SQL language level.

Stored procedures are similar to methods in Java. They need to be defined first and called when used. Stored procedures can define parameters, which are divided into three types: IN, OUT, and INOUT.

IN type parameters represent accepting the data passed in by the caller;
OUT type parameters represent returning data to the caller;
INOUT type parameters can accept parameters passed in by the caller and can also return data to the caller.

Advantages

Stored procedures simplify complex operations by encapsulating processing in easy-to-use units.

Simplify management of change. If the table name, column name, or business logic changes. Only the code of the stored procedure needs to be changed. People who use it don't have to change their code.

Usually stored procedures help improve application performance. When the created stored procedure is compiled, it is stored in the database.
However, MySQL implements stored procedures slightly differently.
MySQL stored procedures are compiled on demand. After compiling the stored procedure, MySQL places it in the cache.
MySQL maintains its own cache of stored procedures for each connection. If the application uses the stored procedure multiple times in a single connection, use the compiled version, otherwise the stored procedure works like a query.

Stored procedures help reduce traffic between the application and the database server.
Because the application does not have to send multiple lengthy SQL statements, it only needs to send the name and parameters of the stored procedure.

Stored procedures are reusable and transparent to any application. Stored procedures expose the database interface to all applications so that developers do not have to develop functionality that is already supported in stored procedures.

Stored programs are safe. The database administrator can grant appropriate permissions to applications that access stored procedures in the database, without providing any permissions to the underlying database tables.

Disadvantages

If you use a large number of stored procedures, the memory usage of each connection using these stored procedures will increase significantly.
In addition, if a large number of logical operations are overused in stored procedures, the CPU usage will also increase, because the original design of the MySQL database focused on efficient queries rather than logical operations.

The structure of stored procedures makes it difficult to develop stored procedures with complex business logic.

It's difficult to debug stored procedures. Only a few database management systems allow debugging of stored procedures. Unfortunately, MySQL does not provide the ability to debug stored procedures.

Developing and maintaining stored procedures is not easy.
Developing and maintaining stored procedures often requires a specialized skill set that not all application developers possess. This can cause problems during application development and maintenance phases.

It has a high degree of dependence on the database and poor transferability.

MySQL stored procedure definition
Basic statement format of stored procedures

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE database name. stored procedure name ([in variable name type, out parameter 2,...])
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
BEGIN
[DECLARE variable name type [DEFAULT value];]
Statement blocks of stored procedures;
END$$

DELIMITER;

The parameters in the stored procedure are of three types: in, out, and inout;

in represents an input parameter (the in parameter by default), indicating that the value of the parameter must be specified by the calling program.
ou represents the output parameter, which means that after the value of the parameter is calculated by the stored procedure, the calculation result of the out parameter is returned to the calling program.
inout represents an immediate input parameter and an output parameter, which means that the value of the parameter can be formulated by the calling program, and the calculation result of the inout parameter can be returned to the calling program.
● The statements in the stored procedure must be included between BEGIN and END.

● DECLARE is used to declare variables. Default variable assignment uses DEFAULT. To change the variable value in a statement block, use SET variable = value;

Use of stored procedures
Define a stored procedure

DELIMITER $$

CREATE
    PROCEDURE `demo`.`demo1`()
-- Stored procedure body
BEGIN
-- DECLARE statement is used to declare variables
DECLARE de_name VARCHAR(10) DEFAULT '';
\t\t
SET de_name = "jim";
\t\t
-- Test output statement (test statements vary from database to database.
SELECT de_name;
END$$

DELIMITER;

Call stored procedure

CALL demo1();

Define a stored procedure with parameters
First define a student database table:

Now we want to query how many people in the student table have male sex.

DELIMITER $$

CREATE
    PROCEDURE `demo`.`demo2`(IN s_sex CHAR(1),OUT s_count INT)
-- Stored procedure body
BEGIN
--Assign the results of the query in SQL to variables through INTO
SELECT COUNT(*) INTO s_count FROM student WHERE sex= s_sex;
SELECT s_count;
\t\t
END$$
DELIMITER;

Call this stored procedure

-- @s_count represents the parameters of the test output
CALL demo2 ('male',@s_count);

Define a flow control statement IF ELSE
The IF statement contains multiple conditional judgments, and the statement is executed based on whether the result is TRUE or FALSE. It is similar to the if, else if, and else syntax in programming languages.

DELIMITER $$

DELIMITER $$
CREATE
    PROCEDURE `demo`.`demo3`(IN `day` INT)
-- Stored procedure body
BEGIN
IF `day` = 0 THEN
SELECT 'Sunday';
ELSEIF `day` = 1 THEN
SELECT 'Monday';
ELSEIF `day` = 2 THEN
SELECT 'Tuesday';
ELSE
SELECT 'Invalid date';
END IF;
\t\t
END$$
DELIMITER;

Call this stored procedure

CALL demo3(2);

Define a conditional control statement CASE
Case is another conditional statement, similar to the choose and when syntax in programming languages. There are two syntax formats for case statements in MySQL.

The first

DELIMITER $$
CREATE
    PROCEDURE demo4(IN num INT)
BEGIN
CASE -- conditional start
\t
WHEN num<0 THEN
SELECT 'negative number';
WHEN num>0 THEN
SELECT 'positive number';
ELSE
SELECT 'Neither positive nor negative';
\t
END CASE; -- End of condition
END$$
DELIMITER;

Call this stored procedure

CALL demo4(1);

2. The second type

DELIMITER $$
CREATE
    PROCEDURE demo5(IN num INT)
BEGIN
CASE num -- conditional start
WHEN 1 THEN
SELECT 'Input is 1';
WHEN 0 THEN
SELECT 'Input is 0';
ELSE
SELECT 'Not 1 nor 0';
END CASE; -- End of condition
END$$
DELIMITER;

call this function

CALL demo5(0);

Define a loop statement WHILE

DELIMITER $$
CREATE
    PROCEDURE demo6(IN num INT,OUT SUM INT)
BEGIN
SET SUM = 0;
WHILE num<10 DO -- loop starts
SET num = num + 1;
SET SUM = SUM + num;
END WHILE; -- End of loop
END$$
DELIMITER;

call this function

-- call function
CALL demo6(0,@sum);

-- Query function
SELECT @sum;

Define a loop statement REPEAT UNTLL
The usage of the REPEATE…UNTLL statement is similar to the do…while statement in Java. The loop operation is executed first and then the condition is judged. The difference is that the loop operation is executed only when the REPEATE expression value is false and stops until the expression value is true.

--Creation process
DELIMITER $$
CREATE
    PROCEDURE demo7(IN num INT,OUT SUM INT)
BEGIN
SET SUM = 0;
REPEAT - start of cycle
SET num = num + 1;
SET SUM = SUM + num;
UNTIL num>=10
END REPEAT; -- End of loop
END$$
DELIMITER;

call this function

CALL demo7(9,@sum);

SELECT @sum;

Define a loop statement LOOP
Loop statements are used to repeatedly execute certain statements.

During execution, you can use the LEAVE statement or ITEREATE to break out of the loop, or you can nest IF and other judgment statements.

The effect of the LEAVE statement is for break in Java, which is used to terminate the loop;
The effect of the ITERATE statement is equivalent to continue in Java, which is used to skip this loop. Enter the next cycle. And the statements under ITERATE will no longer be executed.

DELIMITER $$
CREATE
    PROCEDURE demo8(IN num INT,OUT SUM INT)
BEGIN
SET SUM = 0;
demo_sum:LOOP-- start of loop
SET num = num + 1;
IF num > 10 THEN
LEAVE demo_sum; -- End this loop
ELSEIF num <= 9 THEN
ITERATE demo_sum; -- skip this loop
END IF;
\t\t
SET SUM = SUM + num;
END LOOP demo_sum; -- end of loop
END$$
DELIMITER;

call this function

CALL demo8(0,@sum);

SELECT @sum;

Insert information using stored procedures

DELIMITER $$
CREATE
    PROCEDURE demo9(IN s_student VARCHAR(10),IN s_sex CHAR(1),OUT s_result VARCHAR(20))
BEGIN
-- Declare a variable to determine whether the name already exists
DECLARE s_count INT DEFAULT 0;
-- Verify if such name already exists
SELECT COUNT(*) INTO s_count FROM student WHERE `name` = s_student;
IF s_count = 0 THEN
INSERT INTO student (`name`, sex) VALUES(s_student, s_sex);
SET s_result = 'Data added successfully';
ELSE
                SET s_result = 'The name already exists and cannot be added';
                SELECT s_result;
END IF;
END$$
DELIMITER;

call this function

CALL demo9(“Jim”,”Female”,@s_result);

Call the function again

CALL demo9(“Jim”,”Female”,@s_result)

Stored procedure management
show stored procedure

SHOW PROCEDURE STATUS

Display stored procedures for a specific database
SHOW PROCEDURE STATUS WHERE db = 'db name' AND NAME = 'name name';

Display stored procedures for a specific pattern
SHOW PROCEDURE STATUS WHERE NAME LIKE '%mo%';

Display the source code of the stored procedure
SHOW CREATE PROCEDURE stored procedure name;

Delete stored procedure
DROP PROCEDURE stored procedure name;

Implementation of back-end calling stored procedures
In mybatis, call the stored procedure

<parameterMap type="savemap" id="usermap">
<parameter property="name" jdbcType="VARCHAR" mode="IN"/>
<parameter property="sex" jdbcType="CHAR" mode="IN"/>
<parameter property="result" jdbcType="VARCHAR" mode="OUT"/>
</parameterMap>

<insert id="saveUserDemo" parameterMap="savemap" statementType="CALLABLE">
{<!-- -->call saveuser(?, ?, ?)}
</insert>

Call database management

HashMap<String, Object> map = new HashMap<String, Object>();
map.put("name", "Jim");
map.put("sex","male");
userDao.saveUserDemo(map);
map.get("result");//Get the output parameters