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