PL/SQL Tutorial: Chapter 2

In this chapter, you’ll learn:

• What variables are and how to use them in PL/SQL
• Different data types of these variables
• Operators and how PL/SQL handles them

Our Previous Code

The code from the previous lesson looked like this:

BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;

It wrote the text “Hello World” to the screen.

What if you wanted to change that text?

You can just change what’s inside the function parameter:

BEGIN
DBMS_OUTPUT.PUT_LINE('I am learning');
END;

This will display the changed text as an output when you run the program.

Statement processed.
I am learning

One way to improve this code is to store the text we want to output in a variable. PL/SQL supports the use of variables, just like any other programming language.

A variable is a named area in memory that stores the data you tell it to store. They are used to separate the value of something from the treatment of that value. This means you can easily change the value of the output text without altering the function. It also improves readability of the code.

First, we need to learn about the declarative section.

The Declarative Section in PL/SQL

Earlier in this tutorial we mentioned there were several sections of a PL/SQL program:

• Declarative section: this is where variables are declared
• Executable section: this is the code that is run as part of the program goes.
• Exception section: this defines what happens if something goes wrong.

The executable section is defined with BEGIN and END.

The declarative section is defined with the keyword DECLARE. It goes before the BEGIN keyword:

DECLARE
BEGIN
DBMS_OUTPUT.PUT_LINE('I am learning');
END;

What is the DECLARE section for? We use it to declare variables in PL/SQL. A variable has a name, a data type, and a value.

Let’s declare our first variable. We’ll call it “l_text”. The lowercase L is used as a prefix to indicate it’s a local variable (which means it’s only relevant for the program we are running). It’s not a requirement to add this prefix, but it’s recommended by many developers including Steven Feuerstein.

Our code looks like this so far:

DECLARE
l_text
BEGIN
DBMS_OUTPUT.PUT_LINE('I am learning');
END;

We then have to specify the data type of this l_text variable. All variables need a data type so Oracle knows what kind of data it contains.

We want to store the value that we’re going to use in the PUT_LINE function, which is a text value, so we should use the VARCHAR2 data type. Declaring a VARCHAR2 data type is the same in PL/SQL as specifying a column type in SQL, so we need a maximum length. Let’s use 50 for this example.

Our code then looks like this:

DECLARE
l_text VARCHAR2(50)
BEGIN
DBMS_OUTPUT.PUT_LINE('I am learning');
END;

We’re almost there. Now, we need to specify what l_text is equal to. We do this by adding a colon, an equals sign, then the new value, then a semicolon. We’ll use the value of “Hello World”. Our code looks like this:

DECLARE
l_text VARCHAR2(50) := 'Hello World';
BEGIN
DBMS_OUTPUT.PUT_LINE('I am learning');
END;

Our code now has the variable called l_text, which is of type VARCHAR2 with a maximum of 50 characters. It has been set to the value of “Hello World”.

Now, add this code to your SQL worksheet.

Run the code using the Run button. The output looks like this:

Statement processed.
I am learning

Hang on… we added that line about the variable, but the output still shows the word “I am learning”? How did that happen?

This has happened because even though we added the variable, we didn’t change to code to use that variable. The parameter inside the PUT_LINE function still says “I am learning”.

Let’s change that now. Remove the text “I am learning”, and the single quotes, and put the variable name l_text in there instead:

DECLARE
l_text VARCHAR2(50) := 'Hello World';
BEGIN
DBMS_OUTPUT.PUT_LINE(l_text);
END;

Now, run this code:

Statement processed.
Hello World

You’ll see the words “Hello World” in the output section again. You can now change the value of l_text to whatever you want, and it will be written to the screen.

Why is this useful? It will help us later when we work on more complicated programs, as it improves readability and allows us to manipulate these values in the future.

Indenting

As we add more and more code to our PL/SQL program, it can get harder to read. LiveSQL and many IDEs highlight the keywords and text values in different colours, but there’s more we can do to improve readability.

A great way to improve readability is to indent your lines of code. This is commonly done in other programming languages and is also a good idea in PL/SQL. Whether you use tabs or spaces, indent two, three, or four characters, indenting your code makes it easier to read and work with.

Traditionally, DECLARE, BEGIN, and END keywords are all aligned to the left and not indented. Code in each of those sections is indented. You can do this now with your code:

DECLARE
  l_text VARCHAR2(50) := 'Hello World';
BEGIN
  DBMS_OUTPUT.PUT_LINE(l_text);
END;

In this tutorial, I’ll be indenting two spaces, but it’s up to you how much you indent. As long as it’s consistent!

You’ll also notice that I use spaces before and after operators, such as the := symbols. This is not required, but I think it makes the code more readable. You’ll find I add spaces in many places in the code for this reason.

Defining Variables Without Assigning Them

In our earlier code, we declared a variable (added a variable and a data type) and assigned a value to it.

However, in PL/SQL, we can declare variables without assigning a value to it at the same time. This is useful if you want to use a variable for something but don’t know what the value should be at the time of declaring the variable.

You can declare the name and type of the variable without giving it a value, like this:

DECLARE
  l_text VARCHAR2(50);
BEGIN
  DBMS_OUTPUT.PUT_LINE(l_text);
END;

As long as the variable has a name and a data type, and ends with a semicolon, it will work. However, if we run this code, this is what we get:

Statement processed.

There is no output. This is because the variable of l_text is not set, so the PUT_LINE function does not write anything.

We need to set the variable to something inside our program. We can do this by specifying the variable name, then the colon and equals sign, then the variable. This needs to be done inside the BEGIN section of the code:

DECLARE
  l_text VARCHAR2(50);
BEGIN
  l_text := 'Hello World';
  DBMS_OUTPUT.PUT_LINE(l_text);
END;

This new code has assigned the value of “Hello World” to the variable l_text. The output of this code now looks like this:

Statement processed.
Hello World

The output now includes the text, because we have assigned it to the variable that was used, before the PUT_LINE statement is run.

If we assign the variable after the PUT_LINE statement, then the output is unchanged, because the PUT_LINE statement is called before the value is changed.

DECLARE
  l_text VARCHAR2(50);
BEGIN
  l_text := 'Hello World';
  DBMS_OUTPUT.PUT_LINE(l_text);
  l_text := 'Something else';
END;

The output is:

Statement processed.
Hello World

So, when you declare variables, you can either assign them at the time you declare them, or assign them later.

Number Data Types

Text values aren’t the only variables you can use in PL/SQL. You can use any data type available in Oracle SQL, as well as a few that are PL/SQL specific.

Let’s look at some number data types. We can create a variable with the type of NUMBER, and use PUT_LINE to write it to the screen.

This code does exactly that:

DECLARE
  l_mynumber NUMBER(8);
BEGIN
  l_mynumber := 12;
  DBMS_OUTPUT.PUT_LINE(l_mynumber);
END;

If we run this code, we get this output:

Statement processed.
12

The PUT_LINE function accepts numbers as well as text. If we want to change the value that is used in this program, we just replace the 12 with our new number.

DECLARE
  l_mynumber NUMBER(8);
BEGIN
  l_mynumber := 491;
  DBMS_OUTPUT.PUT_LINE(l_mynumber);
END;

The output is:

Statement processed.
491

Concatenation

In our earlier example, we set the value of a variable and used that as the output. Displaying the number 12 or 491 wasn’t that useful by itself. What if we wanted to add more text to the output? Instead of showing “491”, what if we wanted to say “The number you chose was 491”?

We can do that using a feature called concatenation.

What is concatenation?

Concatenation is the ability to join two values together into one. It’s often used to combine values into a single output. Many programming languages support it using different characters, such as &, || or a . character. Concatenation can also be done with functions.

In PL/SQL, concatenation is done with a double pipe character ||. You specify a value, then a double pipe, then another value, and the two values are then treated as one. This is helpful in many situations, one of them being the PUT_LINE function.

To add the text “The number you chose was” to the output, we can concatenate that text with the l_mynumber variable:

DECLARE
  l_mynumber NUMBER(8);
BEGIN
  l_mynumber := 491;
  DBMS_OUTPUT.PUT_LINE('The number you chose was ' || l_mynumber);
END;

Running this code gives us this result:

Statement processed.
The number you chose was 491

The full output shows the text we added along with the value of the variable.

One thing to keep in mind with concatenation is spaces. I’ve added a space after the word “was” in the text value. Without the space, the number will be placed right next to the word “was”, because concatenation does not automatically add spaces:

DECLARE
  l_mynumber NUMBER(8);
BEGIN
  l_mynumber := 491;
  DBMS_OUTPUT.PUT_LINE('The number you chose was' || l_mynumber);
END;

The output is:

Statement processed.
The number you chose was491

Concatenation is very helpful when it comes to working with variables and constructing helpful output, as we’ve seen above.  

Adding Numbers

You can add numbers together in PL/SQL. This is done using the + symbol between two numbers, just like other programming languages and in SQL. Let’s say you wanted to add two numbers together in PL/SQL. Your code could look like this:

DECLARE
  l_mynumber NUMBER(8);
BEGIN
  l_mynumber := 4 + 9;
  DBMS_OUTPUT.PUT_LINE('The number you chose was ' || l_mynumber);
END;

This code sets the value of l_mynumber to the value of 4 plus 9. This is set to 13, which is shown in the output if you run the code.

Statement processed.
The number you chose was 13

You can use different number values and add them together in this way.  

Subtracting Numbers

If you want to subtract numbers in PL/SQL, you can use the – symbol to subtract one number from the other. The following code will subtract 5 from the number 100 and display the output.

DECLARE
  l_mynumber NUMBER(8);
BEGIN
  l_mynumber := 100 - 5;
  DBMS_OUTPUT.PUT_LINE('The number you chose was ' || l_mynumber);
END;

The output looks like this:

Statement processed.
The number you chose was 95

Multiplying Numbers

You can multiply numbers in PL/SQL by using the * symbol. This is used in many programming languages for multiplication. It’s used in SQL for selecting all columns, but if it’s used in an expression in SQL or in PL/SQL, it’s treated as a multiplication. The following code will multiply 20 by 4 and display the output.

DECLARE
  l_mynumber NUMBER(8);
BEGIN
  l_mynumber := 20 * 4;
  DBMS_OUTPUT.PUT_LINE('The number you chose was ' || l_mynumber);
END;

The output looks like this:

Statement processed.
The number you chose was 80

Dividing Numbers

Finally, PL/SQL allows you to divide numbers by using the / character. The following code will divide 100 by 4 and display the result.

DECLARE
  l_mynumber NUMBER(8);
BEGIN
  l_mynumber := 100 / 4;
  DBMS_OUTPUT.PUT_LINE('The number you chose was ' || l_mynumber);
END;

The output looks like this:

Statement processed.
The number you chose was 25

If the number does not divide evenly, the value will still be calculated and displayed, but may not look neat.

DECLARE
  l_mynumber NUMBER(8);
BEGIN
  l_mynumber := 100 / 7;
  DBMS_OUTPUT.PUT_LINE('The number you chose was ' || l_mynumber);
END;

The output looks like this:

Statement processed.
The number you chose was 14

The reason this shows 14 is because the data type is declared as an 8-digit number. No precision is specified so it uses a precision of 0, rounding down to the nearest number. It shows 100 divided by 7 which is 14.29, rounded down to 14.

Constants

In the code so far, we’ve declared variables and assigned them. We’ve also declared variables and assigned them later in the PL/SQL program. This is an advantage of using variables – you can update the value of them in your program.

For example, let’s say you had this PL/SQL program that calculated the circumference of a circle. The circumference of a circle is 2πr, or 2 times the radius times pi. The value of pi is approximately equal to 3.14159.

Our code may look like this:

DECLARE
  l_radius NUMBER(4, 3);
BEGIN
  l_radius := 8;
  DBMS_OUTPUT.PUT_LINE('The circumference is ' || 2 * l_radius * 3.14159);
END;

Our output looks like this:

Statement processed.
The circumference is 50.26544

This code includes a variable called l_radius which is the radius of our circle. We set this value to 8 inside our code, and then output the value of the circumference as 2 * l_radius * 3.14159.

Let’s use our knowledge of variables to declare a variable for the circumference, set that to the calculated value, and use that variable in the output.

DECLARE
  l_radius NUMBER(4, 3);
  l_circumference NUMBER(10, 3);
BEGIN
  l_radius := 8;
  l_circumference := 2 * l_radius * 3.14159;
  DBMS_OUTPUT.PUT_LINE('The circumference is ' || l_circumference);
END;

Our output looks like this:

Statement processed.
The circumference is 50.265

This is good, but looking at this code you might wonder what the significance of the number 3.14159 is. This example may make sense as you might know what pi is equal to, but the code should actually explain this without you needing to know it.

So, let’s move that number into a variable. We’ll set it when we declare it because we don’t need to change it.

DECLARE
  l_radius NUMBER(4, 3);
  l_circumference NUMBER(10, 3);
  l_pi NUMBER(6, 5) := 3.14159;
BEGIN
  l_radius := 8;
  l_circumference := 2 * l_radius * l_pi;
  DBMS_OUTPUT.PUT_LINE('The circumference is ' || l_circumference);
END;

The output of this code is:

Statement processed.
The circumference is 50.265

The code runs, we’ve used variables, and it’s easy to read.

What if, for some reason, we change the value of pi within our code?

DECLARE
  l_radius NUMBER(4, 3);
  l_circumference NUMBER(10, 3);
  l_pi NUMBER(6, 5) := 3.14159;
BEGIN
  l_radius := 8;
  l_pi := 4;
  l_circumference := 2 * l_radius * l_pi;
  DBMS_OUTPUT.PUT_LINE('The circumference is ' || l_circumference);
END;

We can run our code and get this output.

Statement processed.
The circumference is 64

The output shows a value of 64, which is not correct according to our rules. This is because we have set pi to 4.

Is there any way to prevent this from being changed in our code after we specify it?

Yes, there is. We can declare the l_pi variable as a constant. A constant is a type of variable that is not allowed to change during the program. Once it is declared and assigned, it can’t be changed. It’s great for values that never change, such as pi, and other values your program uses that don’t change. It helps prevent errors such as this.

To declare a variable as a constant, we add the keyword CONSTANT after the variable name and before the data type. The variable also needs to be defined and assigned at the same time.

To set the variable of l_pi as a constant, your code will look like this:

DECLARE
  l_radius NUMBER(4, 3);
  l_circumference NUMBER(10, 3);
  l_pi CONSTANT NUMBER(6, 5) := 3.14159;
BEGIN
  l_radius := 8;
  l_circumference := 2 * l_radius * l_pi;
  DBMS_OUTPUT.PUT_LINE('The circumference is ' || l_circumference);
END;

This variable is now a constant. This code will run and show the right output:

Statement processed.
The circumference is 50.265

If you try to change the value of l_pi during the program, you’ll get an error.

DECLARE
  l_radius NUMBER(4, 3);
  l_circumference NUMBER(10, 3);
  l_pi CONSTANT NUMBER(6, 5) := 3.14159;
BEGIN
  l_radius := 8;
  l_pi := 4;
  l_circumference := 2 * l_radius * l_pi;
  DBMS_OUTPUT.PUT_LINE('The circumference is ' || l_circumference);
END;

The output is:

ORA-06550: line 7, column 3: PLS-00353: expression 'L_PI' cannot be used as an assignment target

This ORA-06550 error means you can’t assign a value to this variable, because you’ve declared it as a constant. This is what we expected.

Conclusion

In this chapter, you learned:

• What variables are, how to declare them, and how to assign them
• Using text and number variables
• How to concatenate two variables together
• How to add, subtract, multiply, and divide numbers
• How to declare constants

Well done for getting this far! In the next chapter, you’ll learn about some key features of PL/SQL: conditional logic and loops.

Chapter Quiz

DECLARE
  l_radius NUMBER(8);
BEGIN
  DBMS_OUTPUT.PUT_LINE('The diameter is ' || 2 * l_radius);
END;

Time's up

< Back to Tutorial Index

Next Chapter >