PL/SQL tutorial 2 : PL/SQL Variables in Oracle Database By Manish Sharma RebellionRider

Summary

In this tutorial from RebellionRider.com, Manish explains the concept of variables in PL/SQL. He covers the necessity of declaring variables before use, emphasizing that variables act as placeholders in memory for data that can change. The video demonstrates how to declare a variable 'v_test' with a VARCHAR2 data type and a width of 15 characters. Manish then shows two methods of initializing variables: directly in the declaration section or in the execution section of a PL/SQL block. He uses the DBMS_OUTPUT package to display the variable's value, highlighting the unique assignment operator in PL/SQL, which combines a colon and an equals sign. The tutorial is designed to help viewers understand variable handling in PL/SQL for both interviews and examinations.

Takeaways

• 💻 Variables in PL/SQL are placeholders in computer memory used to store data, which can change during the program's execution.
• 🔑 Every variable in PL/SQL must have a valid name, data type, and data width, and must be declared before it is used.
• 📝 The declaration of variables in PL/SQL is done in the declaration section of a PL/SQL block.
• 🖥️ The SET SERVEROUTPUT ON command is crucial for enabling server communication and displaying results on the output device.
• 🚀 Variables can be initialized in different ways in PL/SQL, including in the declaration section, execution section, or exception handling section.
• 🎯 Direct Initialization involves assigning a value to a variable directly during its declaration or in the execution section.
• 📚 The assignment operator in PL/SQL is a combination of a colon (:) and an equal to sign (=), which is different from other programming languages.
• 📝 The DBMS_OUTPUT.PUT_LINE procedure is used to display the value of a variable, showcasing the variable's initialization.
• 🔄 There are multiple methods to initialize variables in PL/SQL, and upcoming tutorials will cover more advanced techniques like initializing from database table values.
• 🌐 For further learning and notes, viewers are encouraged to visit the presenter's website, RebellionRider.com.

Q & A

What is the primary function of variables in PL/SQL?

Variables in PL/SQL serve as placeholders in computer memory that hold data. They are called variables because the data they hold can change, hence their nature is variable.

Why is it necessary to declare a variable before using it in PL/SQL?

In PL/SQL, every variable must be declared before its use to announce its presence to the compiler, which then checks its validity and allocates memory according to the variable's data type.

What is the purpose of the 'SET SERVEROUTPUT ON;' command in PL/SQL?

The 'SET SERVEROUTPUT ON;' command in PL/SQL is a special directive that allows the server to communicate with the user and enables the display of results on the default output device.

What is the significance of the DECLARE keyword in PL/SQL?

The DECLARE keyword in PL/SQL signifies the beginning of the declaration section of a block, where variables and other objects are declared.

How is a variable declared in PL/SQL?

A variable in PL/SQL is declared by specifying its name, data type, and data width within the declaration section of a block, followed by a semicolon to indicate the end of the declaration.

What is the difference between the assignment operator in PL/SQL and other programming languages?

In PL/SQL, the assignment operator is a combination of a colon (:) and an equal to sign (=), which is different from other programming languages that typically use just an equal to sign (=).

Can a variable be initialized during its declaration in PL/SQL?

Yes, a variable in PL/SQL can be initialized during its declaration by using the assignment operator and providing a value immediately after its declaration.

Where can a variable be initialized in a PL/SQL block, besides the declaration section?

A variable in PL/SQL can be initialized not only in the declaration section but also in the execution section or even in the exception handling section of a block.

How can you check the value stored in a variable during the execution of a PL/SQL block?

The value stored in a variable during the execution of a PL/SQL block can be checked by calling the 'DBMS_OUTPUT.PUT_LINE' procedure, which displays the value on the default output device.

What is direct initialization of a variable in PL/SQL?

Direct initialization in PL/SQL refers to the process of assigning a value directly to a variable, either during its declaration or in the execution section of a block.

What will be covered in the next tutorial by Manish from RebellionRider.com?

In the next tutorial, Manish will demonstrate how to fetch a value from a database table and use it to initialize a variable in PL/SQL.

Outlines

00:00

💻 Introduction to PL/SQL Variables

Manish from RebellionRider.com introduces the concept of variables in PL/SQL, explaining that variables are placeholders in computer memory that can hold data of variable nature. He emphasizes the necessity of declaring variables before use in PL/SQL, and demonstrates how to declare a variable named 'v_test' with a data type of 'VARCHAR2' and a data width of 15. Manish also explains the use of the 'SET SERVEROUTPUT ON' command, which allows the server to communicate with the user and display results. The tutorial provides a clear example of variable declaration and touches on the importance of the declaration section in the PL/SQL block.

05:03

🔧 Initializing Variables in PL/SQL

In this segment, Manish discusses the process of initializing variables in PL/SQL. He explains that a variable, once declared, is empty and must be initialized to be useful. Initialization can occur in the declaration section, execution section, or exception handling section of a PL/SQL block. Manish provides examples of direct initialization, where the value 'RebellionRider' is assigned to 'v_test' using the assignment operator ' := '. He contrasts the assignment operator in PL/SQL with those in other programming languages and demonstrates how to use the 'DBMS_OUTPUT.PUT_LINE' procedure to display the value of the variable. The tutorial concludes with a preview of the next tutorial, which will cover fetching values from a database table to initialize variables.

Keywords

💡Variables

Variables in the context of the video are placeholders in computer memory that temporarily hold data. They are essential in PL/SQL for storing and manipulating data within a program. Variables are characterized by being 'variable', meaning their values can change, which is central to the theme of the video about PL/SQL programming. In the script, Manish declares a variable 'v_test' with a data type of 'VARCHAR2(15)', illustrating how variables are set up to hold specific types of data.

💡PL/SQL

PL/SQL, which stands for Procedural Language/SQL, is a programming language used in the video for database operations. It allows users to write stored procedures for databases, manipulate data, and control the flow of a program. The video's tutorial focuses on PL/SQL variables, emphasizing its importance in database programming. Manish's tutorial specifically discusses how to declare and initialize variables in PL/SQL, showcasing the language's procedural capabilities.

💡Declaration

In programming, declaration is the process of introducing a variable to the program by specifying its name, data type, and optionally, an initial value. The video script explains that in PL/SQL, variables must be declared before they are used, which is a fundamental concept in programming. Manish demonstrates declaring a variable 'v_test' in the declaration section of a PL/SQL block, which is a critical step before the variable can be utilized in the program.

💡Data Type

Data types define the kind of data a variable can hold, such as numbers, characters, or dates. In the video, Manish specifies that 'v_test' has a data type of 'VARCHAR2(15)', indicating that the variable is intended to store character strings up to 15 characters long. Understanding data types is crucial for correctly declaring variables in PL/SQL and ensuring they can store the appropriate data.

💡Data Width

Data width refers to the amount of storage allocated for a variable, which is particularly relevant for data types like 'VARCHAR2' in PL/SQL. In the script, 'v_test' is declared with a data width of 15, meaning it can store up to 15 characters. This concept is important for optimizing memory usage and ensuring that variables can accommodate the expected data sizes.

💡Initialization

Initialization is the process of assigning an initial value to a variable. The video emphasizes that in PL/SQL, variables must be initialized to be useful, as they start empty upon declaration. Manish shows two ways to initialize 'v_test': directly in the declaration with a value or in the execution section of the block, which is a key step in making the variable operational within the program.

💡Assignment Operator

The assignment operator is used to assign values to variables. Unlike some programming languages that use '=' for assignment, PL/SQL uses a combination of ':' and '='. In the video, Manish uses ':=' to assign the string 'RebellionRider' to 'v_test', demonstrating the correct syntax for initializing variables in PL/SQL. This operator is central to the process of variable initialization discussed in the tutorial.

💡Anonymous Block

An anonymous block in PL/SQL is a block of PL/SQL code that is not bound to an identifier and is executed immediately. The video script mentions that Manish is using an anonymous block to demonstrate variable declaration and initialization. This concept is important for understanding how to write and execute PL/SQL code that performs immediate tasks without being part of a larger program or stored procedure.

💡DBMS_OUTPUT.PUT_LINE

DBMS_OUTPUT.PUT_LINE is a procedure used in PL/SQL to display output on the screen. In the script, Manish uses this procedure to print the value of 'v_test' after initialization, which is a practical way to verify that the variable has been correctly initialized. This procedure is a common tool for debugging and displaying results in PL/SQL programming.

💡SET SERVEROUTPUT ON

SET SERVEROUTPUT ON is a command used in PL/SQL to enable the display of output from the server. In the video, Manish uses this command before declaring and initializing variables to ensure that any output from the DBMS_OUTPUT package will be visible. This command is crucial for setting up the environment to view the results of PL/SQL code execution.

Highlights

Introduction to variables in PL/SQL

Variables as placeholders in computer memory

Variables must have a valid name, data type, and data width

Variables must be declared before use in PL/SQL

Demonstration of variable declaration in SQL Developer

Explanation of SET SERVEROUTPUT ON command

Declaration section for variable declaration in PL/SQL

Declaration of a variable named v_test with data type VARCHAR2 and width 15

Memory allocation by the compiler during variable declaration

Initialization of variables in PL/SQL

Variable initialization can occur anywhere in the PL/SQL block

Direct initialization of variable v_test with the value 'RebellionRider'

Use of assignment operator in PL/SQL, which is a combination of colon and equal sign

Initialization of variables in the execution section of a PL/SQL block

Checking the value of a variable using DBMS_OUTPUT.PUT_LINE procedure

Two ways of initializing variables: Direct Initialization

Teaser for the next tutorial on fetching values from a database table

Invitation to subscribe for more PL/SQL tutorials

Transcripts

What’s up Internet? Welcome back once again I am Manish from RebellionRider.com. Today

in this tutorial we will talk about variables in PL/SQL and learn how to declare and initialize

them. There are different ways of initializing a variable in PL/SQL we will see all those

in this tutorial. So keep watching. First things first – what are variables?

Variables are nothing but a place holder in computer memory that holds some data. We call

them variables because the value or say data they hold is of variable nature which can

be changed at any point in your program. Every variable must have a valid name, a data type

and a data width. Like various other programming languages in PL/SQL also variable must be

declared prior to its use. Right now I am on my SQL Developer but you

can use whatever IDE or tool you want. So let’s write some code and try to understand

this concept more clearly. As I said, in PL/SQL every variable must be declared prior to its

use and the declaration of the variables or even other objects can only be done in the

declaration section of the block in PL/SQL. So let’s declare a variable.

SET SERVEROUTPUT ON; DECLARE

v_test VARCHAR2(15); Ohk, here we have declared a variable v_test.

Declaration of a variable means announcement of your variable to the compiler. As soon

as the compiler comes across to the signature of your variable it first checks for the validity

and allocates the memory according to the data type of the variable.

Anyways let’s come back to our code. First this is an anonymous PL/SQL block in which

the first line of our code is SET SERVEROUTPUT ON. For the time being just understand that

this is a special PL/SQL command which allows the server to communicate with you and enable

the display of result on your default output device. I will do a separate tutorial explaining

this command as it’s a very important topic from both Interview and examination point

of view. In the second line we have a keyword DECLARE

which indicates the Declaration section of our block. Inside our declaration section

we have our variable which is v_test with data type varchar2 and data width 15. Followed

by the semicolon which indicates the “End of Line”. That’s it that’s how we declare

a variable. Now as we have successfully declared our variable,

next we have to initialize it. In PL/SQL like various other languages when we declare a

variable it’s completely empty and holds no data. Thus to make a variable useful we

need to put some data into it, and this process of assigning value to the variable is called

variable Initialization. So let’s see how to initialize a variable

in PL/SQL. Unlike declaration of a variable which can

only be done in declaration section of PL/SQL block the initialization can be done anywhere

inside in your program. Yes, you can initialize a variable either in declaration section while

declaring it or in execution section or even in exception handling section.

Let’s do an example which will help you in understanding this. First we will initialize

the variable in declaration section. Suppose I want to store a character string

“RebellionRider” into our variable v_test. SET SERVEROUTPUT ON;

DECLARE v_test VARCHAR2(15) := 'RebellionRider';

As we are assigning the value which is a character string “RebellionRider” to the variable

v_test thus we used assignment operator. If you have noticed the assignment operator

is slightly different here than in other languages. In PL/SQL assignment operator is combination

of colon (: ) and equal to sign which is very unlikely compared to other programming languages

where just an equal too (=) sign is considered as an assignment operator.

Followed by assignment operator we have our string “RebellionRider” which we want

to store in our variable. As it’s a string thus we have enclosed it into single quotes

and at the end we have the semi colon. That’s how we initialize the variable in

declaration section. Now let’s see how to initialize the PL/SQL variable in execution

section. And for this we have to write the execution section of our anonymous block.

You can watch my previous PL/SQL tutorial where I explained this block and Section thing

in detail. Link is in the description below. Let’s write.

SET SERVEROUTPUT ON; DECLARE

v_test VARCHAR2(15); BEGIN

v_test := 'RebellionRider'; END;

You can check the value stored in your variable by calling put line procedure of DBMS_OUTPUT

package. Let me show you how. SET SERVEROUTPUT ON;

DECLARE v_test VARCHAR2(15);

BEGIN v_test := 'RebellionRider';

DBMS_OUTPUT.PUT_LINE(v_test ); END;

When we initialize the variable in execution section we just write the name of the variable

followed by the assignment operator and then the value which is a string “RebellionRider”

in our case. There is no need to write data type and data width at this point, just make

sure the variable which you are initializing must be declared prior to in declaration section.

As this PL/SQL block has the execution section which means we can execute this. So let’s

execute and see the result. Here is our result.

These are the two different ways of initializing a variable and they are called Direct Initialization

as we are putting value directly into the variable.

In the next tutorial I will show you how you can fetch a value from a table of the database

and store it into the variable or say use it to initialize the variable.

So stay tuned and don’t forget to watch my next tutorial. Also

For more in-depth knowledge or for the notes on the topic you can visit my website. Find

the link below in the description. If you like this video then be shameless and

hit the thumbs up button and share it with your friends on you social networking also

tag me so that I can give you a shoutout. Please subscribe to my channel for more interesting

videos. Thanks for watching this is Manish from RebellionRider.