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);

Anonymous block calls stored procedures

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

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);

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!