Coding Hub: Data Types , Variables & Constants in Pl/SQl

We know variables and constants stores value in specific storage format. There are six built-in PL/SQL Data types.

Scalar data types - Scalar data types haven't internal components.
Composite data types - Composite data types have internal components to manipulate data easily.
Reference data types - This data types works like a pointer to hold some value.
LOB data types - Stores large objects such as images, graphics, video.
Unknown Column types - Identify columns when not know type of column.
User Define data types - Define your own data type that are inherit from predefined base data type.

Scalar Data Types

DATA TYPE DESCRIPTION
NUMERIC Numeric values on which arithmetic operations are performed
CAHARACTER Alphanumeric values that represent single characters or strings of characters.
Boolean   Logical values on which logical operations are performed.
Datetime Dates and times.

NUMERIC DATA TYPES

DATA TYPE DESCRIPTION, Storage(Maximum)
Storage Range : Precision range(p) : 1 to 38 and Scale range(s) : -84 to 127
Number Subtypes : This sub type defines different types storage range.
Sub Data Types   Max. Precision Description
  INTEGER 38 digits
INT 38 digits
SMALLINT 38 digits   This data types are used to store
NUMBER(p,s) DEC 38 digits    fixed decimal points.we can use
DECIMAL 38 digits according to our requirements.
NUMERIC 38 digits
REAL 63 binary digits
  DOUBLE PRECISION   126 binary digits
  FLOAT   126 binary digits
...................................................................................................................................................................
BINARY_INTEGER data type store signed integer's value.
Note : BINARY_INTEGER values require less storage space compare of
NUMBER data type values.
  Storage Range : from -2147483647 to 2147483647
   BINARY_INTEGER Subtypes : This sub type define constraint to store a
value.
Sub Data Types Description
  NATURAL NATURAL/POSITIVE data type prevent to store
   POSITIVE negative value, allow only positive values.
BIANRY_INTEGER   ...............................................................................................................................
NATURALN   NATURALN/POSITIVEN data type prevent to assign a
POSITIVEN NULL value.
..............................................................................................................................
  SIGNTYPE   SIGNTYPE allow only -1, 0, and 1 values.
...................................................................................................................................................................

Character Data Types

DATA TYPE DESCRIPTION Storage(Max)

CHAR CHAR data type used to store character data within 32767 bytes

predefined length.

VARCHAR2 VARCHAR2 data type used to store variable strings 32767 bytes

data within predefined length.

NCHAR NCHAR data type used to store national character 32767 bytes

data within predefined length.

NVARCHAR2 NVARCHAR2 data type used to store Unicode string 32767 bytes

data within predefined length.

RAW The RAW data type used to store binary data such 32767 bytes

as images, graphics etc.

...................................................................................................................................................................

Boolean Data Types

DATA TYPE DESCRIPTION

Boolean   Boolean data type stores logical values.Boolean data type store either
TRUE or FALSE. Also store NULL, Oracle treats NULL as an unassigned
boolean variable.

...................................................................................................................................................................

Date/Time Data Types

Date/time variable can holds value, we can say date/time data type.PL/SQL automatically converts character value in to default date format ('DD-MM-YY') value. Following are the Date/Time data types in PL/SQL:

DATA TYPE DESCRIPTION
DATE   DATE data type stores valid date-time format with fixed length.
Starting date from Jan 1, 4712 BC to Dec 31, 9999 AD.
...................................................................................................................................................................
  TIMESTAMP data type stores valid date (year, month, day) with time (hour,
minute, second).
TYPE TIMESTAMPTYPE
1. Syntax : TIMESTAMP [ (fractional_seconds_precision) ]
Example : TIMESTAMP '2014-04-13 18:10:52.124'
fractional_seconds_precision optionally specifies the number of digits
in the fractional part of the second precision.
TIMESTAMP 2. Syntax : TIMESTAMP [ ( fractional_seconds_precision) ] WITH
TIME ZONE
Example : TIMESTAMP '2014-04-13 18:10:52.124 +05:30'
WITH TIME ZONE specify the UTC time zone.
3. Syntax : TIMESTAMP [ ( fractional_seconds_precision) ] WITH
LOCAL TIME ZONE
Example : COL_NAME TIMESTAMP(3) WITH LOCAL TIME ZONE ;
WITH LOCAL TIME ZONE specifies when you insert values into the
database column, value is stored with the time zone of the database.
...................................................................................................................................................................
In PL/SQL datetime data type or interval data type fields values show the valid values for each field.

Field Name Valid Value   Valid Interval Value

  YEAR -4712 to 9999   Integer Value exclude 0
  MONTH   01 to 12 0 to 11
  DAY   01 to 31   Integer Value exclude 0
  HOUR   00 to 23   0 to 23
  MINUTE   00 to 59   0 to 59
  SECOND   00 to 59.9(n) here n is precision 0 to 59.9(n)
of time fractional seconds

...................................................................................................................................................................

LOB Types

LOB data types used to store large objects such as image, video, graphics, text or audio. Maximum size is up to 4 Gigabytes. Following are the LOB data types in PL/SQL:

DATA TYPES DESCRIPTION STORAGE(Max.)
BFILE BFILE data type is used to store large binary System-dependent. Cannot
objects into Operating System fileoutside the   exceed 4 gigabytes (GB).
database.
BLOB BLOB data type is same as BFILE data type 8 to 128 terabytes (TB)
used to store unstructured binary object into
Operating System file.
CLOB CLOB data type is used to store large blocks of   8 to 128 TB
character data into Database. Store single byte
and multi byte character data.
NCLOB NCLOB data type to store large blocks of NCHAR   8 to 128 TB
data into Database. Store single byte and multi
byte  character data.
...................................................................................................................................................................

Unknown Column Types

PL/SQL this data type is used when column type is not know.
DATA TYPES DESCRIPTION
%Type This data type is used to store value unknown data type column in a table.
Column is identified by %type data type.
Eg. customer.name%type
customer is table, name is a unknown data type column and %Type is data
type to hold the value.
%RowType This data type is used to store values unknown data type in all columns in a
table. All columns are identified by %RowType datatype.
Eg. emp%rowtype
emp name is table,
all column type is %rowtype.
...................................................................................................................................................................

User-Defined SubTypes

PL/SQL gives you the control to create your own sub data type that are inherit from predefined base type.A subtype has the same valid operations as its base type, but only a subset of its valid values.
Defining Subtypes
SUBTYPE subtype_name is base_type [(constraint)] [NOT NULL] ;
Following example, predefined data type inherit from CHARACTER and INTEGER data type to make a new sub types,
SUBTYPE CHARACTER IS CHAR ;
SUBTYPE INTEGER IS NUMBER(38,0);
The following program illustrates defining and using a user-defined subtype:

PL/SQL Variable Declaration & Variable Scope

A variable is nothing but a name given to a storage area that our programs can manipulate.PL/SQL variable declaration always specifies the variable name, data type of variable and size. Variable names are not case-sensitive.We can also specifies initial value of declared variables.

Variable Declaration Syntax
PL/SQL variables must be declared in the declaration section or in a package as a global variable.
The syntax for declaring a variable is:
variable_name [CONSTANT] datatype[SIZE] [NOT NULL] [:= | DEFAULT initial_value]
Where, variable_name is a valid identifier in PL/SQL,datatype must be a valid PL/SQL data type or any user defined data type,Size is an optional specification of data type size to hold the maximum size value, NOT NULL is an optional specification of the variable value can't be accept NULL, each variable declaration is terminated by a semicolon.
Some valid variable declarations along with their definition are shown below:
sales number(10, 2);
pi CONSTANT double precision := 3.1415;
name varchar2(25);
address varchar2(100);
When you provide a size, scale or precision limit with the data type, it is called a constrained declaration. Eg : sales number(10, 2);

Initializing Variable in PL/SQL
If we declare a variable, PL/SQL assigns it a default value of NULL. If you want to initialize a variable with a value other than the NULL value, you can do so during the declaration, using either of the following:

The DEFAULT keyword
The assignment operator

For example: eno number(5) not null :=2;

ename varchar2(15) default 'Hello World!' ;

We can also specify that a variable should not have a NULL value using the NOT NULL constraint. If we use the NOT NULL constraint, we must explicitly assign an initial value for that variable.

Variable Scope In PL/SQL
PL/SQL variable scope is identified the region range which you can reference the variable. PL/SQL have two type scopes local scope and global scope.
LOCAL VARIABLE : variables declared in an inner block and not accessible to outer blocks.
GLOBAL VARIABLE : Where as variables declared in a outer block and can be referencing by itself
in inner blocks.
Variable Scope Example:

PL/SQL Constants Variable

We can declare PL/SQL constants along with the value and can not change them throughout the program block. A constant declaration specifies its name, data type, and value, and allocates storage for it.

Constant Declaration Syntax

constant_name CONSTANT datatype [size] := value ;

Constant_name is a predefined name of the constant (similar to a variable name).
CONSTANT is a resvered keyword.
Data type is a valid PL/SQL data type.
Size is an optional specification of data type. It holds maximum capacity value.
Value must be assigned to a constant when it is declared. You can not change it later.
Each constant declaration is terminated by a semicolon.

Constant Example :

Coding Hub

Pages

Data Types , Variables & Constants in Pl/SQl

Boolean Data Types

PL/SQL Constants Variable

No comments: