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 FAQs: |
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 |
| 2 | 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?
Answer: NOT
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 🙂