时间戳

首页 > 解决方案 > 如果没有二维数组，我的查询是否可以工作？

php - 如果没有二维数组，我的查询是否可以工作？

问题描述

自从我在内部寻找并尝试解决问题以来，我感到很困惑。我有PHP一个正在运行的应用程序，Oracle DB到目前为止，一些表达式是用 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

标签： phporacleplsql

解决方案

这是一个函数，它返回给定 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 数据类型的假设，并在必要时进行更正。

推荐阅读