字串--&gt多行

多行--&gt 字串

[@more@]

多行--&gt字串

-- 建立測試資料

drop table heyf_t8;
create table heyf_t8 (
APP_ID VARCHAR2(20) ,
APP_CATEGORY_ID VARCHAR2(20) );

insert into heyf_t8 values ('1','A');
insert into heyf_t8 values ('1','b');
insert into heyf_t8 values ('1','c');
insert into heyf_t8 values ('2','sdf');
insert into heyf_t8 values ('2','B');
insert into heyf_t8 values ('2','C');
insert into heyf_t8 values ('2','C');
insert into heyf_t8 values ('3','D');
insert into heyf_t8 values ('3','E');

commit;

-- 實現方法1 (9i,sys_connect_by_path + start with … connect by … prior + 分析函式)

select app_id,
ltrim(max(sys_connect_by_path(app_category_id, ',')), ',') categ_ids
from (select t.app_id,
t.app_category_id,
(row_number()
over(partition by app_id order by app_category_id)) numid
from heyf_t8 t)
start with numid = 1
connect by numid - 1 = prior numid
and app_id = prior app_id
group by app_id;
-- 實現方法2 ( 10G,sys_connect_by_path + start with … connect by … prior + 分析函式)

select app_id,
ltrim(sys_connect_by_path(app_category_id, ','), ',') categ_ids
from (select t.app_id,
t.app_category_id,
(row_number()
over(partition by app_id order by app_category_id)) numid
from heyf_t8 t)
WHERE connect_by_isleaf = 1
start with numid = 1
connect by numid - 1 = prior numid
and app_id = prior app_id;

-- connect_by_isleaf 欄位是10g中新提供的一個偽列，他可以用來判斷該條記錄是否是樹形記錄的葉節點

-- 實現方法3 ( 10G,wmsys.wm_concat)

--建立使用者
@/opt/oracle/products/10.2.0/rdbms/admin/owmctab.plb
--建立型別
@$ORACLE_HOME/rdbms/admin/owmaggrb.plb
--建立wm_concat函式
@/opt/oracle/products/10.2.0/rdbms/admin/owmaggrs.plb

select app_id, wmsys.wm_concat(app_category_id) categ_ids
from heyf_t8 t
group by t.app_id;

-- 實現方法4 (建立自己的函式)

CREATE OR REPLACE FUNCTION rowtocol(p_slct IN VARCHAR2,
p_dlmtr IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2 AUTHID CURRENT_USER AS

/*1) Column should be character type.
2) If it is non-character type, column has to be converted into character type.
3) If the returned rows should in a specified order, put that ORDER BY CLASS in the SELECT statement argument.
4) If the SQL statement happened to return duplicate values, and if you don't want that to happen, put DISTINCT in the SELECT statement argument. */
TYPE c_refcur IS REF CURSOR;
lc_str VARCHAR2(4000);
lc_colval VARCHAR2(4000);
c_dummy c_refcur;
l number;
BEGIN
OPEN c_dummy FOR p_slct;
LOOP
FETCH c_dummy
INTO lc_colval;
EXIT WHEN c_dummy%NOTFOUND;
lc_str := lc_str || p_dlmtr || lc_colval;
END LOOP;
CLOSE c_dummy;
RETURN SUBSTR(lc_str, 2);
EXCEPTION
WHEN OTHERS THEN
lc_str := SQLERRM;
IF c_dummy%ISOPEN THEN
CLOSE c_dummy;
END IF;
RETURN lc_str;
END;

select a.department_id,a.department_name,a.location_id,
rowtocol('select distinct job_id from hr.employees where department_id='||a.department_id) as jobs
from hr.departments a
------------------------------------------------------
10 Administration 1700 AD_ASST
20 Marketing 1800 MK_MAN,MK_REP
30 Purchasing 1700 PU_CLERK,PU_MAN
40 Human Resources 2400 HR_REP
50 Shipping 1500 SH_CLERK,ST_CLERK,ST_MAN
60 IT 1400 IT_PROG
70 Public Relations 2700 PR_REP
80 Sales 2500 SA_MAN,SA_REP
90 Executive 1700 AD_PRES,AD_VP
100 Finance 1700 FI_ACCOUNT,FI_MGR
110 Accounting 1700 AC_ACCOUNT,AC_MGR

字串 --&gt 多行

-- 實現方法1

define str ='''aa,bb,cc,dd,ee''';

select rn, substr(text, sbegin, send - sbegin) value
from (select a.text,
a.rn,
decode(a.rn - 1, 0, 0, instr(a.text, ',', 1, a.rn - 1)) + 1 sbegin,
decode(instr(a.text, ',', 1, a.rn),
0,
length(&str) + 1,
instr(a.text, ',', 1, a.rn)) send
from (select &str text, rownum rn
from dual
connect by rownum <= 1 + length(&str) -
length(replace(&str, ','))) a)

-- 實現方法2 (建立函式)

---------------------------------------------------------------------------------------------

--- 字串轉換成數字列

CREATE OR REPLACE TYPE "NUMRECTYPE_ORDER" AS OBJECT(id NUMBER,num NUMBER);
CREATE OR REPLACE TYPE "NUMTABLETYPE_ORDER" AS TABLE OF numRecType_order;

CREATE OR REPLACE FUNCTION str2numList_order( p_string IN VARCHAR2 ) RETURN numTableType_order
AS
v_str VARCHAR2(5000) DEFAULT p_string || ',' ;
v_n NUMBER(6);
v_data numTableType_order := numTableType_order();
BEGIN
LOOP
v_n := INSTR( v_str, ',' );
EXIT WHEN (NVL(v_n,0) = 0);
v_data.extend;
v_data(v_data.COUNT) := numrectype_order(v_data.COUNT, TO_NUMBER(LTRIM(RTRIM(SUBSTR(v_str,1,v_n-1)))));
v_str := SUBSTR( v_str, v_n+1 );
END LOOP;
RETURN v_data;
END;

select * from
table(cast(str2numlist_order ('123,23,34,54') as numtabletype_order ))

---------------------------------------------------------------------------------------------

--- 字串轉換成字元列

CREATE OR REPLACE TYPE "VARRECTYPE_ORDER" AS OBJECT(id NUMBER,var VARCHAR2(128));
CREATE OR REPLACE TYPE "VARTABLETYPE_ORDER" AS TABLE OF varRecType_order;

CREATE OR REPLACE FUNCTION str2varList_order( p_string IN VARCHAR2 ) RETURN VarTableType_order
AS
v_str VARCHAR2(5000) DEFAULT p_string || ',';
v_n NUMBER(6);
v_data VarTableType_order := VarTableType_order();
BEGIN
LOOP
v_n :=INSTR( v_str, ',' );
EXIT WHEN (NVL(v_n,0) = 0);
v_data.extend;
v_data( v_data.COUNT ) := varrectype_order(v_data.COUNT, LTRIM(RTRIM(SUBSTR(v_str,1,v_n-1))));
v_str := SUBSTR( v_str, v_n+1 );
END LOOP;
RETURN v_data;
END;

select * from
table(cast(str2varlist_order('aa,bb,cc,dd,ee') as vartabletype_order))

Oracle 行列轉換小結

相關文章