oracle数据库查存储过程中包含,oracle存储包中包含多个存储过程以及参数的传入和传出...

elsif signs=4 then

if vm_id=0 then

open mod_result for

select * from hotel_module order by m_id;

else

-- re_singn:=vm_id;

open mod_result for

select * from hotel_module where m_id=vm_id;

end if;

end if;

end proc_get_fuction;

-----获取酒店信息的存储过程

procedure proc_hotel_info(signs in number,vi_id in number,vi_title in varchar2,vi_detail in varchar2,vi_remark in varchar2,re_singn out number,mod_result out mod_cur)

is

begin

if signs=1 then

insert into hotel_introduce values(hotel_introduce_id.nextval,vi_title,vi_detail,vi_remark);

re_singn:=1;

elsif signs=2 then

delete from hotel_introduce where i_id=vi_id;

re_singn:=1;

elsif signs=3 then

update hotel_introduce set i_title=vi_title , i_detail=vi_detail ,i_remark=vi_remark where i_id=vi_id;

re_singn:=1;

elsif signs=4 then

if vi_id=0 then

open mod_result for

select * from hotel_introduce order by i_id;

else

open mod_result for

select * from hotel_introduce where i_id=vi_id;

end if;

end if;

end proc_hotel_info;

-----分页存储过程

procedure fenye(pageNo in number, maxNum in number,sql_Count in varchar2,exec_sql in varchar2,

total_record out number,total_page out number,emp_result out mod_cur)

is

v_count number;

v_heiRowNum number;

v_lowRowNum number;

v_sql varchar2(200);

begin

--取记录总数

execute immediate sql_Count into v_count;

total_record := v_count;

if mod(total_record,maxNum)=0 then

total_page:=total_record/maxNum;

else

total_page:= trunc(total_record/maxNum)+1;

end if;

v_lowRowNum:=(pageNo-1)*maxNum+1;

v_heiRowNum:=pageNo*maxNum;

--分页查询语句

v_sql := 'select * from (select a.*,rownum rn from (' || exec_sql ||') a

where rownum <= ' || v_heiRowNum ||') b

where rn >=' || v_lowRowNum;

open emp_result for v_sql;

end fenye;

----留言信息处理的存储过程

procedure proc_assess(signs in number,va_id in number,vad_name in varchar2,vreply in varchar2,vdetail in varchar2,re_singn out number,mod_result out mod_cur)

is

begin

if signs=1 then

insert into hotel_assess values(hotel_assess_id.nextval,vad_name,vreply,vdetail);

re_singn:=1;

elsif signs=2 then

delete from hotel_assess where a_id=va_id;

re_singn:=1;

elsif signs=3 then

update hotel_assess set reply=vreply where a_id=va_id;

re_singn:=1;

elsif signs=4 then

if va_id=0