實(shí)際場景中我們實(shí)現(xiàn)數(shù)據(jù)獲取往往是多個(gè)表聯(lián)合操作
eg: 從表group,user,user-group中獲得每個(gè)分組的信息
自然連接
"select * from? \"SOFTWARE\".\"user_group\" , \"SOFTWARE\".\"group\", \"SOFTWARE\".\"user\""
+ " where? ? \"SOFTWARE\".\"group\" .? \"id\" =?? "
+ "AND "
+ " \"SOFTWARE\".\"user_group\" .\"group_id\"=? \"SOFTWARE\".\"group\" .\"id\""
+ "AND"
+ "\"SOFTWARE\".\"user_group\" .\"user_id\"=? \"SOFTWARE\".\"user\" .\"id\""
可以實(shí)現(xiàn)，但當(dāng)user-group和user中沒有與group綁定的數(shù)據(jù)時(shí)搀捷，即使存在group也查詢?yōu)榭铡?/b>
左外連接
select * from? "
+ " (\"SOFTWARE\".\"group\"? left join? \"SOFTWARE\".\"user_group\""
+ " on \"SOFTWARE\".\"group\" .\"id\"= \"SOFTWARE\".\"user_group\" .\"group_id\")? "
+ " left join? \"SOFTWARE\".\"user\" "
+ " on \"SOFTWARE\".\"user_group\" .\"user_id\"= \"SOFTWARE\".\"user\" .\"id\""
+ "where? \"SOFTWARE\".\"group\" .\"id\"= ?"
解決問題，就算user-group和group中沒有對應(yīng)數(shù)據(jù)，左連接也會(huì)將左邊的表group中內(nèi)容輸出婉徘，右連接同理
eg:刪除一個(gè)分組以及這個(gè)分組下所有和用戶的綁定關(guān)系
"delete? \"SOFTWARE\".\"group\" , \"SOFTWARE\".\"user_group\"? from? "
+ " \"SOFTWARE\".\"group\"? left join? \"SOFTWARE\".\"user_group\""
+ " on \"SOFTWARE\".\"group\" .\"id\"= \"SOFTWARE\".\"user_group\" .\"group_id\" "
+ "where? \"SOFTWARE\".\"group\" .\"id\"=?"
這個(gè)sql在mysql的環(huán)境下好用沟使，oracle不行亡驰，oracle數(shù)據(jù)庫不支持聯(lián)合update和delete，為了實(shí)現(xiàn)一樣的效果链快，提供兩種解決方案
1.開啟事務(wù)，在事務(wù)中對每個(gè)表單獨(dú)delete
2.使用嵌套操作
delete? from? "
+ "("
+ "select 1? from \"SOFTWARE\".\"group\"? left join? \"SOFTWARE\".\"user_group\""
+ " on \"SOFTWARE\".\"group\" .\"id\"= \"SOFTWARE\".\"user_group\" .\"group_id\""
+ "where "
+ "? \"SOFTWARE\".\"group\" .\"id\"=?"
+ ")"
ORA-01752: 不能從沒有一個(gè)鍵值保存表的視圖中刪除
