1、取交集的是intersect
2、minus的作用简单来说是去同留异,例:select * from table where rownum<10 minus se...
3、Union不用多说,关联合并
4、
OracleSQL.oracle中length()与lengthb()区别
SQL> select length('阿猪') from dual;
LENGTH('阿猪')
--------------
2
SQL> select lengthb('阿猪') from dual;
LENGTHB('阿猪')
---------------
4
区别:length求得是字符长度,lengthb求得是字节长度。
5、关联查询一个希望排序的结果
Select A.BarCode,A.PluCode,A.PluName,A.PluAbbrName,A.Unit,A.Spec,
Nvl((Select Sum(KcCount) From tStkLsKc Where OrgCode='1008' And PluId=A.PluID And PluType='0'),0) as KcCoount,
Nvl((Select Price From tSkuPluEx Where OrgCode='1008' And PluID=A.PluId),0) as Price,
(Select YhPrice
From (Select PluId,YhPrice,Row_NUmber() over(partition by OrgCode,PluId order by Pri desc) as RowNo
From tProCxPtPlu
Where OrgCode='1008'
And PosComUpType<>'2' And IsEnabled='1'
And to_char(SysDate,'yyyy-mm-dd') Between BgnDate And EndDate
And to_char(SysDate,'hh24:mi:ss') between BgnTime And EndTime)
Where PluId=A.PluID And RowNo=1
) as YhPrice
From tSkuPlu A
Where A.pluid=10010000026626;
6、Oracle的loop循环中,11G之前不支持continue
解决方法有二:
法1:
loop
fetch c1 into c1_rec
EXIT WHEN c1%NOTFOUND;
if 某个条件
then
--模拟continue.跳到下一个循环
null;
else
do something
end if;
end loop;