home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  


1.4 PL/SQL Language Fundamentals

1.4.1 The PL/SQL Character Set

The PL/SQL language is constructed from letters, digits, symbols, and whitespace, as defined in the following table.

Type

Characters

Letters

A-Z, a-z

Digits

0-9

Symbols

~!@#$%&*()_-+=|[ ]{ }:;"'< >?/

Whitespace

space, tab, carriage return

Characters are grouped together into the four lexical units: identifiers, literals, delimiters, and comments.

1.4.1.1 Identifiers

Identifiers are names for PL/SQL objects such as constants, variables, exceptions, procedures, cursors, and reserved words. Identifiers:

  • Can be up to 30 characters in length

  • Cannot include whitespace (space, tab, carriage return)

  • Must start with a letter

  • Can include a dollar sign ($), an underscore ( _ ), and a pound sign (#)

  • Are not case-sensitive

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.1.2 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 date or 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.

Literal

Actual Value

'That''s Entertainment!'

That's Entertainment!

'"The Raven"'

"The Raven"

'TZ="CDT6CST"'

TZ='CDT6CST'

''''

'

'''hello world'''

'hello world'

''''''

''

1.4.1.3 Delimiters

Delimiters are symbols with special meaning, such as := (assignment operator), || (concatenation operator), and ; (statement delimiter). The following table lists delimiters.

Delimiter

Description

;

Statement terminator

+

Addition operator

-

Subtraction operator

*

Multiplication operator

/

Division operator

**

Exponentiation operator

||

Concatenation operator

:=

Assignment operator

=

Equality operator

<> and !=

Inequality operators

^= and ~=

Inequality operators

<

"Less than" operator

<=

"Less than or equal to" operator

>

"Greater than" operator

>=

"Greater than or equal to" operator

( and )

Expression or list delimiters

<< and >>

Label delimiters

,

Item separator

`

Literal delimiter

"

Quoted literal delimiter

:

Host variable indicator

%

Attribute indicator

.

Component indicator (as in record.field or package.element)

@

Remote database indicator (database link)

=>

Association operator (named notation)

..

Range operator (used in the FOR loop)

--

Single-line comment indicator

/* and */

Multiline comment delimiters

1.4.1.4 Comments

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.

Multiline comments begin with slash asterisk (/*) and end with asterisk slash (*/). The /* */ comment delimiters can also be used on 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 care needs to be exercised during development if you comment out portions of code that include comments. The following code demonstrates:

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. */

1.4.1.5 Pragmas

The PRAGMA keyword is used to give instructions to the compiler. There are four types of pragmas in PL/SQL:

EXCEPTION_INIT

Tells the compiler to associate the specified error number with an identifier that has been declared an EXCEPTION in your current program or an accessible package. See the Section 1.10, "Exception Handling " section for more information on this pragma.

RESTRICT_REFERENCES

Tells the compiler the purity level of a packaged program. The purity level is the degree to which a program does not read/write database tables and/or package variables. See the Section 1.15, "Calling PL/SQL Functions in SQL " section for more information on this pragma.

SERIALLY_REUSABLE

Tells the runtime engine that package data should not persist between references. This is used to reduce per-user memory requirements when the package data is only needed for the duration of the call and not for the duration of the session. See the Section 1.14, "Packages " section for more information on this pragma.

AUTONOMOUS_TRANSACTION ( Oracle8 i )

Tells the compiler that the function, procedure, top-level anonymous PL/SQL block, object method, or database trigger executes in its own transaction space. See the Section 1.8, "Database Interaction and Cursors " section for more information on this pragma.

1.4.1.6 Statements

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.1.7 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. Here is an example of an anonymous block:

DECLARE
   whoops NUMBER DEFAULT 99;
BEGIN
   -- Display a two-digit year number. 
   DBMS_OUTPUT.PUT_LINE ('What century?  ' || whoops);
END;

Here is a named block that performs the same action:

CREATE OR REPLACE PROCEDURE show_the_problem
IS
   whoops NUMBER DEFAULT 99;
BEGIN
   -- Display a two-digit year number. 
   DBMS_OUTPUT.PUT_LINE ('What century?  ' || whoops);
END show_the_problem; 

Figure 1.1: The PL/SQL block structure

Figure 1.1

The following table describes the sections of a PL/SQL block:

Section

Description

Header

Required for named blocks. Specifies the way the program is called by outer PL/SQL blocks. Anonymous blocks do not have a header. They start with the DECLARE keyword if there is a declaration section, or with the BEGIN keyword if there are no declarations.

Declaration

Optional; declares variables, cursors, TYPEs, and local programs that are used in the block's execution and exception sections.

Execution

Optional in package and type specifications; contains statements that are executed when the block is run.

Exception

Optional; describes error handling behavior for exceptions raised in the executable section.


Previous: 1.3 Conventions Oracle PL/SQL Language Pocket Reference Next: 1.5 Variables and Program Data
1.3 Conventions   1.5 Variables and Program Data

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference