{"id":4845,"date":"2023-04-18T13:23:28","date_gmt":"2023-04-18T04:23:28","guid":{"rendered":"http:\/\/oracledba.zapto.org\/wordpress\/?p=4845"},"modified":"2023-04-18T13:23:28","modified_gmt":"2023-04-18T04:23:28","slug":"oracle-merge-%eb%ac%b8","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=4845","title":{"rendered":"Oracle Merge \ubb38"},"content":{"rendered":"

\ud14c\uc774\ube14\uc5d0 \ub370\uc774\ud130\uac00 \uc774\ubbf8 \uc874\uc7ac\ud558\uba74 \uc5c5\ub370\uc774\ud2b8 \ud558\uace0, \uc874\uc7ac\ud558\uc9c0 \uc54a\uc73c\uba74 \uc785\ub825\uc744 \ud574\uc57c \ud558\ub294 \uacbd\uc6b0\uac00 \uc885\uc885 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

\n

\uc624\ub77c\ud074\uc5d0\uc11c \uc774\ub7f0 \uc791\uc5c5\uc744 \ud55c\ubc88\uc5d0 \ud560 \uc218 \uc788\ub294 \ucffc\ub9ac\uac00 MERGE INTO \ubb38 \uc785\ub2c8\ub2e4.<\/p>\n

\n

\ud604\uc2e4\uc801\uc778 \uc608\ub294 \uc544\ub2c8\uc9c0\ub9cc \uac04\ub2e8\ud558\uac8c \uc131\uc801 \ud14c\uc774\ube14\uc744 \uc0dd\uac01\ud574 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4. \uacfc\uc815\ucf54\ub4dc\uacfc \ud559\uc0dd\ucf54\ub4dc\uac00 \uc8fc\ud0a4\uc774\uace0, \uc131\uc801 \ud544\ub4dc\ub97c \uac00\uc9c0\uace0 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

\n\n\n\n
CREATE TABLE TB_SCORE (
\nCOURSE_ID\u00a0 VARCHAR2(10) NOT NULL,
\nSTUDENT_ID VARCHAR2(10) NOT NULL,
\nSCORE\u00a0 \u00a0 \u00a0 NUMBER(5, 2) NULL,
\nCONSTRAINT PK_SCORE PRIMARY KEY (COURSE_ID, STUDENT_ID)
\n);<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n

1. \ub3d9\uc77c\ud55c \ud14c\uc774\ube14 \uad6c\uc870\ub97c \uac00\uc9c0\uace0 \uc788\ub294 TMP_SCORE \ud14c\uc774\ube14\ub85c\ubd80\ud130 \ub370\uc774\ud130\ub97c \uc62e\uae30\ub294 \uc608\uc785\ub2c8\ub2e4.<\/b><\/p>\n

TMP_SCORE \ud14c\uc774\ube14\uc5d0 \uc788\ub294\uac83\uacfc \ub3d9\uc77c\ud55c \uacfc\uc815\ucf54\ub4dc\uc640 \ud559\uc0dd\ucf54\ub4dc\ub97c \uac00\uc9c4 \ub370\uc774\ud130\uac00 TB_SCORE\uc5d0 \uc788\uc73c\uba74 SCORE \ub97c \uc5c5\ub370\uc774\ud2b8\ud558\uace0, \uc5c6\uc73c\uba74 \uc785\ub825\ud569\ub2c8\ub2e4.<\/p>\n

\n\n\n\n
MERGE INTO<\/b>\u00a0TB_SCORE S
\nUSING<\/b>\u00a0TMP_SCORE T
\nON<\/b>\u00a0(S.COURSE_ID = T.COURSE_ID AND S.STUDENT_ID = T.STUDENT_ID)
\nWHEN MATCHED THEN<\/b>
\nUPDATE SET S.SCORE = T.SCORE
\nWHEN NOT MATCHED THEN<\/b>
\nINSERT (S.COURSE_ID, S.STUDENT_ID, S.SCORE)
\nVALUES (T.COURSE_ID, T.STUDENT_ID, T.SCORE)
\n;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n

USING \uc808\uc5d0\ub294 \ud14c\uc774\ube14\uc678\uc5d0 \ubdf0, \uc778\ub77c\uc778 \ubdf0 \ub4f1\uc774 \uc62c \uc218\ub3c4 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

\n

\n

2. \ub2e4\ub978 \ud14c\uc774\ube14\uc5d0\uc11c \ub370\uc774\ud130\ub97c \ube44\uad50\ud558\uc5ec \uac00\uc838\uc624\ub294 \uac83\uc774 \uc544\ub2c8\ub77c, \uc9c1\uc811 \uac12\uc744 \ub123\uace0\uc790 \ud55c\ub2e4\uba74\u00a0DUAL\uc744 \uc0ac\uc6a9\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/b><\/p>\n

\n\n\n\n
MERGE INTO<\/b>\u00a0TB_SCORE S
\nUSING<\/b>\u00a0DUAL<\/b>
\nON<\/b>\u00a0(S.COURSE_ID = ‘C1’ AND S.STUDENT_ID = ‘S1’)
\nWHEN MATCHED THEN<\/b>
\nUPDATE SET S.SCORE = 20
\nWHEN NOT MATCHED THEN<\/b>
\nINSERT (S.COURSE_ID, S.STUDENT_ID, S.SCORE)
\nVALUES (‘C1’, ‘S1’, 20)
\n;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n

\u00a0<\/b><\/p>\n

3. \uc5c5\ub370\uc774\ud2b8\ub098 \uc785\ub825 \ud558\ub098\ub9cc \ud560 \uc218\ub3c4 \uc788\uc2b5\ub2c8\ub2e4.\u00a0<\/b><\/p>\n

\uc874\uc7ac\ud558\uba74 \uc544\ubb34\uac83\ub3c4 \ud558\uc9c0 \uc54a\uace0, \uc5c6\uc73c\uba74 \uc785\ub825\ud558\ub824\uba74 \ub2e4\uc74c\uacfc \uac19\uc774 \ud569\ub2c8\ub2e4.<\/p>\n

\n\n\n\n
MERGE INTO TB_SCORE S
\nUSING DUAL
\nON (S.COURSE_ID = ‘C1’ AND S.STUDENT_ID = ‘S1’)
\nWHEN NOT MATCHED THEN
\nINSERT (S.COURSE_ID, S.STUDENT_ID, S.SCORE)
\nVALUES (‘C1’, ‘S1’, 20)
\n;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n

ON \ub4a4\uc5d0 \ub098\uc624\ub294 \ub9e4\uce6d \uc870\uac74\uc740 \uc8fc\ud0a4\ub97c \uc0ac\uc6a9\ud574\uc57c \ud569\ub2c8\ub2e4. \uadf8\ub807\uc9c0 \uc54a\uc73c\uba74 \uc785\ub825\uc2dc\uc5d0 \uc911\ubcf5\uc774 \ubc1c\uc0dd\ud558\uc5ec \uc5d0\ub7ec\uac00 \ub0a0 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n","protected":false},"excerpt":{"rendered":"

\ud14c\uc774\ube14\uc5d0 \ub370\uc774\ud130\uac00 \uc774\ubbf8 \uc874\uc7ac\ud558\uba74 \uc5c5\ub370\uc774\ud2b8 \ud558\uace0, \uc874\uc7ac\ud558\uc9c0 \uc54a\uc73c\uba74 \uc785\ub825\uc744 \ud574\uc57c \ud558\ub294 \uacbd\uc6b0\uac00 \uc885\uc885 \uc788\uc2b5\ub2c8\ub2e4. \uc624\ub77c\ud074\uc5d0\uc11c \uc774\ub7f0 \uc791\uc5c5\uc744 \ud55c\ubc88\uc5d0 \ud560 \uc218 \uc788\ub294 \ucffc\ub9ac\uac00 MERGE INTO \ubb38 \uc785\ub2c8\ub2e4. \ud604\uc2e4\uc801\uc778 \uc608\ub294 \uc544\ub2c8\uc9c0\ub9cc \uac04\ub2e8\ud558\uac8c \uc131\uc801 \ud14c\uc774\ube14\uc744 \uc0dd\uac01\ud574 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4. \uacfc\uc815\ucf54\ub4dc\uacfc \ud559\uc0dd\ucf54\ub4dc\uac00 \uc8fc\ud0a4\uc774\uace0, \uc131\uc801 \ud544\ub4dc\ub97c \uac00\uc9c0\uace0 \uc788\uc2b5\ub2c8\ub2e4. CREATE TABLE TB_SCORE ( COURSE_ID\u00a0 VARCHAR2(10) NOT NULL, STUDENT_ID VARCHAR2(10) NOT NULL, SCORE\u00a0 \u00a0 […]<\/p>\n","protected":false},"author":1,"featured_media":3627,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[9],"tags":[1443,1445,1444,1446],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/03\/sql1.jpg?fit=489%2C345","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4845"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4845"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4845\/revisions"}],"predecessor-version":[{"id":4846,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4845\/revisions\/4846"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/3627"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4845"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4845"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4845"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}