Control Statements in PL/SQL

Control statements are the most important PL/SQL extension to SQL.

PL/SQL has three categories of control statements:

■ Conditional selection statements: this let you run different statements for different data values. For more information, see Conditional Selection Statements

■ Loop statements: this let you repeat the same statements with a series of different data values.

For more information, see LOOP Statements

■ Sequential control statements: which allow you to go to a specified, labeled statement, or to do nothing. For more information, see Sequential Control Statements

Conditional Compilation

Conditional compilation lets you customize the functionality in a PL/SQL application without removing source code. For example, you can:

■ Use new features with the latest database release, and disable them when running the application in an older database release.

■ Activate debugging or tracing statements in the development environment, and hide them when running the application at a production site. For more information, see "Conditional Compilation"

Processing a Query Result Set One Row at a Time

PL/SQL lets you issue a SQL query and process the rows of the result set one at a time. You can use a simple loop, or you can control the process precisely by using individual statements to perform the query, retrieve data, and finish processing.

Processing Query Result Rows One at a Time

BEGIN

FOR someone IN (SELECT * FROM employees WHERE employee_id < 120)

LOOP

DBMS_OUTPUT.PUT_LINE (

'First name = ' || someone.first_name ||

', Last name = ' || someone.last_name

);

END LOOP;

END;

/

Result:

First name = Steven, Last name = King

First name = Neena, Last name = Kochhar

First name = Lex, Last name = De Haan

First name = Alexander, Last name = Hunold

First name = Bruce, Last name = Ernst

First name = David, Last name = Austin

First name = Valli, Last name = Pataballa

First name = Diana, Last name = Lorentz

First name = Nancy, Last name = Greenberg

First name = Daniel, Last name = Faviet

First name = John, Last name = Chen

First name = Ismael, Last name = Sciarra

First name = Jose Manuel, Last name = Urman

First name = Luis, Last name = Popp

First name = Den, Last name = Raphaely

First name = Alexander, Last name = Khoo

First name = Shelli, Last name = Baida

First name = Sigal, Last name = Tobias

First name = Guy, Last name = Himuro

First name = Karen, Last name = Colmenares