Home » PL/SQL Tutorial » PL/SQL Variables

PL/SQL Variables

Summary : in this tutorial, you will learn about PL/SQL variables and how to use them effectively.

In PL/SQL, a variable is named storage location that stores a value of a particular data type . The value of the variable changes through the program. Before using a variable, you must declare it in the declaration section of a block .

Declaring variables

The syntax for a variable declaration is as follows:

In this syntax:

  • First, specify the name of the variable. The name of the variable should be as descriptive as possible, e.g., l_total_sales , l_credit_limit , and l_sales_revenue .
  • Second, choose an appropriate data type for the variable, depending on the kind of value that you want to store, for example, number, character, Boolean, and datetime.

By convention, local variable names should start with l_ and global variable names should have a prefix of g_ .

The following example declares three variables l_total_sales , l_credit_limit , and l_contact_name :

Default values

PL/SQL allows you to set a default value for a variable at the declaration time. To assign a default value to a variable, you use the assignment operator ( := ) or the DEFAULT keyword.

The following example declares a variable named l_product_name with an initial value 'Laptop' :

It is equivalent to the following block:

In this example, instead of using the assignment operator := , we used the DEFAULT keyword to initialize a variable.

NOT NULL constraint

If you impose the NOT NULL constraint on a value, then the variable cannot accept a NULL value. Besides, a variable declared with the NOT NULL must be initialized with a non-null value. Note that PL/SQL treats a zero-length string as a NULL value.

The following example first declares a variable named l_shipping_status with the NOT NULL constraint. Then, it assigns the variable a zero-length string.

PL/SQL issued the following error:

Because the variable l_shipping_status declared with the NOT NULL constraint, it could not accept a NULL value or zero-length string in this case.

Variable assignments

To assign a value to a variable, you use the assignment operator ( := ), for example:

You can assign a value of a variable to another as shown in the following example:

Anchored declarations

Typically, you declare a variable and select a value from a table column for this variable. If the data type of the table column changes, you must adjust the program to make it work with the new type.

PL/SQL allows you to declare a variable whose data type anchor to a table column or another variable. Consider the following example:

In this example:

  • First,  declare two variables l_customer_name and l_credit_limit whose data type anchors to the name and credit_limit columns respectively, in the declaration section of the block.
  • Second, query the customer name and credit limit of the customer id 38 and assign these column values to the l_customer_name and l_credit_limit variables in the execution block.
  • Third, display the customer name and credit limit.

PL/SQL returned the following output:

This example illustrates how to declare variables that anchor to another variable:

Here is the output:

Now, you should know how to use PL/SQL variables in your block and manipulate them efficiently.

IMAGES

  1. PPT

    pl sql variable assignment select

  2. PL/SQL SELECT INTO

    pl sql variable assignment select

  3. SQL Variables

    pl sql variable assignment select

  4. PL SQL tutorial 6 Bind Variable in PL SQL By Manish Sharma

    pl sql variable assignment select

  5. Declaring PL/SQL Variables. (Lecture 2)

    pl sql variable assignment select

  6. SELECT vs SET For Variable Assignment In SQL Server

    pl sql variable assignment select

VIDEO

  1. 9/125 Oracle PLSQL: Declaring PLSQL Variables 4

  2. K42

  3. SQL (Structured Query Language) Class15

  4. 10/125 Oracle PLSQL: Declaring PLSQL Variables 5

  5. 52. Input Variable in Oracle PL/SQL

  6. plsql functions and procedures| plsql sub programs|stored procedure examples in Realtime

COMMENTS

  1. plsql

    SELECT INTO. DECLARE the_variable NUMBER; BEGIN SELECT my_column INTO the_variable FROM my_table; END; Make sure that …