在表中重复插入数据时作如下处理:若插入的数据已经存在则更新,否则插入,怎么写?
解决方案 »
- 请教如何对oracel进行学习
- sqlplus乱码怎么解决?
- SELECT * from BOOK.LIST 与 select * from list 有什么区别啊? 急!
- 想用execute immediate执行一条update的SQL语句,但不成功,请各位帮看看
- 触发器编译时提示“触发器中不能包含LONG型字段“,但确实没有定义和指向这种类型的字段
- 请教一个关于三个表的SQL更新语句怎么写
- 一个简单的问题,望大侠指点
- 返回一条记录居然好几页,什么原因?是字段为blob还是触发器的原因?
- ★★★★★★OracleOraHome90Agent服务是做什么用的?
- 大小写问题
- oracle 10g ODBC 驱动问题 高分求助(在线等)急!!!
- 海量查询的问题,请高手指教!
Declare
L_x Number;
Begin
--
select count(1)
Into L_x
From t
Where --判断重复的条件
If l_x=0 Then
Insert Into t --新增
Else
Update t
Where --判断重复的条件
End If;
End;
INSERT INTO TAB
END IF
用隐藏式游标
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
WHEN NOT MATCHED THEN <insert_clause>;
CREATE TABLE employee (
employee_id NUMBER(5),
first_name VARCHAR2(20),
last_name VARCHAR2(20),
dept_no NUMBER(2),
salary NUMBER(10))
TABLESPACE data_sml;INSERT INTO employee VALUES (1, 'Dan', 'Morgan', 10, 100000);
INSERT INTO employee VALUES (2, 'Jack', 'Cline', 20, 100000);
INSERT INTO employee VALUES (3, 'Elizabeth', 'Scott', 20, 50000);
INSERT INTO employee VALUES (4, 'Jackie', 'Stough', 20, 40000);
INSERT INTO employee VALUES (5, 'Richard', 'Foote', 20, 30000);
INSERT INTO employee VALUES (6, 'Joe', 'Johnson', 20, 70000);
INSERT INTO employee VALUES (7, 'Clark', 'Urling', 20, 90000);
COMMIT;CREATE TABLE bonuses (
employee_id NUMBER, bonus NUMBER DEFAULT 100)
TABLESPACE data_sml;INSERT INTO bonuses (employee_id) VALUES (1);
INSERT INTO bonuses (employee_id) VALUES (2);
INSERT INTO bonuses (employee_id) VALUES (4);
INSERT INTO bonuses (employee_id) VALUES (6);
INSERT INTO bonuses (employee_id) VALUES (7);
COMMIT;MERGE INTO bonuses B
USING (
SELECT employee_id, salary
FROM employee
WHERE dept_no =20) E
ON (B.employee_id = E.employee_id)
WHEN MATCHED THEN
UPDATE SET B.bonus = E.salary * 0.1
WHEN NOT MATCHED THEN
INSERT (B.employee_id, B.bonus)
VALUES (E.employee_id, E.salary * 0.05);