How to make a call with parameters in Oracle stored procedure

Oracle stored procedure is a pre-compiled reusable SQL statement that is stored in the database and can be executed and called at any time. In Oracle stored procedures, you can use parameters to achieve more flexible calling. This article explains how to use parameters in Oracle stored procedures.

Create a stored procedure and define parameters

In Oracle database, creating a stored procedure requires the use of the CREATE PROCEDURE statement. Here is a simple example:

CREATE OR REPLACE PROCEDURE get_employee_details(
    employee_id IN NUMBER,
    employee_name OUT VARCHAR2,
    hire_date OUT DATE,
    salary OUT NUMBER
)
AS
BEGIN
    SELECT e.employee_name, e.hire_date, e.salary 
    INTO employee_name, hire_date, salary
    FROM employees e
    WHERE e.employee_id = employee_id;
END;

The above code creates a stored procedure named get_employee_details that has four parameters: employee_id(IN input parameters of type), employee_name, hire_date, and salary (output parameters of type OUT). The function of the stored procedure is to query the employee's detailed information based on the employee_id parameter and store the query results in the output parameter.

Call stored procedure

When the stored procedure is successfully created, you can use the EXECUTE statement to call it. The following is the code to call the stored procedure:

DECLARE
    emp_name VARCHAR2(50);
    emp_hire_date DATE;
    emp_salary NUMBER;
BEGIN
    get_employee_details(101, emp_name, emp_hire_date, emp_salary);
    DBMS_OUTPUT.PUT_LINE('Employee Name : ' || emp_name);
    DBMS_OUTPUT.PUT_LINE('Hire Date : ' || emp_hire_date);
    DBMS_OUTPUT.PUT_LINE('Salary : ' || emp_salary);
END;

In the above code, first declare three variables emp_name, emp_hire_date and emp_salary, Then call the stored procedure get_employee_details and pass in the parameter 101, and assign the output parameters to the variables declared above. Finally, use the PUT_LINE function in the DBMS_OUTPUT package to output the query results.

It should be noted that the DECLARE and BEGIN statements are necessary because they represent the beginning and end of a code block. In the code block, you can declare variables, call stored procedures, execute various SQL statements, etc.

Types of parameters

When defining parameters of a stored procedure, you can use the following types:

• IN: Indicates input parameters, these Parameters are used to pass values ​​into stored procedures.
• OUT: Indicates output parameters, which are used to return values ​​from stored procedures.
• IN OUT: Indicates that it is both an input parameter and an output parameter. These parameters can both pass values ​​to and return values ​​from stored procedures.

In addition to the above types, you can also use the NOCOPY keyword to define parameters, which can avoid memory copying during parameter transfer, thereby improving execution efficiency.

Application scenarios

In actual development, parameter calling of stored procedures is very common, and it can be applied in multiple scenarios. The following are some typical application scenarios:

• Parameterized query: In the stored procedure, input parameters can be defined so that the stored procedure can execute different query statements based on different input parameters, thereby achieving parameterization. Inquire.
• Batch operation: In the stored process, you can define input parameters and output parameters, so that the stored process can perform batch operations based on the input parameters, and at the same time store the processing results in the output parameters, reducing the number of subsequent operations.
• Transaction processing: In the stored procedure, input parameters can be used to control the submission or rollback of the transaction, thereby achieving a more flexible transaction processing method.

Summary

This article introduces the method of using parameter calls in Oracle stored procedures. Typically, parameterized calls to stored procedures can greatly improve code reusability and flexibility, and can provide applications with more flexible functionality. Therefore, in actual development, parameterized calls of stored procedures should be fully utilized to improve system performance and maintainability.

The above is the detailed content of How to make a call with parameters in Oracle stored procedure. For more information, please follow other related articles on the PHP Chinese website!