如何为Ansi-Sql制作pl / sql代码？

My question may be not challenging for sql expert. i want to rewrite my sql as a ansi-sql. How can i change below sql to ansi-sql in Oracle?

我的问题可能对sql专家没有挑战性。我想将我的sql重写为ansi-sql。如何在Oracle中将sql更改为ansi-sql?

select * 
from TEST r
start with r.childid=@CHILDID 
connect by prior r.PARENTID=r.childid and r.recordstatus=1

1 个解决方案

#1

The ANSI SQL equivalent would be a recursive common table expression:

ANSI SQL等效项将是递归公用表表达式:

with recursive tree as (
   select * 
   from test
   where childid = .... --<< this is the START WITH part
   union all
   select child.* 
   from test child
     join tree parent ON child.parentid = parent.childid and child.recordstatus = 1  --<< this is the CONNECT BY part
) 
select *
from tree

I'm not 100% if you also want to apply the recordstatus = 1 condition to the recursion start.

如果你还想将recordstatus = 1条件应用于递归开始,我不是100%。

Oracle doesn't comply with the standard here, and you are not allowed to use the recursive keyword.

Oracle不符合此处的标准,并且不允许使用recursive关键字。

So you need to remove recursive from the query above (the same is true for SQL Server)

所以你需要从上面的查询中删除递归(对于SQL Server也是如此)

More details about recursive common table expressions (called "subquery factoring" in Oracle) can be found in the manual:

有关递归公用表表达式的更多详细信息(在Oracle中称为“子查询因子”)可以在手册中找到:

https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF55268

#1