2008-05-29: Merge

本文详细介绍了 Oracle 数据库中 SQL 的 MERGE 语句用法,包括如何使用该语句进行数据更新和插入操作,以及如何通过条件判断决定更新或插入目标表。此外还提供了实际应用案例。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

Skip Headers

Oracle® Database SQL Reference
10g Release 1 (10.1)

Part Number B10759-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

MERGE

Purpose

Use the MERGE statement to select rows from one or more sources for update or insertion into one or more tables. You can specify conditions to determine whether to update or insert into the target tables.

This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.

MERGE is a deterministic statement. That is, you cannot update the same row of the target table multiple times in the same MERGE statement.

Prerequisites

You must have the INSERT and UPDATE object privileges on the target table and the SELECT object privilege on the source table. To specify the DELETE clause of the merge_update_clause, you must also have the DELETE object privilege on the target table.

Syntax


merge::=
Description of merge.gif follows
Description of the illustration merge.gif


merge_update_clause ::=
Description of merge_update_clause.gif follows
Description of the illustration merge_update_clause.gif


merge_insert_clause ::=
Description of merge_insert_clause.gif follows
Description of the illustration merge_insert_clause.gif


where_clause::=
Description of where_clause.gif follows
Description of the illustration where_clause.gif

Semantics


INTO Clause

Use the INTO clause to specify the target table you are updating or inserting into.


USING Clause

Use the USING clause to specify the source of the data to be updated or inserted. The source can be a table, view, or the result of a subquery.


ON Clause

Use the ON clause to specify the condition upon which the MERGE operation either updates or inserts. For each row in the target table for which the search condition is true, Oracle Database updates the row with corresponding data from the source table. If the condition is not true for any rows, then the database inserts into the target table based on the corresponding source table row.


merge_update_clause

The merge_update_clause specifies the new column values of the target table. Oracle performs this update if the condition of the ON clause is true. If the update clause is executed, then all update triggers defined on the target table are activated.

Specify the where_clause if you want the database to execute the update operation only if the specified condition is true. The condition can refer to either the data source or the target table. If the condition is not true, then the database skips the update operation when merging the row into the table.

Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. That is, the DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted. Any delete triggers defined on the target table will be activated for each row deletion.

You can specify this clause by itself or with the merge_insert_clause. If you specify both, then they can be in either order.

Restrictions on the merge_update_clause
  • You cannot update a column that is referenced in the ON condition clause.

  • You cannot specify DEFAULT when updating a view.


merge_insert_clause

The merge_insert_clause specifies values to insert into the column of the target table if the condition of the ON clause is false. If the insert clause is executed, then all insert triggers defined on the target table are activated. If you omit the column list after the INSERT keyword, then the number of columns in the target table must match the number of values in the VALUES clause.

To insert all of the source rows into the table, you can use a constant filter predicate in the ON clause condition. An example of a constant filter predicate is ON (0=1). Oracle Database recognizes such a predicate and makes an unconditional insert of all source rows into the table. This approach is different from omitting the merge_update_clause. In that case, the database still must perform a join. With constant filter predicate, no join is performed.

Specify the where_clause if you want Oracle Database to execute the insert operation only if the specified condition is true. The condition can refer only to the data source table. Oracle Database skips the insert operation for all rows for which the condition is not true.

You can specify this clause by itself or with the merge_update_clause. If you specify both, then they can be in either order.


Restriction on Merging into a View

You cannot specify DEFAULT when updating a view.

Examples


Merging into a Table: Example

The following example creates a bonuses table in the sample schema oe with a default bonus of 100. It then inserts into the bonuses table all employees who made sales, based on the sales_rep_id column of the oe.orders table. Finally, the human resources manager decides that employees with a salary of $8000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who already made sales get an increase in their bonus equal to 1% of their salary. The MERGE statement implements these changes in one step:

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses(employee_id)
   (SELECT e.employee_id FROM employees e, orders o
   WHERE e.employee_id = o.sales_rep_id
   GROUP BY e.employee_id); 

SELECT * FROM bonuses;

EMPLOYEE_ID      BONUS
----------- ----------
        153        100
        154        100
        155        100
        156        100
        158        100
        159        100
        160        100
        161        100
        163        100

MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
     VALUES (S.employee_id, S.salary*0.1)
     WHERE (S.salary <= 8000);

EMPLOYEE_ID      BONUS
----------- ----------
        153        180
        154        175
        155        170
        159        180
        160        175
        161        170
        179        620
        173        610
        165        680
        166        640
        164        720
        172        730
        167        620
        171        740
<think>好的,用户让我解释一段Git操作的日志信息。先看一下用户提供的日志内容。看起来像是CI/CD流水线在执行Git操作时的输出,比如Jenkins或者GitLab Runner之类的工具。用户之前问过关于脚本条件判断的问题,这次可能是在构建或部署过程中遇到问题,需要理解这些日志的含义。 首先,我需要逐条分析日志里的每一行,解释每个步骤的作用。比如,使用凭证、检查是否在Git工作树内、获取远程仓库更新、检出特定提交等。要注意用户可能不太熟悉Git内部命令,所以需要用通俗的语言解释这些步骤的目的。 用户可能关心的是整个流程是否正常,或者某个步骤是否出了问题。比如,日志里提到第一次构建,跳过了变更日志,可能需要说明这是正常行为,因为没有之前的构建记录。另外,凭证的使用和SSH配置可能涉及安全性,需要指出这些信息的作用和潜在注意事项。 还要注意时间戳是未来的日期(2025年),这可能是个笔误或者测试数据,但解释时不用特别指出,按照正常情况处理即可。用户可能想知道每个步骤在CI/CD流程中的作用,以及最终检出哪个提交,合并请求的信息等。 最后,总结整个流程,帮助用户理解这是在拉取代码库并准备构建的过程,可能涉及合并请求的处理。如果有潜在问题,比如凭证权限、网络连接等,也需要在注意事项里提到,帮助用户排查可能出现的问题。</think>以下是对这段日志的逐行解析和关键点说明: --- ### **一、整体流程** 这是典型的 CI/CD 系统(如 Jenkins/GitLab CI 等)在构建任务中**拉取 Git 仓库代码**的操作日志,核心动作包含 **凭证使用 → 仓库检查 → 远程同步 → 代码检出**。 --- ### **二、逐行解析** #### **1. 凭证使用** ```log [2025-03-11 10:28:57] using credential 3b1be6b0-696f-4834-8bb9-1d31ed27d309 ``` - **关键信息** - 使用 ID 为 `3b1be6b0...` 的预存凭证(通常为 SSH 密钥或账号令牌) - 作用:安全访问 Git 仓库(如私有仓库需鉴权) #### **2. 环境检查** ```log [2025-03-11 10:28:57] > git rev-parse --is-inside-work-tree # timeout=10 ``` - **命令作用** - 验证当前目录是否为有效的 Git 工作目录 - `timeout=10` 表示超时时间设置为 10 秒 #### **3. 远程仓库配置** ```log [2025-03-11 10:28:57] Fetching changes from the remote Git repository [2025-03-11 10:28:57] > git config remote.origin.url git@e.aiops.jahwaec.com:devops/emall/jahwaec.git ``` - **关键信息** - 显式设置远程仓库地址为 `git@e.aiops.jahwaec.com:devops/emall/jahwaec.git` - 该地址为 SSH 协议格式(说明使用 SSH 密钥认证) #### **4. 同步远程更新** ```log [2025-03-11 10:28:57] Fetching upstream changes from git@e.aiops.jahwaec.com:devops/emall/jahwaec.git [2025-03-11 10:28:58] using GIT_SSH to set credentials [2025-03-11 10:28:58] > git fetch --tags --force --progress -- git@e.aiops.jahwaec.com:devops/emall/jahwaec.git +refs/heads/*:refs/remotes/origin/* +refs/merge/*:refs/remotes/origin/merge/* ``` - **命令分解** - `fetch --tags`:拉取所有标签 - `--force`:强制覆盖本地引用 - `--progress`:显示传输进度 - `+refs/heads/*:refs/remotes/origin/*`:同步所有分支 - `+refs/merge/*`:同步合并请求相关引用(常见于 GitLab) #### **5. 提交校验** ```log [2025-03-11 10:28:58] > git rev-parse 20ed6d6336dbc2a4f710cb5b208eaa7307bc1864^{commit} # timeout=10 ``` - **作用** - 验证哈希值 `20ed6d...` 是否为合法 Git 提交对象 - `^{commit}` 语法确保该哈希指向提交(而非标签或树对象) #### **6. 代码检出** ```log [2025-03-11 10:28:58] Checking out Revision 20ed6d6336dbc2a4f710cb5b208eaa7307bc1864 (detached) [2025-03-11 10:28:58] > git config core.sparsecheckout # timeout=10 [2025-03-11 10:28:58] > git checkout -f 20ed6d6336dbc2a4f710cb5b208eaa7307bc1864 ``` - **关键行为** - 以 **分离头指针(detached HEAD)** 模式检出特定提交 - `-f` 参数强制覆盖本地修改 - `core.sparsecheckout` 检查是否启用稀疏检出(此处未配置) #### **7. 提交信息** ```log [2025-03-11 10:28:59] Commit message: "Accept Merge Request #4355: (dev20250120-限时折扣 -> master) Merge Request: 限时折扣" ``` - **关键内容** - 本次构建对应合并请求 **#4355** - 合并方向:从分支 `dev20250120-限时折扣` 到 `master` - 主题为 **限时折扣**(功能描述) #### **8. 变更日志说明** ```log [2025-03-11 10:28:59] First time build. Skipping changelog. ``` - **含义** - 首次构建此提交,无法生成与历史版本的差异报告(changelog) - 后续构建时会基于此提交计算变更 --- ### **三、核心流程总结** ```mermaid graph TD A[使用凭证] --> B[检查Git环境] B --> C[配置远程仓库] C --> D[强制拉取最新代码] D --> E[校验目标提交] E --> F[检出特定提交] F --> G[显示合并信息] ``` --- ### **四、技术注意事项** | 项目 | 说明 | |------|------| | **分离头指针** | 检出特定提交而非分支,需注意后续操作可能导致引用丢失 | | **强制拉取(--force)** | 可能覆盖本地配置,需确认流水线设计是否符合预期 | | **凭证安全** | 凭证 ID `3b1be6b0...` 需在 CI 系统中加密存储 | | **合并请求引用** | `+refs/merge/*` 说明系统集成了 Merge Request 追踪功能 | --- ### **五、典型应用场景** 1. **合并请求验证** - 当合并请求 #4355 被创建/更新时触发构建 - 确保合并到 `master` 的代码可通过自动化测试 2. **指定版本部署** - 明确部署到提交 `20ed6d...` 对应的代码版本 - 适用于回滚或定点验证 3. **首次构建初始化** - 新功能分支首次集成时的环境准备 --- 如果需要进一步分析特定环节(如 SSH 连接问题、提交哈希验证失败等),可提供更多上下文。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值