How to call MySQL stored procedure

Overview

Stored procedures are supported starting from MySQL 5.0 version.

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 it by 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.

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

Similar to methods in Java, stored procedures need to be defined first and called when used. Parameters can be defined in stored procedures. These parameters 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

1. Stored procedures are encapsulated in easy-to-use units through processing, simplifying complex operations.

2. Simplify the management of changes. 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.

3. Usually stored procedures help improve application performance. When the created stored procedure is compiled, it is stored in the database.
   However, the stored procedures implemented by MySQL are slightly different.
   MySQL stored procedures are compiled on demand. After compiling the stored procedure, MySQL places it in the cache.
   MySQL maintains its own stored procedure cache 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.

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

5. Stored procedures are reusable and transparent to any application. To save developers from duplicating functionality that is already supported, stored procedures expose the database interface to all applications.

6. Stored programs are safe. Database administrators can grant appropriate permissions to applications that access stored procedures in the database without granting any permissions to the underlying database tables.

Disadvantages

1. 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 the stored procedure, the CPU usage will also increase, because the original design of the MySQL database focused on efficient queries rather than logical operations.

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

3. It is 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.

4. 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.

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

Definition of MySQL stored procedure

Basic statement format of stored procedure

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE 数据库名.存储过程名([in变量名 类型,out 参数 2，...])
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
	BEGIN
		[DECLARE 变量名 类型 [DEFAULT 值];]
		存储过程的语句块;
	END$$

DELIMITER ;

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

1. in represents the input parameter (in parameter by default), indicating that the value of the parameter must be specified by the calling program.

2. 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.

3. 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.

● 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`()
	-- 存储过程体
	BEGIN
		-- DECLARE声明 用来声明变量的
		DECLARE de_name VARCHAR(10) DEFAULT '';
		
		SET de_name = "jim";
		
		-- 测试输出语句（不同的数据库，测试语句都不太一样。
		SELECT de_name;
	END$$

DELIMITER ;

Call the stored procedure

CALL demo1();

Define a stored procedure with parameters Stored procedure

First define a student database table:

现在要查询这个student表中的sex为男的有多少个人。

DELIMITER $$

CREATE
    PROCEDURE `demo`.`demo2`(IN s_sex CHAR(1),OUT s_count INT)
	-- 存储过程体
	BEGIN
		-- 把SQL中查询的结果通过INTO赋给变量
		SELECT COUNT(*) INTO s_count FROM student WHERE sex= s_sex;
		SELECT s_count;
		
	END$$
DELIMITER ;

调用这个存储过程

-- @s_count表示测试出输出的参数
CALL demo2 ('男',@s_count);

定义一个流程控制语句 IF ELSE

IF 语句包含多个条件判断，根据结果为 TRUE、FALSE执行语句，与编程语言中的 if、else if、else 语法类似。

DELIMITER $$
CREATE
    PROCEDURE `demo`.`demo3`(IN `day` INT)
	-- 存储过程体
	BEGIN
		IF `day` = 0 THEN
		SELECT '星期天';
		ELSEIF `day` = 1 THEN
		SELECT '星期一';
		ELSEIF `day` = 2 THEN
		SELECT '星期二';
		ELSE
		SELECT '无效日期';
		END IF;
		
	END$$
DELIMITER ;

调用这个存储过程

CALL demo3(2);

定义一个条件控制语句 CASE

case是一种类似于编程语言中的choose和when语法的条件判断语句。MySQL 中的 case语句有两种语法格式。

第一种

DELIMITER $$
CREATE 
    PROCEDURE demo4(IN num INT)
	BEGIN
		CASE -- 条件开始
	
		WHEN num<0 THEN 
			SELECT &#39;负数&#39;;
		WHEN num>0 THEN 
			SELECT &#39;正数&#39;;
		ELSE 
		SELECT &#39;不是正数也不是负数&#39;;
	
		END CASE; -- 条件结束
	END$$
DELIMITER;

调用这个存储过程

CALL demo4(1);

2.第二种

DELIMITER $$
CREATE 
    PROCEDURE demo5(IN num INT)
	BEGIN
		CASE num  -- 条件开始
		WHEN 1 THEN 
			SELECT '输入为1';
		WHEN 0 THEN 
			SELECT '输入为0';
		ELSE 
		SELECT '不是1也不是0';
		END CASE; -- 条件结束
	END$$
DELIMITER;

调用此函数

CALL demo5(0);

定义一个循环语句 WHILE

DELIMITER $$
CREATE 
    PROCEDURE demo6(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     WHILE num<10 DO -- 循环开始
	         SET num = num+1;
	         SET SUM = SUM+num;
	         END WHILE; -- 循环结束
	END$$
DELIMITER;

调用此函数

-- 调用函数
CALL demo6(0,@sum);

-- 查询函数
SELECT @sum;

定义一个循环语句 REPEAT UNTLL

REPEATE…UNTLL 语句的用法和 Java中的 do…while 语句类似，都是先执行循环操作，再判断条件，区别是REPEATE 表达式值为 false时才执行循环操作，直到表达式值为 true停止。

-- 创建过程
DELIMITER $$
CREATE 
    PROCEDURE demo7(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     REPEAT-- 循环开始
		SET num = num+1;
		SET SUM = SUM+num ;
		UNTIL num>=10
		END REPEAT; -- 循环结束
	END$$
DELIMITER;

调用此函数

CALL demo7(9,@sum);

SELECT @sum;

定义一个循环语句 LOOP

循环语句，用来重复执行某些语句。

执行过程中可使用 LEAVE语句或者ITEREATE来跳出循环，也可以嵌套IF等判断语句。

LEAVE 语句效果对于Java中的break，用来终止循环；

ITERATE语句效果相当于Java中的continue，用来跳过此次循环。进入下一次循环。且ITERATE之下的语句将不在进行。

DELIMITER $$
CREATE 
    PROCEDURE demo8(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     demo_sum:LOOP-- 循环开始
		SET num = num+1;
		IF num > 10 THEN
		    LEAVE demo_sum; -- 结束此次循环
		ELSEIF num < 9 THEN
		    ITERATE demo_sum; -- 跳过此次循环
		END IF;
		
		SET SUM = SUM+num;
		END LOOP demo_sum; -- 循环结束
	END$$
DELIMITER;

调用此函数

CALL demo8(0,@sum);

SELECT @sum;

使用存储过程插入信息

DELIMITER $$
CREATE 
    PROCEDURE demo9(IN s_student VARCHAR(10),IN s_sex CHAR(1),OUT s_result VARCHAR(20))
	BEGIN
	   -- 声明一个变量 用来决定这个名字是否已经存在
	   DECLARE s_count INT DEFAULT 0;
	   -- 验证这么名字是否已经存在
	   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 = '数据添加成功';
	   ELSE
                SET s_result = '名字已存在，不能添加';
                SELECT s_result;
	   END IF;
	END$$
DELIMITER;

调用此函数

CALL demo9("Jim","女",@s_result);

再次调用次函数

CALL demo9("Jim","女",@s_result)

存储过程的管理

显示存储过程

SHOW PROCEDURE STATUS

显示特定数据库的存储过程

SHOW PROCEDURE STATUS WHERE db = 'db名字' AND NAME = 'name名字';

显示特定模式的存储过程

SHOW PROCEDURE STATUS WHERE NAME LIKE '%mo%';

显示存储过程的源码

SHOW CREATE PROCEDURE 存储过程名;

删除存储过程

DROP PROCEDURE 存储过程名;

后端调用存储过程的实现

在mybatis当中，调用存储过程

<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 >

调用数据库管理

HashMap<String, Object> map = new HashMap<String, Object>(); 
	map.put("name", "Jim"); 
	map.put("sex","男");
	userDao.saveUserDemo(map); 
	map.get(“result”);//获得输出参数

通过这样就可以调用数据库中的存储过程的结果。

The above is the detailed content of How to call MySQL stored procedure. For more information, please follow other related articles on the PHP Chinese website!