在Oracle中的SELECT查询中声明变量并设置其值 | 在Oracle中的SELECT查询中声明变量并设置其值

本文介绍了在Oracle中的SELECT查询中声明变量并设置其值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

在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;

确保查询仅返回单行:

如果未返回任何行，则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查询中声明变量并设置其值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！