oracle rules upsert,Oracle行列转换的一点小总结(1)

行列转换包括以下六种情况：

*列转行

*行转列

*多列转换成字符串

*多行转换成字符串

*字符串转换成多列

*字符串转换成多行

下面分别进行举例介绍。

首先声明一点，有些例子需要如下10g及以后才有的知识：

a。掌握model子句，

b。正则表达式

c。加强的层次查询

讨论的适用范围只包括8i,9i,10g及以后版本。begin：

1、列转行CREATE TABLE t_col_row(

ID INT,

c1 VARCHAR2(10),

c2 VARCHAR2(10),

c3 VARCHAR2(10));

INSERT INTO t_col_row VALUES (1, ‘v11’, ‘v21’, ‘v31’);

INSERT INTO t_col_row VALUES (2, ‘v12’, ‘v22’, NULL);

INSERT INTO t_col_row VALUES (3, ‘v13’, NULL, ‘v33’);

INSERT INTO t_col_row VALUES (4, NULL, ‘v24’, ‘v34’);

INSERT INTO t_col_row VALUES (5, ‘v15’, NULL, NULL);

INSERT INTO t_col_row VALUES (6, NULL, NULL, ‘v35’);

INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL);

COMMIT;

SELECT * FROM t_col_row;

1)UNION ALL

适用范围：8i,9i,10g及以后版本

SELECT id, ‘c1’ cn, c1 cv

FROM t_col_row

UNION ALL

SELECT id, ‘c2’ cn, c2 cv

FROM t_col_row

UNION ALL

SELECT id, ‘c3’ cn, c3 cv FROM t_col_row;

若空行不需要转换，只需加一个where条件，

WHERE COLUMN IS NOT NULL 即可。

2)MODEL

适用范围：10g及以后

SELECT id, cn, cv FROM t_col_row

MODEL

RETURN UPDATED ROWS

PARTITION BY (ID)

DIMENSION BY (0 AS n)

MEASURES (‘xx’ AS cn,’yyy’ AS cv,c1,c2,c3)

RULES UPSERT ALL

(

cn[1] = ‘c1’,

cn[2] = ‘c2’,

cn[3] = ‘c3’,

cv[1] = c1[0],

cv[2] = c2[0],

cv[3] = c3[0]

)

ORDER BY ID,cn;

3)collection

适用范围：8i,9i,10g及以后版本

要创建一个对象和一个集合：

CREATE TYPE cv_pair AS OBJECT(cn VARCHAR2(10),cv VARCHAR2(10));

CREATE TYPE cv_varr AS VARRAY(8) OF cv_pair;

SELECT id, t.cn AS cn, t.cv AS cv

FROM t_col_row,

TABLE(cv_varr(cv_pair(‘c1’, t_col_row.c1),

cv_pair(‘c2’, t_col_row.c2),

cv_pair(‘c3’, t_col_row.c3))) t

ORDER BY 1, 2;

转载声明：本站文章若无特别说明，皆为原创，转载请注明来源：三十岁，谢谢！^^

分享到：