oracle - 如何使用 PL/SQL 在 Oracle 中创建具有随机字段数的表？

Question

我需要创建一个包含随机列数的 Oracle 表以进行负载测试。我只想指定类型为 NUMBER 的列数、类型为 VARCHAR2 的列数等，并且字段应该自动生成。此外，我将用随机数据填充表格，我将使用 dbms_random。

我怎样才能做到这一点？

Answer 1

"I just want to specify number of columns with type NUMBER, number of columns with type VARCHAR2 etc and the fields should be generated automatically."

下面的过程就是这样做的。请注意，它是相当基础的；您可能想让它更复杂，例如通过改变 varchar2 列的长度:

SQL> create or replace procedure bld_table
  2      ( p_tab_name in varchar2
  3        , no_of_num_cols in pls_integer
  4        , no_of_var_cols in pls_integer
  5        , no_of_date_cols in pls_integer
  6      )
  7  as
  8  begin
  9      execute immediate 'create table '||p_tab_name||' ('
 10                        ||' pk_col number not null'
 11                        ||', constraint '||p_tab_name||'_pk primary key (pk_col) using index)';
 12      << numcols >>
 13      for i in 1..no_of_num_cols loop
 14          execute immediate 'alter table '||p_tab_name||' add '
 15                            ||' col_n'||trim(to_char(i))||' number';
 16      end loop numcols;
 17      << varcols >>
 18      for i in 1..no_of_var_cols loop
 19          execute immediate 'alter table '||p_tab_name||' add '
 20                            ||' col_v'||trim(to_char(i))||' varchar2(30)';
 21      end loop varcols;
 22      << datcols >>
 23      for i in 1..no_of_date_cols loop
 24          execute immediate 'alter table '||p_tab_name||' add '
 25                            ||' col_d'||trim(to_char(i))||' date';
 26      end loop datcols;
 27  end bld_table;
 28  /

Procedure created.

SQL>

这是在行动:

SQL> exec bld_table ('T23', 2, 3, 0)

PL/SQL procedure successfully completed.

SQL> desc t23
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PK_COL                                    NOT NULL NUMBER
 COL_N1                                             NUMBER
 COL_N2                                             NUMBER
 COL_V1                                             VARCHAR2(30 CHAR)
 COL_V2                                             VARCHAR2(30 CHAR)
 COL_V3                                             VARCHAR2(30 CHAR)

SQL>

我们还可以使用动态 SQL 用随机数据行填充表。

SQL> create or replace procedure pop_table
  2          ( p_tab_name in varchar2
  3        , p_no_of_rows in pls_integer
  4      )
  5  as
  6   stmt varchar2(32767);
  7  begin
  8   stmt := 'insert into '||p_tab_name
  9                || ' select rownum ';
 10        for r in ( select column_name
 11                          , data_type
 12                          , data_length
 13                   from user_tab_columns
 14                   where table_name = p_tab_name
 15                  and column_name != 'PK_COL' )
 16        loop
 17            case r.data_type
 18           when 'VARCHAR2' then
 19               stmt := stmt ||', dbms_random.string(''a'', '||r.data_length||')';
 20           when 'NUMBER' then
 21               stmt := stmt ||', dbms_random.value(0, 1000)';
 22           when 'DATE' then
 23               stmt := stmt ||', sysdate + dbms_random.value(-1000, 0)';
 24            end case;
 25        end loop;
 26        stmt := stmt || ' from dual connect by level <= '||p_no_of_rows;
 27        execute immediate stmt;
 28  end pop_table;
 29  /

Procedure created.

SQL>

请注意，主键是用 ROWNUM 填充的，因此如果表已经包含行，它很可能会失败。

SQL> exec pop_table('T23', 4)

PL/SQL procedure successfully completed.

SQL> select * from t23
  2  /

    PK_COL     COL_N1     COL_N2 COL_V1                         COL_V2                         COL_V3
---------- ---------- ---------- ------------------------------ ----------------------------- ------------------------------
         1 913.797432 934.265814 NUtxjLoRQMCTLNMPKVGbTZwJeYaqnXTkCcWu WFRSHjXdLfpgVYOjzrGrtUoX jIBSoYOhSdhRFeEeFlpAxoanPabvwK
         2 346.879815 104.800387 NTkvIlKeJWybCTNEdvsqJOKyidNkjgngwRNN PPIOInbzInrsVTmFYcDvwygr RyKFoMoSiWTmjTqRBCqDxApIIrctPu
         3 93.1220275 649.335267 NTUxzPRrKKfFncWaeuzuyWzapmzEGtAwpnjj jHILMWJlcMjnlboOQEIDFTBG JRozyOpWkfmrQJfbiiNaOnSXxIzuHk
         4 806.709357 857.489387 ZwLLkyINrVeCkUpznVdTHTdHZnuFzfPJbxCB HnoaErdzIHXlddOPETzzkFQk dXWTTgDsIeasNHSPbAsDRIUEyPILDT

4 rows selected.

SQL>

同样，有各种方法可以提高数据的复杂性。

顺便说一句，使用这些类型的数据池进行负载测试并不总是一个好主意。性能问题通常是由数据值分布的偏斜引起的，而 DBMS_RANDOM 不会得到这些偏斜。对于某些日期列尤其如此 - 例如START_DATE - 在现实生活中往往会聚集在一起，但上述过程不会生成该模式。同样，最大化 varchar2 列将导致表占用的存储空间超过实际使用情况下的存储空间。

简而言之，随机生成的数据总比没有好，但我们需要了解它的弱点。