oracle 列转行
一.
第 1 步(创建类型 type_table_string,用于转换 COLLECT 函数的返回值)
第 2 步(创建函数 columns_to_string,用于将 type_table_string 类型转换成普通字符串)
CREATE OR REPLACE TYPE type_table_string IS TABLE OF VARCHAR2(4000);CREATE OR REPLACE FUNCTION columns_to_string(p_str_tab IN type_table_string,p_separator IN VARCHAR2 DEFAULT ',')RETURN VARCHAR2 ISv_ret_str VARCHAR2(4000);BEGINFOR i IN 1..p_str_tab.COUNT LOOPv_ret_str:=v_ret_str||p_separator||p_str_tab(i);END LOOP;RETURN LTRIM(v_ret_str,p_separator);END;--使用:SELECT t.dept_code,columns_to_string(CAST(COLLECT(t.staff_name) AS type_table_string),'|') staff_namesFROM t_staff t GROUP BY t.dept_code;
二、重写wm_concat
--1.创建类型 type_concat 的定义CREATE OR REPLACE TYPE type_concatAUTHID CURRENT_USER AS OBJECT(v_result_string VARCHAR2(4000),STATIC FUNCTION odciAggregateInitialize(concat IN OUT type_concat) RETURN NUMBER,MEMBER FUNCTION odciAggregateIterate(SELF IN OUT type_concat,str IN VARCHAR2) RETURN NUMBER,MEMBER FUNCTION odciAggregateTerminate(SELF IN type_concat,return_value OUT VARCHAR2,flags IN NUMBER) RETURN NUMBER,MEMBER FUNCTION odciAggregateMerge(SELF IN OUT type_concat,concat IN type_concat) RETURN NUMBER);--2.创建类型 type_concat 的 bodyCREATE OR REPLACE TYPE BODY type_concatISSTATIC FUNCTION odciAggregateInitialize(concat IN OUT type_concat)RETURN NUMBER ISBEGINconcat := type_concat(NULL);RETURN ODCICONST.SUCCESS;END;MEMBER FUNCTION odciAggregateIterate(SELF IN OUT type_concat,str IN VARCHAR2)RETURN NUMBER ISBEGINIF SELF.v_result_string IS NOT NULL THENSELF.v_result_string := SELF.v_result_string||','||str;ELSESELF.v_result_string := str;END IF;RETURN ODCICONST.SUCCESS;END;MEMBER FUNCTION odciAggregateTerminate(SELF IN type_concat,return_value OUT VARCHAR2,flags IN NUMBER)RETURN NUMBER ISBEGINreturn_value := SELF.v_result_string;RETURN ODCICONST.SUCCESS;END;MEMBER FUNCTION odciAggregateMerge(SELF IN OUT type_concat,concat IN type_concat)RETURN NUMBER ISBEGINIF concat.v_result_string IS NOT NULL THENSELF.v_result_string := SELF.v_result_string||','||concat.v_result_string;END IF;RETURN ODCICONST.SUCCESS;END;END;--三.创建函数 fn_concat,替代 WM_CONCATCREATE OR REPLACE FUNCTION fn_concat(str VARCHAR2)RETURN VARCHAR2 AGGREGATE USING type_concat;
2.1 调用方法:
SELECT t.dept_code,fn_concat(t.staff_name) staff_names FROM t_staff t GROUP BY t.dept_code;
一:上例中的 AUTHID CURRENT_USER 是权限控制的关键字,表示调用者权限,即当前用户。默认为 AUTHID DEFINER,表示定义者权限,即模式拥有者。二:将 type_concat 中 v_result_string 和 return_value 的类型改为 CLOB 类型,并将 fn_concat 的返回值类型也改为 CLOB,就成了 CLOB 版的 fn_concat 了。
如果觉得《Oracle 自定义函数实现列转行效果》对你有帮助,请点赞、收藏,并留下你的观点哦!