函数1
--将时间值转换为时间格式(hh:mm:ss)的字符串
CREATE or replace FUNCTION GetTimeStr(sec in NUMBER)
RETURN varchar2 is result varchar2(64);
BEGIN
declare second NUMBER;
declare minute NUMBER;
declare hour NUMBER;
hour = sec / 3600;
minute = (sec % 3600) / 60;
second = (sec % 3600) % 60;
result =cast(hour as varchar2) + ':' + cast(minute as varchar2) + ':' + cast(second as varchar2);
return result;
END GetTimeStr;函数2
--将时间转换为时间组字符串
create or replace function GetTimeGroup(time in datetime,fmt in varchar2)
RETURN varchar2 is Result varchar2(30);
BEGIN
declare value varchar2(30);
set value=case fmt
when 'day' then convert(varchar2(12),time,23)
when 'month' then convert(varchar2(7),time,23)
when 'quarter' then datename(year,time)+'年'+datename(quarter,time)+'季度'
when 'year' then convert(varchar2(4),time,23)
Result :=value;
END
return Result;
END GetTimeGroup;
--将时间值转换为时间格式(hh:mm:ss)的字符串
CREATE or replace FUNCTION GetTimeStr(sec in NUMBER)
RETURN varchar2 is result varchar2(64);
BEGIN
declare second NUMBER;
declare minute NUMBER;
declare hour NUMBER;
hour = sec / 3600;
minute = (sec % 3600) / 60;
second = (sec % 3600) % 60;
result =cast(hour as varchar2) + ':' + cast(minute as varchar2) + ':' + cast(second as varchar2);
return result;
END GetTimeStr;函数2
--将时间转换为时间组字符串
create or replace function GetTimeGroup(time in datetime,fmt in varchar2)
RETURN varchar2 is Result varchar2(30);
BEGIN
declare value varchar2(30);
set value=case fmt
when 'day' then convert(varchar2(12),time,23)
when 'month' then convert(varchar2(7),time,23)
when 'quarter' then datename(year,time)+'年'+datename(quarter,time)+'季度'
when 'year' then convert(varchar2(4),time,23)
Result :=value;
END
return Result;
END GetTimeGroup;
CREATE or replace FUNCTION GetTimeStr(sec in NUMBER)
RETURN varchar2
is
result varchar2(64);
--定义变量在这个地方
second NUMBER;
minute NUMBER;
hour NUMBER;
BEGIN--hour := sec / 3600; -- := 才是赋值 = 是相当与高级语言的 ==
--minute := (sec % 3600) / 60;
--second := (sec % 3600) % 60;
--result :=cast(hour as varchar2) + ':' + cast(minute as varchar2) + ':' + cast(second as varchar2);
--格式转化不对,需要写sql
select cast(hour as varchar2) + ':' + cast(minute as varchar2) + ':' + cast(second as varchar2) into result from dual;
--如果你格式转化没错的话,基本没问题了。一般我直接to_char来转化的
return result;
END GetTimeStr;
定义位置错了
格式转化需要写sql
SQL> edit
已写入文件 afiedt.buf 1 CREATE or replace FUNCTION GetTimeStr(sec in NUMBER)
2 RETURN varchar2
3 is
4 result varchar2(64);
5 second NUMBER;
6 minute NUMBER;
7 hour NUMBER;
8 BEGIN
9 hour := round(sec / 3600);
10 minute := round(mod(sec,3600) / 60);
11 second := mod(mod(sec,3600),60);
12 result :=cast(hour as varchar2) || ':' ||cast(minute as varchar2) || ':' |
|cast(second as varchar2);
13 return result;
14* END GetTimeStr;
SQL> /函数已创建。测试:
SQL> edit
已写入文件 afiedt.buf 1 declare
2 str varchar2(100);
3 sec number:=123;
4 begin
5 str:=GetTimeStr(sec);
6 dbms_output.put_line(GetTimeStr(sec));
7* end;
SQL> /
0:2:3PL/SQL 过程已成功完成。oralce求莫和除法的方法,跟c或者JAVA不同,注意使用round()跟mod();
函数2:
convert函数我很少用,自个儿研究下吧,注意你的case when,连end case都没有;
两个方法都有的错误1楼已经说过了。