Oracle

The SELECT INTO statement in Oracle is part of the Data Manipulation Language (DML) and is used to retrieve data from a table and assign it into variables or records. This is particularly useful in PL/SQL when you want to store query results into variables for further manipulation. It works with single-row queries, meaning the query should return exactly one row, or it will throw an error.

Key Concepts of SELECT INTO:

1. Purpose of SELECT INTO:

   • This is used to fetch data from one or more columns in a table and store the results into PL/SQL variables.
   • The SELECT INTO statement is commonly used in PL/SQL blocks.

2. Syntax of SELECT INTO:

   SELECT column1, column2
   INTO var1, var2
   FROM table_name
   WHERE condition;
   

   • column1, column2: The columns from which the data will be retrieved.
   • var1, var2: The PL/SQL variables where the selected data will be stored.
   • table_name: The table from which the data is selected.
   • condition: Used to filter the result.

3. Example Using Library and Author Table:
   Suppose you have a table authors with columns author_id, first_name, and last_name. You want to store the first_name and last_name of a particular author into variables.

   DECLARE
     v_first_name authors.first_name%TYPE;
     v_last_name authors.last_name%TYPE;
   BEGIN
     SELECT first_name, last_name
     INTO v_first_name, v_last_name
     FROM authors
     WHERE author_id = 101;
   
     DBMS_OUTPUT.PUT_LINE('Author: ' || v_first_name || ' ' || v_last_name);
   END;
   

   • Here, the author_id = 101 fetches the author with ID 101 and stores their first and last name in variables v_first_name and v_last_name.
   • The result is then printed using DBMS_OUTPUT.PUT_LINE.

4. Error Handling:

   • No Data Found Error: If the query does not return any row, a NO_DATA_FOUND exception is raised.
   • Too Many Rows Error: If the query returns more than one row, a TOO_MANY_ROWS exception is raised.

5. Steps for Using SELECT INTO:

   1. Declare Variables:

      • You need to declare the variables where you will store the retrieved data.

      • Use %TYPE to match the data type of the variable with the corresponding column in the table.

      • Example:

        DECLARE
          v_book_title books.title%TYPE;
        

   2. Write the SELECT INTO Statement:

      • This statement selects the columns and assigns them to the declared variables.
      • You should ensure that the query only returns one row.

   3. Check for Errors:

      • Include error handling to manage cases when no rows or multiple rows are returned.

   4. Use the Variables:

      • Once the data is assigned to the variables, you can use them in your PL/SQL logic.

To gain complete access, login with gmail or outlook, no need of signup. click here