What is the purpose of “select into” in Oracle?

In Oracle, the SELECT INTO statement is used to store query results into a variable or table.

In particular, the SELECT INTO statement can be used for the following purposes:

1. Store the query results in a variable: You can store the query results in one or more variables using the SELECT INTO statement. For example:

DECLARE
  v_name employee.name%TYPE;
BEGIN
  SELECT name INTO v_name FROM employee WHERE id = 1;
  DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;

1. Store the query results into multiple variables: You can use the SELECT INTO statement to store the results of a multi-column query into multiple variables. For example:

DECLARE
  v_name employee.name%TYPE;
  v_salary employee.salary%TYPE;
BEGIN
  SELECT name, salary INTO v_name, v_salary FROM employee WHERE id = 1;
  DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ', Salary: ' || v_salary);
END;

1. Store query results in a table: You can store query results in a new table or an existing table using the SELECT INTO statement. For example:

CREATE TABLE new_employee AS
SELECT name, salary FROM employee WHERE hire_date > SYSDATE - 365;

It is important to note that the SELECT INTO statement can only be used to return a single-row query result. If the query result returns multiple rows, an exception will be thrown. If handling multiple row results is necessary, you can use a cursor or the BULK COLLECT INTO statement.