在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屋！