All for Joomla All for Webmasters

PL/SQL Constants

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.

Let’s take an example to explain it well:

Suppose, you have to write a program which will increase the salary of the employees upto 30%, you can declare a constant and use it throughout the program. Next time if you want to increase the salary again you can change the value of constant than the actual value throughout the program.

Syntax to declare a constant:

  1. constant_name CONSTANT datatype := VALUE;
  • Constant_name:it is the name of constant just like variable name. The constant word is a reserved word and its value does not change.
  • VALUE: it is a value which is assigned to a constant when it is declared. It can not be assigned later.

Example of PL/SQL constant

Let’s take an example to explain it well:

  1. DECLARE
  2.    — constant declaration
  3.    pi constant number := 3.141592654;
  4.    — other declarations
  5.    radius number(5,2);
  6.    dia number(5,2);
  7.    circumference number(7, 2);
  8.    area number (10, 2);
  9. BEGIN
  10.    — processing
  11.    radius := 9.5;
  12.    dia := radius * 2;
  13.    circumference := 2.0 * pi * radius;
  14.    area := pi * radius * radius;
  15.    — output
  16.    dbms_output.put_line(‘Radius: ‘ || radius);
  17.    dbms_output.put_line(‘Diameter: ‘ || dia);
  18.    dbms_output.put_line(‘Circumference: ‘ || circumference);
  19.    dbms_output.put_line(‘Area: ‘ || area);
  20. END;
  21. /

After the execution of the above code at SQL prompt, it will produce the following result:.

  1. Radius: 9.5
  2. Diameter: 19
  3. Circumference: 59.69
  4. Area: 283.53
  5. Pl/SQL procedure successfully completed.

PL/SQL Literals

Literals are the explicit numeric, character, string or boolean values which are not represented by an identifier. For example: TRUE, NULL, etc. are all literals of type boolean. PL/SQL literals are case-sensitive. There are following kinds of literals in PL/SQL:

  • Numeric Literals
  • Character Literals
  • String Literals
  • BOOLEAN Literals
  • Date and Time Literals

Example of these different types of Literals:

Literals Examples
Numeric 75125, 3568, 33.3333333 etc.
Character ‘A’ ‘%’ ‘9’ ‘ ‘ ‘z’ ‘(‘
String Hello makeforsure!
Boolean TRUE, FALSE, NULL etc.
Date and Time ’26-11-2002′ , ‘2012-10-29 12:01:01’
PinIt
submit to reddit

Leave a Reply

Top