Parâmetros de procedimento armazenado MySQL

Resumo : neste tutorial, você aprenderá como criar procedimentos armazenados com parâmetros, incluindo IN, OUTe INTOUTparâmetros.

Introdução aos parâmetros de procedimento armazenado do MySQL

Normalmente, os procedimentos armazenados possuem parâmetros, o que os torna mais úteis e reutilizáveis.

Um parâmetro em um procedimento armazenado possui um dos três modos: IN, OUTou INOUT.

Parâmetros IN

INé o modo padrão. Ao definir um INparâmetro em um procedimento armazenado, o programa chamador deve passar um argumento para o procedimento armazenado.

Além disso, o valor de um INparâmetro é protegido. Isso significa que mesmo se você alterar o valor do INparâmetro dentro do procedimento armazenado, seu valor original permanecerá inalterado após o término do procedimento armazenado. Em outras palavras, o procedimento armazenado funciona apenas na cópia do INparâmetro.

Parâmetros de SAÍDA

O valor de um OUTparâmetro pode ser modificado no procedimento armazenado e seu valor atualizado é então passado de volta ao programa de chamada.

Observe que os procedimentos armazenados não podem acessar o valor inicial do OUTparâmetro quando são iniciados.

Parâmetros INOUT

Um INOUT parâmetro é uma combinação de parâmetros INe OUT. Isso significa que o programa chamador pode passar o argumento e o procedimento armazenado pode modificar o INOUTparâmetro e passar o novo valor de volta ao programa chamador.

Definindo um parâmetro

Aqui está a sintaxe básica para definir um parâmetro em procedimentos armazenados:

[IN | OUT | INOUT] parameter_name datatype[(length)]Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe,

Primeiro, especifique o modo do parâmetro, que pode ser IN, OUTou INOUTdependendo da finalidade do parâmetro no procedimento armazenado.
Segundo, forneça o nome do parâmetro. O nome do parâmetro deve seguir as regras de nomenclatura do nome da coluna no MySQL.
Terceiro, defina o tipo de dados e o comprimento máximo do parâmetro.

Exemplos de parâmetros de procedimento armazenado MySQL

Vamos explorar alguns exemplos de uso de parâmetros de procedimento armazenado.

O exemplo do parâmetro IN

O exemplo a seguir cria um procedimento armazenado que localiza todos os escritórios localizados em um país especificado pelo parâmetro de entrada countryName:

DELIMITER //

CREATE PROCEDURE GetOfficeByCountry(
	IN countryName VARCHAR(255)
)
BEGIN
	SELECT * 
 	FROM offices
	WHERE country = countryName;
END //

DELIMITER ;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Neste exemplo, the countryNameé o INparâmetro do procedimento armazenado.

Suponha que você queira encontrar escritórios localizados nos EUA, você precisa passar um argumento ( USA) para o procedimento armazenado conforme mostrado na seguinte consulta:

CALL GetOfficeByCountry('USA');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Parâmetros de procedimento armazenado MySQL

Para localizar escritórios em France, você passa a string literal Francepara o GetOfficeByCountry procedimento armazenado da seguinte maneira:

CALL GetOfficeByCountry('France')Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Como the countryNameé o INparâmetro, você deve passar um argumento. Se você não fizer isso, receberá um erro:

CALL GetOfficeByCountry();Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Aqui está o erro:

Error Code: 1318. Incorrect number of arguments for PROCEDURE classicmodels.GetOfficeByCountry; expected 1, got 0Linguagem de código:  JavaScript  ( javascript )

O exemplo do parâmetro OUT

A seguir é definido um procedimento armazenado que retorna o número de pedidos com base no status do pedido.

DELIMITER $$

CREATE PROCEDURE GetOrderCountByStatus (
	IN  orderStatus VARCHAR(25),
	OUT total INT
)
BEGIN
	SELECT COUNT(orderNumber)
	INTO total
	FROM orders
	WHERE status = orderStatus;
END$$

DELIMITER ;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O procedimento armazenado GetOrderCountByStatus() possui dois parâmetros:

O orderStatusparâmetro INespecifica o status dos pedidos a serem devolvidos.
O totalé o OUTparâmetro que armazena a quantidade de pedidos em um status específico.

Para saber a quantidade de pedidos que já foram enviados, você chama GetOrderCountByStatuse passa o status do pedido a partir de Shipped, e também passa uma variável de sessão ( @total) para receber o valor de retorno.

CALL GetOrderCountByStatus('Shipped',@total);
SELECT @total;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+--------+
| @total |
+--------+
|    303 |
+--------+
1 row in set (0.00 sec)Linguagem de código:  JavaScript  ( javascript )

Para obter o número de pedidos que estão em processamento, chame o procedimento armazenado GetOrderCountByStatus da seguinte maneira:

CALL GetOrderCountByStatus('In Process',@total);
SELECT @total AS total_in_process;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+------------------+
| total_in_process |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)Linguagem de código:  JavaScript  ( javascript )

O exemplo do parâmetro INOUT

O exemplo a seguir demonstra como usar um INOUTparâmetro em um procedimento armazenado:

DELIMITER $$

CREATE PROCEDURE SetCounter(
	INOUT counter INT,
    IN inc INT
)
BEGIN
	SET counter = counter + inc;
END$$

DELIMITER ;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Neste exemplo, o procedimento armazenado SetCounter()aceita um INOUT parâmetro ( counter) e um INparâmetro ( inc). Aumenta o contador ( counter) pelo valor especificado pelo incparâmetro.

Estas instruções ilustram como chamar o SetSounter procedimento armazenado:

SET @counter = 1;
CALL SetCounter(@counter,1); -- 2
CALL SetCounter(@counter,1); -- 3
CALL SetCounter(@counter,5); -- 8
SELECT @counter; -- 8Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )