rem REPTEST.SQL
rem 覧覧覧
rem Takes as input two parameters, the first being
rem the number of employees, the second the number
rem of departments. Employees are assigned at random
rem to departments, and salaries are randomised between
rem 7500 and 10000
rem
set termout off
rem Number of employees will be passed as the first parameter,
rem number of departments as the second. We want to assign these
rem values to two SQL Plus substitution variables called NUM_EMPS
rem and NUM_DEPTS respectively
col x new_value num_emps
col y new_value num_depts
rem Select the values from DUAL to complete the assigment
select &1 x, &2 y from dual;
rem Now we erase the contents of the EMP table and DEPT table
rem in preparation for population with sample data
truncate table EMP reuse storage;
truncate table DEPT reuse storage;
rem Now we load up the EMP table just like we did in the
rem previous example, using the SRC table to generate as
rem many rows as we require
insert into EMP
select rownum,
鮮ame竹|rownum,
sysdate+rownum/100,
dbms_random.value(7500,10000),
dbms_random.value(1,&num_depts)
from SRC
where rownum <= &num_emps;
rem We do a similar exercise with the DEPT table to load
rem the required number of department rows
insert into DEPT
select rownum, 船ept竹|rownum
from sys.source$
where rownum <= &num_depts;
rem And for good measure, we will calculate optimizer
rem statistics on the two tables, because no-one should
rem be using the rule based optimizer anymore
analyze table emp compute statistics;
analyze table dept compute statistics;
rem We truncate our results table in preparation for the test
truncate table EMP_SAL_LOG;
rem And now, here is the real testing portion of the script
rem We will run the procedure 3 times, and report an average
rem execution time across the 3 executions. To do this, we
rem use the DBMS_UTILITY.GET_TIME function to give start and
rem end timings for the test � the delta of these two times
rem is the total time elapsed (in centiseconds). Dividing
rem by 100 and then by 3, gives us the average execution
rem time in seconds.
set serverout on
set termout on
declare
x number := dbms_utility.get_time;
begin
for i in 1 .. 3 loop
report_sal_adjustment;
end loop;
dbms_output.put_line(羨verage run time: 掃|
round((dbms_utility.get_time-x)/3/100,2));
end;
/
精通Oracle PL/SQL--随书源代码
5星 · 超过95%的资源 需积分: 33 98 浏览量
2012-08-21
16:28:09
上传
评论
收藏 101KB ZIP 举报 
精通Oracle PL/SQL--随书源代码 （221个子文件） 
debug.pkh 2KB debug.sql 12KB alert_file_pkg_body.sql 6KB proactive_db_pkg_body.sql 4KB notification_pkg_body.sql 4KB ch9-18.sql 3KB ch9-17.sql 3KB ch9-20.sql 2KB Db_history_pkg_body.sql 2KB page443.sql 2KB ch9-19.sql 2KB debug_ddl.sql 2KB sql17.sql 2KB sql02.sql 2KB sql15.sql 2KB sql09.sql 1KB page434.sql 1KB ch9-16.sql 1KB page444.sql 1KB sql05.sql 982B sql22.sql 974B sql26.sql 971B sql18.sql 914B alert_file_view.sql 913B page447.sql 893B notification_pkg_spec.sql 878B ch9-3.sql 869B ch9-13.sql 804B ch9-15.sql 803B sql03.sql 795B sql14.sql 794B sql07.sql 787B ch9-10.sql 765B proactive_db_pkg_spec.sql 742B page427.sql 730B page421.sql 723B page442.sql 713B page455.sql 711B ch9-5.sql 710B sql25.sql 683B ch9-12.sql 621B sql12.sql 605B Db_history_pkg_spec.sql 579B sql19.sql 562B create_alert_file_external_table.sql 559B page417.sql 549B ch9-9.sql 527B page450.sql 515B pgae454.sql 494B ch9-11.sql 478B alert_file_pkg_spec.sql 456B page419.sql 451B page423.sql 434B page449.sql 433B sql10.sql 429B sql20.sql 418B page452.sql 417B page451.sql 399B create_notifications_alerts_table.sql 377B sql23.sql 363B page418.sql 359B sql01.sql 346B ch9-1.sql 338B page432.sql 329B page428.sql 315B page441.sql 310B page426.sql 306B sql13.sql 301B sql11.sql 298B sql16.sql 297B ch9-6.sql 287B sql04.sql 277B my_proc_wrap_demo.sql 269B sql06.sql 261B page436.sql 215B ch9-8.sql 195B ch9-14.sql 176B sql24.sql 148B sql21.sql 146B ch9-2.sql 134B sql08.sql 128B page424.sql 119B ch9-7.sql 101B ch9-4.sql 89B
ch1_22_reptest.txt 2KB Ch04_runstats.txt 2KB ch5_17.txt 2KB ch5_26.txt 2KB ch5_20.txt 2KB ch5_08.txt 2KB ch5_04.txt 2KB ch5_05.txt 1KB ch1_24.txt 1KB ch2_02.txt 1KB ch5_06.txt 1KB ch1_33.txt 1KB ch2_16.txt 1KB ch1_21.txt 1KB ch5_07.txt 1KB ch5_12.txt 1KB共 221 条
- 1
- 2
- 3
CruiseYoung
- 粉丝: 156
- 资源: 104
- 1
- 2
- 3
前往页