Oracle PL/SQL - PL SQL Introduction Control Statements

Introduction

PL/SQL allows you to run a SQL query and process the rows of the result set one at a time.

You can use a basic loop, condition statements.

You can control the process by using individual statements to run the query, retrieve the results, and do calculation.

Demo

SQL>
SQL> drop TABLE emp;

Table dropped.-- ww  w.  j  av  a 2s . co  m

SQL>
SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King');
SQL> INSERT INTO emp VALUES( 101, 'Mary', 'Smith');
SQL>
SQL>
SQL> BEGIN
  2    FOR someone IN (
  3      SELECT * FROM emp
  4      WHERE empid < 120
  5      ORDER BY empid
  6    )
  7    LOOP
  8      DBMS_OUTPUT.PUT_LINE('First name = ' || someone.first_name ||
  9                           ', Last name = ' || someone.last_name);
 10    END LOOP;
 11  END;
 12   /
First name = Steven, Last name = King
First name = Mary, Last name = Smith

PL/SQL procedure successfully completed.

SQL>