We can use the SELECT INTO statement of SQL for assigning the values to PL/SQL variables. For each and every item in the SELECT list there should be a corresponding, type-compatible variables in the INTO list. Let us see an example that illustrates this concept. Create a table named TEACHERS –

CREATE TABLE TEACHERS (

 ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25)

SALARY DECIMAL (18,2)

PRIMARY KEY (ID)

);

 Let us now insert some values in the table-

INSERT INTO TEACHERS (ID, NAME, AGE, ADDRESS, SALARY)

VALUES (1, ‘RANJITHA’, 34, ‘BANGALORE’, 2000.00);

INSERT INTO TEACHERS (ID, NAME, AGE, ADDRESS, SALARY)

VALUES (2, ‘SAI’, 30, ‘HYDERABAD’, 3000.00);

INSERT INTO TEACHERS (ID, NAME, AGE, ADDRESS, SALARY)

VALUES (3, ‘SHAKTHI’, 34, ‘PUNE’, 2500.00);

INSERT INTO TEACHERS (ID, NAME, AGE, ADDRESS, SALARY)

VALUES (4, ‘GAYATHRI’, 36, ‘BANGALORE’, 4000.00);

INSERT INTO TEACHERS (ID, NAME, AGE, ADDRESS, SALARY)

VALUES (5, ‘VIGNESH’, 34, ‘MUMBAI’, 4500.00);

Given below program assigns the values from the above table to PL/SQL variables using the SELECT INTO clause of SQL-

DECLARE

t_id teachers.id%type := 1;

t_name teachers.name%type;

t_addr teachers.address%type;

t_sal teachers.sal%type;

BEGIN

SELECT name, address, salary INTO t_name, t_addr, t_sal

FROM teachers

WHERE id = t_id;

dbms_output.put_line (‘teacher’||t_name||’from’||t_addr||’earns’||t_sal);

END;

/

When we execute the above code, we get the following output:

Customer Ranjitha from Bangalore earns 2000

PL/SQL procedure completed successfully