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.