No Image

Oracle select into variable

СОДЕРЖАНИЕ
400 просмотров
10 марта 2020

The SELECT INTO statement retrieves data from one or more database tables, and assigns the selected values to variables or collections. For a full description of the SELECT statement, see Oracle Database SQL Reference.

In its default usage ( SELECT . INTO ), this statement retrieves one or more columns from a single row. In its bulk usage ( SELECT . BULK COLLECT INTO ), this statement retrieves an entire result set at once.

Syntax


Description of the illustration select_into_statement.gif


Description of the illustration select_item.gif

Keyword and Parameter Description

Another (usually short) name for the referenced column, table, or view.

BULK COLLECT

Stores result values in one or more collections, for faster queries than loops with FETCH statements. For more information, see "Reducing Loop Overhead for DML Statements and Queries (FORALL, BULK COLLECT)".

collection_name

A declared collection into which select_item values are fetched. For each select_item , there must be a corresponding, type-compatible collection in the list.

function_name

A user-defined function.

host_array_name

An array (declared in a PL/SQL host environment and passed to PL/SQL as a bind variable) into which select_item values are fetched. For each select_item , there must be a corresponding, type-compatible array in the list. Host arrays must be prefixed with a colon.

numeric_literal

A literal that represents a number or a value implicitly convertible to a number.

parameter_name

A formal parameter of a user-defined function.

record_name

A user-defined or %ROWTYPE record into which rows of values are fetched. For each select_item value returned by the query, there must be a corresponding, type-compatible field in the record.

rest_of_statement

Anything that can follow the FROM clause in a SQL SELECT statement (except the SAMPLE clause).

Читайте также:  Amd fx 6300 какая материнка подойдет

schema_name

The schema containing the table or view. If you omit schema_name , Oracle assumes the table or view is in your schema.

subquery

A SELECT statement that provides a set of rows for processing. Its syntax is like that of select_into_statement without the INTO clause. See "SELECT INTO Statement".

table_reference

A table or view that must be accessible when you execute the SELECT statement, and for which you must have SELECT privileges. For the syntax of table_reference , see "DELETE Statement".

TABLE (subquery2)

The operand of TABLE is a SELECT statement that returns a single column value, which must be a nested table or a varray. Operator TABLE informs Oracle that the value is a collection, not a scalar value.

variable_name

A previously declared variable into which a select_item value is fetched. For each select_item value returned by the query, there must be a corresponding, type-compatible variable in the list.

Usage Notes

By default, a SELECT INTO statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined. Make sure your WHERE clause is specific enough to only match one row

If no rows are returned, PL/SQL raises NO_DATA_FOUND . You can guard against this exception by selecting the result of an aggregate function, such as COUNT(*) or AVG() , where practical. These functions are guaranteed to return a single value, even if no rows match the condition.

A SELECT . BULK COLLECT INTO statement can return multiple rows. You must set up collection variables to hold the results. You can declare associative arrays or nested tables that grow as needed to hold the entire result set.

Читайте также:  Видеокарта nvidia geforce 9800 gtx характеристики

The implicit cursor SQL and its attributes %NOTFOUND , %FOUND , %ROWCOUNT , and %ISOPEN provide information about the execution of a SELECT INTO statement.

Examples

The following example demonstrates using the SELECT INTO statement to query a single value into a PL/SQL variable, entire columns into PL/SQL collections, or entire rows into a PL/SQL collection of records:

Is there a way in to just run a query once to select into a variable, considering that the query might return nothing, then in that case the variable should be null.

Currently, I can’t do a select into a variable directly, since if the query returns nothing, the PL/SQL would complain variable not getting set. I can only run the query twice, with the first one do the count and if the count is zero, set the variable to null, and if the count is 1, select into the variable.

So the code would be like:

Update: The reason I didn’t use exception is I still have some following logic after assigning the v_column , and I have to use goto in the exception section to jump back to the following code. I’m kind of hesitate of goto lines.

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.

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:

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.

Читайте также:  Msi boot menu как вызвать

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 examples

Let’s use the customers and contacts tables in the sample database for demonstration.

A) PL/SQL SELECT INTO – selecting one column example

The 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.

Комментировать
400 просмотров
Комментариев нет, будьте первым кто его оставит

Это интересно
Adblock
detector