如果没有if-else中的二维数组，我的查询是否有效

Question

我一直很困惑，因为我在内部查看并试图解决问题。我有一个在Oracle DB中运行的PHP应用程序，到目前为止，一些表达式是在MySQL中编写的，现在我想将这些查询迁移到Oracle SQL函数，并调用函数，而不是直接向模型编写查询。情况如下我有功能线

public function getAtaNumber($data) {
if( $data["Type"] == 1 ) {

$sql = "SELECT SUM(NextNumber) + 1 AS NextNumber 
FROM (
SELECT COUNT(a1.ATAID)  AS NextNumber 
FROM ata AS a1 
WHERE a1.ProjectID = '".$data["project"]["id"]."' 
AND a1.Ata = 1 
AND a1.ParentAta = 0 
AND a1.TYPE = 1 
AND a1.BecomeExternalAtaFromInternal = 0 
UNION ALL
SELECT COUNT(a2.ATAID)  AS NextNumber 
FROM ata AS a2 
WHERE a2.ProjectID = '".$data["project"]["id"]."' 
AND a2.Ata = 1 
AND a2.ParentAta = 0 
AND a2.BecomeExternalAtaFromInternal = 1 ) AS A";

} else {

$sql = "SELECT COUNT(ata.ATAID) + 1 AS NextNumber 
FROM ata 
WHERE ProjectID = '".$data["project"]["id"]."' 
AND Ata = 1 
AND ParentAta = 0 
AND Type = 0 
ORDER BY ATAID DESC 
LIMIT 1;";

if (!$result = $conn->query($sql)) {
error_log($conn->error);
return false;
}
}
}

如果这让我感到困惑，我不知道如何编写这种平静的代码。由于我运行了SELECT语句，它们都返回了正确的结果，但我不知道该如何处理这个IF由于这是二维数组，我不知道如何在PLSQL Oracle SQL中写入它当我运行第一个SELECT语句时

SELECT SUM(NextNumber) + 1 AS NextNumber 
FROM (
SELECT COUNT(a1.ATAID)  AS NextNumber 
FROM ata a1 
WHERE a1.ProjectID = 137 
AND a1.Ata = 1 
AND a1.ParentAta = 0 
AND a1.TYPE = 1 
AND a1.BecomeExternalAtaFromInternal = 0 
UNION ALL
SELECT COUNT(a2.ATAID)  AS NextNumber 
FROM ata  a2
WHERE a2.ProjectID = 137 
AND a2.Ata = 1 
AND a2.ParentAta = 0 
AND a2.BecomeExternalAtaFromInternal = 1 
)  A
NEXTNUMBER
18

当我运行第二个SELECT查询时

SELECT COUNT(ata.ATAID) + 1 AS NextNumber 
FROM ata 
WHERE ProjectID = 137 
AND Ata = 1 
AND ParentAta = 0 
AND Type = 0 
ORDER BY ATAID DESC ; 

NEXTNUMBER
1

有人能告诉我如何在PLSQL Oracle SQL中解决这个二维数组问题吗？或者在Oracle SQL中甚至存在这种数组吗？只有一个符号，我也尝试类似的东西

IF(data"Type"==1)
SELECT
ELSE 
SELECT

还有

IF(data'Type'=1)
SELECT
ELSE 
SELECT

Answer 1

这里有一个函数，它返回给定PROJECT_ID和TYPE的下一个ATA编号。

create or replace function get_ata_number (
p_project_id in number
,p_type       in number
) return number as

l_NextNumber number;
begin  
if p_type = 1 then
SELECT SUM(NextNumber) + 1 
into l_NextNumber
FROM (
SELECT COUNT(a1.ATAID)  AS NextNumber 
FROM ata AS a1 
WHERE a1.ProjectID = p_project_id
AND a1.Ata = 1 
AND a1.ParentAta = 0 
AND a1.TYPE = 1 
AND a1.BecomeExternalAtaFromInternal = 0 
UNION ALL
SELECT COUNT(a2.ATAID)  AS NextNumber 
FROM ata AS a2 
WHERE a2.ProjectID = p_project_id
AND a2.Ata = 1 
AND a2.ParentAta = 0 
AND a2.BecomeExternalAtaFromInternal = 1 );

else 
SELECT COUNT(ata.ATAID) + 1 
into l_NextNumber
FROM ata 
WHERE ProjectID = p_project_id
AND Ata = 1 
AND ParentAta = 0 
AND Type = 0 ;
end if;
return l_NextNumber;

end get_ata_number;    

请注意我对PROJECT_ID数据类型的假设，并在必要时进行更正。