Following is the scenario:
create table test1
( emp_no number,
p_no varchar2(2),
p_amount number(10),
)
insert into test1 values (1, 'x', 10 );
insert into test1 values (1, 'x', 20 );
insert into test1 values (1, 'y', 35 );
insert into test1 values (1, 'z', 60 );
insert into test1 values (1, 'z', 40 );
insert into test1 values (1, 'w', 90 );
insert into test1 values (2, 'x', 50 );
insert into test1 values (2, 'w', 70 );
insert into test1 values (2, 'w', 40 );
create table test2
( emp_no number,
e_date date,
serial_no number, -- EVERY INSERT RECORD +1 SAME EMP_NO
p_no_amount_x varchar2(10),
p_no_amount_y varchar2(10),
p_no_amount_z varchar2(10),
p_no_amount_w varchar2(10),
primary_key(emp_no ,e_date, serial_no)
)
I want to insert the values ??in test1(p_no) to test2
We link mtach sum value p_amount for test1(p_no) and if find with value column test2
For example
emp_no e_date serial_no p_no_amount_x p_no_amount_y p_no_amount_z p_no_amount_w
====== ====== ======= =========== ========= ========== ========
1 2002-01-30 1 30 35 100 90
2 2002-01-30 1 50 110
------------------
i CREATE ::::
CREATE OR REPLACE PROCEDURE p_insert ( P_DATE IN DATE)
IS
V_EMP_NO test1.EMP_NO%TYPE;
V_p_no test1.p_no%TYPE;
V_p_amount test1.P_AMOUNT%TYPE;
V_p_no_amount_x test2.p_no_amount_x%TYPE;
V_p_no_amount_Y test2.p_no_amount_Y%TYPE;
V_p_no_amount_Z test2.p_no_amount_Z%TYPE;
V_p_no_amount_W test2.p_no_amount_W%TYPE;
CURSOR EMP_CUR IS
SELECT EMP_NO,p_no,SUM(P_AMOUNT)
FROM test1
GROUP BY EMP_NO,P_NO
ORDER BY EMP_NO,P_NO ;
V_SERIAL_NO number;
BEGIN
OPEN EMP_CUR;
LOOP
FETCH EMP_CUR INTO V_EMP_NO,V_p_no,V_p_amount;
EXIT WHEN EMP_CUR%NOTFOUND;
-----------------------------
IF V_p_no = 'x' THEN
V_p_no_amount_x := V_p_amount;
END IF;
IF V_p_no = 'y' THEN
V_p_no_amount_Y := V_p_amount;
END IF;
IF V_p_no = 'w' THEN
V_p_no_amount_Y := V_p_amount;
END IF;
IF V_p_no = 'z' THEN
V_p_no_amount_W := V_p_amount;
END IF;
BEGIN
SELECT NVL(MAX(SERIAL_NO),0)+1
INTO V_SERIAL_NO
FROM TEST2
WHERE EMP_NO = V_EMP_NO;
END;
-----------------------------
INSERT INTO TEST2( EMP_NO,e_date,serial_no ,p_no_amount_x,p_no_amount_Y,p_no_amount_Z,p_no_amount_W)
VALUES( V_EMP_NO, P_DATE , V_SERIAL_NO ,V_p_no_amount_x,V_p_no_amount_Y,V_p_no_amount_Z,V_p_no_amount_W );
-----------------------------
END LOOP;
CLOSE EMP_CUR;
COMMIT;
END;
/
-------
I EXECUTE : select p_insert(to_date('2002-01-30', 'yyyy-mm-dd')) from dual
AND RESULT :::
emp_no e_date serial_no p_no_amount_x p_no_amount_y p_no_amount_z p_no_amount_w
====== ====== ======= =========== ========= ========== ========
1 2002-01-30 1 null 90 null null
1 2002-01-30 1 30 90 null null
1 2002-01-30 1 30 35 null null
1 2002-01-30 1 30 35 null 100
2 2002-01-30 1 30 150 null 100
2 2002-01-30 2 50 150 null 100
This result is not as I want,,,
I Want like this ::
emp_no e_date serial_no p_no_amount_x p_no_amount_y p_no_amount_z p_no_amount_w
====== ====== ======= =========== ========= ========== ========
1 2002-01-30 1 30 35 100 90
2 2002-01-30 1 50 null null 110
and another EXECUTE : select p_insert(to_date('2003-12-10', 'yyyy-mm-dd')) from dual
emp_no e_date serial_no p_no_amount_x p_no_amount_y p_no_amount_z p_no_amount_w
====== ====== ======= =========== ========= ========== ========
1 2003-12-10 2 30 35 100 90
2 2003-12-10 2 50 null null 110
then store in table test2 ::
emp_no e_date serial_no p_no_amount_x p_no_amount_y p_no_amount_z p_no_amount_w
====== ====== ======= =========== ========= ========== ========
1 2002-01-30 1 30 35 100 90
2 2002-01-30 1 50 null null 110
1 2003-12-10 1 30 35 100 90
2 2003-12-10 1 50 null null 110
Thanks a lot
create table test1
( emp_no number,
p_no varchar2(2),
p_amount number(10),
)
insert into test1 values (1, 'x', 10 );
insert into test1 values (1, 'x', 20 );
insert into test1 values (1, 'y', 35 );
insert into test1 values (1, 'z', 60 );
insert into test1 values (1, 'z', 40 );
insert into test1 values (1, 'w', 90 );
insert into test1 values (2, 'x', 50 );
insert into test1 values (2, 'w', 70 );
insert into test1 values (2, 'w', 40 );
create table test2
( emp_no number,
e_date date,
serial_no number, -- EVERY INSERT RECORD +1 SAME EMP_NO
p_no_amount_x varchar2(10),
p_no_amount_y varchar2(10),
p_no_amount_z varchar2(10),
p_no_amount_w varchar2(10),
primary_key(emp_no ,e_date, serial_no)
)
I want to insert the values ??in test1(p_no) to test2
We link mtach sum value p_amount for test1(p_no) and if find with value column test2
For example
emp_no e_date serial_no p_no_amount_x p_no_amount_y p_no_amount_z p_no_amount_w
====== ====== ======= =========== ========= ========== ========
1 2002-01-30 1 30 35 100 90
2 2002-01-30 1 50 110
------------------
i CREATE ::::
CREATE OR REPLACE PROCEDURE p_insert ( P_DATE IN DATE)
IS
V_EMP_NO test1.EMP_NO%TYPE;
V_p_no test1.p_no%TYPE;
V_p_amount test1.P_AMOUNT%TYPE;
V_p_no_amount_x test2.p_no_amount_x%TYPE;
V_p_no_amount_Y test2.p_no_amount_Y%TYPE;
V_p_no_amount_Z test2.p_no_amount_Z%TYPE;
V_p_no_amount_W test2.p_no_amount_W%TYPE;
CURSOR EMP_CUR IS
SELECT EMP_NO,p_no,SUM(P_AMOUNT)
FROM test1
GROUP BY EMP_NO,P_NO
ORDER BY EMP_NO,P_NO ;
V_SERIAL_NO number;
BEGIN
OPEN EMP_CUR;
LOOP
FETCH EMP_CUR INTO V_EMP_NO,V_p_no,V_p_amount;
EXIT WHEN EMP_CUR%NOTFOUND;
-----------------------------
IF V_p_no = 'x' THEN
V_p_no_amount_x := V_p_amount;
END IF;
IF V_p_no = 'y' THEN
V_p_no_amount_Y := V_p_amount;
END IF;
IF V_p_no = 'w' THEN
V_p_no_amount_Y := V_p_amount;
END IF;
IF V_p_no = 'z' THEN
V_p_no_amount_W := V_p_amount;
END IF;
BEGIN
SELECT NVL(MAX(SERIAL_NO),0)+1
INTO V_SERIAL_NO
FROM TEST2
WHERE EMP_NO = V_EMP_NO;
END;
-----------------------------
INSERT INTO TEST2( EMP_NO,e_date,serial_no ,p_no_amount_x,p_no_amount_Y,p_no_amount_Z,p_no_amount_W)
VALUES( V_EMP_NO, P_DATE , V_SERIAL_NO ,V_p_no_amount_x,V_p_no_amount_Y,V_p_no_amount_Z,V_p_no_amount_W );
-----------------------------
END LOOP;
CLOSE EMP_CUR;
COMMIT;
END;
/
-------
I EXECUTE : select p_insert(to_date('2002-01-30', 'yyyy-mm-dd')) from dual
AND RESULT :::
emp_no e_date serial_no p_no_amount_x p_no_amount_y p_no_amount_z p_no_amount_w
====== ====== ======= =========== ========= ========== ========
1 2002-01-30 1 null 90 null null
1 2002-01-30 1 30 90 null null
1 2002-01-30 1 30 35 null null
1 2002-01-30 1 30 35 null 100
2 2002-01-30 1 30 150 null 100
2 2002-01-30 2 50 150 null 100
This result is not as I want,,,
I Want like this ::
emp_no e_date serial_no p_no_amount_x p_no_amount_y p_no_amount_z p_no_amount_w
====== ====== ======= =========== ========= ========== ========
1 2002-01-30 1 30 35 100 90
2 2002-01-30 1 50 null null 110
and another EXECUTE : select p_insert(to_date('2003-12-10', 'yyyy-mm-dd')) from dual
emp_no e_date serial_no p_no_amount_x p_no_amount_y p_no_amount_z p_no_amount_w
====== ====== ======= =========== ========= ========== ========
1 2003-12-10 2 30 35 100 90
2 2003-12-10 2 50 null null 110
then store in table test2 ::
emp_no e_date serial_no p_no_amount_x p_no_amount_y p_no_amount_z p_no_amount_w
====== ====== ======= =========== ========= ========== ========
1 2002-01-30 1 30 35 100 90
2 2002-01-30 1 50 null null 110
1 2003-12-10 1 30 35 100 90
2 2003-12-10 1 50 null null 110
Thanks a lot