oracle stored procedure example

In Oracle database, a stored procedure is a repeatable block of SQL code that can accept input parameters and return output parameters. It is similar to a function, except that stored procedures can be called and run in the database, allowing for more complex and scalable applications. This article will introduce how to write a simple Oracle stored procedure and demonstrate how to use the stored procedure in practical applications.

Creating a stored procedure

In Oracle, creating a stored procedure requires the following steps:

1. Creating the structure of a stored procedure

Storage The structure of a process includes defining the process name, input parameters, output parameters and process body. The following is an example of a simple Oracle stored procedure structure:

CREATE OR REPLACE PROCEDURE get_employee_salaries (id IN NUMBER, salary OUT NUMBER)
IS
BEGIN
SELECT salary INTO salary FROM employees WHERE employee_id = id;
END;

In the above example, get_employee_salaries is the name of the process, id is the input parameter, and salary is the output parameter.

2. Writing the process body

The process body is the actual logic inside the stored procedure. In this example, the process body uses the input parameter id to query the salary from the employees table, and then stores the result in the output parameter salary.

3. Compile stored procedures

After the SQL code that creates the stored procedure is written into the database, the code needs to be compiled. In Oracle this can be done via the following command:

SQL> CREATE OR REPLACE PROCEDURE get_employee_salaries (id IN NUMBER, salary OUT NUMBER)
IS
BEGIN
SELECT salary INTO salary FROM employees WHERE employee_id = id;
END;
/

This command will create a stored procedure named get_employee_salaries.

How to use stored procedures

Once a stored procedure is created, it can be used in the application to be called when needed. Here is an example of how to call the stored procedure in the above example:

DECLARE
emp_salary NUMBER;
BEGIN
get_employee_salaries(100, emp_salary);
DBMS_OUTPUT.PUT_LINE('Employee 100 earns ' || TO_CHAR(emp_salary));
END;

This code will call the stored procedure get_employee_salaries and use 100 as the id input parameter. Store the result in emp_salary and then display it in the output. If executed successfully, the output will be similar to the following:

Employee 100 earns 4400

This example just shows an example of a simple Oracle stored procedure. In fact, stored procedures can perform many complex tasks and achieve scalability and repeatability in the database. For example, stored procedures can be used to implement complex data transformation, data cleaning, report generation, business process automation and other tasks.

Summary

Oracle stored procedures are a very powerful and useful tool that can make database applications more flexible, scalable and efficient. This article demonstrates how to create and use a simple Oracle stored procedure. In fact, stored procedures can achieve more complex tasks and provide powerful capabilities for database applications. Therefore, learning and mastering Oracle stored procedures is one of the skills that every database developer must master.

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