create or replace procedure pro_Sel_tbmeetmgrInfo(out_room out sys_refcursor) is
TYPE StringArray IS VARRAY(1000) of number ;
v_Result StringArray := StringArray() ;
TYPE c_time IS REF CURSOR; --创建游离标记
vrec c_time;
TYPE c_room IS REF CURSOR; --创建游离标记
hzwvrec c_room;
varInt number;
strOverSql varchar2(30);
v_months number;
v_notRoomId number ; -- 不可用会议室ID
v_xunhuan number := 1;
begin
select ceil(( to_date(v_stoptime,'yyyy-mm-dd') - next_day(to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')-1, ceil(substr(v_xunhuairiqi,3)))+1 )/7) into varInt from dual;
OPEN vrec for SELECT to_char(next_day(to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')-1,
ceil(substr(v_xunhuairiqi,3)))+1+(rownum-1)*7 , 'yyyy-MM-dd')
from dual connect by rownum<=varInt ;
LOOP
FETCH vrec INTO strOverSql; --
exit when vrec%notfound;
--dbms_output.put_line('----+++++++++++-----:'||strOverSql);
--得到不可用的会议室Id
open hzwvrec for select id from Tbmeetroom where id not in (select id from Tbmeetroom where status = 0
and id not in
(select huiyishi from tbmeetmgrinfo where to_date(v_strtime,'hh24:mi') between to_date(starttime,'hh24:mi')
and to_date(endtime,'hh24:mi') and to_date(stoptime,'yyyy-MM-dd') = to_date(strOverSql,'yyyy-MM-dd')
and ecid = v_ecid or to_date(v_endtime,'hh24:mi') between to_date(starttime,'hh24:mi')
and to_date(endtime,'hh24:mi') and to_date(stoptime,'yyyy-MM-dd') = to_date(strOverSql,'yyyy-MM-dd') and ecid = v_ecid )
and id not in
(select meetroomid from Tbmeetroomlock where to_date(strOverSql||' '||v_strtime,'yyyy-mm-dd hh24:mi')
between to_date(lockstarttime,'yyyy-mm-dd hh24:mi') and to_date(lockendtime,'yyyy-mm-dd hh24:mi')
or to_date(strOverSql||' '||v_endtime,'yyyy-mm-dd hh24:mi') between to_date(lockstarttime,'yyyy-mm-dd hh24:mi')
and to_date(lockendtime,'yyyy-mm-dd hh24:mi')));
LOOP
FETCH hzwvrec INTO v_notRoomId; --不可用会议室Id
exit when vrec%notfound;
v_Result.extend;
v_Result(v_xunhuan) := v_notRoomId ;
v_xunhuan := v_xunhuan + 1 ;
end loop;
-- commit;
end loop;
--open out_room for select id, name from Tbmeetroom where id not in (v_Result); --返回可用会议室--open out_room for select id, name from Tbmeetroom where id not in (v_Result);
--Compilation errors for PROCEDURE MEETING.PRO_SEL_TBMEETMGRINFOError: PLS-00642: local collection types not allowed in SQL statements
Line: 75
Text: open out_room for select id, name from Tbmeetroom where id not in (v_Result);
这一句、请教该怎么写!!!v_Result装的是数组、个数不确定、不想拼接字符、还有就是拼接字符需要","这个符号怎么加???
FOR i IN 1 .. v_Result.count LOOP
v_rooms := v_rooms || ',' || v_Result(i);
END LOOP;
v_rooms := ltrim(v_rooms, ',');
IF length(v_romms) != 0 THEN
OPEN out_room FOR 'select id, name from Tbmeetroom where id not in (' || v_rooms || ')';
ELSE
OPEN out_room FOR 'select id, name from Tbmeetroom';
END IF;
IF length(v_rooms) != 0 THEN 话说老兵就是牛~~~上一个帖子也是你给弄的吧???
只是今天要得比较急~~~要不然我就慢慢玩了!!!
o(︶︿︶)o 唉~~~看来得好好看看Oracle了!