Oracle Inserting or Updating a row through a procedure

Question

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.

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.

score 3 · Accepted Answer · edited May 23 '17 at 12:07

3

For a single SQL statement solution, you can try to use the MERGE statement, as described in this answer 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;

edited May 23 '17 at 12:07

Community

1
1

answered Oct 03 '12 at 09:46

bpgergo

15,669
5
44
68

1

The procedure isn't strictly necessary; all the procedure would have been needed for was to perform multiple DML operations from one JDBC call. Just using the merge statement directly from JDBC is fine. – Colin 't Hart Oct 03 '12 at 10:01
1

Some developers -- most notably Steven Feuerstein -- like to wrap *all* database access in stored procedures. I prefer not to: I treat the relational model as my "interface". – Colin 't Hart Oct 03 '12 at 10:04
Merge seems to be a good option as it is a single database call and would improve performance. The given merge does the following: 1) when not matched insert 2) when matched update For me what is required is 1) when no data insert 2) when matches do nothing 3) when not matches UPDATE would i be able to find out "when no data insert" using merge. – ntonzz Oct 04 '12 at 16:50
i call these "upsert_" Functions and return the sql%rowcount – grokster Sep 05 '14 at 01:18

score 3 · Answer 2 · answered Oct 03 '12 at 09:47

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

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

score 0 · Answer 3 · answered Jun 08 '17 at 09:25

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:

    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

Oracle Inserting or Updating a row through a procedure

3 Answers3