1、Oracle Function
--入参：表 id
/*
cxcyxm02 ：操作表，含有一个存储另一张表（xs0101）多个主键id的字段
ids ：操作表的主键 id
*/
CREATE OR REPLACE FUNCTION cxcyxmcyry(ids VARCHAR2) RETURN VARCHAR2 IS tempcyryxx VARCHAR2(1000); cyryxx VARCHAR2(1000); cxcylength number; i number;BEGIN i := 0; select LENGTH(t.cymc) - LENGTH(REGEXP_REPLACE(t.cymc, ',', '')) + 1 as leng into cxcylength from cxcyxm02 t select nvl(xm ||'/'|| xh, '') into tempcyryxx where cxcyxm02id = ids; while i < cxcylength loop if i = 0 then from xs0101 where xs0101id = (select substr(concat(t.cymc, ','), 0, instr(concat(t.cymc, ','), ',', 1, 1) - 1) from cxcyxm02 t where t.cxcyxm02id = ids); else select nvl(xm || xh, '') into tempcyryxx from xs0101 where xs0101id = (select substr(concat(t.cymc, ','), instr(concat(t.cymc, ','), ',', 1, i) + 1, instr(concat(t.cymc, ','), ',', 1, i + 1) - instr(concat(t.cymc, ','), ',', 1, i) - 1) from cxcyxm02 t where t.cxcyxm02id = ids); end if; i := i + 1; if i = cxcylength then cyryxx := cyryxx || tempcyryxx; else cyryxx := cyryxx || tempcyryxx || ','; end if; end loop; return cyryxx;
END cxcyxmcyry;
2. Oracle自带函数
connect by level 是Oracle中一组关键字，是用来实现递归查询的，譬如说实现查询 1,2,3,4 .....n 的数字可以使用connect by level;
select level +1 from dual where connect by level <=10 ;这种方式可以实现查询1到n的数字，共有n行;select level from dual connect by level < 10;
REGEXP_SUBSTR函数格式如下：function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)__srcstr ：需要进行正则处理的字符串__pattern ：进行匹配的正则表达式__position ：起始位置，从第几个字符开始正则表达式匹配（默认为1）__occurrence ：标识第几个匹配组，默认为1__modifier ：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）实际应用如下：在Oracle中，使用一条语句实现将'34,56,-23'拆分成'34'，'56'，'-23'的集合
SELECT REGEXP_SUBSTR('34,56,-23', '[^,]+', 1, LEVEL, 'i') AS STRFROM DUALCONNECT BY LEVEL <= LENGTH('34,56,-23') -LENGTH(REGEXP_REPLACE('34,56,-23', ',', '')) + 1;
Oracle 行转列pivot 、列转行unpivot 的Sql语句总结http://blog.csdn.net/xiaokui_wingfly/article/details/42419207
转载于:https://www.cnblogs.com/laoyin666/p/7466338.html
