-- 10.9.5 时间间隔的加减运算 ( P291 ) -- 和日期时间值不一样的是,把两个时间间隔进行加法运算是很有意义的。减法运行也同样有意义。要对两个时间间隔进行加减运行,你需要记住的一个规则就是: -- 两个时间间隔的类型必须是相同类型的。比如: DECLARE dts1 INTERVAL DAY TO SECOND := '2 3:4:5.6'; dts2 INTERVAL DAY TO SECOND := '1 1:1:1.1'; ytm1 INTERVAL YEAR TO MONTH := '2-10'; ytm2 INTERVAL YEAR TO MONTH := '1-1'; days1 NUMBER := 3; days2 NUMBER := 1; BEGIN DBMS_OUTPUT.PUT_LINE(dts1 - dts2); DBMS_OUTPUT.PUT_LINE(ytm1 - ytm2); DBMS_OUTPUT.PUT_LINE(days1 - days2); END; /
-- oralce 时间段求和:CREATE TABLE t( t_logid NUMBER(18,0), -- 登录ID(会话ID) t_login timestamp default systimestamp, -- 登录时间 t_logout timestamp default systimestamp, -- 退出时间 t_bet_inout INTERVAL DAY TO SECOND -- 本次在线时长 );CREATE OR REPLACE TRIGGER trg_t BEFORE INSERT OR UPDATE ON t FOR EACH ROW BEGIN :NEW.t_bet_inout:=(:NEW.t_logout-:NEW.t_login) DAY TO SECOND; END; /INSERT INTO t(t_logid) VALUES(1); UPDATE t set t_logout=systimestamp; COMMIT; SELECT * FROM t WHERE t_logid=1;INSERT INTO t(t_logid) VALUES(2); COMMIT; UPDATE t set t_logout=systimestamp; COMMIT; SELECT * FROM t WHERE t_logid=1;create or replace type day_to_second_sum_type as object ( total interval day(9) to second(9), static function ODCIAggregateInitialize(sctx IN OUT day_to_second_sum_type ) return number, member function ODCIAggregateIterate(self IN OUT day_to_second_sum_type , value IN varchar2 ) return number, member function ODCIAggregateTerminate(self IN day_to_second_sum_type, returnValue OUT varchar2, flags IN number) return number, member function ODCIAggregateMerge(self IN OUT day_to_second_sum_type, ctx2 IN day_to_second_sum_type) return number ); /create or replace type body day_to_second_sum_type is static function ODCIAggregateInitialize(sctx IN OUT day_to_second_sum_type) return number is begin sctx := day_to_second_sum_type( numtodsinterval( 0, 'SECOND' ) ); return ODCIConst.Success; end; member function ODCIAggregateIterate(self IN OUT day_to_second_sum_type, value IN varchar2 ) return number is l_value interval day(9) to second(9); begin l_value := value; -- dbms_output.put_line( l_value ); self.total := self.total + l_value; return ODCIConst.Success; end; member function ODCIAggregateTerminate(self IN day_to_second_sum_type, returnValue OUT varchar2, flags IN number) return number is begin returnValue := self.total; return ODCIConst.Success; end; member function ODCIAggregateMerge(self IN OUT day_to_second_sum_type, ctx2 IN day_to_second_sum_type) return number is begin self.total := self.total + ctx2.total; return ODCIConst.Success; end; end; /CREATE or replace FUNCTION ds_sum(input varchar2) RETURN varchar2 PARALLEL_ENABLE AGGREGATE USING day_to_second_sum_type; /-- 求“总在线时长”: select to_dsinterval( ds_sum(t_bet_inout) ) sum from t;
substr各种截断取成秒啊~~
substr各种截断取成秒啊~~
跟我之前实现的一样,字符串截取,没有更好的办法了?
substr各种截断取成秒啊~~
跟我之前实现的一样,字符串截取,没有更好的办法了?
你的是截了再减,这是减了再截,要是觉得麻烦你可以写个过程或函数做一下,应该没有其他
-- 和日期时间值不一样的是,把两个时间间隔进行加法运算是很有意义的。减法运行也同样有意义。要对两个时间间隔进行加减运行,你需要记住的一个规则就是:
-- 两个时间间隔的类型必须是相同类型的。比如:
DECLARE
dts1 INTERVAL DAY TO SECOND := '2 3:4:5.6';
dts2 INTERVAL DAY TO SECOND := '1 1:1:1.1'; ytm1 INTERVAL YEAR TO MONTH := '2-10';
ytm2 INTERVAL YEAR TO MONTH := '1-1'; days1 NUMBER := 3;
days2 NUMBER := 1;
BEGIN
DBMS_OUTPUT.PUT_LINE(dts1 - dts2);
DBMS_OUTPUT.PUT_LINE(ytm1 - ytm2);
DBMS_OUTPUT.PUT_LINE(days1 - days2);
END;
/
t_logid NUMBER(18,0), -- 登录ID(会话ID)
t_login timestamp default systimestamp, -- 登录时间
t_logout timestamp default systimestamp, -- 退出时间
t_bet_inout INTERVAL DAY TO SECOND -- 本次在线时长
);CREATE OR REPLACE TRIGGER trg_t
BEFORE INSERT OR UPDATE ON t
FOR EACH ROW
BEGIN
:NEW.t_bet_inout:=(:NEW.t_logout-:NEW.t_login) DAY TO SECOND;
END;
/INSERT INTO t(t_logid) VALUES(1);
UPDATE t set t_logout=systimestamp;
COMMIT;
SELECT * FROM t WHERE t_logid=1;INSERT INTO t(t_logid) VALUES(2);
COMMIT;
UPDATE t set t_logout=systimestamp;
COMMIT;
SELECT * FROM t WHERE t_logid=1;create or replace type day_to_second_sum_type as object
(
total interval day(9) to second(9), static function
ODCIAggregateInitialize(sctx IN OUT day_to_second_sum_type )
return number, member function
ODCIAggregateIterate(self IN OUT day_to_second_sum_type ,
value IN varchar2 )
return number, member function
ODCIAggregateTerminate(self IN day_to_second_sum_type,
returnValue OUT varchar2,
flags IN number)
return number, member function
ODCIAggregateMerge(self IN OUT day_to_second_sum_type,
ctx2 IN day_to_second_sum_type)
return number
);
/create or replace type body day_to_second_sum_type
is static function ODCIAggregateInitialize(sctx IN OUT day_to_second_sum_type)
return number
is
begin
sctx := day_to_second_sum_type( numtodsinterval( 0, 'SECOND' ) );
return ODCIConst.Success;
end; member function ODCIAggregateIterate(self IN OUT day_to_second_sum_type,
value IN varchar2 )
return number
is
l_value interval day(9) to second(9);
begin
l_value := value;
-- dbms_output.put_line( l_value );
self.total := self.total + l_value;
return ODCIConst.Success;
end; member function ODCIAggregateTerminate(self IN day_to_second_sum_type,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := self.total;
return ODCIConst.Success;
end; member function ODCIAggregateMerge(self IN OUT day_to_second_sum_type,
ctx2 IN day_to_second_sum_type)
return number
is
begin
self.total := self.total + ctx2.total;
return ODCIConst.Success;
end;
end;
/CREATE or replace
FUNCTION ds_sum(input varchar2)
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING day_to_second_sum_type;
/-- 求“总在线时长”:
select to_dsinterval( ds_sum(t_bet_inout) ) sum
from t;
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5269744394385