这是我写的存储过程,现在同事说要返回一个游标,大家说要在这个过程做哪些修改,我不懂我第一次写存储过程,希望大侠帮帮忙,感激不尽,在线等
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 ;
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 ;
create or replace procedure pro_activity (a in varchar2,ret out result_data) is
......
type cur_result is ref cursor;
create or replace procedure pro_activity (
out_result out cur_result --输出参数,作为返回游标
)
as
begin
open out_result for
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);
end pro_activity;