How to use PL/SQL to write a stored procedure in an Oracle database

To write a PL/SQL stored procedure, you need to use CREATE PROCEDURE to define the structure, including parameters, variable declarations, business logic and exception handling; for example, raise_salary adjusts the salary by entering the employee ID and percentage, and handles exceptions during execution to ensure data consistency.

To write a stored procedure in PL/SQL for an Oracle database, you need to understand the basic structure of a procedure and how to use the CREATE PROCEDURE statement. A stored procedure is a named block of code that performs one or more tasks and can accept input parameters, return values ​​through output parameters, and be reused across applications.

Basic Syntax of a PL/SQL Stored Procedure

A stored procedure is defined using the following structure:

The IS keyword (or AS ) begins the declaration section. Local variables, cursors, or nested procedures can be declared here. The actual logic goes in the BEGIN...END block.

Example: Creating a Simple Stored Procedure

Suppose you have a table called employees with columns employee_id , name , and salary . You want to create a procedure that increases an employee's salary by a given percentage.

This procedure takes two input parameters: the employee ID and the percentage increase. It retrieves the current salary, updates it, commits the change, and prints a message. If the employee isn't found, it handles the exception gracefully.

How to Execute a Stored Procedure

You can run the procedure using an anonymous PL/SQL block or from tools like SQL*Plus, SQL Developer, or other Oracle clients.

Alternatively, in SQL*Plus or SQL Developer, you can use:

Key Points to Remember

• Use IN parameters to pass values ​​into the procedure.
• Use OUT parameters if you need to return values ​​to the caller.
• Use IN OUT if a parameter serves both purposes.
• Always handle exceptions to avoid unhandled runtime errors.
• Use COMMIT or ROLLBACK carefully—stored procedures often run as part of larger transactions.
• Test your procedure with edge cases like invalid IDs or NULL inputs.

Basically, writing a stored procedure in PL/SQL involves defining parameters, declaring variables, writing business logic, and handling errors. Once created, it becomes part of the database schema and can be called from various applications.

The above is the detailed content of How to use PL/SQL to write a stored procedure in an Oracle database. For more information, please follow other related articles on the PHP Chinese website!