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

1，在多表联合查询时由于要查出多个表中所需的关键字段，有时候给人很复杂的感觉。
其实仔细分析一下并不难啊~~~~
a.首先列出你要的所有字段。
[code]
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
[/code]
b.在列出你多需要关联的表并取其别名
[code]
from tourist t,touristtype tp,certificate c,certificatetype cc,
nation nt,province p,country ct,hotel h,rooms rs,
bookdetail bd,book bk
[/code]
c.将所有的表的字段关联起来
[code]
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
[/code]
d.最后再加上此次查询的约束条件就可以了
大功告成~~~~~哈哈
完整存储过程代码如下
--根据证件号码，及游客姓名（姓名模糊查找）查找与游客相关的所有入住信息
[code]
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;
[/code]
2，模糊查询
关于模糊查询的语法如下
[code]
select * from tourist t
where upper(trim(t.touristname))
like '%'||upper(trim(p_touristname))||'%';
[/code]
3,子查询（类似于临时表）
当条件比较复杂时，可以尝试先实现一部分条件查询产生的结果集，
再从结果集中查出最后结果
[code]
--根据酒店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;
[/code]