如:
v_arg1 IN NUMBER,v_arg2 IN NUMBER
需求:
参数1: v_arg1 (如:v_arg1:=900.10 即900小时零10分钟)
参数2: v_arg2 (如:v_arg2:=800.20 即800小时零20分钟)需要得到结果:v_arg1 - v_arg2 (即:99.50)即得到99小时零50分钟。
v_arg1 IN NUMBER,v_arg2 IN NUMBER
需求:
参数1: v_arg1 (如:v_arg1:=900.10 即900小时零10分钟)
参数2: v_arg2 (如:v_arg2:=800.20 即800小时零20分钟)需要得到结果:v_arg1 - v_arg2 (即:99.50)即得到99小时零50分钟。
解决方案 »
- oracle联合查询后的排序问题
- 存储过程调用:sqlora8.dll错误?
- sqlite怎么用?
- 已登录当前数据库,oracle数据库,用什么命令可以获得当前数据库名和SID和用户名?
- dbms_job.submit的问题
- 小弟不才,关于 "ORA-01017 invalid username/password;logon denied" 高手请指点一下,
- Oracle9i的安装?
- 数据库读取记录时间
- 我是一个ORACLE的初学都,请大师指点一个简单问题,非常感激!!
- 请问各位 传统dba和etl 这2个职位有什么区别?
- 左右连接
- 急救:Oracle不支持IF not Exists(),怎么写?
2 WHEN sign(MOD(900.10, 1) - MOD(800.20, 1)) >= 0 THEN
3 trunc(900.10) - trunc(800.20) + MOD(900.10, 1) - MOD(800.20, 1)
4 ELSE
5 trunc(900.10) - trunc(800.20) - 1 + 0.6 + MOD(900.10, 1) -
6 MOD(800.20, 1)
7 END
8 FROM dual;
CASEWHENSIGN(MOD(900.10,1)-MOD
------------------------------
99.5
SQL>
SELECT CASE
WHEN sign(MOD(900.10, 1) - MOD(800.20, 1)) >= 0 THEN
900.10 - 800.20
ELSE
900.10 - 800.20 - 0.4
END
FROM dual;
declare
v1 number:=900.10;
v2 number:=800.20;
ds interval day to second;
diff number(8,2);
begin
ds:=numtodsinterval(floor(v1),'hour')+numtodsinterval((v1-floor(v1))*100,'minute')-
numtodsinterval(floor(v2),'hour')-numtodsinterval((v2-floor(v2))*100,'minute');
diff:=extract(day from ds)*24+extract(hour from ds)+extract(minute from ds)/100;
dbms_output.put_line(to_char(diff,'99999.00'));
end;
/
2 v1 number:=900.10;
3 v2 number:=800.20;
4 diff number;
5 begin
6 diff:=floor(v1)*60+(v1-floor(v1))*100-floor(v2)*60-(v2-floor(v2))*100;
7 diff:=floor(diff/60)+mod(diff,60)/100;
8 dbms_output.put_line(to_char(diff,'999999.00'));
9* end;
SQL> /
99.50PL/SQL procedure successfully completed.
declare
v1 number(10,2):=900.10;
v2 number(10,2):=800.20;
str varchar2(10);
str1 varchar2(10);
str2 varchar2(10);
begin
str1:=to_char(v1,'fm9999999.90');
str2:=to_char(v2,'fm9999999.90');
select trunc((trunc(v1)*60+substr(str1,instr(str1,'.')+1)-(trunc(v2)*60+substr(str2,instr(str2,'.')+1)))/60)||'.'
||mod(trunc(v1)*60+substr(str1,instr(str1,'.')+1)-(trunc(v2)*60+substr(str2,instr(str2,'.')+1)),60) into str from dual;
dbms_output.put_line(str);
end;SQL> declare
2 v1 number(10,2):=900.10;
3 v2 number(10,2):=800.20;
4 str varchar2(10);
5 str1 varchar2(10);
6 str2 varchar2(10);
7 begin
8 str1:=to_char(v1,'fm9999999.90');
9 str2:=to_char(v2,'fm9999999.90');
10 select trunc((trunc(v1)*60+substr(str1,instr(str1,'.')+1)-(trunc(v2)*60+substr(str2,instr(str2,'.')+1)))/60)||'.'
11 ||mod(trunc(v1)*60+substr(str1,instr(str1,'.')+1)-(trunc(v2)*60+substr(str2,instr(str2,'.')+1)),60) into str from dual;
12 dbms_output.put_line(str);
13 end;
14 /
99.50
PL/SQL procedure successfully completed