Oracle游标的返回类型 [英] Oracle cursor's return type
问题描述
我要声明以下游标:
CURSOR some_cursor RETURN oks_trips.trip_id % TYPE IS
SELECT trip_id FROM oks_trips;
但是我得到一个错误:
Error(5,36): PLS-00320: the declaration of the type of this expression is incomplete or malformed
oks_trips.trip_id类型为NUMBER(3, 0)，所以我只尝试了NUMBER而不是oks_tripd.trip_id % TYPE，但仍然出现错误.
我不能省略RETURN语句，因为我在程序包中声明了游标，而oracle在那儿要求它.
所以问题是为什么我不能在游标的RETURN子句中使用NUMBER或some_field % TYPE吗?
您可以在过程，函数或函数中显式声明游标 软件包，以促进Oracle数据库的面向记录的处理 数据. PL/SQL引擎也可以隐式声明游标.
重要的短语是面向记录的". 显式游标声明的语法也清楚地显示了返回值类型必须为rowtype，其定义为:
游标返回的行的数据类型.
您要它返回单列而不是行/记录的数据类型.如果您不想使用现有的%ROWTYPE，那么Oracle会提供一种声明记录类型的机制，就像已经显示的另一个答案一样.
您似乎在抱怨文档没有说您不能使用标量值作为返回值.它还没有说您不能返回包，视图或角色.不需要详尽列出您 不能做的所有事情，因为它可以清楚地告诉您 可以做的事情，即返回代表行的类型. /p>
在您的情况下，行类型只需要包含一个列，但是仍然没有理由让您-或希望Oracle-在这种非常有限的情况下让您走捷径.提供一个一致的机制似乎并不太合理-声明record并不困难，而为此大声疾呼，测试和维护单独的路径将是相当大的开销.
I want to declare the following cursor:
CURSOR some_cursor RETURN oks_trips.trip_id % TYPE IS
SELECT trip_id FROM oks_trips;
But i get an error:
Error(5,36): PLS-00320: the declaration of the type of this expression is incomplete or malformed
oks_trips.trip_id type is NUMBER(3, 0), so i tried just NUMBER instead of oks_tripd.trip_id % TYPE but i still get the error.
I can't omit the RETURN statement because i declare cursor in package and oracle demands it there.
So the question is WHY can't i use NUMBER or some_field % TYPE in cursor's RETURN clause?
You can declare a cursor explicitly within a procedure, function, or package to facilitate record-oriented processing of Oracle Database data. The PL/SQL engine can also declare cursors implicitly.
The important phrase there is 'record-oriented'. The syntax for explicit cursor declaration also clearly shows the return type has to be a rowtype, which it defines as:
The data type of the row that the cursor returns.
You are asking it to return the datatype of a single column, not of a row/record. If you don't want to use an existing %ROWTYPE then Oracle provides the mechanism to declare a record type instead, as another answer has already shown.
You seem to be complaining that the documentation doesn't say that you can't use a scalar value as the return. It also doesn't say that you can't return a package, or a view, or a role. It doesn't need to exhaustively list everything you cannot do, since it clearly tells you exactly what you can do, which is to return a type that represents a row.
In your case that row type only needs to contain a single column, but there is still no reason you should be able to - or expect Oracle to - let you take a shortcut in that very limitd scenario. It doesn't seem unreasonable to provide a single consistent mechanism - it's not much of a hardship for you to declare the record, whereas them bulding, testing and maintaining a seperate path for this would be a considerable overhead.
这篇关于Oracle游标的返回类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
