can we insert values into table using cursor in plsql oracle

Question

I want to insert sum of credit and debit of daily transaction from one table to another table when I run sql its give multi row function but when I insert into another table in plsql block it giving error

CREATE TABLE TRANSACTION_DAILY(
T_DATE  DATE,
CREDIT  NUMBER,
DEBIT   NUMBER
);

CREATE TABLE TRANSACTION_DAILY_total(
T_DATE  DATE,
total_CREDIT  NUMBER,
total_DEBIT   NUMBER
);

CREATE OR REPLACE trigger PRO_DAILY 
after insert on transaction_daily
declare
    CREDIT TRANSACTION_DAILY.credit%type;
    DEBIT  TRANSACTION_DAILY.debit%type;
    n_date TRANSACTION_DAILY.t_date%type;

    cursor c_daily
    is
        SELECT SUM( CREDIT ), SUM(DEBIT)
        FROM TRANSACTION_DAILY
        group by t_date;
begin
    open c_daily;

    fetch c_daily bull collect into  credit,debit,n_date;

    INSERT INTO TRANSACTION_DAILY_TOTAL (T_DATE,TOTAL_CREDIT,TOTAL_DEBIT) VALUES 
    (N_DATE,CREDIT,DEBIT);

    close c_daily;
END;

I strongly recommend you delete your question, then edit it to use upper and lower case correctly. People read ALL CAPS as shouting, and people sometimes downvote just on that basis. (They shouldn't, but do. :-) ) — T.J. Crowder
– T.J. Crowder, Commented Dec 14, 2021 at 19:02

Littlefoot · Accepted Answer · 2021-12-14 19:15:14Z

You don't need cursor. Actually, you don't need (nor want) a trigger at all. Here's why.

When fixed, trigger looks like this:

SQL> CREATE OR REPLACE TRIGGER pro_daily AFTER
  2      INSERT ON transaction_daily
  3  BEGIN
  4      INSERT INTO transaction_daily_total (
  5          t_date,
  6          total_credit,
  7          total_debit
  8      )
  9          SELECT
 10              t_date,
 11              SUM(credit),
 12              SUM(debit)
 13          FROM
 14              transaction_daily
 15          GROUP BY
 16              t_date;
 17  END;
 18  /

Trigger created.

SQL>

Does it work? Sure:

SQL> insert into transaction_daily (t_date, credit, debit)
  2    select date '2021-12-10', 100, 50 from dual union all
  3    select date '2021-12-10', 200, 75 from dual union all
  4    select date '2021-10-14', 500, 20 from dual;

3 rows created.

SQL> select * From transaction_daily_total;

T_DATE     TOTAL_CREDIT TOTAL_DEBIT
---------- ------------ -----------
2021-12-10          300         125
2021-10-14          500          20

SQL>

As expected, transaction_daily_total contains totals for those two dates. But, what if we insert just one another row?

SQL> insert into transaction_daily(t_date, credit, debit)
  2    values (date '2021-12-01', 1, 1);

1 row created.

SQL> select * From transaction_daily_total;

T_DATE     TOTAL_CREDIT TOTAL_DEBIT
---------- ------------ -----------
2021-12-10          300         125
2021-10-14          500          20
2021-12-10          300         125
2021-10-14          500          20
2021-12-01            1           1

SQL>

Whooops! Duplicates! Are you sure you really want that to happen?

What should you do? As I said - abandon the trigger & the "total" table idea and switch to a view:

SQL> create or replace view v_transaction_daily_total as
  2    select t_date, sum(credit) credit, sum(debit) debit
  3    from transaction_daily
  4    group by t_date;

View created.

SQL> select * From v_transaction_daily_total;

T_DATE         CREDIT      DEBIT
---------- ---------- ----------
2021-12-10        300        125
2021-10-14        500         20
2021-12-01          1          1

SQL>

Insert another row? No problem for the view:

SQL> insert into transaction_daily(t_date, credit, debit)
  2    values (date '2021-12-02', 50, 50);

1 row created.

SQL> select * From v_transaction_daily_total;

T_DATE         CREDIT      DEBIT
---------- ---------- ----------
2021-12-10        300        125
2021-10-14        500         20
2021-12-02         50         50
2021-12-01          1          1

SQL>

can you please explain me why you used union all in insert instead of using regular insert statement
No particular reason. Didn't feel like typing separate INSERT statements.

Collectives™ on Stack Overflow

can we insert values into table using cursor in plsql oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related