Basics of PL/ SQL

Introduction to PL/SQL

PL/SQL (Procedural Language/SQL) is an extension of SQL (Structured Query Language) used in Oracle databases. It combines the data manipulation power of SQL with the procedural capabilities of a programming language. PL/SQL is designed to work seamlessly with SQL, making it a powerful tool for writing complex queries, managing transactions, and controlling the flow of a program.

Key Features of PL/SQL

1. Block Structure: PL/SQL code is organized into blocks, which are the basic units of code in PL/SQL. Each block can contain declarations, executable commands, and exception-handling code.
2. Control Structures: PL/SQL supports standard control structures found in other programming languages, such as loops (FOR, WHILE, LOOP), conditional statements (IF...THEN...ELSE), and case statements.
3. Error Handling: PL/SQL provides robust error-handling capabilities using EXCEPTION blocks, allowing developers to manage and respond to runtime errors gracefully.
4. Procedures and Functions: PL/SQL allows you to define reusable subprograms (procedures and functions) that can be called within your PL/SQL code or by other programs.
5. Triggers: Triggers are special types of stored procedures that are automatically executed (or “triggered”) in response to specific events on a particular table or view, such as INSERT, UPDATE, or DELETE operations.
6. Cursors: Cursors in PL/SQL allow you to fetch and manipulate data row by row. They are particularly useful when dealing with queries that return multiple rows.
7. Packages: A package is a collection of related procedures, functions, variables, and other PL/SQL constructs grouped together. Packages help organize code and promote reuse.

Block Structure of PL/SQL

The block structure of PL/SQL is a fundamental concept that organizes the code into manageable sections. Each PL/SQL block consists of four main sections: the Declaration, Execution, Exception Handling, and End sections. Here’s a detailed explanation of each part:

1. Declaration Section

Purpose: The declaration section is where you define variables, constants, cursors, and other objects that you intend to use in the PL/SQL block. These objects can hold data or control the flow of the program.

Syntax:

DECLARE
    -- Variable declarations
    variable_name datatype [NOT NULL] [:= initial_value];
    
    -- Constant declarations
    constant_name CONSTANT datatype := initial_value;
    
    -- Cursor declarations
    CURSOR cursor_name IS select_statement;
    
    -- User-defined exceptions
    exception_name EXCEPTION;

Example:

DECLARE
    v_customer_id   NUMBER := 1;
    v_balance       NUMBER;
    insufficient_balance EXCEPTION;

Explanation:

• v_customer_id and v_balance are variables that will hold the customer ID and the current balance.
• insufficient_balance is a user-defined exception that could be used to handle cases where a customer tries to withdraw more than their available balance.

2. Execution Section

Purpose: The execution section contains the executable statements that perform the main tasks of the PL/SQL block. This section is mandatory, and it is where the logic of your program is implemented.

Syntax:

BEGIN
    -- Executable statements
    executable_statement1;
    executable_statement2;
    ...

Example:

BEGIN
    -- Fetch the current balance of the customer
    SELECT Balance INTO v_balance
    FROM Customers
    WHERE CustomerID = v_customer_id;

    -- Check if the balance is sufficient for withdrawal
    IF v_balance < 2000 THEN
        RAISE insufficient_balance;
    ELSE
        -- Deduct the withdrawal amount
        UPDATE Customers
        SET Balance = Balance - 2000
        WHERE CustomerID = v_customer_id;
    END IF;
END;

Explanation:

• The SELECT statement retrieves the balance of the customer from the Customers table.
• The IF statement checks whether the balance is sufficient for a withdrawal. If not, it raises the insufficient_balance exception.
• The UPDATE statement deducts the withdrawal amount from the customer’s balance.

3. Exception Handling Section

Purpose: The exception-handling section is optional and is used to handle runtime errors or exceptions that occur during the execution of the PL/SQL block. This section ensures that the program can recover gracefully from unexpected errors.

Syntax:

EXCEPTION
    WHEN exception_name1 THEN
        -- Statements to handle the exception
        handling_statement1;
        handling_statement2;
    
    WHEN OTHERS THEN
        -- Statements to handle any other exceptions
        handling_statement3;

Example:

EXCEPTION
    WHEN insufficient_balance THEN
        DBMS_OUTPUT.PUT_LINE('Error: Insufficient balance for withdrawal.');
    
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: An unexpected error occurred.');
END;

Explanation:

• The WHEN insufficient_balance block handles the specific case where the customer does not have enough funds to make a withdrawal.
• The WHEN OTHERS block is a catch-all that handles any other exceptions that might occur.

4. End Section

Purpose: The END keyword marks the conclusion of the PL/SQL block. It is a mandatory part of every PL/SQL block, indicating that the block is complete.

Syntax:

END;

Example:

END;

Explanation:

• The END; keyword simply closes the PL/SQL block.

Features of PL/SQL

PL/SQL (Procedural Language/SQL) is an extension of SQL provided by Oracle that includes various features making it a powerful tool for database programming and management. Here are the key features of PL/SQL:

1. Block Structure

• Modular Programming: PL/SQL is structured into blocks, which are the basic units of a PL/SQL program. Each block can include declarations, executable commands, and exception handling, promoting modularity and ease of maintenance.

2. Control Structures

• Conditional Statements: PL/SQL supports IF...THEN...ELSE and CASE statements for making decisions based on conditions.
• Loops: PL/SQL provides FOR, WHILE, and LOOP constructs, allowing developers to repeat a sequence of statements multiple times.
• Exception Handling: PL/SQL allows you to manage errors and exceptions through EXCEPTION blocks, enabling robust error handling and recovery.

3. Procedures and Functions

• Reusable Code: PL/SQL allows the creation of procedures and functions, which are named blocks of code that can be reused across different programs. Procedures perform actions, while functions return a value.
• Parameter Passing: Procedures and functions can accept input (IN), output (OUT), and input/output (IN OUT) parameters, providing flexibility in how data is handled.

4. Packages

• Encapsulation: PL/SQL packages allow grouping related procedures, functions, variables, and other PL/SQL types into a single unit. This promotes better organization and encapsulation of code.
• Package Specification and Body: The specification declares the public elements, while the body contains the actual implementation, allowing for separation of interface and implementation.

5. Cursors

• Implicit and Explicit Cursors: Cursors in PL/SQL allow you to handle query result sets. Implicit cursors are automatically created for SELECT statements, while explicit cursors give more control over query processing.
• Cursor For Loops: PL/SQL supports FOR loops that iterate through each row returned by a cursor, simplifying row-by-row processing.

6. Triggers

• Automatic Execution: Triggers are special types of stored procedures that are automatically executed in response to specific events on a table or view, such as INSERT, UPDATE, or DELETE. Triggers are used for enforcing business rules, auditing changes, and more.

7. Exception Handling

• Built-in Exceptions: PL/SQL provides predefined exceptions for common error conditions, like NO_DATA_FOUND or TOO_MANY_ROWS.
• User-defined Exceptions: You can also define your own exceptions to handle specific error situations in your application.

8. Built-in Packages

• Standard Libraries: Oracle provides several built-in PL/SQL packages, such as DBMS_SQL, DBMS_OUTPUT, and UTL_FILE, that extend PL/SQL’s capabilities for tasks like file handling, dynamic SQL, and more.

9. Data Types

• Rich Set of Data Types: PL/SQL supports a wide range of data types, including scalar types like NUMBER, VARCHAR2, and DATE, as well as composite types like records and collections (e.g., arrays, nested tables).
• User-defined Data Types: PL/SQL allows the creation of custom data types, providing greater flexibility and control over the data structures used in your application.

10. Transaction Control

• COMMIT, ROLLBACK, SAVEPOINT: PL/SQL allows precise control over transactions, enabling you to commit changes, roll them back, or set savepoints to restore to specific points in the transaction.
• Autonomous Transactions: PL/SQL supports autonomous transactions, which are independent of the main transaction, allowing operations like logging or auditing without affecting the main transaction’s state.

11. Integration with SQL

• Seamless SQL Execution: PL/SQL integrates tightly with SQL, allowing SQL statements to be embedded directly within PL/SQL code. This integration simplifies data manipulation and querying within the procedural logic.

12. Portability

• Platform Independence: PL/SQL is highly portable and can be executed on any Oracle database, regardless of the underlying platform. This makes PL/SQL applications scalable and easy to deploy across different environments.

13. Dynamic SQL

• Dynamic Query Execution: PL/SQL supports dynamic SQL through the EXECUTE IMMEDIATE statement and the DBMS_SQL package, allowing the execution of SQL statements that are constructed at runtime. This is useful for applications that require flexible query execution.

14. Security

• Role-based Access Control: PL/SQL supports Oracle’s security model, including role-based access control and fine-grained access controls, ensuring that sensitive data and operations are protected.

15. Interoperability

• Calling External Code: PL/SQL allows calling external procedures written in other programming languages, such as Java or C, through external libraries, making it highly interoperable.

These features make PL/SQL a robust, flexible, and powerful tool for database programming, enabling developers to build complex, high-performance, and secure database applications.

Data types of PL/SQL

In PL/SQL, data types are crucial because they define the kind of data that can be stored and manipulated in variables, constants, and columns. PL/SQL supports a wide range of data types, which can be broadly categorized into the following groups:

1. Scalar Data Types

These represent a single value and are divided into several subtypes:

• Numeric Types:
  • NUMBER: A versatile type that can store integers, fixed-point, and floating-point numbers. It has precision and scale.
  • BINARY_INTEGER / PLS_INTEGER: Used for integers. PLS_INTEGER is generally faster as it operates natively.
  • FLOAT: A floating-point number.
  • BINARY_FLOAT / BINARY_DOUBLE: Fast, floating-point number types optimized for performance.
• Character Types:
  • CHAR: Fixed-length character string.
  • VARCHAR2: Variable-length character string, up to 32,767 bytes.
  • NCHAR: Fixed-length national character set string.
  • NVARCHAR2: Variable-length national character set string.
• Boolean Type:
  • BOOLEAN: Stores logical values: TRUE, FALSE, or NULL.
• Date/Time Types:
  • DATE: Stores date and time to the nearest second.
  • TIMESTAMP: More precise than DATE, includes fractional seconds.
  • INTERVAL YEAR TO MONTH: Represents a difference in years and months.
  • INTERVAL DAY TO SECOND: Represents a difference in days, hours, minutes, and seconds.
• RAW and LONG RAW Types:
  • RAW: Stores binary data up to 2,000 bytes.
  • LONG RAW: Stores binary data up to 2 GB, but is deprecated.
  • Example:

DECLARE
  salary NUMBER(10, 2);  -- 10 digits in total, with 2 decimal places
BEGIN
  salary := 50000.75;
  DBMS_OUTPUT.PUT_LINE('Salary: ' || salary);
END;

Here, salary is declared as a NUMBER with a precision of 10 digits, 2 of which are after the decimal point.

2. Composite Data Types

These data types are collections of related values, allowing you to group multiple fields.

• Records: A composite data type that can hold multiple values of different data types, similar to a struct in C.
• Collections:
  • Associative Arrays (Index-by Tables): A collection of elements indexed by a key.
  • Nested Tables: Similar to associative arrays but can be stored in the database.
  • VARRAYs (Variable-Size Arrays): Fixed-size arrays where the number of elements is specified.

3. Reference Data Types

These data types store references (or pointers) to other data items.

• REF CURSOR: A cursor variable that allows you to dynamically associate it with different query results.

4. LOB (Large Object) Data Types

These are used to store large amounts of data, such as text, images, videos, etc.

• BLOB: Binary Large Object for storing binary data.
• CLOB: Character Large Object for storing large text data.
• NCLOB: National Character Large Object, like CLOB, but supports a different character set.
• BFILE: Stores a pointer to a file stored outside the database, on the server’s file system.

5. User-Defined Data Types

PL/SQL also allows for the creation of user-defined data types using the following:

• SUBTYPE: Defines a subset of an existing data type.
• OBJECT TYPE: Allows for the creation of object-oriented data structures, similar to classes in OOP.

Each of these data types serves a specific purpose and helps in writing efficient PL/SQL programs. Proper use of data types is crucial for the performance and reliability of PL/SQL applications.

Declaration and Naming Convention of Variables

In PL/SQL, declaration and naming conventions of variables are fundamental for writing clear and maintainable code. Here’s a detailed guide on how to declare variables and follow naming conventions:

Declaration of Variables

Purpose: Variables in PL/SQL are used to store data that can be manipulated and used within the PL/SQL block. The declaration section is where you define these variables.

Syntax:

sqlCopy codeDECLARE
    variable_name datatype [NOT NULL] [:= initial_value];

Key Points:

1. Variable Name: The name used to reference the variable.
2. Datatype: Specifies the type of data the variable will hold (e.g., NUMBER, VARCHAR2, DATE).
3. NOT NULL: Optional constraint that ensures the variable cannot hold a NULL value.
4. Initial Value: Optional value assigned to the variable at the time of declaration.

Examples:

1.Basic Variable Declaration:

DECLARE
    v_amount NUMBER;

Here, v_amount is a variable of type NUMBER.

2.Variable with Initial Value:

DECLARE
    v_discount NUMBER := 10; -- Initializes v_discount with a value of 10

Here, v_discount is initialized with a value of 10.

3.Variable with NOT NULL Constraint:

DECLARE
v_employee_name VARCHAR2(50) NOT NULL;

v_employee_name cannot hold a NULL value.

Naming Conventions for Variables

Using consistent and meaningful naming conventions enhances code readability and maintainability. Here are some commonly followed conventions:

1. Use Descriptive Names: Choose names that clearly indicate the purpose of the variable.
   • Good: v_salary, v_employee_id, v_total_amount
   • Bad: v1, temp, data
2. Prefix with Scope: Use prefixes to indicate the variable’s type or scope.
   • v_: For variables (v_amount, v_total)
   • p_: For parameters in procedures or functions (p_salary, p_employee_id)
3. Camel Case or Underscore: Use camel case or underscores to improve readability.
   • Camel Case: vTotalAmount, vEmployeeId
   • Underscore: v_total_amount, v_employee_id
4. Avoid Reserved Words: Do not use PL/SQL reserved words or keywords as variable names.
   • Reserved Words: BEGIN, END, DECLARE, IF
5. Be Consistent: Follow a consistent naming scheme throughout your codebase.
   • Consistent: Use either v_variable_name or vVariableName consistently, not both.
6. Include Units in Names: If the variable represents a measurement, include the unit in the name.
   • Example: v_salary_in_usd, v_price_per_unit

Example of Variable Declaration and Naming Convention

Here’s a full PL/SQL block incorporating these practices:

DECLARE
    -- Variables with descriptive names and initial values
    v_employee_id   NUMBER := 12345;
    v_employee_name VARCHAR2(100) := 'John Doe';
    v_salary        NUMBER := 5000;
    v_bonus         NUMBER := 500;
    v_total_salary  NUMBER;

BEGIN
    -- Calculating total salary
    v_total_salary := v_salary + v_bonus;
    
    -- Displaying results
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
    DBMS_OUTPUT.PUT_LINE('Total Salary: ' || v_total_salary);

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred.');
END;

Explanation:

• v_employee_id, v_employee_name, v_salary, and v_total_salary are named descriptively to reflect their roles.
• The initial values for variables are provided where applicable.
• Consistent Naming: Uses the prefix v_ for variables and follows a clear naming convention.

By following these conventions and practices, you can write PL/SQL code that is easier to understand, debug, and maintain.

PL/SQL Tables and User Defined Records

PL/SQL tables, also known as associative arrays, are one of the collection types in PL/SQL (Procedural Language/Structured Query Language). They provide a way to store and manipulate a collection of elements, where each element is associated with a unique key, similar to a hash table or dictionary in other programming languages.

Key Features of PL/SQL Tables:

1. Associative Arrays:
   • PL/SQL tables are associative arrays, meaning they are collections of key-value pairs.
   • The keys can be either integers or strings (VARCHAR2).
   • The values can be of any scalar or record data type.
2. Sparse Collections:
   • Unlike arrays in other programming languages, PL/SQL tables are sparse. This means that you can have non-sequential indexes, leaving gaps between elements.
3. Unbounded Size:
   • PL/SQL tables can grow dynamically as you add new elements. They do not have a fixed size.
4. Index by Clause:
   • You define PL/SQL tables using the INDEX BY clause, which specifies the data type of the key.

Syntax of PL/SQL Tables

DECLARE
   TYPE table_type IS TABLE OF datatype INDEX BY index_type;
   table_name table_type;
BEGIN
   -- Manipulate the PL/SQL table
END;

• table_type: The name of the PL/SQL table type.
• datatype: The data type of the elements in the table.
• index_type: The data type of the index. It can be BINARY_INTEGER (for integer keys) or VARCHAR2(size) (for string keys).
• table_name: The name of the PL/SQL table variable.

Example

Let’s consider an example where we store employee names using their employee IDs as keys.

DECLARE
   TYPE employees_table IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
   employees employees_table;
BEGIN
   -- Inserting values into the PL/SQL table
   employees(101) := 'Bhanu';
   employees(102) := 'Akhil';
   employees(103) := 'Yaswanth';
   
   -- Accessing values
   DBMS_OUTPUT.PUT_LINE('Employee 101: ' || employees(101));
   DBMS_OUTPUT.PUT_LINE('Employee 102: ' || employees(102));
   DBMS_OUTPUT.PUT_LINE('Employee 103: ' || employees(103));
   
   -- Deleting an entry
   employees.DELETE(102);
   
   -- Checking if a key exists
   IF employees.EXISTS(102) THEN
       DBMS_OUTPUT.PUT_LINE('Employee 102 exists.');
   ELSE
       DBMS_OUTPUT.PUT_LINE('Employee 102 does not exist.');
   END IF;
END;

Key Operations on PL/SQL Tables:

1. Inserting Elements: You can insert elements by simply assigning a value to a specific key.
2. Accessing Elements: Access elements using the key.
3. Deleting Elements: Use the DELETE method to remove elements by key.
4. Checking Existence: Use the EXISTS method to check if a specific key exists in the table.

PL/SQL Table Methods:

• EXISTS(n): Checks if the key n exists in the table.
• COUNT: Returns the number of elements in the table.
• FIRST: Returns the first key in the table.
• LAST: Returns the last key in the table.
• PRIOR(n): Returns the key prior to n.
• NEXT(n): Returns the key after n.
• DELETE: Removes elements from the table.

Usage in Procedures and Functions:

PL/SQL tables are especially useful for passing collections to procedures and functions, allowing bulk processing of data. They can also be used for caching data within a PL/SQL block.

Example in a Procedure

CREATE OR REPLACE PROCEDURE process_employees(emp_table IN employees_table) IS
BEGIN
   FOR indx IN emp_table.FIRST .. emp_table.LAST LOOP
      IF emp_table.EXISTS(indx) THEN
         DBMS_OUTPUT.PUT_LINE('Processing Employee: ' || emp_table(indx));
      END IF;
   END LOOP;
END;

In this procedure, process_employees, the emp_table is passed as an input parameter, and the procedure processes each employee by iterating over the PL/SQL table.

PL/SQL tables provide a powerful and flexible way to work with collections of data in PL/SQL, especially when dealing with scenarios requiring quick lookups and dynamic data manipulation.

INPUT/OUTPUT Statements

PL/SQL: Input/Output Statements

PL/SQL, Oracle’s procedural extension for SQL, includes specific input/output statements that allow for user interaction within the database environment. The most common input/output statements in PL/SQL are:

DBMS_OUTPUT.PUT_LINE

• This is a PL/SQL built-in package that allows you to print output to the console. It’s commonly used for debugging purposes or to display information to the user.

Syntax:

DBMS_OUTPUT.PUT_LINE('Your message here');

Example:

BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, Bhanu!');
END;

This will print “Hello, Bhanu!” to the console when executed in a PL/SQL block.

Decision-making statements in PL/SQL are used to control the flow of execution based on conditions. These statements allow the program to execute different blocks of code depending on whether a certain condition is true or false. The primary decision-making statements in PL/SQL are:

1. IF-THEN
2. IF-THEN-ELSE
3. IF-THEN-ELSIF-ELSE
4. CASE

1. IF-THEN Statement

The IF-THEN statement is the simplest form of decision-making. It executes a block of code only if a specified condition is true.

Syntax:

IF condition THEN
    -- code to be executed if condition is true
END IF;

Example:

DECLARE
    num NUMBER := 10;
BEGIN
    IF num > 5 THEN
        DBMS_OUTPUT.PUT_LINE('The number is greater than 5');
    END IF;
END;
/

• Explanation:
  • The condition num > 5 is checked. If num is greater than 5 (which it is, since num is 10), the message “The number is greater than 5” is printed.

2. IF-THEN-ELSE Statement

The IF-THEN-ELSE statement adds an alternative block of code to be executed if the condition is false.

Syntax:

IF condition THEN
    -- code to be executed if condition is true
ELSE
    -- code to be executed if condition is false
END IF;

Example:

DECLARE
    num NUMBER := 3;
BEGIN
    IF num > 5 THEN
        DBMS_OUTPUT.PUT_LINE('The number is greater than 5');
    ELSE
        DBMS_OUTPUT.PUT_LINE('The number is not greater than 5');
    END IF;
END;
/

• Explanation:
  • If num is greater than 5, the first message is printed. Otherwise, the second message “The number is not greater than 5” is printed. Since num is 3, the second message is displayed.

3. IF-THEN-ELSIF-ELSE Statement

The IF-THEN-ELSIF-ELSE statement allows for multiple conditions to be checked in sequence. Once a true condition is found, its corresponding block is executed, and the rest are ignored.

Syntax:

IF condition1 THEN
    -- code to be executed if condition1 is true
ELSIF condition2 THEN
    -- code to be executed if condition2 is true
ELSE
    -- code to be executed if all conditions are false
END IF;

Example:

DECLARE
    num NUMBER := 7;
BEGIN
    IF num > 10 THEN
        DBMS_OUTPUT.PUT_LINE('The number is greater than 10');
    ELSIF num > 5 THEN
        DBMS_OUTPUT.PUT_LINE('The number is greater than 5 but less than or equal to 10');
    ELSE
        DBMS_OUTPUT.PUT_LINE('The number is 5 or less');
    END IF;
END;
/

• Explanation:
  • The first condition checks if num is greater than 10. If false, it checks if num is greater than 5. If true, it prints the second message. If neither condition is true, the last block is executed.
  • Since num is 7, the message “The number is greater than 5 but less than or equal to 10” is printed.

4. CASE Statement

The CASE statement is a more structured and readable way of handling multiple conditions. It can be used as a direct replacement for IF-THEN-ELSIF in many cases.

Syntax (Simple CASE):

CASE expression
    WHEN value1 THEN
        -- code to be executed if expression = value1
    WHEN value2 THEN
        -- code to be executed if expression = value2
    ELSE
        -- code to be executed if no match is found
END CASE;

Example (Simple CASE):

DECLARE
    grade CHAR(1) := 'B';
BEGIN
    CASE grade
        WHEN 'A' THEN
            DBMS_OUTPUT.PUT_LINE('Excellent');
        WHEN 'B' THEN
            DBMS_OUTPUT.PUT_LINE('Good');
        WHEN 'C' THEN
            DBMS_OUTPUT.PUT_LINE('Average');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Invalid Grade');
    END CASE;
END;
/

• Explanation:
  • The CASE statement checks the value of grade and prints the corresponding message. Since grade is ‘B’, the message “Good” is printed.

Syntax (Searched CASE):

CASE
    WHEN condition1 THEN
        -- code to be executed if condition1 is true
    WHEN condition2 THEN
        -- code to be executed if condition2 is true
    ELSE
        -- code to be executed if no condition is true
END CASE;

Example (Searched CASE):

DECLARE
    score NUMBER := 85;
BEGIN
    CASE
        WHEN score >= 90 THEN
            DBMS_OUTPUT.PUT_LINE('Grade A');
        WHEN score >= 80 THEN
            DBMS_OUTPUT.PUT_LINE('Grade B');
        WHEN score >= 70 THEN
            DBMS_OUTPUT.PUT_LINE('Grade C');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Grade D');
    END CASE;
END;
/

• Explanation:
  • The CASE statement evaluates conditions sequentially. Since score is 85, the message “Grade B” is printed.

Summary

• IF-THEN: Executes a block if the condition is true.
• IF-THEN-ELSE: Provides an alternative block if the condition is false.
• IF-THEN-ELSIF-ELSE: Evaluates multiple conditions in sequence.
• CASE: A structured alternative to multiple IF-THEN-ELSIF conditions, useful for both simple and complex scenarios.

These decision-making structures are crucial for controlling the flow of your PL/SQL programs based on different conditions.

6.GOTO statement

The GOTO statement in PL/SQL is used for transferring control to a labeled statement within the same block. This can be useful for controlling the flow of execution in certain scenarios, but it should be used sparingly because it can make code less readable and harder to maintain.

Syntax

GOTO label;

• label: A user-defined identifier that represents a point in the code to which control will be transferred.

Example

Here’s a basic example to demonstrate how the GOTO statement works in PL/SQL:

DECLARE
  v_counter INTEGER := 1;
BEGIN
  <<start_loop>>
  DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
  
  IF v_counter < 5 THEN
    v_counter := v_counter + 1;
    GOTO start_loop;
  ELSE
    DBMS_OUTPUT.PUT_LINE('Loop ended.');
  END IF;
END;
/

Explanation

1. Label Definition: <<start_loop>> is a label defined in the code. Labels are placed before a block of code to which control can be transferred.
2. GOTO Statement: The GOTO start_loop; statement tells PL/SQL to jump back to the line marked with the start_loop label.
3. Loop Execution: The code repeatedly prints the value of v_counter and increments it. When v_counter reaches 5, it exits the loop and prints “Loop ended.”

Considerations

• Readability: Excessive use of GOTO can lead to code that is difficult to understand and maintain. It can create “spaghetti code” where the flow of execution is hard to follow.
• Alternatives: In most cases, using structured control flow constructs like LOOP, EXIT, CONTINUE, and conditional statements (IF...THEN...ELSE) is preferable to using GOTO.

Looping Statements

In PL/SQL, there are several types of looping statements that you can use to execute a block of code multiple times. Each type of loop has its own characteristics and use cases. Here’s a detailed overview of the different looping statements available in PL/SQL:

1. Basic Loop

The LOOP statement allows you to execute a block of code repeatedly until you explicitly exit the loop.

Syntax:

LOOP
  -- Statements to be executed
  EXIT WHEN condition;
END LOOP;

Example:

DECLARE
  v_counter INTEGER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
    v_counter := v_counter + 1;
    EXIT WHEN v_counter > 5;
  END LOOP;
END;
/

Explanation:

1. Initialization: v_counter is initialized to 1.
2. LOOP Block: Inside the loop:
   • DBMS_OUTPUT.PUT_LINE prints the current value of v_counter.
   • v_counter is incremented by 1.
   • The EXIT WHEN statement checks if v_counter is greater than 5. If true, the loop exits.
3. Termination: The loop stops executing when v_counter becomes 6.

2. WHILE Loop

The WHILE loop continues executing as long as the specified condition is true.

Syntax:

WHILE condition LOOP
  -- Statements to be executed
END LOOP;

Example:

DECLARE
  v_counter INTEGER := 1;
BEGIN
  WHILE v_counter <= 5 LOOP
    DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
    v_counter := v_counter + 1;
  END LOOP;
END;
/

Explanation:

1. Initialization: v_counter is initialized to 1.
2. Condition Check: The loop checks if v_counter is less than or equal to 5.
3. LOOP Block: Inside the loop:
   • DBMS_OUTPUT.PUT_LINE prints the current value of v_counter.
   • v_counter is incremented by 1.
4. Termination: The loop stops executing when v_counter becomes 6, as the condition (v_counter <= 5) is no longer true.

3. FOR Loop

The FOR loop is used when you want to iterate over a range of values or process each record returned by a cursor.

Numeric FOR Loop

Syntax:

FOR index IN start_value..end_value LOOP
  -- Statements to be executed
END LOOP;

Example:

BEGIN
  FOR v_counter IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
  END LOOP;
END;
/

Explanation:

1. Iteration: The loop iterates from 1 to 5.
2. LOOP Block: Inside the loop:
   • DBMS_OUTPUT.PUT_LINE prints the current value of v_counter.
3. Termination: The loop automatically terminates after reaching 5, as specified by the range.

Cursor FOR Loop

Syntax:

FOR record IN (SELECT column1, column2 FROM table_name) LOOP
  -- Statements to be executed
END LOOP;

Example:

BEGIN
  FOR rec IN (SELECT first_name, last_name FROM employees) LOOP
    DBMS_OUTPUT.PUT_LINE('Name: ' || rec.first_name || ' ' || rec.last_name);
  END LOOP;
END;
/

Explanation:

1. Cursor: The cursor fetches records from the employees table, selecting first_name and last_name.
2. Iteration: The loop iterates over each record returned by the query.
3. LOOP Block: Inside the loop:
   • DBMS_OUTPUT.PUT_LINE prints the concatenated first_name and last_name for each record.
4. Termination: The loop terminates after processing all records returned by the query.

Conclusion

PL/SQL provides several looping constructs to handle repetitive tasks:

• Basic Loop: Useful for simple, unconditional repetition where you manage the termination condition.
• WHILE Loop: Executes as long as the condition is true, ideal for situations where the number of iterations is not known beforehand.
• FOR Loop: Ideal for a known number of iterations or when processing cursor results.

Using these looping constructs effectively helps you write efficient and maintainable PL/SQL code.

Procedure

In PL/SQL, a procedure is a named PL/SQL block that performs a specific task. Procedures are a type of stored program unit that can be executed by calling them from other PL/SQL blocks, procedures, functions, or even directly from SQL commands. Procedures allow you to encapsulate a sequence of statements to be executed together, which helps in code reuse, modularity, and maintainability.

Syntax

CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter1 [mode1 datatype1], parameter2 [mode2 datatype2], ... ) ]
IS
  -- Declarations (optional)
BEGIN
  -- Executable statements
EXCEPTION
  -- Exception handling (optional)
END [procedure_name];

• CREATE [OR REPLACE] PROCEDURE: Creates a new procedure or replaces an existing one with the same name.
• procedure_name: The name of the procedure.
• (parameter1 [mode1 datatype1], parameter2 [mode2 datatype2], ... ): Optional list of parameters, where mode can be IN, OUT, or IN OUT, and datatype specifies the data type of the parameter.
• IS or AS: Keyword to start the procedure body.
• Declarations: (Optional) Variable and cursor declarations.
• BEGIN … END: The main executable part of the procedure.
• EXCEPTION: (Optional) Exception handling block.
• END [procedure_name];: Marks the end of the procedure.

Examples

1. Simple Procedure

Here’s an example of a simple procedure that prints a message:

CREATE OR REPLACE PROCEDURE print_message
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, PL/SQL!');
END print_message;
/

Explanation:

• CREATE OR REPLACE PROCEDURE print_message: Defines the procedure with the name print_message.
• BEGIN ... END: The body of the procedure where DBMS_OUTPUT.PUT_LINE prints the message “Hello, PL/SQL!”.

2. Procedure with Parameters

This example shows a procedure that takes an IN parameter and prints a greeting message:

CREATE OR REPLACE PROCEDURE greet_user (p_name IN VARCHAR2)
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END greet_user;
/

Explanation:

• greet_user (p_name IN VARCHAR2): The procedure greet_user has one IN parameter p_name of type VARCHAR2.
• DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!'): Concatenates “Hello, ” with the value of p_name and prints it.

3. Procedure with OUT Parameter

This example demonstrates a procedure that returns a value via an OUT parameter:

CREATE OR REPLACE PROCEDURE get_employee_name (p_employee_id IN NUMBER, p_employee_name OUT VARCHAR2)
IS
BEGIN
  SELECT first_name || ' ' || last_name
  INTO p_employee_name
  FROM employees
  WHERE employee_id = p_employee_id;
END get_employee_name;
/

Explanation:

• get_employee_name (p_employee_id IN NUMBER, p_employee_name OUT VARCHAR2): The procedure takes an IN parameter p_employee_id and an OUT parameter p_employee_name.
• SELECT ... INTO: Fetches the employee’s name from the employees table and stores it in p_employee_name.

Usage Example:

DECLARE
  v_name VARCHAR2(100);
BEGIN
  get_employee_name(1001, v_name);
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
END;
/

Explanation:

• DECLARE: Declares a variable v_name to store the employee’s name.
• get_employee_name(1001, v_name): Calls the procedure with employee ID 1001 and stores the result in v_name.
• DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name): Prints the employee’s name.

Key Points

• Parameters: Procedures can have parameters to pass data in and out. The modes for parameters are IN, OUT, and IN OUT:
  • IN: Passes data into the procedure (default mode).
  • OUT: Returns data from the procedure.
  • IN OUT: Passes data into and out of the procedure.
• Encapsulation: Procedures help encapsulate logic, making code modular and reusable.
• Exception Handling: Procedures can include exception handling to manage errors and exceptions that occur during execution.
• Execution: Procedures are executed by calling their name from PL/SQL blocks or other SQL contexts. They cannot be executed directly from SQL*Plus or SQL Developer without wrapping them in an anonymous block.

Using procedures effectively can help in organizing and simplifying your PL/SQL code, improving maintainability, and enabling code reuse.

Function

In PL/SQL, a function is a named PL/SQL block that performs a specific task and returns a single value. Functions are similar to procedures but are designed to return a result. They can be used in SQL statements, PL/SQL blocks, and can also be called from other procedures or functions.

Syntax

CREATE [OR REPLACE] FUNCTION function_name
( [parameter1 [mode1 datatype1], parameter2 [mode2 datatype2], ... ] )
RETURN return_datatype
IS
  -- Declarations (optional)
BEGIN
  -- Executable statements
  RETURN return_value;
EXCEPTION
  -- Exception handling (optional)
END [function_name];

• CREATE [OR REPLACE] FUNCTION: Creates a new function or replaces an existing one with the same name.
• function_name: The name of the function.
• (parameter1 [mode1 datatype1], parameter2 [mode2 datatype2], ... ): Optional list of parameters, where mode can be IN, OUT, or IN OUT, and datatype specifies the data type of the parameter.
• RETURN return_datatype: Specifies the data type of the value that the function will return.
• IS or AS: Keyword to start the function body.
• Declarations: (Optional) Variable and cursor declarations.
• BEGIN … END: The main executable part of the function.
• RETURN return_value;: Returns a value of the specified return data type.
• EXCEPTION: (Optional) Exception handling block.
• END [function_name];: Marks the end of the function.

Examples

1. Simple Function

Here’s an example of a function that returns a constant value:

CREATE OR REPLACE FUNCTION get_pi
RETURN NUMBER
IS
BEGIN
  RETURN 3.14159;
END get_pi;
/

Explanation:

• CREATE OR REPLACE FUNCTION get_pi: Defines a function named get_pi.
• RETURN NUMBER: Specifies that the function returns a NUMBER data type.
• RETURN 3.14159;: The function returns the value of pi.

Usage Example:

DECLARE
  v_pi NUMBER;
BEGIN
  v_pi := get_pi;
  DBMS_OUTPUT.PUT_LINE('The value of Pi is: ' || v_pi);
END;
/

Explanation:

• v_pi := get_pi;: Calls the function get_pi and stores the returned value in v_pi.
• DBMS_OUTPUT.PUT_LINE('The value of Pi is: ' || v_pi);: Prints the value of Pi.

2. Function with Parameters

Here’s an example of a function that calculates the square of a number:

CREATE OR REPLACE FUNCTION square_number (p_number IN NUMBER)
RETURN NUMBER
IS
BEGIN
  RETURN p_number * p_number;
END square_number;
/

Explanation:

• square_number (p_number IN NUMBER): The function takes one IN parameter, p_number, of type NUMBER.
• RETURN NUMBER: Specifies that the function returns a NUMBER.
• RETURN p_number * p_number;: Returns the square of the input number.

Usage Example:

DECLARE
  v_result NUMBER;
BEGIN
  v_result := square_number(5);
  DBMS_OUTPUT.PUT_LINE('The square of 5 is: ' || v_result);
END;
/

Explanation:

• v_result := square_number(5);: Calls the function square_number with the argument 5 and stores the result in v_result.
• DBMS_OUTPUT.PUT_LINE('The square of 5 is: ' || v_result);: Prints the square of the number.

3. Function with Exception Handling

Here’s an example of a function that handles an exception:

CREATE OR REPLACE FUNCTION divide_numbers (p_num1 IN NUMBER, p_num2 IN NUMBER)
RETURN NUMBER
IS
  v_result NUMBER;
BEGIN
  IF p_num2 = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Division by zero is not allowed.');
  ELSE
    v_result := p_num1 / p_num2;
  END IF;
  RETURN v_result;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END divide_numbers;
/

Explanation:

• divide_numbers (p_num1 IN NUMBER, p_num2 IN NUMBER): The function takes two IN parameters, p_num1 and p_num2, of type NUMBER.
• RETURN NUMBER: Specifies that the function returns a NUMBER.
• IF p_num2 = 0 THEN ...: Checks if the divisor is zero and raises an exception if true.
• RETURN v_result;: Returns the result of the division.
• EXCEPTION ...: Catches exceptions and returns NULL if an error occurs.

Usage Example:

DECLARE
  v_result NUMBER;
BEGIN
  v_result := divide_numbers(10, 2);
  DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
  
  v_result := divide_numbers(10, 0);
  DBMS_OUTPUT.PUT_LINE('Result with zero divisor: ' || v_result);
END;
/

Explanation:

• v_result := divide_numbers(10, 2);: Calls the function with valid arguments and prints the result.
• v_result := divide_numbers(10, 0);: Calls the function with a zero divisor, which triggers the exception handling and prints NULL.

Key Points

• Return Type: Functions must return a single value of a specified data type.
• Parameters: Functions can have parameters to accept input values. Parameters can be IN, OUT, or IN OUT.
• Exception Handling: Functions can include exception handling to manage errors during execution.
• Usage: Functions can be used in SQL statements, PL/SQL blocks, and from other procedures or functions.

Functions are essential for modular programming in PL/SQL, allowing you to encapsulate logic, perform calculations, and return results in a structured manner.

Advantages of Sub programs

Subprograms in PL/SQL, including procedures and functions, offer several advantages that enhance the efficiency, maintainability, and reusability of your code. Here are some key benefits:

1. Modularity

Subprograms allow you to break down complex tasks into smaller, manageable units. This modular approach makes it easier to understand, test, and maintain code.

• Procedures: Can encapsulate a sequence of operations or business logic.
• Functions: Can encapsulate computations or queries that return a value.

2. Reusability

Once a subprogram is created, it can be reused multiple times across different parts of the application or even different applications. This reduces code duplication and promotes consistency.

• Reusable Logic: Common tasks like data validation or complex calculations can be encapsulated in functions or procedures and reused wherever needed.

3. Maintainability

Subprograms simplify code maintenance by localizing changes. When you need to update a particular piece of functionality, you can modify the subprogram without affecting other parts of the application.

• Single Point of Change: If the logic for calculating a value changes, you only need to update the function rather than updating every instance where the logic is used.

4. Encapsulation

Subprograms allow you to encapsulate complex logic and hide implementation details from the rest of the application. This encapsulation promotes abstraction and improves code clarity.

• Encapsulation of Business Logic: Procedures can manage complex business rules, and functions can encapsulate complex calculations or data retrieval operations.

5. Improved Performance

Using subprograms can improve performance through:

• Code Optimization: PL/SQL subprograms can be optimized by the database engine for better performance.
• Reduced Network Traffic: When procedures are executed on the server side, they reduce the need for multiple round-trips between the application and the database.

6. Error Handling

Subprograms can include exception handling to manage errors and exceptions more effectively. This allows for robust error management within the subprograms and can help in maintaining overall application stability.

• Centralized Error Handling: Procedures and functions can handle errors gracefully and provide meaningful error messages or recovery actions.

7. Parameterization

Subprograms support parameters, allowing you to pass values into and out of the subprogram. This flexibility makes it possible to create more dynamic and adaptable code.

• Customizable Execution: Procedures can be customized with different parameter values, and functions can return values based on input parameters.

8. Security

By using subprograms, you can control access to sensitive operations and data. Procedures can be granted specific permissions, and the encapsulated logic can restrict direct access to underlying tables.

• Controlled Access: You can create procedures that perform operations on tables and grant users access to the procedures without directly exposing the tables.

9. Documentation and Standardization

Subprograms provide a structured way to document and standardize common tasks and processes within an application.

• Code Documentation: Each subprogram can include comments and documentation that describe its purpose and usage, making the codebase easier to understand.

Handling procedures with Example Programs

Handling procedures in PL/SQL involves creating, executing, and managing procedures. Procedures are a type of stored subprogram that perform a specific task and do not return a value. They can be used to encapsulate business logic, perform operations, and improve modularity in your code.

Creating a Procedure

To create a procedure in PL/SQL, use the CREATE PROCEDURE statement. Here’s a basic example of a procedure that inserts a new employee into the employees table.

Example: Inserting an Employee

CREATE OR REPLACE PROCEDURE add_employee (
  p_employee_id IN NUMBER,
  p_first_name IN VARCHAR2,
  p_last_name IN VARCHAR2,
  p_salary IN NUMBER
)
IS
BEGIN
  INSERT INTO employees (employee_id, first_name, last_name, salary)
  VALUES (p_employee_id, p_first_name, p_last_name, p_salary);
  
  COMMIT;  -- Commit the transaction to make changes permanent
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE('Employee ID already exists.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END add_employee;
/

Explanation:

• Parameters: p_employee_id, p_first_name, p_last_name, and p_salary are input parameters.
• INSERT Statement: Adds a new employee record to the employees table.
• COMMIT: Makes the changes permanent.
• Exception Handling: Handles specific exceptions, such as duplicate values, and other general exceptions.

Executing a Procedure

You can execute a procedure from an anonymous PL/SQL block or from other PL/SQL code. Here’s an example of calling the add_employee procedure:

Example: Executing the Procedure

BEGIN
  add_employee(1001, 'John', 'Doe', 5000);
END;
/

Explanation:

• BEGIN ... END: Encapsulates the procedure call in an anonymous PL/SQL block.
• add_employee(1001, 'John', 'Doe', 5000);: Calls the add_employee procedure with specific values.

Handling Functions with Example Programs

Handling functions in PL/SQL involves creating, executing, and managing functions. Functions are similar to procedures but are designed to return a single value. They can be used in SQL statements, PL/SQL blocks, and from other procedures or functions. Functions provide a way to encapsulate reusable logic and computations.

Creating a Function

To create a function in PL/SQL, use the CREATE FUNCTION statement. Here’s an example of a simple function that calculates the area of a circle.

Example: Calculating the Area of a Circle

CREATE OR REPLACE FUNCTION calculate_area (p_radius IN NUMBER)
RETURN NUMBER
IS
  v_area NUMBER;
BEGIN
  v_area := 3.14159 * p_radius * p_radius;
  RETURN v_area;
END calculate_area;
/

Explanation:

• CREATE OR REPLACE FUNCTION calculate_area: Defines a function named calculate_area.
• p_radius IN NUMBER: The function takes one IN parameter, p_radius, which is the radius of the circle.
• RETURN NUMBER: Specifies that the function returns a value of type NUMBER.
• v_area := 3.14159 * p_radius * p_radius;: Calculates the area of the circle.
• RETURN v_area;: Returns the calculated area.

Executing a Function

Functions can be executed within SQL statements, PL/SQL blocks, or other functions. Here’s how to use the calculate_area function in a PL/SQL block.

Example: Using the Function in a PL/SQL Block

DECLARE
  v_radius NUMBER := 5;
  v_area NUMBER;
BEGIN
  v_area := calculate_area(v_radius);
  DBMS_OUTPUT.PUT_LINE('The area of the circle with radius ' || v_radius || ' is: ' || v_area);
END;
/

Explanation:

• DECLARE: Declares variables v_radius and v_area.
• v_area := calculate_area(v_radius);: Calls the calculate_area function with v_radius as the argument and stores the result in v_area.
• DBMS_OUTPUT.PUT_LINE: Prints the calculated area.

PL/SQL Recursive Functions:

In PL/SQL, a recursive function is a function that calls itself either directly or indirectly to solve a problem. Recursive functions are particularly useful for tasks that can be broken down into smaller, similar tasks, such as computing factorials, traversing hierarchical data, or solving problems involving sequences.

Basic Concept of Recursion

Recursion involves two key components:

1. Base Case: The condition under which the recursion stops. It prevents infinite recursion and allows the function to return a result without making further recursive calls.
2. Recursive Case: The condition under which the function calls itself, breaking the problem into smaller sub-problems.

Example 1: Calculating Factorial

The factorial of a non-negative integer nn (denoted as n!n!) is the product of all positive integers less than or equal to nn. The factorial function can be defined recursively as follows:

• n!=n×(n−1)!n!=n×(n−1)! for n>0n>0
• 0!=10!=1 (Base case)

PL/SQL Recursive Function Example:

CREATE OR REPLACE FUNCTION factorial (p_number IN NUMBER)
RETURN NUMBER
IS
  v_result NUMBER;
BEGIN
  -- Base case
  IF p_number = 0 THEN
    RETURN 1;
  ELSE
    -- Recursive case
    v_result := p_number * factorial(p_number - 1);
    RETURN v_result;
  END IF;
END factorial;
/

Explanation:

• Base Case: If p_number is 0, return 1.
• Recursive Case: Multiply p_number by the factorial of p_number - 1.

Usage Example:

DECLARE
  v_result NUMBER;
BEGIN
  v_result := factorial(5);
  DBMS_OUTPUT.PUT_LINE('Factorial of 5 is: ' || v_result);
END;
/

Explanation:

• v_result := factorial(5);: Calls the recursive factorial function with 5 and stores the result in v_result.

Example 2: Fibonacci Sequence

The Fibonacci sequence is a series where each number is the sum of the two preceding ones, starting with 0 and 1. It can be defined recursively as:

• F(n)=F(n−1)+F(n−2)F(n)=F(n−1)+F(n−2) for n>1n>1
• F(0)=0F(0)=0
• F(1)=1F(1)=1

PL/SQL Recursive Function Example:

CREATE OR REPLACE FUNCTION fibonacci (p_n IN NUMBER)
RETURN NUMBER
IS
  v_result NUMBER;
BEGIN
  -- Base cases
  IF p_n = 0 THEN
    RETURN 0;
  ELSIF p_n = 1 THEN
    RETURN 1;
  ELSE
    -- Recursive case
    v_result := fibonacci(p_n - 1) + fibonacci(p_n - 2);
    RETURN v_result;
  END IF;
END fibonacci;
/

Explanation:

• Base Cases: Return 0 for p_n = 0 and 1 for p_n = 1.
• Recursive Case: Sum of the Fibonacci numbers of the two preceding positions.

Usage Example:

DECLARE
  v_result NUMBER;
BEGIN
  v_result := fibonacci(6);
  DBMS_OUTPUT.PUT_LINE('Fibonacci number at position 6 is: ' || v_result);
END;
/

Explanation:

• v_result := fibonacci(6);: Calls the recursive fibonacci function with 6 and stores the result in v_result.

Considerations for Recursive Functions

1. Base Case: Always ensure you have a base case to prevent infinite recursion.
2. Performance: Recursive functions, especially those that compute Fibonacci numbers, can be inefficient for large inputs due to redundant calculations. Consider using memoization or iterative solutions for performance optimization.
3. Stack Overflow: Recursive functions can lead to stack overflow errors if the recursion depth is too large. Monitor and manage recursion depth carefully.

Parameter Modes in PL/SQL

In PL/SQL, parameter modes specify how parameters are used in procedures and functions. Understanding these modes is crucial for effective database programming, as they define how data is passed to and from subprograms. There are three primary parameter modes:

1. IN
2. OUT
3. IN OUT

1. IN Parameter

• Purpose: Passes data into a procedure or function.
• Behavior: The parameter value is read-only within the subprogram. The subprogram can use the value but cannot modify it.
• Default Mode: Parameters are IN by default if no mode is specified.

Example:

CREATE OR REPLACE PROCEDURE print_message (p_message IN VARCHAR2)
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Message: ' || p_message);
END print_message;
/

Usage:

BEGIN
  print_message('Hello, World!');
END;
/

Explanation:

• p_message IN VARCHAR2: The p_message parameter is passed into the procedure and cannot be changed within the procedure.

2. OUT Parameter

• Purpose: Returns data from a procedure or function.
• Behavior: The parameter is used to output data from the subprogram. It does not need to be initialized before being passed in, but it must be assigned a value within the subprogram.
• Usage: Typically used to return results or output multiple values from a procedure.

Example:

CREATE OR REPLACE PROCEDURE get_employee_name (
  p_employee_id IN NUMBER,
  p_first_name OUT VARCHAR2,
  p_last_name OUT VARCHAR2
)
IS
BEGIN
  SELECT first_name, last_name
  INTO p_first_name, p_last_name
  FROM employees
  WHERE employee_id = p_employee_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_first_name := NULL;
    p_last_name := NULL;
END get_employee_name;
/

Usage:

DECLARE
  v_first_name VARCHAR2(50);
  v_last_name VARCHAR2(50);
BEGIN
  get_employee_name(1001, v_first_name, v_last_name);
  DBMS_OUTPUT.PUT_LINE('First Name: ' || v_first_name);
  DBMS_OUTPUT.PUT_LINE('Last Name: ' || v_last_name);
END;
/

Explanation:

• p_first_name OUT VARCHAR2 and p_last_name OUT VARCHAR2: These parameters are used to return employee details from the procedure.

3. IN OUT Parameter

• Purpose: Passes data into and out of a procedure or function.
• Behavior: The parameter can be used to both receive an input value and return an output value. The value passed in can be modified within the subprogram and the modified value will be available after the subprogram execution.

Example:

CREATE OR REPLACE PROCEDURE update_salary (
  p_employee_id IN NUMBER,
  p_increase_percentage IN OUT NUMBER
)
IS
BEGIN
  -- Modify the input percentage to a new value
  p_increase_percentage := p_increase_percentage + 5;
  
  -- Update salary based on the new percentage
  UPDATE employees
  SET salary = salary + (salary * p_increase_percentage / 100)
  WHERE employee_id = p_employee_id;
  
  COMMIT;
END update_salary;
/

Usage:

DECLARE
  v_increase_percentage NUMBER := 10;
BEGIN
  update_salary(1001, v_increase_percentage);
  DBMS_OUTPUT.PUT_LINE('Updated Increase Percentage: ' || v_increase_percentage);
END;
/

Explanation:

• p_increase_percentage IN OUT NUMBER: The parameter is passed into the procedure, modified, and the updated value is returned.

Summary of Parameter Modes

• IN: The parameter is used to pass data into a procedure or function. It is read-only within the subprogram.
• OUT: The parameter is used to return data from a procedure or function. It must be assigned a value within the subprogram.
• IN OUT: The parameter is used to pass data into and out of a procedure or function. It can be both read and modified within the subprogram.

Understanding and properly using parameter modes allows you to design flexible and reusable procedures and functions in PL/SQL, making your code more efficient and easier to manage.