Unit 3 :- PL/SQL and conditional Statements

PL/sQL and conditional statement

INDEX

3.1 Introduction to PL/SQL (Definition & Block Structure)
3.2 Variables, Constants and Data Type
3.3 Assigning Values to Variables
3.4 User Defined Record
3.5 Conditional Statements
3.5.1 IF…THEN statement
3.5.2 IF..Else statements
3.5.3 multiple conditions
3.5.4 Nested IF statements
3.5.5 CASE statements

PL/sQL and conditional statement

Introduction to PL/SQL

  • PL/SQL (Procedural Language/Structured Query Language) is an extension of SQL (Structured Query Language) used in Oracle databases.
  • It is designed to enhance the capabilities of SQL by adding procedural constructs such as loops, conditions, and error handling.
  • PL/SQL allows developers to write complex queries, create stored procedures, triggers, and functions, and implement business logic within the database.
  • It helps in reducing network traffic by executing multiple SQL statements in a single block, improving performance and efficiency.

Definition of PL/SQL

  • PL/SQL is a procedural programming language developed by Oracle Corporation.
  • It extends SQL by incorporating programming features such as variables, conditions, loops, and exception handling.
  • It enables users to write modular, reusable, and maintainable code within an Oracle database environment.

Key Features of PL/SQL:

  • Supports procedural constructs like loops and conditions.
  • Allows block-structured programming.
  • Improves performance by reducing network traffic.
  • Provides robust error handling through exception handling.
  • Enables the creation of stored procedures, functions, and triggers.
  • Ensures security through encapsulation of business logic.

PL/SQL Block Structure

  • PL/SQL code is structured into blocks, which makes it modular and easy to manage. A PL/SQL block consists of three main sections:
  1. Declaration Section (Optional)
  2. Execution Section (Mandatory)
  3. Exception Handling Section (Optional)

Basic Syntax of a PL/SQL Block:

DECLARE
   -- Declaration Section (Optional)
   variable_name datatype;

BEGIN
   -- Execution Section (Mandatory)
   -- SQL and PL/SQL statements go here

EXCEPTION
   -- Exception Handling Section (Optional)
   WHEN exception_name THEN
      -- Handling statements

END;
/

1. Declaration Section :- 

  • This section starts with the keyword DECLARE.
  • It is optional and is used to declare variables, constants, cursors, and user-defined exceptions.
  • Variables declared here can be used throughout the block.
  • Example:

DECLARE
   employee_name VARCHAR2(50);
   employee_salary NUMBER(10,2);

2. Execution Section :- 

  • This is the mandatory part of a PL/SQL block.
  • It begins with the keyword BEGIN and ends with END;.
  • This section contains SQL queries, assignments, loops, and conditional statements to perform specific operations.

BEGIN
   employee_name := 'John Doe';
   employee_salary := 50000;
   
   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || employee_name);
   DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || employee_salary);
END;
/

3. Exception Handling Section :- 

  • This section starts with the keyword EXCEPTION.
  • It is optional but useful for handling errors gracefully.
  • It helps in catching runtime errors and taking appropriate actions.

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

Example of a Complete PL/SQL Block:

DECLARE
   employee_name VARCHAR2(50);
   employee_salary NUMBER(10,2);

BEGIN
   -- Assigning values
   employee_name := 'Alice Johnson';
   employee_salary := 60000;

   -- Displaying values
   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || employee_name);
   DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || employee_salary);

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

END;
/

Explanation:

  • The DECLARE section defines two variables: employee_name and employee_salary.
  • The BEGIN section assigns values to these variables and displays them using DBMS_OUTPUT.PUT_LINE.
  • The EXCEPTION section handles any unexpected errors.

Variables, Constants, and Data Types in PL/SQL. 

1. Variables in PL/SQL :- 

  • A variable in PL/SQL is a named storage location that holds a value, which can change during program execution.
  • Variables help store and manipulate data dynamically in PL/SQL blocks.

Declaration of Variables

  • Variables must be declared before they are used.
  • The declaration includes the variable name, data type, and an optional initial value.
  • The syntax for declaring a variable is:

variable_name datatype [DEFAULT initial_value];
Or 
variable_name datatype := initial_value;

EXAMPLE :- 

DECLARE
   employee_name VARCHAR2(50);
   employee_salary NUMBER(10,2) DEFAULT 50000;
   department_id NUMBER := 101;
BEGIN
   -- Assigning values
   employee_name := 'John Doe';
   employee_salary := employee_salary + 5000;

   -- Displaying values
   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || employee_name);
   DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || employee_salary);
   DBMS_OUTPUT.PUT_LINE('Department ID: ' || department_id);
END;
/

Rules for Declaring Variables 

  1. The variable name must start with a letter.
  2. It can contain letters, numbers, and underscores but cannot start with a number.
  3. The variable name must be unique within its scope.
  4. It should not be a reserved keyword in PL/SQL.

2. Constants in PL/SQL :- 

  • A constant is similar to a variable, but its value cannot be changed after being assigned.
  • Constants are useful when a fixed value is needed throughout the program.

Declaration of Constants.

  • Constants are declared using the CONSTANT keyword.
  • They must be initialized at the time of declaration.

constant_name CONSTANT datatype := value;

EXAMPLE :- 

DECLARE
   pi CONSTANT NUMBER := 3.14159;
   gravity CONSTANT NUMBER := 9.81;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Value of Pi: ' || pi);
   DBMS_OUTPUT.PUT_LINE('Gravity Acceleration: ' || gravity);
END;
/

Key Points About Constants. 

  1. The CONSTANT keyword makes the variable read-only.
  2. A constant must be assigned a value at the time of declaration.
  3. Once assigned, its value cannot be changed.

3. Data Types in PL/SQL :- 

  • PL/SQL provides different data types to define variables and constants based on the type of data they store.

Categories of Data Types

  • PL/SQL data types are classified into the following main categories:

1. Scalar Data Types :- 

  • These data types store single values.
Data Type Description Example
NUMBER(p,s) Stores integers and floating-point numbers salary NUMBER(10,2);
VARCHAR2(n) Stores variable-length character strings (max 32,767 bytes) name VARCHAR2(50);
CHAR(n) Stores fixed-length character strings code CHAR(5);
DATE Stores date and time values dob DATE;
BOOLEAN Stores TRUE, FALSE, or NULL values is_active BOOLEAN;

Example: Using Scalar Data Types


DECLARE
   emp_id NUMBER(5);
   emp_name VARCHAR2(50);
   emp_salary NUMBER(8,2);
   emp_join_date DATE;
   is_manager BOOLEAN;
BEGIN
   emp_id := 101;
   emp_name := 'Alice';
   emp_salary := 75000.50;
   emp_join_date := TO_DATE('2023-06-15', 'YYYY-MM-DD');
   is_manager := TRUE;

   DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_id);
   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
   DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || emp_salary);
   DBMS_OUTPUT.PUT_LINE('Joining Date: ' || emp_join_date);
   DBMS_OUTPUT.PUT_LINE('Is Manager: ' || is_manager);
END;
/

Assigning Values to Variables in PL/SQL Block.

  • In PL/SQL, assigning values to variables is a fundamental operation that enables data storage and manipulation within a block.
  • PL/SQL provides multiple ways to assign values to variables, each suitable for different scenarios. Proper value assignment ensures efficient processing and accurate computations.

Ways to Assign Values to  Variables. 

PL/SQL provides three primary methods to assign values to variables:
  1. Using the DEFAULT Keyword (at the time of declaration)
  2. Using the := Operator (in the DECLARE or BEGIN section)
  3. Using the SELECT INTO Statement (assigning values from a database query)

1. Using the DEFAULT Keyword :- 

  • The DEFAULT keyword initializes a variable with a predefined value at the time of declaration.
  • This method improves code readability and prevents uninitialized variables.

  • Syntax :- 

variable_name datatype DEFAULT value;

EXAMPLE :- 

DECLARE
   employee_name VARCHAR2(50) DEFAULT 'John Doe';
   employee_salary NUMBER(8,2) DEFAULT 50000;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || employee_name);
   DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || employee_salary);
END;
/

2. Using the := Operator :- 

  • The := operator assigns a value to a variable in the declaration section or within the execution section (BEGIN ... END).
  • This is the most common way to assign values dynamically.
  • SYNTAX
variable_name := value;

EXAMPLE :- 

DECLARE
   employee_id NUMBER(5);
   employee_name VARCHAR2(50);
   employee_salary NUMBER(8,2);
BEGIN
   -- Assigning values
   employee_id := 101;
   employee_name := 'Alice';
   employee_salary := 75000;

   -- Displaying assigned values
   DBMS_OUTPUT.PUT_LINE('Employee ID: ' || employee_id);
   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || employee_name);
   DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || employee_salary);
END;
/

Key point :- 

  • Allows dynamic assignment during execution.
  • Provides flexibility to update values at runtime.
  • Used for both numerical and string values.

3. Using the SELECT INTO Statement. 

  • The SELECT INTO statement assigns a value retrieved from a database query to a PL/SQL variable.
  • It is useful when fetching values from tables.
  • Syntax
SELECT column_name INTO variable_name FROM table_name WHERE condition;

EXAMPLE :- 

DECLARE
   employee_name VARCHAR2(50);
   employee_salary NUMBER(8,2);
BEGIN
   -- Assigning values from the database
   SELECT name, salary INTO employee_name, employee_salary 
   FROM employees 
   WHERE employee_id = 102;

   -- Displaying assigned values
   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || employee_name);
   DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || employee_salary);
END;
/

Handling Null Values in Assignments.

  • If no value is assigned, a variable holds NULL by default.
  • Using NVL() function prevents NULL values by providing a default.

  • Example: Avoiding Null Values in Assignments

DECLARE
   employee_bonus NUMBER(10,2);
BEGIN
   -- If NULL, assign default value
   employee_bonus := NVL(employee_bonus, 5000);
   DBMS_OUTPUT.PUT_LINE('Employee Bonus: ' || employee_bonus);
END;
/

User-Defined Record in PL/SQL

  • A user-defined record in PL/SQL is a composite data type that groups multiple related values into a single unit.
  • It allows programmers to store and manipulate different types of data as a single record, similar to a row in a table.
  • PL/SQL records help in organizing data efficiently, reducing the need for multiple variables, and improving readability and maintainability of the code.

Declaring a User-Defined Record.

  • A user-defined record is declared using the TYPE statement within the DECLARE section of a PL/SQL block.

Steps to Create a User-Defined. Record:

  1. Declare a record type using TYPE ... IS RECORD.
  2. Declare a variable of that record type.
  3. Assign values to the fields of the record.
  4. Use the record in PL/SQL operations.
Syntax :- 
DECLARE
   TYPE record_type IS RECORD (
      field1 datatype,
      field2 datatype,
      field3 datatype
   );

   variable_name record_type;
BEGIN
   -- Assigning values to record fields
   variable_name.field1 := value1;
   variable_name.field2 := value2;
   variable_name.field3 := value3;

   -- Displaying record values
   DBMS_OUTPUT.PUT_LINE('Field 1: ' || variable_name.field1);
   DBMS_OUTPUT.PUT_LINE('Field 2: ' || variable_name.field2);
   DBMS_OUTPUT.PUT_LINE('Field 3: ' || variable_name.field3);
END;
/

Example 1: Basic User-Defined Record

DECLARE
   -- Defining a record type
   TYPE employee_record IS RECORD (
      emp_id NUMBER(5),
      emp_name VARCHAR2(50),
      emp_salary NUMBER(10,2)
   );

   -- Declaring a variable of record type
   emp employee_record;
BEGIN
   -- Assigning values to the record fields
   emp.emp_id := 101;
   emp.emp_name := 'John Doe';
   emp.emp_salary := 75000;

   -- Displaying record values
   DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp.emp_id);
   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp.emp_name);
   DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || emp.emp_salary);
END;
/

Example 2: Fetching Data from a Table into a Record

DECLARE
   -- Defining a record type
   TYPE employee_record IS RECORD (
      emp_id employees.id%TYPE,
      emp_name employees.name%TYPE,
      emp_salary employees.salary%TYPE
   );

   -- Declaring a variable of record type
   emp employee_record;
BEGIN
   -- Fetching data from the table into the record
   SELECT id, name, salary INTO emp.emp_id, emp.emp_name, emp.emp_salary
   FROM employees
   WHERE id = 102;

   -- Displaying the retrieved values
   DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp.emp_id);
   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp.emp_name);
   DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || emp.emp_salary);
END;
/

Conditional Statements in PL/SQL. 

  • Conditional statements in PL/SQL control the flow of execution based on specific conditions.
  • hese statements evaluate conditions and execute different blocks of code accordingly.
  • PL/SQL provides several conditional structures, including IF...THEN, IF...ELSE, multiple conditions, and nested IF statements.

IF…THEN Statement.

  • The IF...THEN statement executes a block of code only if the specified condition is TRUE.
  • If the condition evaluates to FALSE or NULL, the block of code inside the THEN statement is skipped.
  • Syntax.
IF condition THEN
   -- Code to execute if condition is TRUE
END IF;

Example: Basic IF…THEN Statement

DECLARE
   salary NUMBER := 60000;
BEGIN
   IF salary > 50000 THEN
      DBMS_OUTPUT.PUT_LINE('The salary is above 50,000.');
   END IF;
END;
/

Key Points:

  • Executes only when the condition is TRUE.
  • Skips execution if the condition is FALSE or NULL.
  • Used for single-condition checks.

IF…ELSE Statement.

  • The IF...ELSE statement provides an alternative execution path when the condition evaluates to FALSE or NULL.
  • If the condition is TRUE, the THEN block executes; otherwise, the ELSE block executes.
  • SYNTAX
IF condition THEN
   -- Code to execute if condition is TRUE
ELSE
   -- Code to execute if condition is FALSE or NULL
END IF;

Example: IF…ELSE Statement

DECLARE
   salary NUMBER := 40000;
BEGIN
   IF salary > 50000 THEN
      DBMS_OUTPUT.PUT_LINE('The salary is above 50,000.');
   ELSE
      DBMS_OUTPUT.PUT_LINE('The salary is 50,000 or below.');
   END IF;
END;
/

Key point

  • Provides an alternative block for execution.
  • Ensures that at least one block executes.
  • Useful for binary decision-making (Yes/No, Pass/Fail, etc.).

Multiple Conditions. 

  • To check multiple conditions, ELSE IF (ELSIF) statements are used. This structure evaluates conditions sequentially and executes the first TRUE condition’s block.
  • If none are TRUE, the ELSE block executes.
  • Syntax:
IF condition1 THEN
   -- Code to execute if condition1 is TRUE
ELSIF condition2 THEN
   -- Code to execute if condition2 is TRUE
ELSIF condition3 THEN
   -- Code to execute if condition3 is TRUE
ELSE
   -- Code to execute if none of the conditions are TRUE
END IF;

Example: Using Multiple Conditions

DECLARE
   marks NUMBER := 78;
BEGIN
   IF marks >= 90 THEN
      DBMS_OUTPUT.PUT_LINE('Grade: A');
   ELSIF marks >= 80 THEN
      DBMS_OUTPUT.PUT_LINE('Grade: B');
   ELSIF marks >= 70 THEN
      DBMS_OUTPUT.PUT_LINE('Grade: C');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Grade: F');
   END IF;
END;
/

Key Points:

  • Evaluates multiple conditions sequentially.
  • Executes only the first TRUE condition’s block.
  • The ELSE block handles cases where no conditions are met.

Nested IF Statements.

  • A nested IF statement is an IF statement inside another IF statement.
  • It is useful when decisions depend on multiple conditions that require further evaluation.
  • SYNTAX
IF condition1 THEN
   IF condition2 THEN
      -- Code to execute if both conditions are TRUE
   ELSE
      -- Code to execute if condition1 is TRUE but condition2 is FALSE
   END IF;
ELSE
   -- Code to execute if condition1 is FALSE
END IF;

Example: Nested IF Statements

    DECLARE
   salary NUMBER := 60000;
   experience NUMBER := 5;
BEGIN
   IF salary > 50000 THEN
      IF experience >= 5 THEN
         DBMS_OUTPUT.PUT_LINE('Eligible for Senior Position.');
      ELSE
         DBMS_OUTPUT.PUT_LINE('Eligible for Mid-Level Position.');
      END IF;
   ELSE
      DBMS_OUTPUT.PUT_LINE('Not eligible for a senior position.');
   END IF;
END;
/

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Conditional Structure Description Executes When
IF...THEN Executes a block if the condition is TRUE Condition is TRUE
IF...ELSE Executes one block if condition is TRUE, another if FALSE Always executes one block
IF...ELSIF...ELSE Checks multiple conditions sequentially First TRUE condition
Nested IF An IF inside another IF for complex decisions Both conditions are met

CASE Statements in PL/SQL.

  • The CASE statement in PL/SQL is a conditional control structure used for decision-making.
  • It evaluates conditions and executes a corresponding block of code based on the first TRUE condition.
  • The CASE statement simplifies complex conditional logic, making the code more readable and efficient.

PL/SQL provides two types of CASE statements:

  1. Simple CASE Statement – Compares a single expression with multiple values.
  2. Searched CASE Statement – Evaluates multiple conditions independently.

1. Simple CASE Statement.

  • The simple CASE statement compares an expression against multiple values and executes the block corresponding to the first match.
  • If no match is found, an ELSE block (if provided) executes.
  • Syntax 
CASE expression
   WHEN value1 THEN
      -- Code to execute if expression = value1
   WHEN value2 THEN
      -- Code to execute if expression = value2
   ELSE
      -- Code to execute if no match is found (optional)
END CASE;

Example: Using Simple CASE Statement

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('Fail');
   END CASE;
END;
/

Explanation:

  • The variable grade is compared with predefined values 'A', 'B', and 'C'
  • When grade = 'B', the output is “Good”.
  • If grade had a different value, the ELSE block would execute.

2. Searched CASE Statement.

  • The searched CASE statement evaluates multiple independent conditions.
  • This is useful when different conditions are required instead of comparing a single expression to multiple values.
  • Syntax:
CASE 
   WHEN condition1 THEN
      -- Code to execute if condition1 is TRUE
   WHEN condition2 THEN
      -- Code to execute if condition2 is TRUE
   ELSE
      -- Code to execute if none of the conditions are TRUE (optional)
END CASE;

Example: Using Searched CASE Statement

DECLARE
   salary NUMBER := 70000;
BEGIN
   CASE 
      WHEN salary > 100000 THEN
         DBMS_OUTPUT.PUT_LINE('High Salary');
      WHEN salary BETWEEN 50000 AND 100000 THEN
         DBMS_OUTPUT.PUT_LINE('Medium Salary');
      ELSE
         DBMS_OUTPUT.PUT_LINE('Low Salary');
   END CASE;
END;
/
/

Explanation:

  • The conditions are checked sequentially.
  • Since salary = 70000, the second condition is TRUE, and “Medium Salary” is printed.
  • If salary had been greater than 100000, the first block would execute.

3. Using CASE Statement Inside SELECT Query.

  • The CASE statement can also be used inside a SELECT query to modify output based on conditions.
  • EXAMPLE
SELECT employee_id, name,
       CASE 
           WHEN salary > 100000 THEN 'High Salary'
           WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium Salary'
           ELSE 'Low Salary'
       END AS salary_category
FROM employees;
/

Explanation.

  • The column salary_category is generated dynamically based on salary values.
  • This is useful for categorizing data in reports. 

4. Using CASE Statement in PL/SQL Block with Variables.

  • The CASE statement can also be used inside a PL/SQL block with variables.
  • Example: Assigning Values Using CASE Statement
DECLARE
   age NUMBER := 30;
   category VARCHAR2(20);
BEGIN
   category := CASE 
                  WHEN age < 18 THEN 'Minor'
                  WHEN age BETWEEN 18 AND 60 THEN 'Adult'
                  ELSE 'Senior Citizen'
               END;

   DBMS_OUTPUT.PUT_LINE('Age Category: ' || category);
END;
/

Explanation:

  • The variable category is assigned based on age.
  • If age = 30, the result is “Adult”.
  • This avoids using multiple IF...ELSIF...ELSE conditions, making the code cleaner.

5. Nested CASE Statements.

  • A nested CASE statement is when one CASE statement is placed inside another.
  • This is useful for complex decision-making.
  • EXAMPLE 
DECLARE
   marks NUMBER := 85;
   result VARCHAR2(20);
BEGIN
   result := CASE 
                WHEN marks >= 50 THEN 
                   CASE 
                      WHEN marks >= 90 THEN 'Outstanding'
                      WHEN marks >= 75 THEN 'Distinction'
                      ELSE 'Pass'
                   END
                ELSE 'Fail'
             END;

   DBMS_OUTPUT.PUT_LINE('Result: ' || result);
END;
/

EXPLANATION.

  • The outer CASE checks if marks >= 50.
  • The outer CASE checks if marks >= 50.
  • If marks < 50, the result is “Fail”.
Feature CASE Statement IF Statement
Usage Best for comparing a single variable against multiple values Best for evaluating independent conditions
Readability More concise and structured Can become complex with multiple conditions
Performance Slightly better for multiple comparisons Similar performance, but harder to manage with many conditions
Flexibility Works well in SELECT queries and PL/SQL blocks Used only in procedural logic

Leave a Reply

Your email address will not be published. Required fields are marked *

sign up!

We’ll send you the hottest deals straight to your inbox so you’re always in on the best-kept software secrets.

© Copyright CODEMANIAC STUDIO ™

Twitter Facebook-square Linkedin Youtube