我很困惑，因?yàn)槲蚁騼?nèi)看并試圖解決問(wèn)題。我有PHP正在運(yùn)行的應(yīng)用程序Oracle DB，到目前為止，一些表達(dá)式是在 MySQL 中編寫的，現(xiàn)在我想將這些查詢遷移到Oracle SQL函數(shù)并調(diào)用函數(shù)，而不是直接將查詢寫入模型。情況如下我有功能線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; } }}這讓我很困惑，我不知道如何編寫這段安靜的代碼。因?yàn)槲疫\(yùn)行了它們兩個(gè)的 SELECT 語(yǔ)句并且它返回了正確的結(jié)果，但我不知道該怎么辦，IF 因?yàn)檫@是二維數(shù)組，我不知道如何在PLSQL Oracle SQL 運(yùn)行第一個(gè) SELECT 語(yǔ)句時(shí)寫入它
1 回答
暮色呼如
TA貢獻(xiàn)1853條經(jīng)驗(yàn) 獲得超9個(gè)贊
以下函數(shù)返回給定 PROJECT_ID 和 TYPE 的下一個(gè) ATA 編號(hào)。
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;
請(qǐng)注意我對(duì) PROJECT_ID 數(shù)據(jù)類型的假設(shè)，并在必要時(shí)更正它。
- 1 回答
- 0 關(guān)注
- 116 瀏覽
添加回答
舉報(bào)
0/150
提交
取消