• Home
  • Datatypes in PL/SQL

Datatypes in PL/SQL

In PL/SQL (Procedural Language/Structured Query Language), datatypes refer to the type of data that a variable, constant, or expression can hold or store. PL/SQL supports a wide range of datatypes, including scalar datatypes, composite datatypes, and object-oriented datatypes, which are used to declare and define variables, constants, and expressions in a PL/SQL program.

Scalar datatypes: Scalar datatypes are the most basic and fundamental datatypes in PL/SQL, and they represent a single value or unit of data. Scalar datatypes include numeric datatypes, character datatypes, boolean datatypes, and datetime datatypes.

  • Numeric datatypes: Numeric datatypes are used to store numeric values, such as integers, decimals, and floats. The main numeric datatypes in PL/SQL are NUMBER, BINARY_FLOAT, and BINARY_DOUBLE.
  • Character datatypes: Character datatypes are used to store character strings, such as names, addresses, and descriptions. The main character datatypes in PL/SQL are CHAR, VARCHAR2, and CLOB.
  • Boolean datatypes: Boolean datatypes are used to store logical values, such as true and false. The main boolean datatype in PL/SQL is BOOLEAN.
  • Datetime datatypes: Datetime datatypes are used to store date and time values, such as dates, times, and timestamps. The main datetime datatypes in PL/SQL are DATE, TIMESTAMP, and INTERVAL.

Composite datatypes: Composite datatypes are made up of multiple scalar datatypes, and they are used to store complex data structures, such as records and collections. The main composite datatypes in PL/SQL are records and collections.

  • Records: Records are composite datatypes that are used to store a set of related data values, such as a customer record or an employee record. Records can be defined and declared using the TYPE keyword, and they can be used to store and manipulate data in a PL/SQL program.
  • Collections: Collections are composite datatypes that are used to store a set of related data values, such as an array of numbers or a list of names. Collections can be defined and declared using the TYPE keyword, and they can be used to store and manipulate data in a PL/SQL program.

Object-oriented datatypes: Object-oriented datatypes are complex datatypes that are used to store and manipulate object-oriented data, such as objects and object types. The main object-oriented datatypes in PL/SQL are objects and object types.

  • Objects: Objects are complex datatypes that are used to store and manipulate object-oriented data, such as customer objects or employee objects. Objects can be defined and declared using the TYPE keyword, and they can be used to store and manipulate data in a PL/SQL program.
  • Object types: Object types are complex datatypes that are used to define and declare object-oriented data structures, such as customer types or employee types. Object types can be used to define and declare objects in a PL/SQL program, and they can be used to store and manipulate object-oriented data in a PL/SQL program.

In summary, PL/SQL supports a wide range of datatypes, including scalar datatypes, and composite datatypes.