SELECT æ–‡

æ§‹æ–‡

���
[ORDER BY 節]
[FOR UPDATE 節]
WITH {RR|RS|CS|UR}

SELECTæ–‡ã?¯å•?å?ˆã?›ã?¨ã€?çœ?ç•¥å?¯èƒ½ã?ªORDER BY 節ã?¨FOR UPDATE 節ã?‹ã‚‰æ§‹æˆ?ã?•れã?¾ã?™ã€‚ SELECTæ–‡ã?¯ã€?å•?å?ˆã?›ã?®æœ€åˆ?ã?®å?˜èªžã?Œä¸€èˆ¬çš„ã?«SELECTã?ªã?®ã?§ã€?ã?“ã?®å??å‰?ã?Œä¸Žã?ˆã‚‰ã‚Œã?¦ã?„ã?¾ã?™ã€‚(å•?å?ˆã?›ã?¯é?¸æŠžå¼?以外ã?«ã‚‚ã€?VALUESå¼?ã€?UNIONã€?INTERSECTã€?EXCEPTå¼?ã?§ã?‚ã‚‹å ´å?ˆã?Œã?‚りã?¾ã?™ã€‚)

ORDER BY 節ã?«ã‚ˆã‚Šã€?ResultSetã?®ä¸¦ã?³é †ã?Œæ˜Žç¤ºã?•れã?¾ã?™ã€‚FOR UPDATE 節ã?«ã‚ˆã‚Šã€?çµ?æžœã?®ã‚«ãƒ¼ã‚½ãƒ«ã‚’æ›´æ–°å?¯èƒ½ã?«ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚SELECTæ–‡ã?«ã?¯FOR EACH ONLY 節を置ã??ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚FOR EACH ONLY 節ã?¨FOR READ ONLY節ã?¯å?Œã?˜ã‚‚ã?®ã?§ã?™ã€‚

SELECTæ–‡ã?«ã?¦WITH {RR|RS|CS|UR}ã?¨ã?„ã?†æ›¸ã??æ–¹ã?§åˆ†é›¢ãƒ¬ãƒ™ãƒ«ã‚’指定ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚

例


-- SAL+BONUS+COMMã?¨å??å‰?を並ã?¹ã?¦ã€?TOTAL_PAYã?¨ã?„ã?†å??å‰?ã‚’ã?¤ã?‘ã€?
-- ã??ã?®å??å‰?ã?§ä¸¦ã?³æ›¿ã?ˆã‚‹ã€‚
SELECT FIRSTNME, SALARY+BONUS+COMM AS TOTAL_PAY
     FROM EMPLOYEE
     ORDER BY TOTAL_PAY
-- FOR UPDATE節を指定�る����
-- PROJECT���表��る�開始日時(PRSTDATE)�終了日時(PRENDATE)���列を更新�る�
-- 更新�能�カーソルを作��る
SELECT PROJNO, PRSTDATE, PRENDATE
      FROM PROJECT
      FOR UPDATE OF PRSTDATE, PRENDATE
-- ��文��り分離レベルをRR��る。
SELECT * 
FROM Flights 
WHERE flight_id BETWEEN 'AA1111' AND 'AA1112' 
WITH RR
SELECTæ–‡ã?¯ResultSetã‚’è¿”ã?—ã?¾ã?™ã€‚ カーソルã?¯ResultSetã?«ã?‚る行ã?¸ã?®ãƒ?インタã?§ã?™ã€‚Javaã?®ã‚¢ãƒ—リケーションã?§ã?¯ã€?ResultSetsã?«ã?¯å¯¾å¿œã?—ã?ŸSQLã?®ã‚«ãƒ¼ã‚½ãƒ«ã?Œã?‚りã?¾ã?™ã€‚カーソルã?¯æ›´æ–°å?¯èƒ½ã?¨ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚ã?—ã?Ÿã?Œã?£ã?¦ã€?å•?å?ˆã?›ã?Œå¾Œã?«è¿°ã?¹ã‚‹æ›´æ–°å?¯èƒ½ã?®æ?¡ä»¶ã?«é?©å?ˆã?™ã‚‹ã?®ã?§ã?‚れã?°ã€?ResultSetを通ã?—ã?¦è¡Œã?®æ›´æ–°ã‚„削除ã?Œå?¯èƒ½ã?§ã?™ã€‚FOR UPDATE 節ã?«ã‚ˆã‚Šã€?SQLã?®ã‚³ãƒ³ãƒ‘イル時ã?«SELECTæ–‡ã?Œæ›´æ–°å?¯èƒ½ã?ªã‚«ãƒ¼ã‚½ãƒ«ã?®è¦?ä»¶ã?«é?©å?ˆã?™ã‚‹ã?‹ã‚’確ã?‹ã‚?る事やã€?ã?‚ã‚‹ã?„ã?¯åˆ—ã‚’æ›´æ–°å?¯èƒ½ã?ªã‚‚ã?®ã?®ã?¿ã?«é™?定ã?™ã‚‹äº‹ã?Œã?§ã??ã?¾ã?™ã€‚
注: ORDER BY節ã?«ã‚ˆã‚ŠSELECTã?®çµ?果を並ã?³æ›¿ã?ˆã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚ORDER BY節ã?Œã?ªã?„å ´å?ˆã€?çµ?æžœã?¯ç„¡ä½œç‚ºã?ªä¸¦ã?³é †ã?§ã?™ã€‚

更新�能�カーソル�よ�ResultSet��件

更新�能�カーソル���表���純�SELECT文�よるも����れ��り��ん。 更新�能�ResultSetを得る���SELECT文��更新�能�カーソル���制�を����。更新�能�カーソル��る�����:
  • SELECTæ–‡ã?«ã?¯ORDER BY節ã?Œã?‚ã?£ã?¦ã?¯ã?ªã‚Šã?¾ã?›ã‚“。
  • å•?å?ˆã?›ã?¯é?¸æŠžå¼?ã?§ã?ªã?‘れã?°ã?ªã‚Šã?¾ã?›ã‚“。
  • é?¸æŠžå¼?ã?«ã?¯ä»¥ä¸‹ã?Œã?‚ã?£ã?¦ã?¯ã?ªã‚Šã?¾ã?›ã‚“。
    • DISTINCT
    • 集約
    • GROUP BY 節
    • HAVING 節
    • ORDER BY 節
  • å•?å?ˆã?›ã?®FROM節ã?«ã?¯ä»¥ä¸‹ã?Œã?‚ã?£ã?¦ã?¯ã?ªã‚Šã?¾ã?›ã‚“。
    • 二ã?¤ç›®ä»¥é™?ã?®è¡¨
    • 表以外ã?®å??å‰?
    • é?¸æŠžå¼?s
    • 副å•?ã?„å?ˆã‚?ã?›
注: 既定ã?§ã‚«ãƒ¼ã‚½ãƒ«ã?¯èª­ã?¿å?–り専用ã?§ã?™ã€‚æ›´æ–°å?¯èƒ½ã?ªã‚«ãƒ¼ã‚½ãƒ«ã‚’å¾—ã‚‹ã?Ÿã‚?ã?«ã?¯ã€?上記ã?®è¦?件を満ã?Ÿã?™ã?“ã?¨ã?«åŠ ã?ˆã?¦ã€?ResultSetã?®å?Œæ™‚並行モードをResultSet.CONCUR_UPDATABLEã?¨ã?™ã‚‹ã?‹ã€?SELECTæ–‡ã?®FOR節ã?«FOR UPDATEを記述ã?—ã?ªã??ã?¦ã?¯ã?ªã‚Šã?¾ã?›ã‚“。(FOR UPDATE 節をå?‚ç…§ã?—ã?¦ã??ã? ã?•ã?„。)

SQL言語ã?§ã?¯ã‚«ãƒ¼ã‚½ãƒ«ã?«å??å‰?を与ã?ˆã‚‹äº‹ã?Œã?§ã??ã?¾ã?›ã‚“。ã??ã?®ã?‹ã‚?りã?«JDBC APIã?«ã‚ˆã‚Šã‚«ãƒ¼ã‚½ãƒ«ã?«å??å‰?を与ã?ˆã?Ÿã‚Šã€?システムã?Œã‚«ãƒ¼ã‚½ãƒ«ã?«ä¸Žã?ˆã?Ÿå??å‰?ã‚’å?–å¾—ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚詳細ã?«ã?¤ã?„ã?¦ã?¯Derby ディベロッパーズ ガイドã?®ã‚«ãƒ¼ã‚½ãƒ«ã?¸ã?®å‘½å??ã?Šã‚ˆã?³å??å‰?ã?®åˆ©ç”¨ã‚’å?‚ç…§ã?—ã?¦ä¸‹ã?•ã?„。

文��存

SELECTã?¯ã€?å•?å?ˆã?›ã?®è¡¨ã‚„ビューã€?ã??れらã?®è¡¨ã?¸ã?®ã‚¢ã‚¯ã‚»ã‚¹ãƒ‘スã?«ã?¦é?¸æŠžã?•れã?Ÿã‚³ãƒ³ã‚°ãƒ­ãƒžãƒªãƒƒãƒˆ(ヒープやインデックスã?ªã?©ã?®è¨˜æ†¶ã?®å?˜ä½?ã?§ã?™ã€‚)ã?«ä¾?å­˜ã?—ã?¾ã?™ã€‚ CREATE INDEXã?§ã?¯æº–å‚™ã?•れã?ŸSELECTæ–‡ã?Œç„¡åйã?«ã?ªã‚‹ã?“ã?¨ã?¯ã?‚りã?¾ã?›ã‚“。 DROP INDEXæ–‡ã?§ã?¯ã‚‚ã?—破棄ã?•れる索引ã?Œæ–‡ã?«ã?¦ã‚¢ã‚¯ã‚»ã‚¹ãƒ‘スã?ªã‚‰ã€?準備ã?•れã?ŸSELECTæ–‡ã?¯ç ´æ£„ã?•れã?¾ã?™ã€‚ ã‚‚ã?—SELECTã?«ãƒ“ューã?Œå?«ã?¾ã‚Œã?¦ã?„れã?°ã€?ビューã?Œä¾?å­˜ã?™ã‚‹ãƒ‡ã‚£ã‚¯ã‚·ãƒ§ãƒŠãƒªã?®æƒ…å ±ã?«ã‚‚ä¾?å­˜ã?—ã?¾ã?™ã€‚(CREATE VIEW 文をå?‚ç…§ã?—ã?¦ã??ã? ã?•ã?„。)

SELECTã?®ã‚«ãƒ¼ã‚½ãƒ«ã?«å¯¾ã?—ã?¦ã€?準備ã?•れã?ŸUPDATE WHERE CURRENTã?‚ã‚‹ã?„ã?¯DELETE WHERE CURRENTæ–‡ã?¯SELECTã?«ä¾?å­˜ã?—ã?¾ã?™ã€‚java.sql.Statement.close ã?«ã‚ˆã‚ŠSELECTã‚’å?–り除ã??ã?¨ã€?UPDATE WHERE CURRENT ã?Šã‚ˆã?³ DELETE WHERE CURRENTã?¯ç„¡åйã?¨ã?ªã‚Šã?¾ã?™ã€‚

SELECTã?¯å•?å?ˆã?›ã?«ã?‚ã‚‹ã?‚らゆる別å??ã?«ä¾?å­˜ã?—ã?¾ã?™ã€‚別å??を破棄ã?™ã‚‹ã?“ã?¨ã?«ã‚ˆã‚Šã€?ã‚‚ã?—準備ã?•れã?ŸSELECTæ–‡ã?«ã?¦åˆ¥å??ã?Œä½¿ã‚?れã?¦ã?„ã‚‹ã?ªã‚‰ã€?ã??ã?®æ–‡ã?¯ç„¡åйã?«ã?ªã‚Šã?¾ã?™ã€‚