Summarize the usage and explanation of MySQL stored procedure parameters

This article brings you relevant knowledge about mysql. It mainly introduces the usage and description of MySQL stored procedure parameters. There are three types of parameters for MySQL stored procedures: IN, OUT, INOUT. Let’s take a look at it together, I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

Parameters of MySQL stored procedure

There are total parameters of MySQL stored procedure Three types:

• IN
• OUT
• INOUT

Input parameters

• Input parameters (IN): Parameters that pass data to the stored procedure when calling the stored procedure (the called stored procedure must have actual values Variable or literal value)

Details note: The following stored procedure creation will report an error, its type and type length need and create the type and type length of the table field Consistent

-- 创建一个存储过程：添加一个员工信息
DELIMITER $$
CREATE PROCEDURE procedure_test4(IN `p_name` VARCHAR,IN `p_call` VARCHAR)
BEGIN
  INSERT INTO employees(`name`,`call`)
  VALUES(`p_name`,`p_call`);
END $$

Correct writing

-- 创建一个存储过程：添加一个员工信息
DELIMITER $$
CREATE PROCEDURE procedure_test4(IN `p_name` VARCHAR(64),IN `p_call` VARCHAR(64))
BEGIN
  INSERT INTO employees(`name`,`call`)
  VALUES(`p_name`,`p_call`);
END $$

Now let’s test whether we can call the stored procedure and add data to it

First look at the original data of the table

Call the storage Process

CALL procedure_test4('李四','321321')

##Let’s take a look at the results

Output parameters

• Output parameters (OUT): Return the data generated in the stored procedure to the caller, which is equivalent to the return value of the Java method, but the difference is that a stored procedure can have multiple outputs Parameters

Example:

-- 创建一个存储过程：根据id查询出该员工姓名
DELIMITER $$
CREATE PROCEDURE procedure_test5(IN `p_id` INT(64),OUT `p_name` VARCHAR(64))
BEGIN
  SELECT `name` INTO `p_name` FROM employees WHERE id=`p_id`;
END $$

Creation is OK, let’s call it to test

SET @e_name=''
CALL procedure_test5('2',@e_name)
SELECT @e_name FROM DUAL

Input and output parameters (INOUT)

-- INOUT(输入输出参数)
DELIMITER $$
CREATE PROCEDURE procedure_test6(INOUT str VARCHAR(64))
BEGIN
  -- 把你传进来的值作为条件，查询出的结果再重新赋值给 str 返回出去
  -- 这里的 str 既当输入参数，也当输出参数
  SELECT `name` INTO str FROM employees WHERE id=str;
END $$

No problem creating, let’s test it

SET @e_name='2'
CALL procedure_test6(@e_name)
SELECT @e_name FROM DUAL

is no problem, but in actual development, it is recommended to use INOUT sparingly. The reason: the readability of the code will become worse.

Recommended learning:

mysql video tutorial

The above is the detailed content of Summarize the usage and explanation of MySQL stored procedure parameters. For more information, please follow other related articles on the PHP Chinese website!