在 Oracle 中通过 SELECT 查询声明变量并设置其值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7553901/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Declaring a variable and setting its value from a SELECT query in Oracle
提问by user960567
In SQL Server we can use this:
在 SQL Server 中，我们可以使用这个：
DECLARE @variable INT;
SELECT @variable= mycolumn from myTable;
How can I do the same in Oracle? I'm currently attempting the following:
我如何在 Oracle 中做同样的事情？我目前正在尝试以下操作：
DECLARE COMPID VARCHAR2(20);
SELECT companyid INTO COMPID from app where appid='90' and rownum=1;
Why this is not working?
为什么这不起作用？
回答by Thilo
DECLARE
the_variable NUMBER;
BEGIN
SELECT my_column INTO the_variable FROM my_table;
END;
Make sure that the query only returns a single row:
确保查询只返回一行：
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.
The implicit cursor SQL and its attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN provide information about the execution of a SELECT INTO statement.
默认情况下，SELECT INTO 语句只能返回一行。否则，PL/SQL 会引发预定义的异常 TOO_MANY_ROWS 并且 INTO 子句中的变量值未定义。确保您的 WHERE 子句足够具体以仅匹配一行
如果没有返回任何行，PL/SQL 会引发 NO_DATA_FOUND。在可行的情况下，您可以通过选择聚合函数的结果（例如 COUNT(*) 或 AVG()）来防止出现此异常。即使没有行匹配条件，这些函数也保证返回单个值。
SELECT ... BULK COLLECT INTO 语句可以返回多行。您必须设置集合变量来保存结果。您可以声明根据需要增长以保存整个结果集的关联数组或嵌套表。
隐式游标 SQL 及其属性 %NOTFOUND、%FOUND、%ROWCOUNT 和 %ISOPEN 提供有关执行 SELECT INTO 语句的信息。
回答by Ollie
Not entirely sure what you are after but in PL/SQL you would simply
不完全确定你在追求什么，但在 PL/SQL 中你会简单地
DECLARE
v_variable INTEGER;
BEGIN
SELECT mycolumn
INTO v_variable
FROM myTable;
END;
Ollie.
奥利。
回答by Praveen
One Additional point:
补充一点：
When you are converting from tsqlto plsqlyou have to worry about no_data_foundexception
当您从tsqlto转换时，plsql您必须担心no_data_found异常
DECLARE
v_var NUMBER;
BEGIN
SELECT clmn INTO v_var FROM tbl;
Exception when no_data_found then v_var := null; --what ever handle the exception.
END;
In tsqlif no data foundthen the variable will be nullbut no exception
在tsqlifno data found那么变量将是null但没有exception
回答by user3325275
ORA-01422: exact fetch returns more than requested number of rows
ORA-01422: 精确提取返回的行数超过请求的行数
if you don't specify the exact record by using where condition, you will get the above exception
如果不使用 where 条件指定确切的记录，则会出现上述异常
DECLARE
ID NUMBER;
BEGIN
select eid into id from employee where salary=26500;
DBMS_OUTPUT.PUT_LINE(ID);
END;
回答by Vincent Lal
For storing a single row output into a variable from the select into query :
将单行输出从 select into query 存储到变量中：
declare v_username varchare(20); SELECT username into v_username FROM users WHERE user_id = '7';
声明 v_username varchare(20); SELECT username into v_username FROM users WHERE user_id = '7';
this will store the value of a single record into the variable v_username.
这会将单个记录的值存储到变量 v_username 中。
For storing multiple rows output into a variable from the select into query :
用于将多行输出存储到 select into 查询中的变量中：
you have to use listagg function. listagg concatenate the resultant rows of a coloumn into a single coloumn and also to differentiate them you can use a special symbol. use the query as below SELECT listagg(username || ',' ) within group (order by username) into v_username FROM users;
你必须使用listagg函数。listagg 将一个列的结果行连接成一个列，并且为了区分它们，您可以使用特殊符号。使用如下查询 SELECT listagg(username || ',' ) 组内（按用户名排序）到 v_username FROM users;
