創(chuàng)建用戶
-- Create the user 
create user JXTELE_HOMS
identified by "123456"
default tablespace HOMS
temporary tablespace TEMP
profile DEFAULT;
-- Grant/Revoke role privileges 
grant connect to JXTELE_HOMS with admin option;
grant dba to JXTELE_HOMS with admin option;
grant resource to JXTELE_HOMS with admin option;
-- Grant/Revoke system privileges 
grant unlimited tablespace to JXTELE_HOMS with admin option;

創(chuàng)建視圖
create or replace view inpass_encape_byid as
select p.*,e.ESCAPECODE     ,e.ESCAPE_NAME    ,e.ESCAPE_SEX     ,e.ESCAPE_BIRTHDAY,e.ESCAPE_IDCARD ,e.ESCAPE_AREA    ,
e.ESCAPE_ADDRESS ,e.REASON         ,e.WATCHTIME      ,
e.WATCHUNIT      ,e.SIGN           ,e.AGE            ,e.CALLPHONE      ,e.CALLTELEPHONE ,e.WATCHUNITCODE ,h.hotelname 
from in_passenger p join escape e on p.cardcode=e.escape_idcard join hotel h on p.hotelcode=h.hotelcode;

create or replace view in_out_passstatic as
select code,name,count(pcode) cnt,intime,decode(a.roomcount,null,'0',a.roomcount) roomcount
from (select h.hotelcode code,h.hotelname name,substr(i.in_datetime,0,8) intime,i.in_passengercode pcode,h.roomcount roomcount
from IN_Passenger i right join Hotel h on i.hotelcode=h.hotelcode     
group by h.HotelCode,substr(i.in_datetime,0,8),h.hotelname,i.in_passengercode,h.roomcount
   union select t.hotelcode code,t.hotelname name,substr(p.in_datetime,0,8) intime,p.out_passengercode pcode,t.roomcount roomcount
    from Out_Passenger p right join Hotel t   on   p.hotelcode=t.hotelcode   
     group by t.HotelCode,substr(p.in_datetime,0,8),t.hotelname,p.out_passengercode,t.roomcount ) a 
     group by a.code,a.intime,a.name,a.roomcount order by a.code;

create or replace view in_passstatic as
select code,name,count(pcode) cnt,intime,decode(a.roomcount,null,'0',a.roomcount) roomcount 
from (select h.hotelcode code,h.hotelname name,substr(i.in_datetime,0,8) intime,i.in_passengercode pcode,h.roomcount
from IN_Passenger i right join Hotel h on i.hotelcode=h.hotelcode 
   group by h.HotelCode,substr(i.in_datetime,0,8),h.hotelname,i.in_passengercode,h.roomcount) a
    group by a.code,a.intime,a.name,a.roomcount order by a.code;

create or replace view out_passstatic as
select code,name,count(pcode) cnt,intime,decode(a.roomcount,null,'0',a.roomcount) roomcount 
from (select t.hotelcode code,t.hotelname name,substr(p.in_datetime,0,8) intime,p.out_passengercode pcode,t.roomcount 
from Out_Passenger p right join Hotel t   on   p.hotelcode=t.hotelcode   
group by t.HotelCode,substr(p.in_datetime,0,8),t.hotelname,p.out_passengercode,t.roomcount ) a 
group by a.code,a.intime,a.name,a.roomcount order by a.code;