在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; / 解决方法城镇()和街道()是建设者,但它们只是功能.我不确切地知道为什么会出现这种情况(也许它与通过双内部工作的connecty有关),但即使 docs警告你也不能确定在SQL语句中调用函数的次数:Invoking Stored PL/SQL Functions from SQL Statements
因此,解决方法是为每行调用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 (编辑:上饶站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |