澳门新葡亰平台官网oracle行列转换总结

by admin on 2020年1月20日

注意:数据库版本是10g,不过大部分9i的也适用,闪回9i就没有.

最近论坛很多人提的问题都与行列转换有关系,所以我对行列转换的相关知识做了一个总结,
希望对大家有所帮助,同时有何错疏,恳请大家指出,
我也是在写作过程中学习,算是一起和大家学习吧。

  1. wm_concat函数也来打擂台
    使用场景:简单的将一列的字段通过逗号连接起来。
    Example:
    select a1,(wm_concat(b1),from aa group by a1
    如果是10G版本以下通用的语句如下
    SELECT t.id id, MAX(substr(sys_connect_by_path(t.sname, ‘,’), 2))
    str
    FROM (SELECT id, sname, row_number() over(PARTITION BY id ORDER BY
    sname) rn FROM a) t
    START WITH rn = 1
    CONNECT BY rn = PRIOR rn + 1
    AND id = PRIOR id
    GROUP BY t.id;

 

行列转换包括以下六种情况:
*列转行
*行转列
*多列转换成字符串
*多行转换成字符串
*字符串转换成多列
*字符串转换成多行

2.帝王之作(Sql语句中实现字段的自动增长。)
Example(实现了日期的自动增长):
select to_char(sysdate+rownum,’mm-dd’)
from dual connect by rownum <=7

1.曾经不小心把开发库的数据库表全部删除,当时吓的要死。结果找到下面的语句恢复到了1个小时之前的数据!很简单。

下面分别进行举例介绍。

3.还是你最亲(csdn的兄弟们最喜欢用的虚表)
Example:
with tab as
(
select 1
  from dual
union all
select 2 from dual
)

注意使用管理员登录系统:

首先声明一点,有些例子需要如下10g及以后才有的知识:
a。掌握model子句,
b。正则表达式
c。加强的层次查询

  1. 还真另类(将所有列使用’||’拼接后插入一个clob字段)
    insert into t_result(clob_col) select
    F_TASK_NAME||F_CREATER_NAME
    ||f_result from dual;
    5.选择性插入语句,很实用
    INSERT ALL
      WHEN type=1 THEN INTO tab1 VALUES (myseq.NEXTVAL, val)
      WHEN type=2 THEN INTO tab2 VALUES (myseq.NEXTVAL, val)
      WHEN type IN (3,4,5) THEN INTO tab3 VALUES (myseq.NEXTVAL, val)
      ELSE INTO tab4 VALUES (myseq.NEXTVAL, val)
    SELECT type, val FROM source_tab;
    6.这都被你给删了(删除数据重复的记录)
    delete from a a where a.rowid!=(select max(rowid) from a b where
    a.bm=b.bm and a.mc=b.mc);
    7.的确很美,嫁了吧(美妙的数据更新语句,前提是主键相同)
    update (select s.name name1,s2.name name2 from simple s,simple2 s2 where
    s.id=s2.id) v set v.name1=v.name2;
    8.选择由你(针对同一数据表是选择插入,还是更新)
    merge  into  a  using  b  on  (a.USER_NO=b.USER_NO) 
      when  matched  then  update  set  a.NAME=b.NAME 
      when  not  matched  then    insert  values  (b.USER_NO,b.NAME)
    9.Java爱好者(通用的oracle分页语句)
    SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM (
    //生成标准的查询语句(没有分页条件的)
    Sql_statement
    ) row_ WHERE rownum <=  pageSize * pageNumber

select * from 表名 as of timestamp sysdate-1/12  
//查询两个小时前的某表数据!既然两小时以前的数据都得到了,继续怎么做,知道了吧。。

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

) WHERE rownum_ > pageSize * (pageNumber – 1))
10.让我也来个(使用频率高的查询连续记录)
Example(查询连续3个月的记录):
select id from (select id,sbrq,sbbz,
add_months(trunc(sbrq, ‘mm’),
  1 – rank() over(partition by id order by sbrq)) ord
  from (select id, sbrq, sbbz from gl_wsbxx where sbbz = ‘N’))
  group by id, ord
having count(*) >= 3
11.随机函数也疯狂
Example:
sys_guid() 获取随机字符串。
dbms_random.value()获取随机数
select * from consume SAMPLE(10) where rownum <2;
SELECT * FROM (SELECT * FROM t ORDER BY sys_guid()) WHERE rownum <
2;这个的效率高些
select * from tb order by dbms_random.value()

 

1、列转行
CREATE TABLE t_col_row(
ID INT,
c1 VARCHAR2(10),
c2 VARCHAR2(10),
c3 VARCHAR2(10));

12.这个选择性删除记录也很拽
Example:
create or replace trigger tt_del_tri
  before delete on tb_Group when(old.groupName = ‘system’)
declare
  raise_application_error(-20000,’不能删除系统名字的列’);
begin
阻止删除数据列。。。
13.不老的Kill语句
一  如何查oracle的锁
SELECT /*+ rule */ s.username,decode(l.type,’TM’,’TABLE
LOCK’,’TX’,’ROW LOCK’,NULL)
LOCK_LEVEL,o.owner,o.object_name,o.object_type,     
    s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
    FROM v$session s,v$lock l,dba_objects o
    WHERE l.sid = s.sid
    AND l.id1 = o.object_id(+)
    AND s.username is NOT NULL
二  如何杀掉锁的进程
    Alter system kill session ‘sid,serial#’
    alter system disconnect session ‘sid,serial#’ immediate;
   
在win上,还可以采用oracle提供的orakill杀掉一个线程(其实就是一个Oracle进程)
    在Linux/Unix上,可以直接利用kill杀掉数据库进程对应的OS进程
14.正则表达式也想小试牛刀
Example(regexp_substr,regexp_instr,regexp_like,regexp_replace):
SQL> select REGEXP_REPLACE(‘李四(AAA)’,'(.+)((.+)’,’1′) from
dual;

如果drop了表,怎么办??见下面:

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;

REGEXP_REPLACE(‘李四(AAA)’,'(.

李四

SQL>

  1. translate函数也来指招
    Example(将a用w代替,b用e代替):
    select translate(‘abcdef’,’ab’,’we’) from dual;

drop table 表名;

SELECT * FROM t_col_row;


数据库误删除表之后恢复:(
绝对ok,我就做过这样的事情,汗
)不过要记得删除了哪些表名。
flashback table 表名 to before
drop;

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.查询得到当前数据库中锁,以及解锁:

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;

查锁
SELECT /*+ rule */ s.username,
decode(l.type,’TM’,’TABLE LOCK’,
‘TX’,’ROW LOCK’,
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL;

3)collection
适用范围:8i,9i,10g及以后版本
要创建一个对象和一个集合:
CREATE TYPE cv_pair AS OBJECT(cn VARCHAR2(10),cv VARCHAR2(10));

解锁
alter system kill session ‘sid,serial’;
如果解不了。直接倒os下kill进程kill -9 spid

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;

 ORA-28000:账户被锁定

2、行转列

因为密码输入错误多次用户自动被锁定.

CREATE TABLE t_row_col AS
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;

解决办法:alter user
user_name account unlock;

SELECT * FROM t_row_col ORDER BY 1,2;

 

1)AGGREGATE FUNCTION
适用范围:8i,9i,10g及以后版本
SELECT id,
MAX(decode(cn, ‘c1’, cv, NULL)) AS c1,
MAX(decode(cn, ‘c2’, cv, NULL)) AS c2,
MAX(decode(cn, ‘c3’, cv, NULL)) AS c3
FROM t_row_col
GROUP BY id
ORDER BY 1;

3.关于查询数据库用户,权限的相关语句:

MAX聚集函数也可以用sum、min、avg等其他聚集函数替代。

Sql代码
澳门新葡亰平台官网 1 澳门新葡亰平台官网 2澳门新葡亰平台官网 3)

被指定的转置列只能有一列,但固定的列可以有多列,请看下面的例子:

  1. 1.查看所有用户:   
  2. select * from dba_user;   
  3. select * from all_users;   
  4. select * from user_users;   
  5.   
  6.   
  7. 2.查看用户系统权限:   
  8. select * from dba_sys_privs;   
  9. select * from all_sys_privs;   
  10. select * from user_sys_privs;   
  11.   
  12.   
  13. 3.查看用户对象权限:   
  14. select * from dba_tab_privs;   
  15. select * from all_tab_privs;   
  16. select * from user_tab_privs;   
  17.   
  18.   
  19. 4.查看所有角色:   
  20. select * from dba_roles;   
  21.   
  22.   
  23. 5.查看用户所拥有的角色:   
  24. select * from dba_role_privs;   
  25. select * from user_role_privs;  

SELECT mgr, deptno, empno, ename FROM emp ORDER BY 1, 2;

1.查看所有用户: select * from dba_user; select * from all_users;
select * from user_users; 2.查看用户系统权限: select * from
dba_sys_privs; select * from all_sys_privs; select * from
user_sys_privs; 3.查看用户对象权限: select * from dba_tab_privs;
select * from all_tab_privs; select * from user_tab_privs;
4.查看所有角色: select * from dba_roles; 5.查看用户所拥有的角色:
select * from dba_role_privs; select * from user_role_privs;

SELECT mgr,
deptno,
MAX(decode(empno, ‘7788’, ename, NULL)) “7788”,
MAX(decode(empno, ‘7902’, ename, NULL)) “7902”,
MAX(decode(empno, ‘7844’, ename, NULL)) “7844”,
MAX(decode(empno, ‘7521’, ename, NULL)) “7521”,
MAX(decode(empno, ‘7900’, ename, NULL)) “7900”,
MAX(decode(empno, ‘7499’, ename, NULL)) “7499”,
MAX(decode(empno, ‘7654’, ename, NULL)) “7654”
FROM emp
WHERE mgr IN (7566, 7698)
AND deptno IN (20, 30)
GROUP BY mgr, deptno
ORDER BY 1, 2;

 

这里转置列为empno,固定列为mgr,deptno。

4.几个经常用到的oracle视图:注意表名使用大写………………..

还有一种行转列的方式,就是相同组中的行值变为单个列值,但转置的行值不变为列名:

Sql代码
澳门新葡亰平台官网 4 澳门新葡亰平台官网 5澳门新葡亰平台官网 6)

ID CN_1 CV_1 CN_2 CV_2 CN_3 CV_3
1 c1 v11 c2 v21 c3 v31
2 c1 v12 c2 v22 c3
3 c1 v13 c2 c3 v33
4 c1 c2 v24 c3 v34
5 c1 v15 c2 c3
6 c1 c2 c3 v35
7 c1 c2 c3

  1. 1. 查询oracle中所有用户信息   
  2.        select  * from dba_user;   
  3.    2. 只查询用户和密码   
  4.        select username,password from dba_users;   
  5.    3. 查询当前用户信息   
  6.        select * from dba_ustats;   
  7.    4. 查询用户可以访问的视图文本   
  8.        select * from dba_varrays;   
  9.    5. 查询数据库中所有视图的文本   
  10.        select * from dba_views;   
  11. 6.查询全部索引    
  12. select * from user_indexes;   
  13. 查询全部表格   
  14.       select * from user_tables;   
  15.          查询全部约束   
  16.       select * from user_constraints;   
  17.           查询全部对象   
  18.       select * from user_objects;  

  19. 查询oracle中所有用户信息 select * from dba_user; 2.
    只查询用户和密码 select username,password from dba_users; 3.
    查询当前用户信息 select * from dba_ustats; 4.
    查询用户可以访问的视图文本 select * from dba_varrays; 5.
    查询数据库中所有视图的文本 select * from dba_views; 6.查询全部索引
    select * from user_indexes; 查询全部表格 select * from user_tables;
    查询全部约束 select * from user_constraints; 查询全部对象 select *
    from user_objects;

这种情况可以用分析函数实现:

 

SELECT id,
MAX(decode(rn, 1, cn, NULL)) cn_1,
MAX(decode(rn, 1, cv, NULL)) cv_1,
MAX(decode(rn, 2, cn, NULL)) cn_2,
MAX(decode(rn, 2, cv, NULL)) cv_2,
MAX(decode(rn, 3, cn, NULL)) cn_3,
MAX(decode(rn, 3, cv, NULL)) cv_3
FROM (SELECT id,
cn,
cv,
row_number() over(PARTITION BY id ORDER BY cn, cv) rn
FROM t_row_col)
GROUP BY ID;

5.查看当前数据库中正在执行的语句,然后可以继续做很多很多事情,例如查询执行计划等等

2)PL/SQL
适用范围:8i,9i,10g及以后版本
这种对于行值不固定的情况可以使用。
下面是我写的一个包,包中
p_rows_column_real用于前述的第一种不限定列的转换;
p_rows_column用于前述的第二种不限定列的转换。

Sql代码
澳门新葡亰平台官网 7 澳门新葡亰平台官网 8澳门新葡亰平台官网 9)

CREATE OR REPLACE PACKAGE pkg_dynamic_rows_column AS
TYPE refc IS REF CURSOR;

  1. (1).查看相关进程在数据库中的会话      
  2.   Select   a.sid,a.serial#,a.program,   a.status   ,   
      
  3.   substr(a.machine,1,20),   a.terminal,b.spid      
  4.   from   v$session   a,   v$process   b   
      
  5. 澳门新葡亰平台官网,  where   a.paddr=b.addr      
  6.   and   b.spid   =   &spid;      
  7.        
  8.   (2).查看数据库中被锁住的对象和相关会话      
  9.   select   a.sid,a.serial#,a.username,a.program,   
      
  10.   c.owner,   c.object_name        
  11.   from   v$session   a,   v$locked_object   b,   all_objects   c   
      
  12.   where   a.sid=b.session_id   and      
  13.   c.object_id   =   b.object_id;      
  14.        
  15.   (3).查看相关会话正在执行的SQL      
  16.   select   sql_text   from   v$sqlarea   where   address   =        
  17.   (   select   sql_address   from   v$session   where   sid   =   &sid   );     

PROCEDURE p_print_sql(p_txt VARCHAR2);

(1).查看相关进程在数据库中的会话 Select a.sid,a.serial#,a.program,
a.status , substr(a.machine,1,20), a.terminal,b.spid from v$session a,
v$process b where a.paddr=b.addr and b.spid = &spid;
(2).查看数据库中被锁住的对象和相关会话 select
a.sid,a.serial#,a.username,a.program, c.owner, c.object_name from
v$session a, v$locked_object b, all_objects c where
a.sid=b.session_id and c.object_id = b.object_id;
(3).查看相关会话正在执行的SQL select sql_text from v$sqlarea where
address = ( select sql_address from v$session where sid = &sid );

FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq
INT)
RETURN VARCHAR2;

 

PROCEDURE p_rows_column(p_table    IN VARCHAR2,
p_keep_cols   IN VARCHAR2,
p_pivot_cols IN VARCHAR2,
p_where    IN VARCHAR2 DEFAULT NULL,
p_refc    IN OUT refc);

6.查询表的结构:表名大写!!

PROCEDURE p_rows_column_real(p_table     IN VARCHAR2,
p_keep_cols IN VARCHAR2,
p_pivot_col IN VARCHAR2,
p_pivot_val IN VARCHAR2,
p_where     IN VARCHAR2 DEFAULT NULL,
p_refc    IN OUT refc);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_dynamic_rows_column AS

select t.COLUMN_NAME,
       t.DATA_TYPE,
       nvl(t.DATA_PRECISION, t.DATA_LENGTH),
       nvl(T.DATA_SCALE, 0),
       c.comments
  from all_tab_columns t, user_col_comments c
 whEre t.TABLE_NAME = c.table_name
   and t.COLUMN_NAME = c.column_name
   and t.TABLE_NAME = UPPER(‘OM_EMPLOYEE_T’)
 order by t.COLUMN_ID     

PROCEDURE p_print_sql(p_txt VARCHAR2) IS
v_len INT;
BEGIN
v_len := length(p_txt);
FOR i IN 1 .. v_len / 250 + 1 LOOP
dbms_output.put_line(substrb(p_txt, (i – 1) * 250 + 1, 250));
END LOOP;
END;

 

FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq
INT)
RETURN VARCHAR2 IS
v_first INT;
v_last   INT;
BEGIN
IF p_seq < 1 THEN
RETURN NULL;
END IF;
IF p_seq = 1 THEN
IF instr(p_str, p_division, 1, p_seq) = 0 THEN
RETURN p_str;
ELSE
RETURN substr(p_str, 1, instr(p_str, p_division, 1) – 1);
END IF;
ELSE
v_first := instr(p_str, p_division, 1, p_seq – 1);
v_last   := instr(p_str, p_division, 1, p_seq);
IF (v_last = 0) THEN
IF (v_first > 0) THEN
RETURN substr(p_str, v_first + 1);
ELSE
RETURN NULL;
END IF;
ELSE
RETURN substr(p_str, v_first + 1, v_last – v_first – 1);
END IF;
END IF;
END f_split_str;

7.行列互换:

PROCEDURE p_rows_column(p_table    IN VARCHAR2,
p_keep_cols   IN VARCHAR2,
p_pivot_cols IN VARCHAR2,
p_where    IN VARCHAR2 DEFAULT NULL,
p_refc    IN OUT refc) IS
v_sql VARCHAR2(4000);
TYPE v_keep_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY
BINARY_INTEGER;
v_keep v_keep_ind_by;

Sql代码
澳门新葡亰平台官网 10 澳门新葡亰平台官网 11澳门新葡亰平台官网 12)

TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY
BINARY_INTEGER;
v_pivot v_pivot_ind_by;

  1. 建立一个例子表:   
  2. CREATE TABLE t_col_row(    
  3. ID INT,    
  4. c1 VARCHAR2(10),    
  5. c2 VARCHAR2(10),    
  6. c3 VARCHAR2(10));    
  7. INSERT INTO t_col_row VALUES (1, ‘v11’, ‘v21’, ‘v31’);    
  8. INSERT INTO t_col_row VALUES (2, ‘v12’, ‘v22’, NULL);    
  9. INSERT INTO t_col_row VALUES (3, ‘v13’, NULL, ‘v33’);    
  10. INSERT INTO t_col_row VALUES (4, NULL, ‘v24’, ‘v34’);    
  11. INSERT INTO t_col_row VALUES (5, ‘v15’, NULL, NULL);    
  12. INSERT INTO t_col_row VALUES (6, NULL, NULL, ‘v35’);    
  13. INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL);    
  14. COMMIT;    
  15.   
  16. 下面的是列转行:创建了一个视图   
  17. CREATE view v_row_col AS  
  18. SELECT id, ‘c1’ cn, c1 cv   
  19. FROM t_col_row   
  20. UNION ALL  
  21. SELECT id, ‘c2’ cn, c2 cv   
  22. FROM t_col_row   
  23. UNION ALL  
  24. SELECT id, ‘c3’ cn, c3 cv FROM t_col_row;   
  25.   
  26. 下面是创建了没有空值的一个竖表:   
  27. CREATE view v_row_col_notnull AS  
  28. SELECT id, ‘c1’ cn, c1 cv   
  29.  FROM t_col_row    
  30. where c1 is not null  
  31. UNION ALL  
  32. SELECT id, ‘c2’ cn, c2 cv   
  33.  FROM t_col_row   
  34. where c2 is not null  
  35. UNION ALL  
  36. SELECT id, ‘c3’ cn, c3 cv   
  37.  FROM t_col_row    
  38. where c3 is not null;  

v_keep_cnt INT;
v_pivot_cnt   INT;
v_max_cols INT;
v_partition   VARCHAR2(4000);
v_partition1 VARCHAR2(4000);
v_partition2 VARCHAR2(4000);
BEGIN
v_keep_cnt   := length(p_keep_cols) – length(REPLACE(p_keep_cols,
‘,’)) + 1;
v_pivot_cnt := length(p_pivot_cols) –
length(REPLACE(p_pivot_cols, ‘,’)) + 1;
FOR i IN 1 .. v_keep_cnt LOOP
v_keep(i) := f_split_str(p_keep_cols, ‘,’, i);
END LOOP;
FOR j IN 1 .. v_pivot_cnt LOOP
v_pivot(j) := f_split_str(p_pivot_cols, ‘,’, j);
END LOOP;
v_sql := ‘select max(count(*)) from ‘ || p_table || ‘ group by ‘;
FOR i IN 1 .. v_keep.LAST LOOP
v_sql := v_sql || v_keep(i) || ‘,’;
END LOOP;
v_sql := rtrim(v_sql, ‘,’);
EXECUTE IMMEDIATE v_sql
INTO v_max_cols;
v_partition := ‘select ‘;
FOR x IN 1 .. v_keep.COUNT LOOP
v_partition1 := v_partition1 || v_keep(x) || ‘,’;
END LOOP;
FOR y IN 1 .. v_pivot.COUNT LOOP
v_partition2 := v_partition2 || v_pivot(y) || ‘,’;
END LOOP;
v_partition1 := rtrim(v_partition1, ‘,’);
v_partition2 := rtrim(v_partition2, ‘,’);
v_partition   := v_partition || v_partition1 || ‘,’ || v_partition2
||
‘, row_number() over (partition by ‘ || v_partition1 ||
‘ order by ‘ || v_partition2 || ‘) rn from ‘ || p_table;
v_partition   := rtrim(v_partition, ‘,’);
v_sql        := ‘select ‘;
FOR i IN 1 .. v_keep.COUNT LOOP
v_sql := v_sql || v_keep(i) || ‘,’;
END LOOP;
FOR i IN 1 .. v_max_cols LOOP
FOR j IN 1 .. v_pivot.COUNT LOOP
v_sql := v_sql || ‘ max(decode(rn,’ || i || ‘,’ || v_pivot(j) ||
‘,null))’ || v_pivot(j) || ‘_’ || i || ‘,’;
END LOOP;
END LOOP;
IF p_where IS NOT NULL THEN
v_sql := rtrim(v_sql, ‘,’) || ‘ from (‘ || v_partition || ‘ ‘ ||
p_where || ‘) group by ‘;
ELSE
v_sql := rtrim(v_sql, ‘,’) || ‘ from (‘ || v_partition ||
‘) group by ‘;
END IF;
FOR i IN 1 .. v_keep.COUNT LOOP
v_sql := v_sql || v_keep(i) || ‘,’;
END LOOP;
v_sql := rtrim(v_sql, ‘,’);
p_print_sql(v_sql);
OPEN p_refc FOR v_sql;
EXCEPTION
WHEN OTHERS THEN
OPEN p_refc FOR
SELECT ‘x’ FROM dual WHERE 0 = 1;
END;

建立一个例子表: 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; 下面的是列转行:创建了一个视图 CREATE view v_row_col AS 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;
下面是创建了没有空值的一个竖表: CREATE view v_row_col_notnull AS
SELECT id, ‘c1’ cn, c1 cv FROM t_col_row where c1 is not null UNION
ALL SELECT id, ‘c2’ cn, c2 cv FROM t_col_row where c2 is not null
UNION ALL SELECT id, ‘c3’ cn, c3 cv FROM t_col_row where c3 is not
null;

PROCEDURE p_rows_column_real(p_table     IN VARCHAR2,
p_keep_cols IN VARCHAR2,
p_pivot_col IN VARCHAR2,
p_pivot_val IN VARCHAR2,
p_where     IN VARCHAR2 DEFAULT NULL,
p_refc    IN OUT refc) IS
v_sql VARCHAR2(4000);
TYPE v_keep_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY
BINARY_INTEGER;
v_keep v_keep_ind_by;
TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY
BINARY_INTEGER;
v_pivot v_pivot_ind_by;
v_keep_cnt INT;
v_group_by VARCHAR2(2000);
BEGIN
v_keep_cnt := length(p_keep_cols) – length(REPLACE(p_keep_cols,
‘,’)) + 1;
FOR i IN 1 .. v_keep_cnt LOOP
v_keep(i) := f_split_str(p_keep_cols, ‘,’, i);
END LOOP;
v_sql := ‘select ‘ || ‘cast(‘ || p_pivot_col ||
‘ as varchar2(200)) as ‘ || p_pivot_col || ‘ from ‘ || p_table ||
‘ group by ‘ || p_pivot_col;
EXECUTE IMMEDIATE v_sql BULK COLLECT
INTO v_pivot;
FOR i IN 1 .. v_keep.COUNT LOOP
v_group_by := v_group_by || v_keep(i) || ‘,’;
END LOOP;
v_group_by := rtrim(v_group_by, ‘,’);
v_sql    := ‘select ‘ || v_group_by || ‘,’;

 

FOR x IN 1 .. v_pivot.COUNT LOOP
v_sql := v_sql || ‘ max(decode(‘ || p_pivot_col || ‘,’ || chr(39)
||
v_pivot(x) || chr(39) || ‘,’ || p_pivot_val ||
‘,null)) as “‘ || v_pivot(x) || ‘”,’;
END LOOP;
v_sql := rtrim(v_sql, ‘,’);
IF p_where IS NOT NULL THEN
v_sql := v_sql || ‘ from ‘ || p_table || p_where || ‘ group by ‘
||
v_group_by;
ELSE
v_sql := v_sql || ‘ from ‘ || p_table || ‘ group by ‘ ||
v_group_by;
END IF;
p_print_sql(v_sql);
OPEN p_refc FOR v_sql;
EXCEPTION
WHEN OTHERS THEN
OPEN p_refc FOR
SELECT ‘x’ FROM dual WHERE 0 = 1;
END;

8.下面可能是dba经常使用的oracle视图吧。呵呵

END;
/

Sql代码
澳门新葡亰平台官网 13 澳门新葡亰平台官网 14澳门新葡亰平台官网 15)

3.多列转换成字符串
CREATE TABLE t_col_str AS
SELECT * FROM t_col_row;

  1. 1.示例:已知hash_value:3111103299,查询sql语句:   
  2. select * from v$sqltext    
  3. where hashvalue=’3111103299′  
  4. order by piece    
  5. 2.查看消耗资源最多的SQL:   
  6. SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls
      
  7. FROM V$SQLAREA   
  8. WHERE buffer_gets > 10000000OR disk_reads > 1000000
      
  9. ORDERBY buffer_gets + 100 * disk_reads DESC;   
  10.   
  11. 3.查看某条SQL语句的资源消耗:   
  12. SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls
      
  13. FROM V$SQLAREA   
  14. WHERE hash_Value = 228801498AND address = hextoraw(‘CBD8E4B0’);   
  15.   
  16. 4.查询sql语句的动态执行计划:   
  17.         首先使用下面的语句找到语句的在执行计划中的address和hash_code
      
  18.         SELECT sql_text, address, hash_value FROM v$sql t   
  19.                 where (sql_text like ‘%FUNCTION_T(表名大写!)%’)   
  20.         然后:   
  21.         SELECT operation, options, object_name, cost FROM v$sql_plan   
  22.                 WHERE address = ‘C00000016BD6D248’ AND hash_value = 664376056;   
  23.   
  24. 5.查询oracle的版本:   
  25. select * from v$version;   
  26.   
  27. 6.查询数据库的一些参数:   
  28. select * from v$parameter   
  29.   
  30. 7.查找你的session信息   
  31. SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS
      
  32. FROM V$SESSION WHERE audsid = userenv(‘SESSIONID’);   
  33.   
  34. 8.当machine已知的情况下查找session   
  35. SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL
      
  36. FROM V$SESSION   
  37. WHERE terminal = ‘pts/tl’ AND machine = ‘rgmdbs1’;   
  38.   
  39. 9.查找当前被某个指定session正在运行的sql语句。假设sessionID为100   
  40. select b.sql_text    
  41. from v$session a,v$sqlarea b    
  42. where a.sql_hashvalue=b.hash_value and a.sid=100  

这个比较简单,用||或concat函数可以实现:
SELECT concat(‘a’,’b’) FROM dual;

1.示例:已知hash_value:3111103299,查询sql语句: select * from
v$sqltext where hashvalue=’3111103299′ order by piece
2.查看消耗资源最多的SQL: SELECT hash_value, executions, buffer_gets,
disk_reads, parse_calls FROM V$SQLAREA WHERE buffer_gets >
10000000OR disk_reads > 1000000 ORDERBY buffer_gets + 100 *
disk_reads DESC; 3.查看某条SQL语句的资源消耗: SELECT hash_value,
buffer_gets, disk_reads, executions, parse_calls FROM V$SQLAREA WHERE
hash_Value = 228801498AND address = hextoraw(‘CBD8E4B0’);
4.查询sql语句的动态执行计划:
首先使用下面的语句找到语句的在执行计划中的address和hash_code SELECT
sql_text, address, hash_value FROM v$sql t where (sql_text like
‘%FUNCTION_T(表名大写!)%’) 然后: SELECT operation, options,
object_name, cost FROM v$sql_plan WHERE address = ‘C00000016BD6D248’
AND hash_value = 664376056; 5.查询oracle的版本: select * from
v$version; 6.查询数据库的一些参数: select * from v$parameter
7.查找你的session信息 SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS
FROM V$SESSION WHERE audsid = userenv(‘SESSIONID’);
8.当machine已知的情况下查找session SELECT SID, OSUSER, USERNAME,
MACHINE, TERMINAL FROM V$SESSION WHERE terminal = ‘pts/tl’ AND machine =
‘rgmdbs1’;
9.查找当前被某个指定session正在运行的sql语句。假设sessionID为100 select
b.sql_text from v$session a,v$sqlarea b where
a.sql_hashvalue=b.hash_value and a.sid=100

1)|| OR concat
适用范围:8i,9i,10g及以后版本
SELECT * FROM t_col_str;

 

SELECT ID,c1||’,’||c2||’,’||c3 AS c123
FROM t_col_str;

9.树形结构connect by 排序:

4.多行转换成字符串
CREATE TABLE t_row_str(
ID INT,
col VARCHAR2(10));

Sql代码
澳门新葡亰平台官网 16 澳门新葡亰平台官网 17澳门新葡亰平台官网 18)

INSERT INTO t_row_str VALUES(1,’a’);
INSERT INTO t_row_str VALUES(1,’b’);
INSERT INTO t_row_str VALUES(1,’c’);
INSERT INTO t_row_str VALUES(2,’a’);
INSERT INTO t_row_str VALUES(2,’d’);
INSERT INTO t_row_str VALUES(2,’e’);
INSERT INTO t_row_str VALUES(3,’c’);
COMMIT;

  1. 查询树形的数据结构,同时对一层里面的数据进行排序   
  2. SELECT last_name, employee_id, manager_id, LEVEL  
  3.       FROM employees   
  4.       START WITH employee_id = 100
      
  5.       CONNECT BY PRIOR employee_id = manager_id   
  6.      <SPAN style=”BACKGROUND-COLOR: #ff0000″> ORDER SIBLINGS BY last_name;   
  7.   
  8.   
  9.   
  10.   
  11.   
  12. 下面是查询结果   
  13. LAST_NAME                 EMPLOYEE_ID MANAGER_ID      LEVEL  
  14. ————————- ———– ———- ———-
      
  15. King                              100                     1   
  16. Cambrault                         148        100          2   
  17. Bates                             172        148          3   
  18. Bloom                             169        148          3   
  19. Fox                               170        148          3   
  20. Kumar                             173        148          3   
  21. Ozer                              168        148          3   
  22. Smith                             171        148          3   
  23. De Haan                           102        100          2   
  24. Hunold                            103        102          3   
  25. Austin                            105        103          4   
  26. Ernst                             104        103          4   
  27. Lorentz                           107        103          4   
  28. Pataballa                         106        103          4   
  29. Errazuriz                         147        100          2   
  30. Ande                              166        147          3   
  31. Banda                             167        147          3   
  32.    

SELECT * FROM t_row_str;

查询树形的数据结构,同时对一层里面的数据进行排序 SELECT last_name,
employee_id, manager_id, LEVEL FROM employees START WITH employee_id
= 100 CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name;
下面是查询结果 LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL
————————- ———– ———- ———- King 100 1
Cambrault 148 100 2 Bates 172 148 3 Bloom 169 148 3 Fox 170 148 3 Kumar
173 148 3 Ozer 168 148 3 Smith 171 148 3 De Haan 102 100 2 Hunold 103
102 3 Austin 105 103 4 Ernst 104 103 4 Lorentz 107 103 4 Pataballa 106
103 4 Errazuriz 147 100 2 Ande 166 147 3 Banda 167 147 3

1)MAX + decode
适用范围:8i,9i,10g及以后版本
SELECT id,
MAX(decode(rn, 1, col, NULL)) ||
MAX(decode(rn, 2, ‘,’ || col, NULL)) ||
MAX(decode(rn, 3, ‘,’ || col, NULL)) str
FROM (SELECT id,
col,
row_number() over(PARTITION BY id ORDER BY col) AS rn
FROM t_row_str) t
GROUP BY id
ORDER BY 1;

 

2)row_number + lead
适用范围:8i,9i,10g及以后版本
SELECT id, str
FROM (SELECT id,
row_number() over(PARTITION BY id ORDER BY col) AS rn,
col || lead(‘,’ || col, 1) over(PARTITION BY id ORDER BY col) ||
lead(‘,’ || col, 2) over(PARTITION BY id ORDER BY col) ||
lead(‘,’ || col, 3) over(PARTITION BY id ORDER BY col) AS str
FROM t_row_str)
WHERE rn = 1
ORDER BY 1;

10.有时候写多了东西,居然还忘记最基本的sql语法,下面全部写出来,基本的oracle语句都在这里可以找到了。是很基础的语句!

3)MODEL
适用范围:10g及以后版本
SELECT id, substr(str, 2) str FROM t_row_str
MODEL
RETURN UPDATED ROWS
PARTITION BY(ID)
DIMENSION BY(row_number() over(PARTITION BY ID ORDER BY col) AS rn)
MEASURES (CAST(col AS VARCHAR2(20)) AS str)
RULES UPSERT
ITERATE(3) UNTIL( presentv(str[iteration_number+2],1,0)=0)
(str[0] = str[0] || ‘,’ || str[iteration_number+1])
ORDER BY 1;

Sql代码
澳门新葡亰平台官网 19 澳门新葡亰平台官网 20澳门新葡亰平台官网 21)

4)sys_connect_by_path
适用范围:8i,9i,10g及以后版本
SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ‘,’), 2)) str
FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col)
rn
FROM t_row_str) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND id = PRIOR id
GROUP BY t.id;

  1. 1.在数据字典查询约束的相关信息:   
  2. SELECT constraint_name, constraint_type,search_condition
      
  3. FROM        user_constraints WHERE        table_name = ‘EMPLOYEES’;   
  4.         //这里的表名都是大写!   
  5. 2对表结构进行说明:   
  6.    desc Tablename   
  7. 3查看用户下面有哪些表   
  8.    select table_name from user_tables;   
  9. 4查看约束在那个列上建立:   
  10.    SELECT constraint_name, column_name   
  11.    FROM        user_cons_columns
      
  12.    WHERE          table_name = ‘EMPLOYEES’;   
  13. 10结合变量查找相关某个表中约束的相关列名:   
  14.   select constraint_name,column_name from user_cons_columns where table_name = ‘&tablename’  
  15. 12查询数据字典看中间的元素:   
  16. SELECT   object_name, object_type
      
  17. FROM     user_objects   
  18. WHERE    object_name LIKE ‘EMP%’        
  19. OR       object_name LIKE ‘DEPT%’  
  20. 14查询对象类型:   
  21. SELECT DISTINCT object_type FROM           user_objects ;   
  22. 17改变对象名:(表名,视图,序列)   
  23.   rename  emp to emp_newTable   
  24. 18添加表的注释:   
  25.   COMMENT ON TABLE employees IS ‘Employee Information’;   
  26. 20查看视图结构:   
  27.    describe view_name   
  28. 23在数据字典中查看视图信息:   
  29.   select viewe_name,text from user_views   
  30. 25查看数据字典中的序列:   
  31.   select * from user_sequences   
  32. 33得到所有的时区名字信息:   
  33.         select  * from v$timezone_names   
  34. 34显示对时区‘US/Eastern’的时区偏移量   
  35.         select TZ_OFFSET(‘US/Eastern’) from DUAL–dual英文意思是‘双重的’   
  36.    显示当前会话时区中的当前日期和时间:   
  37.    ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24:MI:SS’;–修改显示时间的方式的设置   
  38.    ALTER SESSION SET TIME_ZONE = ‘-5:0’;–修改时区
      
  39.    SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;–真正有用的语句!   
  40.   SELECT CURRENT_TIMESTAMP FROM DUAL;–返回的时间是当前日期和时间,含有时区   
  41.   SELECT CURRENT_TIMESTAMP FROM DUAL;–返回的时间是当前日期和时间,不含有时区!!!  
      
  42. 35显示数据库时区和会话时区的值:   
  43.         select datimezone,sessiontimezone from dual;   
  44.   
  45. 13普通的建表语句:   
  46. CREATE TABLE dept   
  47. (deptno         NUMBER(2),   
  48. dname         VARCHAR2(14),   
  49. loc         VARCHAR2(13));   
  50. 15使用子查询建立表:   
  51.  CREATE TABLE         dept80   
  52.   AS  SELECT  employee_id, last_name,    
  53.             salary*12 ANNSAL,    
  54.             hire_date   FROM    employees   WHERE   department_id = 80;   
  55. 6添加列:// alter table EMP add column (dept_id number(7));错误!!   
  56.   alter table EMP add (dept_id number(7));   
  57. 7删除一列:   
  58.   alter table emp drop column dept_id;   
  59. 8添加列名同时和约束:   
  60.  alter table EMP add (dept_id number(7)    
  61.    constraint my_emp_dept_id_fk  references dept(ID));   
  62. 9改变列://注意约束不能够修改 的!!   
  63.  alter table dept80 modify(last_name varchar2(30));//这里使用的是modify而不是alter!   
  64. 24增加一行:   
  65.   insert into table_name values();   
  66.   
  67. 5添加主键:   
  68.  alter Table EMP  add constraint my_emp_id_pk primary key (ID);   

适用范围:10g及以后版本
SELECT t.id id, substr(sys_connect_by_path(t.col, ‘,’), 2) str
FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col)
rn
FROM t_row_str) t
WHERE connect_by_isleaf = 1
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND id = PRIOR id;

5)wmsys.wm_concat
适用范围:10g及以后版本
这个函数预定义按’,’分隔字符串,若要用其他符号分隔可以用,replace将’,’替换。

SELECT id, REPLACE(wmsys.wm_concat(col), ‘,’, ‘/’)
FROM t_row_str
GROUP BY id;

5.字符串转换成多列
其实际上就是一个字符串拆分的问题。

CREATE TABLE t_str_col AS
SELECT ID,c1||’,’||c2||’,’||c3 AS c123
FROM t_col_str;

SELECT * FROM t_str_col;

1)substr + instr
适用范围:8i,9i,10g及以后版本
SELECT id,
c123,
substr(c123, 1, instr(c123 || ‘,’, ‘,’, 1, 1) – 1) c1,
substr(c123,
instr(c123 || ‘,’, ‘,’, 1, 1) + 1,
instr(c123 || ‘,’, ‘,’, 1, 2) – instr(c123 || ‘,’, ‘,’, 1, 1) – 1) c2,
substr(c123,
instr(c123 || ‘,’, ‘,’, 1, 2) + 1,
instr(c123 || ‘,’, ‘,’, 1, 3) – instr(c123 || ‘,’, ‘,’, 1, 2) – 1) c3
FROM t_str_col
ORDER BY 1;

2)regexp_substr
适用范围:10g及以后版本
SELECT id,
c123,
rtrim(regexp_substr(c123 || ‘,’, ‘.*?’ || ‘,’, 1, 1), ‘,’) AS c1,
rtrim(regexp_substr(c123 || ‘,’, ‘.*?’ || ‘,’, 1, 2), ‘,’) AS c2,
rtrim(regexp_substr(c123 || ‘,’, ‘.*?’ || ‘,’, 1, 3), ‘,’) AS c3
FROM t_str_col
ORDER BY 1;

6.字符串转换成多行
CREATE TABLE t_str_row AS
SELECT id,
MAX(decode(rn, 1, col, NULL)) ||
MAX(decode(rn, 2, ‘,’ || col, NULL)) ||
MAX(decode(rn, 3, ‘,’ || col, NULL)) str
FROM (SELECT id,
col,
row_number() over(PARTITION BY id ORDER BY col) AS rn
FROM t_row_str) t
GROUP BY id
ORDER BY 1;

SELECT * FROM t_str_row;

1)UNION ALL
适用范围:8i,9i,10g及以后版本
SELECT id, 1 AS p, substr(str, 1, instr(str || ‘,’, ‘,’, 1, 1) – 1) AS
cv
FROM t_str_row
UNION ALL
SELECT id,
2 AS p,
substr(str,
instr(str || ‘,’, ‘,’, 1, 1) + 1,
instr(str || ‘,’, ‘,’, 1, 2) – instr(str || ‘,’, ‘,’, 1, 1) – 1) AS cv
FROM t_str_row
UNION ALL
SELECT id,
3 AS p,
substr(str,
instr(str || ‘,’, ‘,’, 1, 1) + 1,
instr(str || ‘,’, ‘,’, 1, 2) – instr(str || ‘,’, ‘,’, 1, 1) – 1) AS cv
FROM t_str_row
ORDER BY 1, 2;

适用范围:10g及以后版本
SELECT id, 1 AS p, rtrim(regexp_substr(str||’,’, ‘.*?’ || ‘,’, 1, 1),
‘,’) AS cv
FROM t_str_row
UNION ALL
SELECT id, 2 AS p, rtrim(regexp_substr(str||’,’, ‘.*?’ || ‘,’, 1, 2),
‘,’) AS cv
FROM t_str_row
UNION ALL
SELECT id, 3 AS p, rtrim(regexp_substr(str||’,’, ‘.*?’ || ‘,’,1,3),
‘,’) AS cv
FROM t_str_row
ORDER BY 1, 2;

2)VARRAY
适用范围:8i,9i,10g及以后版本
要创建一个可变数组:
CREATE OR REPLACE TYPE ins_seq_type IS VARRAY(8) OF NUMBER;

SELECT * FROM TABLE(ins_seq_type(1, 2, 3, 4, 5));

SELECT t.id,
c.column_value AS p,
substr(t.ca,
instr(t.ca, ‘,’, 1, c.column_value) + 1,
instr(t.ca, ‘,’, 1, c.column_value + 1) –
(instr(t.ca, ‘,’, 1, c.column_value) + 1)) AS cv
FROM (SELECT id,
‘,’ || str || ‘,’ AS ca,
length(str || ‘,’) – nvl(length(REPLACE(str, ‘,’)), 0) AS cnt
FROM t_str_row) t
INNER JOIN TABLE(ins_seq_type(1, 2, 3)) c ON c.column_value <=
t.cnt
ORDER BY 1, 2;

3)SEQUENCE series
这类方法主要是要产生一个连续的整数列,产生连续整数列的方法有很多,主要有:
CONNECT BY,ROWNUM+all_objects,CUBE等。
适用范围:8i,9i,10g及以后版本
SELECT t.id,
c.lv AS p,
substr(t.ca,
instr(t.ca, ‘,’, 1, c.lv) + 1,
instr(t.ca, ‘,’, 1, c.lv + 1) –
(instr(t.ca, ‘,’, 1, c.lv) + 1)) AS cv
FROM (SELECT id,
‘,’ || str || ‘,’ AS ca,
length(str || ‘,’) – nvl(length(REPLACE(str, ‘,’)), 0) AS cnt
FROM t_str_row) t,
(SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 5) c
WHERE c.lv <= t.cnt
ORDER BY 1, 2;

SELECT t.id,
c.rn AS p,
substr(t.ca,
instr(t.ca, ‘,’, 1, c.rn) + 1,
instr(t.ca, ‘,’, 1, c.rn + 1) –
(instr(t.ca, ‘,’, 1, c.rn) + 1)) AS cv
FROM (SELECT id,
‘,’ || str || ‘,’ AS ca,
length(str || ‘,’) – nvl(length(REPLACE(str, ‘,’)), 0) AS cnt
FROM t_str_row) t,
(SELECT rownum rn FROM all_objects WHERE rownum <= 5) c
WHERE c.rn <= t.cnt
ORDER BY 1, 2;

SELECT t.id,
c.cb AS p,
substr(t.ca,
instr(t.ca, ‘,’, 1, c.cb) + 1,
instr(t.ca, ‘,’, 1, c.cb + 1) –
(instr(t.ca, ‘,’, 1, c.cb) + 1)) AS cv
FROM (SELECT id,
‘,’ || str || ‘,’ AS ca,
length(str || ‘,’) – nvl(length(REPLACE(str, ‘,’)), 0) AS cnt
FROM t_str_row) t,
(SELECT rownum cb FROM (SELECT 1 FROM dual GROUP BY CUBE(1, 2))) c
WHERE c.cb <= t.cnt
ORDER BY 1, 2;

适用范围:10g及以后版本
SELECT t.id,
c.lv AS p,
rtrim(regexp_substr(t.str || ‘,’, ‘.*?’ || ‘,’, 1, c.lv), ‘,’) AS cv
FROM (SELECT id,
str,
length(regexp_replace(str || ‘,’, ‘[^’ || ‘,’ || ‘]’, NULL)) AS cnt
FROM t_str_row) t
INNER JOIN (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 5) c ON
c.lv <=
t.cnt
ORDER BY 1, 2;

4)Hierarchical + DBMS_RANDOM
适用范围:10g及以后版本
SELECT id,
LEVEL AS p,
rtrim(regexp_substr(str || ‘,’, ‘.*?’ || ‘,’, 1, LEVEL), ‘,’) AS cv
FROM t_str_row
CONNECT BY id = PRIOR id
AND PRIOR dbms_random.VALUE IS NOT NULL
AND LEVEL <=
length(regexp_replace(str || ‘,’, ‘[^’ || ‘,’ || ‘]’, NULL))
ORDER BY 1, 2;

5)Hierarchical + CONNECT_BY_ROOT
适用范围:10g及以后版本
SELECT id,
LEVEL AS p,
rtrim(regexp_substr(str || ‘,’, ‘.*?’ || ‘,’, 1, LEVEL), ‘,’) AS cv
FROM t_str_row
CONNECT BY id = connect_by_root id
AND LEVEL <=
length(regexp_replace(str || ‘,’, ‘[^’ || ‘,’ || ‘]’, NULL))
ORDER BY 1, 2;

6)MODEL
适用范围:10g及以后版本
SELECT id, p, cv FROM t_str_row
MODEL
RETURN UPDATED ROWS
PARTITION BY(ID)
DIMENSION BY( 0 AS p)
MEASURES( str||’,’ AS cv)
RULES UPSERT
(cv
[ FOR p
FROM 1 TO length(regexp_replace(cv[0],'[^’||’,’||’]’,null))
INCREMENT 1
] = rtrim(regexp_substr( cv[0],’.*?’||’,’,1,cv(p)),’,’))
ORDER BY 1,2;

end.

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图