Home » SQL & PL/SQL » SQL & PL/SQL » generic way of insertion
generic way of insertion [message #199285] Mon, 23 October 2006 03:38 Go to next message
manyal
Messages: 77
Registered: March 2005
Member
Dear Friends,

I am having following procedure to insert records in the table it works fine but i want it to be a more generic


create or replace PROCEDURE EMP_ENTL
AS
CURSOR EMP_ENTL IS SELECT * FROM EXT_EMP_ENTL_DEDU;
BEGIN
FOR CUR_VAL IN EMP_ENTL
LOOP
IF CUR_VAL.EN1 > 0 THEN
INSERT INTO T_EMP_ENTITLEMENT VALUES ('CAT',CUR_VAL.EMP_CC_N0,CUR_VAL.EN1,CUR_VAL.EA1,'C');
IF CUR_VAL.EN2 > 0 THEN
INSERT INTO T_EMP_ENTITLEMENT VALUES('CAT',CUR_VAL.EMP_CC_N0,CUR_VAL.EN2,CUR_VAL.EA2,'C');
IF CUR_VAL.EN3 > 0 THEN
INSERT INTO T_EMP_ENTITLEMENT VALUES('CAT',CUR_VAL.EMP_CC_N0,CUR_VAL.EN3,CUR_VAL.EA3,'C');
IF CUR_VAL.EN4 > 0 THEN
INSERT INTO T_EMP_ENTITLEMENT VALUES('CAT',CUR_VAL.EMP_CC_N0,CUR_VAL.EN4,CUR_VAL.EA4,'C');
IF CUR_VAL.EN5 > 0 THEN
INSERT INTO T_EMP_ENTITLEMENT VALUES('CAT',CUR_VAL.EMP_CC_N0, CUR_VAL.EN5,CUR_VAL.EA5,'C');
IF CUR_VAL.EN6 > 0 THEN
INSERT INTO T_EMP_ENTITLEMENT VALUES('CAT',CUR_VAL.EMP_CC_N0, CUR_VAL.EN6,CUR_VAL.EA6,'C');
IF CUR_VAL.EN7 > 0 THEN
INSERT INTO T_EMP_ENTITLEMENT VALUES('CAT',CUR_VAL.EMP_CC_N0,CUR_VAL.EN1,CUR_VAL.EA7,'C');
IF CUR_VAL.EN8 > 0 THEN
INSERT INTO T_EMP_ENTITLEMENT VALUES('CAT',CUR_VAL.EMP_CC_N0,CUR_VAL.EN8,CUR_VAL.EA8,'C');
IF CUR_VAL.EN9 > 0 THEN
INSERT INTO T_EMP_ENTITLEMENT VALUES('CAT',CUR_VAL.EMP_CC_N0,CUR_VAL.EN9,CUR_VAL.EA9,'C');
IF CUR_VAL.EN10 > 0 THEN
INSERT INTO T_EMP_ENTITLEMENT VALUES('CAT',CUR_VAL.EMP_CC_N0,CUR_VAL.EN10,CUR_VAL.EA10,'C');
IF CUR_VAL.EN11 > 0 THEN
INSERT INTO T_EMP_ENTITLEMENT VALUES('CAT',CUR_VAL.EMP_CC_N0,CUR_VAL.EN11,CUR_VAL.EA11,'C');
IF CUR_VAL.EN12 > 0 THEN
INSERT INTO T_EMP_ENTITLEMENT VALUES('CAT',CUR_VAL.EMP_CC_N0,CUR_VAL.EN12,CUR_VAL.EA12,'C');
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END LOOP;
END;

instead of using various flags for insertion i want to apply insert statement within a loop.



Thanx in Advance
Re: generic way of insertion [message #199384 is a reply to message #199285] Mon, 23 October 2006 20:59 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
See if this gives you any ideas.

Ross Leishman
Re: generic way of insertion [message #199813 is a reply to message #199384] Thu, 26 October 2006 05:34 Go to previous message
manyal
Messages: 77
Registered: March 2005
Member
idea of dynamic sql is good but how we will eliminate condition portion before insertion.




IF CUR_VAL.EN1 > 0 THEN (in first iteration of loop ececution)

INSERT INTO T_EMP_ENTITLEMENT VALUES ('CAT',CUR_VAL.EMP_CC_N0,CUR_VAL.EN1,CUR_VAL.EA1,'C');

IF CUR_VAL.EN2 > 0 THEN (in second iteration of loop ececution)

INSERT INTO T_EMP_ENTITLEMENT VALUES('CAT',CUR_VAL.EMP_CC_N0,CUR_VAL.EN2,CUR_VAL.EA2,'C');

..........
..........
Previous Topic: Table heading
Next Topic: Inner Sub Queries
Goto Forum:
  


Current Time: Mon Dec 05 21:33:41 CST 2016

Total time taken to generate the page: 0.27248 seconds