在 Oracle 中通过 SELECT 查询声明变量并设置其值 [英] Declaring a variable and setting its value from a SELECT query in Oracle

问题描述

在 SQL Server 中我们可以使用这个:

In SQL Server we can use this:

DECLARE @variable INT;
SELECT @variable= mycolumn from myTable;

如何在 Oracle 中执行相同的操作?我目前正在尝试以下操作:

How can I do the same in Oracle? I'm currently attempting the following:

DECLARE COMPID VARCHAR2(20);
SELECT companyid INTO COMPID from app where appid='90' and rownum=1;

为什么这不工作?

推荐答案

选择进入

DECLARE
   the_variable NUMBER;

BEGIN
   SELECT my_column INTO the_variable FROM my_table;
END;

确保查询只返回一行:

默认情况下，SELECT INTO 语句只能返回一行.否则，PL/SQL 会引发预定义的异常 TOO_MANY_ROWS 并且 INTO 子句中的变量值未定义.确保您的 WHERE 子句足够具体以仅匹配一行

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

如果没有返回任何行，PL/SQL 会引发 NO_DATA_FOUND.在可行的情况下，您可以通过选择聚合函数的结果(例如 COUNT(*) 或 AVG())来防止出现此异常.即使没有行匹配条件，这些函数也保证返回单个值.

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.

一个 SELECT ... BULK COLLECT INTO 语句可以返回多行.您必须设置集合变量来保存结果.您可以声明关联数组或嵌套表，它们会根据需要增长以容纳整个结果集.

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.

隐式游标 SQL 及其属性 %NOTFOUND、%FOUND、%ROWCOUNT 和 %ISOPEN 提供有关执行 SELECT INTO 语句的信息.

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

这篇关于在 Oracle 中通过 SELECT 查询声明变量并设置其值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！