请各位高手帮忙下、
我要创建如下一个表:
表名 Procs
proc_id anest_name start_time end_time
=======================================================
10 'Baker' 08:00 11:00
20 'Bacd' 09:00 13:00
30 'Dow' 09:00 15:30
40 'CDE' 08:00 13:30
我不知道要怎么定义start_time和end_time两个字段的类型来接受08:00 和11:00
我要创建如下一个表:
表名 Procs
proc_id anest_name start_time end_time
=======================================================
10 'Baker' 08:00 11:00
20 'Bacd' 09:00 13:00
30 'Dow' 09:00 15:30
40 'CDE' 08:00 13:30
我不知道要怎么定义start_time和end_time两个字段的类型来接受08:00 和11:00
定义为VARCHAR2就可以了。。
--将时间字段定义为varcahr2类型,存储时用to_char(date,'hh24:mi')函数
--例如:to_char(sysdate,'hh24:mi')
SQL> select to_char(sysdate,'hh24:mi') time from dual;
TIME
-----
05:50SQL> select to_char(sysdate,'hh24:mi:ss') time from dual;
TIME
--------
05:50:45
--
SQL> create table procs(
2 proc_id number(4),
3 anest_name varchar2(10),
4 start_time varchar2(10),
5 end_time varchar2(10)
6 );
Table createdSQL> insert into procs
2 select 10,'Baker',to_char(to_date('08:00','hh24:mi'),'hh24:mi'),to_char(to_date('11:00','hh24:mi'),'hh24:mi') from dual union all
3 select 20,'Bacd',to_char(to_date('09:00','hh24:mi'),'hh24:mi'),to_char(to_date('13:00','hh24:mi'),'hh24:mi') from dual union all
4 select 30,'Dow',to_char(to_date('09:00','hh24:mi'),'hh24:mi'),to_char(to_date('15:30','hh24:mi'),'hh24:mi') from dual union all
5 select 40,'CDE',to_char(to_date('08:00','hh24:mi'),'hh24:mi'),to_char(to_date('13:30','hh24:mi'),'hh24:mi') from dual;4 rows insertedSQL> select * from procs;PROC_ID ANEST_NAME START_TIME END_TIME
------- ---------- ---------- ----------
10 Baker 08:00 11:00
20 Bacd 09:00 13:00
30 Dow 09:00 15:30
40 CDE 08:00 13:30
create table procs(
proc_id number(4),
anest_name varchar2(10),
start_time date,
end_time date
);
--
insert into procs
select 10,'Baker',to_date('08:00','hh24:mi'),to_date('11:00','hh24:mi') from dual union all
select 20,'Bacd',to_date('09:00','hh24:mi'),to_date('13:00','hh24:mi') from dual union all
select 30,'Dow',to_date('09:00','hh24:mi'),to_date('15:30','hh24:mi') from dual union all
select 40,'CDE',to_date('08:00','hh24:mi'),to_date('13:30','hh24:mi') from dual
--
--to_date()函数将你的时间转化为了日期格式
--自动加上了本月的开始日期2011-4-1
SQL> select * from procs;PROC_ID ANEST_NAME START_TIME END_TIME
------- ---------- ----------- -----------
10 Baker 2011-4-1 8: 2011-4-1 11
20 Bacd 2011-4-1 9: 2011-4-1 13
30 Dow 2011-4-1 9: 2011-4-1 15
40 CDE 2011-4-1 8: 2011-4-1 13
2 proc_id number(4),
3 anest_name varchar2(10),
4 start_time date,
5 end_time date
6 );表已创建。SQL> insert into procs
2 select 10,'Baker',to_date('08:00','hh24:mi'),to_date('11:00','hh24:mi') from dual union all
3 select 20,'Bacd',to_date('09:00','hh24:mi'),to_date('13:00','hh24:mi') from dual union all
4 select 30,'Dow',to_date('09:00','hh24:mi'),to_date('15:30','hh24:mi') from dual union all
5 select 40,'CDE',to_date('08:00','hh24:mi'),to_date('13:30','hh24:mi') from dual
6 /已创建4行。SQL> SELECT * FROM PROCS; PROC_ID ANEST_NAME START_TI END_TIME
---------- ---------- -------- --------
10 Baker 11-04-01 11-04-01
20 Bacd 11-04-01 11-04-01
30 Dow 11-04-01 11-04-01
40 CDE 11-04-01 11-04-01
根本字段后面就没时间啊
/*
还是将你的时间存储为varchar2类型吧!
7楼那样!
*/
select proc_id,
anest_name,
to_char(start_time, 'hh24:mi'),
to_char(end_time, 'hh24:mi')
from procs;10 Baker 08:00 11:00
20 Bacd 09:00 13:00
30 Dow 09:00 15:30
40 CDE 08:00 13:30