Working with PL/SQL Datatypes
In any programming language, you need datatypes to be able to manipulate data quickly. PL/SQL provides a variety of datatypes to help you store values.In this module, we will first take a look at the composition of a PL/SQL block. Then, you will learn about the rules for using a nested block. You will also learn about identifiers and literals that will help you build program logic. Next, we will review the different conversion functions that let you convert datatypes. You will also learn the use of operators in PL/SQL.
This will provide you with the knowledge necessary to help build and execute your first PL/SQL block. In this process, we will also review the different ways to comment code.
By the end of this module, you will know how to:
- Describe the basic structure of a PL/SQL block
- Define the scope of variables within nested blocks
- Place identifiers and use literals within a PL/SQL block
- Add comments to a PL/SQL block
- Identify implicit and explicit datatype conversions within PL/SQL
- Use operators within PL/SQL
In the next lesson, the basic structure of a PL/SQL block we will bed described.
Main Features of PL/SQL
PL/SQL combines the data-manipulating power of SQL with the processing power of procedural languages.
When you can solve a problem with SQL, you can issue SQL statements from your PL/SQL program, without learning new APIs.
Like other procedural programming languages, PL/SQL lets you declare constants and variables, control program flow, define subprograms, and trap runtime errors. You can break complex problems into easily understandable subprograms, which you can reuse in multiple applications.
Overview of PL/SQL Datatypes
Whenever you declare a variable or a constant, you must assign it a datatype. PL/SQL is, with very few exceptions, a statically typed programming language (see the following sidebar for a definition). PL/SQL offers a comprehensive set of predefined scalar and composite datatypes, and you can create your own user-defined types (also known as abstract datatypes). Many of the PL/SQL datatypes are not supported by database columns, such as Boolean and NATURAL, but within PL/SQL code, these datatypes are quite useful.
Virtually all of these predefined datatypes are defined in the PL/SQL STANDARD package. Here, for example, are the statements that define the Boolean datatype and two of the numeric datatypes:
Oracle DBMS Packages
create or replace package STANDARD is
type BOOLEAN is (FALSE, TRUE);
type NUMBER is NUMBER_BASE;
subtype INTEGER is NUMBER(38,);
When it comes to datatypes, PL/SQL supports the standard types. This section provides a quick overview of the various predefined datatypes
What Does Static Typing Mean?
A programming language uses static typing, also called strong typing, if type checking is performed at compile-time as opposed to at runtime. Some programming languages that use static typing include PL/SQL, Ada, C, and Pascal.
Static typing can find type errors at compile time, which can increase the reliability of the program. Static typing has the additional benefit of compiling to faster executing programs. An optimizing compiler that knows the exact datatypes in use can find assembler shortcuts more easily and produce more highly optimized machine code. Dynamic typing also has some advantages: for example, metaclasses and introspection are easier to implement with dynamic typing.