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]