{"id":1520,"date":"2017-11-23T21:09:44","date_gmt":"2017-11-23T14:09:44","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=1520"},"modified":"2025-05-24T18:53:40","modified_gmt":"2025-05-25T01:53:40","slug":"plsql-select-into","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-select-into\/","title":{"rendered":"PL\/SQL SELECT INTO"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the PL\/SQL SELECT INTO<\/code> statement to fetch data of a single row from a table into variables.<\/p>\r\n\r\n\r\n\r\n

Introduction to PL\/SQL SELECT INTO statement #<\/a><\/h2>\r\n\r\n\r\n\r\n

PL\/SQL SELECT INTO<\/code> statement is the simplest and fastest way to fetch a single row from a table into variables<\/a>.<\/p>\r\n\r\n\r\n\r\n

Here’s the syntax of the PL\/SQL SELECT INTO<\/code> statement:<\/p>\r\n\r\n\r\n

SELECT<\/span>\r\n  select_list\r\nINTO<\/span>\r\n  variable_list\r\nFROM<\/span>\r\n  table_name<\/span>\r\nWHERE<\/span>\r\n  condition; <\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
In this syntax, the number of columns in the variable_list<\/code> must be the same as the number of variables (or the number of components of a record) in the select_list<\/code>.\u00a0 Additionally, their corresponding data type must be compatible.<\/p>\r\n\r\n\r\n\r\n
If the SELECT<\/code> statement returns more than one row, Oracle will raise the TOO_MANY_ROWS<\/code> exception<\/a>.<\/p>\r\n\r\n\r\n\r\n
If the SELECT<\/code> statement does not return any row, Oracle will raise the NO_DATA_FOUND<\/code> exception.<\/p>\r\n\r\n\r\n\r\n
PL\/SQL SELECT INTO examples #<\/a><\/h2>\r\n\r\n\r\n\r\n
We’ll use the customers<\/code> and contacts<\/code> tables in the sample database<\/a> for demonstration.<\/p>\r\n\r\n\r\n\r\n
\"PL\/SQL<\/figure>\r\n\r\n\r\n\r\n
Selecting one column example #<\/a><\/h3>\r\n\r\n\r\n\r\n
The following example uses a SELECT INTO<\/code> statement to retrieve the name of a customer based on the customer id, which is the primary key of the customers<\/code> table.<\/p>\r\n\r\n\r\n
DECLARE<\/span>\r\n  l_customer_name customers.name%TYPE<\/span>;\r\nBEGIN<\/span>\r\n  -- get name of the customer 100 and assign it to l_customer_name<\/span>\r\n  SELECT<\/span> name<\/span> INTO<\/span> l_customer_name\r\n  FROM<\/span> customers\r\n  WHERE<\/span> customer_id = 100<\/span>;\r\n\r\n  -- show the customer name<\/span>\r\n  dbms_output.put_line( l_customer_name );\r\nEND<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
In this example:<\/p>\r\n\r\n\r\n\r\n
Besides the WHERE<\/a><\/code> clause, you can use other clauses in the SELECT<\/a><\/code> statement, such as INNER JOIN<\/a><\/code>, GROUP BY<\/a><\/code>, HAVING<\/a><\/code>, and UNION<\/a><\/code>.<\/p>\r\n\r\n\r\n\r\n