Параметры и возврат результатов в хранимых процедурах

Параметры и возврат результатов в хранимых процедурах являются важными элементами, которые позволяют передавать данные в процедуру и получать результаты. Вот как это работает в различных СУБД:

Параметры хранимых процедур

Хранимые процедуры могут иметь три типа параметров:

1. Входные параметры (IN):

   • Позволяют передавать данные в процедуру.

   • Используются для получения информации, необходимой для выполнения процедуры.

2. Выходные параметры (OUT):

   • Позволяют возвращать данные из процедуры.

   • Используются для передачи данных обратно в вызывающий контекст.

3. Входные/выходные параметры (INOUT):

   • Позволяют как передавать данные в процедуру, так и возвращать их обратно.

   • Позволяют изменять переданные данные и возвращать обновленные значения.

Примеры в различных СУБД

1. SQL Server

Создание хранимой процедуры с параметрами:

CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT,
    @FullName NVARCHAR(100) OUTPUT,
    @Salary DECIMAL(18, 2) OUTPUT
AS
BEGIN
    SELECT 
        @FullName = FirstName + ' ' + LastName,
        @Salary = Salary
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;

Вызов хранимой процедуры с выходными параметрами:

DECLARE @FullName NVARCHAR(100);
DECLARE @Salary DECIMAL(18, 2);

EXEC GetEmployeeDetails 
    @EmployeeID = 1,
    @FullName = @FullName OUTPUT,
    @Salary = @Salary OUTPUT;

SELECT @FullName AS EmployeeName, @Salary AS EmployeeSalary;

2. MySQL

Создание хранимой процедуры с параметрами:

CREATE PROCEDURE GetEmployeeDetails(
    IN EmployeeID INT,
    OUT FullName VARCHAR(100),
    OUT Salary DECIMAL(18, 2)
)
BEGIN
    SELECT 
        CONCAT(FirstName, ' ', LastName) INTO FullName,
        Salary INTO Salary
    FROM Employees
    WHERE EmployeeID = EmployeeID;
END;

Вызов хранимой процедуры с выходными параметрами:

CALL GetEmployeeDetails(1, @FullName, @Salary);

SELECT @FullName AS EmployeeName, @Salary AS EmployeeSalary;

3. PostgreSQL

Создание хранимой процедуры с параметрами:

CREATE OR REPLACE FUNCTION GetEmployeeDetails(
    EmployeeID INT,
    OUT FullName TEXT,
    OUT Salary NUMERIC
)
AS $$
BEGIN
    SELECT 
        CONCAT(FirstName, ' ', LastName),
        Salary
    INTO FullName, Salary
    FROM Employees
    WHERE EmployeeID = EmployeeID;
END;
$$ LANGUAGE plpgsql;

Вызов хранимой функции с выходными параметрами:

SELECT * FROM GetEmployeeDetails(1);

4. Oracle

Создание хранимой процедуры с параметрами:

CREATE OR REPLACE PROCEDURE GetEmployeeDetails(
    EmployeeID IN NUMBER,
    FullName OUT VARCHAR2,
    Salary OUT NUMBER
)
AS
BEGIN
    SELECT 
        FirstName || ' ' || LastName,
        Salary
    INTO FullName, Salary
    FROM Employees
    WHERE EmployeeID = EmployeeID;
END;

Вызов хранимой процедуры с выходными параметрами:

DECLARE
    FullName VARCHAR2(100);
    Salary NUMBER;
BEGIN
    GetEmployeeDetails(1, FullName, Salary);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || FullName);
    DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || Salary);
END;

Ключевые моменты

1. Определение параметров:

   • IN: Используется для передачи данных в процедуру.

   • OUT: Используется для получения данных из процедуры.

   • INOUT: Используется для передачи данных и получения обновленных значений.

2. Возврат значений:

   • В SQL Server и Oracle, параметры OUTPUT и OUT позволяют возвращать значения.

   • В MySQL и PostgreSQL, параметры OUT возвращают значения, которые можно получить после выполнения процедуры.

3. Вызов процедуры:

   • Процедуры с выходными параметрами требуют использования дополнительных команд для получения значений (например, OUTPUT в SQL Server, CALL в MySQL, SELECT в PostgreSQL).

Эти примеры помогут вам создавать и использовать хранимые процедуры с параметрами и возвратом результатов в различных СУБД.