Oracle PL/SQL Tutorial - PL/SQL Literals

• « Previous
• Next »

A literal is an explicit numeric, character, string, or BOOLEAN value.

For example, the numeric 14 and the BOOLEAN FALSE are two literals.

Numeric Literals

We can use two types of numeric literals in arithmetic expressions: integers and reals.

An integer literal is an optionally signed whole number without a decimal point.

For example:

3   6   -14   1123   +32767 

A real literal is an optionally signed fractional number with a decimal point.

For example:

+3330.0120 .5   25.  6.6 0.0   -1.0   3.14 

Numeric literals can be written using scientific notation.

Suffix the number with an E (or e) followed by an optionally signed integer.

For example:

22E15   1.0E-7   3.14-1E38   -129.125e-13 

Example

The following code shows how to use number literal in PL/SQL code.

DECLARE 
  n NUMBER; 
BEGIN 
  n := -9.999999E-130; 
  n :=  9.999E125; 
  n := 10.0E12; 
END; 
/ 

The code above generates the following result.

Real literals can use the trailing letters f and d to specify the types BINARY_FLOAT and BINARY_DOUBLE.

  
DECLARE 
  x BINARY_FLOAT := sqrt(2.0f); 
    -- single-precision floating-point number 
  y BINARY_DOUBLE := sqrt(2.0d); 
    -- double-precision floating-point number 
BEGIN 
  NULL; 
END; 
/ 

The code above generates the following result.

Character Literals

A character literal is an individual character enclosed by single quotes (').

'A'   '%'   '7'   ' '   'a'   '(' 

PL/SQL is case sensitive for character literals.

String Literals

String literal is a sequence of zero or more characters enclosed by single quotes.

 'this is a test!' 
 'java2s.com' 
 

PL/SQL is case sensitive for string literals.

To represent an apostrophe within a string, write two single quotes.

'I''m a string, you''re not a string.' 

BOOLEAN Literals

BOOLEAN literals are the predefined values TRUE, FALSE, and NULL.

NULL stands for a unknown value.

Date and Time Literals

Datetime literals have various formats depending on the data type.

DECLARE --  w w w .ja  va  2  s  .co  m
   d1 DATE      := DATE '2020-12-25'; 
   t1 TIMESTAMP := TIMESTAMP '2002-10-22 13:01:01'; 
   t2 TIMESTAMP WITH TIME ZONE := TIMESTAMP '2002-03-31 19:26:56.66 +02:00'; 
  
   i1 INTERVAL YEAR TO MONTH := INTERVAL '3-2' YEAR TO MONTH; 
   -- Five days, four hours, three minutes, two and 2/100 seconds 
   i2 INTERVAL DAY TO SECOND := INTERVAL '5 04:03:02.02' DAY TO SECOND; 
 
BEGIN 
  NULL; 
END; 
/ 

The code above generates the following result.

• Next »
• « Previous