Help me to write a procedure for:verify a table for record if it is exists need to update or else insert that record( using cursor write a procedure )

manikandan

Dec 8th, 2006

using cursor u can fetch particular record and
check %found or %notfound. and insert
using procedure u can use exception whennodatafound and
insert the record in exception section.

solaimurugn

Dec 8th, 2006

create proc proc_in_del(@key int)
variable rst="select * from tblname where colname = @key ";
as
begin
if(rst)
begin
delete from tblname where colname=@key;
end
else
begin
insert into tblname values(@key);
end
end

SANTO

Mar 4th, 2007

HI,

U CAN SEACH FOR THE RECORD WEATHER IT'S PRESENT IN THE TABLE OR NOT USING FOLLOWING QUERY.

IN THIS EXAMPLE MY TABLE IS TEMP WITH ONE COLUMN SAY 'NAME'.

CREATE OR REPLACE PROCEDURE SEARCH(P_IN IN VARCHAR2)IS

CURSOR CC(P_IN IN VARCHAR2) IS
SELECT NAME FROM TEMP
WHERE NAME = P_IN;
V_NAME VARCHAR2(20);
EXP_FAIL EXCEPTION;
EXP_SUC EXCEPTION ;

BEGIN
OPEN CC(P_IN);
FETCH CC INTO V_NAME;
IF CC%FOUND THEN
RAISE EXP_FAIL;
ELSIF CC%NOTFOUND THEN
FETCH CC INTO V_NAME;
INSERT INTO TEMP(NAME) VALUES(P_IN);
COMMIT;
RAISE EXP_SUC;
END IF;

EXCEPTION
WHEN EXP_FAIL THEN
CLOSE CC;
DBMS_OUTPUT.PUT_LINE(P_IN|| ' IS ALREADY IN THE TABLE..!!');
WHEN EXP_SUC THEN
CLOSE CC;
DBMS_OUTPUT.PUT_LINE(P_IN|| ' HAS BEEN INSERTED..!!');
END;

REGS
SANTO.

ss

Sep 25th, 2007

Insert the row you want you as if it doesn't exist, in the exception write the update statement

Begin
//Inser the row

Exception
when< row already exists>
//update the row

End