在oracle 11g中生成样本数据

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

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;
/

解决方法

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

（编辑：济南站长网）

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