1. Procedures and Functions
1.1 Introduction and Advantages
Definition:
- Procedures: A
procedure is a subprogram that performs a specific action. It executes a series
of SQL statements and PL/SQL code.
- Functions: A
function is similar to a procedure but returns a single value. It computes and
returns a value based on input parameters.
Usage:
- Procedures:
Used to perform tasks like data validation, formatting, and complex operations
that do not necessarily need to return a value.
- Functions:
Often used in SQL queries where a value needs to be computed on the fly.
Advantages:
- Reusability:
Can be reused across multiple applications.
- Modularity:
Allows breaking down complex operations into simpler subprograms.
-
Maintainability: Easier to update or modify small parts of the code.
- Security:
Restrict direct access to data by encapsulating business logic.
1.2 Procedure Creation
Definition:
A procedure is a set of PL/SQL statements
grouped together to perform a specific task.
Syntax:
CREATE [OR REPLACE] PROCEDURE
procedure_name (parameter_list)
IS
BEGIN
-- PL/SQL statements
END procedure_name;
Normal Example:
CREATE OR REPLACE PROCEDURE update_salary
(emp_id NUMBER, increment NUMBER)
IS
BEGIN
UPDATE employees
SET salary = salary + increment
WHERE employee_id = emp_id;
COMMIT;
END update_salary;
Real-Time Example:
In a payroll system, a procedure might be
used to update the salaries of employees after an annual appraisal.
1.3 Function Creation
Definition:
A function is a subprogram that computes a
value and returns it.
Syntax:
CREATE [OR REPLACE] FUNCTION function_name
(parameter_list)
RETURN return_datatype
IS
BEGIN
-- PL/SQL statements
RETURN some_value;
END function_name;
Normal Example:
CREATE OR REPLACE FUNCTION
get_employee_bonus (emp_id NUMBER)
RETURN NUMBER
IS
bonus NUMBER;
BEGIN
SELECT salary * 0.10 INTO bonus FROM employees WHERE employee_id =
emp_id;
RETURN bonus;
END get_employee_bonus;
Real-Time Example:
In a retail application, a function can
calculate the discount percentage for a customer based on their purchase
history and loyalty program status.
2. Package
2.1 Introduction and Use of Package
Definition:
A package is a schema object that groups
logically related PL/SQL types, variables, procedures, and functions. It helps
organize and encapsulate code for easier management and reuse.
Usage:
- Improves code
organization.
- Hides
implementation details.
- Provides
modular programming constructs.
- Enhances
performance through session-level caching.
2.2 Components of Package
- Package Specification: The
interface to the package. It declares public types, variables, constants,
exceptions, cursors, and subprograms accessible from outside the package.
- Package Body: Contains the implementation of the subprograms declared in the
specification. It may also have private declarations not accessible externally.
2.3 Overloading of Procedure and Function through Package
Definition:
Overloading allows multiple procedures or
functions with the same name in the same package if they have different
parameter lists.
Usage:
Simplifies interfaces by using the same
name for similar operations on different data types or parameters.
Example:
Package Specification:
CREATE OR REPLACE PACKAGE emp_pkg AS
PROCEDURE update_info(p_emp_id NUMBER, p_salary NUMBER);
PROCEDURE update_info(p_emp_id NUMBER, p_job_title VARCHAR2);
FUNCTION get_info(p_emp_id NUMBER) RETURN VARCHAR2;
END emp_pkg;
Package Body:
CREATE OR REPLACE PACKAGE BODY emp_pkg AS
PROCEDURE update_info(p_emp_id NUMBER, p_salary NUMBER) IS
BEGIN
UPDATE employees SET salary = p_salary WHERE employee_id = p_emp_id;
COMMIT;
END update_info;
PROCEDURE update_info(p_emp_id NUMBER, p_job_title VARCHAR2) IS
BEGIN
UPDATE employees SET job_title = p_job_title WHERE employee_id =
p_emp_id;
COMMIT;
END update_info;
FUNCTION get_info(p_emp_id NUMBER) RETURN VARCHAR2 IS
emp_details VARCHAR2(100);
BEGIN
SELECT name || ' - ' || job_title INTO emp_details
FROM employees
WHERE employee_id = p_emp_id;
RETURN emp_details;
END get_info;
END emp_pkg;
Real-Time Example:
A human resources management system might
use overloading to update various employee attributes using the same procedure
name.
3. Trigger
3.1 Introduction and Use of Triggers
Definition:
A trigger is a stored procedure that
automatically executes in response to specific events on a table or view, such
as insertions, updates, or deletions.
Usage:
- Enforces
business rules.
- Maintains
audit trails.
- Synchronizes
data.
- Validates
input data.
3.2 Types of Triggers
- Row-Level
Triggers: Fired for each row affected by the triggering statement.
-
Statement-Level Triggers: Fired once per SQL statement regardless of the number
of rows.
- Before vs.
After Triggers: Before triggers execute before the event, after triggers
execute after.
Example (Row-Level Trigger):
CREATE OR REPLACE TRIGGER trg_update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
:NEW.last_modified := SYSDATE;
END trg_update_timestamp;
3.3 Use of Raise_Application_Error Procedure
Definition:
The RAISE_APPLICATION_ERROR procedure is
used to return custom error messages, enforcing business rules in your database
application.
Syntax:
RAISE_APPLICATION_ERROR(error_number,
message);
Example:
CREATE OR REPLACE TRIGGER trg_salary_check
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative.');
END IF;
END trg_salary_check;
Real-Time Example:
In a banking application, triggers can
ensure no transaction results in a negative balance, enforcing financial rules.
4. Exception and Error Handling
4.1 Oracle’s Named Exception Handlers
Definition:
Oracle provides predefined (named)
exceptions such as NO_DATA_FOUND or TOO_MANY_ROWS to handle common error
conditions.
Usage:
Helps manage errors gracefully by allowing
specific responses to known error conditions.
Example:
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = p_emp_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found with the provided ID.');
END;
4.2 User Named Exception Handlers
Definition:
User-named exceptions are predefined
exceptions that are given a custom name to improve code readability.
Usage:
Allows more descriptive error handling in
PL/SQL code.
Example:
DECLARE
employee_missing EXCEPTION;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = p_emp_id;
IF v_salary IS NULL THEN
RAISE employee_missing;
END IF;
EXCEPTION
WHEN employee_missing THEN
DBMS_OUTPUT.PUT_LINE('Employee record is missing or incomplete.');
END;
4.3 User Defined Exception Handlers
Definition:
User-defined exceptions are created by
developers to handle specific error conditions not covered by Oracle’s
predefined exceptions.
Usage:
Declared in the declaration section and
raised explicitly when needed.
Example:
DECLARE
invalid_salary EXCEPTION;
BEGIN
IF p_salary < 0 THEN
RAISE invalid_salary;
END IF;
EXCEPTION
WHEN invalid_salary THEN
DBMS_OUTPUT.PUT_LINE('Error: The salary provided is not valid.');
END;
Real-Time Example:
In an e-commerce platform, user-defined
exceptions can manage errors related to order processing, such as invalid
coupon codes or stock shortages.
Comments
Post a Comment