I have the below query which is used to perform an insert or update based on the data retrieved from "NEW_TABLE", how can we rewrite the same using Oracle MERGE statement? Please clarify.
for TESTTABLE1 in (select NAME, ADDRESS, TESTTABLE1.ID from TESTTABLE1, TESTTABLE2 testtable2
where TESTTABLE1.ID = testtable2.ID)
select tableID into tableId from NEW_TABLE where ID = TESTTABLE1.ID and rownum=1;
WHEN NO_DATA_FOUND THEN
tableID := NULL;
if tableID is null then
Just I want to know since we are not using any table name for INSERT & UPDATE for MATCHED and NOT MATCHED for MERGE STATEMENT, in which table the records will be inserted and updated? Also if I have multiple INSERT and UPDATE statements under MATCHED and NOT MATCHED specific to different tables , how it will work as we wont specify the table name? For example :-
1. Use code tags around your (pseudo-)code as it makes it easier to read.
2. Your pseudo-code is rather confusing and mixes up various queries and inserts/updates against various tables. It would probably make sense to step back and describe clearly what you are looking for, and what you want to happen.
3. Oracle's MERGE statement is an "UPSERT" i.e. you tell it how to match records in a specific table. If the record does not exist, it will be inserted, otherwise it will be updated:
In the above example from the Oracle MERGE documentation, you should be able to see how it works.
We are merging into the bonuses table.
Our source data (in the USING clause) is queried from the employees table for department_id 80.
A record matches in the bonuses table if it has the same employee_id as the source record.
If the employee_id matches, then we update the existing record in the bonuses table and set the new bonus value.
If there is no matching record, then we insert the required data into the bonuses table.
This is one example of the MERGE syntax that illustrates how much logic you can encapsulate in a single SQL statement that can be executed efficiently on the database without fetching data into your Java application.
Rithanya Laxmi wrote:Just I want to know since we are not using any table name for INSERT & UPDATE for MATCHED and NOT MATCHED for MERGE STATEMENT, in which table the records will be inserted and updated? Also if I have multiple INSERT and UPDATE statements under MATCHED and NOT MATCHED specific to different tables , how it will work as we wont specify the table name? For example :-
A few notes:
WHEN NO_DATA_FOUND THEN
tableID := NULL;
That does absolutely nothing, as, if no record was returned, it would already be NULL. Remove that line of code. It does nothing.
It is generally not a good idea to store a "table id" in a table. It just confuses the code. Either the SP should decide what to do (and it does not need to be passed a "table id") or, if the code is aware of the required table, there should be two separate SPs, and the code should call the correct one.
This code wants to INSERT INTO two TABLEs, which is accomplished via an INSERT ALL (and UPDATE one of them), or it wants to INSERT INTO one table, and MERGE INTO another. Most likely, the MERGE is a better idea, as it will only acquire the lock on the table's record(s) once.
To decide whether to INSERT o not, use a WHERE clause. That's what it's for. Using procedural code just slows things down.
The FOR is most likely not required. In created an implicit CURSOR, and invokes the PL/SQL engine where SQL itself ought to work just fine.
Naming a variable the same name as the table it is getting code from is a bit confusing.
You cannot use a variable for a table name outside of dynamic SQL. Dynamic SQL is insecure and should not be used.
I am guessing that you want to write a generic SP to UPSERT into any given table. Please do not do this. It is bad design. If you must have one SP to rule them all, specify each TABLE separately. And include the COLUMN list. Besides being self-documenting, it'll save you from other bugs due to added or reordered columns.