Variables ,Constant and Datatypes

 

Ø  In PL/SQL, a variable is a named storage location that holds a value of a specific data type.

Ø  Variables are used to store and manipulate data during the execution of a PL/SQL block.

 

PL/SQL variables are declared in the DECLARE section of a block using the syntax:

 

variable_name  data_type [:= initial_value];

 

a)      Scalar Variables

Hold a single value at a time (e.g., NUMBER, VARCHAR2, DATE).

Numeric Data Types in PL/SQL

Numeric Data Types in PL/SQL :

Data Type	Description	Example
NUMBER(p,s)	Stores fixed or floating-point numbers, where p is the total number of digits, and s is the number of digits after the decimal point.	NUMBER(10,2) := 12345.67;
INTEGER	Stores whole numbers (same as NUMBER(38,0)).	INTEGER := 100;
SMALLINT	Stores smaller whole numbers (same as NUMBER(38,0)).	SMALLINT := 500;
FLOAT(p)	Floating-point number, where p defines binary precision (max 126).	FLOAT(10) := 123.456;
REAL	A subtype of FLOAT that provides a default precision.	REAL := 987.65;
DECIMAL(p,s)	Stores fixed-point numbers (same as NUMBER(p,s)).	DECIMAL(8,2) := 1000.50;
NUMERIC(p,s)	Same as DECIMAL(p,s), ANSI standard type.	NUMERIC(10,3) := 1234.567;
BINARY_INTEGER	Stores signed integers; used in older PL/SQL versions (deprecated, use PLS_INTEGER).	BINARY_INTEGER := -50;
PLS_INTEGER	Optimized for performance; stores signed integers.	PLS_INTEGER := 1000;
SIMPLE_INTEGER	Subtype of PLS_INTEGER, faster with no overflow checking.	SIMPLE_INTEGER := 25;

 

 

 

 

 

 

 

Character Data Types in Oracle (PL/SQL) – One Table:

Data Type	Description	Max Size	Storage Type
CHAR(n)	Fixed-length character string. If the value is shorter than n, it is padded with spaces.	2000 bytes	Fixed-length
VARCHAR2(n)	Variable-length string. Stores only the actual length of the value (more efficient than CHAR).	4000 bytes (in CDB/PDB: up to 32,767 in 12c+)	Variable-length
NCHAR(n)	Fixed-length Unicode string, used for multilingual data. Similar to CHAR but supports Unicode (UTF-16).	2000 bytes	Fixed-length (Unicode)
NVARCHAR2(n)	Variable-length Unicode string. Similar to VARCHAR2 but supports Unicode (UTF-16).	4000 bytes (in CDB/PDB: up to 32,767 in 12c+)	Variable-length (Unicode)
CLOB (Character Large Object)	Stores large character data (documents, XML, logs).	128 TB	LOB Storage
LONG (Deprecated)	Stores variable-length character data, up to 2GB (replaced by CLOB).	2 GB	LOB Storage (Obsolete)

 

🔹 CHAR vs VARCHAR2 in Oracle

1. CHAR

• Fixed-length character data type.

• Pads spaces to the right if the value is shorter than the defined length.

• Storage is always the defined size.

• Best for storing data with consistent length (e.g., country codes, status flags).

Example:

CREATE TABLE demo_char (
    col1 CHAR(5)
);

INSERT INTO demo_char VALUES ('A');
SELECT col1, LENGTH(col1), DUMP(col1) FROM demo_char;

Result:

• Stored as 'A ' (with 4 spaces).

• LENGTH(col1) = 5.

---

2. VARCHAR2

• Variable-length character data type.

• Stores only the actual entered characters, no extra space padding.

• More space-efficient for values of varying length.

• Best for storing dynamic strings (e.g., names, addresses).

Example:

CREATE TABLE demo_varchar (
    col1 VARCHAR2(5)
);

INSERT INTO demo_varchar VALUES ('A');
SELECT col1, LENGTH(col1), DUMP(col1) FROM demo_varchar;

Result:

• Stored as 'A'.

• LENGTH(col1) = 1.

---

🔹 Key Differences

Feature	CHAR	VARCHAR2
Storage Type	Fixed-length	Variable-length
Space Usage	Always reserves full size	Uses only needed space
Padding	Pads with spaces	No padding
Performance	Slightly faster for fixed-length fields	Better for variable-length fields
Max Size (SQL)	2000 bytes	4000 bytes

 

       b) Composite Variables

Used to hold multiple related values.
Records (like a table row)

Collections (Arrays, Nested Tables, VARRAYs)

       c) Reference Variables (REF CURSOR)

·       Used for dynamic queries in PL/SQL.

       d) Boolean Variables

·       Hold TRUE, FALSE, or NULL values.

e.g. v_bonus_flag BOOLEAN := FALSE;

Variable Scope in PL/SQL

• Local Variables: Declared inside a PL/SQL block, only accessible within that block.
• Global Variables: Declared in a package and accessible across different procedures and functions.

 

Constant :

Constants are declared using CONSTANT keyword.

Value remains same throughout the program .