Previous | Table of Contents | Next

Page 198

An inner block may reference an object in an enclosing block through the use of labels. This is a poor programming practice and you should instead use a different variable name, or pass the variable in on the stack to a subroutine. However, the language does support labels, so if you feel compelled to use this feature, follow these steps:

  1. Insert a label at the start of the outer block.
  2. Use dot notation within the inner block to refer to variables defined by the outer block.
    The label is the qualifier needed to reference the outer variable of the same name as an inner one.

Listing 10.9 illustrates the correct and incorrect use of qualifying labels.

Listing 10.9 badlabel.sql—Use and Abuse of Labels to Differentiate Blocks

<<OUTER_BLOCK>>    -- this label names the outer block
DECLARE
  x NUMBER;  -- available to both inner and next blocks
  y NUMBER;
BEGIN
  <<INNER_BLOCK>>  -- might as well label the inner one too
  DECLARE
    x NUMBER;  -- only available to this block
  BEGIN  -- inner block
    x := OUTER_BLOCK.x;  -- qualified reference to outer block variable
    y := 0;  -- an unqualified reference to outer block variable
  END INNER_BLOCK;
  <<NEXT_BLOCK>>
  DECLARE
    x NUMBER;
  BEGIN  -- next block
    x := INNER_BLOCK.x;  -- THIS IS ILLEGAL!!
    x := OUTER_BLOCK.x;  -- this is OK
  END NEXT_BLOCK;
  x := INNER_BLOCK.x;  -- THIS IS ALSO ILLEGAL!!
END OUTER_BLOCK;

You get the following errors:

ERROR at line 1:
ORA-06550: line 17, column 22:
PLS-00219: label `INNER_BLOCK' reference is out of scope
ORA-06550: line 17, column 5:
PL/SQL: Statement ignored
ORA-06550: line 20, column 20:
PLS-00219: label `INNER_BLOCK' reference is out of scope
ORA-06550: line 20, column 3:
PL/SQL: Statement ignored

Note that you cannot go in the other direction; namely, you cannot try to refer to an inner block's variable in the outer block. Why not? Because it doesn't exist. The variable in the inner

Page 199

block isn't created until you're actually within the inner block, and is immediately deallocated upon exiting the inner block, so that there is no opportunity for the outer block to refer to it.

Likewise, a subroutine nested inside of a subprogram uses the name of the subprogram with dot notation to refer to a variable defined at the level of the enclosing subprogram. But it is always a better coding practice to pass the needed value in on the parameter list, or simply do not reuse variable names in inner blocks. The fewer informal or unusual references, the easier it'll be to maintain your code.

It is OK to have nested blocks without labeling them. You can even create new variables in the sub-blocks. Nested blocks are useful when, for example, you're performing an operation that might generate an exception and you need to localize the exception handler to within the enclosing block.

You've already seen a few identifiers—the variables, data types, packages, and procedures. Now you need to become aware of a few general rules about naming them.

IdentifiersIdentifiers are lexemes representing programming items, such as variables, constants, user-defined data types, cursors, exceptions, database tables, columns, procedures, functions and packages. An identifier must begin with a letter and may contain up to 30 letters, numbers, underscores, dollar signs ($), or pound signs (#) only.

Valid Identifiers Invalid Identifiers
PI 3D-array
length_of_string _function_header
char$ var name
RQ$Get$Segment area/height
local##time $HOME

Identifiers cannot contain embedded blanks, hyphens, or slashes, but you can use double quotes around identifiers to distinguish them from other lexemes, such as when a table's column name is the same as a PL/SQL reserved word. For example, EXCEPTION is a PL/SQL reserved word, yet it's not reserved in SQL and therefore might have been used as a column name. In order to properly reference such a column, use double quotes, as in:

SELECT "EXCEPTION" INTO exc FROM prog_errors;

Since columns are stored in uppercase, the quoted identifier has to be uppercase. You might also use double quotes to include blanks or other delimiters, such as: SELECT cr_msg "*** Credit Message ***" FROM messages;

although this is more typically used in SQL*Plus for column headings.

Commenting Your CodeGood programmers comment their code heavily. The more time they have when writing code, the better their comments tend to be.

You can use comments when first laying out a complex PL/SQL program to indicate various sections. In fact, you can often take your comments directly from the program specifications.

Page 200

Then you can go back to each section and flesh it out with the actual code. For example, use a module header comment that contains some basic information, then organize your declarations by scope and type.

/*
  Program Name: c2f
  Module Name : c2f.sql
 Written By  : Daniel J.. Clamage
  Description : .This module converts Celsius to Fahrenheit.
 Modification: V.001 04-OCT-1997 - djc - Initial release.
*/
...
  -- public global constant variables (shared between modules)
  prog_version CONSTANT VARCHAR2 (25) := `V.001 04-OCT-1997 - djc';
  prog_name CONSTANT VARCHAR2(30) := `c2f';
...
 -- public global record types needed
...
  -- public global variables
...
  -- public global cursors needed
...

When you compile a saved script with those multiline comments, SQL*Plus echoes them back as a DOC block:

SQL> @d:\plsql\library\c2f
DOC>  Program Name: c2f
...
DOC>  Modification: V.001 04-OCT-1997 - djc - Initial release.
DOC>*/

This is convenient when you're compiling a whole set of modules, and you're spooling the outcome to a file. It's recommended that you limit the use of multiline comments in the comment header block of a module because they may be echoed during compilation, causing some confusion. If the first multiline comment delimiter is not placed on its own line, then the first comment line doesn't even show up. You can use single-line comments everywhere else.

Declaring Variables

In general, there are two forms of variables: scalar and composite. Scalar variables permit only a single value for the variable. Composite variables can contain multiple values of either identical or single data types, depending on the kind of composite variable. You declare only one variable per statement.

Table 10.3 shows a list of scalar data types supported by PL/SQL and their corresponding database types (if any).

Previous | Table of Contents | Next