Procedure in Oracle With Example
Stored procedures in Oracle are powerful tools that allow developers and database administrators to encapsulate complex business logic inside the database. This not only improves efficiency but also ensures that the logic is centralized and easy to manage. In this blog, we will explore what procedures are, how they work, and how you can create and use them effectively. Explore the Procedure in Oracle with Example. Learn how to create and execute stored procedures effectively to streamline database operations and improve performance.
What is a Procedure in Oracle?
A procedure is a named PL/SQL block that performs a specific task or set of tasks. Unlike functions, procedures do not return a value, but they can return multiple values via OUT parameters. They are often used to execute a series of SQL or PL/SQL statements.
Why Use Procedures?
- Code Reusability: Procedures allow you to write code once and reuse it multiple times across different applications.
- Security: By using procedures, you can encapsulate and control access to critical database operations.
- Performance: Procedures can reduce network traffic since the PL/SQL engine executes all the logic on the server side.
- Maintainability: It is easier to manage and update business logic when procedures are centralized.
Structure of an Oracle Procedure
A procedure typically consists of the following sections:
- Header: Defines the name of the procedure and any input/output parameters.
- Declarative Section: Optional; used for declaring variables and cursors.
- Executable Section: Contains the logic and SQL statements that the procedure will execute.
- Exception Section: Optional; handles exceptions that occur during the execution of the procedure.
Syntax:
CREATE OR REPLACE PROCEDURE procedure_name
(parameter_1 [IN | OUT | IN OUT] datatype,
parameter_2 [IN | OUT | IN OUT] datatype)
IS
— Declaration Section (Optional)
BEGIN
— Executable Section
— SQL and PL/SQL code here
EXCEPTION
— Exception Handling Section (Optional)
END procedure_name;
- IN: The parameter is passed into the procedure.
- OUT: The procedure returns a value using this parameter.
- IN OUT: The parameter is both passed into and returned from the procedure.
Example:
CREATE OR REPLACE PROCEDURE update_salary (
emp_id IN NUMBER,
increment_percentage IN NUMBER
)
IS
current_salary NUMBER;
BEGIN
— Retrieve the current salary of the employee
SELECT salary INTO current_salary
FROM employees
WHERE employee_id = emp_id;
— Update the salary based on the increment
UPDATE employees
SET salary = current_salary + (current_salary * increment_percentage / 100)
WHERE employee_id = emp_id;
— Commit the transaction
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Employee not found.’);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error occurred: ‘ || SQLERRM);
END update_salary;
Explanation of Example:
- The procedure update_salary takes two input parameters:
- emp_id: The employee’s ID.
- increment_percentage: The percentage by which to increase the salary.
- It selects the current salary of the employee, calculates the new salary based on the increment percentage, and updates the employees table.
- If the employee is not found, an exception is handled using the NO_DATA_FOUND block.
Executing a Procedure
Once you’ve created a procedure, you can execute it using the EXEC command or an anonymous PL/SQL block.
Using EXEC:
EXEC update_salary(1001, 10);
This will increase the salary of the employee with employee_id = 1001 by 10%.
Using PL/SQL Block:
BEGIN
update_salary(1001, 10);
END;
Procedures with OUT Parameters
In some cases, you might want a procedure to return values. For this, you can use the OUT parameter. Here’s an example:
Example:
CREATE OR REPLACE PROCEDURE get_employee_details (
emp_id IN NUMBER,
emp_name OUT VARCHAR2,
emp_salary OUT NUMBER
)
IS
BEGIN
SELECT first_name || ‘ ‘ || last_name, salary
INTO emp_name, emp_salary
FROM employees
WHERE employee_id = emp_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Employee not found.’);
END get_employee_details;
Calling the Procedure with OUT Parameters:
DECLARE
name VARCHAR2(50);
salary NUMBER;
BEGIN
get_employee_details(1001, name, salary);
DBMS_OUTPUT.PUT_LINE(‘Name: ‘ || name || ‘, Salary: ‘ || salary);
END;
In this example, the procedure get_employee_details retrieves the name and salary of an employee and returns these values through the OUT parameters emp_name and emp_salary.
Managing Errors in Procedures
When working with stored procedures, error handling is crucial. You can use the EXCEPTION block to manage any errors that occur during the execution of your procedure.
Common Oracle Exceptions:
- NO_DATA_FOUND: Triggered when a query returns no rows.
- TOO_MANY_ROWS: Triggered when a query returns more than one row, and you are using INTO.
- OTHERS: A catch-all exception that handles any errors not explicitly addressed.
Best Practices for Writing Procedures
- Use Parameter Modes Wisely: Avoid using too many IN OUT parameters as it may make the procedure hard to understand.
- Optimize SQL Queries: Always aim for optimized SQL queries inside procedures to enhance performance.
- Exception Handling: Ensure robust exception handling to make the procedure resilient and avoid unnecessary failures.
- Use Comments: Comment on the logic inside procedures, especially when handling complex business logic.
Conclusion
Oracle procedures provide a structured way to encapsulate and execute business logic within the database. They enhance performance, offer security, and improve the maintainability of your applications. Understanding how to create and manage procedures effectively can significantly improve your database development experience. With proper planning, procedures can help automate complex tasks and ensure that your logic remains consistent and easy to update.
Do visit our channel to learn more: Click Here
Author:
Suraj Kale
Call the Trainer and Book your free demo Class For PLSQL Call now!!!
| SevenMentor Pvt Ltd.
© Copyright 2021 | SevenMentor Pvt Ltd.