How to write stored procedures in Oracle

With the increase of enterprise needs and system complexity, database stored procedures are receiving more and more attention. As a large-scale relational database, Oracle's stored procedures play an increasingly important role when processing massive amounts of data. So, how to write stored procedures in Oracle?

1. What is a stored procedure?

A stored procedure is a set of SQL statements that are stored and executed in the database. Some specific tasks can be completed by executing the stored procedure. This has the following advantages:

1. Improves the execution efficiency of the database.
2. Simplifies the writing of SQL statements and reduces the difficulty of development.
3. The security of the database has been improved.
4. can effectively ensure data consistency.

2. Syntax format of stored procedures

In Oracle, writing stored procedures needs to follow a certain syntax format. The basic syntax is as follows:

CREATE OR REPLACE PROCEDURE Stored procedure name
(parameter 1, parameter 2,..., parameter n)
IS

变量1 数据类型;
变量2 数据类型;
……

BEGIN

存储过程体；
EXCEPTION
异常处理语句；

END Stored procedure name;

The parameter list and variable declaration parts can be omitted, but the stored procedure body and exception handling statement parts are required.

3. Example of stored procedure

Next, let’s take a look at a simple stored procedure, which is used to query specified employee information based on the incoming parameters.

Create or Replace Procedure Find_EMPLOYEE
(## P_employee_id in Number,
P_emp_name in Varchar2,
P_EMALARY OUMBER,
P_em p_dept_id out number
)
is
BEGIN

SELECT salary, dept_id
INTO p_emp_salary, p_emp_dept_id
FROM employee
WHERE employee_id = p_employee_id AND employee_name = p_emp_name;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('查询出错' || SQLERRM);

END find_employee;

In this stored procedure, we define 4 parameters: p_employee_id and p_emp_name are used to query employee information, p_emp_salary and p_emp_dept_id are used to return the employee's Salary and department ID. If an exception occurs during the query, we use DBMS_OUTPUT to output exception information.

4. Calling stored procedures

We can call stored procedures in the following two ways:

Anonymous block calls stored procedures

In SQL Developer, select "SQL Worksheet" and enter the following code:

DECLARE

emp_salary NUMBER;
emp_dept_id NUMBER;
BEGIN
find_employee(100,'Tom' ,emp_salary,emp_dept_id);
DBMS_OUTPUT.PUT_LINE('salary = ' || emp_salary || ', dept_id = ' || emp_dept_id);
END;

Called through the application Stored procedure

In a Java program, we can use JDBC to connect to the Oracle database and then call the stored procedure. The sample code is as follows:

CallableStatement stmt = conn.prepareCall("{call find_employee(?,?,?,?)}");

stmt.setInt(1, 100);
stmt .setString(2, "Tom");
stmt.registerOutParameter(3, java.sql.Types.NUMERIC);
stmt.registerOutParameter(4, java.sql.Types.NUMERIC);
stmt .execute();
int emp_salary = stmt.getInt(3);
int emp_dept_id = stmt.getInt(4);
System.out.println("salary = " emp_salary ", dept_id = " emp_dept_id);

Through the above code, we can easily call the stored procedure, obtain the query results, and output the result information.

Summary: Stored procedures are a very important part of the Oracle database. It can improve data processing efficiency and ensure data consistency and security, thus bringing great convenience to enterprise data processing. Through this article, we can clearly understand the basic syntax of Oracle stored procedures, and also master the examples and calling methods of stored procedures.

The above is the detailed content of How to write stored procedures in Oracle. For more information, please follow other related articles on the PHP Chinese website!