PL/SQL

 PL/SQL is a block structured language that can have multiple blocks in it.

PL/SQL language such as conditional statements, loops, arrays, string, exceptions, collections, records, triggers, functions, procedures, cursors etc

SQL stands for Structured Query Language i.e. used to perform operations on the records stored in database such as inserting records, updating records, deleting records, creating, modifying and dropping tables, views etc.

What is PL/SQL

PL/SQL is a block structured language. The programs of PL/SQL are logical blocks that can contain any number of nested sub-blocks. Pl/SQL stands for "Procedural Language extension of SQL" that is used in Oracle. PL/SQL is integrated with Oracle database (since version 7). The functionalities of PL/SQL usually extended after each release of Oracle database. Although PL/SQL is closely integrated with SQL language, yet it adds some programming constraints that are not available in SQL.

PL/SQL Functionalities

PL/SQL includes procedural language elements like conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variable of those types and triggers. It can support Array and handle exceptions (runtime errors). After the implementation of version 8 of Oracle database have included features associated with object orientation. You can create PL/SQL units like procedures, functions, packages, types and triggers, etc. which are stored in the database for reuse by applications.

With PL/SQL, you can use SQL statements to manipulate Oracle data and flow of control statements to process the data.

The PL/SQL is known for its combination of data manipulating power of SQL with data processing power of procedural languages. It inherits the robustness, security, and portability of the Oracle Database.

PL/SQL is not case sensitive so you are free to use lower case letters or upper case letters except within string and character literals. A line of PL/SQL text contains groups of characters known as lexical units. It can be classified as follows:

Delimeters

Identifiers

Literals

Comments

Syllabus: Topics

1. **Relationship between SQL & PL/SQL**: 

   - SQL (Structured Query Language) is a language used to manage and manipulate relational databases, performing tasks such as querying, updating, and deleting data.

   - PL/SQL (Procedural Language/Structured Query Language) is an extension of SQL that includes procedural features, such as loops, conditional statements, and exception handling. PL/SQL allows for the creation of stored procedures, functions, triggers, and more, enhancing the capabilities of SQL by enabling more complex logic and processing within the database.

2. **Advantages of PL/SQL**:

   - Provides procedural constructs like loops, conditional statements, and exception handling for more complex logic.

   - Enhances performance by reducing the need for multiple round-trips between the database and application, as logic can be executed within the database itself.

   - Improves code reusability and maintainability through the use of stored procedures and functions.

   - Increases security by encapsulating sensitive logic within the database and controlling access through permissions.

   - Facilitates easier integration with other programming languages and systems.

3. **Arithmetic & Expressions in PL/SQL**:

   - PL/SQL supports arithmetic operations such as addition, subtraction, multiplication, and division using standard operators (+, -, *, /).

   - Expressions can be composed using variables, literals, functions, and operators to perform calculations and manipulate data.

4. **Loops and Conditional Statements in PL/SQL**:

   - PL/SQL provides several types of loops, including FOR loops, WHILE loops, and LOOP statements, allowing for iterative processing of data.

   - Conditional statements like IF-THEN-ELSE and CASE statements enable branching logic based on conditions, allowing different paths of execution depending on the evaluation of expressions.

5. **Exceptions Handling**:

   - PL/SQL allows for the handling of errors and exceptions using the EXCEPTION block, which can catch specific exceptions or handle general errors.

   - Exception handling mechanisms include raising exceptions, handling predefined exceptions, and defining custom exceptions to handle specific error conditions gracefully.

6. **Cursor Management**:

   - Cursors in PL/SQL are used to process result sets returned by SELECT queries.

   - Cursors can be implicitly or explicitly declared and manipulated to fetch rows, iterate over result sets, and perform operations on retrieved data.

7. **Triggers**:

   - Triggers in PL/SQL are special types of stored procedures that are automatically executed in response to certain events, such as INSERT, UPDATE, or DELETE operations on a table.

   - Triggers can be used to enforce data integrity, implement business rules, or audit changes to the database.

8. **Functions & Procedures**:

   - Functions and procedures in PL/SQL are reusable blocks of code that encapsulate logic to perform specific tasks.

   - Functions return a single value and can be used in SQL queries or other PL/SQL code.

   - Procedures do not return a value directly but can modify data, perform operations, or call other procedures/functions.

   - Both functions and procedures can have parameters to accept input values and can be stored in the database for reuse.

To create a simple PL/SQL program that demonstrates various concepts such as arithmetic operations, loops, conditional statements, exception handling, cursor management, triggers, functions, and procedures. Here's a program that calculates the factorial of a number:

```sql

-- Create a function to calculate factorial

CREATE OR REPLACE FUNCTION factorial(n IN NUMBER) RETURN NUMBER IS

    result NUMBER := 1;

BEGIN

    -- Check if n is negative, raise an exception if it is

    IF n < 0 THEN

        RAISE_APPLICATION_ERROR(-20001, 'Factorial of negative number is undefined');

    END IF;

    

    -- Iterate from 1 to n and calculate factorial

    FOR i IN 1..n LOOP

        result := result * i;

    END LOOP;

    

    -- Return the factorial

    RETURN result;

END factorial;

/

-- Test the factorial function

DECLARE

    num INTEGER := 5;

    fact_result INTEGER;

BEGIN

    -- Calculate factorial of num using the factorial function

    fact_result := factorial(num);

    

    -- Display the result

    DBMS_OUTPUT.PUT_LINE('Factorial of ' || num || ' is ' || fact_result);

EXCEPTION

    -- Catch any exceptions raised by the factorial function

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);

END;

/

```

Explanation:

1. **Function Definition (factorial):**

   - `CREATE OR REPLACE FUNCTION factorial(n IN NUMBER) RETURN NUMBER IS`: Defines a function named `factorial` that takes an input parameter `n` of type `NUMBER` and returns a `NUMBER`.

2. **Exception Handling:**

   - `IF n < 0 THEN ...`: Checks if the input number `n` is negative. If it is, raises a custom exception using `RAISE_APPLICATION_ERROR`.

3. **Loop (FOR loop):**

   - `FOR i IN 1..n LOOP ...`: Iterates from 1 to `n` and calculates the factorial by multiplying `result` with each value of `i`.

4. **Return Statement:**

   - `RETURN result;`: Returns the calculated factorial value.

5. **Test Block (DECLARE - BEGIN - END):**

   - `DECLARE ... BEGIN ... END;`: Declares variables, performs calculations, and displays the result.

   - `fact_result := factorial(num);`: Calls the `factorial` function with a test number (`num`) and assigns the result to `fact_result`.

   - `DBMS_OUTPUT.PUT_LINE(...)`: Displays the factorial result using `DBMS_OUTPUT.PUT_LINE`.

   

6. **Exception Handling in Test Block:**

   - `WHEN OTHERS THEN ...`: Catches any exceptions that occur during the execution of the test block and displays the error message using `SQLERRM`.

This program demonstrates the use of functions, loops, conditional statements, exception handling, and outputting results in PL/SQL.