Oracle PL/SQL Data Types and Variables and Literals

Yusuf SEZER October 11, 2020 ORACLE

This article contains information about PL/SQL Data Types and Variables and Literals.

Oracle PL/SQL Data Types

Data types indicate how data is stored in memory.

PL / SQL has several data types to store data.

PL / SQL data types are divided into four sections: Scalar, Large Object, Composite and Refence.

1)Scalar Data Types in Oracle PL/SQL

Scalar data types is a data type that store a single data such as NUMBER, DATE, BOOLEAN.

Numeric Data Types in Oracle

PLS_INTEGER, BINARY_INTEGER, BINARY_FLOAT, BINARY_DOUBLE, NUMBER(prec, scale), DEC(prec, scale), DECIMAL(prec, scale), NUMERIC(pre, secale), DOUBLE PRECISION, FLOAT, INT, INTEGER, SMALLINT, REAL

SET SERVEROUTPUT ON;
DECLARE
    v_number_1 INT := 1;
    v_number_2 INTEGER := 2;
    v_number_3 PLS_INTEGER := 3;
    v_number_4 DOUBLE PRECISION := 4;
BEGIN
    DBMS_OUTPUT.put_line(v_number_1);
    DBMS_OUTPUT.put_line(v_number_2);
    DBMS_OUTPUT.put_line(v_number_3);
    DBMS_OUTPUT.put_line(v_number_4);
END;

SET SERVEROUTPUT ON;

DECLARE

v_number_1 INT := 1;

v_number_2 INTEGER := 2;

v_number_3 PLS_INTEGER := 3;

v_number_4 DOUBLE PRECISION := 4;

BEGIN

DBMS_OUTPUT.put_line(v_number_1);

DBMS_OUTPUT.put_line(v_number_2);

DBMS_OUTPUT.put_line(v_number_3);

DBMS_OUTPUT.put_line(v_number_4);

END;

Character Data Types in Oracle

CHAR, VARCHAR2, RAW, NCHAR, NVARCHAR2, LONG, LONG RAW, ROWID, UROWID

SET SERVEROUTPUT ON;
DECLARE
    v_myname_1 CHAR(5) := 'YUSUF';
    v_myname_2 VARCHAR2(5) := 'YUSUF';
    v_myname_3 NCHAR(5) := 'YUSUF';
    v_myname_4 NVARCHAR2(5) := 'YUSUF';
BEGIN
    DBMS_OUTPUT.put_line(v_myname_1);
    DBMS_OUTPUT.put_line(v_myname_2);
    DBMS_OUTPUT.put_line(v_myname_3);
    DBMS_OUTPUT.put_line(v_myname_4);
END;

SET SERVEROUTPUT ON;

DECLARE

v_myname_1 CHAR(5) := 'YUSUF';

v_myname_2 VARCHAR2(5) := 'YUSUF';

v_myname_3 NCHAR(5) := 'YUSUF';

v_myname_4 NVARCHAR2(5) := 'YUSUF';

BEGIN

DBMS_OUTPUT.put_line(v_myname_1);

DBMS_OUTPUT.put_line(v_myname_2);

DBMS_OUTPUT.put_line(v_myname_3);

DBMS_OUTPUT.put_line(v_myname_4);

END;

Boolean Data Types in Oracle

The BOOLEAN data type takes TRUE, FALSE and NULL.

Datetime Data Types in Oracle

DATE, TIMESTAMP, INTERVAL

SET SERVEROUTPUT ON;
DECLARE
    v_date DATE := SYSDATE;
    v_date_hour TIMESTAMP := SYSTIMESTAMP;
    v_month INTERVAL YEAR (2) TO MONTH := INTERVAL '15' MONTH;
BEGIN
    DBMS_OUTPUT.put_line(v_date);
    DBMS_OUTPUT.put_line(v_date_hour);
    DBMS_OUTPUT.put_line(v_month);
END;

SET SERVEROUTPUT ON;

DECLARE

v_date DATE := SYSDATE;

v_date_hour TIMESTAMP := SYSTIMESTAMP;

v_month INTERVAL YEAR (2) TO MONTH := INTERVAL '15' MONTH;

BEGIN

DBMS_OUTPUT.put_line(v_date);

DBMS_OUTPUT.put_line(v_date_hour);

DBMS_OUTPUT.put_line(v_month);

END;

2)Large Object Data Types in Oracle PL/SQL

Large Object data types are data types that store files such as text, images, and videos.

BFILE, BLOB, CLOB, NCLOB

SET SERVEROUTPUT ON;
DECLARE
    v_file_name VARCHAR2(255) := 'elma.jpg';
    v_file BFILE := bfilename('BLOB_DIR', v_file_name);
BEGIN
    NULL;
END;

SET SERVEROUTPUT ON;

DECLARE

v_file_name VARCHAR2(255) := 'elma.jpg';

v_file BFILE := bfilename('BLOB_DIR', v_file_name);

BEGIN

NULL;

END;

3)Composite Data Types in Oracle PL/SQL

Composite data types are data types such as Collections and Records.

4)Reference Data Types in Oracle PL/SQL

The reference data type is the data type that refers to the previously defined data types.

NOTE: Some data types have the same feature.

User Defined Data Types in Oracle PL/SQL

We can define data types as follows.

SET SERVEROUTPUT ON;
DECLARE
    SUBTYPE name_surname IS VARCHAR2(80);
    yusuf_sezer name_surname := 'Yusuf Sefa SEZER';
BEGIN
    DBMS_OUTPUT.put_line(yusuf_sezer);
END;

SET SERVEROUTPUT ON;

DECLARE

SUBTYPE name_surname IS VARCHAR2(80);

yusuf_sezer name_surname := 'Yusuf Sefa SEZER';

BEGIN

DBMS_OUTPUT.put_line(yusuf_sezer);

END;

Unnecessary length of data is prevented by using user-defined data types.

Also, developed PL/SQL programs are provided to be more meaningful.

Variables in Oracle PL/SQL

PL/SQL allows the use of variables to temporarily store data.

We can define a variable as follows.

variable_name [CONSTANT] data_type [NOT NULL] [:= | DEFAULT first_value_of_variable]

1	variable_name [CONSTANT] data_type [NOT NULL] [:= \| DEFAULT first_value_of_variable]

Variables can be up to 30 characters long.

name_surname, v_name_surname

Variables must start with the ASCII character.

For example, a variable name cannot be 1_name_surname.

v_name_surname VARCHAR2(80);

1	v_name_surname VARCHAR2(80);

The keyword := and DEFAULT can be used to give initial values to variables.

v_name_surname VARCHAR2(80) := 'Yusuf SEZER';

1	v_name_surname VARCHAR2(80) := 'Yusuf SEZER';

v_name_surname VARCHAR2(80) DEFAULT 'Yusuf SEZER';

1	v_name_surname VARCHAR2(80) DEFAULT 'Yusuf SEZER';

The NOT NULL keyword can be used so that variables do not take NULL.

SET SERVEROUTPUT ON;
DECLARE
    v_name_surname VARCHAR2(20) NOT NULL := 'Yusuf Sefa SEZER';
BEGIN
    --v_name_surname := ''; -- it will fail.
    --v_name_surname := NULL; -- it will fail.
    DBMS_OUTPUT.put_line(v_name_surname);
END;

SET SERVEROUTPUT ON;

DECLARE

v_name_surname VARCHAR2(20) NOT NULL := 'Yusuf Sefa SEZER';

BEGIN

--v_name_surname := ''; -- it will fail.

--v_name_surname := NULL; -- it will fail.

DBMS_OUTPUT.put_line(v_name_surname);

END;

The data type of the variable can be the data type of the column in the table.

v_name_surname table.column%TYPE;

1	v_name_surname table.column%TYPE;

The CONSTANT keyword can be used to make the variable constant.

SET SERVEROUTPUT ON;
DECLARE
    PI CONSTANT NUMBER := 3.141592654;
BEGIN
    --PI := 1; -- it will fail.
    DBMS_OUTPUT.put_line(PI);
END;

SET SERVEROUTPUT ON;

DECLARE

PI CONSTANT NUMBER := 3.141592654;

BEGIN

--PI := 1; -- it will fail.

DBMS_OUTPUT.put_line(PI);

END;

Literals in Oracle PL/SQL

Literal values in PL/SQL are called literal.

‘Hello Oracle’

TRUE, FALSE, NULL

‘A’

You can find more detailed information about below topics in the below link.

PL/SQL Tutorial

You will find below topics in this article.

Also you can find more about data types at docs.oracle.com

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

Oracle PL/SQL Data Types and Variables and Literals