PL/SQL Programming   «Prev  Next»

Lesson 4Variable datatypes
ObjectiveIdentify the Different Datatypes within PL/SQL

Identify the Different Datatypes within PL/SQL

Every variable has a datatype, which specifies a storage format, constraints, and a valid range of values. PL/SQL provides a variety of datatypes: SCALAR, COMPOSITE, REFERENCE, and LOB. You define a variable for a particular datatype to be able to manipulate the data. Review the following series of diagrams to identify the different datatypes available within PL/SQL.

1) Scalar, Composite, Reference, and LOB Types in PL/SQL
1) Scalar Type, 2) Composite, 3) Reference Type, and 4) LOB Types in PL/SQL

2) A scalar datatype has no internal components and holds a single value.
2) A scalar datatype has no internal components and holds a single value

3) A composite datatype has internal components that can be manipulated individually
3) A composite datatype has internal components that can be manipulated individually. PL/SQL TABLE and RECORD fall under the category of a composite datatype.

4) A  reference datatype variable holds a value, called pointer, that designates other program items.
4) A reference datatype variable holds a value, called pointer, that designates other program items. Variables that are declared with the %TYPE and %ROWTYPE would be good examples of reference datatype variables.

5) LOB large objects datatype holds a value, called locator, that specifies the location of a large object.
5) a) LOB large objects datatype holds a value, called locator, that specifies the location of a large object.
b) With LOB datatypes, you can store blocks of unstructured data such as text, graphic images, video clips, and audio files up to 4 gigabytes in size.
c) LOB datatypes allow efficient, random, piecewise access to the data and can be further divided into 4 categories as show above.

PL/SQL Datatypes have tight integration with SQL

PL/SQL datatypes have tight integration with the structured query language (SQL). PL/SQL is Oracle's procedural language extension to SQL, and it is designed to work seamlessly with SQL. PL/SQL datatypes are closely aligned with SQL datatypes, and they are used to manipulate and store data in Oracle databases. PL/SQL datatypes can be categorized into two main groups:
  1. Scalar datatypes: These are similar to SQL datatypes and include types such as NUMBER, VARCHAR2, DATE, and TIMESTAMP.
  2. Composite datatypes: These are more complex datatypes that can store multiple values, such as arrays and records.
PL/SQL datatypes are used to declare variables, which can then be used to store and manipulate data in Oracle databases. The tight integration with SQL allows for easy exchange of data between PL/SQL and SQL, making it a powerful tool for database development and management.
Here are some examples of PL/SQL datatypes and their corresponding SQL datatypes:
PL/SQL SQL Datatype
BOOLEAN (no equivalent)

Note that while there is a high degree of integration between PL/SQL and SQL datatypes, there are some differences in how they are used and manipulated.

Beginning Oracle PL/SQL

How to program with PL/SQL Datatypes?

SQL has become the standard database language because it is flexible, powerful, and easy to learn. A few English-like statements such as
  1. SELECT,
  2. INSERT,
  3. UPDATE, and
make it easy to manipulate the data stored in a relational database. PL/SQL is tightly integrated with SQL. With PL/SQL, you can use all SQL data manipulation, cursor control, and transaction control statements, and all SQL functions, operators, and pseudocolumns. PL/SQL fully supports SQL data types and you need not convert between PL/SQL and SQL data types. For example, if your PL/SQL program retrieves a value from a database column of the SQL type VARCHAR2, it can store that value in a PL/SQL variable of the type VARCHAR2. Special PL/SQL language features let you work with table columns and rows without specifying the data types, saving on maintenance work when the table definitions change.
Running a SQL query and processing the result set is as easy in PL/SQL as opening a text file and processing each line in popular scripting languages. Using PL/SQL to access metadata about database objects and handle database error conditions, you can write utility programs for database administration that are reliable and produce readable output about the success of each operation. Many database features, such as triggers and object types, use PL/SQL. You can write the bodies of triggers and methods for object types in PL/SQL and PL/SQL supports both
  1. static and
  2. dynamic
SQL. "Static SQL" is SQL whose full text is known at compilation time. "Dynamic SQL" is SQL whose full text is not known until run time. Dynamic SQL enables you to make your applications more flexible and versatile.
PL/SQL datatypes include all the valid SQL datatypes as well as "complex datatypes" based on query structures.

Oracle Database PL/SQL Programming

Scalar Datatypes

Here is a list of scalar datatypes available in PL/SQL: Numeric Datatypes
  1. NUMBER (includes integer and floating-point numbers)
  4. DEC (deprecated)
  5. DECIMAL (deprecated)
  6. NUMERIC (deprecated)
  7. INTEGER (subtype of NUMBER)
  8. INT (subtype of NUMBER)
  9. SMALLINT (subtype of NUMBER)
  10. FLOAT (subtype of NUMBER)
  11. DOUBLE PRECISION (subtype of NUMBER)

Character Datatypes
  1. CHAR
  3. NCHAR
  5. STRING (subtype of CHAR or VARCHAR2, depending on the context)

Date and Time Datatypes
  1. DATE

Boolean Datatype

Miscellaneous Datatypes
  1. RAW (raw binary data)
  2. LONG RAW (deprecated)
  3. ROWID (a unique identifier for a row in a table)
  4. UROWID (a universal rowid, used for foreign tables and non-Oracle tables)
Note: The DEC, DECIMAL, NUMERIC, and LONG RAW datatypes are deprecated and should not be used in new code. Also, some subtypes like INTEGER, INT, SMALLINT, FLOAT, DOUBLE PRECISION are subtypes of NUMBER and can be used interchangeably in most cases.

Composite Types
Reference Types
REF CURSOR REF object_type
LOB Types

Variable PL/SQL Data Types

The correct values and their datatypes are:
  1. TRUE: SCALAR datatype
  2. RECORD: COMPOSITE datatype
  3. %ROWTYPE: REFERENCE datatype
  4. BLOB: LOB datatype

There are two generalized types that you define in packages. You can declare
  1. static or
  2. dynamic datatypes.
Datatypes are typically PL/SQL 1) structures, 2) collections, 3) reference cursors, and 4) cursors. All of these can be dynamic or static datatypes. They are dynamic when their declaration anchors their type to a row or column definition. You use the %ROWTYPE to anchor to a row and %TYPE to anchor to a column, as qualified in Table 2-4. Types are static when they rely on explicitly declared SQL datatypes, such as DATE, INTEGER, NUMBER, or VARCHAR2. As a general rule, package specifications are independent of other schema-level objects. You build dependencies when you anchor package specification–declared types to catalog objects, like tables and views. If something changes in the dependent table or view, the package specification becomes invalid. Changes in package specifications can create a cascade reaction that invalidates numerous package bodies and standalone schema-level programs.

Attribute Description
%ROWTYPE The %ROWTYPE anchors the datatype of a variable to the row structure of a database catalog object (table or view), or PL/SQL record structure. The new variable inherits both the position and datatype of the columns found in the referenced table or view when you anchor to a catalog object. The new variable inherits both the position and datatype of the explicit PL/SQL record structure, which may inherit indirectly from one or more catalog objects
%TYPE The %TYPE anchors the datatype of a variable to a column datatype found in a database catalog object, like a table or view.

Table 2-4: Anchoring Attributes

BLOB Datatype

The BLOB datatype stores unstructured binary large objects. BLOB objects can be thought of as bitstreams with no character set semantics. BLOB objects can store binary data up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage). If the tablespaces in your database are of standard block size, and if you have used the default value of the CHUNK parameter of LOB storage when creating a LOB column, then this is equivalent to (4 gigabytes - 1) * (database block size). BLOB objects have full transactional support. Changes made through SQL, the DBMS_LOB package, or the Oracle Call Interface (OCI) participate fully in the transaction. BLOB value manipulations can be committed and rolled back. However, you cannot save a BLOB locator in a PL/SQL or OCI variable in one transaction and then use it in another transaction or session.
Every constant, variable, and parameter has a datatype (or type), which specifies a storage format, constraints, and valid range of values.
PL/SQL provides many predefined datatypes. For instance, you can choose from
  1. integer,
  2. floating point,
  3. character,
  5. date,
  6. collection,
  7. reference, and
  8. large object (LOB) types.
PL/SQL also lets you define your own subtypes. This chapter covers the basic types used frequently in PL/SQL programs. Later chapters cover the more specialized types.
In the next lesson, non-PL/SQL bind and host variables will be discussed.

SEMrush Software