As far as i know, function is not executing DML but today I got the chance to review a function where a developer has used DML within a function and it is executing like procedure. Oh my GOD...What is this?? It is the first statement that I give when I executed that function. Right from my educational life, I was taught by my seniors that only difference between function and procedure is that function always return a value and procedure may or may not but if function is also doing the same operations that we do in procedure then where is the difference?? It could also possible that I have never used function like this but for now it is fact that my mind is unable to accept this. Please find the code below if any one is unaware of this fact. Also please put some light on such behavior of oracle like why DML in function if we have procedures?? One thing that I also want to mention over here that if we use function in select statement then it throws error but if we execute it like in begin end clause then it performs whatever we have wrote in it.
create table CUSTM_REC
(
RECORD_ID NUMBER,
CUSTOMER_ID NUMBER,
AMOUNT_DTE DATE,
AMOUNT NUMBER,
CURRENT_BLNCE NUMBER
);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (25, 5, to_date('06-01-2012', 'dd-mm-yyyy'), 5000, 5000);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (26, 5, to_date('06-01-2012', 'dd-mm-yyyy'), 5000, 10000);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (16, 5, to_date('07-11-2011', 'dd-mm-yyyy'), 3000, 3000);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (19, 3, to_date('06-01-2012', 'dd-mm-yyyy'), -2000, 5000);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (20, 5, to_date('06-01-2012', 'dd-mm-yyyy'), -2000, 0);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (9, 1, to_date('06-01-2012', 'dd-mm-yyyy'), 5000, 9000);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (10, 2, to_date('06-01-2012', 'dd-mm-yyyy'), 15000, 15000);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (11, 3, to_date('06-01-2012', 'dd-mm-yyyy'), 10000, 10000);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (12, 1, to_date('07-11-2011', 'dd-mm-yyyy'), 10000, 10000);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (13, 1, to_date('06-12-2011', 'dd-mm-yyyy'), -6000, 4000);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (14, 3, to_date('06-01-2012', 'dd-mm-yyyy'), -3000, 7000);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (15, 2, to_date('07-01-2012', 'dd-mm-yyyy'), -8000, 15000);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (18, 2, to_date('06-01-2012', 'dd-mm-yyyy'), 8000, 23000);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (17, 5, to_date('06-01-2012', 'dd-mm-yyyy'), -1000, 2000);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (32, 6, to_date('06-01-2012', 'dd-mm-yyyy'), 2000, 2000);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (34, 6, to_date('06-01-2012', 'dd-mm-yyyy'), -3000, 1000);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (28, 4, to_date('06-01-2012', 'dd-mm-yyyy'), 6000, 6000);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (30, 4, to_date('06-01-2012', 'dd-mm-yyyy'), 1000, 7000);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (31, 4, to_date('06-01-2012', 'dd-mm-yyyy'), -2000, 5000);
insert into CUSTM_REC (RECORD_ID, CUSTOMER_ID, AMOUNT_DTE, AMOUNT, CURRENT_BLNCE)
values (33, 6, to_date('06-01-2012', 'dd-mm-yyyy'), 2000, 4000);
CREATE OR REPLACE FUNCTION FN_BLANCE
(p_val IN NUMBER)
RETURN NUMBER
AS
v_sum number (20) :=0;
v_cus_id number (20):=0;
v_rec_id number (20);
CURSOR i IS SELECT d.customer_id,d.record_id,d.amount
FROM CUSTM_REC d
ORDER BY(CUSTOMER_ID) ,(AMOUNT_DTE),(RECORD_ID);
BEGIN
FOR CUSTM_REC IN i
LOOP
v_rec_id:= CUSTM_REC.record_id;
IF v_cus_id <> CUSTM_REC.customer_id
THEN
v_sum := 0;
END IF;
v_cus_id:= CUSTM_REC.customer_id;
v_sum := v_sum + CUSTM_REC.amount;
UPDATE CUSTM_REC d SET d.current_blnce=v_sum
WHERE d.customer_id=v_cus_id and d.record_id=v_rec_id;
END LOOP;
RETURN p_val;
END;
/
SELECT FN_BLANCE(1) FROM dual;
It will give below error
ORA-14551: cannot perform a DML operation inside a query
DECLARE a INT;
BEGIN
a := fn_blance(1);
END;
It will execute successfully.