Introduction
Stored procedures and functions in Oracle are essential
components of database management, enabling the
encapsulation of SQL code for repeated execution. Stored
procedures are sets of SQL statements stored in the database and
executed as a single unit, while functions return a single value
and can be used within SQL statements. They enhance efficiency,
security, and maintainability by centralizing logic within the
database, reducing network traffic, and providing a layer of
abstraction for data access and manipulation. Every organization
has information that it must store and manage to meet its
requirements. For example, a corporation must collect and
maintain human resources records for its employees. This
information must be available to those who need it. An
information system is a formal system for storing and processing
information. An information system could be a set of cardboard
boxes containing manila folders along with rules for how to store
and retrieve the folders. In 1977, Larry Ellison, Bob Miner, and Ed
Oates started the consultancy Software Development
Laboratories, which became Relational Software, Inc. (RSI). In
1983, RSI became Oracle Systems Corporation and then later
Oracle Corporation.
Conceptual Understanding
Stored procedures and functions in Oracle are database objects
that encapsulate SQL and PL/SQL logic for repeated execution.
Stored procedures are sets of SQL statements stored in the
database, while functions return a single value and can be used
within SQL statements.
Concepts and principles of stored procedures and functions in
Oracle include:
1. Encapsulation: Logic is encapsulated within the database,
promoting modularity and code reuse.
2. Execution: Procedures and functions are executed within
the Oracle database engine.
3. Parameterization: They can accept input parameters for
dynamic behavior.
4. Return Values: Functions return a single value, while
procedures can modify the state of the database.
5. Transaction Control: They can include transaction control
statements for managing database transactions.
6. Error Handling: Exception handling blocks enable graceful
error handling.
7. Security: Privileges can be granted to control access to
procedures and functions.
8. Performance Optimization: Executing logic within the
database engine improves performance and reduces
network traffic.
9. Maintainability: Centralizing logic promotes code
maintainability and reduces redundancy.
Practical Implementation:
Example Stored procedures:
1- First we create a table and insert some data in.
create table departmentt (id number(2) primary key, name varchar(10),stage
number, d_name varchar(10));
insert into departmentt values (1,'Ahmed',3,'ISE');
insert into departmentt values (2,'Meer',2,'Civil');
insert into departmentt values (3,'Diyar',4,'HW');
insert into departmentt values (4,'Muhammed',1,'Mechanic');
insert into departmentt values (5,'Shafih',2,'ISE');
2- Then we create the procedure when we add other
procedures show us the text to tell us the code executed
successfully.
CREATE OR REPLACE PROCEDURE test_procedure1 IS
BEGIN
dbms_output.put_line('Test procedure executed successfully.');
END;
/
This code creates a stored procedure named "test_procedure1"
that simply outputs the text "Test procedure executed
successfully." using the DBMS_OUTPUT.PUT_LINE procedure.
CREATE OR REPLACE: This statement is used to either create a
new stored procedure or replace an existing one if it already
exists with the same name. If the procedure already exists, using
"CREATE OR REPLACE" ensures that it will be updated with the
new definition without causing an error.
PROCEDURE: This keyword indicates that what follows is the
definition of a stored procedure. A stored procedure is a named
PL/SQL block that performs a specific task or set of tasks when
called.
BEGIN: This keyword marks the beginning of the executable
section of the stored procedure. Everything between BEGIN and
END constitutes the body of the procedure, where the actual logic
is written.
END: This keyword marks the end of the executable section of the
stored procedure. All the code for the procedure's logic is written
between BEGIN and END.
3- This code creates or replaces a stored procedure named
"add_dept". It takes four input parameters: p_id, p_name,
p_stage, and p_dname, corresponding to the columns in
the "departmentt" table. Inside the procedure, it executes
an INSERT statement to add a new record to the
"departmentt1" table using the provided parameters. After
the insertion, it outputs the text "Department added."
using the DBMS_OUTPUT.PUT_LINE procedure.
CREATE OR REPLACE PROCEDURE add_student
(p_id IN departmentt.id%TYPE,
p_name IN departmentt.name%TYPE,
p_stage IN departmentt.stage%TYPE,
p_dname IN departmentt.d_name%TYPE)
IS
BEGIN
INSERT INTO departmentt VALUES (p_id, p_name, p_stage, p_dname);
dbms_output.put_line('Department added.');
END;
4- After that we insert data into add_student.
execute add_student(50, 'karzan', 10, 'ise');
Example 2:
1- We use same table when we created in above code.
2- This code creates or replaces a stored procedure named
"add_dept". It has two parameters: p_id as an input
parameter and p_dname as an output parameter. Inside the
procedure, it executes a SELECT statement to retrieve the
department name (d_name) from the "departmentt" table
based on the provided p_id. The retrieved department name
is then assigned to the output parameter p_dname.
CREATE OR REPLACE PROCEDURE add_dept
(p_id IN departmentt.id%TYPE, p_dname OUT departmentt.d_name%TYPE)
IS
BEGIN
SELECT d_name
INTO p_dname
FROM departmentt
WHERE id = p_id;
END;
/
3- This code block begins with a DECLARE section where a
variable "dname" of type VARCHAR(20) is declared. Then,
within the BEGIN-END block, the stored procedure
"add_dept" is called with the arguments 50 (for p_id) and
"dname" (for p_dname). After the procedure call, the value of
"dname" is outputted using the DBMS_OUTPUT.PUT_LINE
procedure. Essentially, it's calling a procedure to retrieve a
department name based on an ID and then printing that
department name.
declare
dname varchar(20);
begin
add_dept(50, dname);
dbms_output.put_line(dname);
end;
/
Output:
SQL> select * from departmentt;
ID NAME STAGE D_NAME
---------- ---------- ---------- ----------
1 Ahmed 3 ISE
2 Meer 2 Civil
3 Diyar 4 HW
4 Muhammed 1 Mechanic
5 Shafih 2 ISE
50 karzan 10 ise
7 rows selected.
Example of Function:
This SQL code creates or replaces a function named
"get_tax_amount" that takes a parameter "p_salary" of type
NUMBER. Inside the function, it calculates the tax amount by
multiplying the salary by 10% (0.10). Finally, the function returns
the calculated tax amount.
SQL> CREATE OR REPLACE FUNCTION get_tax_amount(p_salary number) RETURN
NUMBER IS 2 BEGIN
3 return (p_salary * 10 / 100);
4 END;
5/
Output:
FIRST_NAME LAST_NAME SALARY GET_TAX_AMOUNT (SALARY)
----------- ------------- ----------- ------------------------
Jennifer Pat 4400 440
Fay Mavris 13000 1300
Hermann Whalen 6500 650
Hartstein Michael 9000 900
4 rows selected.
Challenges and Solutions:
Performance Overhead: Complex stored procedures and functions
may cause performance issues, particularly with heavy processing
or large datasets. To address this, optimize the code by
minimizing unnecessary calculations, data manipulation, and
ensuring proper indexing on relevant tables. Security Risks: Poorly
designed or implemented stored procedures and functions can
result in security vulnerabilities such as SQL injection attacks or
unauthorized data access. Prevent SQL injection by using
parameterized queries, enforce access controls, and regularly
update security measures to mitigate risks. Maintenance
Complexity: Handling numerous stored procedures and functions
can lead to maintenance challenges. Standardize naming
conventions and documentation to enhance codebase
comprehension. Additionally, modularize procedures and
functions for better reusability and maintainability.
Use Cases:
Data Validation: Utilize stored procedures to enforce data
validation rules, ensuring that only valid data is inserted or
updated in the database.
Business Logic Implementation: Implement complex business
logic within stored procedures to maintain consistency and
integrity across various operations.
Performance Optimization: Use functions to encapsulate
frequently used calculations or operations, improving query
performance by reducing repetitive code execution.
Transaction Management: Employ stored procedures to manage
transactions, ensuring data consistency and integrity in multi-step
processes.
Performance Considerations:
Compilation Overhead: While stored procedures and functions
offer performance benefits by reducing network traffic, they incur
compilation overhead during execution. Pre-compile frequently
used procedures to minimize this overhead.
Resource Utilization: Long-running or resource-intensive
procedures can impact the overall performance of the database.
Monitor resource utilization and optimize procedures accordingly
to maintain optimal performance.
Caching: Leverage caching mechanisms to store frequently
accessed data or query results, reducing the need for repetitive
executions of stored procedures and functions.
Security Aspects:
SQL Injection Prevention: Use parameterized queries in stored
procedures to prevent SQL injection attacks, ensuring that user
inputs are properly sanitized and validated.
Access Control: Implement granular access controls and privileges
to restrict access to sensitive procedures and functions, ensuring
that only authorized users can execute them.
Encryption: Encrypt sensitive data within stored procedures and
functions to protect it from unauthorized access or tampering.
Audit Trails: Implement audit trails to track and monitor the
execution of stored procedures and functions, facilitating the
detection of suspicious activities or security breaches.
Application to Business or
Industry:
Finance: Stored procedures can be used to automate financial
calculations, such as interest accruals or risk assessments,
improving efficiency and accuracy in financial operations.
E-commerce: Functions can be employed to calculate shipping
costs or apply discounts based on predefined business rules,
enhancing the shopping experience for customers.
Healthcare: Stored procedures can facilitate patient data
management and medical record keeping, ensuring compliance
with regulatory requirements and maintaining data integrity.
Manufacturing: Functions can be utilized to optimize production
processes or perform quality control checks, leading to improved
productivity and product quality in manufacturing environments.
