Some samples are run interactively from SQL*Plus; others are run
from Pro*C programs. You can experiment with the samples from any Oracle account. However, the Pro*C examples expect you to use the SCOTT/TIGER account.

Before trying the samples, you must create some database tables, then load the tables with data. You do that by running two SQL*Plus scripts, EXAMPBLD and EXAMPLOD, supplied with PL/SQL. These scripts can be found in the PL/SQL installation library. Check the Oracle installation or user's guide for your system.

Creating the Tables

Below is a listing of the SQL*Plus script EXAMPBLD. The CREATE statements in this script build the database tables processed by the sample programs. To run the script, invoke SQL*Plus, then issue the following command:

procedure get_employees(
dept_number in integer,
batch_size in integer,
found in out integer,
done_fetch out integer,
emp_name out charArrayTyp,
job-title out charArrayTyp,
salary out numArrayTyp);
end personnel;
/
create or replace package body personnel as
cursor get_emp (dept_number integer) is
select ename, job, sal from emp
where deptno = dept_number;
procedure get_employees(
dept_number in integer,
batch_size in integer,
found in out integer,
done_fetch out integer,
emp_name out charArrayTyp,
job_title out charArrayTyp,
salary out numArrayTyp) is
begin
if not get_emp%isopen then
open get_emp(dept_number);
end if;
done_fetch := 0;
found := 0;
for i in 1..batch_size loop
fetch get_emp into emp_name(i),
job_title(i), salary(i);
if get_emp%notfound then
close get_emp;
done_fetch := 1;
exit;
else
found := found + 1;
end if;
end loop;
end get_employees;
end personnel;
/

Loading the Data

Below is a listing of the SQL*Plus script EXAMPLOD. The INSERT statements in this script load (or reload) the database tables processed by the sample programs. To run the script, invoke SQL*Plus in the same Oracle account from which you ran EXAMPBLD, then issue the following command:

Sample 1. FOR Loop

The following example uses a simple FOR loop to insert ten rows into a database table. The values of a loop index, counter variable, and either of two character strings are inserted. Which string is inserted depends on the value of the loop index.

Input Table

Not applicable.

PL/SQL Block

-- available online in file SAMPLE1

DECLARE
x NUMBER := 100;
BEGIN
FOR i IN 1..10 LOOP
IF MOD(i,2) = 0 THEN -- i is even
INSERT INTO temp VALUES (i, x, 'i is even');
ELSE
INSERT INTO temp VALUES (i, x, 'i is odd');
END IF;
x := x + 100;
END LOOP;
COMMIT;
END;

Output Table

SQL> SELECT * FROM temp ORDER BY col1;

COL1 COL2 MESSAGE
----- ------- ---------
1 100 i is odd
2 200 i is even
3 300 i is odd
4 400 i is even
5 500 i is odd
6 600 i is even
7 700 i is odd
8 800 i is even
9 900 i is odd
10 1000 i is even
10 records selected.

Sample 2. Cursors

The next example uses a cursor to select the five highest paid employees from the emp table.

PL/SQL Block

-- available online in file SAMPLE2

DECLARE
CURSOR c1 is
SELECT ename, empno, sal FROM emp
ORDER BY sal DESC; -- start with highest paid employee
my_ename CHAR(10);
my_empno NUMBER(4);
my_sal NUMBER(7,2);
BEGIN
OPEN c1;
FOR i IN 1..5 LOOP
FETCH c1 INTO my_ename, my_empno, my_sal;
EXIT WHEN c1%NOTFOUND; /* in case the number requested */
/* is more than the total */
/* number of employees */
INSERT INTO temp VALUES (my_sal, my_empno, my_ename);
COMMIT;
END LOOP;
CLOSE c1;
END;

Output Table

Sample 3. Scoping

The following example illustrates block structure and scope rules. An outer block declares two variables named x and counter and loops four times. Inside this loop is a sub-block that also declares a variable
named x. The values inserted into the temp table show that the two x's are indeed different.

Sample 4. Batch Transaction Processing

In the next example the accounts table is modified according to instructions stored in the action table. Each row in the action table contains an account number, an action to be taken (I, U, or D for insert, update, or delete), an amount by which to update the account, and a time tag used to sequence the transactions.

On an insert, if the account already exists, an update is done instead. On an update, if the account does not exist, it is created by an insert. On a delete, if the row does not exist, no action is taken.

PL/SQL Block

-- available online in file SAMPLE4

DECLARE
CURSOR c1 IS
SELECT account_id, oper_type, new_value FROM action
ORDER BY time_tag
FOR UPDATE OF status;
BEGIN
FOR acct IN c1 LOOP -- process each row one at a time
acct.oper_type := upper(acct.oper_type);
/*----------------------------------------*/
/* Process an UPDATE. If the account to */
/* be updated doesn't exist, create a new */
/* account. */
/*----------------------------------------*/
IF acct.oper_type = 'U' THEN
UPDATE accounts SET bal = acct.new_value
WHERE account_id = acct.account_id;

Sample 6. Calling a Stored Procedure

This Pro*C program connects to Oracle, prompts the user for a department number, then calls a procedure named get_employees, which is stored in a package named personnel. The procedure declares three PL/SQL tables as OUT formal parameters, then fetches a batch of employee data into the PL/SQL tables. The matching actual parameters are host arrays. When the procedure finishes, it automatically assigns all row values in the PL/SQL tables to corresponding elements in the host arrays. The program calls the procedure repeatedly, displaying each batch of employee data, until no more data is found.