Skip to main content

PL/SQL LOOPS

PL/SQL Loops are iterative constructs that enable repeated execution of a block of code based on specified conditions or ranges. They include FOR LOOP, WHILE LOOP, and LOOP-END LOOP, facilitating efficient data processing and task automation within Oracle database programs. These loops enhance flexibility by allowing developers to iterate over collections, execute statements multiple times, and handle complex logic seamlessly.



LOOP-END LOOP

The LOOP-END LOOP loop is an indefinite loop that continues until an EXIT or EXIT WHEN statement is encountered.

LOOP

   -- Code block executed indefinitely until an EXIT or EXIT WHEN statement is encountered

   EXIT WHEN condition;

END LOOP;

 

Example:

DECLARE

   total NUMBER := 0;

   i NUMBER := 1;

BEGIN

   LOOP

      total := total + i;

      i := i + 1;

      EXIT WHEN i > 10;

   END LOOP;

   DBMS_OUTPUT.PUT_LINE('Total: ' || total);

END;


This PL/SQL code snippet demonstrates the use of a LOOP statement to calculate the sum of numbers from 1 to 10:

  1. DECLARE: This keyword starts the declaration section where variables (total and i in this case) are initialized.
    • total NUMBER := 0;: Initializes the variable total to 0, which will store the cumulative sum of numbers.
    • i NUMBER := 1;: Initializes the variable i to 1, which will be used as a counter in the loop.
  2. BEGIN: Marks the beginning of the executable section of the PL/SQL block, where actual logic and statements are written.
  3. LOOP: This keyword starts a loop that will execute repeatedly until a specific condition is met.
    • total := total + i;: Adds the current value of i to total, incrementally calculating the sum.
    • i := i + 1;: Increments the value of i by 1 in each iteration, progressing through each number from 1 to 10.
    • EXIT WHEN i > 10;: Checks if i has reached or exceeded 10. If true, the loop is exited, terminating further iterations.
  4. END LOOP;: Marks the end of the loop structure.
  5. DBMS_OUTPUT.PUT_LINE('Total: ' || total);: Outputs the final calculated sum (total) to the console using the DBMS_OUTPUT.PUT_LINE procedure.
  6. END;: Marks the end of the PL/SQL block.

 


FOR LOOP

The FOR LOOP iterates over a range of values.

FOR loop_counter IN lower_bound..upper_bound LOOP

   -- Code block executed for each iteration of the loop

END LOOP;


Example:

DECLARE

   total NUMBER := 0;

BEGIN

   FOR i IN 1..10 LOOP

      total := total + i;

   END LOOP;

   DBMS_OUTPUT.PUT_LINE('Total: ' || total);

END;


This PL/SQL code snippet demonstrates the use of a FOR LOOP to calculate the sum of numbers from 1 to 10:

  1. DECLARE: This keyword starts the declaration section where variables (total in this case) are initialized.

    • total NUMBER := 0;: Initializes the variable total to 0, which will store the cumulative sum of numbers.
  2. BEGIN: Marks the beginning of the executable section of the PL/SQL block, where actual logic and statements are written.

  3. FOR i IN 1..10 LOOP: This line initiates a FOR LOOP that iterates over a range of values from 1 to 10 (1..10).

    • i: Represents the loop index variable that takes on each value within the specified range in each iteration of the loop.

    • total := total + i;: Adds the current value of i to total in each iteration, calculating the sum of numbers from 1 to 10.

  4. END LOOP;: Marks the end of the FOR LOOP structure.

  5. DBMS_OUTPUT.PUT_LINE('Total: ' || total);: Outputs the final calculated sum (total) to the console using the DBMS_OUTPUT.PUT_LINE procedure.

  6. END;: Marks the end of the PL/SQL block.

 


WHILE LOOP

The FOR LOOP iterates over a range of values. The WHILE LOOP executes a block of code as long as a specified condition is true.

WHILE condition LOOP

   -- Code block executed as long as condition is true

END LOOP;


Example:

DECLARE

   total NUMBER := 0;

   i NUMBER := 1;

BEGIN

   WHILE i <= 10 LOOP

      total := total + i;

      i := i + 1;

   END LOOP;

   DBMS_OUTPUT.PUT_LINE('Total: ' || total);

END;


This PL/SQL code snippet demonstrates the use of a WHILE LOOP to calculate the sum of numbers from 1 to 10:

  1. DECLARE: This keyword starts the declaration section where variables (total and i in this case) are initialized.

    • total NUMBER := 0;: Initializes the variable total to 0, which will store the cumulative sum of numbers.
    • i NUMBER := 1;: Initializes the variable i to 1, which serves as a counter for the loop.
  2. BEGIN: Marks the beginning of the executable section of the PL/SQL block, where actual logic and statements are written.

  3. WHILE i <= 10 LOOP: This line initiates a WHILE LOOP that continues to iterate as long as the condition i <= 10 is true.

    • i: Represents the loop control variable that increments in each iteration.
    • total := total + i;: Adds the current value of i to total in each iteration, calculating the sum of numbers from 1 to 10.
    • i := i + 1;: Increments the value of i by 1 in each iteration to progress through each number from 1 to 10.
  4. END LOOP;: Marks the end of the WHILE LOOP structure.

  5. DBMS_OUTPUT.PUT_LINE('Total: ' || total);: Outputs the final calculated sum (total) to the console using the DBMS_OUTPUT.PUT_LINE procedure.

  6. END;: Marks the end of the PL/SQL block.

 

 

 

 

 

 

 

 

 

Comments

Popular posts from this blog

Performance Optimization

Performance optimization in SQL is crucial for ensuring that your database queries run efficiently, especially as the size and complexity of your data grow. Here are several strategies and techniques to optimize SQL performance: Indexing Create Indexes : Primary Key and Unique Indexes : These are automatically indexed. Ensure that your tables have primary keys and unique constraints where applicable. Foreign Keys : Index foreign key columns to speed up join operations. Composite Indexes : Use these when queries filter on multiple columns. The order of columns in the index should match the order in the query conditions. Avoid Over-Indexing:  Too many indexes can slow down write operations (INSERT, UPDATE, DELETE). Only index columns that are frequently used in WHERE clauses, JOIN conditions, and as sorting keys. Query Optimization Use SELECT Statements Efficiently : SELECT Only Necessary Columns : Avoid using SELECT * ; specify only ...

DAX UPPER Function

The DAX UPPER function in Power BI is used to convert all characters in a text string to uppercase. This function is useful for standardizing text data, ensuring consistency in text values, and performing case-insensitive comparisons. Syntax: UPPER(<text>) <text>: The text string that you want to convert to uppercase. Purpose: The UPPER function helps ensure that text data is consistently formatted in uppercase. This can be essential for tasks like data cleaning, preparing text for comparisons, and ensuring uniformity in text-based fields. E xample: Suppose you have a table named "Customers" with a column "Name" that contains names in mixed case. You want to create a new column that shows all names in uppercase. UppercaseName = UPPER(Customers[Name]) Example Scenario: Assume you have the following "Customers" table: You can use the UPPER function as follows: Using the UPPER function, you can convert all names to uppercase: UppercaseName = ...

TechUplift: Elevating Your Expertise in Every Click

  Unlock the potential of data with SQL Fundamental: Master querying, managing, and manipulating databases effortlessly. Empower your database mastery with PL/SQL: Unleash the full potential of Oracle databases through advanced programming and optimization. Unlock the Potential of Programming for Innovation and Efficiency.  Transform raw data into actionable insights effortlessly. Empower Your Data Strategy with Power Dataware: Unleash the Potential of Data for Strategic Insights and Decision Making.