《oracle存储包中包含多个存储过程以及参数的传入和传出.docx》由会员分享，可在线阅读，更多相关《oracle存储包中包含多个存储过程以及参数的传入和传出.docx（14页珍藏版）》请在三一办公上搜索。
1、oracle存储包中包含多个存储过程以及参数的传入和传出-建包-用singn 1,2,3,4分别标记为增删改查drop package body hotel_packagedrop package hotel_packagecreate or replace package hotel_packageistype mod_cur is ref cursor;procedure proc_login_check(u_name in varchar2,u_pwd in varchar2,limit_id out number);procedure proc_get_fuction(signs in
2、 number,vm_id in number,vm_name in varchar2,vm_link in varchar2,vm_remark in varchar2,re_singn out number,mod_result out mod_cur);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); pr
3、ocedure 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);/*-signs2判断操作的是哪张表procedure proc_hotel_indent(signs in number,signs2 in number,voi_id in number,vr_id in number,vf_time in varchar2,ve_tim
4、e in varchar2,vo_price in number,voi_status in varchar2,voi_remark in varchar2,vol_id in number,vad_name in varchar2,vol_time in varchar2,vol_deal in varchar2,vol_status in varchar2,vol_remark in varchar2,re_signs out number,mod_result out mod_cur );*/procedure proc_assess(signs in number,va_id in n
5、umber,vad_name in varchar2,vreply in varchar2,vdetail in varchar2,re_singn out number,mod_result out mod_cur); procedure proc_userinfo(signs in number,signs2 in number,vu_id in number,vu_name in varchar2,vtelNum in varchar2,vrealName in varchar2,vgender in varchar2,vemail in varchar2,vid_card in var
6、char2,vu_cost in number,vvip_level in number,re_singn out number,mod_result out mod_cur);end hotel_package;-包主体create or replace package body hotel_packageis- 登录验证的存储过程procedure proc_login_check(u_name in varchar2,u_pwd in varchar2,limit_id out number)isad_pwd varchar2(20);beginselect ad_pwd into ad
7、_pwd from hotel_adinfo where ad_name=u_name;if ad_pwd=u_pwd thenselect limit_id into limit_id from hotel_adinfo where ad_name=u_name;elselimit_id:=0;end if; end proc_login_check; -获取功能模块的存储过程procedure proc_get_fuction(signs in number,vm_id in number,vm_name in varchar2,vm_link in varchar2,vm_remark
8、in varchar2,re_singn out number,mod_result out mod_cur)isbeginif signs=1 theninsert into hotel_module values(hotel_module_id.nextval,vm_name,vm_link,vm_remark);re_singn:=1;elsif signs=2 thendelete from hotel_module where m_id=vm_id;re_singn:=1;elsif signs=3 thenupdate hotel_module set m_name=vm_name
9、 , m_link=vm_link ,m_remark=vm_remark where m_id=vm_id;re_singn:=1;elsif signs=4 thenif vm_id=0 thenopen mod_result forselect * from hotel_module order by m_id;else- re_singn:=vm_id;open mod_result forselect * from hotel_module where m_id=vm_id;end if;end if; end proc_get_fuction;-获取酒店信息的存储过程procedu
10、re 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)isbeginif signs=1 theninsert into hotel_introduce values(hotel_introduce_id.nextval,vi_title,vi_detail,vi_remark);re_singn:=1;elsif signs=2 t
11、hendelete from hotel_introduce where i_id=vi_id;re_singn:=1;elsif signs=3 thenupdate 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 thenif vi_id=0 thenopen mod_result forselect * from hotel_introduce order by i_id;elseopen mod
12、_result forselect * 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)isv_count number;v_heiRowNum number;v
13、_lowRowNum number;v_sql varchar2(200);begin-取记录总数execute immediate sql_Count into v_count;total_record := v_count;if mod(total_record,maxNum)=0 thentotal_page:=total_record/maxNum;elsetotal_page:= trunc(total_record/maxNum)+1;end if;v_lowRowNum:=(pageNo-1)*maxNum+1;v_heiRowNum:=pageNo*maxNum;-分页查询语句
14、v_sql := select * from (select a.*,rownum rn from ( | exec_sql |) awhere rownum <= | v_heiRowNum |) bwhere 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_sing
15、n out number,mod_result out mod_cur) isbeginif signs=1 theninsert into hotel_assess values(hotel_assess_id.nextval,vad_name,vreply,vdetail);re_singn:=1;elsif signs=2 thendelete from hotel_assess where a_id=va_id;re_singn:=1;elsif signs=3 thenupdate hotel_assess set reply=vreply where a_id=va_id;re_s
16、ingn:=1;elsif signs=4 thenif va_id=0thenopen mod_result forselect * from hotel_assess order by a_id;elseopen mod_result forselect * from hotel_assess where a_id=va_id;end if;end if; end proc_assess;-会员用户管理的存储过程procedure proc_userinfo(signs in number,signs2 in number,vu_id in number,vu_name in varcha
17、r2,vtelNum in varchar2,vrealName in varchar2,vgender in varchar2,vemail in varchar2,vid_card in varchar2,vu_cost in number,vvip_level in number,re_singn out number,mod_result out mod_cur)isbeginif signs=1 theninsert into hotel_user values(hotel_user_id.nextval,vu_name,vtelNum,vrealName,vgender,vemai
18、l,vid_card,vu_cost,vvip_level);re_singn:=1;elsif signs=2 thendelete from hotel_user where u_id=vu_id;delete from hotel_adinfo where ad_name=vu_name;re_singn:=1;elsif signs=3 thenif signs2=0 thenupdate hotel_user set vip_level=vvip_level where u_id=vu_id;re_singn:=1;elsif signs2=1 thenupdate hotel_us
19、er set u_cost=vu_cost where u_id=vu_id;re_singn:=1;elsif signs2=3 thenupdate hotel_user set telNum=vtelNum,realName=vrealName,gender=vgender,email=vemail,id_card=vid_card where u_id=vu_id;re_singn:=1;end if;elsif signs=4 thenif vu_id=0 thenopen mod_result forselect * from hotel_user order by u_id;el
20、seopen mod_result forselect * from hotel_user where u_id=vu_id;end if;end if; end proc_userinfo;/* -订单信息的处理存储过程procedure proc_hotel_indent(signs in number,signs2 in number,voi_id in number,vr_id in number,vf_time in varchar2,ve_time in varchar2,vo_price in number,voi_status in varchar2,voi_remark in
21、 varchar2,vol_id in number,vad_name in varchar2,vol_time in varchar2,vol_deal in varchar2,vol_status in varchar2,vol_remark in varchar2,re_signs out number,mod_result out mod_cur )isbeginif signs=1 then- savepoint point1;insert into hotel_oinfo values(hotel_oinfo_id.nextval,vr_id,vf_time,ve_time,vo_
22、price,voi_status,voi_remark);insert into hotel_olist values(hotel_olist_id.nextval,hotel_oinfo_id.nextval-1,vad_name,vol_time,vol_deal,vol_status,vol_remark);- commit; - exception- when others then- rollback work to savepoint point1;re_signs:=1;elsif signs=2 then- savepoint point1;delete from hotel_
23、olist where ol_id=vol_id;delete from hotel_oinfo where oi_id=voi_id;- commit;- exception- when others then- rollback work to savepoint point1;re_signs:=1; elsif signs=3 thenif signs2=1 thenupdate hotel_oinfo set r_id=vr_id , f_time=vf_time ,e_time=ve_time,o_price=vo_price,oi_status=voi_status,ol_rem
24、ark=vol_remark where m_id=vm_id; re_signs:=1; elsif signs2=2 thenupdate hotel_olist set oi_id=voi_id , ad_name=vad_name ,ol_time=vol_time,ol_deal=vol_deal,ol_status=vol_status,ol_remark=vol_remark where m_id=vm_id;re_signs:=1; elsif signs2=3 thensavepoint point1;update hotel_olist set oi_id=voi_id ,
25、 ad_name=vad_name ,ol_time=vol_time,ol_deal=vol_deal,ol_status=vol_status,ol_remark=vol_remark where m_id=vm_id;update hotel_oinfo set r_id=vr_id , f_time=vf_time ,e_time=ve_time,o_price=vo_price,oi_status=voi_status,ol_remark=vol_remark where m_id=vm_id;re_signs:=1; end if;elsif signs=4 thenif signs2=1 thenopen mod_result forselect * from hotel_oinfo where ad_name=vad_name;elsif signs2=2 thenopen mod_result forselect * from hotel_olist;elsif signs2=3 thenopen mod_result forselect a.*,b.* from hotel_oinfo a,hotel_olist b;end if;end if; end proc_hotel_indent;*/end hotel_package;
