代码之家 › 专栏 › 技术社区 › Chandam

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

code-generation plsql random oracle

Chandam · 技术社区 · 14 年前

我怎样才能做到这一点?

3 回复 | 直到 12 年前

APC 14 年前

“我只想指定 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\u RANDOM无法得到的。这对于某些日期列尤其如此,例如START\u date,它们在现实生活中往往聚集在一起,但上面的过程不会生成这种模式。类似地,最大化varchar2列将导致表占用比实际使用中更多的存储空间。

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

JulesLt 14 年前

两种方法

2) 使用嵌入在PL/SQL中的动态SQL-

 PROCEDURE create_random_table 
      (pTableName IN VARCHAR2,
       pNumberOfColumns IN INTEGER)
 IS  
     PRAGMA AUTONOMOUS_TRANSACTION;
     lCommand VARCHAR2(32000);
 BEGIN
     lCommand := 
     'CREATE TABLE '||pTableName||'(';
     FOR i IN 1..pNumberOfColumns LOOP
        append your column definition here
     END LOOP;
     lCommand := lCommand||';';
     --
     EXECUTE IMMEDIATE lCommand;
 END;

这应该给你一个很好的起点——系统中没有任何东西不写代码就可以做你想做的事情。

Michael Pakhantsov 14 年前

您可以自己生成这样的表。

创建具有所需数据类型的表:

  Create Table RandomTable
  AS
  Select dbms_random.string('A', 1) CharField,
         dbms_random.string('a', 20) VarCharField,
         TRUNC(dbms_random.value(0, 35000)) IntField,
         dbms_random.value(0, 35000) NumberField,
         Level SequenceField,
         sysdate + dbms_random.value(-3500, 3500) DateField
  from dual connect by level < 1000

(您可以将1000更改为必需的行号,并添加必需的数据类型)

之后,您可以使用RandomTable中的随机数据创建或填充表

  Create Table TestTable1
  AS
  SELECT CharField as a, NumberField as b
  from RandomTable

  INSERT INTO TestTable2(DateFrom, Quantity)
  SELECT DateField, IntField 
  from RandomTable
  Where SequenceField <= 500