Compare values of single column in PL/SQL table using loop

Question

after that i need to pipe row compared values How we can compare values of single column in PLSQL table using loop? After that I need to Pipe row compared values. Record:

type rec is record (tab_value varchar2(4000),
        trn_date date);
        V_REC REC;
        --table
        type tab is table of rec;
        V_TAB TAB;
    -- variable
    v_query(4000);
    -- defining cursor
      TYPE CUR IS REF CURSOR;
      V_CUR CUR;
    -- query
    -- MASTER TABLE
    v_query := ' SELECT D.DESCRIPTION  AS TAB_VALUE,--column on which dml performs
                        D.TRN_DATE
                      FROM TEST.TABLE D
                     WHERE 1 = 1 '||
          ' UNION
    SELECT D.DESCRIPTION AS TAB_VALUE,-- column which stores old value of master table
                    D.NEW_TRN_DATE AS TRN_DATE
                  FROM TEST.SYN_TABLE D
                 WHERE 1 = 1 ';

When DML is performed on description column (D.DESCRIPTION) of master table then recent value of master table ---stored in history table

--opening cursor      
OPEN V_CUR_HIST FOR V_QUERY;
      -- loop started
      LOOP
        FETCH V_CUR 
          INTO V_REC;
        EXIT WHEN V_CUR_HIST%NOTFOUND;

        V_TAB(V_INDEX).TAB_VALUE  := V_REC.TAB_VALUE;--contains value of master & history table
        V_TAB(V_INDEX).TRN_DATE   := V_REC.TRN_DATE;
    --adding increment in v_index
        V_INDEX := V_INDEX + 1;

    END LOOP;

Now what I require is data comparison of master value and history table value in form of output like this:

old_value(history table value)      new_value(master table value)
                                    a
a                                   b
b                                   c
c                                   d

user_2299760 · Answer

First, I think you can do this without using PL/SQL. The following query will represent your output in regular SQL:

WITH cur_table (val, trn_date) AS
(
  SELECT 'Z', SYSDATE FROM DUAL
),
hist_table (old_val, trn_date) AS
(
  SELECT DECODE(LEVEL, 26, NULL, CHR(90-LEVEL)), SYSDATE-LEVEL
  FROM DUAL
  CONNECT BY LEVEL <= 26
)
SELECT *
FROM (SELECT sub.val AS OLD_VAL, 
             LEAD(sub.val) OVER (ORDER BY sub.trn_date) AS NEW_VAL, 
             sub.trn_date
      FROM (SELECT c.val, c.trn_date
            FROM cur_table c
            UNION ALL
            SELECT h.old_val, h.trn_date
            FROM hist_table h) sub)
WHERE new_val IS NOT NULL;

If you do need to use PL/SQL, just call the query above and BULK COLLECT INTO v_tab, you'll need to expand your record to take the third value. You don't even need a CURSOR. Then we can loop over the results:

dbms_output.put_line('Old Value'||CHR(9)||'New Value');
IF v_tab.count > 0 THEN
  FOR i IN 1..v_tab.LAST() LOOP
    dbms_output.put_line(v_tab(i).old_value||chr(9)||v_tab(i).new_value);
  END LOOP;
END IF;

This will output in the format you originally specified (CHR(9) tabs over, you may need to adjust to get the spacing right). There are other ways to loop over the collection, but I like this one. You can also use a LOOP-EXIT WHEN NULL setup or replace the 1 with v_tab.FIRST().

Compare values of single column in PL/SQL table using loop

1 Answers