Example of oracle stored procedure

A stored procedure is a precompiled database program that contains a set of SQL statements and control statements that can be called when needed. This article will introduce the basic knowledge and examples of Oracle database stored procedures.

1. Basics of stored procedures

1.1 Advantages of stored procedures

Stored procedures are an effective method to improve database performance. They improve the efficiency of application interaction with the database because SQL statements are precompiled on the database side, allowing them to complete operations more quickly when called. It also increases data security because stored procedures can perform permission checks before creating and modifying data in the database.

1.2 Creation of stored procedures

You can use Oracle SQL development tools to create stored procedures. Oracle SQL Developer and SQL Plus are commonly used tools.

The following is the basic syntax for creating a stored procedure:

CREATE [OR REPLACE] PROCEDURE procedure_name
([parameter_name IN/OUT datatype [, parameter_name IN/OUT datatype …]])
IS
BEGIN
statement(s);
EXCEPTION
exception_handler;
END;

The parameters are optional, '[OR REPLACE]' command You can specify that the application must exist and retain the state of the stored procedure.

1.3 Input and output parameters of stored procedures

Stored procedures can accept input parameters and output parameters. Input parameters can be used to perform conditional operations within a stored procedure or to pass data to a stored procedure. Output parameters are used to return information such as values ​​or specified values ​​in the output process.

The following is how some parameters interact:

IN: Input parameters are used to pass values ​​to the stored procedure.

OUT: Output parameters are not used for input data, but can return values ​​through stored procedures.

INOUT: Input/output parameters allow a value to be passed as a parameter and changed through the execution return value of the stored procedure.

1.4 Exception handling of stored procedures

Stored procedures can handle exceptions like functions. When an error occurs in the stored procedure, you can set up an exception handling. It can implement the management of custom error messages and use specified behaviors to submit these errors when errors occur.

The following is the basic syntax for creating exception handling:

DECLARE
exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT (exception_name, error_code);
BEGIN
statement(s) ;
EXCEPTION

  WHEN exception_name THEN
     statement(s);

END;

2. Stored procedure examples

The following are some common stored procedure examples:

2.1 Stored procedure Simple query

The following is a simple stored procedure example, which will output data that meets the conditions in the table:

CREATE OR REPLACE PROCEDURE get_emp_data
(
ID IN NUMBER,
NAME OUT VARCHAR2,
SALARY OUT NUMBER
)
IS
BEGIN
SELECT employee_name,salary INTO NAME,SALARY FROM employees WHERE employee_id = ID;
END;

The above stored procedure instance needs to pass in two parameters: ID is a required input parameter, which defines the employee ID for which information is to be queried; while name and salary are output parameters, which accept the values ​​of the corresponding columns in the query results. .

To retrieve the value of the output parameter of the stored procedure, you can call the stored procedure like a function:

DECLARE
emp_name VARCHAR2(20);
emp_salary NUMBER(10,2);
BEGIN
get_emp_data (100,emp_name,emp_salary);
DBMS_OUTPUT.PUT_LINE('Name: ' || emp_name);
DBMS_OUTPUT.PUT_LINE('Salary: ' || emp_salary);
END;

In the above code, the stored procedure parameter ID is set to 100, so the employee's name and salary will be returned.

2.2 Insertion operation of stored procedure

The following is an example of a stored procedure, which implements the function of inserting a row of data into the specified employee roster:

CREATE OR REPLACE PROCEDURE add_employee
(
ID IN NUMBER,
NAME IN VARCHAR2,
AGE IN NUMBER,
SALARY IN NUMBER
)
IS
BEGIN
INSERT INTO employees VALUES ( ID,NAME,AGE,SALARY);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Employee added.');
EXCEPTION
WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Error adding employee.');

END;

The above stored procedure example requires 4 input parameters: employee ID, employee name, employee age and employee salary, and then inserts them into the "employees" table. When the insertion is successful, the "employee added" message will be prompted, and when the insertion fails, the "Error adding employee" message will be prompted.

2.3 Update operation of stored procedure

The following example provides the function of increasing the salary of an employee with a specified ID in the employee table by 10%:

CREATE OR REPLACE PROCEDURE increase_employee_salary
(
ID IN NUMBER
)
IS
CURSOR c_employee_salary IS

SELECT salary FROM employees WHERE employee_id = ID;

v_employee_salary NUMBER;
BEGIN
OPEN c_employee_salary;
FETCH c_employee_salary INTO v_employee_salary;
v_employee_salary := v_employee_salary * 1.1;
UPDATE employees SET salary = v_employee_salary WHERE employee_id = ID;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Salary increased.');
EXCEPTION
WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('Employee not found.');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Error increasing salary.');

END;

The above stored procedure example requires 1 input parameter: employee ID, which gets the employee's salary based on the employee ID, multiplies it by 1.1 and updates it to the table. When updated correctly, the message "salary increased" will be prompted; when the employee cannot be found, the message "employee not found" will be prompted; when other errors occur, the message "error increasing salary" will be prompted.

Summary

In this article, we introduced the basics of Oracle database stored procedures and some examples. Stored procedures can improve database performance and data security, and are very useful for tasks that need to be performed frequently. Through some examples, you can better understand how to create and use Oracle stored procedures.

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