In this article, we will discuss PL/SQL constants and literals in Oracle PL/SQL tutorial.
If you missed the previous article of Variables in PL/SQL, then please click here.
let’s start now …
|What’s inside: |
Introduction to Constant
Declaring a Constant
The PL/SQL Literals
Types of Literals
What is PL/SQL constants in Oracle
A PL/SQL constants hold a value that does not change during the whole execution of the program. The declaration of a constant specifies its name, data type, and value, and allocates storage for it.
Declaring a PL/SQL Constants
vconstant_name CONSTANT datatype := VALUE;
- constant_name is that we are declaring.
- The CONSTANT word is restrained, and it ensures that the value does not change throughout the program.
- VALUE – The value which must be assigned to a constant when it is declared. You can’t give it later.
PL/SQL constants examples:
DECLARE co_payment_term CONSTANT NUMBER := 45; -- days co_payment_status CONSTANT BOOLEAN := FALSE; BEGIN NULL; END;
If you try to change the co_payment_term in the execution section, PL/SQL will give an error, for example:
DECLARE co_payment_term CONSTANT NUMBER := 40; -- days co_payment_status CONSTANT BOOLEAN := FALSE; BEGIN co_payment_term := 30; -- error END;
Here is the error message:
PLS-00363: expression 'CO_PAY_TERM' cannot be used as an assignment target
The PL/SQL Literals
Oracle PL/SQL, a literal is an equivalent as a constant. PL/SQL, literals are case-sensitive. A PL/SQL literal is a value that is not represented by an identifier; it is simply a value.
PL/SQL supports the following kinds of literals −
- Numeric Literals
- Character Literals
- String Literals
- BOOLEAN Literals
- Date and Time Literals
|S.No.||Literal Type & Example|
|1||Numeric Literals can be up to 38 digits. It literals can be either positive or negative numbers; also, it can be integers or floats.|
e.g. 050 78 -14 0 +32767
6.6667 0.0 -12.0 3.14159 +7800.00
6E5 1.0E-8 3.14159e0 -1E38 -9.5e-3
String Literals are always enclosed by single quotes (‘).e.g. ‘Hewlett Packard’ ’28-MAY-03
|3||Character literals are string literals consisting of single characters.|
e.g., ‘A’ ‘%’ ‘9’ ‘ ‘ ‘z’ ‘(‘
|4||BOOLEAN Literals are predetermined constants. The preferences that can be allocated to these data types are: TRUE, FALSE, and NULL.|
|5||Date and Time Literals Date and time are enclosed in single quotes (‘).e.g.DATE ‘1978-12-25’;|
When dealing with date/time values, you will want to use the TO_DATE function to convert a literal to a date.
e.g. SELECT TO_DATE(‘2015/04/30’, ‘yyyy/mm/dd’) FROM dual;
FAQs in PL/SQL Constants:
1.The PL/SQL is a completely portable, ………………. transaction processing language.
Answer: The Oracle PL/SQL is a completely portable, high-performance transaction processing language.
2. How many types Literals are present in PL/SQL?
Answer: Five types of Literals present in PL/SQL.
3. ………….is not a PL/SQL unit.
Answer: Table does not a PL/SQL unit.
4. Which operator has the highest precedence among the following −AND, NOT, OR?
5. What is a PL/SQL package?
Answer: A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions.
6. Which data type is not allowed in PL/SQL?
Answer: A Collection datatype is not allowed in PL/SQL record.
In this article, we are discussed about the Constant in PL/SQL and also different types of Literals in PL/SQL. It is the basics concept of Oracle PL/SQL Tutorials. In the next article, we will discuss the different operators of PL/SQL.
Thank You 🙂