Oracle DBA FAQ - Introduction to PL/SQL

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4

(Continued from previous part...)

What Are the Execution Control Statements?

PL/SQL supports three groups of execution control statements:

IF Statements - Conditionally executes a block of statements.
CASE Statements - Selectively executes a block of statements.
LOOP Statements - Repeatedly executes a block of statements.
GOTO Statements - Unconditional changes the execution flow to a specified statement.

The script below shows execution control statements:

SQL> set serveroutput on;

SQL> DECLARE                                                    
  2    total NUMBER;                                            
  3  BEGIN                                                      
  4    total := 0;                                              
  5    LOOP                                                     
  6      total := total+1;                                      
  7      IF total >= 10 THEN                                    
  8        GOTO print;                                          
  9      END IF;                                                
 10    END LOOP;                                                
 11    <>                                                
 12    DBMS_OUTPUT.PUT_LINE('Total counts: ' 
 13       || TO_CHAR(total));
 14  END;
 15  /
Total counts: 10

This sample script shows you how to use LOOP, IF, and GOTO statements.

How To Use SQL Statements in PL/SQL?

SQL DML (Data Manipulation Language) statements can be included in PL/SQL code blocks directly without any changes. See the script below for examples:

SQL> CREATE TABLE tip (id NUMBER(5) PRIMARY KEY,
  2    subject VARCHAR(80) NOT NULL,
  3    description VARCHAR(256) NOT NULL);
Table created.

SQL> BEGIN
  2    INSERT INTO tip VALUES(1, 'PL/SQL',
  3       'Good for beginners.');
  4    UPDATE tip SET description = 'Good for beginners.';
  5  END;
  6  /
PL/SQL procedure successfully completed.

SQL> COL subject FORMAT A12;
SQL> COL description FORMAT A24;
SQL> SELECT * FROM tip;
        ID SUBJECT      DESCRIPTION
---------- ------------ -------------------
         1 PL/SQL       Good for beginners.

SQL> DROP TABLE tip;
Table dropped.

This script example actually has 3 parts:

Part 1 - A single DDL (Data Definition Language) statement, CREATE TABLE, outside the PL/SQL code block.
Part 2 - A PL/SQL code block defined and executed to insert and updated a record.
Part 3 - A couple of SQL statements outside the PL/SQL code block to view the record and trop the table.

How To Process Query Result in PL/SQL?

You can run queries (SELECT statements) in a PL/SQL code blocks, and process the results a loop as shown in the following script example:

SQL> set serveroutput on;

SQL> BEGIN
  2     FOR row IN
  3         (SELECT * FROM employees WHERE manager_id = 101)
  4         LOOP
  5       DBMS_OUTPUT.PUT_LINE('Name = ' || row.last_name);
  6     END LOOP;
  7  END;
  8  /
Name = Greenberg
Name = Whalen
Name = Mavris
Name = Baer
Name = Higgins

The loop statement in this script automatically sets a temporary variable "row" each row in the result set returned from the SELECT statement.

How To Create an Array in PL/SQL?

If you want create an array data structure, you can use the collection type VARRAY. VARRAY stands for Variable Array. Here is a sample script on how to use VARRAY:

SQL> set serveroutput on;

SQL> DECLARE
  2    TYPE list IS VARRAY(3) OF VARCHAR(16);
  3    domain_list list;
  4  BEGIN
  5    domain_list := list('google','fyicenter','myspace');
  6    DBMS_OUTPUT.PUT_LINE('Second domain: '
  7       || domain_list(2));
  8  END;
  9  /
Second domain: fyicenter

As you can see, to define an array variable, you need to create your own array type, and use that type to define array variables.

(Continued on next part...)

Part: 1 2 3 4

More...