精华内容
下载资源
问答
  • 【转】oracle行转列通用过程

    千次阅读 2009-12-22 18:26:00
    oracle行转列通用过程(转) 环境oracle 10g工作关系,常做些行转列报表,报表通常不是在大数据集合上处理.所以写了个过程.本过程比较适合在于需要动态输出报表的地方,例如web中.不是很完美,但已经可以解决绝大部分的...

    oracle行转列通用过程(转)

    环境oracle 10g
    工作关系,常做些行转列报表,报表通常不是在大数据集合上处理.
    所以写了个过程.
    本过程比较适合在于需要动态输出报表的地方,例如web中.

    不是很完美,但已经可以解决绝大部分的问题.
    create or replace function func_RowToCol(
    viewName Varchar2,
    grpCols Varchar2,
    colCol Varchar2,
    valueCol Varchar2,
    fillEmptyWithZero Number:=1,
    rowOrder Varchar2:='',
    colOrder Varchar2:='',
    rowOrderinGrp Integer:=1,
    colOrderStyle Varchar2:=' asc ',
    fillValue Varchar2:=''
    ) return varchar2
    Is
    /*****************************************************************************************************
    名称:func_RowToCol

    参数说明:
    viewName 视图名称,实际上可以是数据库的表格名称,视图名称,也可以是SQL语句.

    grpCols 需要分组的列,以格式 col1,col2..coln传入,其中n是大于0的整数
    colCol 由行转为列的那个列
    valueCol 行转列后,依然作为值填充的那个列,只能是一个列
    --viewIsSql 视图是否是sql语句,如果是则传入1,反之传入2,默认是1(是sql)
    fillEmptyWithZero 用0来填充空值,默认空值依然保留空值.如果是1,则只对valueCol为数值类型的有效.
    rowOrder 结果默认的排序语句,如果有,则使用这个,这个是对结果的行排序
    colOrder 对转成的列进行排序的依据.
    rowOrderinGrp 行的排序列是否在分组列(grpcols)中,0 表示不是,1表示是,默认是在分组列中。
    colOrderStyle 这个参数说明了列的排序方式
    fillValue 填充值,如果非空,且fillEmptyWithZero=1,则用.

    举例:有一个表格EmpSalary(SalMonth number,EmpName varchar2(20),salary number) 其中
    salMonth,EmpName组成唯一约束
    假设有以下数据:
    SALMONTH EMPNAME SALARY
    ---------- -------------------- ----------
    200801 lzf 8000
    200801 wth 8000
    200801 lxl 7000
    200801 fjl 8000
    200801 wcl 40000
    200802 lzf 9000
    200802 wth 8000
    ....
    现在需要按照这样的格式输出
    salaryMonth lzf wth lxl fjl wcl
    200801 8000 8000 7000 8000 40000
    200801 9000 8000

    那么参数应该这样传递func_RowToCol('empsalary','','salarymonth','empname','salary',0,1);
    输出:
    如果成功,则返回一个基于tempdata_manycols的查询sql字符串
    如果失败,则返回空值.

    注意事项:
    本函数是基于一个叫tempdata_manyCols的全局临时表处理的.
    并且有以下假设:
    1)固定列加动态列不超过300列
    2)原来的一个列(只能有一个列)作为行内容填充的新形成后的表格中.
    3)只能处理数字或者字符的信息,如果是字符,不能超过2000个.但本函数的colCol的值不应该超过30个B.
    因为太长的话,行转列就没有意义了(根本没有办法看),同时oracle也不支持超过30B的列名
    4)********************************不建议的事情***********************************************
    *不建议对一个巨大的记录集合进行行专列操作,否则可能效率之低下是难于想象
    *盖因为行转列通常用于编写报表之用.
    *也严重不建议,传入的视图是基于一个很耗时的复杂查询
    *最后,如果您的sql大于32K左右,本过程无能为力!
    *******************************************************************************************

    5)严重警告:数据源必须有数据,其次分组列应该都有数据,
    6)不接受需要把数据聚集之后再显示的数据,最好自行先聚集.

    修改记录:
    ------------------------------------------------------------
    2009-03-11 lzf 新增
    2009-03-12 lzf 完成初稿,可以在简单代码上测试成功.
    2009-03-27 lzf 增加一个控制转换列输出的功能 colOrder
    2009-04-01 lzf 修改,以便更完善
    2009-04-07 lzf 修改,增加了列排序的方式,为了节约时间,不再调整参数顺序.
    增加了一个填充值,以便按照要求来填充需要的内容.
    *****************************************************************************************************
    */
    Pragma Autonomous_Transaction;

    vResultsql varchar2(32767):='';

    -----处理临时数据的变量
    vDatas type_str_arrs:=type_str_arrs();

    vDealRows pls_integer:=0;
    ----最终存放数据的地方
    vColNames type_str_arr:=type_str_arr(); --列名数组
    vColAmount pls_integer:=0; --列的个数
    vRowNames type_str_arr:=type_str_arr(); --行的内容

    vGrpColAmount pls_integer:=0; --分组字段的个数,即grpCols的字段个数.
    --vStarColPos pls_integer:=1; --返回的起点列标号,默认是1,但是如果行分组列不在其中,则从2开始
    vSortAmount pls_integer:=0;

    vIntoSqls Varchar2(32767):=''; --用于存储插入到tempdata_manycols的into脚本
    vJoinCols Varchar2(32767):=''; --插入和分组的字段
    vOrderCols Varchar2(32767):=''; --排序的字段
    vOrderCols2 Varchar2(32767):='';

    vRecordAmount pls_integer:=0; --原始数据记录数

    --填充值
    vFillValue Varchar2(1000):='';

    v_sSql Varchar2(32767);


    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 * from empsalary;

    SALMONTH EMPNAME SALARY COUNTRY
    ---------- -------------------- ---------- --------------------
    200801 lzf 8000 中国
    200801 wth 8000 美国
    200801 lxl 7000 日本
    200801 fjl 7000 巴基斯坦
    200801 wcl 40000 美国
    200802 lzf 9000 中国
    200802 wth 8000 美国
    200802 lxl 8500 日本

    8 rows selected

    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 /

    SALMONTH COUNTRY fjl lxl lzf wcl wth
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
    200801 巴基斯坦 7000 0 0 0 0
    200801 美国 0 0 0 40000 8000
    200801 日本 0 7000 0 0 0
    200801 中国 0 0 8000 0 0
    200802 美国 0 0 0 0 8000
    200802 日本 0 8500 0 0 0
    200802 中国 0 0 9000 0 0

    7 rows selected
    展开全文
  • ORACLE存储过程

    万次阅读 多人点赞 2018-11-02 18:14:48
    oracle存储过程 目录 一.什么是存储过程 二.为什么要写存储过程 三.存储过程基础 1.存储过程结构 2.存储过程语法 3.pl/sql处理存储过程 四.存储过程进阶 1.BUIK COLLECT 2.FORALL 3.pl/sql调试存储过程 ...

                                                  oracle存储过程

    目录

             一.什么是存储过程

    二.为什么要写存储过程

    三.存储过程基础

    1.存储过程结构

    2.存储过程语法

    3.pl/sql处理存储过程

    四.存储过程进阶

    1.BUIK COLLECT

    2.FORALL

    3.pl/sql调试存储过程

    4.案例实战

    附.参考资料


    一.什么是存储过程

    存储过程,百度百科上是这样解释的,存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来调用存储过程。

    简单的说就是专门干一件事一段sql语句。

    可以由数据库自己去调用,也可以由java程序去调用。

    在oracle数据库中存储过程是procedure。

    二.为什么要写存储过程

    1.效率高

      存储过程编译一次后,就会存到数据库,每次调用时都直接执行。而普通的sql语句我们要保存到其他地方(例如:记事本  上),都要先分析编译才会执行。所以想对而言存储过程效率更高。

    2.降低网络流量

    存储过程编译好会放在数据库,我们在远程调用时,不会传输大量的字符串类型的sql语句。

    3.复用性高

    存储过程往往是针对一个特定的功能编写的,当再需要完成这个特定的功能时,可以再次调用该存储过程。

    4.可维护性高

    当功能要求发生小的变化时,修改之前的存储过程比较容易,花费精力少。

    5.安全性高

    完成某个特定功能的存储过程一般只有特定的用户可以使用,具有使用身份限制,更安全。

    三.存储过程基础

    1.存储过程结构

    (1).基本结构

    Oracle存储过程包含三部分:过程声明,执行过程部分,存储过程异常(可写可不写,要增强脚本的容错性和调试的方便性那就写上异常处理)

    (2).无参存储过程

    CREATE OR REPLACE PROCEDURE demo AS/IS
    	变量2 DATE;
    	变量3 NUMBER;
    BEGIN
    	--要处理的业务逻辑
    	EXCEPTION    --存储过程异常
    END 
    

    这里的as和is一样任选一个,在这里没有区别,其中demo是存储过程名称。

    (3).有参存储过程

    a.带参数的存储过程

    CREATE OR REPLACE PROCEDURE 存储过程名称(param1 student.id%TYPE)
    AS/IS
    name student.name%TYPE;
    age number :=20;
    BEGIN
      --业务处理.....
    END

    上面脚本中,

    第1行:param1 是参数,类型和student表id字段的类型一样。

    第3行:声明变量name,类型是student表name字段的类型(同上)。

    第4行:声明变量age,类型数数字,初始化为20

     

    b.带参数的存储过程并且进行赋值

    CREATE OR REPLACE PROCEDURE 存储过程名称(
           s_no in varchar,
           s_name out varchar,
           s_age number) AS
    total NUMBER := 0;
    BEGIN
      SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
      dbms_output.put_line('符合该年龄的学生有'||total||'人');
      EXCEPTION
        WHEN too_many_rows THEN 
        DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 
    END

    上面脚本中:

    其中参数IN表示输入参数,是参数的默认模式。
    OUT表示返回值参数,类型可以使用任意Oracle中的合法类型。
    OUT模式定义的参数只能在过程体内部赋值,表示该参数可以将某个值传递回调用他的过程
    IN OUT表示该参数可以向该过程中传递值,也可以将某个值传出去

    第7行:查询语句,把参数s_age作为过滤条件,INTO关键字,把查到的结果赋给total变量。

    第8行:输出查询结果,在数据库中“||”用来连接字符串

    第9—11行:做异常处理

    2.存储过程语法

    (1).运算符

    这里s,m,n是变量,类型是number;

    分类

    运算符

    含义

    示例表达式

     

     

     

    算术运算符

    +

    s := 2 + 2;

    -

    s := 3 – 1;

    *

    s := 2 * 3;

    /

    s := 6 / 2;

    mod(,)

    取模,取余

    m : = mod(3,2)

    **

    乘方

    10**2 =100

     

     

     

    关系运算符

    =

    等于

    s = 2

    <>或!=或~=

    不等于

    s != 2

    <

    小于

    s < 3

    >

    大于

    s > 0

    <=

    小于等于

    s <= 9

    >=

    大于等于

    s >= 1

     

     

    比较运算符

    LIKE

    满足匹配为true

    ‘li’ like ‘%i’返回true

    BETWEEN

    是否处于一个范围中

    2 between 1 and 3 返回true

    IN

    是否处于一个集合中

    ‘x’ in (‘x’,’y’) 返回true

    IS NULL

    判断变量是否为空

    若:n:=3,n is null,返回false

     

    逻辑运算符

    AND

    逻辑与

    s=3 and c is null

    OR

    逻辑或

    s=3 or c is null

    NOT

    逻辑非

    not c is null

     

    其他

    :=

    赋值

    s := 0;

    ..

    范围

    1..9,即1至9范围

    ||

    字符串连接

    ‘hello’||’world’

     

    (2).SELECT INTO STATEMENT语句

    该语句将select到的结果赋值给一个或多个变量,例如:

    CREATE OR REPLACE PROCEDURE DEMO_CDD1 IS
    s_name VARCHAR2;   --学生名称
    s_age NUMBER;      --学生年龄
    s_address VARCHAR2; --学生籍贯
    BEGIN
      --给单个变量赋值
      SELECT student_address INTO s_address
      FROM student where student_grade=100;
       --给多个变量赋值
      SELECT student_name,student_age INTO s_name,s_age
      FROM student where student_grade=100;
      --输出成绩为100分的那个学生信息
      dbms_output.put_line('姓名:'||s_name||',年龄:'||s_age||',籍贯:'||s_address);
    END

    上面脚本中:

    存储过程名称:DEMO_CDD1, student是学生表,要求查出成绩为100分的那个学生的姓名,年龄,籍贯

    (3).选择语句

    a.IF..END IF

    学生表的sex字段:1-男生;0-女生

    IF s_sex=1 THEN
      dbms_output.put_line('这个学生是男生');
    END IF

    b.IF..ELSE..END IF

    IF s_sex=1 THEN
      dbms_output.put_line('这个学生是男生');
    ELSE
      dbms_output.put_line('这个学生是女生');
    END IF

    (4).循环语句

    a.基本循环

    LOOP
      IF 表达式 THEN
        EXIT;
      END IF
    END LOOP;

    b.while循环

    WHILE 表达式 LOOP
      dbms_output.put_line('haha');
    END LOOP;

    c.for循环

    FOR a in 10 .. 20 LOOP
      dbms_output.put_line('value of a: ' || a);
    END LOOP;

    (5).游标

        Oracle会创建一个存储区域,被称为上下文区域,用于处理SQL语句,其中包含需要处理的语句,例如所有的信息,行数处理,等等。

        游标是指向这一上下文的区域。 PL/SQL通过控制光标在上下文区域。游标持有的行(一个或多个)SQL语句返回。行集合光标保持的被称为活动集合。

    a.下表是常用的游标属性

    属性

    描述

    %FOUND

    如果DML语句执行后影响有数据被更新或DQL查到了结果,返回true。否则,返回false。

    %NOTFOUND

    如果DML语句执行后影响有数据被更新或DQL查到了结果,返回false。否则,返回true。

    %ISOPEN

    游标打开时返回true,反之,返回false。

    %ROWCOUNT

    返回DML执行后影响的行数。

    b.使用游标

    声明游标定义游标的名称和相关的SELECT语句:

    CURSOR cur_cdd IS SELECT s_id, s_name FROM student;

    打开游标游标分配内存,使得它准备取的SQL语句转换成它返回的行:

    OPEN cur_cdd;

    抓取游标中的数据,可用LIMIT关键字来限制条数,如果没有默认每次抓取一条:

    FETCH cur_cdd INTO id, name ;

    关闭游标来释放分配的内存:

    CLOSE cur_cdd;

    3.pl/sql处理存储过程

    (1).新建存储过程:右键procedures,点击new,弹出PROCEDURE框,再点击OK,如下图:

     

    (2).在下面的编辑区,编写存储过程脚本

     

    (3).在这里我们编写一个demo_cdd存储过程,要求输出“hello world”,如下图:

     

    (4).右键刚才新建的存储过程名称,点击“Test”,在点击执行按钮

     

    4.案例实战

    场景:

    有表student(s_no, s_name, s_age, s_grade),其中s_no-学号,也是主键,是从1开始向上排的(例如:第一个学生学号是1,第二个是2,一次类推);s_name-学生姓名;s_age-学生年龄;s_grade-年级;这张表的数据量有几千万甚至上亿。一个学年结束了,我要让这些学生全部升一年级,即,让s_grade字段加1。

    这条sql,写出来如下:

    update student set s_grade=s_grade+1

    分析:

    如果我们直接运行运行这条sql,因数据量太大会把数据库undo表空间撑爆,从而发生异常。那我们来写个存储过程,进行批量更新,我们每10万条提交一次。

    CREATE OR REPLACE PROCEDURE process_student is
    total NUMBER := 0;
    i NUMBER := 0;
    BEGIN
      SELECT COUNT(1) INTO total FROM student;
      WHILE i<=total LOOP
        UPDATE student SET grade=grade+1 WHERE s_no=i;
        i := i + 1;
        IF i >= 100000 THEN
          COMMIT;
        END IF;
      END LOOP;
      dbms_output.put_line('finished!');
    END;
    

    四.存储过程进阶

           在上面的案例中,我们的存储过程处理完所有数据要多长时间呢?事实我没有等到它执行完,在我可接受的时间范围内它没有完成。那么对于处理这种千万级数据量的情况,存储过程是不是束手无策呢?答案是否定的,接下来我们看看其他绝招。

           我们先来分析下执行过程的执行过程:一个存储过程编译后,在一条语句一条语句的执行时,如果遇到pl/sql语句就拿去给pl/sql引擎执行,如果遇到sql语句就送到sql引擎执行,然后把执行结果再返回给pl/sql引擎。遇到一个大数据量的更新,则执行焦点(正在执行的,状态处于ACTIVE)会不断的来回切换。

           Pl/SQL与SQL引擎之间的通信则称之为上下文切换,过多的上下文切换将带来过量的性能负载。最终导致效率降低,处理速度缓慢。

           从Oracle8i开始PL/SQL引入了两个新的数据操纵语句:FORALLBUIK COLLECT,这些语句大大滴减少了上下文切换次数(一次切换多次执行),同时提高DML性能,因此运用了这些语句的存储过程在处理大量数据时速度简直和飞一样。

    1.BUIK COLLECT

        Oracle8i中首次引入了Bulk Collect特性,Bulk Collect会能进行批量检索,会将检索结果结果一次性绑定到一个集合变量中,而不是通过游标cursor一条一条的检索处理。可以在SELECT INTO、FETCH INTO、RETURNING INTO语句中使用BULK COLLECT,接下来我们一起看看这些语句中是如何使用BULK COLLECT的。

    (1).SELECT INTO

    查出来一个结果集合赋值给一个集合变量。

    语法结构是:

    SELECT field BULK COLLECT INTO var_conllect FROM table where colStatement;

    说明:

           field:要查询的字段,可以是一个或多个(要保证和后面的集合变量要向对应)。

           var_collect:集合变量(联合数组等),用来存放查到的结果。

           table:表名,要查询的表。

           colStatement:后面过滤条件语句。比如s_age < 10;

    例子:查出年龄小于10岁的学生姓名赋值给数组arr_name变量

    SELECT s_name BULK COLLECT INTO arr_name FROM s_age < 10;

    (2).FETCH INTO

    从一个集合中抓取一部分数据赋值给一个集合变量。

    语法结构如下:

    FETCH cur1 BULK COLLECT INTO var_collect [LIMIT rows]

    说明:

            cur1:是个数据集合,例如是个游标。

            var_collect:含义同上。

            [LIMIT rows]:可有可无,限制每次抓取的数据量。不写的话,默认每次一条数据。

    例子:给年龄小于10岁的学生的年级降一级。

    --查询年龄小于10岁的学生的学号放在游标cur_no里
    CURSOR cur_no IS 
    		SELECT s_no FROM student WHERE s_age < 10;
    
    --声明了一个联合数组类型,元素类型和游标cur_no每个元素的类型一致
    TYPE ARR_NO IS VARRAY(10) OF cur_no%ROWTYPE;
    
    --声明一个该数组类型的变量no
    no ARR_NO;
    BEGIN
      FETCH cur_no BULK COLLECT INTO no LIMIT 100;
      FORALL i IN 1..no.count SAVE EXCEPTONS
    	UPDATE student SET s_grade=s_grade-1 WHERE no(i);
    END;
    

    说明:先查出年龄小于10岁的学生的学号放在游标里,再每次从游标里拿出100个学号,进行更新,给他们的年级降一级。

    (3).RETURNING

    BULK COLLECT除了与SELECT,FETCH进行批量绑定之外,还可以与INSERT,DELETE,UPDATE语句结合使用,可以返回这些DML语句执行后所影响的记录内容(某些字段)。

    再看一眼学生表的字段情况:student(s_no, s_name, s_age, s_grade)

    语法结构如下:

    DMLStatement
           RETURNING field BULK COLLECT INTO var_field;

    说明:

            DMLStatement:是一个DML语句。

            field:是这个表的某个字段,当然也可以写多个逗号隔开(field1,field2, field3)。

            var_field:一个类型为该字段类型的集合,多个的话用逗号隔开,如下:

            (var_field1, var_field2, var_field3)

     

    例子:获取那些因为年龄小于10岁而年级被将一级的学生的姓名集合。

    TYPE NAME_COLLECT IS TABLE OF student.s_name%TYPE;
    names NAME_COLLECT;
    BEGIN
      UPDATE student SET s_grade=s_grade-1 WHERE s_age < 10
      RETURNING s_name BULK COLLECT INTO names;
    END;

    说明:

           NAME_COLLECT:是一个集合类型,类型是student表的name字段的类型。

           names:定义了一个NAME_COLLECT类型的变量。

    (4).注意事项

    a.不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。

    b.只能在服务器端的程序中使用BULK COLLECT,如果在客户端使用,就会产生一个不支持这个特性的错误。

    c.BULK COLLECT INTO 的目标对象必须是集合类型。

    d.复合目标(如对象类型)不能在RETURNING INTO 子句中使用。

    e.如果有多个隐式的数据类型转换的情况存在,多重复合目标就不能在BULK COLLECT INTO 子句中使用。

    f.如果有一个隐式的数据类型转换,复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。

    2.FORALL

    (1).语法

    FORALL index IN bounds [SAVE EXCEPTIONS]     
         sqlStatement;

    说明:

            index是指下标;

            bounds是一个边界,形式是start..end

            [SAVE EXCEPTIONS] 可写可不写,这个下面介绍;

            sqlStatement是一个DML语句,这里有且仅有一个sql语句;

    例子:

    --例子1:移除年级是5到10之间的学生
    FORALL i IN 5..10
           DELETE FROM student where s_grade=i;
    --例子:2,arr是一个数组,存着要升高一年级的学生名称
    FORALL s IN 1..arr.count SAVE EXCEPTIONS
           UPDATE student SET s_grade=s_grade+1 WHERE s_name=arr(i);

    (2).SAVE EXCEPTIONS

    通常情况写我们在执行DML语句时,可能会遇到异常,可能致使某个语句或整个事务回滚。如果我们写FORALL语句时没有用SAVE EXCEPTIONS语句,那么DML语句会在执行到一半的时候停下来。

           如果我们的FORALL语句后使用了SAVE EXCEPTIONS语句,当在执行过程中如果遇到异常,数据处理会继续向下进行,发生的异常信息会保存到SQL%BULK_EXCEPTONS的游标属性中,该游标属性是个记录集合,每条记录有两个字段,例如:(1, 02300);

           ERROR_INDEX:该字段会存储发生异常的FORALL语句的迭代编号;

           ERROR_CODE:存储对应异常的,oracle错误代码;

    SQL%BULK_EXCEPTONS这个异常信息总是存储着最近一次执行的FORALL语句可能发生的异常。而这个异常记录集合异常的个数则由它的COUNT属性表示,即:

           SQL%BULK_EXCEPTONS.COUNT,SQL%BULK_EXCEPTIONS有效的下标索引范围在1到%BULK_EXCEPTIONS.COUNT之间。

    (3). INDICES OF

    在Oracle数据库10g之前有一个重要的限制,该数据库从IN范围子句中的第一行到最后一行,依次读取集合的内容,如果在该范围内遇到一个未定义的行,Oracle数据库将引发ORA-22160异常事件:ORA-22160: element at index [N] does not exist。针对这一问题,Oracle后续又提供了两个新语句:INDICES OF 和 VALUES OF。

    接下来我们来看看这个INDICES OF语句,用于处理稀疏数组或包含有间隙的数组(例如:一个集合的某些元素被删除了)。

    该语句语法结构是:

    FORALL i INDICES OF collection [SAVE EXCEPTIONS]
    
           sqlStatement;

    说明:

    i:集合(嵌套表或联合数组)下标。

    collection:是这个集合。

    [SAVE EXCEPTIONS]和sqlStatement上面已经解释过。

    例子:arr_std是一个联合数组,每个元素包含(name,age,grade),现在要向student表插入数据。

    FORALL i IN INDICES OF arr_stu
           INSERT INTO student VALUES(
               arr_stu(i).name,
                  arr_stu(i).age,
                  arr_stu(i).grade
           );

    (4). VALUES OF

    VALUES OF适用情况:绑定数组可以是稀疏数组,也可以不是,但我只想使用该数组中元素的一个子集。VALUES OF选项可以指定FORALL语句中循环计数器的值来自于指定集合中元素的值。但是,VALUES OF在使用时有一些限制:

           如果VALUES OF子句中所使用的集合是联合数组,则必须使用PLS_INTEGER和BINARY_INTEGER进行索引,VALUES OF 子句中所使用的元素必须是PLS_INTEGER或BINARY_INTEGER;

           当VALUES OF 子句所引用的集合为空,则FORALL语句会导致异常;

    该语句的语法结构是:

    FORALL i IN VALUES OF collection [SAVE EXCEPTIONS]
           sqlStatement;

    说明:i和collection含义如上

    联合数组请看文章(或自行百度):https://blog.csdn.net/leshami/article/details/7372061

    3.pl/sql调试存储过程

    首先,当前这个用户得有能调试存储过程的权限,如果没有的话,以数据库管理员身份给你这个用户授权:

    --userName是你要拿到调试存储过程权限的用户名
    GRANT DEBUG ANY PROCEDURE,DEBUG CONNECT SESSION TO username;

    (1).右键一个存储过程名称,点击测试,如下图:

    这里我用的pl/sql是12.0.4版本的,下面截图中与低版本的pl/sql按钮位置都相同,只是图标不一样。

     

    (2).点击两次step into按钮,进入语句调试,如下图:

     

    (3).每点击一次step into按钮,会想下执行一条语句,也可以查看变量和表达式的值,如下图:

     

    查看变量值:在查看变量区域,在Variable列输入变量i,在Value列点击下,该变量的值就显示出来了。

    4.案例实战

    场景和上面的案例实战是同一个,如下:

    有表student(s_no, s_name, s_age, s_grade),其中s_no-学号,也是主键,是从1开始向上排的(例如:第一个学生学号是1,第二个是2,一次类推);s_name-学生姓名;s_age-学生年龄;s_grade-年级;这张表的数据量有几千万甚至上亿。一个学年结束了,我要让这些学生全部升一年级,即,让s_grade字段加1。

    这条sql,写出来如下:

    update student set s_grade=s_grade+1

    编写存储过程:

    (1).存储过程1

    名称为:process_student1,student表的s_no字段类型为varchar2(16)。

    CREATE OR REPLACE PROCEDURE process_student1 AS
        CURSOR CUR_STUDENT IS SELECT s_no FROM student;
        TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
        students REC_STUDENT;
    BEGIN
      OPEN CUR_STUDENT;
      WHILE (TRUE) LOOP
        FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
        FORALL i IN 1..students.count SAVE EXCEPTIONS
          UPDATE student SET s_grade=s_grade+1 WHERE s_no=students(i);
        COMMIT;
        EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
      END LOO;
      dbms_output.put_line('finished');
    END;

    说明:

            把student表中要更新的记录的学号拿出来放在游标CUR_STUDENT,每次从这个游标里抓取10万条数据赋值给数组students,每次更新这10万条记录。循环进行直到游标里的数据全部抓取完。

            FETCH .. BULK COLLECT INTO .. LIMIT rows语句中:这个rows我测试目前最大可以为10万条。

    (2).存储过程2(ROWID)

           如果我们这个student表没有主键,也没有索引呢,该怎么来做呢?

    分析下:

           ROWNUM是伪列,每次获取结果后,然后在结果集里会产生一列,从1开始排,每次都是从1开始排。

            ROWID在每个表中,每条记录的ROWID都是唯一的。在这种情况下,我们可以用ROWID。但要注意的是,ROWID是一个类型,注意它和VARCHAR2之间的转换。有两个方法:ROWIDTOCHAR()是把ROWID类型转换为CHAR类型;CHARTOROWID()是把CAHR类型转换为ROWID类型。

    接下来我们编写存储过程process_student2,脚本如下:

    CREATE OR REPLACE PROCEDURE process_student1 AS
        CURSOR CUR_STUDENT IS SELECT ROWIDTOCHAR(ROWID) FROM student;
        TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
        students REC_STUDENT;
    BEGIN
      OPEN CUR_STUDENT;
      WHILE (TRUE) LOOP
        FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
        FORALL i IN 1..students.count SAVE EXCEPTIONS
          UPDATE student SET s_grade=s_grade+1 WHERE ROWID=CHARTOROWID(students(i));
        COMMIT;
        EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
      END LOO;
      dbms_output.put_line('finished');
    END;

    说明:

           我们首先查到记录的ROWID并把它转换为CHAR类型,存放到游标CUR_STUDENT里,

    再每次抓取10万条数据赋值给数组进行更新,更新语句的WHERE条件时,又把数组元素是CAHR类型的rowid串转换为ROWID类型。

    附.参考资料

    存储过程基础:

           https://www.yiibai.com/plsql/plsql_basic_syntax.html

    存储过程进阶之FORALL:

           https://blog.csdn.net/leshami/article/details/7536926

           https://blog.csdn.net/jie1336950707/article/details/49966753

    存储过程进阶之BUIL COLLECT:

           https://blog.csdn.net/leeboy_wang/article/details/7991021

           https://blog.csdn.net/leshami/article/details/7545597

    联合数组:

           https://blog.csdn.net/leshami/article/details/7372061

    展开全文
  • Oracle 行转列 pivot函数基本用法

    万次阅读 多人点赞 2018-09-30 23:28:05
    2018年9月30日22点,眼看着就10月份了,回头看下,8月份就写了一...所以暂时先写个Oracle自带的行转列函数,pivot的基本用法。国庆几天看下有时间的话完善一下动态转列的做法,到时候再另写一篇附链接过来。 一、运...

    2018年9月30日22点,眼看着就10月份了,回头看下,8月份就写了一篇博客,9月一篇都没写,想着还是得续一续。

    刚好前几天,帮一个群友处理了一个关于Oracle中行转列,根据查询中有的项目,动态转列的做法。想着也挺好玩,不过看下时间,不太充足。所以暂时先写个Oracle自带的行转列函数,pivot的基本用法。国庆几天看下有时间的话完善一下动态转列的做法,到时候再另写一篇附链接过来。

    一、运行环境

    还是先介绍下环境,虽然应该也没啥影响,Win10,Oracle Database 11g r2,plsql 12。

    二、看下结果

    三、测试数据准备

    --建表
    --drop table SalesList;
    create table SalesList(
        keHu                varchar2(20),   --客户
        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 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.shangPin, floor(dbms_random.value(10,50)) from dual;
         end loop;
      end loop;
      commit;
    end;
    /

    四、pivot进行转换的SQL(查询结果就是上面的结果图)

    --行转列
    select *
      from SalesList pivot(
        max(salesNum) for shangPin in (    --shangPin 即要转成列的字段
            '上衣' as 上衣,                 --max(salesNum) 此处必须为聚合函数,
            '裤子' as 裤子,                 --in () 对要转成列的每一个值指定一个列名
            '袜子' as 袜子,
            '帽子' as 帽子
        )
      )
     where 1 = 1;                          --这里可以写查询条件,没有可以直接不要where

    五、转动态列

    有时候可能需要行转列的值,即shangPin字段的值的个数很多,或者是不确定个数,那 in () 里面的部分就不好去写死,然后,Oracle的pivot其实也是提供了一个转出动态列的功能,不过转出来的是xml格式的数据。。。这也是为啥我说要自己写一个处理的方法的原因了、、、不过具体做法下次再说,现在先看下原汁原味的转xml的做法,sql如下:

    --动态出列(xml的形式)
    select *
      from SalesList pivot xml(                        --pivot xml 以xml的形式输出
        max(salesNum) for shangPin in (
           select distinct shangPin from SalesList     --通过查询查出所有需要转列的值,即所有列名
        )
      );

    还是有点感人的。不过这样的结果,实在是,,用途不大。

    所以呢,要么,咱们把这个xml的结果,转换成你要的结果,又要么呢,咱们写个存储过程什么的,通过一些参数,配置,把数据处理成咱们需要的效果。

    我个人是比较倾向与存储过程处理,可以通过动态sql拼接,或者是循环的方式处理,具体实现,下期再聊。

     

    ===================================一条低调的分割线================================

    2018-10-02,动态转换的出来了,有兴趣的可以看下

    Oracle 行转列 动态出转换的列

    展开全文
  • Oracle 行转列----存储过程

    千次阅读 2012-07-26 11:28:28
    欢迎技术交流。 QQ:138986722 原文地址--... --行转过程 create or replace procedure row_to_col(tabname in varchar2, --需要进行

    欢迎技术交流。 QQ:138986722

    原文地址--http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html?39758

    --行转列过程
    create or replace procedure row_to_col(tabname         in varchar2,  --需要进行行转列操作的表名
                                           group_col       in varchar2,  --查询结果要按某列或某些列分组的字段名
                                           column_col      in varchar2,  --要从行转成列的字段
                                           value_col       in varchar2,  --需要聚合的值字段
                                           Aggregate_func  in varchar2 default 'max',  --选用的聚合函数,可选,默认为max
                                           colorder        in varchar2 default null,   --行转列后列的排序,可选
                                           roworder        in varchar2 default null,   --行转列后记录的排序,可选
                                           when_value_null in varchar2 default '0',   --若value_col字段的值聚合后为空,则转换成该值,可选
                                           viewname        in varchar2 default 'v_tmp' --创建的视图名称,可选,默认为v_tmp
                                            ) 
      Authid Current_User as
      sqlstr varchar2(2000) := 'create or replace view ' || viewname ||
                               ' as select ' || group_col || ' ';
      c1     sys_refcursor;
      v1     varchar2(100);
    begin
      open c1 for 'select distinct ' || column_col || ' from ' || tabname || case when colorder is not null then ' order by ' || colorder end;
      loop
        fetch c1
          into v1;
        exit when c1%notfound;
        sqlstr := sqlstr || chr(10) || ',' || case
                    when when_value_null is not null then
                     'nvl('
                  end || Aggregate_func || '(decode(to_char(' || column_col ||
                  '),''' || v1 || ''',' || value_col || '))' || case
                    when when_value_null is not null then
                     chr(44) || when_value_null || chr(41)
                  end || '"' || v1 || '"';
      end loop;
      close c1;
      sqlstr := sqlstr || ' from ' || tabname || ' group by ' || group_col || case
                  when roworder is not null then
                   ' order by ' || roworder
                end;
      execute immediate sqlstr;
    end row_to_col;
    /



    展开全文
  • 偶遇Oracle行转

    千次阅读 2016-05-06 16:58:41
    使用decode实现报表的行转
  • oracle 行转列的通用过程

    千次阅读 2009-11-18 21:56:00
    原文传送门:http://topic.csdn.net/u/20091019/11/67cd55a3-3f42-4db7-a3f8-91dd52a913cd.html?24122经常遇到发帖求行列...就要用存储过程来写,这些存储过程的代码都大同小异,我就想能不能写个通用点的过程 试了一下
  • Oracle 行转列 动态出转换的列

    万次阅读 多人点赞 2018-10-02 13:03:23
    10月的第二天,前天写了个Oracle中行列的pivot的基本使用方法,然后,因为pivot的用法中,正常情况下,我们需要出多少个列,都得在我们的sql中完完整整地写出,而不能直接在里面写个查询来动态转换。然后,趁着...
  • Oracle 行转列小结

    万次阅读 热门讨论 2015-07-26 12:45:51
    最近在工作中,对行转列进行了应用,在此做一个简单的小结。  转换过程如下:   1、创建表结构 CREATE TABLE RowToCol ( ID NUMBER(10) not null, USER_NAME VARCHAR2(20 CHAR), COURSE VARCHAR2...
  • oracle行转

    2015-05-27 17:09:08
    行转列 场景:查询结果作为字段头出现方式: 存储过程中,写function拼接 缺点:需有存储过程的调用 使用decode函数进行硬转 缺点:必须确切的知道该列所有的取值 decode函数行转列...
  • oracle行转列函数pivot

    千次阅读 2019-04-16 19:25:43
    什么情形下需要使用行转列 对于同一类的事物下具有多个属性(属性是有限的),比如说学生选课,每一名可以选择多个课程,而且课程的数量也是有限重复的(对于不通学生选同一门科就相当于是课程重复)。比如说下面这样的...
  • Oracle行转列的一次经历

    千次阅读 2018-07-17 14:16:47
    需求如下,由于系统项目改造,之前党员各个月份的成绩如下表所示: ...由于需要数据迁移,我把之前的表放到新版数据库中,通过编写一共存储过程实现考核数据转换。(这次经历主要是目前统计考核规则有...
  • ---------------------------------------------------------------行转列的存储过程 CREATE OR REPLACE PROCEDURE P_STU ---存储过程名称 P_STU 存储过程没有返回结果的 IS ---声明变量 V_SQL VARCHAR2(2000); --...
  • Oracle行转列+排序

    千次阅读 2016-07-12 10:05:05
    根据站点给各个线路赋值途径站点(行转列+排序) begin for t2 in ( select A.line_no,A.line_direct, max ( KEY ) Station_DIRECT from ( select t.line_no,t.line_direct, WMSYS.WM_CONCAT(t....
  • Oracle 10g: wm_concat  Oracle 11g新增了函数:LISTAGG 简单例子: SELECT a.id, wm_concat (a.remark) new_result FROM tb_name a group by ...在使用过程中发现有些oracle会存...
  • Oracle 存储过程实现行转列-pivot

    千次阅读 2017-12-06 14:13:11
    行转列 pivot unpivot 存储过程行转列 pivot in 子查询
  • oracle行转列(动态行转不定列)

    千次阅读 2018-07-26 09:16:00
    ---------------------------------------------------------------行转列的存储过程 CREATE OR REPLACE PROCEDURE P_TEST IS V_SQL VARCHAR2(2000); CURSOR CURSOR_1 IS SELECT DISTINCT T.XCLCK FROM TEST T ...
  • oracle动态行转

    2013-09-13 08:47:34
    用存储过程写的动态行转列。简单易用,查询速度高效
  • 这里通过一个例子展示将ORACLE存储转化为MYSQL存储过程的一些必须修改的地方。 ORACLE存储过程 修改后的MYSQL存储过程 两者比较: 语法格式上的不同与修改: 1、CREATE OR REPLACE 改为 CREATE 和 2、入参:入参中...
  • Oracle实现动态行转

    千次阅读 2019-08-02 13:39:23
    oracle中要实现行转列的方式有很多种,比如case when …else …end 、wm_concat()函数,lag() over() 、lead() over() 函数等,以及11g版本后的pivot函数都可实现。可根据具体的需求选取不同的方式。 前两天恰好一...
  • NULL 博文链接:https://vernonchen163.iteye.com/blog/1902976
  • Oracle行转列 简单示例

    千次阅读 2012-04-19 17:28:36
    在开发的过程中,有时候需要将数据转换为列数据,这种情况可用Decode函数和分组来实现 --构造数据集 with A as (select '阿诗玛' Name, '语文' Class, '86' Score from dual union all select '阿诗玛',...
  • oracle存储过程 专列 java调用 存储过程 结果集 (按照文档即可使用)
  • Oracle函数篇 - pivot行转列函数

    千次阅读 2019-12-19 16:30:24
    SELECT 语句pivot_clause允许您编写交叉表位查询,将旋转到列中,在旋转过程中聚合数据。透视是数据仓库中的一项关键技术。在其中,您将多行输入转换为数据仓库中更少且通常更宽的。透视时,将针对数据透视列值...
  • 当初设计表的时候,指标、数据及公司在一张表里,现在要求列是动态维护的,也就是说需要多表关联,实现动态行转列,想了半天最后选择用Oracle存储过程加游标来做,下面把这个存储过程分享给大家,有不足的地方大家...
  • Oracle存储过程

    万次阅读 多人点赞 2019-07-01 14:52:17
    Oracle存储过程详解 procedure 1.创建Oracle存储过程 prodedure create or replace procedure test(var_name_1 in type,var_name_2 out ty pe) as --声明变量(变量名 变量类型) begin --存储过程的执行体 end ...
  • 最近在接触数据库的有关知识,因为水平有限,对数据库方面的一些知识缺乏了解,这次遇见的主要是 数据库的存储过程,根据公司项目需求,将oracle的存储过程切换为mysql的存储过程,首先oracle的存储过程与mysql的...
  • 效果图: 左边是前数据,行数不确定。...--1-----------------------创建存储过程------------------------- CREATE OR REPLACE PROCEDURE P_TEST IS --定义变量 拼接语句 V_SQL VARCHAR2(2000); --查询...
  • ORACLE 实现行转列(字符串求和)

    千次阅读 2016-03-09 09:16:48
    使用关系型数据的实际项目中,难以避免变更增加字段,有时为了方便,不想加字段,把多个值存储一个字段中,用逗号或其他分隔符进行分隔;存储解决了,但是展示有时就比较麻烦了,...oracle提供非常简单的方式来解决。

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 94,810
精华内容 37,924
关键字:

oracle行转过程