 |
|
Oracle Tips by Burleson |
Oracle 10g An Example Merge
The following example is taken from the Oracle
documentation for Oracle Database 10g. The 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
SEE
CODE DEPOT
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
SEE
CODE DEPOT
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)
EMPLOYEE_ID BONUS
Get the complete Oracle10g story:
To get the code instantly, click here:
Need an Oracle Mentor?
BEI is now offering personal mentors for Oracle DBAs where you can have an
Oracle expert right at your fingertips, anytime day or night. We work with
hundreds of Oracle databases every year, so we know exactly how to quickly
assist you with any Oracle question.
Why risk an unplanned outage? You can now get telephone access to Don
Burleson or any of his Oracle Certified DBAs with more than 20 years of
full-time IT experience. Click here for details:
http://www.dba-oracle.com/service_oracle_backup.htm
|
