Lesson 1

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.

Module Objectives
By the end of this module, you will know how to:
1. Describe the basic structure of a PL/SQL block
2. Define the scope of variables within nested blocks
3. Place identifiers and use literals within a PL/SQL block
4. Add comments to a PL/SQL block
5. Identify implicit and explicit datatype conversions within PL/SQL
6. Use operators within PL/SQL

Like many other programming languages, offers a variety of data types to manage and manipulate data efficiently. Here's an overview of some key data types in PL/SQL:

Numeric Types:
NUMBER: General purpose numeric data type for integers or real numbers.
1. Examples:
  1. NUMBER, NUMBER(5,2) for precision and scale.
  2. BINARY_FLOAT, BINARY_DOUBLE: Floating-point types for faster numeric operations.
Character Types:
- CHAR: Fixed-length character data up to 32767 bytes. Pads with spaces if shorter.
- VARCHAR2: Variable-length character data up to 32767 bytes. Does not pad with spaces.
- NCHAR, NVARCHAR2: Similar to CHAR and VARCHAR2 but for Unicode data.
Date and Time Types:
- DATE: Stores date and time in a fixed format.
- TIMESTAMP: Date and time, including fractional seconds.
- TIMESTAMP WITH TIME ZONE: Adds time zone information to TIMESTAMP.
- TIMESTAMP WITH LOCAL TIME ZONE: Converts to the database's local time zone.
Large Object (LOB) Types:
- BLOB: Binary Large Object for storing large binary data like images or sounds.
- CLOB: Character Large Object for storing large text data.
- NCLOB: Like CLOB but for Unicode data.
- BFILE: For storing pointers to external binary files.
Boolean Type:
- BOOLEAN: Represents true or false values, used primarily in PL/SQL code for control structures.
Miscellaneous Types:
- RAW: For raw binary data up to 2000 bytes.
- LONG: An older type for variable-length character data up to 2GB (though VARCHAR2 or LOB types are generally preferred now).
- ROWID: A unique identifier for every row in a table.
User-Defined Types:
- PL/SQL Records: Composite data types that can hold multiple data items of different types.
- Collections (Nested Tables, VARRAYs, Associative Arrays): For grouping elements of the same type.
Special Types:
- REF CURSOR: Used for dynamic SQL queries where the structure of the result set is not known at compile time.

Here's a simple example to illustrate some of these data types in PL/SQL:

DECLARE
  v_number NUMBER(10,2) := 12345.67; -- Numeric type with precision and scale
  v_char CHAR(5) := 'Hello'; -- Fixed length character string
  v_varchar2 VARCHAR2(20) := 'World'; -- Variable length character string
  v_date DATE := SYSDATE; -- Current date
  v_boolean BOOLEAN := TRUE; -- Boolean type
  v_blob BLOB; -- Binary Large Object (BLOB)
  v_clob CLOB; -- Character Large Object (CLOB)
BEGIN
  DBMS_OUTPUT.PUT_LINE('Number: ' || v_number);
  DBMS_OUTPUT.PUT_LINE('Char: ' || v_char);
  DBMS_OUTPUT.PUT_LINE('Varchar2: ' || v_varchar2);
  DBMS_OUTPUT.PUT_LINE('Date: ' || v_date);
  DBMS_OUTPUT.PUT_LINE('Boolean: ' || CASE WHEN v_boolean THEN 'True' ELSE 'False' END);
END;
/

Each data type in PL/SQL serves specific purposes, allowing for efficient storage, retrieval, and manipulation of different kinds of data within Oracle databases.

Oracle DBMS Packages

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:
```
create or replace package STANDARD is
  type BOOLEAN is (FALSE, TRUE);
  type NUMBER is NUMBER_BASE;
  subtype INTEGER is NUMBER(38,);
```
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. A dynamically typed programming language, like JavaScript, Perl, or Ruby performs most type checking at runtime. 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.

When it comes to datatypes, PL/SQL supports the standard types.
In the next lesson, the basic structure of a PL/SQL block we will bed described.