[B! oracle] at_homeã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

at_home id:at_home

oracleã«é–¢ã™ã‚‹at_homeã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯ (26)

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

${{author_name}}$
{{author_name}}{{created}}
{{ #comment }}{{ comment }}{{ /comment }}
- {{ label }}

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

Oracleã§ã¯Limitå¥ãŒä½¿ãˆãªã„
ãƒšãƒ¼ã‚¸ãƒ³ã‚°ã§Selectçµæžœã®ä¸€éƒ¨ã®ãƒ‡ãƒ¼ã‚¿ã‚’ã—ãŸã„ã¨ãã€PostgreSQLã‚„MySQLã§ä½¿ç”¨ã§ãã‚‹Limitå¥ã¯ã¨ã¦ã‚‚ä¾¿åˆ©ã€‚ãŸã¨ãˆã°Selectçµæžœã®10ã‹ã‚‰14ç•ªç›®ã¾ã§ã‚’å–å¾—ã™ã‚‹ã«ã¯select * from emp where Limit 10,5ã§ã‚‚Oracleã§ã¯Limitå¥ã¯ä½¿ãˆãªã„... ã¨ã„ã†ã“ã¨ã§Oracleã§ã¯ROWNUMã‚’ä½¿ç”¨ã—ã¦ã‚„ã£ã¦ã¿ã‚‹ã€‚(Oracle10g Windowsç‰ˆã§æ¤œè¨¼) ã¨ã“ã‚ãŒã“ã®ROWNUMã¯ä¸€ç‹ç¸„ã§ã¯ã„ã‹ãªã„ã®ã§ã‚ã£ãŸã€‚ Order By å¥ã‚’ä½¿ã£ã¦ä¸¦ã¹æ›¿ãˆã‚’ã™ã‚‹ã¨ã€ROWNUMãŒ...select rownum rn ,t.ä½æ‰€ã‚³ãƒ¼ãƒ‰ ,t.å¸‚åŒºç”ºæ‘å ,t.ä½æ‰€1 ,t.ä½æ‰€2 from tb_ä½æ‰€ãƒžã‚¹ã‚¿ t order by ä½æ‰€2;çµæžœ RNä½æ‰€ã‚³ãƒ¼ãƒ‰å¸‚åŒºç”ºæ‘åä½æ‰€1ä½æ‰€2
at_home 2011/05/16
Oracle

é–‹ç™º

DB

sql

limit
ãƒªãƒ³ã‚¯
èª•ç”Ÿæ—¥ã‹ã‚‰å¹´é½¢ã‚’ç®—å‡ºã™ã‚‹ - ã‚ªãƒ©ã‚¯ãƒ«ãƒ»Oracle SQL é–¢æ•°ãƒªãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹
å¹´é½¢ã‚’è¨ˆç®—ã™ã‚‹ï¼ˆæš¦å¹´é½¢ï¼‰ æº€å¹´é½¢ ä¸€èˆ¬çš„ãªå¹´é½¢ ç”Ÿã¾ã‚ŒãŸå¹´ã¯ 0æ³ ã¨ã—ã¦ç¿Œå¹´ã®èª•ç”Ÿæ—¥ã«å¹´é½¢ +1 ã™ã‚‹ã€‚ æ°‘æ³•ãªã©ç‰¹å®šã®çŠ¶æ³ã§ä½¿ç”¨ã•ã‚Œã‚‹å¹´é½¢ ç”Ÿã¾ã‚ŒãŸå¹´ã¯ 0æ³ ã¨ã—ã¦ç¿Œå¹´ã®èª•ç”Ÿæ—¥ã®å‰æ—¥ã«å¹´é½¢ +1 ã™ã‚‹ã€‚ å…¥å¦è³‡æ ¼ã‚„ä¸€éƒ¨ã®æ³•ä»¤ã«ã¯æœˆæœ«ã«ãŠã‘ã‚‹å¹´é½¢ã‚’æ¡ä»¶ã«ã—ã¦ã„ã‚‹ã“ã¨ãŒå¤šã„ã€‚1æ—¥ç”Ÿã¾ã‚Œã®äººã¯å‰ã®æœˆæœ«ã«ã¯ å¹´é½¢ãŒåŠ ç®—ã•ã‚Œã¦ã„ã‚‹ã“ã¨ã«ãªã£ã¦ã„ã‚‹ã®ã§ 1æ—¥ç”Ÿã¾ã‚Œã®äººã¯è¡Œæ”¿ã‚µãƒ¼ãƒ“ã‚¹ï¼ˆå°±å¦ã€å¹´é‡‘ã‚„æ‰€å¾—ç¨Žãªã©ï¼‰ã‚’å—ã‘ã‚‹ã¨ãã«ã¯è¦æ³¨æ„ã€‚ï¼ˆã€Œä½†ã—æ›¸ãã€ã‚‚ã•ã‚Œã¦ã„ã‚‹ã‚‰ã—ã„ï¼‰ æ•°ãˆå¹´ ç”Ÿã¾ã‚ŒãŸå¹´ã¯ 1æ³ ã¨ã—ã¦ç¿Œå¹´ã‹ã‚‰ã¯ 1æœˆ1æ—¥ã‚’èª•ç”Ÿæ—¥ã¨ã™ã‚‹ã€‚ ã¤ã¾ã‚Š æ•°ãˆå¹´ = è¨ˆç®—ã—ãŸã„å¹´ - èª•ç”Ÿã—ãŸå¹´ + 1 ã€‚ å„€ç¤¼ãªã©ã«ã¯æ•°ãˆå¹´ãŒä½¿ã‚ã‚Œã¦ã„ã‚‹ã‚ˆã†ã§ã‚ã‚‹ã€‚ ä¸ƒäº”ä¸‰ã‚„é‚„æš¦ï¼ˆæ•°ãˆå¹´ã§ 61æ³ï¼‰ã‚„å–œå¯¿ï¼ˆæ•°ãˆå¹´ã§ 77æ³ï¼‰ã€åŽ„é™¤ã‘ã€åŽ„æ‰•ã„ãªã©ã«ä½¿ã‚ã‚Œã‚‹ã€‚ YEARS_OF_AGE ãƒ•ã‚¡ãƒ³ã‚¯ã‚·ãƒ§ãƒ³ (èª•ç”Ÿæ—¥ã‹ã‚‰å¹´é½¢ã‚’ç®—å‡º
at_home 2011/03/22
SQL

å¹´é½¢

Oracle

tips
ãƒªãƒ³ã‚¯
EXTRACT - ã‚ªãƒ©ã‚¯ãƒ«ãƒ»Oracle SQL é–¢æ•°ãƒªãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹
EXTRACT é–¢æ•°ã®å†…å®¹ æŒ‡å®šã—ãŸæ—¥æ™‚ãƒ•ã‚£ãƒ¼ãƒ«ãƒ‰ã®å€¤ã‚’å–ã‚Šå‡ºã™ã€‚ DATE å€¤ã‹ã‚‰ã¯ YEARã€MONTH ãŠã‚ˆã³ DAY ã®ã¿ã‚’æŠ½å‡ºã§ãã€TIMESTAMP å€¤ã‹ã‚‰ã¯ SECOND ã¾ã§å–å¾—ãŒå¯èƒ½ã€‚(TIMEZONE_HOURã¨TIMEZONE_MINUTE ã¯ TIMESTAMP WITH TIME ZONE å€¤ã‹ã‚‰ã®ã¿) DATE åž‹ã‹ã‚‰ HOUR ã‚’æŠ½å‡ºã™ã‚‹ã¨ ORA-30076: æŠ½å‡ºã‚½ãƒ¼ã‚¹ã®æŠ½å‡ºãƒ•ã‚£ãƒ¼ãƒ«ãƒ‰ãŒç„¡åŠ¹ã§ã™ ãŒç™ºç”Ÿã™ã‚‹ã€‚ ANSI ã§ã¯ DATE å€¤ã¯æ—¥ã«ã¡ã¾ã§ã¨å®šç¾©ã•ã‚Œã¦ã„ã‚‹ãŸã‚ã«ç™ºç”Ÿã™ã‚‹ç¾è±¡ã§ã‚ã‚‹ã€‚(ANSI-SQL92 ã®ãƒ‡ãƒ¼ã‚¿ã‚¿ã‚¤ãƒ—ã¯ DATE, TIME, TIMESTAMP) EXTRACT ä½¿ç”¨ä¾‹ SQL> selectÂ dt, EXTRACT(YEAR FROM dt), EXTRACT(MONTH FROM dt), EXTRACT(DAY FROM
at_home 2011/03/22
SQL

Oracle

Tips
ãƒªãƒ³ã‚¯
CASE å¼ ã¨ CASE æ–‡ã®é•ã„ - ã‚ªãƒ©ã‚¯ãƒ«ãƒ»Oracleã‚’ãƒžã‚¹ã‚¿ãƒ¼ã™ã‚‹ãŸã‚ã®åŸºæœ¬ã¨ä»•çµ„ã¿
CASE å¼ ã¨ CASE æ–‡ ã®é•ã„ SQL ã¯ CASE å¼ ã®ã¿ã§ CASE æ–‡ ã¯è¨˜è¿°ã§ããªã„ã€‚ CASE æ–‡ã¯ PL/SQL ã§è¨˜è¿°ã™ã‚‹ã‚¹ãƒ†ãƒ¼ãƒˆãƒ¡ãƒ³ãƒˆã§ã‚ã‚‹ã€‚ CASE å¼ ï¼š æ¡ä»¶æ¯Žã®ã‚»ãƒŸã‚³ãƒãƒ³ãŒãªãã€CASE ã®æœ€å¾Œã¯ END ã§çµ‚äº†ã™ã‚‹ãƒ•ã‚¡ãƒ³ã‚¯ã‚·ãƒ§ãƒ³ã®ã‚ˆã†ãªã‚‚ã®ã§ã‚ã‚‹ã€‚å½“ç„¶ã§ã‚ã‚‹ãŒå¼ãªã®ã§è©•ä¾¡ã‚„ä»£å…¥æ–‡ã«ã‚‚ä½¿ç”¨ã§ãã‚‹ã€‚ CASE æ–‡ ï¼š è¤‡æ•°ã®ã‚¹ãƒ†ãƒ¼ãƒˆãƒ¡ãƒ³ãƒˆã‹ã‚‰æ§‹æˆã•ã‚Œã‚‹ã€‚æ§‹æ–‡ã®é•ã„ã¯ä»¥ä¸‹å‚ç…§ å˜ç´” CASE (simple case) ã®æ›¸ãæ–¹ã®é•ã„ CASE å¼ - CASE ã§å§‹ã¾ã‚Š END ã§çµ‚äº†ã™ã‚‹ã€‚ expr ã¨ comparison_expr ã‚’æ¯”è¼ƒã—çœŸå½åˆ¤å®šã‚’ã™ã‚‹ã€‚ CASEÂ expr -- CASE å¼ WHEN comparison_expr1 THEN ret_expr1 Â Â -- å¼ã‚„å€¤ã‚’è¨˜è¿°ã™ã‚‹ WHEN comparison_expr2 THEN
at_home 2011/03/22
SQL

DB

Oracle

tips
ãƒªãƒ³ã‚¯
Oracle
2025å¹´2æœˆ13æ—¥ã«æ±äº¬ã§é–‹å‚¬ã•ã‚Œã‚‹ã€ã‚ªãƒ©ã‚¯ãƒ«ã®ãƒ†ã‚¯ãƒŽãƒã‚¸ãƒ¼ãŠã‚ˆã³ãƒãƒƒãƒˆãƒ¯ãƒ¼ã‚ãƒ³ã‚°ã‚¤ãƒ™ãƒ³ãƒˆã€ŒCloudWorld Tourã€ã«ãœã²ã”å‚åŠ ãã ã•ã„ã€‚ã‚ªãƒ©ã‚¯ãƒ«ã®ã‚¨ã‚°ã‚¼ã‚¯ãƒ†ã‚£ãƒ–ã‚„å°‚é–€å®¶ã€ãƒ‘ãƒ¼ãƒˆãƒŠãƒ¼ã€åŒæ¥ä»–ç¤¾ã¨äº¤æµã—ã€ã‚¯ãƒ©ã‚¦ãƒ‰ã‚„AIãªã©ã«ã¤ã„ã¦æ„è¦‹ã‚’äº¤ã‚ã—ã¾ã—ã‚‡ã†ã€‚
at_home 2010/12/01
Oracle

qa

è§£æ±º
ãƒªãƒ³ã‚¯
Oracle9i ã‚¹ãƒ†ãƒƒãƒ—ãƒžã‚¹ã‚¿ãƒ¼
at_home 2010/06/08
oracle

ã‚ãƒ¥ãƒ¼

tips
ãƒªãƒ³ã‚¯
Oracleã®æ©Ÿèƒ½ã‚’ä½¿ã£ã¦è¡¨ã®çµåˆã‚’é«˜é€ŸåŒ–ã™ã‚‹ï¼ˆ1/4ï¼‰ ï¼ ï¼ IT
æœ¬é€£è¼‰ã§ã¯ã€Oracleãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã®ãƒ‘ãƒ•ã‚©ãƒ¼ãƒžãƒ³ã‚¹ãƒ»ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã®ä¸ã‹ã‚‰ã€ç‰¹ã«SQLã®ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã«æ³¨ç›®ã—ã¦ã€å®Ÿè·µãƒ¬ãƒ™ãƒ«ã®æ‰‹æ³•ã‚’è§£èª¬ã™ã‚‹ã€‚èªè€…ã¯Oracleãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã®ã‚¢ãƒ¼ã‚ãƒ†ã‚¯ãƒãƒ£ã‚’ç†è§£ã—ã€é‹ç”¨ç®¡ç†ã®å®Ÿå‹™çµŒé¨“ã‚’ç©ã‚“ã§ã„ã‚‹ã“ã¨ãŒæœ›ã¾ã—ã„ã€‚å¯¾è±¡ã¨ã™ã‚‹ãƒãƒ¼ã‚¸ãƒ§ãƒ³ã¯ç¾çŠ¶ã§åºƒãä½¿ã‚ã‚Œã¦ã„ã‚‹Oracle9iã®æ©Ÿèƒ½ã‚’åŸºæœ¬ã¨ã™ã‚‹ãŒã€Oracle 10gã§æœ‰åŠ¹ãªæƒ…å ±ã‚‚éšæ™‚ç´¹ä»‹ã—ã¦ã„ãã€‚ï¼ˆç·¨é›†å±€ï¼‰ é€£è¼‰ç›®æ¬¡ å‰å›žã€Œè¡¨ã®çµåˆã‚’æ¥µã‚ã‚‹ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ãƒ»ãƒ†ã‚¯ãƒ‹ãƒƒã‚¯ã€ã§ã¯ã€çµžã‚Šè¾¼ã¿æ¡ä»¶ãŒãªã„çµåˆã‚’å«ã‚€SQLã€çµžã‚Šè¾¼ã¿æ¡ä»¶ã®ã‚ã‚‹çµåˆã‚’å«ã‚€SQLã«ã¤ã„ã¦å®Ÿä¾‹ã‚’åŸºã«æœ€é©ãªçµåˆæ–¹æ³•ã‚’èª¬æ˜Žã—ã¾ã—ãŸã€‚ ä»Šå›žã¯ã€Oracleã®æ©Ÿèƒ½ã§ã‚ã‚‹ã€Œãƒžãƒ†ãƒªã‚¢ãƒ©ã‚¤ã‚ºãƒ‰ãƒ»ãƒ“ãƒ¥ãƒ¼ã€ã€ŒBIT MAP JOIN INDEXã€ã€Œã‚¯ãƒ©ã‚¹ã‚¿ã€ã‚’åˆ©ç”¨ã—ãŸçµåˆå‡¦ç†ã«é–¢ã™ã‚‹ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ãƒ»ãƒ†ã‚¯ãƒ‹ãƒƒã‚¯ã«ã¤ã„ã¦èª¬æ˜Žã—ã¾ã™ã€‚ ãƒžãƒ†ãƒªã‚¢ãƒ©ã‚¤ã‚ºãƒ‰ãƒ»ãƒ“ãƒ¥ãƒ¼ã‚’ç†è§£ã™ã‚‹ çµåˆå‡¦ç†ãª
at_home 2010/06/08
ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹

Oracle

SQL

ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°
ãƒªãƒ³ã‚¯
ã‚ªãƒ©ã‚¯ãƒ« ãƒžãƒ†ãƒªã‚¢ãƒ©ã‚¤ã‚ºãƒ‰ãƒ“ãƒ¥ãƒ¼ãƒ¡ãƒ¢(Hishidama's Materialized view for Oracle Memo)
ãƒªãƒ•ãƒ¬ãƒƒã‚·ãƒ¥æ–¹æ³•ã‚’æŒ‡å®šã—ãªã„å ´åˆã¯forceã«ãªã‚‹ã€‚ å¾Œã‹ã‚‰ãƒªãƒ•ãƒ¬ãƒƒã‚·ãƒ¥æ–¹æ³•ã‚’å¤‰ãˆã‚‹ã“ã¨ã‚‚å‡ºæ¥ã‚‹ã€‚ SQL> alter materialized view MV_TEST refresh fast; ãƒžãƒ†ãƒªã‚¢ãƒ©ã‚¤ã‚ºãƒ‰ãƒ»ãƒ“ãƒ¥ãƒ¼ãŒå¤‰æ›´ã•ã‚Œã¾ã—ãŸã€‚ å®Œå…¨ãƒªãƒ•ãƒ¬ãƒƒã‚·ãƒ¥ ãƒžãƒ†ãƒ“ãƒ¥ãƒ¼ã‚’è¨ˆç®—ã—ç›´ã™éš›ã«ã€å…¨ãƒ‡ãƒ¼ã‚¿ã‚’ä½œã‚Šç›´ã™ã€‚ãƒ‡ãƒ¼ã‚¿ã®å¤‰æ›´ç®‡æ‰€ãŒå°‘ãªã„ãªã‚‰åŠ¹çŽ‡ã¯æ‚ªã„ã“ã¨ã«ãªã‚‹ãŒã€ãƒã‚¸ãƒƒã‚¯ã¯ä¸€ç•ªå˜ç´”ã ã€‚ å®Œå…¨ãƒªãƒ•ãƒ¬ãƒƒã‚·ãƒ¥ã®ãƒžãƒ†ãƒ“ãƒ¥ãƒ¼ã‚’ä½œã‚‹éš›ã«ã¯ã€ã€Œrefresh completeã€ã‚’æŒ‡å®šã™ã‚‹ã€‚å€‹äººçš„ã«ã¯ã€Œrefresh allã€ã®æ–¹ãŒåˆ†ã‹ã‚Šæ˜“ã‹ã£ãŸã¨æ€ã†ã‘ã©â€¦ã‚ãŸã—ã‚ƒè‹±èªžåœã®äººé–“ã˜ã‚ƒãªã„ã‹ã‚‰ãªï½ž create materialized view MV_TEST refresh complete as select YYMM_GROUP, SHOP_CODE, sum(AMOUNT) SUM_AMOUNT fr
at_home 2010/06/08
DB

SQL

Oracle

ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹

ãƒžãƒ†ãƒ“ãƒ¥ãƒ¼
ãƒªãƒ³ã‚¯
ï¼¯ï¼²ï¼¡ï¼£ï¼¬ï¼¥ï¼ã‚ªãƒ©ã‚¯ãƒ«ï¼³ï¼±ï¼¬ãƒªãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹ï¼ˆSQLPLUSï¼‰
ã‚¹ãƒãƒ³ã‚µãƒ¼ãƒ‰ã€€ãƒªãƒ³ã‚¯ ä¾‹ï¼‘ï¼‰ã€€è¡¨ã€Œempã€ã®SELECTçµæžœã‚’ã€ã‚«ãƒ³ãƒž','åŒºåˆ‡ã‚Šã®CSVãƒ•ã‚¡ã‚¤ãƒ«ã‚’ã€Œc\tmp\emp.csvã€ã«å‡ºåŠ›ã—ã¾ã™ã€‚ SQL> set echo off SQL> set linesize 1000 SQL> set pagesize 0 SQL> set trimspool on SQL> set feedback off SQL> set colsep ',' SQL> spool c:\temp\emp.csv SQL> select * from emp; E01,D01,åå—ï¼‘ã€€ã€€,åå‰ï¼‘ã€€ã€€, 10 E02,D01,åå—ï¼’ã€€ã€€,åå‰ï¼’ã€€ã€€, 50 E03,D02,åå—ï¼“ã€€ã€€,åå‰ï¼“ã€€ã€€, 100 SQL> spool off
at_home 2010/03/16
oracle

ãƒªãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹

sqlplus

CSV

SQL
ãƒªãƒ³ã‚¯
Oracle/SQL*Plus - ã„ãŒã£ã¡ã®è¦šæ›¸
æ–‡å—åˆ— â€ ä½•ã‚‚è¨å®šã—ãªã„å ´åˆã¯ã€linesizeã‚„longãƒ‘ãƒ©ãƒ¡ãƒ¼ã‚¿ã«ã‚ˆã£ã¦å¤‰åŒ–ã—ã¾ã™ãŒã€ä»¥ä¸‹ã®ã‚ˆã†ã«ãªã‚Šã¾ã™ã€‚ SQL> select TABLE_NAME,TABLESPACE_NAME from user_tables; TABLE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME -------------------------------------------------------------------------------- IT EM SAMPLE_TBS PLAN_TABLE SAMPLE_TBS ã‚«ãƒ©ãƒ æ¯Žã«æ¡æ•°ã‚’æŒ‡å®šã™ã‚‹ã€‚æ–‡å—åˆ—ã«ã¯ã€aNNNã¨æ¡æ•°ã‚’æŒ‡å®šã—ã¾ã™ã€‚ å®Ÿéš›ã«ã‚„ã£ã¦ã¿ã‚‹ã¨ã€ä»¥ä¸‹ã®ã‚ˆã†ã«è¦‹ã‚„ã™ãè¡¨ç¤ºã™ã‚‹
at_home 2010/01/15
oracle

SQL
ãƒªãƒ³ã‚¯
WEBï¼’MEMO =PHPã€JavaScriptã€CSSã€DBã€Javaã€AIRã€Flexã€Rubyã€Railsç‰ã®ãƒ¡ãƒ¢= ORACLEã‚¨ãƒ©ãƒ¼
PHPã€JavaScriptã€CSSã€Javaã€AIRã€Flexã€Rubyã€Railsï¼ˆRuby on Railsï¼‰ã€DBï¼ˆMySQLã€Posï½”greSQLã€ORACLE)ã€ã‚µãƒ¼ãƒæ§‹ç¯‰ç®¡ç†ãªã© ä»•äº‹ã§å¿˜ã‚Œãã†ãªã“ã¨ã‚’æ›¸ã„ã¦ã¾ã™ã€‚
at_home 2009/12/17
oracle
ãƒªãƒ³ã‚¯
ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’æ‰‹ã£å–ã‚Šæ—©ãï¼£ï¼³ï¼¶å½¢å¼ã§å‡ºåŠ›ã™ã‚‹ï¼ˆSQL*Plusï¼‰
ãƒ†ã‚ã‚¹ãƒˆãƒ‡ãƒ¼ã‚¿ã‚’å–ã‚Šè¾¼ã‚€ã«ã¯ã€SQL*Loaderã¨ã„ã†ãƒ¦ãƒ¼ãƒ†ã‚£ãƒªãƒ†ã‚£ãŒç”¨æ„ã•ã‚Œã¦ã„ã¾ã™ãŒã€ é€†ã®SQL*Saverãªã©ã¨ã„ã†ã‚‚ã®ã¯ç”¨æ„ã•ã‚Œã¦ã„ã¾ã›ã‚“ã€‚ CSVå½¢å¼ã®ãƒ†ã‚ã‚¹ãƒˆãƒ•ã‚¡ã‚¤ãƒ«ã«ãƒ‡ãƒ¼ã‚¿ã‚’å‡ºåŠ›ã—ãŸã„å ´åˆã¯ã€PL/SQLã§å¤–éƒ¨ãƒ•ã‚¡ã‚¤ãƒ«ã«æ›¸ãå‡ºã™æ–¹æ³•ã‚„ MicroSoft ACCESSã€€ãªã©ã®ã‚½ãƒ•ãƒˆã‹ã‚‰ãƒªãƒ³ã‚¯ã—å–ã‚Šå‡ºã™æ–¹æ³•ãŒè€ƒãˆã‚‰ã‚Œã¾ã™ã€‚ MicroSoft ACCESSã‹ã‚‰ãƒªãƒ³ã‚¯ã™ã‚‹æ–¹æ³•ã¯ã€å°‘é‡ã®ãƒ‡ãƒ¼ã‚¿ã®å ´åˆã¯ãŠæ‰‹è»½ã§è‰¯ã„ã®ã§ã™ãŒã€ æ•°ä¸‡ä»¶ã‚ªãƒ¼ãƒ€ã®ãƒ‡ãƒ¼ã‚¿ã‚’æ‰±ã†å ´åˆã€ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’è¡¨ç¤ºã™ã‚‹ã ã‘ã§æ™‚é–“ãŒæŽ›ã‹ã£ãŸã‚Šã¨ ä¸€é•·ä¸€çŸã§ã‚ã£ãŸã‚Šã—ã¾ã™ã€‚ ä»Šå›žã¯ã€SQL*Plusã®SPOOLæ©Ÿèƒ½ã‚’ä½¿ã£ã¦CSVå½¢å¼ã®ãƒ†ã‚ã‚¹ãƒˆãƒ•ã‚¡ã‚¤ãƒ«ã«å‡ºåŠ›ã™ã‚‹æ–¹æ³• ã§ã‚„ã£ã¦è¦‹ã¾ã—ã‚‡ã†ã€‚
at_home 2009/12/08
oracle

SQL

æ–¹æ³•
ãƒªãƒ³ã‚¯
ï¼¯ï¼²ï¼¡ï¼£ï¼¬ï¼¥ï¼ã‚ªãƒ©ã‚¯ãƒ«ï¼³ï¼±ï¼¬ãƒªãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹ï¼ˆãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ï¼‰
ã‚¹ãƒãƒ³ã‚µãƒ¼ãƒ‰ã€€ãƒªãƒ³ã‚¯ â—ã€€åˆ—ç•ªå·ã¯ä½¿ç”¨ã—ãªã„ ORDER BYå¥ã«åˆ—ç•ªå·ã§æŒ‡å®šã—ãŸå ´åˆã€ï¼³ï¼±ï¼¬è§£æžæ™‚ã«èªã¿æ›¿ãˆå‡¦ç†ãŒç™ºç”Ÿã™ã‚‹ã®ã§ãƒ‘ãƒ•ã‚©ãƒ¼ãƒžãƒ³ã‚¹ä½Žä¸‹ã«ç¹‹ãŒã‚‹ â—‹ select a.emp_id, a.emp_name from emp a order by salary; Ã— select a.emp_id, a.emp_name from emp a order by 5; â—ã€€DISTINCTå¥ã‚ˆã‚ŠEXISTSå¥ã®ä½¿ç”¨ã‚’æ¤œè¨Žã™ã‚‹ DISTINCTã¯ã€æ¡ä»¶ã«ä¸€è‡´ã™ã‚‹ãƒ¬ã‚³ãƒ¼ãƒ‰ã‚’å–ã‚Šå‡ºã—æš—é»™ã®ã‚½ãƒ¼ãƒˆå‡¦ç†å¾Œã«é‡è¤‡ãƒ¬ã‚³ãƒ¼ãƒ‰ã‚’æŽ’é™¤ã™ã‚‹ã“ã¨ã«å¯¾ã—ã€EXISTSå¥ã¯æ¡ä»¶ã«ä¸€è‡´ã™ã‚‹ãƒ¬ã‚³ãƒ¼ãƒ‰ï¼‘ä»¶ã§ã‚‚ã‚ã‚Œã°ãã“ã§å‡¦ç†ã¯çµ‚äº†ã™ã‚‹ç‚ºã€æš—é»™ã®ã‚½ãƒ¼ãƒˆå‡¦ç†ã‚’ã—ãªã„åˆ†ã€DISTINCTã«æ¯”ã¹ã‚‹ã¨è² è·ãŒå°ã•ããªã‚‹ Ã— select distinct a.emp_id, a.dept_id from emp a,dept b
at_home 2009/12/08
ãƒªãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹

ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°

oracle

sql
ãƒªãƒ³ã‚¯
ï¼¯ï¼²ï¼¡ï¼£ï¼¬ï¼¥ï¼ã‚ªãƒ©ã‚¯ãƒ«ï¼³ï¼±ï¼¬ãƒªãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹ï¼ˆNVLï¼‰
select nvl( null, 'ZZZ' ) from dual çµæžœã¯ã€'ZZZ'ãŒè¿”ã•ã‚Œã¾ã™ã€‚
at_home 2009/12/03
ãƒªãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹

oracle
ãƒªãƒ³ã‚¯
åˆ¶å¾¡æ§‹æ–‡ - 2éƒ¨ Perlè¨€èªžä»•æ§˜ - [SMART]
æ¡ä»¶æ–‡ æ¡ä»¶æ–‡ã¯ã„ãã¤ã‹ã®é¸æŠžè‚¢ã‚’ã‚³ãƒ¼ãƒ‰å†…ã§å®Ÿç¾ã—ãŸã„éš›ã«ä½¿ç”¨ã—ã¾ã™ã€‚ã€Œã‚‚ã—ï½žãªã‚‰ã°ã€ãƒ»ãƒ»ãƒ»ã‚’å®Ÿè¡Œã™ã‚‹ã€ã€ã€Œã‚‚ã—ï½žãªã‚‰ã°ã€ãƒ»ãƒ»ãƒ»ã‚’å®Ÿè¡Œã—ãªã„ã€ã¨ã„ã£ãŸã‚ˆã†ã«æ¡ä»¶ã«ã‚ˆã£ã¦å‡¦ç†ã‚’å®Ÿè¡Œã—ãŸã‚Šã€ã„ãã¤ã‹ã®é¸æŠžè‚¢ã®ä¸ã‹ã‚‰ã²ã¨ã¤ã®å‡¦ç†ã‚’å®Ÿè¡Œã—ãŸã‚Šã¨ã„ã£ãŸã“ã¨ã‚’å®Ÿç¾ã™ã‚‹ã“ã¨ãŒã§ãã¾ã™ã€‚ ifæ–‡ ã€Œã‚‚ã—ï½žãªã‚‰ã°ã€ãƒ»ãƒ»ãƒ»ã‚’å®Ÿè¡Œã™ã‚‹ã€ã¨ã„ã†æ¡ä»¶ã¤ãã®å‘½ä»¤ã‚’å®Ÿç¾ã™ã‚‹ãŸã‚ã«ã€if æ–‡ãŒç”¨æ„ã•ã‚Œã¦ã„ã¾ã™ã€‚ifæ–‡ã¯ã€ã‚«ãƒƒã‚³ã®ä¸ã®æ¡ä»¶å¼ã‚’è©•ä¾¡ã—ã€TRUE ã®ã¨ãã ã‘ if ãƒ–ãƒãƒƒã‚¯ã®å‘½ä»¤æ–‡ã‚’å®Ÿè¡Œã—ã¾ã™ã€‚ if ( æ¡ä»¶å¼ ) { å‡¦ç†.... } å¤‰æ•° $var ã®å€¤ãŒ A ã®æ™‚ã ã‘å‡ºåŠ›ã™ã‚‹ã«ã¯æ¬¡ã®ã‚ˆã†ã«ãªã‚Šã¾ã™ã€‚ if ( $var eq 'A' ) { print "Aã§ã™\n"; } æ¡ä»¶å¼ã¯è¤‡æ•°ã‚ã£ã¦ã‚‚ã‹ã¾ã„ã¾ã›ã‚“ã€‚è¤‡æ•°ã®æ¡ä»¶ã‚’ä¸¦ã¹ãŸã„ã¨ãã¯ã€è«–ç†æ¼”ç®—åã® and ã‚‚ã—ãã¯ or ã‚’ä½¿ã„ã¾ã™ã€‚ # or: $v
at_home 2009/12/01
oracle

perl

DBI

DB

database

sql

ãƒªãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹
ãƒªãƒ³ã‚¯
MAXIMUM ERROR COUNT EXCEEDED sqlloader
at_home 2009/10/28
oracle

SQL
ãƒªãƒ³ã‚¯
ï¼©ï¼´ãƒ¡ãƒ¢å¸³ - Database/Oracle9i/SQL*Loaderã®åˆ¶å¾¡ãƒ•ã‚¡ã‚¤ãƒ«
TopPage SQL*Loaderã®å‘¼ã³å‡ºã— â€ ã‚³ãƒžãƒ³ãƒ‰ãƒ—ãƒãƒ³ãƒ—ãƒˆã‹ã‚‰ctlãƒ•ã‚¡ã‚¤ãƒ«ã‚’æŒ‡å®šã—ã¦å®Ÿè¡Œã™ã‚‹æ–¹æ³•ã§ã™ã€‚ > sqlldr userid=user/passwd control=ctl_file_name ctl_file_nameã«ã¯ãªã‚“ã¨ã‹.ctlãƒ•ã‚¡ã‚¤ãƒ«ã‚’æŒ‡å®šã—ã¾ã™ã€‚ä»¥ä¸‹ã«æ›¸ã„ã¦ã‚ã‚‹ã‚ˆã†ãªå†…å®¹ã®ã‚‚ã®ã‚’è‡ªåˆ†ã§ä½œæˆã—ã¦ãã ã•ã„ã€‚ ã¾ãŸã€ctlãƒ•ã‚¡ã‚¤ãƒ«ã®ä¸ã§ã¯ã€csvãƒ•ã‚¡ã‚¤ãƒ«ã‚’ä½¿ã†ã®ã§ã“ã¡ã‚‰ã‚‚ç”¨æ„ã—ã¦ãã ã•ã„ã€‚csvãƒ•ã‚¡ã‚¤ãƒ«ã«ã¯ãƒãƒ¼ãƒ‰ã™ã‚‹ãƒ‡ãƒ¼ã‚¿ã‚’å…¥ã‚Œã¦ãŠãã¾ã™ã€‚ â†‘ 500ãƒã‚¤ãƒˆä»¥ä¸Šã®ã‚«ãƒ©ãƒ ã‚’å–ã‚Šè¾¼ã‚€å ´åˆ â€ SQL*Loaderã®åˆ¶å¾¡ãƒ•ã‚¡ã‚¤ãƒ«(*.ctl)ã®ä¸€ä¾‹ã§ã™ã€‚ ã“ã‚Œã§æ°—ã‚’ä»˜ã‘ãªã‘ã‚Œã°ãªã‚‰ãªã„ã‚³ãƒˆã‚’ï¼‘ã¤ï¼ err1msg char(500) ã¨æ›¸ã„ã¦ã‚ã‚Šã¾ã™ãŒã€ã‚ã–ã‚ã–char(500)ã‚’ã¤ã‘ãªã‘ã‚Œã°ã„ã‘ãªã„ç†ç”±ãŒã‚ã‚Šã¾ã™ã€‚ ãƒ‡ãƒ•ã‚©ãƒ«ãƒˆã§ã¯ã€æ–‡å—åˆ—åž‹ã®ãƒ•ã‚£ãƒ¼ãƒ«ãƒ‰ã«ã¯
at_home 2009/10/28
SQL

oracle

è§£æ±º

sqlldr
ãƒªãƒ³ã‚¯
Oracle
æ—¥æœ¬ã®ã‚ªãƒ©ã‚¯ãƒ«ãƒ»ã‚³ãƒŸãƒ¥ãƒ‹ãƒ†ã‚£ãŒä¸€å ‚ã«ä¼šã™ã‚‹ãƒ—ãƒ¬ãƒŸã‚¢ãƒ»ã‚¤ãƒ™ãƒ³ãƒˆã«ãœã²ã”å‚åŠ ãã ã•ã„ã€‚æ–°ã—ã„ã‚¹ã‚ãƒ«ã‚’èº«ã«ä»˜ã‘ã€æ¥ç•Œã‚¨ã‚ã‚¹ãƒ‘ãƒ¼ãƒˆã¨äº¤æµã—ã€è¤‡é›‘ãªãƒ“ã‚¸ãƒã‚¹èª²é¡Œã‚’è§£æ±ºã™ã‚‹ãŸã‚ã®ã‚½ãƒªãƒ¥ãƒ¼ã‚·ãƒ§ãƒ³ã‚’ç™ºè¦‹ã—ã¾ã—ã‚‡ã†ã€‚
at_home 2009/10/28
SQL

oracle

è§£æ±º
ãƒªãƒ³ã‚¯
ã‚µãƒ¼ãƒ“ã‚¹çµ‚äº†ã®ãŠçŸ¥ã‚‰ã›
ã‚µãƒ¼ãƒ“ã‚¹çµ‚äº†ã®ãŠçŸ¥ã‚‰ã› ã„ã¤ã‚‚Yahoo! JAPANã®ã‚µãƒ¼ãƒ“ã‚¹ã‚’ã”åˆ©ç”¨ã„ãŸã ãèª ã«ã‚ã‚ŠãŒã¨ã†ã”ã–ã„ã¾ã™ã€‚ ãŠå®¢æ§˜ãŒã‚¢ã‚¯ã‚»ã‚¹ã•ã‚ŒãŸã‚µãƒ¼ãƒ“ã‚¹ã¯æœ¬æ—¥ã¾ã§ã«ã‚µãƒ¼ãƒ“ã‚¹ã‚’çµ‚äº†ã„ãŸã—ã¾ã—ãŸã€‚ ä»Šå¾Œã¨ã‚‚Yahoo! JAPANã®ã‚µãƒ¼ãƒ“ã‚¹ã‚’ã”æ„›é¡§ãã ã•ã„ã¾ã™ã‚ˆã†ã€ã‚ˆã‚ã—ããŠé¡˜ã„ã„ãŸã—ã¾ã™ã€‚
at_home 2009/10/26
oracle

SQL

æ–¹æ³•
ãƒªãƒ³ã‚¯
ï¼¯ï¼²ï¼¡ï¼£ï¼¬ï¼¥ï¼ã‚ªãƒ©ã‚¯ãƒ«ï¼³ï¼±ï¼¬ãƒªãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹ï¼ˆTO_DATE/TO_CHAR/TO_TIMESTAMPï¼‰
ã‚¹ãƒãƒ³ã‚µãƒ¼ãƒ‰ã€€ãƒªãƒ³ã‚¯ æ—¥ä»˜ãƒ‡ãƒ¼ã‚¿ï¼æ–‡å—åˆ—ãƒ‡ãƒ¼ã‚¿ã®å¤‰æ›ã«ã¯ã€TO_DATEé–¢æ•°ã€TO_CHARé–¢æ•°ã€TO_TIMESTAMPã‚’ä½¿ç”¨ã™ã‚‹
at_home 2009/10/22
oracle

SQL

ãƒªãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹
ãƒªãƒ³ã‚¯
1 2 æ¬¡ã®ãƒšãƒ¼ã‚¸