Single-parameter or multi-parameter passing of MySQL stored procedure

Almost all stored procedures developed by MySQL require parameters. These parameters make stored procedures more flexible and useful. In MySQL, there are three modes for parameters: IN,OUT or INOUT.

1, single parameter in

DELIMITER $$

USE dc3688 $$

CREATE PROCEDURE GetOfficeByCountry (IN countryName VARCHAR)

BEGIN

SELECT *

FROM offices

WHERE country = countryName

END$$

DELIMITER

2, multiple parameters (in out)

DELIMITER $$

CREATE PROCEDURE CountOrderByStatus (

IN orderStatus VARCHAR (25)

OUT total INT)

BEGIN

SELECT count (orderNumber)

INTO total

FROM orders

WHERE status = orderStatus

END$$

DELIMITER

Call method

CALL CountOrderByStatus ('Shipped',@total)

SELECT @ total

3. Parameter inout

DELIMITER $$

CREATE PROCEDURE set_counter (INOUT count INT (4), IN inc INT (4))

BEGIN

SET count = count + inc

END$$

DELIMITER

Calling procedure

SET @ counter = 1

CALL set_counter (@ counter,1)

You can actually use different parameters.

The article is from www.dc3688.com.