How to update column value to a table from another table in a stored procedure?

Question

I created a procedure from querying other tables including transaction tbl to settle all transaction records with a reference number and date automatically stamped on it.

What I try to do is my settle_transaction procedure needs to generate my query from the selected statement and insert them into the settlement table. Meanwhile, I also need to update the ref_num and processed date as a "stamp" to the transaction table so that I don't have duplicated settlement when calling the procedure again. Otherwise, I don't know how to stop showing the same settlement data twice

Here is the procedure to output a settlement tbl and structure similar below:

BEGIN
    for r_client in 
    (
        select clientid,
        client_name, sum(transaction) total_amount
        from transaction_tbl tran join terminal_tbl term
        on tran.terminalid = term.terminalid join client_tbl c on c.clientid = term.clientid
        where refnr is null
    )
    loop
    v_refnr := get_refnr;
        insert into settlement_tbl
        (
            Ref_Num, 
            Total,
            CLIENTID,
            TITLE, 
            processeddate
        )
        values (v_refnr, total_amount, clientid,
        name,sysdate);
update_refnr(v_refnr, sysdate)
        end loop;
END

Output:

| reference_num | total amount | client id | client name | processed_date |
|---------------|--------------|-----------|-------------|----------------|

When I execute the above procedure, it populates all the result from the select query. However, if I execute again, it will duplicate the same result especially the total amount.

I'm seeking a solution to put another procedure/function inside this settlement procedure to prevents duplicate records from the selected query in this procedure.

I use the ref. no# and process_date to update the existing reference num and date to the transaction tbl show below.

| transaction_num | transaction amount | reference_num | processed_date |
|-----------------|--------------------|---------------|----------------|

Here is the attempted code I put inside the settlement procedure but still shows duplicated records and can not update to the transaction tbl.

procedure update_refnr(
  p_refnr in number,
  p_processeddate in date
)
is
begin
    UPDATE TRANSACTION t 
       SET t.refnr = p_refnr
     WHERE EXISTS (SELECT p_processeddate 
                     FROM terminal_tbl
                    WHERE t.TERMINALID= term.TERMINALID
                      AND t.processeddate = p_processeddate
                      AND t.refnr IS NULL);   
--exception handling below

end update_refnr;

I also tried other SQL reference but cannot compile.

Ideally, I don't have duplicated records in my settlement tbl when I retrieve each record from my stored procedure.

Answer 1

You want to insert new data into your table only when it doesn't already exist. As others have said, you can use MERGE to do that:

BEGIN
  for r_client in (select clientid,
                          client_name,
                          sum(transaction) total_amount
                     from transaction_tbl tran
                     join terminal_tbl term
                       on tran.terminalid = term.terminalid
                     join client_tbl c
                       on c.clientid = term.clientid
                     where refnr is null)
  loop
    v_refnr := get_refnr;

    MERGE INTO settlement_tbl s
      USING (SELECT v_refnr      AS REF_NUM,
                    total_amount AS TOTAL,
                    clientid     AS CLIENTID,
                    name         AS TITLE,
                    SYSDATE      AS PROCESSEDDATE
               FROM DUAL) d
        ON (s.REF_NUM = d.REF_NUM)
      WHEN NOT MATCHED THEN
        INSERT (Ref_Num,   Total,   CLIENTID,   TITLE,   processeddate)
        VALUES (d.REF_NUM, d.TOTAL, d.CLIENTID, d.TITLE, d.PROCESSEDDATE);

    update_refnr(v_refnr, sysdate);
  END LOOP;
END;

WHEN NOT MATCHED inserts new data when v_refnr does not already exist in your table.

Best of luck.