Stored procedure 2

[WP]

I need to write a store procedure which does the following:

Passes entire record as parameters ( one for each field).
Checks for the records existence.

If it exists then
update the record
Else
insert the record.

I'm new to this stuff and have been toiling with the syntax all day.

Hope someone can help.

My insert proc looks like this:

CREATE OR REPLACE PROCEDURE Addfund
(P_OLYID IN VARCHAR2,
P_WKN IN VARCHAR2,
P_ISIN IN VARCHAR2,
P_SECURITY_NAME IN VARCHAR2,
P_PROVIDER IN VARCHAR2,
P_DOMOCILE IN VARCHAR2,
P_CURRENCY IN VARCHAR2,
P_RISK IN VARCHAR2,
P_MIN_INVEST IN NUMBER,
P_COMMISSION IN FLOAT,
P_ALLOW_REINVEST IN VARCHAR2,
P_CUT_OFF_TIME IN DATE,
P_NAV IN NUMBER,
P_NAV_DATE IN DATE) AS

BEGIN

INSERT INTO TEST_FUND
VALUES(P_OLYID, P_WKN, P_ISIN,
P_SECURITY_NAME, P_PROVIDER,
P_DOMOCILE, P_CURRENCY,
P_RISK, P_MIN_INVEST,
P_COMMISSION, P_ALLOW_REINVEST,
P_CUT_OFF_TIME, P_NAV,
P_NAV_DATE);

END Addfund;
/
Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out my website !

 

[carp]

How about something like



CREATE OR REPLACE PROCEDURE Addfund
(P_OLYID IN VARCHAR2,
P_WKN IN VARCHAR2,
P_ISIN IN VARCHAR2,
P_SECURITY_NAME IN VARCHAR2,
P_PROVIDER IN VARCHAR2,
P_DOMOCILE IN VARCHAR2,
P_CURRENCY IN VARCHAR2,
P_RISK IN VARCHAR2,
P_MIN_INVEST IN NUMBER,
P_COMMISSION IN FLOAT,
P_ALLOW_REINVEST IN VARCHAR2,
P_CUT_OFF_TIME IN DATE,
P_NAV IN NUMBER,
P_NAV_DATE IN DATE) AS

v_rows NUMBER;

BEGIN
select count(*) into v_rows
from test_fund
where
olyid = p_olyid AND
wkn = p_wkn
etc,etc;

if v_rows <> 0 then
update test_fund
set whatever_needs_to_be_updated
where
olyid = p_olyid etc,etc;

else
INSERT INTO TEST_FUND
VALUES(P_OLYID, P_WKN, P_ISIN,
P_SECURITY_NAME, P_PROVIDER,
P_DOMOCILE, P_CURRENCY,
P_RISK, P_MIN_INVEST,
P_COMMISSION, P_ALLOW_REINVEST,
P_CUT_OFF_TIME, P_NAV,
P_NAV_DATE);
end if;

END Addfund;
/

Doubtless there is at least one cleaner way to do this, but that is left as an exercise to the reader ;-}

 

[WP]

Carp,

Thanks for the code. I had worked out something similar but hadn't the ; in all the right places.

Without your help I'd still be scratching my head.

Much appreciated,


WP




Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out my website !

 

[LokiDba]

Heres a different solution. The problem with SELECT COUNT(*) is that its not that efficent if the table is big. So as an alternative to the SELECT COUNT(*) you could do the following:

CREATE OR REPLACE PROCEDURE Addfund
(P_OLYID IN VARCHAR2,
P_WKN IN VARCHAR2,
P_ISIN IN VARCHAR2,
P_SECURITY_NAME IN VARCHAR2,
P_PROVIDER IN VARCHAR2,
P_DOMOCILE IN VARCHAR2,
P_CURRENCY IN VARCHAR2,
P_RISK IN VARCHAR2,
P_MIN_INVEST IN NUMBER,
P_COMMISSION IN FLOAT,
P_ALLOW_REINVEST IN VARCHAR2,
P_CUT_OFF_TIME IN DATE,
P_NAV IN NUMBER,
P_NAV_DATE IN DATE) AS

BEGIN

BEGIN
update test_fund
set whatever_needs_to_be_updated
where olyid = p_olyid etc,etc;

EXCEPTION
WHEN NO_DATA_FOUND THEN

INSERT INTO TEST_FUND
VALUES(P_OLYID, P_WKN, P_ISIN,
P_SECURITY_NAME, P_PROVIDER,
P_DOMOCILE, P_CURRENCY,
P_RISK, P_MIN_INVEST,
P_COMMISSION, P_ALLOW_REINVEST,
P_CUT_OFF_TIME, P_NAV,
P_NAV_DATE);

END;

END Addfund;
/

This is using an EXCEPTION to catch the fact that the data does not exist in your table and is designed for when you expect the details to exist.

If it is more likely that the value will not exist then you can switch the insert and update round and use the exception of DUP_VALUE_ON_INDEX as long as the table has a primary key.

Hope this is useful

LokiDba

 

[manojbajaj]

This again very Simple . Please use this

update test_fund
set whatever_needs_to_be_updated
where olyid = p_olyid etc,etc;

IF SQL%NROWS <= 0 Then
INSERT INTO....

End If;


Here Keyword SQL Acts as a Implicit cursor Name and NROWS
is its property..

Manoj

 

[WP]

LokiDba,maojbajaj,

Thanks for the code.

My tables are not so big and Carp's solution is fine.

But this is good stuff that I can use in the future.

Thank you very much.


Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out my website !