oracle stored procedure with parameters

Oracle is a common relational database management system. Using Oracle stored procedures can optimize database operations and improve database performance. This article will introduce Oracle stored procedures, including how to define stored procedures with parameters and how to call stored procedures with parameters.

1. Overview of stored procedures:

A stored procedure is a precompiled database program whose program code is stored in the database. Once created, it can be reused. Unlike SQL statements, stored procedures execute faster, are reusable, and have certain security and maintenance.

Stored procedures can achieve the following purposes in Oracle database:

1. Reduce network transmission data and improve data processing efficiency;
2. Can implement complex business logic, Such as transaction control, etc.;
3. Data maintenance is easier and easier to maintain and manage.

2. Define a stored procedure with parameters

When defining an Oracle stored procedure, you can take parameters. The parameters can be input parameters, output parameters, or input and output parameters.

1. Input parameters: Within the stored procedure, they are used to pass data to the stored procedure.
2. Output parameters: They are assigned values ​​within the stored procedure and returned to the caller.
3. Input and output parameters: They are used to pass data and obtain return values ​​at the same time.

The following is the general form of defining a stored procedure with parameters:

CREATE [OR REPLACE] PROCEDURE procedure_name
(parameter1 [IN | OUT | IN OUT] type1 [, parameter2 [IN | OUT | IN OUT] type2 ...])
IS | AS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [procedure_name];

Among them, the CREATE PROCEDURE statement is used to define the stored procedure, procedure_name is the name of the stored procedure, IN, OUT, IN OUT are the parameter transfer methods, type is the data type of the parameter, IS/ After AS is the declaration of local variables, between BEGIN and END is the executable statement of the stored procedure, and EXCEPTION is the exception handling statement.

Take a simple stored procedure as an example:

CREATE PROCEDURE get_employee_salary
(
emp_id IN NUMBER,
salary OUT NUMBER
)
IS
BEGIN
SELECT salary INTO salary FROM employees WHERE employee_id = emp_id;
END;

This stored procedure has two parameters, an input parameter emp_id and an output parameter salary. The stored procedure The function is to query the corresponding salary value based on emp_id in the employees table and assign it to the output parameter salary.

3. Call a stored procedure with parameters

When calling a stored procedure, you need to provide the stored procedure name and parameter list. The order of the parameter list must be the same as the order in which the parameters are listed when the stored procedure is defined. For example, to call the above get_employee_salary stored procedure, you can use the following SQL statement:

DECLARE
n_employee_id NUMBER := 100;
n_salary NUMBER;
BEGIN
get_employee_salary(n_employee_id, n_salary) ;
DBMS_OUTPUT.PUT_LINE('The salary of employee with ID ' || n_employee_id || ' is ' || n_salary);
END;

This statement defines a variable n_employee_id, and the value is 100. Another variable n_salary is not assigned an initial value. Use the get_employee_salary stored procedure to query the salary value corresponding to n_employee_id, assign the result to the n_salary variable, and finally output the result.

The above is the parameter definition and calling method of Oracle stored procedures. Stored procedures can greatly improve the efficiency and flexibility of database operations. However, when designing stored procedures, you need to consider the efficiency and maintainability of the stored procedures, and whether you need to use stored procedures to complete the required business logic.

The above is the detailed content of oracle stored procedure with parameters. For more information, please follow other related articles on the PHP Chinese website!