PL/SQL by Example / Scalar Data Types

Data types in PL/SQL are divided into three basic categories: scalar, composite, and reference. Scalar types typically store only a single value, composite types hold arrays of values, or they can be more complex structures like records or objects. A reference data type serves as a pointer.

Boolean and Number

The previous part, in addition to code structure and control flow, introduced two basic data types in a simplified way – number and boolean. The following example demonstrates two, or rather three, methods of assigning a value to a variable: default initialization in the declarative part, assignment in the execution part, and assignment using an SQL query with the INTO keyword.

DECLARE
  l_Bool BOOLEAN; -- boolean data type, cannot be used in SQL
  l_Number NUMBER:=100; -- number type
BEGIN
  l_Number:=200;
  SELECT 300
    INTO l_Number
    FROM dual -- auxiliary system table
  ;
  IF (l_Bool IS NULL) THEN -- default value of a variable is always NULL
    dbms_output.put_line(l_Number); -- 300
  END IF;
END;

The NUMBER data type can store both integer values and floating-point values. Precision (total number of digits) and scale (number of digits after the decimal point) can be specified manually. If not specified, the maximum precision and scale are used. In PL/SQL, we can also work with various numeric subtypes, such as INT, which internally maps to NUMBER(38,0). For maximum simplification, I will only use NUMBER from now on; more information about numeric data types can be found in the documentation.

Character and String

The CHAR type is used for working with strings of a predetermined fixed length, while VARCHAR2 is used for variable-length strings. The maximum size depends on the configuration and the chosen character set, and again, can be found in the documentation. However, it generally holds that for very long text, using the CLOB (“Character Large Object”) data type is more appropriate. String concatenation and basic functions for working with them are described in the following example.

DECLARE
  l_Character CHAR(1):='A'; -- fixed size
  l_String VARCHAR2(4000):='apple'; -- maximum size
BEGIN
  -- string concatenation using double pipes
  dbms_output.put_line(l_String||' '||l_Character); -- apple A
  -- substring extraction
  dbms_output.put_line(SUBSTR(l_String, 1, 3)); -- app
  -- position within the string
  dbms_output.put_line(INSTR(l_String, 'pl')); -- 3
  -- replacement
  dbms_output.put_line(REPLACE(l_String, 'p', 'P')); -- aPPle
END;

Date

For storing dates, Oracle uses the DATE type, which includes the year, month, day, hour, minute, and second. Assignment is done using the TO_DATE() function, and conversion to a human-readable string is done with the TO_CHAR() function. Both functions accept a format as their second parameter.

DECLARE
  l_Date DATE;
  l_String VARCHAR2(20);
BEGIN
  l_Date:=TO_DATE('1.1.2016', 'DD.MM.YYYY'); -- assignment
  -- implicit conversion according to database settings
  l_String:=l_Date;
  dbms_output.put_line(l_String); -- 01.01.16
  -- explicit conversion using format
  l_String:=TO_CHAR(l_Date, 'DD.MM.YYYY');
  dbms_output.put_line(l_String); -- 01.01.2016
  -- retrieving and converting the current date
  l_String:=TO_CHAR(SYSDATE, 'HH24:MI:SS DD.MON.YY');
  dbms_output.put_line(l_String); -- 17:30:12 21.MAR.16
  -- retrieving and converting to the name of the day of the week
  l_String:=TO_CHAR(SYSDATE, 'DAY');
  dbms_output.put_line(l_String); -- MONDAY
  -- overriding the database language setting
  l_String:=TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH');
  dbms_output.put_line(l_String); -- MON
END;

Basic date operations like adding an hour, day, or month are summarized in the following example.

DECLARE
  l_Date DATE:=TO_DATE('01.01.2016', 'DD.MM.YYYY');
BEGIN
  -- adding a day
  dbms_output.put_line(l_Date+1); -- 02.01.16
  -- adding an hour
  dbms_output.put_line(TO_CHAR(l_Date+1/24, 'HH24:MI')); -- 01:00
  -- adding a month
  dbms_output.put_line(ADD_MONTHS(l_Date, 1)); -- 01.02.16
  -- subtracting a year
  dbms_output.put_line(ADD_MONTHS(l_Date, -12)); -- 01.01.15
  -- last day of the month
  dbms_output.put_line(LAST_DAY(l_Date)); -- 31.01.16
  -- rounded difference in months
  dbms_output.put_line(ROUND(MONTHS_BETWEEN(l_Date+80, l_Date))); -- 3
END;

%TYPE

If we are unsure about the data type or if it might change over time, we can choose to declare it based on another variable or a table column.

DECLARE
  l_Number NUMBER;
  l_Employee_ID l_Number%TYPE; -- NUMBER
  l_FirstName employees.first_name%TYPE; -- VARCHAR2(20)
BEGIN
  l_Employee_ID:=100;
  l_FirstName:='Steven';
  dbms_output.put_line(l_Employee_ID||' '||l_FirstName); -- 100 Steven
END;

Constant and NOT NULL Clause

The PL/SQL language also supports constants, which cannot be overwritten with another value within the block’s body. The NOT NULL clause, which checks if a given variable is initialized, is also very useful.

DECLARE
  L_NUMBER CONSTANT NUMBER:=100; -- constant
  l_Bool BOOLEAN NOT NULL:=FALSE; -- non-NULL type
BEGIN
  -- CANNOT DO L_NUMBER:=200;
  -- CANNOT DO l_Bool:=NULL;
  IF (NOT l_Bool) THEN
    dbms_output.put_line(L_NUMBER); -- 100
  END IF;
END;

Maximum Size of Basic Data Types

* note that different characters have different sizes in UTF-8

** depends on the database block size setting