关于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)asbegin 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)ASV_A NUMBER :=-1;BEGINP_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);ELSeIF P_TYPE=2 THEN--计算房间类型数量 SELECT COUNT(P.PHOTOID) INTO V_A FROMPHOTOES 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;elseP_RESULT:=-1;END IF;END IF;END GETPHOTONUMBER;
页:
[1]