Home > Articles > Data > Oracle

Fundamentals of Programming in PL/SQL

  • Print
  • + Share This
In Oracle PL/SQL by Example, Third Edition, authors Benjamin Rosenzweig and Elena Silvestrova cover Oracle 10G and all the fundamentals. In this sample chapter, you'll be introduced to the key words and their syntax. You'll also expand on simple block structure with an exploration of scope and nesting blocks.
This chapter is from the book

Chapter Objectives

In this Chapter, you will learn about:

  • PL/SQL Programming Fundamentals

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


Lab Objectives

After this Lab, you will be able to:

  • Make Use of PL/SQL Language Components

  • Make Use of PL/SQL Variables

  • Handle PL/SQL Reserved Words

  • Make Use of Identifiers in PL/SQL

  • Make Use of Anchored Data types

  • Declare and Initialize Variables

  • Understand 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. Additionally, 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 then a third for other foreign words. In order to speak any foreign language, you have to begin by learning these character sets. Then you progress to learn how to make words from these character sets. Finally, you learn the parts of speech and you can begin talking. 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, you can progress to learn the structure of the PL/SQL language.


The PL/SQL engine accepts four types of characters: letters, digits, symbols (*, +, -, =, etc.), and white space. When elements from one or more of these character types are joined together, they will create a lexical unit (these 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.


Although PL/SQL can be considered a language, don't try talking to your fellow programmers in PL/SQL. For example, at a dinner table of programmers, if you say, "BEGIN, LOOP FOR PEAS IN PLATE EXECUTE EAT PEAS, END LOOP, EXCEPTION WHEN BROCCOLI FOUND EXECUTE SEND TO PRESIDENT BUSH, END EAT PEAS," you may not be considered human. This type of language is reserved for Terminators and the like.


A language such as English contains different parts of speech. Each part of speech, such as a verb or 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 fall within one of the following five groups:

  1. Identifiers. Identifiers must begin with a letter and may be up to 30 characters long. See a PL/SQL manual for a more detailed list of restrictions; generally, if you stay with characters, numbers, and " ", and 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. 123, "Declaration of Independence," and FALSE are examples of literals.

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

See Appendix B, "PL/SQL Formatting Guide," for details on formatting.

In the following exercises, you will practice putting these units together.

  • + Share This
  • 🔖 Save To Your Account