当前位置：文江博客话题详情

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

发布于 2024-09-17 20:25:24 字数 135 浏览 8 评论 0原文

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

我怎样才能实现这个目标？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

叹梦 2024-09-24 20:25:24

“我只想指定数量
类型为 NUMBER 的列，数量
类型为 VARCHAR2 等的列以及
应该生成字段
自动。”

做的。请注意，它相当基本；您可能希望使其更复杂，例如通过改变 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 列将导致表占用比实际使用情况更多的存储空间。

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

"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."

The following procedure does just that. Note that it is rather basic; you might want to make it more sophisticated, for example by varying the length of the varchar2 columns:

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>

Here it is in action:

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>

We can also use dynamic SQL to populate the table with rows of random data.

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>

Note that the primary key is populated with the ROWNUM so it will most likely fail if the table already contains rows.

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>

Again, there are all sorts of ways to improve the sophistication of the data.

As an aside, using these sorts of data pools for load testing is not always a good idea. Performance problems are often caused by skews in the distribution of data values which you just aren't going to get with DBMS_RANDOM. This is particularly true of some date columns - e.g. START_DATE - which would tend to be clustered together in real life but the above procedure will not generate that pattern. Similarly maxing out the varchar2 columns will lead to tables which take up more storage than they wlll under real-life usage.

In short, randomly generated data is better than nothing but we need to understand its weaknesses.

回复收藏 0 原文

假情假意假温柔 2024-09-24 20:25:24

两种方法

1) 编写代码来生成包含运行和填充表所需的 CREATE TABLE 命令的文本文件，然后使用 SQL*Plus 执行这些命令。

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;

您还可以使用“CREATE TABLE AS SELECT”同时填充表（请参阅其他答案）。

这应该给你一个很好的起点——系统中没有任何东西可以在不编写代码的情况下完成你想要的事情。

Two approaches

1) Write code to generate text files containing the CREATE TABLE commands that you need to run and populate your tables, then execute these using SQL*Plus.

2) Use Dynamic SQL embedded inside PL/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;

You could also use 'CREATE TABLE AS SELECT' to populate your table at the same time (see other answer).

This should give you a good starting point - there isn't anything in the system that will do what you want without writing code.

回复收藏 0 原文

梦情居士 2024-09-24 20:25:24

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

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

  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

You may generate such table by yourself.

Create table with required datatypes:

  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

(You can change 1000 to required rows numbers, and add required data types)

After that you can create or populate tables with random data from 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

回复收藏 0 原文

~没有更多了~