Site icon Database Tutorials

Oracle PL/SQL Loops

This article contains information about PL/SQL Loops such as WHILE, FOR and necessary statements for these loops like GOTO, Continue, EXIT.

What is loop in PL SQL?

PL/SQL Loops are structures used to run commands again until a certain condition is met.

What are Oracle loops?

LOOP in Oracle

It is used to run the commands in the block continuously.

NOTE: If the loop is not terminated with the EXIT keyword it will run continuously-infinitely. Example LOOP usage is as follows.

Example LOOP WHEN EXIT usage is as follows.

WHILE LOOP in PL/SQL

It is used to run the commands in the block until the condition is met.

Example WHILE usage is as follows.

FOR LOOP in PL/SQL

It is used to run commands in the block as much as predetermined number.

Example FOR usage is as follows.

The number range can be a custom range.

To reverse the start and end in the FOR loop

GOTO Statement in PL/SQL

It is used to direct the command flow to a predetermined tag.

Example GOTO usage is as follows.

With GOTO, the loop can be created as follows.

NOTE: It is not recommended to use GOTO. Because GOTO complicates the command flow.

EXIT Statement in PL/SQL

It is used to exit the loop.

The WHEN keyword can be used to determine the condition.

Continue Statement in PL/SQL

Its used to skip loop steps.

The WHEN keyword can be used to determine the condition.

Various operators can be used for a certain range.

NOTE: The keyword CONTINUE must be added at the beginning of the commands. Otherwise, the step is skipped after the commands are executed.

You can find more detailed information about below topics in the below link.

PL/SQL Tutorial

You will find below topics in this article.

  1. What is PL/SQL
  2. Oracle PL/SQL Data Types and Variables and Literals
  3. Oracle PL/SQL Operators
  4. Oracle PL/SQL Conditional Statements
  5. Oracle PL/SQL Loops
  6. Oracle PL/SQL Procedures and Procedure Parameters
  7. Oracle PL/SQL Functions
  8. Oracle PL/SQL Cursor
  9. Oracle PL/SQL Records
  10. Oracle PL/SQL Exception
  11. Oracle PL/SQL Trigger
  12. Oracle PL/SQL Packages
  13. Oracle PL/SQL Collections

You can find more information about loops at docs.oracle.com

Exit mobile version