PL/SQL - Data Types

Hello, aspiring programmers! Today, we're diving into the fascinating world of PL/SQL data types. Don't worry if you're new to programming; I'll guide you through this journey step by step. By the end of this tutorial, you'll be comfortable with various data types in PL/SQL and ready to start your coding adventure!

PL/SQL Scalar Data Types and Subtypes

Let's start with the basics. In PL/SQL, scalar data types are the building blocks of our programs. They represent single values, like numbers or text.

What are Scalar Data Types?

Scalar data types are the simplest form of data in PL/SQL. They're like the atoms of our programming universe – small, indivisible units that we use to build more complex structures.

Here's a simple example to illustrate a scalar data type:

DECLARE
  my_name VARCHAR2(50) := 'John Doe';
BEGIN
  DBMS_OUTPUT.PUT_LINE('My name is: ' || my_name);
END;

In this example, my_name is a variable of the VARCHAR2 data type, which is used to store text. We've assigned it the value 'John Doe'. When we run this code, it will output: "My name is: John Doe".

PL/SQL Numeric Data Types and Subtypes

Now, let's talk about numbers. PL/SQL has several numeric data types to handle different kinds of numbers.

Common Numeric Data Types

Data Type	Description	Example
NUMBER	General purpose number	NUMBER(10,2)
INTEGER	Whole numbers	INTEGER
BINARY_FLOAT	32-bit floating-point number	BINARY_FLOAT
BINARY_DOUBLE	64-bit floating-point number	BINARY_DOUBLE

Let's see these in action:

DECLARE
  my_integer INTEGER := 42;
  my_float BINARY_FLOAT := 3.14;
  my_salary NUMBER(8,2) := 5000.50;
BEGIN
  DBMS_OUTPUT.PUT_LINE('The answer is: ' || my_integer);
  DBMS_OUTPUT.PUT_LINE('Pi is approximately: ' || my_float);
  DBMS_OUTPUT.PUT_LINE('My salary is: $' || my_salary);
END;

This script declares three variables with different numeric types. The NUMBER(8,2) type for my_salary means it can store up to 8 digits in total, with 2 digits after the decimal point.

PL/SQL Character Data Types and Subtypes

Text data is crucial in programming. PL/SQL provides several character data types to handle text efficiently.

Common Character Data Types

Data Type	Description	Example
VARCHAR2	Variable-length character string	VARCHAR2(100)
CHAR	Fixed-length character string	CHAR(10)
NVARCHAR2	Variable-length unicode string	NVARCHAR2(100)
NCHAR	Fixed-length unicode string	NCHAR(10)

Here's how we use these in practice:

DECLARE
  my_varchar VARCHAR2(50) := 'Hello, World!';
  my_char CHAR(10) := 'Fixed     ';
  my_nvarchar NVARCHAR2(50) := N'こんにちは'; -- Japanese "Hello"
BEGIN
  DBMS_OUTPUT.PUT_LINE('VARCHAR2: ' || my_varchar);
  DBMS_OUTPUT.PUT_LINE('CHAR: ' || my_char || '(end)');
  DBMS_OUTPUT.PUT_LINE('NVARCHAR2: ' || my_nvarchar);
END;

Notice how the CHAR type pads the string with spaces to fill its fixed length.

PL/SQL Boolean Data Types

Boolean data types are essential for making decisions in our programs. They can only have three possible values: TRUE, FALSE, or NULL.

DECLARE
  is_student BOOLEAN := TRUE;
  has_job BOOLEAN := FALSE;
  is_married BOOLEAN;
BEGIN
  IF is_student THEN
    DBMS_OUTPUT.PUT_LINE('This person is a student.');
  END IF;

  IF NOT has_job THEN
    DBMS_OUTPUT.PUT_LINE('This person does not have a job.');
  END IF;

  IF is_married IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('We don''t know if this person is married.');
  END IF;
END;

This script demonstrates how we use boolean values to make decisions in our code.

PL/SQL Datetime and Interval Types

When working with dates and times, PL/SQL provides specific data types to handle these values accurately.

Common Datetime Types

Data Type	Description	Example
DATE	Date and time	DATE
TIMESTAMP	Date and time with fractional seconds	TIMESTAMP
INTERVAL YEAR TO MONTH	Period of time in years and months	INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND	Period of time in days, hours, minutes, and seconds	INTERVAL DAY TO SECOND

Let's see these in action:

DECLARE
  current_date DATE := SYSDATE;
  future_date TIMESTAMP := SYSTIMESTAMP + INTERVAL '1' YEAR;
  age_interval INTERVAL YEAR TO MONTH;
BEGIN
  age_interval := (SYSDATE - TO_DATE('1990-01-01', 'YYYY-MM-DD')) YEAR TO MONTH;

  DBMS_OUTPUT.PUT_LINE('Current date: ' || TO_CHAR(current_date, 'DD-MON-YYYY'));
  DBMS_OUTPUT.PUT_LINE('Date after 1 year: ' || TO_CHAR(future_date, 'DD-MON-YYYY HH24:MI:SS.FF'));
  DBMS_OUTPUT.PUT_LINE('Age: ' || age_interval);
END;

This script demonstrates working with dates, timestamps, and intervals.

PL/SQL Large Object (LOB) Data Types

For handling large amounts of data, PL/SQL provides LOB (Large Object) data types.

Common LOB Types

Data Type	Description	Example
CLOB	Character Large Object	CLOB
BLOB	Binary Large Object	BLOB
NCLOB	National Character Large Object	NCLOB

Here's a simple example of using a CLOB:

DECLARE
  my_clob CLOB;
BEGIN
  my_clob := 'This is a very long string that could potentially be several megabytes in size.';
  DBMS_OUTPUT.PUT_LINE(DBMS_LOB.GETLENGTH(my_clob) || ' characters long');
END;

This script creates a CLOB and demonstrates how to get its length.

PL/SQL User-Defined Subtypes

PL/SQL allows you to create your own subtypes based on existing data types. This can help make your code more readable and maintainable.

DECLARE
  SUBTYPE positive_number IS NUMBER(8,2) CHECK (positive_number > 0);
  my_salary positive_number;
BEGIN
  my_salary := 5000.50;
  DBMS_OUTPUT.PUT_LINE('My salary: $' || my_salary);

  -- This would raise an error:
  -- my_salary := -1000;
END;

In this example, we've created a subtype positive_number that ensures the value is always positive.

NULLs in PL/SQL

Finally, let's talk about NULL values. In PL/SQL, NULL represents an unknown or unavailable value. It's not the same as zero or an empty string.

DECLARE
  var1 NUMBER;
  var2 VARCHAR2(10) := '';
BEGIN
  IF var1 IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('var1 is NULL');
  END IF;

  IF var2 IS NOT NULL THEN
    DBMS_OUTPUT.PUT_LINE('var2 is not NULL, it''s an empty string');
  END IF;
END;

This script demonstrates the difference between NULL and an empty string.

And there you have it! We've covered the main data types in PL/SQL. Remember, understanding data types is crucial for writing efficient and error-free code. Keep practicing, and soon you'll be a PL/SQL pro!

Credits: Image by storyset