-- 看到人家写的一个存储过程,只看两行,就看不下去啦......
CREATE OR REPLACE PROCEDURE "MUSIC"."LOGIN_STAT"
is
v_sum number(18);
v_shu number(18);
v_begintime date;
v_current date;
str_sql varchar2(2000);
begin
str_sql:='alter session set nls_date_format = ''yyyy-mm-dd''';
execute immediate str_sql;
select to_char(sysdate,'yyyy')||'-'||to_char(sysdate,'mm')||'-'||'01' into v_begintime from dual;
select to_char(sysdate-1,'yyyy-mm-dd') into v_current from dual;
...end;
/-- 测试:
----------------------------1 : 验证是否相等:------------------------------------
ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd';DECLARE
v_begintime_a date;
v_current_a date; v_begintime_b date;
v_current_b date;
BEGIN
SELECT trunc(sysdate,'MM') INTO v_begintime_a FROM DUAL;
SELECT trunc(sysdate-1) INTO v_current_a FROM DUAL; select to_char(sysdate,'yyyy')||'-'||to_char(sysdate,'mm')||'-'||'01' into v_begintime_b from dual;
select to_char(sysdate-1,'yyyy-mm-dd') into v_current_b from dual; IF v_begintime_a <> v_begintime_b THEN
dbms_output.put_line('v_begintime_a <> v_begintime_b');
dbms_output.put_line('v_begintime_a: '||to_char(v_begintime_a,'YYYY-MM-DD HH24:MI:SS')||' v_begintime_b: '||to_char(v_begintime_b,'YYYY-MM-DD HH24:MI:SS'));
ELSE
dbms_output.put_line('v_begintime_a = v_begintime_b');
dbms_output.put_line('v_begintime_a: '||to_char(v_begintime_a,'YYYY-MM-DD HH24:MI:SS')||' v_begintime_b: '||to_char(v_begintime_b,'YYYY-MM-DD HH24:MI:SS'));
END IF; IF v_current_a <> v_current_b THEN
dbms_output.put_line('v_current_a <> v_current_b');
dbms_output.put_line('v_current_a: '||to_char(v_current_a,'YYYY-MM-DD HH24:MI:SS')||' v_current_b: '||to_char(v_current_b,'YYYY-MM-DD HH24:MI:SS'));
ELSE
dbms_output.put_line('v_current_a = v_current_b');
dbms_output.put_line('v_current_a: '||to_char(v_current_a,'YYYY-MM-DD HH24:MI:SS')||' v_current_b: '||to_char(v_current_b,'YYYY-MM-DD HH24:MI:SS'));
END IF;END;
/----------------------------2 : 效率检测:-------------------------------------- 方法一:
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 to_char(sysdate,'yyyy')||'-'||to_char(sysdate,'mm')||'-'||'01' into v_begintime from dual;
select to_char(sysdate-1,'yyyy-mm-dd') into 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;
/v_begin_loop_time: 2010-12-09 09:51:56 v_end_loop_time: 2010-12-09 09:52:34PL/SQL 过程已成功完成。-- 执行一百万次循环赋值,执行时间:38秒----------------------------------
-- 方法二: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 to_char(sysdate,'yyyy')||'-'||to_char(sysdate,'mm')||'-'||'01', to_char(sysdate-1,'yyyy-mm-dd') 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;
/
v_begin_loop_time: 2010-12-09 09:53:31 v_end_loop_time: 2010-12-09 09:53:52
-- 执行一百万次循环,执行时间:21秒----------------------------------
-- 方法三: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;
/v_begin_loop_time: 2010-12-09 09:56:57 v_end_loop_time: 2010-12-09 09:57:13PL/SQL 过程已成功完成。-- 执行一百万次循环,执行时间:16秒----------------------------------
-- 方法三: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;
v_begintime := trunc(sysdate,'MM');
v_current := trunc(sysdate-1);
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;
/v_begin_loop_time: 2010-12-09 10:05:36 v_end_loop_time: 2010-12-09 10:05:58PL/SQL 过程已成功完成。-- 执行一百万次循环,执行时间:22秒----------------------------------
-- 方法四:DECLARE
v_begintime date;
v_current date; v_begin_loop_time date;
v_end_loop_time date;
v_sql VARCHAR2(500);
BEGIN
v_begin_loop_time := sysdate;
v_sql := 'select trunc(sysdate,''MM''), trunc(sysdate-1) from dual';
FOR i IN 1..1000000 LOOP
EXECUTE IMMEDIATE v_sql INTO v_begintime, v_current;
END LOOP;
v_end_loop_time := sysdate;
dbms_output.put_line('v_begintime: '||to_char(v_begintime,'YYYY-MM-DD HH24:MI:SS')||' v_current: '||to_char(v_current,'YYYY-MM-DD HH24:MI:SS'));
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;
/
CREATE OR REPLACE PROCEDURE "MUSIC"."LOGIN_STAT"
is
v_sum number(18);
v_shu number(18);
v_begintime date;
v_current date;
str_sql varchar2(2000);
begin
str_sql:='alter session set nls_date_format = ''yyyy-mm-dd''';
execute immediate str_sql;
select to_char(sysdate,'yyyy')||'-'||to_char(sysdate,'mm')||'-'||'01' into v_begintime from dual;
select to_char(sysdate-1,'yyyy-mm-dd') into v_current from dual;
...end;
/-- 测试:
----------------------------1 : 验证是否相等:------------------------------------
ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd';DECLARE
v_begintime_a date;
v_current_a date; v_begintime_b date;
v_current_b date;
BEGIN
SELECT trunc(sysdate,'MM') INTO v_begintime_a FROM DUAL;
SELECT trunc(sysdate-1) INTO v_current_a FROM DUAL; select to_char(sysdate,'yyyy')||'-'||to_char(sysdate,'mm')||'-'||'01' into v_begintime_b from dual;
select to_char(sysdate-1,'yyyy-mm-dd') into v_current_b from dual; IF v_begintime_a <> v_begintime_b THEN
dbms_output.put_line('v_begintime_a <> v_begintime_b');
dbms_output.put_line('v_begintime_a: '||to_char(v_begintime_a,'YYYY-MM-DD HH24:MI:SS')||' v_begintime_b: '||to_char(v_begintime_b,'YYYY-MM-DD HH24:MI:SS'));
ELSE
dbms_output.put_line('v_begintime_a = v_begintime_b');
dbms_output.put_line('v_begintime_a: '||to_char(v_begintime_a,'YYYY-MM-DD HH24:MI:SS')||' v_begintime_b: '||to_char(v_begintime_b,'YYYY-MM-DD HH24:MI:SS'));
END IF; IF v_current_a <> v_current_b THEN
dbms_output.put_line('v_current_a <> v_current_b');
dbms_output.put_line('v_current_a: '||to_char(v_current_a,'YYYY-MM-DD HH24:MI:SS')||' v_current_b: '||to_char(v_current_b,'YYYY-MM-DD HH24:MI:SS'));
ELSE
dbms_output.put_line('v_current_a = v_current_b');
dbms_output.put_line('v_current_a: '||to_char(v_current_a,'YYYY-MM-DD HH24:MI:SS')||' v_current_b: '||to_char(v_current_b,'YYYY-MM-DD HH24:MI:SS'));
END IF;END;
/----------------------------2 : 效率检测:-------------------------------------- 方法一:
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 to_char(sysdate,'yyyy')||'-'||to_char(sysdate,'mm')||'-'||'01' into v_begintime from dual;
select to_char(sysdate-1,'yyyy-mm-dd') into 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;
/v_begin_loop_time: 2010-12-09 09:51:56 v_end_loop_time: 2010-12-09 09:52:34PL/SQL 过程已成功完成。-- 执行一百万次循环赋值,执行时间:38秒----------------------------------
-- 方法二: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 to_char(sysdate,'yyyy')||'-'||to_char(sysdate,'mm')||'-'||'01', to_char(sysdate-1,'yyyy-mm-dd') 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;
/
v_begin_loop_time: 2010-12-09 09:53:31 v_end_loop_time: 2010-12-09 09:53:52
-- 执行一百万次循环,执行时间:21秒----------------------------------
-- 方法三: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;
/v_begin_loop_time: 2010-12-09 09:56:57 v_end_loop_time: 2010-12-09 09:57:13PL/SQL 过程已成功完成。-- 执行一百万次循环,执行时间:16秒----------------------------------
-- 方法三: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;
v_begintime := trunc(sysdate,'MM');
v_current := trunc(sysdate-1);
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;
/v_begin_loop_time: 2010-12-09 10:05:36 v_end_loop_time: 2010-12-09 10:05:58PL/SQL 过程已成功完成。-- 执行一百万次循环,执行时间:22秒----------------------------------
-- 方法四:DECLARE
v_begintime date;
v_current date; v_begin_loop_time date;
v_end_loop_time date;
v_sql VARCHAR2(500);
BEGIN
v_begin_loop_time := sysdate;
v_sql := 'select trunc(sysdate,''MM''), trunc(sysdate-1) from dual';
FOR i IN 1..1000000 LOOP
EXECUTE IMMEDIATE v_sql INTO v_begintime, v_current;
END LOOP;
v_end_loop_time := sysdate;
dbms_output.put_line('v_begintime: '||to_char(v_begintime,'YYYY-MM-DD HH24:MI:SS')||' v_current: '||to_char(v_current,'YYYY-MM-DD HH24:MI:SS'));
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;
/
解决方案 »
- 菜鸟弱弱的问下Oracle与SqlServer最大的区别是什么
- oracle 中 把IF A=1 THEN RETURN ‘男’ ELSE RETURN ‘女’ END IF; 语句用一个函数表达出来
- ORACLE 数据库内存占用问题
- OCP考试情况
- oracle 的问题
- 急求:已经有数据库以及表了,但没有PowerDesigner模型,现需要将oracle的数据导入到PowerDesigner ,有什么好方法?谢谢!
- oralce817中编译存储过程时状态这invalid,但错误显示不能用,请问如何才能看这个错误提示啊,谢谢
- 请问我的要存放含有‘年月日时分秒毫秒微秒’的数据能否用date类型存储?
- 我想结交Oracle的朋友!请大家留下MSN或QQ
- oracle job时间被改变
- 那位熟悉数据字典,有问题请教
- Oracle中如何處理帶中劃線的字段?
罗哥已经测试完毕---最优
--已用时间: 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;
多在前面一步 转类型trunc 对类型不变 只是取后面对应格式的值 所以花费时间少 所以总结 能用trunc则尽量
----------------------------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.
--结论:
方法三最优!方法一最差!
总的来说To_Char()比较耗时间,因为对日期型进行了格式转换成字符型
Trunc()由于也是格式转换,但是没有进行类型转换,转换格式后仍然是日期型的至于一条SQL能转换的,用两条,当然要耗多一点时间的
--为虾米我的第三种执行了这么长时间呢?
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:09PL/SQLプロシージャが正常に完了しました。