create or replace function get_date(p_date in date) return varchar2 is Result varchar2(10); begin if to_char(p_date,'hh24')>=0 and to_char(p_date,'hh24')<5 then result:='夜宵'; elsif to_char(p_date,'hh24')>=5 and to_char(p_date,'hh24')<12 then result:='早餐'; elsif to_char(p_date,'hh24')>=12 and to_char(p_date,'hh24')<18 then result:='午餐'; else result:='晚餐'; end if;return(Result); end get_date; /SQL> select effectdate from student;EFFECTDATE ----------- 2002-2-2 1:32:00 2002-2-2 12:35:00 2002-2-2 19:54:00 2002-2-2 7:18:00SQL> select get_date(effectdate) 餐次,effectdate from student;餐次 EFFECTDATE -------------------------------------------------------------------------------- ----------- 夜宵 2002-2-2 1:32:00 午餐 2002-2-2 12:35:00 晚餐 2002-2-2 19:54:00 早餐 2002-2-2 7:18:00
use sql, some complicated
-------------------
2002-11-21 13:32:13
2002-11-21 13:32:16
2002-12-12 16:45:32
2002-12-12 16:46:29
2003-01-04 02:56:45
2003-01-04 09:56:58
2003-01-04 18:57:25已选择7行。
SQL> select decode(sign(to_char(aaa,'hh24')-5),-1,'夜宵',decode(sign(to_char(aaa,'hh24')-12),
2 -1,'早餐',decode(sign(to_char(aaa,'hh24')-18),-1,'午餐','晚餐'))) from b;DECO
----
午餐
午餐
午餐
午餐
夜宵
早餐
晚餐已选择7行。
现在有一个表trjn,内容和结构如下:
effectdate是date型,数据库是oracle8i
是CSDN有问题还是我的IE有问题?
唉,csdn最近乱乱的
Result varchar2(10);
begin
if to_char(p_date,'hh24')>=0 and to_char(p_date,'hh24')<5 then
result:='夜宵';
elsif to_char(p_date,'hh24')>=5 and to_char(p_date,'hh24')<12 then
result:='早餐';
elsif to_char(p_date,'hh24')>=12 and to_char(p_date,'hh24')<18 then
result:='午餐';
else
result:='晚餐';
end if;return(Result);
end get_date;
/SQL> select effectdate from student;EFFECTDATE
-----------
2002-2-2 1:32:00
2002-2-2 12:35:00
2002-2-2 19:54:00
2002-2-2 7:18:00SQL> select get_date(effectdate) 餐次,effectdate from student;餐次 EFFECTDATE
-------------------------------------------------------------------------------- -----------
夜宵 2002-2-2 1:32:00
午餐 2002-2-2 12:35:00
晚餐 2002-2-2 19:54:00
早餐 2002-2-2 7:18:00