10 Replies Latest reply: Aug 30, 2012 5:17 PM by rp0428 RSS

    how to compare two rows in PL/SQL?

    672680
      Hi All,

      How to compare two rows in PL/SQL? Is there any method that I can use instead of comparing them column by column?

      Any feedback would be highly appreciated.
        • 1. Re: how to compare two rows in PL/SQL?
          Toon Koppelaars
          I'm afraid not no. Even if your record only holds one field it doesn't work:
            1  declare
            2  type v1 is record(i number);
            3  r1 v1;
            4  r2 v1;
            5  begin
            6    if r1 = r2 then null; end if;
            7* end;
          SQL> /
            if r1 = r2 then null; end if;
                  *
          ERROR at line 6:
          ORA-06550: line 6, column 9:
          PLS-00306: wrong number or types of arguments in call to '='
            1  declare
            2  type v1 is record(i number);
            3  r1 v1;
            4  r2 v1;
            5  begin
            6    if r1.i = r2.i then null; end if;
            7* end;
          SQL> /
          
          PL/SQL procedure successfully completed.
          • 2. Re: how to compare two rows in PL/SQL?
            548849
            You can use minus operator
            • 3. Re: how to compare two rows in PL/SQL?
              BluShadow
              PhoenixBai wrote:
              Hi All,

              How to compare two rows in PL/SQL? Is there any method that I can use instead of comparing them column by column?
              What "rows" are you referring to?

              If you're talking of rows within a PL/SQL associative array there are techniques as described in the documentation... e.g.
              SQL> ed
              Wrote file afiedt.buf
              
                1  declare
                2    type v1 is table of number;
                3    r1 v1 := v1(1,2,4);
                4    r2 v1 := v1(1,2,3);
                5  begin
                6    if r1 MULTISET EXCEPT DISTINCT r2 = v1() then
                7      dbms_output.put_line('Same');
                8    else
                9      dbms_output.put_line('Different');
               10    end if;
               11* end;
              SQL> /
              Different
              
              PL/SQL procedure successfully completed.
              
              SQL> ed
              Wrote file afiedt.buf
              
                1  declare
                2    type v1 is table of number;
                3    r1 v1 := v1(1,2,3);
                4    r2 v1 := v1(1,2,3);
                5  begin
                6    if r1 MULTISET EXCEPT DISTINCT r2 = v1() then
                7      dbms_output.put_line('Same');
                8    else
                9      dbms_output.put_line('Different');
               10    end if;
               11* end;
              SQL> /
              Same
              
              PL/SQL procedure successfully completed.
              
              SQL>
              If you're talking about rows on a table then you can use the MINUS set operator to find the rows that differ between two sets of data...
              SQL> select * from emp;
              
                   EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
              ---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
                    7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                    20
                    7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300         30
                    7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500         30
                    7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                    20
                    7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400         30
                    7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                    30
                    7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                    10
                    7788 SCOTT      ANALYST         7566 19-APR-1987 00:00:00       3000                    20
                    7839 KING       PRESIDENT            17-NOV-1981 00:00:00       5000                    10
                    7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0         30
                    7876 ADAMS      CLERK           7788 23-MAY-1987 00:00:00       1100                    20
                    7900 JAMES      CLERK           7698 03-DEC-1981 00:00:00        950                    30
                    7902 FORD       ANALYST         7566 03-DEC-1981 00:00:00       3000                    20
                    7934 MILLER     CLERK           7782 23-JAN-1982 00:00:00       1300                    10
              
              14 rows selected.
              
              SQL> select * from emp2;
              
                   EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
              ---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
                    7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500         30
                    7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                    20
                    7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                    10
                    7788 SCOTT      ANALYST         7566 19-APR-1987 00:00:00       3000                    20
                    7839 KING       PRESIDENT            17-NOV-1981 00:00:00       5000                    10
                    7900 JAMES      CLERK           7698 03-DEC-1981 00:00:00        950                    30
                    7934 MILLER     CLERK           7782 23-JAN-1982 00:00:00       1300                    10
              
              7 rows selected.
              
              SQL> select * from emp
                2  minus
                3  select * from emp2;
              
                   EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
              ---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
                    7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                    20
                    7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300         30
                    7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400         30
                    7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                    30
                    7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0         30
                    7876 ADAMS      CLERK           7788 23-MAY-1987 00:00:00       1100                    20
                    7902 FORD       ANALYST         7566 03-DEC-1981 00:00:00       3000                    20
              
              7 rows selected.
              If you actually need to know what columns data is different on "non-matching" rows (based on your primary key) then you'll have to compare column by column.
              • 4. Re: how to compare two rows in PL/SQL?
                672680
                I just need to compare two rows and see if they are different. I do not need to know in which columns they are different, I only need the result as true or false.

                I am actually seeking for the possible built-in procedure or function to compare two rows like r1.compare(r2) does in Java.
                I guess there is no such proc/func at all;-)

                Thank you very much for your feedback BluShadow!
                • 5. Re: how to compare two rows in PL/SQL?
                  BluShadow
                  You still haven't explained what you mean by "rows". I can only assume you mean rows on a database table.

                  As such, you should do the comparison using SQL. Whether that SQL is embedded in PL/SQL is irrelevant.

                  e.g.
                  SQL> ed
                  Wrote file afiedt.buf
                  
                    1  with old_data as (select 1 as id, 'A' as dta from dual union all
                    2                     select 2, 'B' from dual union all
                    3                     select 3, 'C' from dual)
                    4      ,new_data as (select 1 as id, 'A' as dta from dual union all
                    5                    select 3, 'X' from dual union all
                    6                    select 4, 'Y' from dual)
                    7  --
                    8      ,ins_upd as (select * from new_data minus select * from old_data)
                    9      ,del_upd as (select * from old_data minus select * from new_data)
                   10      ,upd as (select id from ins_upd intersect select id from del_upd)
                   11      ,ins as (select id from ins_upd minus select id from upd)
                   12      ,del as (select id from del_upd minus select id from upd)
                   13  --
                   14  select 'Inserted' as action, null as old_id, null as old_dta, new_data.id as new_id, new_data.dta as new_dta
                   15  from new_data join ins on (ins.id = new_data.id)
                   16  union all
                   17  select 'Updated', old_data.id, old_data.dta, new_data.id, new_data.dta
                   18  from old_data join new_data on (old_data.id = new_data.id)
                   19                join upd on (upd.id = new_data.id)
                   20  union all
                   21  select 'Deleted', old_data.id as old_id, old_data.dta as old_dta, null as new_id, null as new_dta
                   22* from old_data join del on (del.id = old_data.id)
                  SQL> /
                  
                  ACTION       OLD_ID O     NEW_ID N
                  -------- ---------- - ---------- -
                  Inserted                       4 Y
                  Updated           3 C          3 X
                  Deleted           2 B
                  
                  SQL>
                  • 6. Re: how to compare two rows in PL/SQL?
                    672680
                    By rows I mean, two row from the same table!
                    and I need to compare column by column to see if there is any difference between these two rows.

                    Can I do like, as you suggested in my another post:
                    select .....
                           MINUS
                    select ......
                    My only concern is the result from this sql. Is it reliable? Can it really be used to differentiate two rows? I mean, its result would be the same as I do it by Java?

                    Just need a confirmation!
                    • 7. Re: how to compare two rows in PL/SQL?
                      BluShadow
                      PhoenixBai wrote:
                      By rows I mean, two row from the same table!
                      Ah, finally, we get a little more useful information
                      and I need to compare column by column to see if there is any difference between these two rows.
                      Like this you mean?
                      SQL> ed
                      Wrote file afiedt.buf
                      
                        1  with data as (select 1 as id, 'A' as dta, 'B' as dta2 from dual union all
                        2                select 2, 'A', 'B' from dual union all
                        3                select 3, 'B', 'C' from dual)
                        4  --
                        5      ,chk as (select &id1 as id1, &id2 as id2 from dual)
                        6  --
                        7  select case when (
                        8    select count(*)
                        9    from (
                       10      select dta, dta2
                       11      from data, chk
                       12      where id = id1
                       13      minus
                       14      select dta, dta2
                       15      from data, chk
                       16      where id = id2
                       17      )
                       18    ) = 0 then 'No Difference'
                       19          else 'Difference'
                       20          end as check_result
                       21* from dual
                      SQL> /
                      Enter value for id1: 1
                      Enter value for id2: 2
                      old   5:     ,chk as (select &id1 as id1, &id2 as id2 from dual)
                      new   5:     ,chk as (select 1 as id1, 2 as id2 from dual)
                      
                      CHECK_RESULT
                      -------------
                      No Difference
                      
                      SQL> /
                      Enter value for id1: 1
                      Enter value for id2: 3
                      old   5:     ,chk as (select &id1 as id1, &id2 as id2 from dual)
                      new   5:     ,chk as (select 1 as id1, 3 as id2 from dual)
                      
                      CHECK_RESULT
                      -------------
                      Difference
                      
                      SQL>
                      My only concern is the result from this sql. Is it reliable? Can it really be used to differentiate two rows? I mean, its result would be the same as I do it by Java?
                      Not sure what you mean by "reliable"?. SQL is not some sort of random result generator (notwithstanding the use of the DBMS_RANDOM package). If you ask it if 1+1=2 then it will give always give the correct answer.
                      • 8. Re: how to compare two rows in PL/SQL?
                        672680
                        Thank you very much for your inputs. It helps:-)
                        • 9. Re: how to compare two rows in PL/SQL?
                          849621
                          I know this is an old post but possibly this solution represent a very simple was to do this:

                          declare
                          l_result_count number(10,0);
                          begin

                          select
                          distinct count(1)
                          into
                          l_result_count
                          from
                          <your_table>
                          where
                          your_table.id in (1,2)
                          ;

                          if(l_result_count > 1) then

                          .........
                          .........

                          end if;

                          end;

                          If you get two rows you know there is something different in the rows.
                          • 10. Re: how to compare two rows in PL/SQL?
                            rp0428
                            >
                            I know this is an old post but possibly this solution represent a very simple was to do this
                            >
                            Yes it is - three years old. Please let it rest in peace. The question has already been marked ANSWERED and your 'very simple' solution doesn't compare 'rows' and is not scalable.

                            Good to have more people that want to contribute to the forum but your contributions will be more helpful if you focus on the current unanswered threads.