The GOTO Statement

Yes, PL/SQL allows you to use the potentially dangerous GOTO statement. Of course, to use GOTO, you must provide a label to which control is transferred. In PL/SQL, a statement label is defined in this way:

<<my_label>>

Listing 9.7 provides an example of how a GOTO statement and a label can be used as an alternative to the EXIT statement.

Listing 9.7. Using the GOTO statement.

SQL> declare
  2
   2  i  positive := 1;
    3  max_loops constant positive := 100;
  4
  4  begin
  5
  5  i := 1;
  6
  6  loop
  7
  7    i := i + 1;
  8    if i > max_loops then
  9       goto more_processing;
 10    end if;
 11
 11  end loop;
 12
 12  <<more_processing>>
 13  i := 1;
 14
 14  end;
 15  /
PL/SQL procedure successfully completed.

The NULL Statement

For certain situations, you should indicate to PL/SQL that no action is to be taken. For instance, in an exception handler, you might not want to do anything when a particular exception occurs. For the sake of clarity, use the NULL statement in an IF-THEN-ELSIF to indicate that no action is to be taken for a particular ELSIF clause.

Unfortunately, Oracle chose to name this statement NULL, even though it has nothing to do with the null value. Listing 9.8 demonstrates how the NULL statement can be used.

Listing 9.8. Using the NULL statement.

if (mod(i,10) = 0) then
  i := i + 1;
else
    NULL;
end if;

The Assignment Statement

As you've already seen, PL/SQL uses := to assign a value to a PL/SQL variable. You can define a constant's value or a variable's default value in the declaration section. One point is worth noting: You can't assign a NULL to a variable that was declared using the %TYPE notation when the referenced column is defined as NOT NULL.

Including Comments in a PL/SQL Subprogram

PL/SQL gives you two ways to document your code. First, you can add a comment on any line by placing a -- followed by the comment, as shown:

Depot_Est_Row.Technician := Last_Tech_Name;
-- Assign the name of the last technician involved

You can also add comments in the C style—by enclosing them within /* and */. This method is best suited for including multiline comments, as shown in Listing 9.9.

Listing 9.9. Commenting PL/SQL code.

j := j + 1;
/* The next section inserts a row into the Utility_Audit table
   to record the name of the current Oracle user and the
   current date and time (SYSDATE).
*/
insert into Utility_Audit
...

Using SQL Statements in a PL/SQL Program

You can use SQL statements in an anonymous block, procedure, or function as they are used in SQL*Plus, with a few differences. As with other PL/SQL statements, each SQL statement must be terminated by a semicolon. However, PL/SQL enables you to reference declared variables in an SQL statement. Listing 9.10 provides an example of how declared variables are referenced in an SQL statement.

Listing 9.10. Referencing variables in an SQL statement.

DECLARE
max_records CONSTANT int := 100;
i           int := 1;
BEGIN
FOR i IN 1..max_records LOOP
  if (mod(i,10) = 0) then
    INSERT INTO test_table
           (record_number, current_date)
    VALUES
           (i, SYSDATE);
  else
    NULL;
  end if;
END LOOP;
COMMIT;
END;
/

In this example the INSERT statement uses the numeric variable i and the pseudocolumn SYSDATE to place values in the Record_Number and Current_Date columns.

PL/SQL and the SELECT Statement

In a PL/SQL subprogram, the SELECT statement employs another clause—INTO—to identify the PL/SQL variables that should receive column values. Place the INTO clause between the select list and the FROM clause. Listing 9.11 contains an example of an anonymous PL/SQL block that contains a SELECT statement.

Listing 9.11. Using a SELECT statement in a PL/SQL block.

SQL> set serveroutput on
SQL>
SQL> declare
  2
   2  Average_Body_Temp     Patient.Body_Temp_Deg_F%type;
  3
  3  begin
  4
   4  dbms_output.enable;
  5
   5  select avg(Body_Temp_Deg_F)
  6    into Average_Body_Temp
  7    from Patient;
  8
   8  dbms_output.put_line(`Average body temp in Deg. F: ` ||
     to_char(Average_Body_Temp,'999.99'));
  9
  9  end;
 10  /
Average body temp in Deg. F:   99.80
PL/SQL procedure successfully completed.

PL/SQL Subprograms

PL/SQL also supports the use of subprograms—named procedures and functions. A
PL/SQL procedure performs some action and can accept optional parameters. A PL/SQL function returns a value of some specified datatype and can also accept optional parameters.

Using Sub-Blocks

PL/SQL enables you to include sub-blocks within a block. For instance, Listing 9.12 displays an anonymous block that contains another anonymous sub-block that has its own declaration section.

Listing 9.12. Example of a sub-block.

SQL> declare
  2
  2  max_i       constant int := 100;
  3  i           int := 1;
  4  rec_number  int;
  5
  5  begin
  6
  6  for i in 1..max_i loop
  7
  7    if mod(i,5) = 0 then
  8      rec_number := 5;
  9    elsif mod(i,7) = 0 then
 10      rec_number := 7;
 11    else
 12      rec_number := i;
 13    end if;
 14
 14    insert into test_table
 15      (record_number, current_date)
 16      values
 17      (rec_number, sysdate);
 18
 18  -- Here is a sub-block:
 19
 19    declare
 20    max_j constant int := 20;
 21    j int := 1;
 22
 22    begin
 23
 23      for j in 1..max_j loop
 24
 24        rec_number := rec_number * j;
 25
 25        insert into test_table



                                                         continues

Listing 9.12. continued

 26          (record_number, current_date)
 27          values
 28          (rec_number, sysdate);
 29
 29      end loop;
 30
 30    end;
 31
 31    commit;
 32  end loop;
 33
 33  end;
 34  /
PL/SQL procedure successfully completed.
SQL> select count(*) from test_table;
 COUNT(*)
---------

     2100

The first line of Listing 9.12 begins the declaration section of the anonymous block. On line 5, the execution section of the main block begins. The declaration section of the sub-block begins at line 19; line 22 marks the beginning of the execution section of the sub-block.

Declaring a Procedure

In addition to anonymous blocks, you can also declare PL/SQL procedures and functions.

The syntax for declaring a procedure is

PROCEDURE procedure-name [(argument1 ... [, argumentN) ] IS
[local-variable-declarations]
BEGIN
executable-section
[exception-section]