Summary: in this tutorial, you will learn how to use the PL/SQL SELECT INTO statement to fetch data of a single row from a table into variables. Show PL/SQL SELECT INTO statement is the simplest and fastest way to fetch a single row from a table into variables. The following illustrates the syntax of the PL/SQL SELECT INTO statement: SELECT select_list INTO variable_list FROM table_name WHERE condition; Code language: SQL (Structured Query Language) (sql)In this syntax, the number of columns in the variable_list must be the same as the number of variables (or the number of components of a record) in the select_list. In addition, their corresponding data type must be compatible. Besides the WHERE clause, you can use other clauses in the SELECT statement such as INNER JOIN, GROUP BY, HAVING, and UNION. If the SELECT statement returns more than one row, Oracle will raise the TOO_MANY_ROWS exception. If the SELECT statement does not return any row, Oracle will raise the NO_DATA_FOUND exception. PL/SQL SELECT INTO examplesLet’s use the customers and contacts tables in the sample database for demonstration. A) PL/SQL SELECT INTO – selecting one column exampleThe following example uses a SELECT INTO statement to get the name of a customer based on the customer id, which is the primary key of the customers table. DECLARE l_customer_name customers.name%TYPE; BEGIN SELECT name INTO l_customer_name FROM customers WHERE customer_id = 100; dbms_output.put_line( v_customer_name ); END; Code language: SQL (Structured Query Language) (sql)In this example:
Because the customers table has only one row with customer ID 100, the code block displayed the customer name. Code language: SQL (Structured Query Language) (sql)If there were no such row, the code block would fail with an unhandled NO_DATA_FOUND exception. B) PL/SQL SELECT INTO – selecting a complete row exampleThe following example fetches the entire row from the customers table for a specific customer ID: DECLARE r_customer customers%ROWTYPE; BEGIN SELECT * INTO r_customer FROM customers WHERE customer_id = 100; dbms_output.put_line( r_customer.name || ', website: ' || r_customer.website ); END; Code language: SQL (Structured Query Language) (sql)Here is the output: Code language: JavaScript (javascript)In this example:
C) PL/SQL SELECT INTO – selecting data into multiple variables exampleThe following example fetches the names of customer and contact from the customers and contacts tables for a specific customer id. DECLARE l_customer_name customers.name%TYPE; l_contact_first_name contacts.first_name%TYPE; l_contact_last_name contacts.last_name%TYPE; BEGIN SELECT name, first_name, last_name INTO l_customer_name, l_contact_first_name, l_contact_last_name FROM customers INNER JOIN contacts USING( customer_id ) WHERE customer_id = 100; dbms_output.put_line( l_customer_name || ', Contact Person: ' || l_contact_first_name || ' ' || l_contact_last_name ); END; Code language: SQL (Structured Query Language) (sql)Oracle issued the following output: Verizon, Contact Person: Elisha Lloyd Code language: SQL (Structured Query Language) (sql)In this example:
PL/SQL SELECT INTO common errorsIf the number of columns and expression in the SELECT clause is greater than the number of variables in the INTO clause, Oracle issues this error: ORA-00947: not enough values The INTO list contains fewer variables than the SELECT list. Code language: PHP (php)Oracle issues the following error if the number of columns and expression in the SELECT clause is less than the number of variables in the INTO clause: ORA-00913: too many values The INTO list contains more variables than the SELECT list. Code language: PHP (php)If the number of variables and element in the select list are the same, but their corresponding datatypes are not compatible so that Oracle cannot implicitly convert from one type to the other. It will issue the following error: ORA-06502: PL/SQL: numeric or value error Code language: SQL (Structured Query Language) (sql)Now, you should know how to use the PL/SQL SELECT INTO statement to fetch a single row from a table into variables. Was this tutorial helpful?
1.Which SQL statements can be used directly in a PL/SQL block? (Choose two.) 2.Which one of these SQL statements can be directly included in a PL/SQL executable block? *INSERT INTO...; 3.Which of the following is NOT a valid guideline for retrieving data in PL/SQL? * Do NOT use a WHERE clause in SELECT statements. 4. It is good programming practice to create identifiers having the same name as column names. True or False? 5.Does PL/SQL allow you to have a variable with the same name as a database column? *yes 6. When used in a PL/SQL block, which SQL statement must return exactly one row? *SELECT 7. Look at this PL/SQL block: DECLARE v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM employees WHERE salary > 50000; END; No employees earn more than $50000. Which of the following statements are true? (Choose two). *The SELECT will return value 0 into V_COUNT * The SELECT returns exactly one row. 8 What will happen when the following block is executed? DECLARE v_last employees.last_name%TYPE; v_first employees.first_name%TYPE; v_salary employees.salary%TYPE; BEGIN SELECT first_name, last_name INTO v_first, v_last, v_salary FROM employees WHERE employee_id=100; END; * The block will fail because the SELECT is trying to read two columns into three PL/SQL variables. |