In this post we will discuss about PLSQL Constants in Oracle, How do you declare a constant variable in PL SQL, how to Declare a constant in Oracle stored procedure
Oracle PLSQL Constant
- Oracle PLSQL constant is just like any other procedural language and Its value does not change during the execution of the program
- Constants must be declared for use in procedural and SQL code, although the datatypes available in SQL are only a subset of those available in PL/SQL.
How do you declare a constant variable in PL SQL
- All constants must be declared before they are referenced.
- Declaration involves the name of the constant followed by its data type.
- Remember that each constant must be declared on a separate line and multiple constants cannot be defined in the same statement.
- All statements must end with a semicolon
- The declarations of constant definitions must contain the CONSTANT keyword and must be assigned a value as part of the definition. Subsequent attempts to assign a value to a constant will result in an error
General Syntax
variable_name CONSTANT datatype [NOT NULL := value ];
(1) variable_name is the name of the variable.
(2) datatype is a valid PL/SQL datatype.
Example of declaring Constants:
v_global constant number := 200; V_DEPT CONSTANT NUMBER (6) DEFAULT 160;
- Constants cannot be changed.
- You must initialize constants at the time of declaration.
Example as full Plsql Block
DECLARE
c_limit CONSTANT REAL := 1000.00; -- SQL data type
max_days_in_year CONSTANT INTEGER := 266; -- SQL data type
u_legend CONSTANT BOOLEAN := TRUE; -- PL/SQL-only data type
BEGIN
NULL;
END;
/
Let’s try to assign some value to the constant variable
DECLARE c_limit CONSTANT REAL := 1000.00; -- SQL data type max_days_in_year CONSTANT INTEGER := 266; -- SQL data type u_legend CONSTANT BOOLEAN := TRUE; -- PL/SQL-only data type BEGIN c_limit :=1; END; / PLS-00363: expression 'c_limit' cannot be used as an assignment target
So it has received an error if we assign any value to the constant in the begin block
PLSQL Constants are an important construct and are quite used in PLSQL Programming. I hope you this post on PLSQL Constants in Oracle, How do you declare a constant variable in PL SQL, how to Declare a constant in Oracle stored procedure
Related Articles
Oracle PLSQL Variable: Check out this post on Oracle PLSQL Variable, how to declare variable in stored procedure oracle
Oracle PLSQL records: Check out this article on the working of oracle plsql records. Also, find out the various ways to define it and assign value to it
PLSQL Tables: Check out this post for a detailed description of PLSQL Tables.How to manipulate it and work on it in Oracle PLSQL block and benefits
What is cursor in oracle : Check out this post to get a great overview of What is cursor in oracle.Details about Cursor parameter, cursor loop, cursor for an update