PL SQL

Hi Everyone, Today's topic is datatypes used in PL/SQL.

Each value in PL/SQL such as a constant, variable and parameter has a data type that determines the storage format, valid values, and allowed operations.

Types of Datatypes in PL/SQL:

1. Scaler : store single values such as

a. Number

b. Boolean

c. Character

d. DateTime

2. Composite : store multiple values such as

a. Record

b. Collection

Note that PL/SQL scalar data types include SQL data types and its own data type such as Boolean.

Variables in PL/SQL :

In PL/SQL, a variable is named storage location that stores a value of a particular data type.

The value of the variable changes through the program.

Before using a variable, you must declare it in the declaration section of a block.

How to Declare Variables in PL/SQL:

Syntax:

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

Naming rules for PL/SQL variables

The variable in PL/SQL must follow some naming rules like other programming languages.

• The variable_name should not exceed 30 characters.

• The name of the variable must begin with ASCII letter. The PL/SQL is not case sensitive so it could be either lowercase or uppercase. For example: v_data and V_DATA refer to the same variables.

• You should make your variable easy to read and understand, after the first character, it may be any number, underscore (_) or dollar sign ($).

• NOT NULL is an optional specification on the variable.

Initializing Variables in PL/SQL

Evertime you declare a variable, PL/SQL defines a default value NULL to it. If you want to initialize a variable with other value than NULL value, you can do so during the declaration, by using any one of the following methods.

1 The assignment(:=) operator

counter binary_integer := 0;  

2. The DEFAULT keyword

greetings varchar2(20) DEFAULT 'Hello JavaTpoint';    

Example:

DECLARE 
a integer := 30;  
b integer := 40;  
 c integer;  
f real;  
BEGIN 
 c := a + b;  
 dbms_output.put_line('Value of c: ' || c);  
 f := 100.0/3.0;  
 dbms_output.put_line('Value of f: ' || f);  
END;  

Output:

Statement processed.
Value of c: 70
Value of f: 33.33333333333333333333333333333333333333

Constants in PL/SQL:

A constant is a value used in a PL/SQL block that remains unchanged throughout the program. It is a user-defined literal value. It can be declared and used instead of actual values.

Syntax:

constant_name CONSTANT datatype := VALUE;  

Example:

DECLARE    
 num   CONSTANT NUMBER   := 45; -- days     
 bol CONSTANT BOOLEAN  := FALSE;  
BEGIN
NULL;
 END;

Thank you for reading.

Ask your Doubt or Query in Comment Sections.