In the previous tutorial we learnt about variables as well as how to declare and initialize them. There we saw two different examples of direct initialization. Here in this tutorial we will see another way of initializing the variable using SELECT INTO statement.

The SELECT INTO statement retrieves data from one or more database tables, and assigns the selected values to variables or collections.

Syntax

SELECT column1, column2…. Column n INTO variable1, variable2… Variable n FROM table_name WHERE <expression>;

Now let’s see some examples of initializing a variable by fetching values from tables of your database. For the demonstration I will use the Employees table of HR sample Schema.

Example 1

DECLARE
v_salary NUMBER(8);

As I mentioned in my previous tutorial, that every variable must be declared prior to its use and we can only declare a variable in declaration section of PL/SQL block. In the above demonstration I declared a variable by the name of v_salary which has data type NUMBER and Data width 8. One thing you must take care while declaring variable here is that the data type and data width of your variable and the column whose value you want to fetch must match.

BEGIN
SELECT salary INTO v_salary FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE (v_salary);
END;

This is the execution section of our anonymous block. This section contains our select statement. This select statement is retrieving salary of the employee whose employee id is 100 from employees table and storing it into the variable v_salary.

The variable v_salary which we declare above in the declaration section is capable of holding single data at a time thus make sure your select statement must return only single data. This you can ensure by using WHERE clause of your SELECT statement as I did in this example. 

Let’s put all the parts together and see the complete anonymous block.

DECLARE
v_salary NUMBER(8);
BEGIN
SELECT salary INTO v_salary FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE (v_salary);
END;

Example 2.

Fetch data from multiple column and store it into multiple variables.

Suppose along with Salary you also want to display the first name of the employee using PL/SQL. In this case we will need two different variables as we want to fetch data from two different columns of the table first name and salary. Let’s see the example

DECLARE
 v_salary      NUMBER(8);
 v_fname       VARCHAR2 (20);
BEGIN
 SELECT first_name, salary INTO v_fname, v_salary FROM employees
 WHERE employee_id =100;
 DBMS_OUTPUT.PUT_LINE(v_fname||’ has salary ‘||v_salary);
END;

In this query we have two variables v_salary which will hold the value from salary column and v_fname which will hold the value from first name column of employees table. The select statement is very similar to the previous one except the one extra column with first name and variable v_fname.

This select statement will return the first name and salary of the employee whose employee id is 100 and then those values will be stored into our variable v_ fname and v_salary. Few things which you must take care here are:

1. As I explained a while ago that variable v_fname and v_salary can hold only one data at a time thus make sure your select statement will return data from one row at a time. You can ensure this by using WHERE clause.
2. The value from first name and salary columns will be stored into variable v_fname and v_salary respectively hence you should always make sure that the data type and data width of the variable matches that of the columns.

That’s it in this section. Hope you liked it. Kindly please share it on your social network and help me reach out to more people. Thanks & have a great day!