PL SQL Data Types, Variables, Constants And Literals

This tutorial explains whats are PL SQL Data Types, Variables, Constants & Literals in detail with the help of programming examples:

In the previous tutorial of the PL/SQL series, we got introduced to PL/SQL with its features and basic syntax with examples.

In this article, we will discuss the different data types that PL/SQL supports. Also, we shall explore the variables and the constants used extensively in PL SQL.

PL/SQL literals are also important and we will learn about it in this tutorial along with its practical usage.

Pl SQL Data Types

All the variables, constants, parameters of PL/SQL have a particular data type that defines its range of values, constraints, and the format in which they are stored. PL/SQL contains the data types like Large Object also known as LOB, Scalar, Reference, and Composite.

Scalar Data Types

Let us first discuss the Scalar Data Types which consist of the following:

• NUMERIC data types that deal with values on which mathematical operations are done.
• BOOLEAN data types that deal with the values on which logical operations are done.
• CHARACTER data types that deal with the values consisting of alphanumeric ones.
• DATE TIME data types.

Numeric Data Types

The Numeric datatype of PL/SQL consists of the following:

• BINARY_FLOAT
• BINARY_INTERGER
• PLS_INTEGER
• BINARY_DOUBLE
• DECIMAL
• NUMERIC
• NUMBER
• FLOAT
• INT
• INTEGER
• DOUBLE PRECISION
• REAL
• SMALLINT
• DEC

Code Snippet with numeric data types:

DECLARE
   numA INTEGER := 10; 
   numB NUMBER(10) := 10.8; 
   numC DOUBLE PRECISION := 10.6; 
BEGIN 
   dbms_output.put_line('The numA is: ' || numA);
   dbms_output.put_line('The numB is: ' || numB);
   dbms_output.put_line('The numC is: ' || numC);
END; 
/

In the above code, we have INTEGER, NUMBER, and DOUBLE PRECISION data types with the variables numA, numB and numC respectively. numB has a number data type with precision 10 and has no digits after the decimal.

The output of the above code should be:

Character Data Types

The character data types of PL/SQL consist of the following as listed below:

#1) VARCHAR2: This data type will store string but the string length is not fixed at the time of declaration. The varchar2 has a maximum size of up to 32767 bytes. The maximum width of a varchar2 database column is 4000 bytes.

Syntax:
test VARCHAR2(20) := ‘SoftwareTest’;

#2) CHAR: This datatype will store string but the string length is fixed at the time of declaration. The char has a maximum size of up to 32767 bytes. The maximum width of a char database column is 2000 bytes.

Syntax:
test CHAR2(20) := ‘SoftwareTest’;

#3) NCHAR: This is similar to CHAR, but it will store only the national character set. The NCHAR has a maximum size of up to 32767 bytes. The maximum width of an NCHAR database column is 2000 bytes.

Syntax:
test NCHAR2(20);

#4) LONG: This will store variable-length character strings. The long has a maximum size up to 32760 bytes.

Syntax:
test LONG;

#5) LONG ROW: This will store data in binary format or byte strings. The long row has a maximum size of up to 32760 bytes.

Syntax:
test LONG ROW ;

#6) ROWID: These are the physical identifiers of a row which points to the address of a row in a normal table.

#7) UROWID: These are the universal identifiers of rows.

#8) NVARCHAR2: This is similar to VARCHAR2, but it will store only the national character set. The maximum width of a nvarchar2 database column is 4000 bytes.

#9) VARCHAR: This is similar to VARCHAR2.

Syntax:
test VARCHAR2(20) := ‘SoftwareTest’;

Let us now discuss the character data types of PL/SQL in tabular format.

Boolean Data Types

These data types of PL/SQL can be used for storing logical values. TRUE, FALSE, and NULL are the Boolean values.

SQL does not have BOOLEAN data types. So we need to avoid them in the following scenarios:

• PL/SQL statements generated from SQL.
• Default functions of PL/SQL.
• Normal SQL statements.

Syntax:

test Boolean;

The output of the test variable will be TRUE or FALSE depending on certain criteria.

Date Time Data Types

These data types are used for constant lengths of date and time. The proper range of dates starts from January 1, 4712 BC to December 31, 9999 AD, and the time is defined in terms of seconds. The built-in date format is DD-MON-YY which implies double digits allotted for the day of the month, month name in short, and then the last two digits of the year.

A DATE consists of second, minute, day, hour, month, year, and century. Each of these fields has a defined specific range as listed below:

• SECOND: It is defined from the range of 00 to 59.9.
• MINUTE: It is defined in the range from 00 to 59.
• HOUR: It is defined in the range from 00 to 23.
• DAY: It is defined in the range from 01 to 31.
• MONTH: It is defined in the range from 01 to 12.
• YEAR: It is defined in the range from -4712 to 9999 (which does not include 0).
• TIMEZONE_HOUR: It is defined in the range from -12 to 14.
• TIMEZONE_MINUTE: It is defined in the range from 00 to 59.
• TIMEZONE_REGION
• TIMEZONE_ABBR

Syntax:

TIMESTAMP '1990-06-04 9:00:00 US/Pacific'
TIMESTAMP '1990-06-04 01:30:00 US/Pacific PDT'

Here, we have described the time zone using symbols. US/Pacific or PDT specification is used to define a particular time zone. PDT form is mostly used since it gives the guidelines while switching to daylight saving time.

LOB Data Types

Let us now discuss the LOB datatypes which deal with huge chunks of data consisting of videos, sounds, graphics, images, and so on.

LOB datatypes have numerous advantages over long data types. They are listed below:

• Long can accommodate 2GB whereas LOB can hold 128TB.
• A table can have a single column of type LONG while there can be numerous columns of data type LOB.
• LOB datatype is undergoing constant improvements and updates from Oracle while LONG data type is not having many improvements and updates.

LOB data types are listed below:

• BFILE: This is used to hold unstructured data in binary format out of the database as an operating system file.
• NCLOB: This is used to hold huge NCHAR data in the database.
• CLOB: This is used to hold huge data of character type in the database.

Syntax:
Binary CLOB;

• BLOB: This is used to hold huge binary data in the database.

Syntax:
Binary BLOB;

Code Implementation with some data types:

DECLARE
    m CHAR(20) := 'softwareTest!';
    n VARCHAR2(30) := 'plsql';
    o NCHAR(30) := 'plsql datatypes';
    p NVARCHAR2(30) := 'plsql literals';
    presentDt DATE:= SYSDATE; 
    a INTEGER := 16; 
    b NUMBER(20) := 11.2; 
    c DOUBLE PRECISION := 14.7; 
BEGIN
   dbms_output.put_line('The char datatype is: ' || m);
   dbms_output.put_line('The varchar datatype is: ' || n);
   dbms_output.put_line('The nchar datatype is: ' || o);
   dbms_output.put_line('The nvarchar2 datatype is: ' || p);
   dbms_output.put_line('The current date is: ' || presentDt);
   dbms_output.put_line('The number a is: ' || a);
   dbms_output.put_line('The number b is: ' || b);
   dbms_output.put_line('The number c is: ' || c);
END;
/

The output of the above code should be:

PL SQL Variables

PL/SQL variable names follow the naming convention which consists of alphanumeric characters not more than thirty. PL/SQL is case insensitive and the keywords should not be used as a variable.

The variable name should be meaningful and can be followed by an underscore (_), number or dollar ($).

PL SQL Variable Declaration

Variables of PL/SQL must be available in the declaration area or present in a package as a global variable. PL/SQL reserves memory for the variables and the location of the storage is defined by the variable name.

Syntax for variable declaration:

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

Here, the variable_name is an authentic PL/SQL identifier and data type can be a user-defined or a confirmed PL/SQL datatype.

Code implementation for variable declaration:

DECLARE
    v_namis VARCHAR2(10);
    n_addressis NUMBER(10,3);
BEGIN
   dbms_output.put_line('The name is: ' || v_namis);
   dbms_output.put_line('The address is: ' || n_addressis);
END;

The output of the above code should be:

Please note, we have mentioned both the precision and size of the number datatype. This is known as the constrained declaration. This type of declaration consumes less memory.

PL/SQL Variable Initialization

By default, PL/SQL takes the value of a variable as NULL. However, if we do not want to initialize a variable with NULL, then that can be achieved by either using the DEFAULT keyword or with the assignment operator.

Syntax:

num integer := 50;
test varchar(40) DEFAULT 'Software Test Help';

We can also define that a variable should not have NULL value by mentioning NOT NULL constraint. However, if the NOT NULL constraint is used, we have to set a value for the variable.

Code implementation with variable initialization:

DECLARE
  v_namis VARCHAR2(40) := 'softwareTest!';
  n_couris VARCHAR2(30) := 'plsql';
BEGIN
  dbms_output.put_line('The value is: ' || v_namis);
  dbms_output.put_line('The value is: ' || n_couris);
END;
/

The output of the above code should be:

Code implementation with Assignment concept:

DECLARE
  v_namis VARCHAR2(40);
  n_couris VARCHAR2(30);
BEGIN
  v_namis := 'softwareTest!';
  n_couris := 'plsql';
  dbms_output.put_line('The value is: ' || v_namis);
  dbms_output.put_line('The value is: ' || v_couris);
END;
/

Here the assignment operator (=) is used to assign values to the variable.

The output of the above code should be:

PL/SQL Variable Scope

A PL/SQL block can have inner blocks of code. If a variable that is declared, is a part of the inner block it cannot be utilized by the outside block. But a variable which is a part of the outer block can be manipulated in the inner block of code.

These types of variables are described below:

• Global variables: The variables which are declared at the outer block or package.
• Local variables: The variables which are declared in the inner block of code and cannot be accessed from the outside block.

Code implementation with global and local variables:

DECLARE 
   -- Global variables 
   val number := 10; 
BEGIN 
   dbms_output.put_line('Global variable is : ' || val); 
   DECLARE 
       -- Local variables 
       val number := 100; 
   BEGIN 
       dbms_output.put_line('Local variable is: ' || val); 
   END; 
END; 
/

The output of the above code should be:

Please note that the output here, the Global variable is: 10 (because of the global variable val) and ‘Local variable is: 100 (because of the local variable val).

PL/SQL Anchor

PL/SQL anchors are defined with the keyword %TYPE to declare variables with the data type connected with the data type of that specific column of the table.

Code snippet with anchor:

 
DECLARE
   v_namis RESSIDENT.NAME%TYPE;
   n_addessis RESSIDENT.ADDRESS%TYPE;
BEGIN
   NULL; 
END;
/

Here, we have a RESIDENT table and we are dealing with the columns NAME and ADDRESS of that table.

Constants In PL/SQL

A constant keeps the value that is once declared unchanged throughout the program.

PL/SQL Constant declaration

The CONSTANT keyword is used to define constants in the program. It starts with a predefined value that remains the same throughout the program.

Syntax for variable declaration:

const_name CONSTANT data type := val

Code Implementation with constant:

DECLARE 
    -- Constant 
    p constant number := 3.141592654; 
    -- Radius declarations 
    r number(10,1); 
    a_circle number(10,3);
BEGIN 
    -- Area calculation
    r := 9.4; 
    a_circle := p * r * r; 
    -- Output in console
    dbms_output.put_line('Area of circle: ' || a_circle); 
END; 
/

The output of the above code should be:

Literals In PL SQL

A Boolean, Numeric, or a String value which is not defined by a PL/SQL identifier is called a literal. The literals are case sensitive and are of the following types as listed below:

• Boolean Literals [For Example – FALSE, TRUE ]
• Character Literals [For Example – ‘s’, ‘7’, ‘)’ ]
• String Literals [For Example – ‘Software Test’ ]
• Numeric Literals [For Example – 78, 04, 6.3 ]
• DATE and Time Literals [For Example – ’25-05-2012’ ]

Frequently Asked Questions and Answers

Q #1) What are the data types in PL SQL?

Answer: The PL SQL data types are composite and scalar. The scalar data types can hold single values like Character, Number, Boolean, and DateTime. While the composite data types store more than one value like collection and record.

Q #2) What is the PL/SQL variable?

Answer: A PL SQL variable is a name that helps a developer to store data temporarily during program execution. It is a meaningful name assigned to the storage area. All the variables in PL/SQL belong to a specific data type.

Q #3) How to pass a date parameter in PL/SQL?

Answer: We can pass a date parameter in PL/SQL with the help of the DATE keyword. It follows a fixed format as ‘YYYY-MM-DD’.

Q #4) How do you declare a constant in PL/SQL?

Answer: We can declare a constant in PL/SQL with the help of the keyword CONSTANT. After CONSTANT, we have to mention the value which is assigned to it. This value remains fixed throughout the program.

Q #5) How many types of literals are available in PL/SQL?

Answer: The types of literals in PL/SQL are Number, DateTime, Text, and Integer.

Q #6) Are PL SQL variables case sensitive?

Answer: PL SQL variables including the reserved words are case insensitive. For example, BEGIN and begin both serve the same purpose.

Conclusion

A large portion of PL/SQL dealing with topics like the various data types used and their importance and the variables of PL SQL should be comprehensible now after reading this tutorial.

Also, we have explored in detail the PL SQL constants and literals. Go through each of the topics one by one and slowly you will master this. PL/SQL is used in industries for both web and server development nowadays.

In the next tutorial, we shall discuss the PL/SQL Insert, Update, Delete, Select statement, and other related topics.

Let’s stay tuned for more knowledge sharing.

<<PREV Tutorial | NEXT Tutorial>>