请教大侠下,如何写存储过程,把跨天的日期和时间当天的详细的显示出来,我就简单的举个例子:比如开始日期2009-12-08 8:00,结束日期是2009-12-11 15:00 ,然后我的需求是这样的
日期 开始时间 结束时间 地址
2009-12-08 8:00 23:59 台北
2009-12-09 00:00 23:59 台北
2009-12-10 00:00 23:59 台北
2009-12-11 00:00 15:00 台北数据库表activity有start_date,end_date,address字段等,我写的比较粗糙,还有关联很多表,希望有经验者帮我说下,因为我写过,存储过程比较薄弱,谢谢,感激不尽,在线等,急急
日期 开始时间 结束时间 地址
2009-12-08 8:00 23:59 台北
2009-12-09 00:00 23:59 台北
2009-12-10 00:00 23:59 台北
2009-12-11 00:00 15:00 台北数据库表activity有start_date,end_date,address字段等,我写的比较粗糙,还有关联很多表,希望有经验者帮我说下,因为我写过,存储过程比较薄弱,谢谢,感激不尽,在线等,急急
这种要人家写procedure或sql的,最好还是表结构、测试数据,基于测试数据的要什么效果都详细写明。
否则要人家猜,浪费大家时间。话不好听,看不惯就删了!
"ACTIVITY_ID" NUMBER(10) NOT NULL ,
"ACTIVITY_TYPE_ID" NUMBER(10) ,
"ASSOCIATE_ID" NUMBER(10) ,
"PLAN_SCHEDULE_ID" NUMBER(10) ,
"NAME" VARCHAR2(50 BYTE) ,
"NUMBER_OF_DONOR" NUMBER(10) ,
"PLANNING_VOLUME_OF_BLOOD" NUMBER(10,2) ,
"ADDRESS" VARCHAR2(256 BYTE) ,
"NEXT_ACTIVITY_DATE" DATE ,
"ACTIVITY_START_DATE" DATE ,
"ACTIVITY_END_DATE" DATE ,
PRIMARY KEY("ACTIVITY_ID")
)
关联太多表了,不好意思
from activity act
join plan_schedule ps on act.plan_schedule_id = ps.plan_schedule_id
join schedule_task_manpower stm on ps.plan_schedule_id=stm.schedule_id
join task_manpower tm on stm.schedule_task_manpower_id=tm.schedule_task_manpower_id
join users on users.user_id=tm.user_id
join plan_schedule_status ps_stauts on ps.current_status_id=ps_stauts.plan_schedule_status_id
where (ps.current_status_id=3 or ps.current_status_id=4) into c_act;
这是SQL语句,我把它贴出来,希望大侠能懂帮忙写下,谢谢
create table herry_temp(activity_id number, start_date timestamp, end_date timestamp, address varchar2(200));2. --Insert data
insert into herry_temp
values
(1,
to_timestamp('2009-12-08 08:00', 'YYYY-MM-DD hh24:mi'),
to_timestamp('2009-12-11 15:00', 'YYYY-MM-DD hh24:mi'),
'台北');
v_end_date herry_temp.end_date%type;
v_address herry_temp.address%type;
v_start_day VARCHAR2(10);
v_start_time VARCHAR2(5);
v_end_day VARCHAR2(10);
v_end_time VARCHAR2(5);
v_between_time number; cursor c_search is
select start_date, end_date, address
from herry_temp
where activity_id = v_id;begin
open c_search;
fetch c_search
into v_start_date, v_end_date, v_address;
dbms_output.put_line(rpad('日期',10) || '|' || rpad('开始时间',8) ||
'|' || rpad('结束时间',8) || '|' || '地址');
Loop
v_start_day := to_char(v_start_date, 'yyyy-mm-dd');
v_start_time := to_char(v_start_date, 'hh24:mi');
v_end_day := to_char(v_end_date, 'yyyy-mm-dd');
v_end_time := to_char(v_end_date, 'hh24:mi');
v_between_time := to_number(to_date(TO_CHAR(v_end_date,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(TO_CHAR(v_start_date,
'yyyy-mm-dd hh24:mi:ss'),
'YYYY-MM-DD HH24:mi:ss')) * 86400;
if v_between_time <= 0 then
dbms_output.put_line(rpad(v_start_day,10) || '|' || rpad(v_start_time,8) || '|' ||
rpad(v_end_time,8) || '|' || v_address);
exit;
elsif v_between_time > 0 then
dbms_output.put_line(rpad(v_start_day,10) || '|' || rpad(v_start_time,8) || '|' ||
rpad('23:59',8) || '|' || v_address); v_start_day := to_char(to_date(v_start_day, 'yyyy-mm-dd') + 1,
'yyyy-mm-dd');
v_start_date := to_timestamp(v_start_day, 'YYYY-MM-DD HH24:mi:ss');
end if;
end loop;
close c_search;
end p_testdate;
begin
p_testdate(1);
end;
/
5. It will output follow:日期 |开始时间|结束时间|地址
2009-12-08|08:00 |23:59 |台北
2009-12-09|00:00 |23:59 |台北
2009-12-10|00:00 |23:59 |台北
2009-12-11|00:00 |23:59 |台北
2009-12-12|00:00 |15:00 |台北
create or replace procedure pro_activity (a in varchar2)
is
v_name VARCHAR2(256);
v_sdate VARCHAR2(256);
v_edate VARCHAR2(256);
v_sTime VARCHAR2(256);
v_eTime VARCHAR2(256);
v_addr VARCHAR2(256);
v_count number(10) default 0;
v_number number(2) default 0;
cursor c_act
is select
CONCAT(users.firstname,users.middleName) as fullName,
to_char(act.activity_start_date,'yyyy-mm-dd') as sdate,
to_char(act.activity_end_date,'yyyy-mm-dd') as edate,
to_char(act.activity_start_date,'hh24:mi') as stime,
to_char(act.activity_end_date,'hh24:mi') as stime,
act.address
from activity act
join plan_schedule ps on act.plan_schedule_id = ps.plan_schedule_id
join schedule_task_manpower stm on ps.plan_schedule_id=stm.schedule_id
join task_manpower tm on stm.schedule_task_manpower_id=tm.schedule_task_manpower_id
join users on users.user_id=tm.user_id
join plan_schedule_status ps_stauts on ps.current_status_id=ps_stauts.plan_schedule_status_id
where (ps.current_status_id=3 or ps.current_status_id=4);
begin
OPEN c_act;
LOOP
FETCH c_act INTO v_name,v_sdate,v_edate,v_sTime,v_eTime,v_addr;
v_count:=0; if(v_edate > v_sdate) then
LOOP
if v_count = 0 then
dbms_output.put_line('人员姓名 '||'日期 '||'开始时间 '||'结束时间 '||'地址 ');
dbms_output.put_line(v_name ||v_sdate ||v_sTime|| '23:59'|| v_addr);
else
if(v_edate != v_sdate) then
dbms_output.put_line('人员姓名 '||'日期 '||'开始时间 '||'结束时间 '||'地址 ');
dbms_output.put_line(v_name ||v_sdate ||'00:00'|| '23:59' ||v_addr);
else
dbms_output.put_line('人员姓名 '||'日期 '||'开始时间 '||'结束时间 '||'地址 ');
dbms_output.put_line(v_name ||v_sdate ||'00:00'|| v_eTime|| v_addr);
end if; end if;
EXIT WHEN v_sdate = v_edate;
v_sdate := to_char(to_date(v_sdate,'yyyy-mm-dd')+1,'YYYY-MM-DD');
v_count := v_count+1;
END LOOP;
else
dbms_output.put_line('人员姓名 '||'日期 '||'开始时间 '||'结束时间 '||'地址 ');
dbms_output.put_line(v_name ||v_sdate ||v_sTime|| v_eTime|| v_addr);
end if;
EXIT WHEN c_act%NOTFOUND;
END LOOP;close c_act;
end pro_activity ;