Function getGrpColAmount(pGrpCols In Varchar2,vRows In Out type_str_arr) Return Pls_Integer Is /* 本函数的作用:计算分组字段的个数 */
Begin --一个自定义的函数,用于把用特定符号隔开的字符串分解到一个字符串数组中. pkg_bit.SpilitStr(pGrpcols,',',vRows); Return vRows.Count; End;
Function getJoinSql(pGrpcols In Varchar2) Return Varchar2 Is /* 本函数的作用:返回join中的条件,已经假定,两个表一定是x,y */ vCols type_str_arr:=type_str_arr(); vResult Varchar2(32767):=''; vColName Varchar2(30):='' ; --列名 Begin --一个自定义的函数,用于把用特定符号隔开的字符串分解到一个字符串数组中. pkg_bit.SpilitStr(pGrpcols,',',vCols); For i In 1..vCols.Count Loop vColName:=vCols(i); If i=1 Then vResult:='y.'||vColName||'=x.'||vColName; Else vResult:=vResult||' and y.'||vColName||'=x.'||vColName; End If; End Loop; Return vResult; End;
Function getIntoSql(pGrpCols In Varchar2,pColCol In Varchar2:='',pRowOrderCol In Varchar2:='') Return Varchar2 Is /* 本函数的作用:根据分组字段和转列来决定插入到临时表中所需要的子句sql pColCol目前是没有什么意义存在的. */ vTempStr Varchar2(32767):=pGrpCols||','||Case When pColCol Is Not Null Then pColCol||',' Else '' End; vPos Pls_Integer; vAmount Pls_Integer:=0; vResult Varchar2(32767):=''; Begin --行排序列放在首位. vTempstr:=Case When pRowOrderCol Is Not Null Then pRowOrderCol||',' Else '' End ||vTempstr;
vPos:=instr(vTempStr,','); While vpos>0 Loop vAmount:=vAmount+1; --组成输出字段 If vAmount=1 Then vResult:='C'||to_char(vAmount); Else vResult:=vResult||',C'||to_char(vAmount); End If; vTempStr:=substr(vTempStr,vpos+1); vPos:=instr(vTempStr,','); End Loop; Return vResult;
End;
Function getOrderSql(pGrpCols In Varchar2) Return Varchar Is /* 本函数的作用:组成排序字段. */ Begin Return getIntoSql(pGrpCols,''); End;
Begin --0)获得分组字段的个数 vGrpColAmount:=getGrpColAmount(grpCols,vRowNames);
--1)获得转为列之后的列名,列个数 v_sSql:=' Select Distinct '||colCol||' from ( '||viewName||' ) order by '||colCol; Execute Immediate v_sSql Bulk Collect Into vColNames; vColAmount:=vColNames.Count;
--2)把数据填充到临时数组表中
If colOrder Is Not Null Then --是用这个. vOrderCols2:=' x.'||replace(grpCols,',',',x.')||',x.'||colOrder||colOrderStyle; Else vOrderCols2:= ' x.'||replace(grpCols,',',',x.')||',x.'||colCol||colOrderStyle; End If;
--计算填充内容 If fillEmptyWithZero=1 And fillValue Is Not Null Then vFillValue:=fillValue; Elsif fillEmptyWithZero=1 And fillValue Is Null Then vFillValue:='0'; Elsif fillEmptyWithZero=0 Then vFillValue:=''; End If;
Execute Immediate 'truncate table tempdata_manyCols'; vJoinCols:=getJoinSql(grpCols); v_sSql:=' select type_str_arr('||Case When rowOrderinGrp=0 Then 'x.'||rowOrder||',' Else '' End||'x.'||replace(grpCols,',',',x.')||',x.'||colCol||','|| Case When vFillValue Is Not Null Then 'nvl('||valueCol||','''||vFillValue||''') ' Else valueCol End ||') from ( select a.*,b.* from (Select Distinct '||colCol||Case When colOrder Is Null Then '' Else ','||colOrder End||' from ('||viewName||')) a, (select distinct '||grpCols||Case When rowOrderinGrp=0 Then ','||rowOrder Else '' End||' from ('||viewName||')) b ) x left join ('||viewName||') y on '||vJoinCols||' and y.'||colCol||'=x.'||colCol||' order by '||vOrderCols2; Execute Immediate v_sSql Bulk Collect Into vDatas; Commit;
--3)通过矩阵转换,读取vDatas的内容,插入到tempdata_manycols vRecordAmount:=vDatas.Count; --设置插入列的SQL If rowOrderinGrp=1 Then --如果排序行的列在分组列中，那么就仅仅使用分组列即可. vIntoSqls:=getIntoSql(grpCols); Else --如果行的排序列不在分组列中，那么就再多插入一个列. vIntoSqls:=getIntoSql(grpCols,'',rowOrder); vSortAmount:=1; End If;
/*组合sql */ vDealRows:=0; For i In 1..vRecordAmount Loop --每一行 If Mod(i,vColAmount)=0 Then --当读取到足够一行数据的时候 vDealRows:=vDealRows+1; If vDealRows=1 Then --为了不至于搞错顺序,第一次需要把转列内容填到 --vColNames,也就是例子中的 lzf,wth...等部分 --同时,组合成真正的intosql For j In 1 ..vColAmount Loop --vsortAmount表示的是行排序列的个数. vColNames(j):=vDatas(j)(vGrpColAmount+vSortAmount+1); --dbms_output.put(lpad( vColNames(j-(vDealRows-1)*vColAmount),20,' ')); vIntoSqls:=vIntoSqls||',C'||to_char(vGrpColAmount+vSortAmount+j); End Loop;
End If;
v_sSql:=''; For x In 1..vGrpColAmount+vSortAmount Loop --设置值部分分组部分的内容和排序部分内容 v_sSql:=v_sSql||Case x When 1 Then '' Else ',' End||''''||vDatas(i)(x)||''''; End Loop; --读取值部分填充值的信息 --真正的值的位置=分组字段个数＋排序列个数＋2 For j In (vDealRows-1)*vColAmount+1..vDealRows*vColAmount Loop v_sSql:=v_sSql||','||Case When vDatas(j)(vGrpColAmount+vSortAmount+ 2) Is Null Then 'null' Else ''''||vDatas(j)(vGrpColAmount+vSortAmount+2)||'''' End; --dbms_output.put(lpad( vDatas(j)(vGrpColAmount+2),20,' ')); End Loop; --dbms_output.put_line('');
--组合成最后的SQL v_sSql:=' insert into tempdata_manycols('||vIntoSqls||') values(' ||v_sSql||')'; --dbms_output.put_line(v_sSql); Execute Immediate v_sSql; End If; End Loop;
Commit; --4)形成返回的sql /* 关键在于知道列名:=分组名称+行转列 */ /* If rowOrder Is Not Null And rowOrderinGrp=0 Then vStarColPos:=2; End If;*/ vResultsql:='select '; --分别是排序列，和转换列 For i In 1..vGrpColAmount+vColAmount Loop If i<=vgrpcolamount Then --前一部分的字段别名是分组字段名称,是外部传入的. vResultsql:=vResultsql||' '||(Case i When 1 Then '' Else ',' End)||'C'||(i+vSortAmount)||' as '||vRowNames(i); Else --后面一部分则是由原来行转过来的列的别名. vResultsql:=vResultsql||' '||(Case i When 1 Then '' Else ',' End)||'C'||(i+vSortAmount)||' as "'||vColNames(i-vGrpColAmount)||'"'; End If; End Loop; If rowOrder Is Null Then vOrderCols:=getordersql(grpCols); Else --vOrderCols:=rowOrder; --默认只有一个的情况下.正确的情况，应该是另外处理。 vOrderCols:=' c1 '; End If; vResultsql:=vResultsql||' from tempdata_manycols order by '||vOrderCols; return(vResultsql); Exception When Others Then Rollback; Return ''; end func_RowToCol;
SQL> Select func_RowToCol(' empsalary ','salmonth,country','empname','salary') From dual;
FUNC_ROWTOCOL('EMPSALARY','SAL -------------------------------------------------------------------------------- select C1 as salmonth ,C2 as country ,C3 as "fjl" ,C4 as "lxl" ,C5 as "lzf" ,C6 as "wcl" ,C7 as "wth" from tempdata_manycols order by C1,C2
SQL> select 2 cast(C1 As Varchar2(10)) as salmonth 3 ,cast(C2 As Varchar2(10)) as country 4 ,cast(C3 As Varchar2(10)) as "fjl" 5 ,cast(C4 As Varchar2(10)) as "lxl" 6 ,cast(C5 As Varchar2(10)) as "lzf" 7 ,cast(C6 As Varchar2(10)) as "wcl" 8 ,cast(C7 As Varchar2(10)) as "wth" 9 from tempdata_manycols 10 order by C1,C2 11 /
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有