练练手,不对的地方请大家指出:
create table test(start_time date,end_time date,gay_time varchar2(50),fromtoweek varchar2(50));
create or replace procedure proc_test(v_start_time date,v_end_time date)
as
invalid_time_exception exception;
gap_time number;
week_day varchar2(100);
begin
if v_start_time is null or v_end_time is null then
raise invalid_time_exception;
else
select trunc(v_end_time-v_start_time,2) into gap_time from dual;
if gap_time>=1 then
select to_char(v_start_time,'day')||'--'||to_char(v_end_time,'day') into week_day from dual;
insert into test values(v_start_time,v_end_time,gap_time||'(天)',week_day);
else
if substr(to_char(v_start_time),1,8)=substr(to_char(v_end_time),1,8) then
select to_char(v_start_time,'day') into week_day from dual;
insert into test values(v_start_time,v_end_time,gap_time*24||'(小时)',week_day);
else
select to_char(v_start_time,'day')||'--'||to_char(v_end_time,'day') into week_day from dual;
insert into test values(v_start_time,v_end_time,gap_time*24||'(小时)',week_day);
end if;
end if;
end if;
commit;
dbms_output.put_line('procedure successful');
exception
when invalid_time_exception then
dbms_output.put_line('time inputed is invalid');
when others then
dbms_output.put_line(sqlcode||sqlerrm);
end proc_test;