分組聚合函數(shù)詳細(xì)講解

1、學(xué)習(xí)必備 歡迎下載 聚合函數(shù)總結(jié) 在實(shí)際 SQL 應(yīng)用中,經(jīng)常需要進(jìn)行分組聚合,即將查詢對象按確定條件分組,然后對每一 個組進(jìn)行聚合分析；比如我們常見的一些分組運(yùn)算需求：求某個部門的薪資總和,薪資平均 值,薪資最大值等等； 分組聚合函數(shù)也可稱之為多行函數(shù),它的輸入是多個行構(gòu)成得一個行集（這個行集可以 是一張表的全部行,也可以是依據(jù)某個維度進(jìn)行分組后的某一組行）,而輸出都是一個值； 1. 分組聚合函數(shù)語法以及 SQL 語句執(zhí)行過程 SQL 中使用分組聚合函數(shù)的語法 SELECT column, group_functioncolumn, . FROM tableWHERE condition G

2、ROUP BY columnhaving ORDER BY column; 在 select 語句中使用 group by 子句將行劃分成較小的組, 然后,使用聚組函數(shù)返回每 一個組的匯總信息,另外,可以使用 having 子句限制返回的結(jié)果集； 查詢語句的 select 和 groupby,having,Order by子句是分組聚合函數(shù)唯獨(dú)顯現(xiàn)的地方, 在 where 子句中不能使用分組聚合函數(shù)； select department_id, sumsalary from employees where salary 10000 group by department_id having s

3、um salary 11000 order by sum salary sql 語句執(zhí)行過程 （ 1） 按 select 找到 where 中意條件的元組形成結(jié)果相當(dāng)于： 表； select department_id, salary from employees where salary 10000 第 1 頁,共 22 頁學(xué)習(xí)必備 歡迎下載 （ 2） 將結(jié)果表按 group 中指定的列進(jìn)行分組,該屬性列值相等的元組為一組,通常 在每組中作用集函數(shù)； （假如 group 指定多個列,先按第一列分組,然后在每個組中再按其次 列分組） 相當(dāng)于： select department_id, sum

4、salary from employees where salary 10000 group by department_id （ 3） 假如 group 子句帶 having 短語,就分組聚合后只有滿having 指定條件的組才輸 出； 意 相當(dāng)于： select department_id, sumsalary from employees where salary 10000 group by department_id having sum salary 11000 第 2 頁,共 22 頁學(xué)習(xí)必備 歡迎下載 （ 4） 假如有 order 子句,就將結(jié)果表按 order 子句指定的列升

5、序或降序排序； 相當(dāng)于： select department_id, sumsalary from employees where salary 10000 group by department_id by having sum salary 11000 order by sum salary 可選項(xiàng) 什么時候可以無：非分組查詢中 非分組查詢中,聚合函數(shù)實(shí)際上等于將表中全部記錄作 為一個組來運(yùn)算；此時在 select 列表中指定的列只能是包含聚組函數(shù), 不能包含數(shù)據(jù)表本身的列； 比如求全部員工的總工資； select sum salary from employees 什么時候必需有：在分組

6、查詢中 分組查詢中,聚合函數(shù)是將數(shù)據(jù)按分組關(guān)鍵字分組, 然后對每一組的函數(shù)自變量中的內(nèi) 容進(jìn)行聚合運(yùn)算； Select 子句字段可以是分組關(guān)鍵字 如求各個部門員工總工資； 留意： （group by 后面字段） 和聚合函數(shù)； 比 a. 假如沒有 group by 子句, select 列表中不答應(yīng)顯現(xiàn)字段與分組函數(shù)混用的情形； b. 在帶有 group by 子句的查詢語句中,在 select 列表中指定的列要么是 group by 子 句 中指定的列,要么包含聚組函數(shù)；顯現(xiàn)在 select 列表中的字段,假如不是包含在分組函數(shù) 中,那么該字段必需同時出在 Group by 子句中； by 后

7、面字段的次序不同分組結(jié)果不同； 3. 條件查詢（ where 和 having ） 在一個 sql 語句中可以有 where 子句和 having 子句； having 與 where 子句類似,均 用于設(shè)置限定條件； 第 3 頁,共 22 頁學(xué)習(xí)必備 歡迎下載 where 子句的作用是在對查詢結(jié)果進(jìn)行分組前, 將不符合 where 條件的行去掉, 即在分 組之前過濾數(shù)據(jù),條件中不能包含聚組函數(shù),使用 where 條件顯示特定的行； having 子句的作用是選擇中意條件的組,即在分組之后過濾數(shù)據(jù),條件中經(jīng)常包含聚組 函數(shù),使用 having 條件顯示特定的組,也可以使用多個分組標(biāo)準(zhǔn)進(jìn)行分組；

8、 group by 可以與 where 來搭配使用, where 只能在 group by 的前面, group by 后面 不能使用 where；where 只針對單記錄,不針對記錄組的搜尋條件,子句能包括分組函數(shù),當(dāng) 用戶要指定一個記錄組的搜尋條件只能使用 having 表示； 4. 常用的聚合函數(shù) 計(jì)數(shù)（ COUN）T COUNT函數(shù)用來運(yùn)算表中記錄的個數(shù) count* 或者列中值的個數(shù) countcolumn , 運(yùn)算內(nèi)容由 SELEC語 T 句指定；使用 COUN函T 數(shù)時,必需指定一個列的名稱或者使用星號, 星號 表示運(yùn)算一個表中的全部記錄；兩種使用形式如下； *COUNT*,運(yùn)算

9、表中行的總數(shù),即使表中行的數(shù)據(jù)為 NULL,也被計(jì)入在內(nèi)； *COUNTcolumn,運(yùn)算 column 列包含的行的數(shù)目,假如該列中某行數(shù)據(jù) NULL,就該行 為 不計(jì)入統(tǒng)計(jì)總數(shù)； 留意： COUNT函數(shù)只對那些傳遞到函數(shù)（括號）中的參數(shù)不是 NULL行計(jì)數(shù)； Select count1 和 Select 返回的結(jié)果一樣； 的 count* 1 使用 COUNT*函數(shù)對表中的行數(shù)計(jì)數(shù)； COUNT*函數(shù)將返回中意 SELEC語 T 句的 WHER子E 句中的搜尋條件的函 示例 查詢 employees數(shù)； 表 中的全部記錄的行數(shù)；實(shí)例代碼： select count * from emplo

10、yees 在該例中, SELEC 語 T 句中沒有 WHER子E 句,那么認(rèn)為表中的全部行都滿 SELEC語 T 句,意 所 以 SELEC語 假如 DBM 在 T 句將返回表中全部行的計(jì)數(shù)； S 其系統(tǒng)表中儲備了表的行數(shù),COUNT*將很快地返回表的行數(shù),由于這時, DBM 不 S 必從頭到尾讀取表,并對物理表中的行計(jì)數(shù),而直接從系統(tǒng)表中提取行的計(jì)數(shù)；而如果 DBM 沒 S 有在系統(tǒng)表儲備表的行數(shù),將具 NOT NUL約L束的列作為參數(shù),使 COUN函T 數(shù),有 用 就 可能更快地對表行計(jì)數(shù)； （ 2）使用 COUNTcolumn 函 數(shù)對一列中的數(shù)據(jù)計(jì)數(shù)； COUNTcolumn函數(shù)可用于

11、對一列中 的數(shù)據(jù)值計(jì)數(shù)； 與忽視了全部列的 COUNT*函數(shù)不同, COUNTcolumn函 數(shù)逐一檢查一列（或多列）中的值,并對那些值不 NULL的行計(jì)是 實(shí)例 查詢多列中全部記錄的行數(shù) 數(shù)； SELECT COUNeTmployee_id FROMemployees; （ 3）使用 COUNTcolumn 函 數(shù)同時對多列中的數(shù)據(jù)計(jì)數(shù) COUNTcolumn 函數(shù)不僅可用于對一列中的數(shù)據(jù)值計(jì)數(shù),也可以對多列中的數(shù)據(jù)值計(jì)數(shù)； 假如對多列計(jì)數(shù),就需要將要計(jì)數(shù)的多列連接后,作為 COUNTcolumn 函數(shù)的參數(shù)； 實(shí)例 使用 COUNTcolumn 函數(shù)對多列中的數(shù)據(jù)計(jì)SELECT COUNT

12、 employee_id 數(shù) AS c_eno, -employee_id 不為空的記錄個數(shù) COUNTsalary AS c_salary, -salary 不為空的記錄個數(shù) COUNTemployee_id+salary AS t_eno_salary - 兩者都不為空的個數(shù) 第 4 頁,共 22 頁學(xué)習(xí)必備 歡迎下載 FROM employees; 在進(jìn)行兩列的連接時,假如它們的數(shù)據(jù)類型不一樣,要使用 CAST表達(dá)式將它們轉(zhuǎn)換成同的數(shù)據(jù)類型； 相 SELECT COUNT employee_id AS c_eno, -employee_id 不為空的記錄個數(shù) -department_id

13、 不為空的記錄個數(shù) COUNTdepartment_id AS c_dno, - 兩者都不為空的個數(shù) COUNTemployee_id+ cast department_id as int as c_eno_dno FROM employees 假如在被連接的列中的任何一列有 NULL 值時,那么連接的結(jié)果NULL,就該列不會被 COUNT 函數(shù)計(jì)數(shù)； 為 求和 SUM 求和函數(shù) SUM 用于對數(shù)據(jù)求和,返回選取結(jié)果集中全部值的總和；語法如下 SELECT SUMcolumn_name FROMtable_name 說明： 適用范疇： SUM函數(shù)只能作用于數(shù)值型數(shù)據(jù),即列 column_nam

14、e 中的數(shù)據(jù)必是數(shù)值型 需 的； 對 NULL值的處理： 當(dāng)對某列數(shù)據(jù)進(jìn)行求和值； 時, 示例：查找員工總工資大于 10000 的部select department_id, sumsalary 門 from employees group by department_id having sum salary 10000 order by department_id; 假如該列存在 NULL值,就 SUM函數(shù)會忽視 該求平均值 AVG 函數(shù) AVG用于運(yùn)算一列中數(shù)據(jù)值的平 均值；語法如下 SELECT AVG column_name FROM table_name 說明： 適用范疇：與 SUM

15、 函數(shù)一樣,的數(shù)據(jù)必需是數(shù)值型的； AVG函數(shù)只能作用于數(shù)值型數(shù)據(jù),即列 column_name 中 對 NULL值的處理：在運(yùn)算平均值 AVG函數(shù)將忽視 NULL值； AVG函數(shù)的執(zhí)行過程際上是將一列中的值加起來,再將其和 除以非 NULL 值的數(shù)目 ,等價(jià)于 時, 實(shí) count column ；假如在某列中,全部行的值都是 sumcolumn/ NULL,就 AVG函數(shù)將返回 NULL示例： 值； select avg salary as AVG1, sumsalary / count * as AVG2, sumsalary / count salary as AVG3, FROMem

16、ployees 運(yùn)行結(jié)果如以下圖 第 5 頁,共 22 頁學(xué)習(xí)必備 歡迎下載 可以發(fā)覺得到了不同的結(jié)果； 實(shí)際上, “ avgsalary ”與“ sumsalary/ s countsalaary”語句是等價(jià)的；由于 avgsalary 語句的執(zhí)行過程實(shí)際上是將 salary 列中的值加起來, 再將其和除以非 NULL 值的目（也就等價(jià)于 count salary ）；而語句“ sumsalaray / counnt * ”就不然, 由于 COUNT* 返回的是表中全部記錄的個數(shù),而不管 salary 列中的數(shù)值是否為 NULL； 求最大值 MAX,最小值 MIN 當(dāng)需要明白一列中的最大值時

17、,可以使用 MAX函數(shù)；同 樣,當(dāng)需要明白一列中的最小值 時,可以使用 MIN 函數(shù)；語法如下； SELECT MAX column_name / MIN 說明： column_name FROM table_name 適用范疇：列 column_name 中的數(shù)據(jù)可以是數(shù)值,字符串或是日期時間數(shù)據(jù)類型； MAX/MIN 函數(shù)將返回與被傳遞的列同一數(shù)據(jù)類型的單一值； 對 NULL值的處理：確定列中的最大值 （最小值）時,MAX,MIN 函數(shù)忽視 NULL值；但假如在該列中,全部行的值都是 NULL,就 MAX,MIN 函數(shù)將返回 NULL 示例：獲得各種工作的最高工資和最低工資 值； grou

18、p by SELECT job_idi, maxsalary, , min salary FROM employees job_id; 留意： 在字符串?dāng)?shù)據(jù)類型中使用 MAX和 MIN 時,對字符型數(shù)據(jù)的最大值,是依據(jù)首字母AZ 的次序排列,越往后,其值越大；當(dāng)然,對于漢字就是依據(jù)其全拼拼音排列的,如首字符相 由 同,就比較下一個字符,以此類推,返回結(jié)果的數(shù)據(jù)類型為字符型數(shù)據(jù)； 在日期數(shù)據(jù)類型中使用 MAX和 MIN 時,對日期時間類型的數(shù)據(jù)也可以求其最 其大小排列就是日期時間的早晚,越早認(rèn)為其值越小,返回結(jié)果的數(shù)據(jù)類型為日期型； 大 / 最小值, 4.5 聚合函數(shù)的重值處理 前面介紹的幾種聚

19、合函數(shù),可以作用于所選列中的全部數(shù)據(jù)（不 管列中的數(shù)據(jù)是否有重 置）,也可以只對列中的非重值進(jìn)行處理,即把重復(fù)的值只取一次進(jìn)行聚合分析；當(dāng)然,對 于 MAX/MIN 函數(shù)來講,重值處理意義不大； 可以使用 ALL 關(guān)鍵字指明對所選列中的全部數(shù)據(jù)進(jìn)行處理,使DISTINCT 關(guān)鍵字指明 對 所選列中的非重值數(shù)據(jù)進(jìn)行處理；以 用 COUNT函 數(shù)為例,語法如下；SELECT COUNTALL/DISTINCT column_nameFROM table_name 說明： ALL/DISTINCT 在缺省狀態(tài)下, 默認(rèn)是 ALL 關(guān)鍵字 ,即不管是否有重 值,處理全部數(shù)據(jù)；其他聚合函數(shù)的用法與此相同

20、； 例如查詢工資部門編號列中存在的不同記錄的數(shù)目： select count distinct ddepartmentt_id FROMMemployeees; 5ROLLUP運(yùn)算符和 cube運(yùn)算符 第 6 頁,共 22 頁學(xué)習(xí)必備 歡迎下載 ROLLUP 運(yùn)算符 在 Group By 中使用 可以產(chǎn)生常規(guī)分組匯總行以及分組小計(jì)； Rollup 假如是 ROLLUPA,B, C的話,第一會對 A ,B,C進(jìn)行 GROUBPY,然后對 A,B進(jìn)行 GROUP BY,然后是 A 進(jìn)行 GROUP B,Y 然后對全表進(jìn)行 GROUP B操Y 作 , 最終進(jìn)行 union操作； Rollup 后面跟

21、了 n 個字段,就將進(jìn)行 n+1 次分組,從左到右每次削減一個字段進(jìn)行分然后進(jìn)行 union ； Rollup 一個字sumsalary from employees group by 段： select department_id, rollup department_id; 進(jìn)行 GROUP BY結(jié), 果等價(jià)employees group by 第一會對 department_id 于： sumsalary from select department_id, department_id order by department_id; 然后對全表進(jìn)行 GROUP B操Y 作,結(jié)果等價(jià)sel

22、ect sum salary from employees 最終進(jìn)行 union 操作； 意義：第一依據(jù)部門編號分組運(yùn)算工資, 這是常規(guī)分組函數(shù)無法做到的； 留意：由于然后對分組進(jìn)行匯總合計(jì), 并且一次顯現(xiàn)結(jié)果, 其次步中沒有 department_id 字段,和第一步產(chǎn)生的表 union 時,department_id 字段為空,以此類推； Rollup 多個字sumsalary from employees group by 段 select department_id,job_id, rollup department_id,job_id; 第一會對 department_id,job_

23、id select department_id, job_id, from employees 進(jìn)行 GROUP B,Y 相當(dāng) 于sumsalary group by department_id, job_id 學(xué)習(xí)必備 歡迎下載 order by department_id, job_id; 然后對 department_id 進(jìn)行 GROUP B,Y 相當(dāng)于select department_id, from employees sumsalary group by department_id order by department_id; 然后對全表進(jìn)行 GROUP B操Y 作,相當(dāng)于

24、select sum salary from employees 最終 union ； 1a4b2c 3意義： a 是對各個部門依job 分組進(jìn)行薪資匯總； b 是對各個部門總體進(jìn)行薪資匯c 是對全部部據(jù) 總； 門 進(jìn)行薪資匯總； 5.2 cube 運(yùn)算符 在 Group By 中使用 Cube 可以產(chǎn)生 Rollup 結(jié)果集 +和多維度的交叉表數(shù)據(jù)源； GROUP BY CUBEA, B, C,第一會對 A,B,C進(jìn)行 GROUP B,Y 然后依次是 A, B,A, C,A ,B,C,B ,C,最終對全表進(jìn)行 GROUP B操Y 作； Cube 后面跟了 n 個字段,就將進(jìn)行 2 的 N 次

25、方的分組運(yùn)算,然后進(jìn) union ； 行 第 8 頁,共 22 頁SELECT department_id, job_id, 學(xué)習(xí)必備 歡迎下載 SUMsalary FROM employees GROUP BY cube department_id, job_id; 第一會對 department_id,job_id 進(jìn)行 GROUP B,Y 相當(dāng)于 select department_id, job_id, sumsalary from employees group by department_id, job_id order by department_id, job_id; -24

26、rows 然后對 department_id 進(jìn)行 GROUP B,Y 相當(dāng)于select department_id, from employees sumsalary group by department_id order by department_id; -12 rows 然后對 job_id 進(jìn)行 GROUP B,Y 相當(dāng)于select job_id, sumsalary from employees group by job_id order by job_id; -20 rows 然后對全表進(jìn)行 GROUP B操Y 作,相當(dāng)于 select sum salary from em

27、ployees; -1 rows 最終 union ；-57 rows GROUPING函數(shù) Rollup 和 Cube 有點(diǎn)抽象,他分別相當(dāng) n+1 和 2 的 n 次方常規(guī) Group 運(yùn)算；那么在 Rollup 和 Cube 的結(jié)果集中如何很明確的看出哪些行是針對那些列或者列的組合進(jìn)行分組 by 算的結(jié)果的？ 答案是可以使用 Grouping 函數(shù)； 沒有被 Grouping 到返回 1,否就返回 0； 舉例如下： SELECT department_id, job_id,SUMsalary, GROUPINGdepartment_id GRP_DEPT, GROUPINGjob_id

28、GRP_JOB FROM employees GROUP BY ROLLUP department_id, job_id; 第 9 頁,共 22 頁學(xué)習(xí)必備 歡迎下載 5.4 Grouping Set可以使用 Grouping Set 來代替多次 UNION: Grouping Set 單一字段 SELECT department_id, job_id, FROM employees sumsalary GROUP BY GROUPING SETS department_id, job_id; 相當(dāng)于先按 department_id 分組,等價(jià)于 SELECT department_id, s

29、umsalary FROM employees GROUP BY department_id 然后按 job_id 分組,等價(jià)于 SELECT job_id, sumsalary FROM employees GROUP BY job_id 最終結(jié)果 union all 到一起； 第 10 頁,共 22 頁學(xué)習(xí)必備 歡迎下載 sumsalary Grouping Set 組合字段 SELECT department_id, job_id, manager_id,FROM employees GROUPBY GROUPINGSETSdepartment_id, job_id,job_id, ma

30、nager_id; 相當(dāng)于先按 department_id,job_id 分組 SELECT department_id, job_id, sumsalary FROM employees GROUP BY department_id,job_id 然后按 job_id, manager_id 分組 sumsalary SELECT job_id, manager_id,FROM employees GROUP BY job_id, manager_id; 最終 union all ； 第 11 頁,共 22 頁學(xué)習(xí)必備 歡迎下載 12 3第 12 頁,共 22 頁學(xué)習(xí)必備 歡迎下載 6. 自

31、定義集合函數(shù) 6.1 原理介紹 Oracle 供應(yīng)了很多預(yù)定義好的集合函數(shù),比如前面講的 Max, Sum, AVG , 但是這 些預(yù)定義的集合函數(shù)基本上都是適應(yīng)于標(biāo)量數(shù)據(jù), 的 Object type, Clob 等,是不支持的； 對于復(fù)雜的數(shù)據(jù)類型, 比如說用戶自定義 但是,幸運(yùn)的是, Oracle 的定制功能特別強(qiáng)大,答應(yīng)用戶定制自己的集合函數(shù)；用戶可 以通過實(shí)現(xiàn) Oracle 的 Extensibility Framework 中的 ODCIAggregate interface 來創(chuàng)建自定 義集合函數(shù),而且自定義的集合函數(shù)跟內(nèi)建的集合函數(shù)用法上沒有差別； 通過實(shí)現(xiàn) ODCIAggre

32、gate rountines 來創(chuàng)建自定義的集合函數(shù)；可以通過定義一個對象 類型（ Object Type ）,然后在這個類型內(nèi)部實(shí)現(xiàn) ODCIAggregate 接口函數(shù) routines , 可 以用任何一種 Oracle 支持的語言來實(shí)現(xiàn)這些接口函數(shù), 比如 C/C+, JAVA, PL/SQL 等；在這 個 Object Type 定義之后,相應(yīng)的接口函數(shù)也都在該 Object Type Body 內(nèi)部實(shí)現(xiàn)之后, 就 可以通過 CREATE FUNCTIO語N句來創(chuàng)建自定義的集合函數(shù)了； 每個自定義的集合函數(shù)需要實(shí)現(xiàn) 4 個 ODCIAggregate 接口函數(shù), 這些函數(shù)定義了任何一

33、 個集合函數(shù)內(nèi)部需要實(shí)現(xiàn)的操作, 這些函數(shù)分別是 initialization, iteration, merging 和 termination ； a.STATIC FUNCTION ODCIAGGREGATEINITIALIZEs_obj IN OUT str_agg_type RETURN NUMBER自定義集合函數(shù)初始化操作 傳回給 oracle. , 從這兒開頭一個集合函數(shù)； 初始化的集合環(huán)境會以對象實(shí)例 b.MEMBER FUNCTION ODCIAGGREGATEITERATEself IN OUT str_agg_type,p_var IN VARCHAR2 RETURN N

34、UMBER自定義集合函數(shù)最主要的步驟 , 這個函數(shù)定義我們的集合函數(shù)具體做什么操作 , 后面的例 子, 是取最大值 , 最小值 , 平均值 , 仍是做連接操作 .self 為當(dāng)前集合函數(shù)的指針 , 用來與前面的 運(yùn)算結(jié)果進(jìn)行關(guān)聯(lián) 這個函數(shù)用來遍歷需要處理的數(shù)據(jù),被 環(huán)境和新的（一組）值會作為傳入?yún)?shù)； oracle 重復(fù)調(diào)用；每次調(diào)用的時候,當(dāng)前的集合 這個函數(shù)會處理這些傳入值,然后返回更新后的聚 集環(huán)境 . 這個函數(shù)對每一個 NON-NULL的值都會被執(zhí)行一次； NULL 值不會被傳遞個集合函 數(shù) ； c.MEMBER FUNCTION ODCIAGGREGATEMERGEself IN O

35、UT str_agg_type, p_ obj IN str_agg_type RETURN NUMBER用來合并兩個集合函數(shù)的兩個不同的指針對應(yīng)的結(jié)果 是處理并行查詢集合函數(shù)的時候 . , 用戶合并不同結(jié)果結(jié)的數(shù)據(jù) , 特別 這個函數(shù)用來把兩個集合環(huán)境整合在一起,一般用來并行運(yùn)算中（當(dāng)一個函數(shù)被設(shè)置成 enable parallel 處理的時候）； ODCIAGGREGATETERMINATEself ARCHAR2, p_flags IN NUMBER RETURN NUMBER終止集合函數(shù)的處理 , 返回集合函數(shù)處理的結(jié)果 . INstr_agg_type, x_var OUTV第 13

36、 頁,共 22 頁學(xué)習(xí)必備 歡迎下載 這個函數(shù)是 Oracle 調(diào)用的最終一個函數(shù)； 數(shù)處理的結(jié)果 . 應(yīng)用之字符串相 SUM（字符串相連接） 第一步：建立對象類型規(guī)范 它接收集合環(huán)境作為參數(shù), 返回最終的集合函 create or replace type str_agg_type as object str VARCHAR2 300 , separator_flag VARCHAR2 64 , - 分隔符,默認(rèn)用自由定義 | ,可以修改此處 STATIC FUNCTION ODCIAGGREGATEINITIALIZEs_obj IN OUT str_agg_type RETURN NUM

37、BER, MEMBER FUNCTION ODCIAGGREGATEITERATE self IN OUT str_agg_type, p_obj x_var p_var IN VARCHAR2 RETURN NUMBER, MEMBERFUNCTION ODCIAGGREGATEMERGEself IN OUT str_agg_type, IN str_agg_type RETURN NUMBER, MEMBERFUNCTION ODCIAGGREGATETERMINATEself IN str_agg_type, OUT VARCHAR2, p_flags IN NUMBER RETURN

38、 NUMBER ; 其次步：建立對象類型體 create or replace type body str_agg_type is - 自定義集合函數(shù)初始化操作 STATIC FUNCTION ODCIAGGREGATEINITIALIZEs_obj RETURN NUMBER is IN OUT str_agg_type begin s_obj:= str_agg_type , , ; - 初始化為空,用逗號分隔 RETURN ODCICONST.SUCCESS; end ; - 定義函數(shù)的功能,實(shí)現(xiàn)字符串 sum（拼接） MEMBER FUNCTION ODCIAGGREGATEITERA

39、TE self IN OUT str_agg_type, p_var IN VARCHAR2 RETURN NUMBER is begin self.str:= self.str | p_var|self.separator_flag; RETURN ODCICONST.SUCCESS; end ; - 用來合并兩個集合函數(shù)的兩個不同的指針對應(yīng)的結(jié)果,此處默認(rèn)即可 MEMBERFUNCTION ODCIAGGREGATEMERGEself IN OUT str_agg_type, p_obj IN str_agg_type RETURN NUMBER is begin RETURN ODCIC

40、ONST.SUCCESS; end ; 第 14 頁,共 22 頁學(xué)習(xí)必備 歡迎下載 - 定義終止集合函數(shù)的處理 , 返回集合函數(shù)處理的結(jié)果 MEMBERFUNCTION ODCIAGGREGATETERMINATEself IN str_agg_type, x_var OUT VARCHAR2, p_flags IN NUMBER RETURN NUMBER is begin IF x_var IS NOT NULL THEN x_var:= SUBSTRself.str, 1 ,LENGTHself.str- 1; ELSE x_var:= self.str; END IF ; RETUR

41、N ODCICONST.SUCCESS; end ; end ; 第三步：創(chuàng)建自定義集合函數(shù) create or replace function sum_strp_str in varchar2 return varchar2 AGGREGATE USING str_agg_type; 第四步：使用自定義集合函數(shù) 找出部門號為 60,70,80 的部門員工名稱集合 SQL SELECT department_id,sum_strlast_name as ename FROM employees where department_id in60,70,80 GROUP BY departmen

42、t_id; 輸出： DEPARTMENT_ID ENAME - 60 Hunold,Ernst,Austin,Lorentz,Pataballa, 70 Baer, 80 Russell,Doran,Smith,McEwen,Sully,King,Tuvault, Cambrault,Olsen,Hall,Livingston,Ta 留意：假如期望自定義的集合函數(shù)和系統(tǒng)預(yù)定義的集合函數(shù)同名,比如 create or replace function sump_str in varchar2 return varchar2 AGGREGATE USING str_agg_type; 那么在使用該

43、自定義集合函數(shù)是必需指定函數(shù)所屬用戶,否就報(bào)錯； SQL SELECT department_id,sumlast_name as ename FROMemployees where department_id in60,70,80 GROUP BY department_id; ORA-01722: invalid number SQL SELECT department_id,xqq.sumlast_name as ename FROM employees where department_id in60,70,80 GROUP BY department_id; DEPARTMENT_I

44、D ENAME - 60 Hunold,Ernst,Austin,Lorentz,Pataballa, 70 Baer, 80 Russell,Doran,Smith,McEwen,Sully,King,Tuvault, Cambrault,Olsen,Hall,Livingston,Ta 第 15 頁,共 22 頁學(xué)習(xí)必備 歡迎下載 應(yīng)用之模擬預(yù)定義 COUNT 創(chuàng)建自定義對象類型規(guī)范 創(chuàng)建自定義對象類型體 創(chuàng)建自定義集合函數(shù) 使用自定義集合函數(shù) 第一步：創(chuàng)建自定義對象類型規(guī)范 create or replace type count_num_type as object num numbe

45、r , static function , ODCIAggregateInitializep_type In Out count_num_type , return number ODCIAggregateIterate self In Out count_num_type, member function member function p_numIn number return number , ODCIAggregateMerge self In Out count_num_type, member function p_typeIn count_num_type return numb

46、er ODCIAggregateTerminate self In Out count_num_type, x_num Out number , flags In number return number ; 其次步：創(chuàng)建自定義對象類型體 create or replace type body count_num_type is In Out count_num_type static function ODCIAggregateInitializep_type return number is begin p_type:=count_num_type 0 ; return ODCIConst

47、.Success; end ; member function ODCIAggregateIterate self In Out count_num_type, p_numIn number return number is begin self.num:=self.num+ 1; returnODCIConst.Success; end ; member function ODCIAggregateMerge self In Out count_num_type, p_typeIn count_num_type return number is begin self.num := self.

48、num; return ODCIconst.Success; end ; member function ODCIAggregateTerminate self In Out count_num_type, x_numOut number , 第 16 頁,共 22 頁學(xué)習(xí)必備 歡迎下載 return number is flags In number begin x_num:= self.num; return ODCIConst.Success; end ; end ; 第三步：創(chuàng)建自定義集合函數(shù) CREATE OR REPLACE FUNCTION count_numinput numb

49、er RETURN number PARALLEL_ENABLE AGGREGATE USING count_num_type; 第四步：使用自定義集合函數(shù) select department_id, count_numemployee_id, count employee_id from employees group by department_id 應(yīng)用之模擬預(yù)定義 SUM 創(chuàng)建自定義對象類型規(guī)范 創(chuàng)建自定義對象類型體 創(chuàng)建自定義集合函數(shù) 使用自定義集合函數(shù) 第一步：創(chuàng)建自定義對象類型規(guī)范 create or replace type , sum_num_type as object ,

50、 num number , ODCIAggregateInitializep_type In Out sum_num_type static function return number ODCIAggregateIterate self In Out sum_num_type, member function p_numIn number return number, member function ODCIAggregateMerge self In Out sum_num_type, p_type In sum_num_type return number member function

51、 ODCIAggregateTerminate self In Out sum_num_type, x_num Out number , ; flags In number return number 第 17 頁,共 22 頁學(xué)習(xí)必備 歡迎下載 其次步：創(chuàng)建自定義對象類型體 create or replace type body sum_num_type is In Out sum_num_type static function ODCIAggregateInitializep_type return number isbegin p_type:=sum_num_type 0 ; retu

52、rn ODCIConst.Success; end ; member function ODCIAggregateIterate self In Out sum_num_type, p_numIn number return number is begin self.num:=self.num+p_num; return ODCIConst.Success; end ; member function ODCIAggregateMerge self In Out sum_num_type, p_typeIn sum_num_type return number is begin self.nu

53、m := self.num; return ODCIconst.Success; end ; member function ODCIAggregateTerminate self In Out sum_num_type, x_numOut number , flags In number return number is begin x_num:= self.num; return ODCIConst.Success; end ; end ; 第三步：創(chuàng)建自定義集合函數(shù) CREATE OR REPLACE FUNCTION sum_numinput number RETURN number

54、PARALLEL_ENABLE AGGREGATE USING sum_num_type; 第四步：使用自定義集合函數(shù) select department_id, sum_numsalary, sumsalary from employees group by department_id; 第 18 頁,共 22 頁學(xué)習(xí)必備 歡迎下載 應(yīng)用之模擬預(yù)定義 AVG 創(chuàng)建自定義對象類型規(guī)范 創(chuàng)建自定義對象類型體 創(chuàng)建自定義集合函數(shù) 使 用自定義集合函數(shù) 第一步：創(chuàng)建自定義對象類型規(guī)范 create or replace type avg_num_type as object num number ,

55、 - 總數(shù) , sum number, - 總和 static function ODCIAggregateInitializep_type In Out avg_num_type return number , member function ODCIAggregateIterate self In Out avg_num_type, member function p_num In number return number, ODCIAggregateMerge self In Out avg_num_type, member function p_typeIn avg_num_type

56、return number ODCIAggregateTerminate self In Out avg_num_type, x_num Out number , flags In number return number ; 其次步：創(chuàng)建自定義對象類型體 create or replace type body avg_num_type is In Out avg_num_type static function ODCIAggregateInitializep_type return number is begin p_type:=avg_num_type 0 , 0 ; return OD

57、CIConst.Success; end ; member function ODCIAggregateIterate self In Out avg_num_type, p_numIn number return number is 第 19 頁,共 22 頁學(xué)習(xí)必備 歡迎下載 begin self.sum:=self.num+p_num;self.num:=self.num+ 1; return ODCIConst.Success; end ; member function ODCIAggregateMerge self In Out avg_num_type, p_typeIn avg_num_type return number is begin self.num := self.num; return ODCIconst.Success; end ; member function ODCIA