Oracle PL/SQL by Example, Fifth Edition (2015)

In most languages, you have only two sets of characters: numbers and letters. Some languages, such as Hebrew or Tibetan, have specific characters for vowels that are not placed in line with consonants. Other languages, such as Japanese, have three character sets: one for words originally taken from the Chinese language, another set for native Japanese words, and a third for other foreign words. To speak any foreign language, you must begin by learning these character sets. You then progress to learn how to make words from these character sets. Finally, you learn the parts of speech and can begin speaking the language.

You can think of PL/SQL as being a more complex language because it has many character types and, additionally, many types of words or lexical units that are made from these character sets. Once you learn these building blocks, you can progress to learn the structure of the PL/SQL language.

PL/SQL Language Components

Character Types

The PL/SQL engine accepts four types of characters: letters, digits, symbols (*, +, –, =, and so on), and white space. When elements from one or more of these character types are joined together, they create a lexical unit (lexical units can be a combination of character types). The lexical units are the words of the PL/SQL language. First you need to learn the PL/SQL vocabulary, and then you will move on to the syntax, or grammar. Soon you can start talking in PL/SQL.

Lexical Units

A language such as English contains different parts of speech. Each part of speech, such as a verb or a noun, behaves in a different way and must be used according to specific rules. Likewise, a programming language has lexical units that are the building blocks of the language. PL/SQL lexical units are classified into one of the following five groups:

1. Identifiers. Identifiers must begin with a letter and may be up to 30 characters long. A PL/SQL manual provides a more detailed list of restrictions. Generally, if you stick with characters, numbers, and “ ”, and you avoid reserved words, you will not run into problems.

2. Reserved words. Reserved words are words that PL/SQL saves for its own use (e.g., BEGIN, END, SELECT).

3. Delimiters. These are characters that have special meaning to PL/SQL, such as arithmetic operators and quotation marks.

4. Literals. A literal is any value (character, numeric, or Boolean [true/false]) that is not an identifier. Examples of literals include 123, “Declaration of Independence,” and FALSE.

5. Comments. These can be either single-line comments (i.e., --) or multiline comments (i.e., /* */).

See Appendix A, “PL/SQL Formatting Guide,” for details on formatting.

The PL/SQL engine recognizes different characters as having different meanings and, therefore, processes them differently. PL/SQL is neither a pure mathematical language nor a spoken language, yet it contains elements of both. Letters will form various lexical units such as identifiers or keywords, mathematic symbols will form lexical units known as delimiters that will perform an operation, and other symbols, such as /*, indicate comments that should not be processed.

PL/SQL Variables

Variables may be used to hold a temporary value.

Syntax : <variable-name> <data type> [optional default ¬assignment]

Variables may also be known as identifiers. There are some restrictions that you need to be familiar with. Specifically, variables must begin with a letter and may be up to 30 characters long. Consider the following example, which contains a list of valid identifiers:

For Example ch02_1a.sql

DECLARE
v_student_id
v_last_name
V_LAST_NAME
apt_#

Note that the identifiers v_last_name and V_LAST_NAME are considered identical, because PL/SQL is not case sensitive.

Next, consider an example of illegal identifiers:

For Example

X+Y
1st_year
student ID

The identifier X+Y is illegal because it contains the “+” sign. This sign is reserved by PL/SQL to denote an addition operation, and it is referred to as a mathematical symbol. The identifier 1st_year is illegal because it starts with a number. Finally, the identifier student ID is illegal because it contains a space.

Now consider another example:

For Example

SET SERVEROUTPUT ON;
DECLARE
first&last_names VARCHAR2(30);
BEGIN
first&last_names := 'TEST NAME';
DBMS_OUTPUT.PUT_LINE(first&last_names);
END;

In this example, you declare a variable called first&last_names. Next, you assign a value to this variable and display this value on the screen.

When run, this example produces the following output in SQL*Plus:

Enter value for last_names: Ben
old 2: first&last_names VARCHAR2(30);
new 2: firstBen VARCHAR2(30);
Enter value for last_names: Ben
old 4: first&last_names := 'TEST NAME';
new 4: firstBen := 'TEST NAME';
Enter value for last_names: Ben
old 5: DBMS_OUTPUT.PUT_LINE(first&last_names);
new 5: DBMS_OUTPUT.PUT_LINE(firstBen);
TEST NAME
PL/SQL procedure successfully completed.

When you run this example in SQL Developer, you will get a slightly different response. Instead of seeing the line “enter value for Last Name,” you will see a dialog box that says “Enter Substitution Values” with a box for Last_Name. This is how SQL Developer works with variables.

Consider the output produced. Because an ampersand (&) is part of the name of the variable first&last_names, the portion of the variable is considered to be a substitution variable (you learned about substitution variables in Chapter 1). In other words, the portion of the variable name after the ampersand (last_names) is treated by the PL/SQL compiler as a substitution variable. As a result, you are prompted to enter the value for the last_names variable every time the compiler encounters it.

While this example does not produce any syntax errors (it would if you don’t give the same response to each prompt), the variable first&last_names is still an invalid identifier because the ampersand character is reserved for substitution variables. To avoid this problem, change the name of the variable from first&last_names to first_and_last_names. In other words, you should use an ampersand sign in the name of a variable only when you use it as a substitution variable in your program. It is also important to consider which type of program you are developing and in which you are running your PL/SQL statements. This would be true if the program (or PL/SQL block) will be executed by SQL*Plus. Later, when you write stored code, you would not use the ampersand but you will make use of parameters.