关于CURSOR的一个问题

zlxfei

今天碰到一个问题，现象如下：

在一个PC中声明以下CURSOR定义：
SELECT lsh,wjm,scrq,zjs,jls,yhmc,jfykzbs,jfykjehj
from STI_COLL_TTL
where scrq = :pCond->;m_cprq and
substr(wjm,1,1) = :pCond->;m_yhdm and
:pCond->;m_opcode in ('3','4')
union all
select lsh,wjm,to_char(scrq,'yyyymmdd') scrq,zrs * 107 + 98 zjs,zrs jls,' ' yhmc,zrs jfykzbs,to_number(ffze)/100 jfykjehj
from sti_sup_pay_head
where to_char(scrq,'yyyymmdd') = :pCond->;m_cprq and
substr(wjm,1,1) = :pCond->;m_yhdm and
:pCond->;m_opcode = '5'
union all
SELECT lsh,wjm,scrq,zrs * 103 + 58 zjs,zrs jls,yhmc ,zrs jfykzbs,zje jfykjehj
from sti_payout_ttl
where scrq = :pCond->;m_cprq and
substr(wjm,1,1) = :pCond->;m_yhdm and
:pCond->;m_opcode ='6'
order by scrq asc;

PC程序编译通过，但在调用的时候报ORA-00904: invalid column name，如果把上述三个SQL去掉一个SQL语句，改成任意两两UNION ALL，编译和运行均正常。

写一个匿名块在PL/SQL中执行上述语句：
DECLARE
CURSOR C1
IS
SELECT lsh,wjm,scrq,zjs,jls,yhmc,jfykzbs,jfykjehj
from STI_COLL_TTL
where scrq = '20040308' and
substr(wjm,1,1) = 'SDF' and
'3' in ('3','4')
union all
select lsh,wjm,to_char(scrq,'yyyymmdd'),zrs * 107 + 98,zrs,' ',zrs,to_number(ffze)/100
from sti_sup_pay_head
where to_char(scrq,'yyyymmdd') = '20040308' and
substr(wjm,1,1) = 'SDF' and
'5'= '5'
union all
SELECT lsh,wjm,scrq,zrs * 103 + 58,zrs,yhmc ,zrs,zje
from sti_payout_ttl
where scrq = '20040308' and
substr(wjm,1,1) = 'SDF' and
'6' ='6'
order by scrq asc;
BEGIN
NULL;
FOR R1 IN C1 LOOP
NULL;
END LOOP;
END;
/
发现得到和PC中一样的结论，说明和PROC没有任何关系。

目前解决问题的办法有两个，如下：
1。去掉ORDER BY子句；
2。对SQL语句中的表达式和其它列名不一致的统统加上别名。

但是关于这个问题及相关解决办法，ORACLE理论何在？？？哪位高手知道，请指导指导，谢谢。

zlxfei

顶，请高手指导，谢谢。

zlxfei

顶

zlxfei

顶顶顶。。。。。。。。。。。。。。。。。。。。。。。。。

wsll

oracle对复合查询的规定中，不允许在任何一个select语句中使用order by字句！

wsll

oracle对复合查询的规定中，不允许在任何一个select语句中使用order by字句！

zlxfei

先引用Oracle文档中的一段
是在Oracle 8 SQL Reference中的select命令介绍一节
UNION, UNION ALL, INTERSECT, and MINUS
The UNION, UNION ALL, INTERSECT, and MINUS operators combine the results of two queries into a single result. The number and datatypes of the columns selected by each component query must be the same, but the column lengths can be different.
If more than two queries are combined with set operators, adjacent pairs of queries are evaluated from left to right. You can use parentheses to specify a different order of evaluation.

The total number of bytes in all select list expressions of a component query is limited to the size of a data block minus some overhead. The size of a data block is specified by the initialization parameter DB_BLOCK_SIZE.


You cannot use these set operators to combine the results of queries that use the THE or MULTISET keywords.


ORDER BY Clause
Use the ORDER BY clause to order the rows selected by a query. Without an ORDER BY clause, it is not guaranteed that the same query executed more than once will retrieve rows in the same order. The clause specifies either expressions or positions or aliases of expressions in the select list of the statement. Oracle returns rows based on their values for these expressions.


You can specify multiple expressions in the ORDER BY clause. Oracle first sorts rows based on their values for the first expression. Rows with the same value for the first expression are then sorted based on their values for the second expression, and so on. Oracle sorts nulls following all others in ascending order and preceding all others in descending order.


Sorting by position is useful in the following cases:

To order by a lengthy select list expression, you can specify its position, rather than duplicate the entire expression, in the ORDER BY clause.
For compound queries (containing set operators UNION, INTERSECT, MINUS, or UNION ALL), the ORDER BY clause must use positions, rather than explicit expressions. Also, the ORDER BY clause can only appear in the last component query. The ORDER BY clause orders all rows returned by the entire compound query.
The ORDER BY clause is subject to the following restrictions:

If the ORDER BY clause and the DISTINCT operator both appear in a SELECT statement, the ORDER BY clause cannot refer to columns that do not appear in the select list.
The ORDER BY clause cannot appear in subqueries within other statements.
The ORDER BY clause can contain no more than 255 expressions.

If you use the ORDER BY clause in a hierarchical query, Oracle uses the ORDER BY clause rather than the hierarchy to order the rows.

zlxfei

似乎找到了点理论依据