ORACLE一些特性1
ROWNUM:表示行号,实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现,ORACLE的分页一般用ROWNUM实现。不像MYSQL的LIMIT,于MSSQL的TOP.
SELECT B.PROD_ID,B.PROD_NAME
FROM (SELECT ROWNUM RN,A.*
FROMTB_PROD_MAS A WHERE ROWNUM<=10) B
WHERE B.RN>5;
递归查询
1:按树型结构查询商品分类树,并同一级别按特定的顺序显示
SELECT CATE_ID ID, CATE_NM NAME, CATE_PID PID
FROMTB_PROD_CATE A
START WITHA.CATE_PID = 0
CONNECT BY PRIOR A.CATE_ID = A.CATE_PID
ORDER SIBLINGSBY A.LEVEL_SEQ
2:根据子节点获取上面所有的父节点分类
SELECT CATE_ID ID, CATE_NM NAME, CATE_PID PID
FROM TB_PROD_CATE A
START WITH A.CATE_ID=1984
CONNECT BY PRIOR A.CATE_PID=A.CATE_ID
ORACLE一些特性2
根据子节点分类获取所有的父节点分类同一级别的分类
SELECT C.CATE_ID id,C.CATE_NMname,C.CATE_PIDpId
FROM TB_PROD_CATE C,(SELECT A.CATE_ID,A.Cate_PID,LVL_NUM
FROM TB_PROD_CATE A
START WITH A.CATE_ID=1984
CONNECT BY PRIOR A.CATE_PID=A.CATE_ID) B
Where C.Cate_Pid=B.Cate_Pid
AND C.Lvl_Num=B.Lvl_Num
Order BY C.Cate_PidDESC,C.Level_Seq
WMSYS.WM_CONCAT:分组统计后连接不是GROUP BY的列
SELECT PGM_ID,WMSYS.WM_CONCAT(H.EMP_NM) SH_NM
FROM TB_PGM_USER F,TB_SYS_USER H
WHERE F.JOB_CD='B30'
AND F.USER_ID=H.USER_ID
GROUP BY PGM_ID
dbms_random.value():返回随机数
造数据时很好用,后面讲到。
ORACLE一些特性3
创建DBLINK语句:
create databaselink DEV
connect toUGOERPTEST identified by UGOERPTEST
using'(DESCRIPTION = (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.255.173)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME=WEBEDITEST ) ) )';