Assign Query Results to Variables in Oracle

Q

How To Assign Query Results to Variables in Oracle?

✍: FYIcenter.com

A

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

 

Working with Database Objects in Oracle PL/SQL

⇒⇒Oracle Database Tutorials

2016-10-17, 145👍, 0💬