Oracle 通过过程插入或更新行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12705752/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Inserting or Updating a row through a procedure
提问by ntonzz
I have a table
我有一张桌子
CREATE TABLE STUDENT
(
ID INTEGER PRIMARY KEY,
FIRSTNAME VARCHAR2(1024 CHAR),
LASTNAME VARCHAR2(1024 CHAR),
MODIFIEDDATE DATE DEFAULT sysdate
)
I am inserting a row of data
我正在插入一行数据
insert into STUDENT (ID, FIRSTNAME, LASTNAME, MODIFIEDDATE) values (1,'Scott', 'Tiger', sysdate);
When I have to insert a record of data, I need to write a procedure or function which does the following:
当我必须插入数据记录时，我需要编写一个执行以下操作的过程或函数：
- if there is no record for the same id insert the row.
- if there is a record for the same id and data matches then do nothing.
- if there is a record for the same id but data does not match then update the data.
- 如果没有相同 ID 的记录，则插入该行。
- 如果有相同 id 和数据匹配的记录，则什么都不做。
- 如果有相同 ID 的记录但数据不匹配，则更新数据。
I am new to oracle. From the java end, It is possible to select the record by id and then update that record, but that would make 2 database calls. just to avoid that I am trying update the table using a procedure. If the same can be done in a single database call please mention.
我是 oracle 的新手。从 java 端，可以通过 id 选择记录，然后更新该记录，但这会进行 2 次数据库调用。只是为了避免我尝试使用过程更新表。如果可以在单个数据库调用中完成相同的操作，请提及。
回答by bpgergo
For a single SQL statement solution, you can try to use the MERGEstatement, as described in this answer https://stackoverflow.com/a/237328/176569
对于单个 SQL 语句解决方案，您可以尝试使用MERGE语句，如本答案https://stackoverflow.com/a/237328/176569 中所述
e.g.
例如
create or replace procedure insert_or_update_student(
p_id number, p_firstname varchar2, p_lastname varchar2
) as
begin
merge into student st using dual on (id = p_id)
when not matched then insert (id, firstname, lastname)
values (p_id, p_firstname, p_lastname)
when matched then update set
firstname = p_firstname, lastname = p_lastname, modifiedate = SYSDATE
end insert_or_update_student;
回答by Girish R Acharya
instead of procedure try using merge in oracle . If Values is matched it will update the table and if values is not found it will insert the values
而不是过程尝试在 oracle 中使用合并。如果值匹配，它将更新表，如果未找到值，它将插入值
MERGE INTO bonuses b
USING (
SELECT employee_id, salary, dept_no
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
DELETE WHERE (e.salary < 40000)
WHEN NOT MATCHED THEN
INSERT (b.employee_id, b.bonus)
VALUES (e.employee_id, e.salary * 0.05)
WHERE (e.salary > 40000)
Try this
尝试这个
回答by Eduard Okhvat
To solve the second task - "if there is a record for the same id and data matches then do nothing."- starting with 10g we have additional "where" clause in update and insert sections of merge operator. To do the task we can add some checks for data changes:
解决第二个任务—— “如果有相同 id 的记录并且数据匹配，那么什么都不做。” - 从 10g 开始，我们在合并运算符的更新和插入部分有额外的“where”子句。为了完成这项任务，我们可以添加一些数据更改检查：
when matched then update
set student.last_name = query.last_name
where student.last_name <> query.last_name
This will update only matched rows, and only for rows where data were changed
这将仅更新匹配的行，并且仅更新数据已更改的行
