procedureimpl.txt

旅馆预订系统.采用jstl+oracle,jsp/servlet,,供初学者学习用.

create or replace package body HotelSystem is

--添加新旅馆
  procedure insertHotel(hotelname in varchar2,
                        city      in varchar2,
                        rcount    in number,
                        rprice    in number,
                        dcount    in number,
                        dprice    in number) as
  begin
    insert into hotel
    values
      (seq.nextval,
       hotelname,
       city,
       rcount,
       rprice,
       rcount,
       dcount,
       dprice,
       dcount);
  end;

--显示所有旅馆
  procedure showHotel(rs out resultSet) as
    hotelSQL long;
  begin
    hotelSQL := 'select * from hotel';
    open rs for hotelSQL;
  end;

  procedure showHotelByCity(rs out resultSet, in_city in varchar2) as
    hotelSQL long;
  begin
    hotelSQL := 'select * from hotel where city=''' || in_city || '''';
    open rs for hotelSQL;
  end;

  procedure insertResort(resortname in varchar2,
                         city       in varchar2,
                         ccount     in number,
                         cprice     in number) as
  begin
    insert into resort
    values
      (seq.nextval, resortname, city, ccount, cprice, ccount);
  end;
  procedure showResort(rs out resultSet) as
    resortSQL long;
  begin
    resortSQL := 'select * from resort';
    open rs for resortSQL;
  end;

  procedure showResortByCity(rs out resultSet, in_city in varchar2) as
    resortSQL long;
  begin
    resortSQL := 'select * from resort where city=''' || in_city || '''';
    open rs for resortSQL;
  end;

--添加或修改订单
  procedure insertOrUpdateorder(housestyle in varchar2,
                                in_id         in number,
                                roomstyle  in varchar2,
                                num        in number,
                                in_custom  in varchar2,
                                in_tel     in varchar2,
                                email      in varchar2) as
    rCount          number; --判断是更新还是插入的字段
    customid        customs.customid%TYPE; --顾客ID
    price           orders.price%TYPE; --价格
    rCountSQL       long;
    customidSQL     long;
    priceSQL        long;
    ordersUpdateSQL long;
  begin
  
    if trim(roomstyle) = '标准房' then
      begin
        priceSQL := 'select dprice from hotel where id=''' || in_id || '''';
        EXECUTE IMMEDIATE priceSQL
          into price;
        update hotel set dcanusecount = dcanusecount-num where id=in_id; --相应减去标准房可用数量
      end;
    elsif trim(roomstyle) = '豪华房' then
      begin
        priceSQL := 'select rprice from hotel where id=''' || in_id || '''';
        EXECUTE IMMEDIATE priceSQL
          into price;
        update hotel set rcanusecount = rcanusecount-num where id=in_id; --相应减去豪华房可用数量
      end;
    elsif trim(roomstyle) = '别墅' then
      begin
        priceSQL := 'select cprice from resort where id=''' || in_id || '''';
        EXECUTE IMMEDIATE priceSQL
          into price;
        update resort set ccanusecount = ccanusecount-num where id=in_id; --相应减去别墅可用数量
      end;
    else
      price := 0;
    end if;
  
    rCountSQL := 'select count(*) from customs where custom=''' ||
                 in_custom || '''and tel=''' || in_tel || '''';
    EXECUTE IMMEDIATE rCountSQL
      into rCount;   --判断数据库中有无此数据,来判断插入或更新数据
  
    if rCount = 0 then
      begin
        insert into customs
        values
          (customseq.nextval, in_custom, in_tel, email);
        insert into orders
        values
          (orderseq.nextval,
           housestyle,
           in_id,
           roomstyle,
           price * num,
           num,
           to_char(sysdate, 'YYYY-MM-DD'),
           1,
           customseq.currval);
          
      end;
    else
      begin
        customidSQL := 'select customid from customs where custom=''' ||
                       in_custom || '''and tel=''' || in_tel || '''';
        EXECUTE IMMEDIATE customidSQL
          into customid;
        ordersUpdateSQL := '
       update orders set housestyle=''' ||
                           housestyle || ''',id=''' || in_id ||
                           ''',roomstyle=''' || roomstyle || ''',price=''' ||
                           price * num || ''',num=''' || num ||
                           '''state=1,customid=''' || customid || '''';
        EXECUTE IMMEDIATE ordersUpdateSQL;
      end;
    end if;
  Exception
    when others then
      rollback;
  end;

  procedure deleteOrderByCustomName(customname in varchar2) as
  begin
    delete from orders
     where orderid =
           (select orderid from customs where custom = customname); --删除订单
  Exception
    when others then
      rollback;
  end;

  --删除订单的业务逻辑
  procedure deleteOrderByOrderId(in_orderid in number) as
  
    style        orders.housestyle%TYPE;
    roomstyle    orders.roomstyle%TYPE;
    styleSQL     long;
    roomstyleSQL long;
  begin
  
    roomstyleSQL := 'select roomstyle from orders where orderid=''' ||
                    in_orderid || '''';
    EXECUTE IMMEDIATE roomstyleSQL
      into roomstyle;
  
    styleSQL := 'select housestyle from orders where orderid=''' ||
                in_orderid || '''';
    EXECUTE IMMEDIATE styleSQL
      into style;
  
    if style = '旅馆' then
      begin
        if roomstyle = '标准房' then
          begin
            update hotel
               set rcount = rcanusecount +
                            (select num
                               from orders
                              where orderid = in_orderid)
             where id = (select id from orders where orderid = in_orderid);
          end;
        elsif style = '渡假村' then
          begin
            update hotel
               set rcount = dcanusecount +
                            (select num
                               from orders
                              where orderid = in_orderid)
             where id = (select id from orders where orderid = in_orderid);
          end;
        elsif style = '渡假村' then
          begin
            update resort
               set ccount = ccanusecount +
                            (select num
                               from orders
                              where orderid = in_orderid)
             where id = (select id from orders where orderid = in_orderid);
          end;
        end if;
      end;
    end if;
    delete from orders where orderid = in_orderid;
    commit;
  Exception
    when others then
      rollback;
  end;

--列出所有订单
  procedure showOrders(rs out resultSet) as
    showOrdersSQL long;
  begin
    showOrdersSQL := 'select o.orderid,c.custom,c.tel,c.email,h.city,h.hotelname housename,o.roomstyle,o.num,o.price from orders o,customs c,hotel h where o.customid=c.customid and h.id=o.id UNION select o.orderid,c.custom,c.tel,c.email,r.city,r.resortname housename,o.roomstyle,o.num,o.price from orders o,customs c,resort r where o.customid=c.customid and r.id=o.id';
    open rs for showOrdersSQL;
  end;

--按城市名称列出所有旅馆和渡假村的名字和ID
  procedure showHousesByCity(rs out resultSet, in_city in varchar2) as
    showHousesSQL long;
  begin
    if in_city = 'all' then
      begin
        showHousesSQL := 'select id,hotelname name,city,1 style from hotel UNION select id,resortname name,city,2 style from resort';
      end;
    else
      begin
        showHousesSQL := 'select id,hotelname name,city,1 style from hotel where city=''' ||
                         in_city ||
                         ''' UNION select resort id,resortname name,city,2 style from resort where city=''' ||
                         in_city || '''';
      end;
    end if;
    open rs for showHousesSQL;
  Exception
    when others then
      rollback;
  end;

end HotelSystem;