Oracle with使用方法以及遞迴

劉一二發表於2022-04-24
Oracle遞迴

資料準備

表結構

-- 部門表
CREATE TABLE DEPT (
    dept_no VARCHAR2(5) NOT NULL,
    dept_name VARCHAR2(255) NOT NULL,
    PRIMARY KEY(dept_no)
);
-- 新增註釋
COMMENT ON TABLE DEPT IS '部門表';
COMMENT ON COLUMN DEPT.dept_no IS '部門編碼';
COMMENT ON COLUMN DEPT.dept_name IS '部門名稱';
-- 員工表
CREATE TABLE EMP (
	emp_no VARCHAR2(8) NOT NULL,
    emp_name VARCHAR2(20) NOT NULL,
    dept_no VARCHAR2(5) NOT NULL,
    salary NUMBER(10, 2),
    PRIMARY KEY(emp_no)
);
-- 新增註釋
COMMENT ON TABLE EMP IS '員工表';
COMMENT ON COLUMN EMP.emp_no IS '員工編碼';
COMMENT ON COLUMN EMP.emp_name IS '員工名稱';
COMMENT ON COLUMN EMP.dept_no IS '所屬部門編碼';
COMMENT ON COLUMN EMP.salary IS '工資';

演示資料

-- 插入部門
insert into DEPT(dept_no, dept_name) values ('D001', '總經理部');
insert into DEPT(dept_no, dept_name) values ('D002', '人力資源部');
insert into DEPT(dept_no, dept_name) values ('D003', '行政後勤部');
insert into DEPT(dept_no, dept_name) values ('D004', '銷售一部');
insert into DEPT(dept_no, dept_name) values ('D005', '銷售二部');
insert into DEPT(dept_no, dept_name) values ('D006', '研發一部');
insert into DEPT(dept_no, dept_name) values ('D007', '研發二部');

-- 批量插入員工資料
declare
	type e_name is varray(7) of varchar2(20);
	e_name_arr e_name :=e_name('陳天龍','李曉紅','田萌','張三','李四', '王五', '趙六');
begin
	for d in 1..7 loop
		for i in 1..(d*3) Loop
			 insert into EMP(emp_no, emp_name, dept_no, salary) values (
             	'E' || d || replace(lpad(i,5),' ','0'),
                 e_name_arr(d) || i || '號',
                 'D' || replace(lpad(d,3),' ','0'),
                 trunc(dbms_random.value(3,80)) * 1000
             );
		end loop;
	end loop;
end;

基本語法

簡單的with語句:

WITH t AS 
(SELECT * FROM EMP)
SELECT * FROM t;

在檢視中使⽤WITH語句進⾏連線:

CREATE OR REPLACE VIEW V_EMP_DETAIL AS 
WITH W_DEPT AS (
    SELECT * FROM DEPT
),
W_EMP AS (
    SELECT * FROM EMP
)
SELECT d.dept_name, e.* 
FROM W_EMP e 
LEFT JOIN W_DEPT d ON d.dept_no = e.dept_no;

總結：

  • 使⽤WITH AS 語句可以為⼀個⼦查詢語句塊定義⼀個名稱，在查詢語句的其他地⽅引⽤這個⼦查詢。

  • Oracle 資料庫像對待內聯檢視或臨時表⼀樣對待 被引⽤的⼦查詢名稱，從⽽起到⼀定的優化作⽤

  • 在同級select前有多個查詢定義的時候，第1個⽤with，後⾯的不⽤with，並且⽤逗號隔開。

  • 最後⼀個with ⼦句與下⾯的查詢之間不能有逗號，只通過右括號分割,with ⼦句的查詢必須⽤括號括起來

WITH語句的優點：

  1. SQL可讀性增強。⽐如對於特定with⼦查詢取個有意義的名字等。
  2. with⼦查詢只執⾏⼀次，將結果儲存在⽤戶臨時表空間中，可以引⽤多次，增強效能。

示例

1、查詢出部門的總工資⼤於所有部門平均總工資的部門。

分析：做這個查詢，⾸先必須計算出所有部門的總工資，然後計算出所有部門的平均總工資，再篩選出部門的總工資⼤於所有部門總工資平均工資的部門。

  1. 那麼第1步 with 查詢查出所有部門的總工資
  2. 第2步⽤with 從第1 步獲得的結果表中查詢出平均工資
  3. 最後利⽤這兩次 的with 查詢⽐較總工資⼤於平均工資的結果
WITH W_DEPT_TOTAL_SALARY AS -- 查詢出部門的總⼯資
(	SELECT d.dept_name, SUM(e.salary) total_salary
    FROM DEPT d
 	JOIN EMP e ON e.dept_no = d.dept_no
    GROUP BY d.dept_name
),
W_DEPT_AVG_SALARY AS -- 查詢出部門的平均⼯資，在後⼀個WITH語句中可以引⽤前⼀個定義的WITH語句
(
	SELECT SUM(total_salary) / COUNT(1) avg_salary 
    FROM W_DEPT_TOTAL_SALARY
)
SELECT *
FROM W_DEPT_TOTAL_SALARY dts
WHERE dts.total_salary > ( -- 進⾏⽐較
    SELECT das.avg_salary 
    FROM W_DEPT_AVG_SALARY das
);

2. 統計資料並關聯到每條員工資料

展⽰根據查詢結果查詢出的資料，並把根據查詢出的結果進⾏統計，如最⼤⼯資，最⼩⼯資，平均⼯資，
進⾏級聯，由於查詢的統計資料的條數為1條，所以不會發⽣笛卡⼉積的錯誤，

WITH W_EMP AS -- 查詢基礎資料
(
    SELECT emp_no, emp_name, dept_no, salary
    FROM EMP
),
W_EMP_DATA AS -- 查詢統計資料
(	
    SELECT MAX(salary) as max_salary, 
    	MIN(salary) as min_salary, 
    	SUM(salary) as total_salary
 	FROM W_EMP
)
SELECT *
FROM W_EMP, W_EMP_DATA -- 進⾏級聯，由於查詢的統計資料的條數為1條，所以不會發⽣笛卡⼉積的錯誤

3. 後⾯的with定義可以引⽤前⾯的結果集，但是with⼦查詢不可巢狀定義。

下⾯的語句錯誤：因為不允許巢狀定義with語句

WITH W_EMP_2 AS
-- with中有巢狀with,不允許
(
	WITH W_EMP AS
   	(
		SELECT emp_name FROM EMP WHERE emp_no='E100001'
    )
	SELECT emp_name FROM W_EMP
)
SELECT * FROM W_EMP_2;

遞迴案例

  1. 實現從1到10的輸出

    with w_num(n) as (
	select 1 as n from dual
    union all
    select n+1 from w_num where n<10
)
select n from w_num;

  2. 空瓶換啤酒最多能喝幾瓶問題

    /**
	2元1瓶啤酒
	4個瓶蓋換1瓶啤酒
	2個空瓶換1瓶啤酒
	問：10元可以喝幾瓶
*/
with w_drink_beer(beer, bottle, lid) AS
(
	select 10/2 as beer, 10/2 as bottle, 10/2 as lid
    from dual
    union all
    select 
    	beer + trunc(bottle/2) + trunc(lid/4) as beer,
    	mod(bottle, 2) + trunc(bottle/2) + trunc(lid/4) as bottle,
    	mod(lid, 4) + trunc(bottle/2) + trunc(lid/4) as lid
    from w_drink_beer
    where trunc(bottle/2) != 0 or trunc(lid/4) != 0
)
select beer as '喝了幾瓶啤酒', bottle as '剩下幾個瓶子', lid as '剩下幾個瓶蓋'
from w_drink_beer;

遞迴-地鐵線路換乘問題

SQL案例分析：地鐵換乘線路查詢

示例表和指令碼下載：https://github.com/dongxuyang1985/sql_in_action

-- Oracle
WITH transfer (start_station, stop_station, stops, path) AS (
  SELECT station_name, next_station, 1, line_name||station_name||'->'||line_name||next_station
    FROM bj_subway WHERE station_name = '王府井'
   UNION ALL
  SELECT p.start_station, e.next_station, stops + 1, p.path||'->'||e.line_name||e.next_station
    FROM transfer p
    JOIN bj_subway e
      ON p.stop_station = e.station_name AND (INSTR(p.path, e.next_station) = 0)
)
SELECT * FROM transfer WHERE stop_station ='積水潭';

相關文章