Home » SQL & PL/SQL » SQL & PL/SQL » Stored proc using merge (Oracle 9.2.0.3)
Stored proc using merge [message #419606] Mon, 24 August 2009 08:12 Go to next message
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 #419615 is a reply to message #419606] Mon, 24 August 2009 08:24 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member

Are you looking for how to write Merge command?

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9016.htm#SQLRF01606
Re: Stored proc using merge [message #419625 is a reply to message #419606] Mon, 24 August 2009 09:07 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Performabce tuning in SQL
Next Topic: ORA-01460: unimplemented or unreasonable conversion requested
Goto Forum:
  


Current Time: Wed Dec 07 08:55:09 CST 2016

Total time taken to generate the page: 0.11538 seconds