MySQL stored procedure with in and out parameters

Welcome to the Linux community forum and interact with 2 million technical staff to enter the simple example of the in and out parameters in the MySQL stored procedure: [html] mysqlDELIMITER $ mysqlUSEtest $ DatabasechangedmysqlDROPPROCEDUREIFEXISTS 'SP _ add' $ QueryOK, 0 rowsaffected (0.00sec) mysq

Welcome to the Linux community forum and interact with 2 million technical staff> enter the simple example of MySQL stored procedure with in and out parameters: [html] mysql DELIMITER $ mysql USE test $ Database changed mysql drop procedure if exists 'SP _ add' $ Query OK, 0 rows affected (0.00 sec) mysq

Welcome to the Linux community forum and interact with 2 million technicians>

MySQL stored procedure with in and out parameters

The simplest example:

[Html]

Mysql> DELIMITER $

Mysql> USE test $

Database changed

Mysql> drop procedure if exists 'SP _ add' $

Query OK, 0 rows affected (0.00 sec)

Mysql> create procedure sp_add (a INT, B INT, OUT c INT)

-> BEGIN

-> SET c = a + B;

-> END $

Query OK, 0 rows affected (0.00 sec)

Mysql> DELIMITER;

[Html]

Mysql> CALL sp_add (1, 2, @ c );

Query OK, 0 rows affected (0.00 sec)

Mysql> SELECT @ c;

+ ------ +

| @ C |

+ ------ +

| 3 |

+ ------ +

1 row in set (0.00 sec)

A slightly complex example:

[Html]

Mysql> show create table t_BillNo;

+ ---------- + -------------------------------------------------------- +

| Table | Create Table |

+ ---------- + -------------------------------------------------------- +

| T_BillNo | create table 't_ billno '(

'Saleno' bigint (20) default null,

'Bmh 'varchar (20) DEFAULT NULL

) ENGINE = InnoDB default charset = utf8 CHECKSUM = 1 DELAY_KEY_WRITE = 1 ROW_FORMAT = DYNAMIC |

+ ---------- + -------------------------------------------------------- +

1 row in set (0.00 sec)

Mysql> select * from t_BillNo;

+ -------- + ------ +

| SaleNo | bmh |

+ -------- + ------ +

| 1 | 2 |

| 4 | 3 |

| 4 | 5 |

| 7 | 7 |

| 12 | 8 |

+ -------- + ------ +

5 rows in set (0.00 sec)

Mysql>

Mysql> DELIMITER $

Mysql> USE test $

Database changed

Mysql> drop procedure if exists 'SP _ getmaxnumber' $

Query OK, 0 rows affected (0.01 sec)

DELIMITER $

USE test $

Drop procedure if exists 'SP _ getmaxnumber' $

Create procedure sp_GetMaxNumber (IN v_bmh VARCHAR (6), OUT v_MaxNo INT)

BEGIN

[1] [2]