What is the method used in Oracle for “select into”?

1 year ago
William Carter
2 minutes

In Oracle, you can use the SELECT INTO statement to store the results of a query into a variable or table.

The syntax for storing query results into a variable using the SELECT INTO statement is as follows:

SELECT column_name INTO variable_name
FROM table_name
WHERE condition;

In the above syntax, column_name refers to the name of the column to be queried, variable_name is the name of the variable where the result will be stored, table_name is the name of the table to be queried, and condition represents the query condition.

For example, the following example will query the employee table for employees with the last name “Smith” and store their names in a variable:

DECLARE
  v_employee_name employees.last_name%TYPE;
BEGIN
  SELECT last_name INTO v_employee_name
  FROM employees
  WHERE last_name = 'Smith';
  
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
END;
/

In the example above, a variable v_employee_name is defined using the DECLARE statement, and then the SELECT INTO statement is used to store the query result in this variable. Finally, the result is output to the console using the DBMS_OUTPUT.PUT_LINE function.

In addition to storing in a variable, you can also use the SELECT INTO statement to store query results in a table. The syntax is as follows:

SELECT column_name1, column_name2, ...
INTO table_name
FROM table_name
WHERE condition;

In the above syntax, column_name1, column_name2, … represent the columns to be queried, table_name is the name of the table where the results will be stored, and condition is the parameter for the query.

For example, the following code will select the names and salaries of employees with the last name “Smith” from the employee table and store them in a new table.

CREATE TABLE new_table AS
SELECT last_name, salary
FROM employees
WHERE last_name = 'Smith';

In the above example, the SELECT INTO statement is used to store the query results in a new table called new_table. The structure of the new table matches the structure of the query results, including the last_name and salary columns.

bannerAds