【12c-新特性篇】Select语句增强特性

1 可以通过指定偏移量、行数或行数的百分比来返回前n行记录

1）12c之前，获取前n行记录：

SQL> SELECT * FROM (SELECT * FROM scott.emp ORDER BY empno) WHERE rownum < 3;

     EMPNO ENAME      JOB	       MGR HIREDATE		      SAL	COMM	 DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 1980-12-17 00:00:00	      800		     20
      7499 ALLEN      SALESMAN	      7698 1981-02-20 00:00:00	     1600	 300	     30

2）12c，获取前n行记录：

SQL> SELECT * FROM scott.emp ORDER BY empno FETCH FIRST 2 rows ONLY;

     EMPNO ENAME      JOB	       MGR HIREDATE		      SAL	COMM	 DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 1980-12-17 00:00:00	      800		     20
      7499 ALLEN      SALESMAN	      7698 1981-02-20 00:00:00	     1600	 300	     30

3）12c，指定偏移量，获取前n行记录：

SQL> SELECT * FROM scott.emp ORDER BY empno OFFSET 2 ROWS FETCH FIRST 2 rows ONLY;

     EMPNO ENAME      JOB	       MGR HIREDATE		      SAL	COMM	 DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7521 WARD       SALESMAN	      7698 1981-02-22 00:00:00	     1250	 500	     30
      7566 JONES      MANAGER	      7839 1981-04-02 00:00:00	     2975		     20

4）12c，获取百分之n行记录：

SQL> SELECT * FROM scott.emp ORDER BY empno FETCH NEXT 10 PERCENT rows ONLY;

     EMPNO ENAME      JOB	       MGR HIREDATE		      SAL	COMM	 DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 1980-12-17 00:00:00	      800		     20
      7499 ALLEN      SALESMAN	      7698 1981-02-20 00:00:00	     1600	 300	     30

2 查询中指定横向内联视图

SQL> SELECT * FROM  scott.emp t1, lateral(SELECT * FROM scott.dept t2 WHERE t1.deptno=t2.deptno);

     EMPNO ENAME      JOB	       MGR HIREDATE		      SAL	COMM	 DEPTNO     DEPTNO DNAME	  LOC
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ---------- -------------- -------------
      7782 CLARK      MANAGER	      7839 1981-06-09 00:00:00	     2450		     10 	10 ACCOUNTING	  NEW YORK
      7839 KING       PRESIDENT 	   1981-11-17 00:00:00	     5000		     10 	10 ACCOUNTING	  NEW YORK
      7934 MILLER     CLERK	      7782 1982-01-23 00:00:00	     1300		     10 	10 ACCOUNTING	  NEW YORK
      7566 JONES      MANAGER	      7839 1981-04-02 00:00:00	     2975		     20 	20 RESEARCH	  DALLAS
      7902 FORD       ANALYST	      7566 1981-12-03 00:00:00	     3000		     20 	20 RESEARCH	  DALLAS
      7876 ADAMS      CLERK	      7788 1987-05-23 00:00:00	     1100		     20 	20 RESEARCH	  DALLAS
      7369 SMITH      CLERK	      7902 1980-12-17 00:00:00	      800		     20 	20 RESEARCH	  DALLAS
      7788 SCOTT      ANALYST	      7566 1987-04-19 00:00:00	     3000		     20 	20 RESEARCH	  DALLAS
      7521 WARD       SALESMAN	      7698 1981-02-22 00:00:00	     1250	 500	     30 	30 SALES	  CHICAGO
      7844 TURNER     SALESMAN	      7698 1981-09-08 00:00:00	     1500	   0	     30 	30 SALES	  CHICAGO
      7499 ALLEN      SALESMAN	      7698 1981-02-20 00:00:00	     1600	 300	     30 	30 SALES	  CHICAGO

     EMPNO ENAME      JOB	       MGR HIREDATE		      SAL	COMM	 DEPTNO     DEPTNO DNAME	  LOC
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ---------- -------------- -------------
      7900 JAMES      CLERK	      7698 1981-12-03 00:00:00	      950		     30 	30 SALES	  CHICAGO
      7698 BLAKE      MANAGER	      7839 1981-05-01 00:00:00	     2850		     30 	30 SALES	  CHICAGO
      7654 MARTIN     SALESMAN	      7698 1981-09-28 00:00:00	     1250	1400	     30 	30 SALES	  CHICAGO

14 rows selected.

3 With中定义PL/SQL函数或存储过程，Select语句中引用

1）定义函数，在查询语句引用

SQL> with 
  2  function str_to_lower(pi_str in varchar2) return varchar2 is
  3  begin 
  4  return lower(pi_str);
  5  end;
  6  select str_to_lower(ename) ename,job,mgr,sal,deptno from scott.emp where empno=7369;
  7  /
ENAME	   JOB		    MGR        SAL     DEPTNO
---------- --------- ---------- ---------- ----------
smith	   CLERK	   7902        800	   20

2）递归查询

查询直接或间接向101汇报的雇员

SQL> conn hr/hr@orcl
Connected.
SQL> WITH reports_to_101(eid,emp_last,mgr_id,reportlevel) AS
 (SELECT employee_id,
         last_name,
         manager_id,
         0 reportlevel
    FROM employees
   WHERE employee_id = 101
  UNION ALL
  SELECT e.employee_id,
         e.last_name,
         e.manager_id,
         reportlevel + 1
    FROM reports_to_101 r,
         employees      e
   WHERE r.eid = e.manager_id)
SELECT eid,
       emp_last,
       mgr_id,
       reportlevel
  FROM reports_to_101
 ORDER BY reportlevel,eid;  

       EID EMP_LAST			 MGR_ID REPORTLEVEL
---------- ------------------------- ---------- -----------
       101 Kochhar			    100 	  0
       108 Greenberg			    101 	  1
       200 Whalen			    101 	  1
       203 Mavris			    101 	  1
       204 Baer 			    101 	  1
       205 Higgins			    101 	  1
       109 Faviet			    108 	  2
       110 Chen 			    108 	  2
       111 Sciarra			    108 	  2
       112 Urman			    108 	  2
       113 Popp 			    108 	  2

       EID EMP_LAST			 MGR_ID REPORTLEVEL
---------- ------------------------- ---------- -----------
       206 Gietz			    205 	  2

12 rows selected.

查询整个组织的关系

SQL> WITH
  org_chart (eid, emp_last, mgr_id, reportLevel, salary, job_id) AS
  (
    SELECT employee_id, last_name, manager_id, 0 reportLevel, salary, job_id
    FROM employees
    WHERE manager_id is null
  UNION ALL
    SELECT e.employee_id, e.last_name, e.manager_id,
           r.reportLevel+1 reportLevel, e.salary, e.job_id
    FROM org_chart r, employees e
    WHERE r.eid = e.manager_id
  )
  SEARCH DEPTH FIRST BY emp_last SET order1
SELECT lpad(' ',2*reportLevel)||emp_last emp_name, eid, mgr_id, salary, job_id
FROM org_chart
ORDER BY order1; 

EMP_NAME					    EID     MGR_ID     SALARY JOB_ID
-------------------------------------------------- ---- ---------- ---------- ----------
King						    100 		24000 AD_PRES
  Cambrault					    148        100	11000 SA_MAN
    Bates					    172        148	 7300 SA_REP
    Bloom					    169        148	10000 SA_REP
    Fox 					    170        148	 9600 SA_REP
    Kumar					    173        148	 6100 SA_REP
    Ozer					    168        148	11500 SA_REP
    Smith					    171        148	 7400 SA_REP
  De Haan					    102        100	17000 AD_VP
    Hunold					    103        102	 9000 IT_PROG
      Austin					    105        103	 4800 IT_PROG
---------------省略------------