自定义函数: create or replace function f_qq (id1 in varchar2, id2 in varchar2, id3 in varchar2 ) return integer as begin insert into try1 values(id1,id2,id3); commit; return 0; exception when others then begin rollback; return -1; end; end f_qq; 编译成功调用函数: declare time_before number; time_after number; ret integer; begin time_before:= DBMS_UTILITY.GET_TIME*1000; ret := f_qq(1,2,3); time_after:= DBMS_UTILITY.GET_TIME*1000; dbms_output.put_line(time_after - time_before); end; 也执行成功!可是结果一直为0 各位看看我上面的代码那有问题! 万分感谢
create or replace function f_qq (id1 in varchar2, id2 in varchar2, id3 in varchar2 ) return integer as begin insert into try1 values(id1,id2,id3); commit; return 0; exception when others then begin --rollback;用不作rollback因为如果插入不成功的话,上面的数据是不会插入try1表的。并且此处你没有其他的dml操作。 return -1; end; end f_qq; 编译成功调用函数: declare time_before number; time_after number; ret integer; begin time_before:= DBMS_UTILITY.GET_TIME; ret := f_qq(1,2,3); time_after:= DBMS_UTILITY.GET_TIME; dbms_output.put_line((time_after - time_before)*100);--应该乘100才是以秒为单位的时间 end;
如果你要在dbms_utility.get_time后乘100,应该这样使用: select dbms_utility.get_time*100 into time_before from dual;
如果你执行函数的操作小于百分之一秒,时间太短
两次操作相减可能为0
结果是
+000000000 00:00:00.000000000
看来还是0
SQL> DECLARE
2 time_before BINARY_INTEGER;
3 time_after BINARY_INTEGER;
4 n NUMBER := 0;
5 BEGIN
6 time_before := DBMS_UTILITY.GET_TIME;
7 FOR i IN 1 .. 1000000 LOOP
8 n := n + i;
9 END LOOP;
10 time_after := DBMS_UTILITY.GET_TIME;
11 dbms_output.put_line(time_after - time_before);
12 END;
13 /
25PL/SQL 过程已成功完成。SQL>
执行你的是有时间的
可是我已经time_before := DBMS_UTILITY.GET_TIME*1000;
可结果还是0
*10000就数据溢出了
是不是函数的问题呀
time_before binary_integer;
time_after binary_integer;
begin
time_before := DBMS_UTILITY.GET_TIME;
调用自定义函数语句;
time_after := DBMS_UTILITY.GET_TIME;
dbms_output.put_line(time_after - time_before);
end;说明:自定义函数是一个带输入参数的查询函数,无论我数据量有多大,十万,百万
上面函数所得结果永远是0
搞不懂!有高手没?
time_before number;
time_after number;
begin
time_before := DBMS_UTILITY.GET_TIME;
dbms_lock.sleep(1);
time_after := DBMS_UTILITY.GET_TIME;
dbms_output.put_line(time_after - time_before);
end;
create or replace function f_qq
(id1 in varchar2,
id2 in varchar2,
id3 in varchar2
)
return integer
as
begin
insert into try1 values(id1,id2,id3);
commit;
return 0;
exception
when others then
begin
rollback;
return -1;
end;
end f_qq;
编译成功调用函数:
declare
time_before number;
time_after number;
ret integer;
begin
time_before:= DBMS_UTILITY.GET_TIME*1000;
ret := f_qq(1,2,3);
time_after:= DBMS_UTILITY.GET_TIME*1000;
dbms_output.put_line(time_after - time_before);
end;
也执行成功!可是结果一直为0
各位看看我上面的代码那有问题! 万分感谢
(id1 in varchar2,
id2 in varchar2,
id3 in varchar2
)
return integer
as begin
insert into try1 values(id1,id2,id3);
commit;
return 0;
exception
when others then
begin
--rollback;用不作rollback因为如果插入不成功的话,上面的数据是不会插入try1表的。并且此处你没有其他的dml操作。
return -1;
end;
end f_qq;
编译成功调用函数:
declare
time_before number;
time_after number;
ret integer;
begin
time_before:= DBMS_UTILITY.GET_TIME;
ret := f_qq(1,2,3);
time_after:= DBMS_UTILITY.GET_TIME;
dbms_output.put_line((time_after - time_before)*100);--应该乘100才是以秒为单位的时间
end;
select dbms_utility.get_time*100 into time_before from dual;