Stored proc using merge [message #419606] |
Mon, 24 August 2009 08:12  |
nishita2009
Messages: 39 Registered: August 2009 Location: INDIA
|
Member |
|
|
Dear Oracle geeks,
Can we do the follwing problem using Merge? If yes, how?
Problem:
Check for P_ID exists . if it does not exist then it should be an insert then update.
table name: CUST01
COLUMN Update rules
P_ID NOT NULL NUMBER(10,0) N
C_ID NOT NULL NUMBER(3,0) N
FILE_ID NOT NULL NUMBER(5,0) V
BILL_NO NOT NULL NUMBER(5,0) V
CUST_NO NOT NULL NUMBER(11,0) V
UPD_DT NOT NULL DATE (8) Y
UPD_USER NOT NULL VARCHAR2(8) Y
UPD_G_ID NOT NULL VARCHAR2(8) Y
V- Verify if it is different from the existing one only then update.
If null is passed, ignore the null if data exists already
N- NOT UPDATEABLE COLUMN
Y- UPDATE COLUMNS
Input parameters : All the columns in the table
|
|
|
|
Re: Stored proc using merge [message #419625 is a reply to message #419606] |
Mon, 24 August 2009 09:07  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Post a working Test case: create table and insert statements along with the result you want with these data.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.
Regards
Michel
|
|
|