关于pl/sql的查询语句技巧个人总结

1，在多表联合查询时由于要查出多个表中所需的关键字段，有时候给人很复杂的感觉。
其实仔细分析一下并不难啊~~~~
a.首先列出你要的所有字段。

select t.touristid,t.touristname,t.sex,t.mobile,t.times,
       c.cardid,nt.nationname,tp.tourtypename,p.provincename,
       ct.countryid,ct.countryname,h.hotelname,rs.chamberid,
       cc.certificatetypename,bd.days,bd,indate,bd.valid

b.在列出你多需要关联的表并取其别名

 from tourist t,touristtype tp,certificate c,certificatetype cc,
        nation nt,province p,country ct,hotel h,rooms rs,
        bookdetail bd,book bk

 
c.将所有的表的字段关联起来

   where t.tourtypeid=tp.tourtypeid
     and cc.typeid=c.typeid
     and t.countryid=ct.countryid
     and t.nationid=nt.nationid
     and t.certificateid=c.certificateid
     and t.provinceid=p.provinceid
     and t.touristid=bk.touristid
     and bk.hotelid=h.hotelid
     and bk.bookid=bd.bookid
     and bd.roomid=rs.roomid

d.最后再加上此次查询的约束条件就可以了
大功告成~~~~~哈哈
完整存储过程代码如下 
--根据证件号码，及游客姓名（姓名模糊查找）查找与游客相关的所有入住信息

procedure searchfulltourinfo(p_cardid certificate.cardid%type,
                             p_touristname tourist.touristname%type,
                             b out ems_cursor)
as
begin
 open b for
 select distinct
        t.touristid,t.touristname,ct.countryid,
        cc.certificatetypename,t.sex,t.mobile,
        t.times,c.cardid,nt.nationname,tp.tourtypename,
        p.provincename,ct.countryname,h.hotelname,
        rs.chamberid,bd.days,bd.indate,bd.valid


   from tourist t,touristtype tp,certificate c,certificatetype cc,
        nation nt,province p,country ct,hotel h,rooms rs,
        bookdetail bd,book bk

   where t.tourtypeid=tp.tourtypeid
     and cc.typeid=c.typeid
     and t.countryid=ct.countryid
     and t.nationid=nt.nationid
     and t.certificateid=c.certificateid
     and t.provinceid=p.provinceid
     and t.touristid=bk.touristid
     and bk.hotelid=h.hotelid
     and bk.bookid=bd.bookid
     and bd.roomid=rs.roomid
     and (p_cardid is null or c.cardid=p_cardid)
     ----与非表达式判断游客名是否为空，如果不为空则模糊查询
     and (p_touristname is null or upper(trim(t.touristname)) 
         like'%'||upper(trim(p_touristname))||'%')
     and rownum<=60
     order by bd.indate desc;
end searchfulltourinfo;

2，模糊查询
关于模糊查询的语法如下

select * from tourist t 
     where upper(trim(t.touristname)) 
      like '%'||upper(trim(p_touristname))||'%';

3,子查询（类似于临时表）
当条件比较复杂时，可以尝试先实现一部分条件查询产生的结果集，
再从结果集中查出最后结果

--根据酒店id及房间类型id计算照片数量
PROCEDURE GETPHOTONUMBER(P_USERID NUMBER,
                         P_TYPE NUMBER,
                         P_RESULT OUT NUMBER)
AS
V_A NUMBER :=-1;
BEGIN
P_RESULT:=-1;
IF P_TYPE=1 THEN--计算酒店照片数量
      SELECT COUNT(P.PHOTOID) 
     INTO V_A FROM PHOTOES P
     WHERE EXISTS(SELECT 1/*+rule*/ FROM PHOTOES P,HOTEL H
                                           WHERE H.VALID=0
                                             AND P.VALID=0
                                             AND P.HOTELID=H.HOTELID
                                             AND P.PHOTOTYPEID=P_TYPE
                                             AND H.USERID=P_USERID);

ELSe
IF P_TYPE=2 THEN--计算房间类型数量
     SELECT COUNT(P.PHOTOID) 
    INTO V_A FROM  PHOTOES P
    WHERE EXISTS(SELECT 1/*+rule*/ FROM PHOTOES P,HOTEL H
                                          WHERE H.VALID=0
                                            AND P.VALID=0
                                            AND P.HOTELID=H.HOTELID
                                            AND P.PHOTOTYPEID=P_TYPE
                                            AND H.USERID=P_USERID);

P_RESULT:=V_A;
else
P_RESULT:=-1;
END IF;
END IF;
END GETPHOTONUMBER;