美文网首页
oracle 运用存储过程解决动态行转列完成实际生产需要

oracle 运用存储过程解决动态行转列完成实际生产需要

作者: tangBiao_ | 来源:发表于2020-12-05 08:03 被阅读0次
    建表语句
    create table SalesList(
        keHu                varchar2(20),   --客户
        shangPinId          number(8),      --商品Id
        shangPin            varchar2(20),   --商品名称
        salesNum            number(8)       --销售数量
    );
    --插入数据
    declare
      --谈几个客户
      cursor lr_kh is 
      select regexp_substr('张三、李四、王五、赵六','[^、]+',1, level) keHu from dual
       connect by level <= 4;
      --进点货
      cursor lr_sp is 
      select level shangPinId, regexp_substr('上衣、裤子、袜子、帽子','[^、]+',1, level) shangPin from dual
       connect by level <= 4;
    begin
      --循环插入
      for v_kh in lr_kh loop
         for v_sp in lr_sp loop
            insert into SalesList
            select v_kh.keHu, v_sp.shangPinId, v_sp.shangPin, floor(dbms_random.value(10,50)) from dual;
         end loop;
      end loop;
      commit;
    end;
    /
    
    执行select * from SalesList; 得到下列结果
       
        KEHU    SHANGPINID  SHANGPIN    SALESNUM
    1   张三  1   上衣  33
    2   张三  2   裤子  25
    3   张三  3   袜子  12
    4   张三  4   帽子  49
    5   李四  1   上衣  19
    6   李四  2   裤子  29
    7   李四  3   袜子  24
    8   李四  4   帽子  12
    9   王五  1   上衣  38
    10  王五  2   裤子  35
    11  王五  3   袜子  17
    12  王五  4   帽子  13
    13  赵六  1   上衣  19
    14  赵六  2   裤子  21
    15  赵六  3   袜子  20
    16  赵六  4   帽子  12
    
    --固定行转列
    select *
      from (select kehu, shangPin, salesNum from salesList) pivot(
        max(salesNum) for shangPin in (
            '上衣' as 上衣,
            '裤子' as 裤子,
            '袜子' as 袜子,
            '帽子' as 帽子
        )
      );
    --动态行转列
    call p_RowsToCols('select keHu, shangPin, salesNum from salesList',
                      'select distinct shangPinId, shangPin cols from salesList order by shangPinId',
                      'max(salesNum)',
                      'shangPin',
                      'sales_RowsToCols');
     
    --以上两种查询结果均为:
        KEHU    上衣  裤子  袜子  帽子
    1   王五  38  35  17  13
    2   赵六  19  21  20  12
    3   李四  19  29  24  12
    4   张三  33  25  12  49
    
    --动态行转列执行得是以下存储过程:
    create or replace procedure p_RowsToCols(as_sql       in varchar2 --源数据的查询sql
                                            ,as_sql_cols  in varchar2 --动态转换列的查询sql,要求转为列的那列,字段名必须为cols,支持排序
                                            ,as_aggCol    in varchar2 --对应pivot函数的 聚合函数
                                            ,as_changeCol in varchar2 --源数据中,要转为列的字段名
                                            ,as_viewName  in varchar2 --结果输出的视图名,执行完后查此视图即可
                                             ) is
      ls_sql varchar2(4000);
      ls_in  varchar2(4000);
    begin
      --拼接in的内容
      ls_sql := 'select listagg(''''''''||cols||'''''' "''||cols||''"'', '','')within group(order by rn) ' || 
                  'from (select rownum rn, cols from (' || as_sql_cols || '))';
      execute immediate ls_sql
        into ls_in;
     
      --创建视图
      ls_sql := 'create or replace view ' || as_viewName ||' as ' ||
                'select * from (' || as_sql || ') ' ||
                 'pivot (' || as_aggCol || ' for ' || as_changeCol || ' in (' || ls_in || '))';
      execute immediate ls_sql;
    end p_RowsToCols;
    

    相关文章

      网友评论

          本文标题:oracle 运用存储过程解决动态行转列完成实际生产需要

          本文链接:https://www.haomeiwen.com/subject/uniwwktx.html