我很困惑，因為我向內看并試圖解決問題。我有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 語句時寫入它
1 回答
暮色呼如
TA貢獻1853條經驗 獲得超9個贊
以下函數返回給定 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 數據類型的假設，并在必要時更正它。
- 1 回答
- 0 關注
- 119 瀏覽
添加回答
舉報
0/150
提交
取消