Sql

Summary: in this tutorial, you will learn how to create stored procedures with parameters, including IN, OUT, and INTOUT parameters.

Bạn đang xem: Sql

Introduction to MySquốc lộ stored procedure parameters

Often, stored procedures have parameters. The parameters make the stored procedure more useful and reusable. A parameter in a stored procedure has one of three modes: IN,OUT, or INOUT.

IN parameters

IN is the default mode. When you define an IN parameter in a stored procedure, the calling program has lớn pass an argument khổng lồ the stored procedure.

In addition, the value of an IN parameter is protected. It means that even you change the value of the IN parameter inside the stored procedure, its original value is unchanged after the stored procedure ends. In other words, the stored procedure only works on the copy of the IN parameter.

OUT parameters

The value of an OUT parameter can be changed inside the stored procedure & its new value is passed baông chồng khổng lồ the calling program.

Notice that the stored procedure cannot access the initial value of the OUT parameter when it starts.

INOUT parameters

An INOUT parameter is a combination of IN and OUT parameters. It means that the calling program may pass the argument, và the stored procedure can modify the INOUT parameter, and pass the new value baông chồng to the calling program.

Defining a parameter

Here is the basic syntax of defining a parameter in stored procedures:


parameter_name datatype<(length)>
Code language: SQL (Structured Query Language) (sql)In this syntax,

First, specify the parameter mode, which can be IN , OUT or INOUT depending on the purpose of the parameter in the stored procedure.Second, specify the name of the parameter. The parameter name must follow the naming rules of the column name in MySQL.Third, specify the data type và maximum length of the parameter.

Xem thêm: Download Outlook 2007 Professional, Tải Xuống Microsoft Outlook 2007

MySquốc lộ stored procedure parameter examples

Let’s take some examples of using stored procedure parameters.

The IN parameter example

The following example creates a stored procedure that finds all offices that locate in a country specified by the đầu vào parameter countryName:


DELIMITER //CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))BEGINSELECT * FROM officesWHERE country = countryName;END //DELIMITER ;
Code language: SQL (Structured Query Language) (sql)In this example, the countryName is the IN parameter of the stored procedure.

Suppose that you want lớn find offices locating in the USA, you need to lớn pass an argument (USA) khổng lồ the stored procedure as shown in the following query:


CALL GetOfficeByCountry("USA");
Code language: SQL (Structured Query Language) (sql)

*
To find offices in France, you pass the literal string France to lớn the GetOfficeByCountrystored procedure as follows:


CALL GetOfficeByCountry("France")
Code language: Squốc lộ (Structured Query Language) (sql)

*
Because the countryName is the IN parameter, you must pass an argument. If you don’t vì chưng so, you’ll get an error:


CALL GetOfficeByCountry();
Code language: SQL (Structured Query Language) (sql)Here’s the error:


Error Code: 1318. Incorrect number of arguments for PROCEDURE classicmodels.GetOfficeByCountry; expected 1, got 0
Code language: JavaScript (javascript)

The OUT parameter example

The following stored procedure returns the number of orders by order status.


DELIMITER $$CREATE PROCEDURE GetOrderCountByStatus (IN orderStatus VARCHAR(25),OUT total INT)BEGINSELECT COUNT(orderNumber)INTO totalFROM ordersWHERE status = orderStatus;END$$DELIMITER ;
Code language: Squốc lộ (Structured Query Language) (sql)The stored procedure GetOrderCountByStatus()has two parameters:

The orderStatus: is the IN parameter specifies the status of orders lớn return.The total: is the OUT parameter that stores the number of orders in a specific status.

To find the number of orders that already shipped, you call GetOrderCountByStatus and pass the order status as of Shipped, and also pass a session variable (
total ) to receive the return value.


CALL GetOrderCountByStatus("Shipped",
total);SELECT
total;
Code language: SQL (Structured Query Language) (sql)

*
To get the number of orders that are in-process, you điện thoại tư vấn the stored procedure GetOrderCountByStatusas follows:


CALL GetOrderCountByStatus("in process",
total);SELECT
total AS total_in_process;
Code language: SQL (Structured Query Language) (sql)

*

The INOUT parameter example

The following example demonstrates how to lớn use an INOUT parameter in a stored procedure:


DELIMITER $$CREATE PROCEDURE SetCounter(INOUT counter INT, IN inh INT)BEGINSET counter = counter + inc;END$$DELIMITER ;
Code language: SQL (Structured Query Language) (sql)In this example, the stored procedure SetCounter() accepts one INOUT parameter ( counter ) và one IN parameter ( inc ). It increases the counter ( counter ) by the value of specified by the inc parameter.

These statements illustrate how khổng lồ call the SetSounter stored procedure:


SET
counter = 1;CALL SetCounter(
counter,1); -- 2CALL SetCounter(
counter,1); -- 3CALL SetCounter(
counter,5); -- 8SELECT
counter; -- 8
Code language: Squốc lộ (Structured Query Language) (sql)Here is the output:

*

In this tutorial, you have sầu learned how create stored procedures with parameters including IN, OUT, and INOUT parameters.