3,490
社区成员
发帖
与我相关
我的任务
分享
--为虾米我的第三种执行了这么长时间呢?
SQL> DECLARE
2 v_begintime date;
3 v_current date;
4
5 v_begin_loop_time date;
6 v_end_loop_time date;
7
8 BEGIN
9 v_begin_loop_time := sysdate;
10 FOR i IN 1..1000000 LOOP
11 select trunc(sysdate,'MM'), trunc(sysdate-1) into v_begintime, v_current from dual;
12 END LOOP;
13 v_end_loop_time := sysdate;
14
15 dbms_output.put_line('v_begin_loop_time: '||to_char(v_begin_loop_time,'YYYY-MM-DD HH24:MI:SS'
)||' v_end_loop_time: '||to_char(v_end_loop_time,'YYYY-MM-DD HH24:MI:SS'));
16
17 END;
18 /
v_begin_loop_time: 2010-12-09 18:17:22 v_end_loop_time: 2010-12-09 18:18:09
PL/SQLプロシージャが正常に完了しました。
-- 测试:
----------------------------1 : 验证是否相等:------------------------------------
PL/SQL block, executed in 0.015 sec.
v_begintime_a = v_begintime_b
v_begintime_a: 2010-12-01 00:00:00 v_begintime_b: 2010-12-01 00:00:00
v_current_a = v_current_b
v_current_a: 2010-12-08 00:00:00 v_current_b: 2010-12-08 00:00:00
Total execution time 0.031 sec.
----------------------------2 : 效率检测:------------------------------------
-- 方法一:
PL/SQL block, executed in 01:3.391 (63.391 sec.)
v_begin_loop_time: 2010-12-09 15:14:39 v_end_loop_time: 2010-12-09 15:15:43
Total execution time 01:3.407 (63.407 sec.)
-- 方法二:
PL/SQL block, executed in 36.266 sec.
v_begin_loop_time: 2010-12-09 15:13:28 v_end_loop_time: 2010-12-09 15:14:05
Total execution time 36.281 sec.
-- 方法三:
PL/SQL block, executed in 27.094 sec.
v_begin_loop_time: 2010-12-09 15:12:13 v_end_loop_time: 2010-12-09 15:12:40
Total execution time 27.125 sec.
-- 方法四:
PL/SQL block, executed in 36.859 sec.
v_begin_loop_time: 2010-12-09 15:10:55 v_end_loop_time: 2010-12-09 15:11:31
Total execution time 36.907 sec.
----------------------------------
-- 方法五:
PL/SQL block, executed in 28.376 sec.
v_begintime: 2010-12-01 00:00:00 v_current: 2010-12-08 00:00:00
v_begin_loop_time: 2010-12-09 15:09:43 v_end_loop_time: 2010-12-09 15:10:11
Total execution time 28.391 sec.
--结论:
方法三最优!方法一最差!
---最优
--已用时间: 00: 00: 21.62 ---第三种
DECLARE
v_begintime date;
v_current date;
v_begin_loop_time date;
v_end_loop_time date;
BEGIN
v_begin_loop_time := sysdate;
FOR i IN 1..1000000 LOOP
select trunc(sysdate,'MM'), trunc(sysdate-1) into v_begintime, v_current from dual;
END LOOP;
v_end_loop_time := sysdate;
dbms_output.put_line('v_begin_loop_time: '||to_char(v_begin_loop_time,'YYYY-MM-DD HH24:MI:SS')||' v_end_loop_time: '||to_char(v_end_loop_time,'YYYY-MM-DD HH24:MI:SS'));
END;