1.4 PL/SQL Language Fundamentals
This section summarizes the fundamental components of the PL/SQL language: characters, identifiers, literals, delimiters, use of comments and pragmas, and construction of statements and blocks.
1.4.1 PL/SQL Character Set
The PL/SQL language is constructed from letters, digits, symbols, and whitespace, as defined in the following table:
Characters are grouped together into four lexical units: identifiers, literals, delimiters, and comments.
Identifiers are names for PL/SQL objects such as constants, variables, exceptions, procedures, cursors, and reserved words. Identifiers have the following characteristics:
In addition, you must not use PL/SQL's reserved words as identifiers. For a list of those words, see the table in the final section in this book, Section 1.20.
If you enclose an identifier within double quotes, then all but the first of these rules are ignored. For example, the following declaration is valid:
DECLARE "1 ^abc" VARCHAR2(100); BEGIN IF "1 ^abc" IS NULL THEN ... END;
1.4.3 Boolean, Numeric, and String Literals
Literals are specific values not represented by identifiers. For example, TRUE, 3.14159, 6.63E-34, `Moby Dick', and NULL are all literals of type Boolean, number, or string. There are no complex datatype literals as they are internal representations. Unlike the rest of PL/SQL, literals are case-sensitive. To embed single quotes within a string literal, place two single quotes next to each other. See the following table for examples:
1.4.4 Datetime Interval Literals (Oracle9i)
The datetime interval datatypes are new with Oracle9i. These datatypes represent a chronological interval expressed in terms of either years and months or days, hours, minutes, seconds, and fractional seconds. Literals of these datatypes require the keyword INTERVAL followed by the literal and format string(s). The interval must go from a larger field to a smaller one, so YEAR TO MONTH is valid, but MONTH TO YEAR is not. See the following table for examples:
Delimiters are symbols with special meaning, such as := (assignment operator), || (concatenation operator), and ; (statement delimiter). The following table lists the PL/SQL delimiters:
Comments are sections of the code that exist to aid readability. The compiler ignores them.
A single-line comment begins with a double hyphen (?) and ends with a new line. The compiler ignores all characters between the ? and the new line.
A multiline comment begins with slash asterisk (/*) and ends with asterisk slash (*/). The /* */ comment delimiters can also be used for a single-line comment. The following block demonstrates both kinds of comments:
DECLARE -- Two dashes comment out only the physical line. /* Everything is a comment until the compiler encounters the following symbol */
You cannot embed multiline comments within a multiline comment, so be careful during development if you comment out portions of code that include comments. The following code demonstrates this issue:
DECLARE /* Everything is a comment until the compiler /* This comment inside another WON'T work!*/ encounters the following symbol. */ /* Everything is a comment until the compiler -- This comment inside another WILL work! encounters the following symbol. */
The PRAGMA keyword is used to give instructions to the compiler. There are four types of pragmas in PL/SQL:
A PL/SQL program is composed of one or more logical statements. A statement is terminated by a semicolon delimiter. The physical end-of-line marker in a PL/SQL program is ignored by the compiler, except to terminate a single-line comment (initiated by the ? symbol).
1.4.9 Block Structure
Each PL/SQL program is a block consisting of a standard set of elements, identified by keywords (see Figure 1-1). The block determines the scope of declared elements, and how exceptions are handled and propagated. A block can be anonymous or named. Named blocks include functions, procedures, packages, and triggers.
Figure 1-1. The PL/SQL block structure
Here is an example of an anonymous block:
DECLARE today DATE DEFAULT SYSDATE; BEGIN -- Display the date. DBMS_OUTPUT.PUT_LINE ('Today is ' || today); END;
Here is a named block that performs the same action:
CREATE OR REPLACE PROCEDURE show_the_date IS today DATE DEFAULT SYSDATE; BEGIN -- Display the date. DBMS_OUTPUT.PUT_LINE ('Today is ' || today); END show_the_date;
The following table summarizes the sections of a PL/SQL block: