Oracle Database Interview Question:

How To Assign Query Results to Variables?

Submitted by: Administrator

If you want to assign results from SELECT statements to variables, you can use the INTO clause, which an extension of SELECT statements for PL/SQL. The sample code below shows some good example on INTO clause:

DECLARE
total NUMBER;
now DATE;
fname VARCHAR2(10);
lname VARCHAR2(10);
BEGIN
SELECT COUNT(*) INTO total FROM employees;
DBMS_OUTPUT.PUT_LINE('Count = ' || TO_CHAR(total));
SELECT SYSDATE INTO now FROM DUAL;
DBMS_OUTPUT.PUT_LINE('Now = ' || TO_CHAR(now, 'SSSSS'));
SELECT first_name, last_name INTO fname, lname
FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('Name = ' || fname || ' ' || lname);
END;
/
Count = 107
Now = 82375
Name = Steven King

Submitted by: Administrator