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

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

Chapter 2. PL/SQL Language Fundamentals

In this chapter, you will learn about

Image PL/SQL Programming Fundamentals

In the Introduction and Chapter 1, you learned about the difference between machine language and a programming language. You have also learned how PL/SQL differs from SQL and how the PL/SQL basic block structure works. This is similar to learning the history behind a foreign language and the context in which it is used. To use the PL/SQL language, you must learn the keywords, including what they mean and when and how to use them. First, you will encounter the different types of keywords. You will then encounter their full syntax. Finally, in this chapter, you will expand on simple block structure with an exploration of scope and nesting blocks.

Lab 2.1: PL/SQL Programming Fundamentals

After this lab, you will be able to

Image Describe PL/SQL Language Components

Image Explain the Use of PL/SQL Variables

Image Identify PL/SQL Reserved Words

Image Explain the Use of Identifiers in PL/SQL

Image Describe Anchored Data Types

Image Discuss the Scope of a Block, Nested Blocks, and Labels

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


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

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

first&last_names VARCHAR2(30);
first&last_names := 'TEST NAME';

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);
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.

By the Way

If you are using SQL Developer, you will need to go the menu view and click on “DBMS Output” prior to running this script. This will open a new window that shows only the output script. There will be three windows in the SQL Worksheet. The first window is where you put your PL/SQL or SQL statement. The second window is the script output; you will see both Oracle messages and the DBMS output here when you have SET SERVEROUTPUT ON. The third window, the DBMS output, shows only DBMS_OUTPUT that you have in the script. Click on the pencil with the eraser icon to clear the output windows.

For Example ch02_1b.sql

v_name VARCHAR2(30);
v_dob DATE;
v_us_citizen BOOLEAN;
DBMS_OUTPUT.PUT_LINE(v_name||'born on'||v_dob);

When this example is run in SQL*Plus or SQL Developer, you would see the output born on. The reason is that the variables v_name and v_dob have no values.

Three variables are declared. When each one is declared, its initial value is null. v_name is set as a VARCHAR2 with a length of 30, v_dob is set as a character type date, and v_us_citizen is set to BOOLEAN. Once the executable section begins, the variables have no value and, therefore, when DBMS_OUTPUT is told to print their values, it prints nothing.

This case can be seen when the variables are replaced as follows: Instead of v_name, use COALESCE(v_name, ‘No Name’); and instead of v_dob, use COALESCE (v_dob, ‘01-Jan-1999’). Then run the same block and you will get

No Name born on 01-Jan-1999

To make use of a variable, you must declare it in the declaration section of the PL/SQL block. You will have to give it a name and state its data type. You also have the option to give your variable an initial value. Note that if you do not assign an initial value to a variable, its value will be null. It is also possible to constrain the declaration to “not null,” in which case you must assign an initial value. Variables must first be declared; only then can they be referenced. PL/SQL does not allow forward references. You can set the variable to be a constant, which means it cannot change.

PL/SQL Reserved Words

Reserved words are ones that PL/SQL saves for its own use (e.g., BEGIN, END, and SELECT). You cannot use reserved words for names of variables, literals, or user-defined exceptions.

For Example

exception VARCHAR2(15);
exception := 'This is a test';

In this example, you declare a variable called exception. Next, you initialize this variable and display its value on the screen.

This example illustrates an invalid use of reserved words. To the PL/SQL compiler, “exception” is a reserved word that denotes the beginning of the exception-handling section. As a result, this word cannot be used to name a variable. This small piece of code will produce a long error message. The most important part of this error message is the following section:

exception VARCHAR2(15);

ORA-06550: line 2, column 4:
PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:

begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior...../

Here is a question you should ask yourself: If you did not know that the word “exception” is a reserved word, do you think you would attempt to debug the preceding script after looking at this error message?

Identifiers in PL/SQL

Take a look at the use of identifiers in the following example:

For Example

v_var1 VARCHAR2(20);
v_var2 VARCHAR2(6);
v_var3 NUMBER(5,3);
v_var1 := 'string literal';
v_var2 := '12.345';
v_var3 := 12.345;
DBMS_OUTPUT.PUT_LINE('v_var1: '||v_var1);
DBMS_OUTPUT.PUT_LINE('v_var2: '||v_var2);
DBMS_OUTPUT.PUT_LINE('v_var3: '||v_var3);

In this example, you declare and initialize three variables. The values that you assign to them are literals. The first two values, 'string literal' and '12.345' are string literals because they are enclosed by single quotes. The third value, 12.345, is a numeric literal. When run, the example produces the following output:

v_var1: string literal
v_var2: 12.345
v_var3: 12.345
PL/SQL procedure successfully completed.

Consider another example that uses numeric literals.

For Example

v_var1 NUMBER(2) := 123;
v_var2 NUMBER(3) := 123;
v_var3 NUMBER(5,3) := 123456.123;
DBMS_OUTPUT.PUT_LINE('v_var1: '||v_var1);
DBMS_OUTPUT.PUT_LINE('v_var2: '||v_var2);
DBMS_OUTPUT.PUT_LINE('v_var3: '||v_var3);

In this example, you declare and initialize three numeric variables. The first declaration and initialization (v_var1 NUMBER(2) := 123) causes an error because the value 123 exceeds the specified precision. The second variable declaration and initialization (v_var2 NUMBER(3) := 123) does not cause any errors because the value 123 corresponds to the specified precision. The last declaration and initialization (v_var3 NUMBER(5,3) := 123456.123) causes an error because the value 123456.123 exceeds the specified precision. As a result, this example produces the following output:

ORA-06512: at line 2 ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 2

Anchored Data Types

The data type that you assign to a variable can be based on a database object. This assignment is called an anchored declaration since the variable’s data type depends on that of the underlying object. It is wise to make use of anchored data types when possible so that you do not have to update your PL/SQL when the data types of base objects change.

Syntax: <variable_name> <type attribute>%TYPE

The type is a direct reference to a database column.

For Example ch02_2a.sql

v_name student.first_name%TYPE;
v_grade grade.numeric_grade%TYPE;
DBMS_OUTPUT.PUT_LINE(NVL(v_name, 'No Name ')||
' has grade of '||NVL(v_grade, 0));

In the preceding example, the variable v_name was declared with the identical data type as the column first_name from the database table STUDENT: varchar2(25). Additionally, the variable v_grade was declared with the identical data type as the column grade_numericon the grade database table: number NUMBER(3). Each has a value of null. The results of this example would be as follows:

No Name has grade of 0
PL/SQL procedure successfully completed.

Most Common Data Types

As a programmer, it is important to know the major data types that you can use in a programming language. This will determine the various options you have to solve a programmatic problem. Also, you need to keep in mind that some functions work only on certain types of data types. The following is a list of the major data types in the Oracle platform that you can use in PL/SQL.


Image Stores variable-length character data.

Image Takes a required parameter that specifies a maximum length up to 32,767 bytes, with the Extended Data Types parameter enabled. Otherwise, the maximum length is 4000 bytes.

Image Does not use a constant or variable to specify the maximum length; an integer literal must be used.


Image Stores fixed-length (blank-padded if necessary) character data.

Image Takes an optional parameter that specifies a maximum length up to 32,767 bytes.

Image Does not use a constant or variable to specify the maximum length; an integer literal must be used. If maximum length is not specified, it defaults to 1.

Image The maximum width of a CHAR database column is 2000 bytes; the default is 1 byte.

NUMBER[(precision, scale)]

Image Stores fixed or floating-point numbers of virtually any size.

Image The precision is the total number of digits.

Image The scale determines where rounding occurs.

Image It is possible to specify a precision and omit the scale, in which case the scale is 0 and only integers are allowed.

Image Constants or variables cannot be used to specify a precision and scale; integer literals must be used.

Image The maximum precision of a NUMBER value is 38 decimal digits.

Image The scale can range from 0 to 127.

Image For instance, a scale of 2 rounds to the nearest hundredth (3.456 becomes 3.46).

Image The scale can be negative, which causes rounding to the left of the decimal point. For example, a scale of –3 rounds to the nearest thousandth (3456 becomes 3000). A scale of zero rounds to the nearest whole number. If you do not specify the scale, it defaults to zero.


Image Stores fixed-length date values.

Image Valid dates for DATE variables include January 1, 4712 BC, to December 31, AD 9999.

Image When stored in a database column, date values include the time of day in seconds since midnight. The date portion defaults to the first day of the current month; the time portion defaults to midnight.

Image Dates are actually stored in binary format and will be displayed according to the default format.


Image Stores variable-length character strings.

Image The LONG data type is like the VARCHAR2 data type, except that the maximum length of a LONG value is 2 gigabytes.

Image You cannot select a value longer than 4000 bytes from a LONG column into a LONG variable.

Image LONG columns can store text, arrays of characters, or even short documents. You can reference LONG columns in UPDATE, INSERT, and (most) SELECT statements, but not in expressions, SQL function calls, or certain SQL clauses, such as WHERE, GROUP BY, andCONNECT BY.


Image Stores raw binary data of variable length up to 2 gigabytes.

LOB (Large Object)

Image There are four types of LOBs: BLOB, CLOB, NCLOB, and BFILE. These can store binary objects, such as image or video files, up to 4 gigabytes in length.

Image A BFILE is a large binary file stored outside the database. The maximum size is 4 gigabytes.


Image Data type for storing variable length binary data.

Image Maximum size is 32,767 bytes, with the Extended Data Types parameter enabled. Otherwise, the maximum length is 2000 bytes.

Declare and Initialize Variables

In PL/SQL, variables must be declared before they can be referenced. This is done in the initial declarative section of a PL/SQL block. Recall that each declaration must be terminated with a semicolon. Variables can be assigned using the assignment operator “:=”. If you declare a variable to be a constant, it will retain the same value throughout the block; to do this, you must give it a value at declaration.

Type the following statements into a text file and run the script from a SQL*Plus or SQL Developer session.

v_cookies_amt NUMBER := 2;
v_calories_per_cookie CONSTANT NUMBER := 300;
DBMS_OUTPUT.PUT_LINE('I ate ' || v_cookies_amt ||
' cookies with ' || v_cookies_amt *
v_calories_per_cookie || ' calories.');
v_cookies_amt := 3;
DBMS_OUTPUT.PUT_LINE('I really ate ' ||
|| ' cookies with ' || v_cookies_amt *
v_calories_per_cookie || ' calories.');
v_cookies_amt := v_cookies_amt + 5;
DBMS_OUTPUT.PUT_LINE('The truth is, I actually ate '
|| v_cookies_amt || ' cookies with ' ||
v_cookies_amt * v_calories_per_cookie
|| ' calories.');

The output of running the preceding script will be as follows:

I ate 2 cookies with 600 calories.
I really ate 3 cookies with 900 calories.
The truth is, I actually ate 8 cookies with
2400 calories.
PL/SQL procedure successfully completed.

Initially the variable v_cookies_amt is declared to be a NUMBER with the value of 2, and the variable v_calories_per_cookie is declared to be a CONSTANT NUMBER with a value of 300 (since it is declared to be a CONSTANT, its value will not change). In the course of the procedure, the value of v_cookies_amt is set to be 3, and finally it is set to be its current value, 3 plus 5, thus becoming 8.

For Example ch02_3a.sql

v_lname VARCHAR2(30);
v_regdate DATE;
v_pctincr CONSTANT NUMBER(4,2) := 1.50;
v_counter NUMBER := 0;
v_new_cost course.cost%TYPE;
v_counter := ((v_counter + 5)*2) / 2;
v_new_cost := (v_new_cost * v_counter)/4;
v_counter := COALESCE(v_counter, 0) + 1;
v_new_cost := 800 * v_pctincr;

PL/SQL variables are held together with expressions and operators. An expression is a sequence of variables and literals, separated by operators. These expressions are then used to manipulate data, perform calculations, and compare data.

Expressions are composed of a combination of operands and operators. An operand is an argument to the operator; it can be a variable, a constant, a function call. An operator is what specifies the action (+, **, /, OR, and so on).

You can use parentheses to control the order in which Oracle evaluates an expression. Initially the variable v_lname is declared as a data type VARCHAR2 with a length of 30 and a value of null. The variable v_regdate is declared as a date data type with a value of null. The variablev_pctincr is declared as a CONSTANT NUMBER with a length of 4, a precision of 2, and a value of 1.15. The variable v_counter is declared as a NUMBER with a value of 0. The variable v_YorN is declared as a variable of BOOLEAN data type and a value of TRUE.

Once the executable section is complete, the variable v_counter will be changed from null to 1. The value of v_new_cost will change from null to 1200 (800 * 1.50).

A common way to find out the value of a variable at different points in a block is to add DBMS_OUTPUT.PUT_LINE(v_variable_name); statements throughout the block.

The value of the variable v_counter will then change from 1 to 6, which is ((1 + 5) *2))/2, and the value of new_cost will go from 1200 to 1800, which is (800 * 6)/4. The output from running this procedure will be

PL/SQL procedure successfully completed.

Operators (Delimiters): The Separators in an Expression

As a programmer, it is important to know the operators that you can use in a programming language. This will determine the various options you have to solve a programmatic problem. The following is a list of the operators you can use in PL/SQL.

Arithmetic ( ** , * , / , + , -)
Comparison( =, <>, !=, <, >, <=, >=, LIKE, IN, BETWEEN, IS NULL, IS
Logical (AND, OR, NOT)
String ( ||, LIKE )
Operator Precedence
** , NOT
+, - (arithmetic identity and negation), *, /, , - , ||, =, <>, != ,
<= , >= , < , > , LIKE, BETWEEN, IN, IS NULL
AND—logical conjunction
OR—logical inclusion

Scope of a Block, Nested Blocks, and Labels

When making use of variables in a PL/SQL block, it is important to understand their scope. This will allow you to understand how and when you can make use of variables. It will also help you debug the programs you write. The opening section of your PL/SQL block contains the declaration section—that is, the section where you declare the variables that the block will use.

Scope of a Variable

The scope, or existence, of structures defined in the declaration section is local to that block. The block also provides the scope for exceptions that are declared and raised. Exceptions are covered in more detail in Chapters 8, 9, and 10.

The scope of a variable is the portion of the program in which the variable can be accessed, or where the variable is visible. It usually extends from the moment of declaration until the end of the block in which the variable was declared. The visibility of a variable is the part of the program where the variable can be accessed.

BEGIN -- outer block
BEGIN -- inner block
END; -- end of inner block
END; -- end of outer block

Labels and Nested Blocks

Labels can be added to a block to improve readability and to qualify the names of elements that exist under the same name in nested blocks. The name of the block must precede the first line of executable code (either BEGIN or DECLARE) as follows:

For Example ch02_4a.sql

set serveroutput on
<< find_stu_num >>
find_stu_num has been executed.');
END find_stu_num;

The label optionally appears after END. For commenting purposes, you may alternatively use either -- or /* ... */. Blocks can be nested in the main section or in an exception handler. A nested block is a block that is placed fully within another block. Use of this type of nesting affects the scope and visibility of variables. The scope of a variable in a nested block begins when memory is allocated for the variable and extends from the moment of declaration until the END of the nested block from which it was declared. The visibility of a variable is the part of the program where the variable can be accessed.

For Example ch02_4b.sql

<< outer_block >>
v_test NUMBER := 123;
('Outer Block, v_test: '||v_test);
<< inner_block >>
v_test NUMBER := 456;
('Inner Block, v_test: '||v_test);
('Inner Block, outer_block.v_test: '||
END inner_block;
END outer_block;

This example produces the following output:

Outer Block, v_test: 123
Inner Block, v_test: 456
Inner Block, outer_block.v_test: 123

For Example ch02_5a.sql

e_show_exception_scope EXCEPTION;
v_student_id NUMBER := 123;
DBMS_OUTPUT.PUT_LINE('outer student id is '
v_student_id VARCHAR2(8) := 125;
DBMS_OUTPUT.PUT_LINE('inner student id is '
RAISE e_show_exception_scope;
WHEN e_show_exception_scope
DBMS_OUTPUT.PUT_LINE('When am I displayed?');
DBMS_OUTPUT.PUT_LINE('outer student id is '

This example produces the following output:

outer student id is 123
inner student id is 125
When am I displayed?
outer student id is 123

The flow of logic in this block is as follows: The variable e_Show_Exception_Scope is declared as an exception type in the declaration section of the block. There is also a declaration of the variable called v_student_id of data type NUMBER that is initialized to the number 123. This variable has a scope of the entire block, but it is visible only outside the inner block. Once the inner block begins, another variable, named v_student_id, is declared. This time it is of data type VARCHAR2(8) and is initialized to 125. This variable will have a scope and visibility only within the inner block. The use of DBMS_OUTPUT helps to show which variable is visible. The inner block raises the exception e_Show_Exception_Scope; as a consequence, the focus will move out of the execution section and into the exception section. The focus will look for an exception named e_Show_Exception_Scope. Since the inner block has no exception with this name, the focus will move to the outer block’s exception section, where it finds the exception. The inner variable v_student_id is now out of scope and visibility. The outer variablev_student_id (which has always been in scope) now regains visibility. Because the exception has an IF/THEN construct, it will execute the DBMS_OUTPUT call.

This example illustrates a simple use of nested blocks. Later in the book you will see more complex examples. After you learn about exception handling in Chapters 8, 9, and 10, you will see that there is greater opportunity to make use of nested blocks.


In this chapter, you learned the fundamentals of the PL/SQL language. The first section introduced the basic components of the PL/SQL language, which collectively allow you to construct simple PL/SQL code. Then you learned about variables, which allow you to store values that may change each time the program is run. You also learned about the PL/SQL keywords; these terms have specific meanings and cannot be used as names. Identifiers and anchored data types were covered to help you understand how data type values are used. The chapter ended by explaining the basics of a PL/SQL block, as well as how to nest blocks and make use of labels to organize your code.

By the Way

The companion website ( provides additional exercises and suggested answers for this chapter, with discussion related to how those answers resulted. The main purpose of these exercises is to help you test the depth of your understanding by utilizing all of the skills that you have acquired throughout this chapter.