DML(insert/update/delete) queries in Oracle Function

DML(insert/update/delete) queries in Oracle Function

Use PRAGMA AUTONOMOUS_TRANSACTION while creating function, then you can use the function as in select function)name from dual and you can perform DML statements such as insert, update and delete within the query.


Function MEMBERS.FN_UPDATE_BADGES
( M_USER_ID INTEGER,
M_BADGE_TYPE_ID INTEGER,
M_RAMP_CODE VARCHAR2,
M_D_CODE VARCHAR2
)
RETURN VARCHAR2 IS

-- MODIFICATION HISTORY
-- THOWFEEK M. KHAN 10-04-2009
-- --------- ------ -------------------------------------------

M_STATUS VARCHAR2(50);
M_COUNTS INTEGER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

SELECT COUNT(*) INTO M_COUNTS FROM MEMBERS.TBL_USER_BADGES WHERE USER_ID=M_USER_ID AND BADGE_TYPE=M_BADGE_TYPE_ID;

IF(M_COUNTS>0) THEN
UPDATE MEMBERS.TBL_USER_BADGES SET BADGE_TYPE=M_BADGE_TYPE_ID, RAMP_CODE=M_RAMP_CODE, D_CODE=M_D_CODE, AWARD_DATE=sysdate
WHERE USER_ID=M_USER_ID;
COMMIT;
M_STATUS := 'UPDATED';
ELSE
INSERT INTO MEMBERS.TBL_USER_BADGES VALUES(M_USER_ID,M_BADGE_TYPE_ID,M_RAMP_CODE,M_D_CODE,sysdate);
COMMIT;
M_STATUS := 'INSERTED';
END IF;


RETURN M_STATUS;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'FAILED';
END; -- Function FN_UPDATE_BADGES