PLSQL and Oracle Databases

 Databases are the backbone of an application, it is crucial they allow for efficient storage, retrieval, and data manipulation. Oracle Database is a common and widespread database utilized in software. It is voted the most popular database engine in 2024. PL/SQL is an extension of SQL which is specifically created to use procedural constructs for different customized logic.

SQL 

SQL as we know is utilized specifically for: querying, manipulating, and managing data inside of a database with SQL statements. These statements are executed in line sequentially, and in order to chain together statements to combine multiple conditions or filters they have to be written in specific order. 

Specific features of SQL are:

-Simple queries (e.g., SELECT, INSERT, UPDATE, DELETE).

-Data definition (e.g., CREATE TABLE, ALTER TABLE).

-Data control (e.g., GRANT, REVOKE).

The difference between SQL and PL/SQL is the execution of statements. SQL is simple and executes in individual order in a straightforward manner. 

PL/SQL

PL/SQL is an extension of SQL created by Oracle to be used similar to programming languages in order to extend their functionality. This functionality includes the ability for developers to write more in depth statements such as: loops, variables and data types, advanced data structures, error handling, procedural constructs. 

Details of each feature: 

-Variables and Data Types- Ability to declare and manipulate variables which is not possible with standard SQL. These variables allow for storing values during execution which allows for more customization of requests. 

E.g., 

DECLARE

   total_sales NUMBER;

BEGIN

   SELECT SUM(sales) INTO total_sales FROM orders WHERE year = 2024;

   DBMS_OUTPUT.PUT_LINE('Total Sales: ' || total_sales);

END;

- Error handling- standard SQL does not have built in error handling like PL/SQL does. An EXCEPTION block can be utilized to handle errors gracefully. Custom error conditions can also be utilized to ensure procedures are done properly.

E.g. of custom error condition:

DECLARE

   low_salary EXCEPTION; -- Declare custom exception

   salary NUMBER := 2000; -- Example salary value

   min_salary CONSTANT NUMBER := 3000; -- Minimum allowable salary

BEGIN

   -- Check if salary is below the threshold

   IF salary < min_salary THEN

      RAISE low_salary; -- Trigger the custom exception

   END IF;

   DBMS_OUTPUT.PUT_LINE('Salary is acceptable.');

EXCEPTION

   WHEN low_salary THEN -- Handle the custom exception

      DBMS_OUTPUT.PUT_LINE('Error: Salary is below the minimum allowable threshold.');

END;

Procedural Constructs- A procedural construct is a programming structure that allows for specific decision making from a developer. It allows for control of constructs to create complex and specific conditions. Put it simply, these procedural constructs are- control structures, loops, exceptions, functions (reusable blocks), cursors, blocks, triggers. 

i) Control structures: IF-ELSE / IF-THEN-ELSE and CASE / CASE WHEN 

ii) Loops: Basic standard loop (LOOP exit when condition met), While loops (while condition LOOP), For loop (for counter in startValue .. endValue LOOP)

iii) Exception handling: Handles errors during runtime gracefully 

iv) Procedures and Functions: Allows for functions / reusable blocks of code. Procedures do not return values but can perform actions, however functions can do both which is perform actions and return values. 

v) Cursors: Row by row processing both explicitly and implicitly. Allows for fetching/processing multiple rows of data in controlled manner 

Explicit cursors- manually declared by developer using CURSOR keyword 

Implicit cursors- created automatically by Oracle when certain statements are used (SELECT INFO, INSERT, UPDATE, or DELETE)

vi) Transaction management: allows for advanced control such as commit, roll back, and save points in a transaction 

vii) Dynamic SQL: Can execute SQL statements at runtime (using EXECUTE IMMEDIATE or DBMS_SQL)

viii) Packages and Utilities: Built in packages for debugging, file handling, and scheduling jobs

ix) Advanced data structures: Advanced data type for data management and manipulation such as collections and records

x) Modularization and Reusability: Procedures, functions, and packages allows for modularity and reusability of code and queries 

xi) Triggers/Automation : allows for automation of tasks such as event logging/storing, security authorizations, derived column values