Best examples of Procedure in Oracle SQL

1. Data Manipulation and CRUD Operations

Procedures are commonly used to handle data manipulation tasks such as creating, updating, and deleting records in the database. For instance, in a scenario where you need to update an employee's salary, a procedure can encapsulate the logic for updating the salary of a specific employee. This helps in maintaining the consistency of the database and ensures that the logic is reusable across the application. Procedures also ensure that changes to the data are encapsulated in a single unit, making the code easier to maintain and reducing redundancy.

Example:
A procedure that updates the salary of an employee could be written as:

CREATE OR REPLACE PROCEDURE update_salary(emp_id IN NUMBER, new_salary IN NUMBER) IS
BEGIN
UPDATE employees SET salary = new_salary WHERE employee_id = emp_id;
END;

Here, you abstract the SQL operation into a reusable procedure. In an interview, you can highlight how procedures streamline data updates and ensure consistency across different parts of the application, reducing the need for repetitive SQL code.

1. Logging Mechanism and Error Handling

Procedures are also crucial for logging and error handling in applications, especially in scenarios where you need to track errors or log critical actions to help with auditing or debugging. By using a procedure, you can ensure that all errors or important events are logged into a central table, making it easier to troubleshoot or analyze historical data.

Example:
A procedure to log transaction errors could look like:

CREATE OR REPLACE PROCEDURE log_transaction_error(trans_id IN NUMBER, err_message IN VARCHAR2) IS
BEGIN
INSERT INTO transaction_errors (transaction_id, error_message, log_time)
VALUES (trans_id, err_message, SYSDATE);
COMMIT;
END;

In this example, any errors encountered during transactions are logged in a dedicated table. This approach provides a central point for logging, which is vital for maintaining application reliability and ease of troubleshooting. By mentioning such a logging mechanism, you demonstrate your ability to handle error management and maintain a robust logging infrastructure, which is essential in complex systems.