PL/SQL Tutorial - Lesson 2: Variables and Constants

In this lesson we will review variables and how to use then in a PL/SQL block.

PL/SQL lets you to declare both variables and constants. Variables can have any SQL data type, such as CHAR, DATE, or NUMBER, or a PL/SQL-only datatype such as BOOLEAN or PLS_INTEGER. You can also assign an initial or default value during the variable declaration. The syntax for declaring variables in PL/SQL is:

 identifier [CONSTANT] datatype (length optional)  [NOT NULL] [:= | DEFAULT expr];

Identifier
The identifier allows you to assign a name to recognize your variable. Some recommendations when choosing the name of a variable are:

• First character of the variable name should be a letter
• Variable names can include letters, numbers, or special characters such as $, _, or #
• Maximum length should be 30 characters
• Reserved words should not be used as variable name
• Avoid the use of columns names
• Use meaningful words, that can provide an insight the content to be stored in the variable
• Try to follow a naming convention
• Add a prefix indicating the type of identifier, for instance:

Datatype

The datatype determines the storage format, constraints, valid range of values, and operations that can be performed on it. Variables can be any predefined scalar datatype such as BOOLEAN, NUMBER or VARCHAR2. For instance:

v_employee_name         VARCHAR2(25);
v_employee_age          NUMBER;
v_employee_status       BOOLEAN; 
v_employee_hire_date    DATE;

%TYPE

Also, we can use datatypes from previously declared collection, cursor variable, field, object, record, database column, or variable. The benefit of using this type of variables is when you load values from queries, in this case you don’t need to know the original data type of the column. Also if the column definition changes you don’t have to go back and change your data in the PL/SQL script.

v_employee_last_name    EMPLOYEES.LAST_NAME%TYPE;
v_employee_email        EMPLOYEES.EMAIL%TYPE;
v_employee_hire_date    EMPLOYEES.HIRE_DATE%TYPE;

%ROWTYPE

In addition to %TYPE, we can use variables that represents a record that can hold a row from a database table or a cursor. Fields in the record have the same names and datatypes as columns in the row.

Assigning values
You can initialize your variables by using the assignment operator (:=)

v_employee_weekly_hours  NUMBER  := 40;
v_today                                 DATE    := SYSDATE;
v_greeting                             VARCHAR2(15):= 'Hello World';

CONSTANT

Denotes the declaration of a constant. Once initialized it cannot be changed. If you try to assign a new value during the executable part, you will get an error.

c_earth_circunference CONSTANT NUMBER:=24901;

NOT NULL
This parameter prevents the program from assigning a null value to a variable or constant

v_employee_weekly_hours NUMBER NOT NULL := 40; CONSTANT NUMBER:=24901;

Putting all together

Now that we understand how PL/SQL works and how to declare variables, let’s put everything together and see how it works.

Before we start just some additional considerations, for the following examples we have run the following command to be able to send information to the output script window and visualize the result of our examples.

Example 1
In the following example an anonymous block has been created. In this block, two variables are declared and initial values are assigned. Next, this variables are accessed in the executable section. and we print the output using the PUT LINE procedure.
In addition, notice in the first line that we execute the SET SERVEROUTPUT ON command in order to use later the DBMS_OUTPUT package to print the values stored in the variables.

Example 2:
String delimiters. What if we need to add apostrophes or single quotation marks in our strings. Well, there are several ways to handle this.
1.Double the quotation mark: You must double the quotation mark, as in the following example. The quotation mark acts as the escape character.
 v_message_1      VARCHAR(64):= 'Happy Father''s day!!';
2.Use q’ notation. You can use a character that is not not present in the string as delimiter, the character if placed after the q’ notation. In the following examples, first we use wildcard as delimiter and the second one uses brackets:
  v_message_2      VARCHAR(64):= q'*Happy Father's day!!*';
  v_message_2      VARCHAR(64):= q'[Happy Father's day!!]';

Resources:

• You can download the examples from here