Find matching numbers-CodePudding

I'm trying to write a PLSQL query that will in two separate columns print random integers. It will print 1000 numbers total (random 1 - 50 in each row). What I need to figure out is how I after this has been done, replace the second column with either "yes" or "no" if it matches the first column Such as:

Col A    Col B
 10      NO(42)
 32      NO(12)
 25      YES(25)

And so on. This is my code:

CREATE TABLE table
      
   (random_num INTEGER NOT NULL,
    match INTEGER NOT NULL);

Declare

CURSOR cur_ IS 
(Select 

random_num,
match 
from table);

Begin 
  
FOR rec_ IN 1..1000 
    
  LOOP    
 
 INSERT INTO "table" (random_num,match) VALUES (DBMS_RANDOM.VALUE(1,50),DBMS_RANDOM.VALUE(1,50));
   
  END LOOP;
  
  END;

Now this works as I get two 1000 rows of each column with random numbers, but I need to implement this select:

SELECT random_num, CASE WHEN random_num = match THEN 'yes' ELSE 'no' END as match
FROM table

Into the loop so. Any takers on how I can do?

CodePudding user response：

There's something wrong in what you said. You can't put yes (string) into an INTEGER datatype column.

This makes more sense:

Sample table:

SQL> CREATE TABLE test
  2  (
  3     random_num_1   INTEGER NOT NULL,
  4     random_num_2   INTEGER NOT NULL,
  5     match          VARCHAR2 (3) NOT NULL
  6  );

Table created.

Procedure: use local variables to store random numbers; then it is easy to compare them.

SQL> DECLARE
  2     val1  NUMBER;
  3     val2  NUMBER;
  4  BEGIN
  5     FOR i IN 1 .. 10 --> change it to 1000
  6     LOOP
  7        val1 := DBMS_RANDOM.VALUE (1, 50);
  8        val2 := DBMS_RANDOM.VALUE (1, 50);
  9
 10        INSERT INTO test (random_num_1, random_num_2, match)
 11                VALUES (val1,
 12                        val2,
 13                        CASE WHEN val1 = val2 THEN 'yes' ELSE 'no' END);
 14     END LOOP;
 15  END;
 16  /

PL/SQL procedure successfully completed.

Result:

SQL> SELECT * FROM test;

RANDOM_NUM_1 RANDOM_NUM_2 MAT
------------ ------------ ---
          45           31 no
          40           48 no
          43           27 no
          49           41 no
           6           38 no
           5           18 no
          18           35 no
          15           34 no
          11           19 no
          37           39 no

10 rows selected.

SQL>

CodePudding user response：

First if you want to produce random integers from 1 to 50 that are equaly distributed, you must be carefull.

[DBMS_RANDOM.VALUE (1, 50)][1] returns a decimal number greater than or equal than 1 and less than 50.

example

select DBMS_RANDOM.VALUE (1, 50) col from dual;
       COL
----------
30,4901593

You cast the result in INTEGER type that performs rounding, so you will see all numbers, but the 1 and 50 will appear only half frequenty as other numbers.

So a better way to get random integers 1 .. 50 is 1 trunc(50*DBMS_RANDOM.VALUE)

VALUE without parameters returns [0,1)

Also typically if you do not need to use PL/SQL do not use it

create table tab1 as
select 1   trunc(50*DBMS_RANDOM.VALUE) col1, 1   trunc(50*DBMS_RANDOM.VALUE) col2
from dual connect by level <= 10 /* increase as much rows are needed */

and add the MATCH column as virtual

alter table tab1 
add (match varchar2(3) generated always as (
  case when col1 = col2 then 'YES' else 'NO' end ) virtual);

      COL1       COL2 MAT
---------- ---------- ---
        33          6 NO 
        26         28 NO 
        35         22 NO 
        30         27 NO 
        17         45 NO 
        31          4 NO 
        11         21 NO 
         2         48 NO 
        35         25 NO 
        39         15 NO