10.1ãDMLæã«ããããŒã¿ã®è¿œå ã»æŽæ°ã»åé€
確èª
DMLãšã¯ïŒData Manipulation Language:ããŒã¿æäœèšèªã®ããšã
è¡šãªã©ã®ããŒã¿ãçŽæ¥æäœããã
Â
INSERTæã«ããããŒã¿ã®è¿œå
è¡šã«ããŒã¿ãè¿œå ãããšãã«äœ¿çšããã
åºæ¬æ§æ
INSERT INTOãè¡šåã[ ( åå1ã[, åå2âŠ] ) ]
VALUESãå€1ã[,å€2,âŠ] ) ;
è¡šåïŒããŒã¿ãè¿œå ããè¡šã®åå
ååïŒããŒã¿ãè¿œå ããè¡šã«å®çŸ©ãããŠããååãçç¥åã
å€ãïŒè¿œå ããããŒã¿ã®ååã«èšå®ããå€ãååã®ãªã¹ãã®é çªã§ååã«èšå®ããå€ãæå®
ã»ååãšå€ã¯1察1ã®é¢ä¿ã«ãªãã
ã»è¿œå ããå€ã¯åã«å®çŸ©ãããŠããããŒã¿åãšäžèŽããªããã°ãªããªãã
ã»æåå€ãæ¥ä»å€ãè¿œå ããå Žåã¯å€ããâãïŒäžéåŒçšç¬ŠïŒã§æ¬ããÂ
ååãçç¥ããINSERTæ
ååãçç¥ããå Žåã¯ïŒVALUESå¥ã«ã¯è¡šã«å®çŸ©ãããŠããåãšåæ°ã®å€ãïŒè¡šã®åæ§æãšåãé çªã§æå®ããã
ååãæ瀺çã«æå®ããINSERTæ
ååãæå®ããå Žåã¯VALUESå¥ã«ã¯ååã®ãªã¹ããšåæ°ã®å€ãïŒåãé çªã§æå®ãããâè¡šã«å®çŸ©ãããŠããé çªã§ãªããŠãOK
è¡šã«å®çŸ©ãããŠããªãååãæå®ãããšãšã©ãŒã«ãªãã
Â
NULLå€ãå«ãè¡ã®è¿œå
æé»çææ³
ååã®ãªã¹ããã察象ã®ååãçç¥ãããçç¥ãããåã«ã¯NULLå€ãèšå®ãããã
æ瀺çææ³
1ïŒNULLããŒã¯ãŒããæå®ãã
2ïŒæåå€ãæ¥ä»å€ãæ ŒçŽããåã«å¯ŸããŠãââãïŒç©ºã®æååïŒãæå®ãããÂ
NOT NULLãå®çŸ©ãããŠããåã«å¯ŸããNULLå€ã®æå®
åã«NOT NULLãå®çŸ©ãããŠããå ŽåïŒNULLå€ãèšå®ããããšã¯ã§ããªããNULLå€ãèšå®ããããšãããšãšã©ãŒã«ãªãã
NOT NULLã«ãªã£ãŠãããã©ããã¯ãdesc è¡šåãã§ç¢ºèªã§ããã
é¢æ°ã䜿çšããããŒã¿ã®è¿œå
INSERTæã®VALUESå¥ã«ã¯é¢æ°ã䜿çšã§ããã
SYSDATEé¢æ°ã䜿çšããŠINSERTæãå®è¡ããæ¥æãæå®ãããªã©ã
å¯ååãã䜿çšããããŒã¿ã®è¿œå
å¯ååãã䜿çšãããšå€ãæ¢åã®è¡šããååŸããŠè€æ°è¡ã®ããŒã¿ãäžåºŠã«è¿œå ããããšãã§ããã
åºæ¬æ§æ
INSERT INTO è¡šå1ã[ ( åå[ ,åå⊠] )
SELECTãååã[ , ååâŠ]
FROMãè¡šå2
[WHERE æ¡ä»¶];
 ã»å¯ååããå²ãïŒïŒã¯å¿ é ã§ã¯ãªãã
ã»VALUESå¥ã¯æå®ããªãã
ã»INSERTå¥ã«æå®ããååãçç¥ããå Žåã¯ïŒå¯ååãã®SELECTå¥ã«ã¯ïŒè¡šã«å®çŸ©ãããŠããåãšåæ°ã®å€ãïŒè¡šã®åæ§æãšåãé çªã§æå®ããã
ã»INSERTå¥ã«ååãæå®ããå Žåã¯ïŒå¯ååãã®SELECTå¥ã«ã¯ååã®ãªã¹ããšåæ°ã®å€ãåãé çªã§æå®ããã
UPDATEæã«ããããŒã¿ã®æŽæ°
ãã§ã«è¡šã«æ ŒçŽãããŠããããŒã¿ãæŽæ°ã§ããã
åºæ¬æ§æ
UPDATE è¡šå
SETãååïŒå€ [ . ååïŒå€âŠ]
[WHEREãæ¡ä»¶];
è¡šåïŒããŒã¿ãæŽæ°ããè¡šã®åå
ååïŒããŒã¿ãæŽæ°ããåã®ååãå€ãšã»ããã§æå®ãã
å€ãïŒæŽæ°åŸã®ããŒã¿ã®å€ãååãšã»ããã§æå®ãã
æ¡ä»¶ïŒæŽæ°ããè¡ãç¹å®ããããã®æ¡ä»¶ïŒæ¡ä»¶ãæºããè¡ãæŽæ°ããããïŒWHEREå¥ã¯çç¥å¯èœã ãïŒçç¥ãããšãã¹ãŠã®è¡ãæŽæ°ãããã®ã§æ³šæã
Â
å¯ååãã䜿çšããããŒã¿ã®æŽæ°
SETå¥ã«å¯ååãã䜿çšããã°ïŒå¯ååãã§åãåºããããŒã¿ã䜿çšããŠè¡šãæŽæ°ã§ããã
1ã€ã®UPDATEæã§è€æ°ã®å¯ååãã䜿çšããããšãã§ããã
WHEREå¥ïŒSETå¥ïŒããã³è¡šåã®ä»£ããã«å¯ååããæå®ã§ããã
è€æ°åå¯ååãã䜿çšããŠïŒåæã«åäžè¡ã®è€æ°åãæŽæ°ããããšãã§ããã
Â
DELETEæã«ããããŒã¿ã®åé€
åºæ¬æ§æ
DELETEã[FROM]ãè¡šå
[WHEREãæ¡ä»¶];
è¡šåïŒããŒã¿ãåé€ããè¡šã®åå
æ¡ä»¶ïŒåé€ããè¡ãæå®ããããã®æ¡ä»¶ãWHEREå¥ãçç¥ãããšãã¹ãŠã®è¡ãåé€ãããã®ã§æ³šæã
ã»ããŒã¿ã¯è¡åäœã§åé€ãããã
ã»æ¡ä»¶ãæºããè¡ãè€æ°ããå Žåã¯ïŒãã®ãã¹ãŠã®è¡ãåæã«åé€ãããã
ã»WHEREå¥ãçç¥ãããšè¡šå ã®ãã¹ãŠã®è¡ãåé€ãããã
ã»DELETEæã®WHEREå¥ã«å¯ååãã䜿çšããããšãã§ããã
Â
10.2ããã©ã³ã¶ã¯ã·ã§ã³ã®å¶åŸ¡
ãã©ã³ã¶ã¯ã·ã§ã³ãšã¯ïŒ1ã€ãŸãã¯è€æ°ã®æäœã«ãã£ãŠå®çµããäžé£ã®ããŒã¿æäœããŸãšãããè«ççãªåŠçåäœãã®ããšã
OracleãµãŒãã¯ãã©ã³ã¶ã¯ã·ã§ã³ãšããŠãŸãšããããäžé£ã®ããŒã¿æäœãå¿ ã以äžã®ã©ãããšããŠåŠçããã
ã»ãã¹ãŠã®æäœã確å®ããïŒã³ãããïŒ
ã»ãã¹ãŠã®æäœãåãæ¶ãïŒããŒã«ããã¯ïŒ
ãã®ããã«åŠçããããšã§ïŒããŒã¿ããŒã¹å šäœã®ããŒã¿ã®æŽåæ§ãä¿æããã
OracleãµãŒãã§ã¯ããŒã¿ã®æŽåæ§ã¯ãã©ã³ã¶ã¯ã·ã§ã³åäœã§ä¿èšŒãããã
Â
ãã©ã³ã¶ã¯ã·ã§ã³ã®æ§æèŠçŽ
DMLïŒããŒã¿æäœèšèªïŒ
SELECTæïŒINSERTæïŒUPDATEæïŒDELETEæãªã©ããã©ã³ã¶ã¯ã·ã§ã³ã¯ïŒè«ççãªæå°äœæ¥åäœãšããŠæ±ããã1ã€ãŸãã¯è€æ°ã®DMLæã§æ§æãããã
DDLïŒããŒã¿å®çŸ©èšèªïŒ
CREATEæïŒALTERæïŒDROPæãªã©ããã©ã³ã¶ã¯ã·ã§ã³ã¯1ã€ã®DDLæã®ã¿ã§æ§æãããã
DCLïŒããŒã¿å¶åŸ¡èšèªïŒ
GRANTæïŒREVOKEæãªã©ããã©ã³ã¶ã¯ã·ã§ã³ã¯1ã€ã®DCLæã®ã¿ã§æ§æãããã
 ãã©ã³ã¶ã¯ã·ã§ã³ã®éå§ãšçµäº
DMLãå«ããã©ã³ã¶ã¯ã·ã§ã³ã¯ïŒOracleãµãŒãã«æ¥ç¶åŸãŸãã¯çŽåã®ãã©ã³ã¶ã¯ã·ã§ã³ã®çµäºåŸïŒãæåã®DMLæãå®è¡ããããšããã«èªåçã«éå§ããïŒä»¥äžã®ããããã®ã€ãã³ããçºçããéã«çµäºããã
ã»COMMITæãŸãã¯ROLLBACKæã®å®è¡
ã»DDLæã®å®è¡ïŒèªåã³ãããïŒ
ã»DCLæã®å®è¡ïŒèªåã³ãããïŒ
ã»ãŠãŒã¶ã«ããSQL DeveloperãSQL*Plusã®çµäº
ã»ãã·ã³é害ãã·ã¹ãã ã¯ã©ãã·ã¥ã®çºç
æ瀺çãªãã©ã³ã¶ã¯ã·ã§ã³å¶åŸ¡
ãã©ã³ã¶ã¯ã·ã§ã³å¶åŸ¡æ
COMMIT
äžé£ã®åŠçïŒããŒã¿ã®è¿œå ïŒæŽæ°ïŒåé€ïŒãå šãŠç¢ºå®ãïŒãã©ã³ã¶ã¯ã·ã§ã³ãçµäºããã
SAVEPOINTãã»ãŒããã€ã³ãå
ã»ãŒããã€ã³ããäœæããããã©ã³ã¶ã¯ã·ã§ã³ã¯ç¶ç¶ããã
ROLLBACK
äžé£ã®åŠçïŒããŒã¿ã®è¿œå ïŒæŽæ°ïŒåé€ïŒããã¹ãŠåãæ¶ãïŒãã©ã³ã¶ã¯ã·ã§ã³ãçµäºããã
ROLLBACK TO [SAVEPOINT]ãã»ãŒããã€ã³ãå
æå®ããã»ãŒããã€ã³ããŸã§ã®åŠçãåãæ¶ãïŒããŒã«ããã¯ããïŒãã©ã³ã¶ã¯ã·ã§ã³ã¯ç¶ç¶ããããªãïŒæå®ãããã»ãŒããã€ã³ããããåŸã«äœæãããã»ãŒããã€ã³ãã¯åé€ããããÂ
Â
Â
Â
æé»çãªãã©ã³ã¶ã¯ã·ã§ã³åŠç
 ãã©ã³ã¶ã¯ã·ã§ã³å¶åŸ¡æãå®è¡ããŠããªãå Žåã§ããã©ã³ã¶ã¯ã·ã§ã³åŠçãå®è¡ãããå ŽåãããïŒããããæé»çãªãã©ã³ã¶ã¯ã·ã§ã³åŠçããšããã
âèªåã³ããã
ã»DDLæã®å®è¡æ
ã»DCLæã®å®è¡æ
ã»ãŠãŒã¶ã«ããSQL DeveloperãSQL*Plusã®æ£åžžçµäº
âèªåããŒã«ããã¯
 ã»ãŠãŒã¶ã«ããSQL DeveloperãSQL*Plusã®ç°åžžçµäº
 ã»ã·ã¹ãã é害çºçæïŒOracleã€ã³ã¹ã¿ã³ã¹ã®ç°åžžåæ¢æïŒ
Â
ïŒïŒïŒSQL*Plusã®AUTOCOMMITæ©èœ
 SET AUTOCOMMIT { ON | OFF }
AUTOCOMMITæ©èœãæå¹ã«ãããšïŒåDMLæã®å®è¡æã«èªåçã«ã³ãããåŠçãå®è¡ãããã
Â
TRUNECATEæ
è¡šã«æ ŒçŽãããŠããå šãŠã®ããŒã¿ãåé€ããã
TRUNCATEãTABLEãè¡šå;
ã»åé€ããããŒã¿ã¯æå®ã§ããªã
ã»DDLæãªã®ã§å®è¡æã«ã¯èªåã³ããããå®è¡ãããã
ã»èªåã³ããããããã®ã§ROLLBACKã§ããªãã
ã»ããŒã«ããã¯çšã®ããŒã¿ãçæããå¿ èŠããªãããïŒDELETEæããçæéã§ããŒã¿ãåé€ã§ããã
Â
Â