oraclectas用特定字符随机填充列

4ngedf3f 于 2021-08-01 发布在 Java

关注(0)|答案(2)|浏览(234)

我目前正在使用CTA来创建和填充一个带有随机值的表。
我想更改CTA以添加一个新的列work\u days varchar2（7）。对于所有行，这个新列的所有7个字节如何随机填充“y”或“n”？
例子：
年
重写创建和清空表的过程，首先使用2个循环，然后将所有内容连接在一起。

Create table employees(
 employee_id NUMBER(6), 
 first_name VARCHAR2(20),
 last_name VARCHAR2(20),
card_num VARCHAR2(10),
work_days VARCHAR2(7)
);

insert into employees.    (employee_id, first_name, last_name, card_num,  work_days)
with loop1(employee_id) as.    (select level from
 dual connect by level <= 50)
    ,loop2(b) as (select level from dual connect by level <= 7)

SELECT employee_id, 
CASE round(dbms_random.value(1,20)) 
        WHEN 1 THEN 'Albert' 
        WHEN 2 THEN 'Tom' 
        WHEN 3 THEN 'Anna'
        WHEN 4 THEN 'Ty' 
        WHEN 5 THEN 'Andy' 
        WHEN 6 THEN 'Thomas' 
        WHEN 7 THEN 'Alan'
        WHEN 8 THEN 'Tara' 
        WHEN 9 THEN 'Cheryl' 
        WHEN 10 THEN 'Ed' 
        WHEN 11 THEN 'Steve'
        WHEN 12 THEN 'Mel' 
        WHEN 13 THEN 'Micheal' 
        WHEN 14 THEN 'Ron' 
        WHEN 15 THEN 'Donald'
        WHEN 16 THEN 'Donny' 
        WHEN 17 THEN 'Racheal' 
        WHEN 18 THEN 'Debbie' 
        WHEN 19 THEN 'Madison'
        WHEN  20 THEN 'Danny' 
     END AS first_name,

 CASE  round(dbms_random.value(1,20)) 
        WHEN 1 THEN 'Andrews' 
        WHEN 2 THEN 'Thorton' 
        WHEN 3 THEN 'Smith'
        WHEN 4 THEN 'Jones' 
        WHEN 5 THEN 'Ott' 
        WHEN 6 THEN 'Stevens' 
        WHEN 7 THEN 'Feldman'
        WHEN 8 THEN 'Stein' 
        WHEN 9 THEN 'Ross' 
        WHEN 10 THEN 'Eden' 
        WHEN 11 THEN 'Saltzman'
        WHEN 12 THEN 'Kramer'
        WHEN 13 THEN 'Monroe' 
        WHEN 14 THEN 'Hanks' 
        WHEN 15 THEN 'Dunn'
        WHEN 16 THEN 'Dunbar' 
        WHEN 17 THEN 'Rucker' 
        WHEN 18 THEN 'Silverberg' 
        WHEN 19 THEN 'Daniels'
        WHEN  20 THEN 'Kahn' 
     END AS last_name, 

 dbms_random.string('X',        dbms_random.value(5, 10))  AS card_num,

      replace(listagg(CASE round(dbms_random.value(1,2))
                       WHEN 1 THEN 'Y'
                       WHEN 2 THEN 'N'
                      END,',')               within group (order by b)
             ,',') AS work_days
  FROM  loop1
    cross join loop2
 group by employee_id;

sql oracle Random

来源：https://stackoverflow.com/questions/62681842/oracle-ctas-randomly-populating-column-with-specific-characters

2条答案

按热度按时间

e5njpo681#

我在获取非唯一值时遇到了一些困难，但这头野兽成功了：

update employees set work_days = (
  select wd from (
    select level id, substr(wd, level, 7) wd
      from (select listagg(case when dbms_random.value(0, 1) <.5 then 'Y' else 'N' end) 
                   within group (order by null) wd
              from dual connect by level <= 7*50)
      connect by level < 50 )
   where id = employees.employee_id)

D小提琴
想法很简单，下面的数字。5变成“y”，否则变成“n”。然后把这个长串连起来切成50块。
第二，也许更简单的解决办法是 merge :

merge into employees e
using (select employee_id, translate(dbms_random.string('U', 7),
                                     'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
                                     'YYYYYYYYYYYYYNNNNNNNNNNNNN') str
         from employees) r
on (e.employee_id = r.employee_id)
when matched then update set work_days = str

D小提琴

赞(0）回复(0）举报 2021-08-01

q5lcpyga2#

您可以使用一个函数将employee\u id转换为二进制值，并将其转换为ys和ns的组合。
函数（此处为原件）

create or replace function dec2yn ( n number )
return varchar2
deterministic
is
  binval varchar2( 7 ) := '' ;
  n2     pls_integer := mod( n, 128 ) ;  -- allow decimals between 0 and 127 (we only have 7 days)
begin
  if n2 = 0 then return 'NNNNNNN' ;
  else
    while ( n2 > 0 ) loop
       binval := mod( n2, 2 ) || binval;
       n2 := trunc( n2 / 2 );
    end loop;
    binval := lpad( binval, 7, '0' ) ;           -- add 0 padding (lhs)
    binval := translate( binval, '01', 'NY' ) ;  -- translate: 0 -> N and 1 -> Y
    return binval;
  end if ;
end dec2yn;
/

您可以在虚拟列中使用它（参见dbfiddle），也可以调用它并传入雇员id。
查询

select employee_id, first_name, last_name,  dec2yn( employee_id ) workdays_
from employees ;

-- result
+-----------+----------+---------+---------+
|EMPLOYEE_ID|FIRST_NAME|LAST_NAME|WORKDAYS_|
+-----------+----------+---------+---------+
|1          |Thomas    |Dunbar   |NNNNNNY  |
|2          |Micheal   |Stein    |NNNNNYN  |
|3          |Steve     |Monroe   |NNNNNYY  |
|4          |Madison   |Eden     |NNNNYNN  |
|5          |Albert    |Andrews  |NNNNYNY  |
|6          |Donald    |Kramer   |NNNNYYN  |
|7          |Andy      |Thorton  |NNNNYYY  |
|8          |Donald    |Ross     |NNNYNNN  |
|9          |Debbie    |Kramer   |NNNYNNY  |
|10         |Steve     |Stevens  |NNNYNYN  |
|11         |Micheal   |Monroe   |NNNYNYY  |
|12         |Steve     |Smith    |NNNYYNN  |
...
...
+-----------+----------+---------+---------+

赞(0）回复(0）举报 2021-08-01

我来回答

oraclectas用特定字符随机填充列

2条答案

相关问题

热门标签

最新问答