加入收藏 | 设为首页 | 会员中心 | 我要投稿 上饶站长网 (https://www.0793zz.com.cn/)- 数据库平台、视觉智能、智能搜索、决策智能、迁移!
当前位置: 首页 > 站长百科 > 正文

在oracle 11g中生成样本数据

发布时间:2021-01-17 18:06:20 所属栏目:站长百科 来源:网络整理
导读:我有以下程序的问题,它根本不选择随机的街道和城镇.问题在于 streets(SYS.DBMS_RANDOM.VALUE(1,50)) 和 towns(SYS.DBMS_RANDOM.VALUE(1,50)) 看起来random.value只评估一次,我怎样才能让它选择varray的随机元素? CREATE OR REPLACE PROCEDURE GENERATE_ADDR

我有以下程序的问题,它根本不选择随机的街道和城镇.问题在于

streets(SYS.DBMS_RANDOM.VALUE(1,50))

towns(SYS.DBMS_RANDOM.VALUE(1,50))

看起来random.value只评估一次,我怎样才能让它选择varray的随机元素?

CREATE OR REPLACE PROCEDURE GENERATE_ADDRESSES AS
    type streetType is varray(50) of addresses.street%TYPE;
    streets streetType:= streetType('Rebel Loop East','Everleigh Rise','Double Oak,Pig Mount West','Byam Byway','South Damour Trail','Chart Hill Northwest','West Down Turnpike','Southeast Lawyers Hill Mount','East Jibbon Road','Old Browns Valley Viaduct','Queensbury Viaduct','Northeast Hards Plaza','Northwest Cushing Promenade','North Queens Wood','South Oakton Plantation','East Redeker Terrace','Stanbaugh Mount','Huse Nook','East Savine Grade','Bardo Manor','West Mina Rosa Place','West Oldarker Mall','West Oakgrove Lane','Woodleigh Row','Southwest Stoney Ridge Passage','Cucumber Mews','Stoffa Trace North','West Echo Bay Alley','North Monkhams Terrace','Weller Grove West','Estate Walk','Doneraile Rise','North Yunga Burra Manor','Boundaries Square','Windsor Hill Row West','South Silver Maple Close','West Back Westminster','East Bibsworth Causeway','Widdop Dell','Sawyer Hill','East Minehurst Street','East Ecclesbridge Close','North Clouston Court','Southwest Towradgi Alley','Northeast Barkdoll Promenade','Southwest Icklingham Quay','North Fanum Quadrant','Nerbonne Croft','West Montee Alley','East Burra Street');

    type townType is varray(50) of addresses.town%TYPE;
    towns townType:= townType('Linland','Havenmoor','Fallbank','Marshbush','Whitenesse','Crystalfort','Dorhaven','Spellhall','Northbell','Westermerrow','Butterbeach','Fairbarrow','Violetbush','Westbeach','Landness','Rosemaple','Lochbush','Coastfield','Westmarsh','Golddale','Violetford','Elfacre','Brightmill','Bypine','Starryfox','Barrowmeadow','Ashbridge','Swynpond','Eribourne','Wintermill','Bridgebeach','Roselyn','Summerwinter','Fairviolet','Ashvale','Dordale','Osthaven','Deephaven','Whiteflower','Welledge','Snowbeach','Marblenesse','Witchnesse','Bluewell','Shorelake','Coldfalcon','Strongbush','','Freyholt');

    TYPE Addresses_type IS TABLE OF addresses%ROWTYPE INDEX BY BINARY_INTEGER;
    adb Addresses_type;
  BEGIN
    --fill streets and towns varrays
    insert into addresses(id,street,streetNo,town,countries_id)
    select rownum,streets(SYS.DBMS_RANDOM.VALUE(1,50)),floor(SYS.DBMS_RANDOM.VALUE(1,10000)) || '/' || floor(SYS.DBMS_RANDOM.VALUE(1,1000)),towns(SYS.DBMS_RANDOM.VALUE(1,SYS.DBMS_RANDOM.VALUE(1,500)
    from dual
    connect by level <= 500;
  END GENERATE_ADDRESSES;
/

解决方法

城镇()和街道()是建设者,但它们只是功能.我不确切地知道为什么会出现这种情况(也许它与通过双内部工作的connecty有关),但即使 docs警告你也不能确定在SQL语句中调用函数的次数:

Invoking Stored PL/SQL Functions from SQL Statements

Because SQL is a declarative language,rather than an imperative (or
procedural) one,you cannot know how many times a function invoked by
a SQL statement will run—even if the function is written in PL/SQL,an
imperative language. If your application requires that a function be
executed a certain number of times,do not invoke that function from a
SQL statement. Use a cursor instead.

For example,if your application requires that a function be called
for each selected row,then open a cursor,select rows from the
cursor,and call the function for each row. This technique guarantees
that the number of calls to the function is the number of rows fetched
from the cursor.

因此,解决方法是为每行调用dbms_random包多次:

for k in (select level as lev from dual connect by level <= 500)
loop
 insert into addresses(
   id,countries_id
   )
values 
(
  k.lev,500)
);
end loop;

稍后编辑:

这与dbms_random包无关.例如:

declare 
type townType is varray(50) of varchar2(1000);
    towns townType:= townType('Linland','Freyholt');
v  varchar2(2000);

begin

   dbms_output.put_line('start!');

   select listagg(towns(level+1),',') within group (order by 1) 
   into v 
   from dual
   connect by level < 5;

   dbms_output.put_line(v);

end loop;
end;
/

输出:

Linland,Linland,Linland

(编辑:上饶站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    热点阅读