ORA-01427問題的分析和解決

jeanron100發表於2015-09-06
前幾天開發的同事反饋一個問題，說前臺系統報出了ORA錯誤，希望我們能看看是什麼原因。
java.sql.SQLException: ORA-01427: single-row subquery returns more than one row
我一看到這個錯誤的第一反應就是應該是sql語句的問題，然後開發同事反饋這個程式已經用了蠻長時間了，現在突然報出了錯誤。
簡單溝通之後，我得到了對應的sql語句。
SELECT ID, PROJECTNAME, GRAPH, TYPE, PERSON, RECORDTIME, PROJECTURL , ICON, GO, COLOR, LOCATION, MANAGER , 
(SELECT NAME FROM app_iwork.WS_PRIVILEGE_NODE WHERE MENUID = pro.projectkey ) KEYNAME 
FROM app_iwork.WS_TEST_PROJECT pro 
WHERE TYPE = '495' ORDER BY LOCATION ASC ,ID DESC
從sql語句能夠猜出來錯誤應該是從子查詢裡返回的。
(SELECT NAME FROM app_iwork.WS_PRIVILEGE_NODE WHERE MENUID = pro.projectkey )
我們來看看這個語句，按照ORA-01427的錯誤，應該是這個子查詢返回了多行值。
簡單來驗證一下,首先根據type='495'能夠得到下面的查詢結果
select projectkey from app_iwork.WS_TEST_PROJECT pro WHERE TYPE = '495'
PROJECTKEY
--------------------------------------------------
1557739
1516023
1577799
374871
1584374
取出任意一條，然後按照兩個表的關聯欄位值查詢，可以看到輸出了3行資料。
SQL> SELECT NAME FROM app_iwork.WS_PRIVILEGE_NODE WHERE MENUID = 1557739;
NAME
--------------------------------------------------------------------------------
分析系統
分析系統
分析系統
所以這種情況的解決方案有幾種。
SELECT ID, PROJECTNAME, GRAPH, TYPE, PERSON, RECORDTIME, PROJECTURL , ICON, GO, COLOR, LOCATION, MANAGER , n.name
FROM app_iwork.WS_TEST_PROJECT pro ,app_iwork.WS_PRIVILEGE_NODE n
WHERE MENUID = pro.projectkey and TYPE = '495' ORDER BY LOCATION ASC ,ID DESC
這種方式可以輸出結果而不會報錯，儘管存在冗餘資料，但是也不推薦。
另外一種思路就是在子查詢中進行重複值的過濾，使用group by來完成。
SELECT ID, PROJECTNAME, GRAPH, TYPE, PERSON, RECORDTIME, PROJECTURL , ICON, GO, COLOR, LOCATION, MANAGER , 
(SELECT NAME FROM app_iwork.WS_PRIVILEGE_NODE WHERE MENUID = pro.projectkey group by name ) KEYNAME 
FROM app_iwork.WS_TEST_PROJECT pro 
WHERE TYPE = '495' ORDER BY LOCATION ASC ,ID DESC
還有一種思路就是和開發確認，刪除冗餘的資料，這種方案不用修改程式碼，還是相對來說可以實現的一種方式。
和開發同事簡單溝通，這個表中還是存在部分的髒資料，修改之後，問題就解決了。
如果對這個問題進一步改進，可以在確認這個表結構的基礎上，看看能夠新增相應的約束，這樣也能夠保證表中的資料不會存在冗餘，避免後續出現此類的問題。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1792019/，如需轉載，請註明出處，否則將追究法律責任。

相關文章