Hello every one i am new in oracle i want to make a procedure for retrieve date from two tables, add them and save into 3rd table.
i have 3 tables named table1,table2,table3. table1 have three columns named DR,CR Date, table 2 have two columns named Balance,Date and table three also two columns totalbalance,date..
the working i want select from table1 DR-CR and select from table 2 Balance...
and insert into totalbalance = table1's value + table two's value between fromdate and todate parameters ..
.
.
create or replace PROCEDURE LOOPUPDATEWITHDATE
(FRMNUMBER IN DATE,
TONUMBER IN DATE)
IS
LOOPNO DATE;
FCODE NUMBER;
BEGIN
FOR LOOPNO IN FRMNUMBER..TONUMBER LOOP
INSERT INTO T3
(BALANCE)
SELECT (DR-CR) FROM T1 WHERE FRMDATE = LOOPNO;
SELECT CODE INTO FCODE FROM T1 WHERE FRMDATE = LOOPNO;
UPDATE T3
SET BALANCE = (BALANCE) + (SELECT BALANCE
FROM T2
WHERE FRMDATE = LOOPNO)
WHERE CODE = FCODE;
commit;
END LOOP;
END LOOPUPDATEWITHDATE;.
.
.
If I understand your question correctly, you dont need a looping structure, just SQL will do, eg
SQL> create table t1 ( code varchar2(10), dr int, cr int, d date );
Table created.
SQL> create table t2 ( code varchar2(10), balance int, d date );
Table created.
SQL> create table t3 ( code varchar2(10), total_balance int, d date );
Table created.
SQL>
SQL> insert into t1 select 'x', dbms_random.value(0,100), dbms_random.value(0,100), trunc(sysdate)-rownum from dual connect by level <= 10;
10 rows created.
SQL> insert into t2 select 'x', dbms_random.value(0,100), trunc(sysdate)-rownum from dual connect by level <= 10;
10 rows created.
SQL>
SQL> select * from t1;
CODE DR CR D
---------- ---------- ---------- ---------
x 54 67 01-JAN-16
x 11 78 31-DEC-15
x 99 11 30-DEC-15
x 87 12 29-DEC-15
x 6 85 28-DEC-15
x 71 39 27-DEC-15
x 37 29 26-DEC-15
x 80 41 25-DEC-15
x 65 83 24-DEC-15
x 81 70 23-DEC-15
10 rows selected.
SQL> select * from t2;
CODE BALANCE D
---------- ---------- ---------
x 88 01-JAN-16
x 58 31-DEC-15
x 44 30-DEC-15
x 94 29-DEC-15
x 16 28-DEC-15
x 24 27-DEC-15
x 46 26-DEC-15
x 70 25-DEC-15
x 26 24-DEC-15
x 14 23-DEC-15
10 rows selected.
SQL> create or replace
2 procedure load_t3(p_from date, p_to date ) is
3 begin
4 insert into T3
5 select t1.code, t1.dr - t1.cr + t2.balance, t1.d
6 from t1, t2
7 where t1.d between p_from and p_to
8 and t2.d = t1.d
9 and t2.code = t2.code;
10 end;
11 /
Procedure created.
SQL>
SQL>
SQL> exec load_t3(sysdate-20,sysdate)
PL/SQL procedure successfully completed.
SQL> select * from t3;
CODE TOTAL_BALANCE D
---------- ------------- ---------
x 75 01-JAN-16
x -9 31-DEC-15
x 132 30-DEC-15
x 169 29-DEC-15
x -63 28-DEC-15
x 56 27-DEC-15
x 54 26-DEC-15
x 109 25-DEC-15
x 8 24-DEC-15
x 25 23-DEC-15
10 rows selected.
SQL>