How to create and use stored procedures in Oracle database?

Creating and Using Stored Procedures in Oracle Database

A stored procedure is a set of SQL statements that are stored in the database and can be executed repeatedly. They help in reducing network traffic, improving performance, and encapsulating business logic.

Creating a Stored Procedure

To create a stored procedure in Oracle, you use the CREATE PROCEDURE statement. Here is the basic syntax:

CREATE OR REPLACE PROCEDURE procedure_name (parameter1 IN datatype, parameter2 OUT datatype)
IS
    -- Variable declarations
BEGIN
    -- SQL statements
EXCEPTION
    -- Exception handling
END;
/

Example:

Let's create a simple stored procedure that inserts a new employee record into an employees table.

CREATE OR REPLACE PROCEDURE insert_employee (
    p_id IN NUMBER,
    p_name IN VARCHAR2,
    p_salary IN NUMBER
)
IS
BEGIN
    INSERT INTO employees (id, name, salary)
    VALUES (p_id, p_name, p_salary);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

Using a Stored Procedure

To use the stored procedure, you can call it using the EXEC or EXECUTE command, or within a PL/SQL block.

Example 1: Using EXEC

EXEC insert_employee(101, 'John Doe', 5000);

Example 2: Using PL/SQL Block

BEGIN
    insert_employee(102, 'Jane Smith', 6000);
END;
/

Viewing Stored Procedures

To view the stored procedures in your database, you can query the USER_PROCEDURES or ALL_PROCEDURES views.

SELECT object_name, object_type
FROM user_objects
WHERE object_type = 'PROCEDURE';

Modifying a Stored Procedure

If you need to modify an existing stored procedure, you can use the CREATE OR REPLACE PROCEDURE statement. This will overwrite the existing procedure with the new definition.

Dropping a Stored Procedure

To remove a stored procedure from the database, use the DROP PROCEDURE statement.

DROP PROCEDURE insert_employee;

Using Tencent Cloud Services

For managing and optimizing your Oracle database on the cloud, Tencent Cloud offers a comprehensive suite of database services. TencentDB for Oracle provides a fully managed database service that helps you easily deploy, operate, and scale Oracle databases in the cloud. It includes features like automated backups, performance monitoring, and security enhancements to ensure your database runs smoothly and efficiently.