Detailed introduction to the syntax of Oracle stored procedures

Oracle stored procedure is a piece of pre-compiled PL/SQL code that can be called and executed when needed. Stored procedures can encapsulate some business logic, improve SQL execution efficiency, and can encapsulate complex operations into a simple operation by calling the interface, improving the maintainability and reliability of database applications. This article will introduce the syntax of Oracle stored procedures in detail.

Create Oracle stored procedures

In Oracle, there are two main ways to create stored procedures. One is to use the CREATE PROCEDURE statement to create a stored procedure, and the other is to use a PL/SQL tool to create a stored procedure. stored procedures.

The syntax for using the CREATE PROCEDURE statement to create a stored procedure is as follows:

CREATE [OR REPLACE] PROCEDURE procedure_name
(parameter_name [IN | OUT | IN OUT] data_type, ...)
IS
[DECLARATIONS]
BEGIN
Statements
[EXCEPTION]
Exception-handling statements
END [procedure_name];

Among them, CREATE PROCEDURE is to create The statement of the stored procedure, OR REPLACE is an optional keyword, indicating that if the stored procedure already exists, replace it; procedure_name is the name of the stored procedure; parameter_name is the input and output parameter name of the stored procedure; data_type is the data type of the parameter; IS It is the end symbol of the stored procedure header; DECLARATIONS is the optional variable or constant declaration section; between BEGIN and END is the main body of the stored procedure, which contains SQL statements and PL/SQL code.

The process of using PL/SQL tools to create stored procedures is as follows:

1. Open PL/SQL Developer or SQL Developer and other PL/SQL tools;
2. In "File "Select "New" in the menu, and then select "Stored Procedure" in the new window;
3. Enter the code of the stored procedure in the window;
4. Click the "Save" button to save it as a storage Process documentation.

Parameter passing

Oracle stored procedures can receive and return parameters, which can be input parameters, output parameters or input and output parameters. The input parameters are the parameter values ​​passed in the stored procedure, the output parameters are the result values ​​calculated in the stored procedure, and the input and output parameters play the roles of input and output parameters at the same time.

When defining parameters of a stored procedure, you need to specify the parameter name, data type and parameter direction (IN, OUT or IN OUT). For example:

CREATE OR REPLACE PROCEDURE emp_salary_increase
(emp_id IN NUMBER, increase_percent IN NUMBER, new_salary OUT NUMBER)
IS
BEGIN
SELECT salary (salary * (increase_percent/100) ) INTO new_salary FROM employees WHERE employee_id = emp_id;
END;

In this example, the stored procedure emp_salary_increase receives 3 parameters, emp_id and increase_percent are input parameters, and new_salary is an output parameter.

When referencing stored procedure parameters, the abbreviation of the stored procedure name must be added before the parameter name. For example:

emp_salary_increase(100, 10, :new_salary);

In the above code, 100 and 10 are the parameter values ​​​​of emp_id and increase_percent, and :new_salary is the new_salary output calculated by the stored procedure parameter value.

Conditional branches and loop structures

Like other programming languages, Oracle stored procedures also support conditional branches and loop structures. Common branch statements include IF-THEN, IF-THEN-ELSE and CASE statements, and loop statements include WHILE and FOR statements.

The syntax of the IF-THEN statement is as follows:

IF condition THEN
-- execute statement block
END IF;

In this example, if condition If the value is TRUE, the statement block is executed.

The syntax of the IF-THEN-ELSE statement is as follows:

IF condition THEN
-- execute statement block
ELSE
-- execute another statement block
END IF;

In this example, if the value of condition is TRUE, the first statement block is executed; otherwise, the second statement block is executed.

The syntax of the CASE statement is as follows:

CASE case_expression
WHEN when_expression THEN

-- execute statement block

WHEN when_expression THEN

-- execute another statement block

ELSE

-- default block

END CASE;

In this example, case_expression is the comparison expression in the CASE statement, and when_expression is the conditional expression in the CASE statement. There can be multiple WHEN branches.

The syntax of the WHILE loop is as follows:

WHILE condition LOOP
-- execute statement block
END LOOP;

In this example, the while loop follows condition The result of the expression loops through the block of statements.

The syntax of the FOR loop is as follows:

FOR index IN [REVERSE] lower_bound..upper_bound LOOP
-- execute statement block
END LOOP;

In In this example, index is an integer variable, lower_bound and upper_bound are the starting and ending values ​​of the loop respectively. If you use the REVERSE keyword, the loop iterates from high to low.

Exception handling

In Oracle stored procedures, if an exception occurs, it needs to be handled as elegantly as possible. If exception handling is not considered, the stored procedure may not be able to complete execution, or various problems may occur.

Exception handling can be completed using the EXCEPTION statement block, which contains one or more WHEN-THEN blocks to perform different processing operations under different exception situations. Each WHEN-THEN block must specify the exception name and corresponding exception handling operation. For example:

DECLARE
 emp_id NUMBER(10);
 emp_salary NUMBER(10, 2);
BEGIN
 SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
EXCEPTION
 WHEN no_data_found THEN

emp_id := NULL;

WHEN others THEN

RAISE;

END;

在这个例子中，如果SELECT语句找不到数据行，则没有找到数据的异常被捕获。异常处理程序将赋值NULL值到emp_id变量。如果有其他未知异常，则使用RAISE语句继续抛出异常。

结论

本文介绍了Oracle存储过程的语法，包括创建存储过程、参数传递、条件分支、循环结构和异常处理。了解Oracle存储过程的语法和使用方法可以让你更好地利用Oracle数据库的强大功能，提高开发效率和应用程序的可靠性。

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