精华内容
下载资源
问答
  • PLSQL程序性能分析及优化 经验总结 实施过程中,经常会使用 PL/SQL Developer 工具进行数据转换和处理业务数据。通过性能 优化来提高程序执行效率是必须掌握的一份技能。性能问题中绝部分都是由于程序编写的 不合理...
  • plsql导出大字段数据方法

    万次阅读 2015-01-23 15:06:16
    plsql导入导出大字段数据方法

    基于

    PL/SQL

    的数据库备份方法

     

    PL/SQL Developer

    Oracle

    数据库中用于导入或导出数据库的主要工具,本文主要介绍

    了利用

    PL/SQL Developer

    导入和导出数据库的过程,

    并对导入或导出时的一些注意事项进行

    了说明,接下来我们就一一介绍。

     

    导出步骤:

     

    1

    tools ->export user object 

    选择选项,导出

    .sql

    文件。

     

    2

    tools ->export tables-> Oracle Export 

    选择选项导出

    .dmp

    文件。

     

    导入步骤:

     

    注:导入之前最好把以前的表删除,当然导入另外数据库除外。

     

    1

    tools->import tables->SQL Inserts 

    导入

    .sql

    文件。

     

    2

    tools->import talbes->Oracle Import

    然后再导入

    dmp

    文件。

     

    一些说明:

     

    Tools->Export User Objects

    导出的是建表语句(包括存储结构)

    PS

    :这种方式只能导出

    属于这个用户的表,其他用户的表不能导出,建议用命令行

    (exp,imp)

    导出。

     

    Tools->Export Tables

    里面包含三种导出方式,三种方式都能导出表结构以及数据,如下:

     

     

    Oracle Export 

     

    Sql Insert 

     

    pl/sql developer 

    第一种是导出为

    .dmp

    的文件格式,

    .dmp

    文件是二进制的,

    可以跨平台,

    还能包含权限,

    效率也很不错,用得最广

     

     

    第二种是导出为

    .sql

    文件的,

    可用文本编辑器查看,

    通用性比较好,

    但效率不如第一种,

    适合小数据量导入导出。尤其注意的是表中不能有大字段

     

    blob,clob,long

    ,如果有,会提

    示不能导出

    (

    提示如下:

     

    table 

    contains 

    one 

    or 

    more 

    LONG 

    columns 

    cannot 

    export 

    in 

    sql 

    format,user Pl/sql developer format instead)

     

    第三种是导出为

    .pde

    格式的,

    .pde

    Pl/sql 

    developer

    自有的文件格式,只能用

    Pl/sql 

    developer

    自己导入导出,不能用编辑器查看。

     

    PS

    :只有

    "Oracle 

    Export"

    方式导出再导入后,表结构和索引结构没有发生变化,另两种方式

    都改变了索引类型。

     

    ---------------------------------------------------------------------------------------------------------------------------------

    ----------------------------- 

    1

     

     

    导入数据的苦恼

     

    最近,一直再为搭建测试数据库的事情烦恼,主要碰到如下困难:

     

     

    1

    、使用

    oracle 

    dump

    方式导出的数据在导入的时候不能随意选择一张表的数据进行导

    入;

    一般业务的表的数量都有

    1000

    左右,

    在出现由于某张表数据异常导致的故障时,

    dump

    文件进行恢复基本不可行或者很费事;

     

     

    2

    、表存在外键约束和触发器,使用

    oracle 

    dump 

    import

    方式导入表的时候不能非常简

    单的禁止触发器和外键,导致大量错误产生,导入的表的数据不全,部分表数据没有导入;

     

     

    3

    当修改了某些表的数据后需要将这些表的数据恢复到测试环境搭建时的数据,

    import

    不能将这些表的数据清空后重新导入。

     

     

    4

    当修改了某些表的字段后需要将这些表的数据恢复到测试环境搭建时的数据,

    import

    不能将这些表重建后重新导入。

     

     

    那么有没有一个工具能够解决上面我们这位工程师的苦恼呢?

     

     

     

    2

     

     

    PL/SQL Developer

    导入导出工具

     

    PL/SQL Developer

    位于

    PLSQL DEV

    工具的

    Tools

    菜单下的

    Export tables

    „”

    Import 

    tables

    „”下,可以进行数据的导出和导入。

     

    2.1 

    导出功能介绍

     

     

     

    Where clause: 

    使导出操作支持

    where

    条件,

    比如你只需要导出每张表的

    10000

    条记录,

    可以在输入框里输入“

    rownum < 10001

     

    Compress 

    file: 

    选中后支持导出文件的压缩,节省存储空间,但是压缩会占用多余的时

    间。

     

    Include storage: 

    导出的文件中包含建表信息,如果需要在导入的时候能支持建表操作,

    需要选中该项。

     

    2.2 

    导入功能介绍

     

     

     

    Drop tables

    支持在导入数据前先删除表,

    选择该项后,

    默认选择

    Create tables

    选项,

    并且“

    Truncate tables

    ”和“

    Delete table

    ”操作变成灰色不可用状态。

     

    Create 

    tables

    :支持在导入数据前先创建表,比如我们已将建好了用户,这个时候需要

    导入表而之前又没有执行建表脚本的时候,可以选中此项。

     

     

    Truncate 

    tables

    :支持在导入数据前先清空表数据,想恢复表数据到建测试环境的时候

    可以使用这个选项,该选项和“

    Delete tables

    ”互斥。

     

     

    Delete 

    tables

    :支持在导入数据前删除表数据,这个选项目前还没有发现比较特殊的使

    用意义,该选项和“

    Truncate tables

    ”互斥。该选项的导入速度大大慢于“

    Truncate tables

    一般使用“

    Truncate tables

     

    2.3 

    如何解决导出

    clob

    blob

    类型数据报错的问题

     

    当表字段中含有

    clob

    blob

    类型数据时,使用

    PL/SQL Developer

    导出会报

    stream read 

    error

    的错误,

    导出操作终止,

    说明

    PL/SQL Developer

    方式导出不支持这种类型,

    oracle export

    方式可以支持这种类型。

     

    由于需要导出的表很多,

    PL/SQL

    在导出前都要对需要导出的表进

    行分析,通常都会花费十几分钟的时间,然而由于某张表存在

    clob

    blob

    类型数据时就会

    异常终止,

    那之前的操作时间就会浪费,

    为了导出所有表需要将导出的表进行标记,

    只导出

    没有

    clob

    blob

    类型数据的表。我们通常的做法是在导出表的时候用

    ctrl

    或者

    shift

    按键进

    行手工选择,

    通过导出的日志记录不能导出的表,

    然后手工将这些表反选出待导出的表。

    是这样操作费时费力,需要通过不停反复的操作,才能知道哪些表不能导出。

     

    下面介绍一

    下如何使用

    oracle

    系统视图

    all_tab_columns

    PL/SQL

    在导出表的时候提供的

    Object selection

    功能快速导出不包含

    clob

    blob

    的表数据。

     

    1

    、使用下面的

    sql

    语句拼出

    Object selection

    的文件内容。

     

     

    --

    不包含

    clob

    blob

    的表

     

    select distinct('TABLE "'||a.OWNER ||'"."'||a.TABLE_NAME||'"') 

    from 

    sys.all_tab_columns 

    where 

     

    a.OWNER 

    'ICDPUB' 

    and 

    a.TABLE_NAME 

    not 

    in 

    (select 

    t.TABLE_NAME 

    from 

    sys.all_tab_columns 

    where 

    t.OWNER 

    'ICDPUB' 

    and 

    t.DATA_TYPE 

    in 

    ('CLOB','BLOB')) 

     

     

    注意:上面的

    sql

    语句里的

    ICDPUB

    是用户名

     

     

    2

    、按照上面的语句的执行结果生成

    Object selection

    的文件(后缀是

    .osf

    ,文件的内容

    如下:

     

     

     

     

     

    PL/SQL Developer Object Selection File 1 

     

    TABLE "ICDPUB"."ACTIVE_ALARMS" 

     

    TABLE "ICDPUB"."ALLAPPOINT" 

     

    TABLE "ICDPUB"."ALLOPTIONVIEW" 

     

    TABLE "ICDPUB"."ALLOTCONFIG" 

     

    TABLE "ICDPUB"."ALLPAPERAUTHVIEW" 

     

    TABLE "ICDPUB"."ALLPAPERVIEW" 

     

    TABLE "ICDPUB"."ALLQUESTIONVIEW" 

    3

    、在导出表功能的表选择框里单击右键选择“

    Load Object selection

    ,选择上一步制作

    的文件

    ,

    完成表的选择。

     

    4

    、选择合适的参数,进行导出操作。

     

    5

    、用

    oracle export

    方式导出包含

    clob

    blob

    的数据。

     

    展开全文
  • PL/SQL复合数据类型包括:PL/SQL记录、PL/SQL表(索引表)、嵌套表、可变长数组。 复合数据类型的说明在本博客的《PL/SQL数据类型》中有详述,这里对复合数据类型所使用到的方法做专题分析
    Oracle PL/SQL集合数据类型专题:
    


    一、什么是PL/SQL集合数据类型
    PL/SQL复合数据类型包括:PL/SQL记录、PL/SQL集合(分为PL/SQL表(别名索引表)、嵌套表、VARRAY)。
    其中PL/SQL集合数据类型是指符合数据类型中的PL/SQL集合。


    复合数据类型的说明在本博客的《PLSQL数据类型》中有详述,这里只对PL/SQL集合数据类型的一系列特有操作与应用做讲述。




    二、PL/SQL集合的多级集合调用应用
    说明:多级集合是指嵌套了集合类型的集合类型,从而实现多维数组功能,9i后支持。


    1)多级PL/SQL表(索引表)
    说明:如果多维数组元素个数没有限制,可以使用多级PL/SQL表和嵌套表。
    Declare
    Type al_table_type is table of int index by binary_integer; --定义一维table
    Type nal_table_type is table of al_table_type index by binary_integer;--定义二维table集合
    Nv1 nal_table_type;
    Begin
    Nv1(1)(1):=10; 
    Nv1(1)(2):=5;                                                                                                
    Nv1(2)(1):=100; 
    Nv1(2)(2):=50;
    Dbms_output.put_line(‘显示二维数组所有元素’);
    For I in 1..nv1.count loop
      For j in 1..nu1(i).count loop
        Dbms_output.put_line(i||’,’||j||’shi’||nv1(i)(j));
      End loop;
    End loop;
    End;


    2)多级嵌套表
    Declare
    Type al_table_type is table of int;--定义一维嵌套表
    Type nal_table_type is table of al_table_type;--定义二维嵌套表集合
    Nv1 nal_table_typer :=nal_table_typer (al_table_type(2,4),al_table_type(5,73));
    Begin
    Dbms_output.put_line(‘显示二维数组所有元素’);
    For I in 1..nv1.count loop
      For j in 1..nv1(i).count loop
        Dbms_output.put_line(i||’,’||j||’shi’||nv1(i)(j));
      End loop;
    End loop;
    End;


    3)多级VARRAY
    Declare
    Type al_varray_type is varray(10) of int;--定义一维varray
    Type nal_varray_type is varray(10) of al_varray_type;--定义二维varray集合
    nvl nal_varray_type:= nal_varray_type(
    al_varray_type(58,100,102);
    al_varray_type(55,6,73);
    al_varray_type(2,4);
    begin
    dbms_output.put_line(‘显示二维数组所有元素’);
    for i in 1..nv1.count loop
      for j in 1..nv1.count loop
        Dbms_output.put_line(i||’,’||j||’shi’||nv1(i)(j));
      End loop;
    End loop;
    End;




    三、集合数据类型的方法
    说明:复合数据类型中PL/SQL集合类型所特有的集合方法,集合方法是Oracle所提供用于操纵集合变量的内置函数或过程。
    方法包含EXISTS、COUNT、LIMIT、FIRST、NEXT、PRIOR和NEXT是函数,EXTEND、TRIM和DELETE是过程。


    1、exists方法
    说明:确定特定的PL/SQL表元素是否存在,如果集合元素存在,则返回true;如果不存在,则返回false
    例子:
    declare
    type ename_table_type is table of emp.ename%type;
    ename_table ename_table_type;
    begin
    if ename_table.exsts(1) then
    ename_table(1):='scott';
    else
    dbms_output.put_line('必须初始化集合元素');


    2、count方法
    说明:返回当前集合变量中的元素总个数。
    declare
    type aneme_table_type is table of emp.ename%type index by binary_integer;
    ename_table ename_table_type;
    begin
    ename_table(-5):='aa';
    ename_table(1):='b';
    ename_table(10):='bk';
    dbms_output.put_line(ename_table.count);
    end;


    3、limit方法
    说明:返回集合元素的最大元素个数。因为嵌套表和索引表的元素个数没有限制,所以调用方法会返回NULL;而对于VARRAY会返回最大元素个数。
    declare
    type aneme_table_type is varray(20) of emp.ename%type;
    ename_table ename_table_type:=ename_table_type('mary');
    begin
    dbms_output.put_line(ename_table_type.limit);
    end;


    4、first和last方法
    说明:返回集合第一个、最后一个元素的下标。
    declare
    type aneme_table_type is table of emp.ename%type index by binary_integer;
    ename_table ename_table_type;
    begin
    ename_table(-5):='aa';
    ename_table(1):='b';
    ename_table(10):='bk';
    dbms_output.put_line(ename_table.first);
    dbms_output.put_line(ename_table.last);
    end;


    5、prior和next方法
    说明:返回当前集合元素的前一个元素和下一个元素


    举例:
    declare
    type ename_table_type is table of emp.ename%type index by benary_integer; --PL/SQL表
    ett ename_table_type;
    begin
    et(-5):='scott';
    et(1):='smith';
    st(5):='mary';
    dbms_output.put_line('元素1的前一个元素值:' || et(et.prior(1))); --结果是scott
    dbms_output.put_line('元素1的后一个元素值:' || et(et.next(1))); --结果是mary
    end;


    6、extend方法
    说明:为集合变量增加元素,只适用于嵌套表和VARRAY。
    格式:extend 添加一个null元素
    extend(n) 添加n个null元素
    extend(n,i) 添加n个值与第i个元素相同的元素


    举例:
    declare
    type id_table_type is table of number(6); --嵌套表
    idt id_table_type:=id_table_type(1,2,5); --声明并初始化3个元素
    begin
    if idt.exists(4) then --判读第四个元素是否存在
    dbms_output.put_line('元素值:' || idt(no));
    else
    dbms_output.put_line('元素未初始化'); --结果
    end if;
    idt.extend(999); --添加999个空元素
    dbms_output.put_line('第一个元素是:' || idt(idt.first)); --结果为1
    dbms_output.put_line('最后一个元素是:' || nvl(to_char(idt(idt.first)),'null')); --结果为null
    dbms_output.put_line('元素总数为:' || idt.count); --结果为1002
    end;


    7、trim方法
    说明:从集合尾部删除元素,只适用于嵌套表和VARRAY。
    格式:trim 删除一个元素
    trim(n) 删除n个


    举例:
    declare
    type id_array_type is varray(30) of number(6); --可变长数组
    iat id_array_type:=id_array_type('A','B','C','D','E','F','G','H','I','J','K','L');
    begin
    dbms_output.put_line('元素总数为:' || iat.count); --结果为12
    dbms_output.put_line('最大元素个数:' || iat.limit); --结果是30
    iat.trim(5);
    dbms_output.put_line('最后一个元素:' || iat(iat.last)); --结果是G
    end;


    8、delete方法
    说明:删除集合元素,只适用于嵌套表和索引表。
    格式:delete 删除全部元素
    delete(n) 删除第N个元素
    delete(m,n) 删除除集合变量中m~n之间的所有元素


    举例:
    declare
    type id_table_type is table of number(6) index by binary_integer; --PL/SQL表
    itt id_table_type;
    begin
    for i in 1..10000 loop
    itt(i):=i;
    end loop;
    for j in 1..itt.count loop
    if mod(j,2)=0 then
    id.delete(j); --余数为0则删除该元素
    end if;
    end loop;
    end;




    四、集合数据类型的赋值。
    当使用嵌套表和VARRAY时,通过执行insert、update、fetch、select,赋值语句,用可以将一个集合的数据赋值给另外一个集合。
    从Oracle10g开始,给嵌套表赋值时,还可以用set、multiset union、multiset intersect、multiset except等集合操作符。
    其中set用于取消嵌套表中的重复值,multiset union用于取得2个嵌套表的并集(带有distinct可以取消重复结果)
    multiset intersect取得2个嵌套表的交集,multiset except取得2个嵌套表的差集。


    1、将一个集合的数据赋值给另外一个集合
    说明:当使用赋值语句(:=)或sql语句将源集合中的数据赋值给目标集合时,会自动清除模板集合原有数据再赋值。
    注意:当进行集合赋值时,源集合和目标集合的数据类型必须一致。
    declare
    type name_varray_type is varray(4) of varchar2(10);
    name_array1 name_varray_type;
    name_array2 name_varray_type;
    begin
    name_array1:=name_varray_type('scott','smith');
    name_array2:=name_varray_type('a','a','a','a','a');
    dbms_output.put('2的原数据:');
    for i in 1..name_array2.count loop
    dbms_output.put(name_array2(i));
    end loop;
    dbms_output.new_line;
    name_array2:=name_array1;
    dbms_output.put('2的新数据:');
    for i in 1..name_array2.count loop
    dbms_output.put(name_array2(i));
    end loop;
    dbms_output.new_line;
    end;


    2、给集合符NULL值
    说明:在清空集合变量的所有数据时,既可以使用集合delete和trim,也可以讲一个null集合变量赋给目标集合变量。
    declare
    type name_varray_type is varray(4) of varchar2(10);
    name_array1 name_varray_type;
    name_array2 name_varray_type;
    begin
    name_array1:=name_varray_type('scott','smith');
    dbms_output.put('2的原数据个数'||name_array1.count);
    name_array1:=name_array2;
    --dbms_output.put('2的新数据个数'||name_array1.count);
    end;


    3、使用集合操作符给嵌套表赋值
    说明:10g开始,允许将多个嵌套表的结果组合到某个嵌套表中,通过使用ANSI集合操作符(set,multiset union,
    multiset intersect,multiset except)实现。


    1)、set操作符
    说明:用于去掉嵌套表中的重复值。
    declare
    type nt_table_type is table of number;
    nt_table nt_table_type:=nt_table_type(2,4,3,1,2);
    result nt_table_type;
    begin
    result:=set(nt_table);
    dbms_output.put('result:');
    for i in 1..result.count loop
    dbms_output.put(' '||result(1));
    end loop;
    dbms_output.new_line;
    end;


    2)、multiset union和multiset union distinct操作符
    说明:multiset union取得2个嵌套表的并集,保留重复项;multiset union distinct去掉重复。
    declare 
    type nt_table_type is table of numberr;
    nt1 nt_table_type:=nt_table_type(1,2,3);
    nt2 nt_table_type:=nt_table_type(3,4,5);
    result nt_table_type;
    begin
    result:=nt1 multiset union nt2;
    dbms_output.put('resilt:');
    for i in 1..result.count loop
    dbms_output.put(' '||result(i));
    end loop;
    dbms_output.new_line;
    end;


    3)、multiset intersect操作符
    说明:取得2个嵌套表的交集。
    declare 
    type nt_table_type is table of numberr;
    nt1 nt_table_type:=nt_table_type(1,2,3);
    nt2 nt_table_type:=nt_table_type(3,4,5);
    result nt_table_type;
    begin
    result:=nt1 multiset intersect nt2;
    dbms_output.put('resilt:');
    for i in 1..result.count loop
    dbms_output.put(' '||result(i));
    end loop;
    dbms_output.new_line;
    end;


    4)、multiset except操作符
    说明:取两个嵌套表变量的差集。
    declare 
    type nt_table_type is table of numberr;
    nt1 nt_table_type:=nt_table_type(1,2,3);
    nt2 nt_table_type:=nt_table_type(3,4,5);
    result nt_table_type;
    begin
    result:=nt1 multiset except nt2;
    dbms_output.put('resilt:');
    for i in 1..result.count loop
    dbms_output.put(' '||result(i));
    end loop;
    dbms_output.new_line;
    end;




    五、集合数据类型的比较
    说明:用于比较两个集合变量是否相同。
    10g后新增集合比较操作符包括:
    CARDINALITY(返回嵌套表变量的元素个数),
    SUBMULTISET OF(确定一个嵌套表是否为领一个嵌套表的子集),
    MEMBER OF(检测特定数据是否为嵌套表元素),
    IS A SET(检测嵌套表是否包含重复的元素),
    IS EMPTY(检测嵌套表是否为null)等。


    1、检测集合是否为null
    说明:is null使用于嵌套表或VARRAY变量;is empty只使用于嵌套表。
    declare
    type name_array_type is varray(3) of varchar2(10);
    name_array nane_array_type;
    begin
    --if name_array is empty then
    if name_array is null then
    dbms_output.put_line('未初始化');
    end if;
    end;


    2、比较嵌套表是否相同
    说明:使用=和!=比较嵌套表是否相同,只能用于嵌套表。
    declare
    type name_table_type is table of varchar2(10);
    name_table1 name_table_type;
    name_table2 name_table_type;
    begin
    name_table1:=name_table_type('scott');
    name_table2:=name_table_tyoe('smith');
    if name_table1=name_table2 then
    dbms_output.put_line('相同');
    else
    dbms_output.put_line('不同');
    end if;
    end;


    3、在嵌套表上使用ANSI集合操作符
    说明:这些操作符只适用于嵌套表。


    1)、CARDINALITY
    说明:也可以称为函数,返回嵌套表变量的元素个数。
    declare
    type nt_table_type is table of number;
    nt1 nt_table_tyoe:=nt_table_type(1,2,3,1);
    begin
    dbms_output.put_line('元素个数'||CARDINALITY(nt1));
    end;


    2)、SUBMULTISET OF
    说明:用于确定一个嵌套表是否为另一个嵌套表的子集。
    declare
    type nt_table_type is table of number;
    nt1 nt_table_type:=nt_table_type(1,2,3);
    nt2 nt_table_type:=nt_table_type(1,2,3);
    begin
    if nt1 submiltiset of nt2 then
    dbms_output.put_line('nt1是nt2的子集');
    end if;
    end;


    3)、MEMBER OF
    说明:检测特定数据是否为嵌套表的元素。
    declare
    type nt_table_type is table of number;
    nt1 nt_table_tyoe:=nt_table_tyoe(1,2,3,5);
    v1 number:=2;
    begin
    if v1 member of nt1 then
    dbms_output.put_line('v1是nt1的元素');
    end if;
    end;


    4)、IS A SET
    说明:用于检测嵌套表是否包含重复的元素值。
    declare
    type nt_table_type is table of number;\
    ot1 nt_table_type:=nt_table_type(1,2,3,5);
    begin
    if nt1 is a set then
    dbms_output.put_line('嵌套表nt1无重复值');
    end if;
    end;




    六、集合数据类型的批量绑定
    说明:9i新增,是指执行单次SQL操作能传递所有集合的数据,当在select、insert、update、delete
    语句上处理批量数据时,通过批量绑定,可以极大加快数据处理速度。
    批量绑定用bult collect子句和forall语句完成,其中bult collect子句用于取得批量数据,
    该子句只能用于select语句、fetch语句、dml返回子句中;而forall语句值适用于执行批量DML操作。


    1、使用与不使用批量绑定的区别


    1)、不适用批量绑定
    说明:9i之前,为了将多个集合元素插入到数据库表,必须要使用循环完成。
    例子:以索引表为例
    declare
    type id_table_type id table of number(6) index by binary_integer;
    type name_table_type is table of varchar2(10) index by binary_integer;
    id_table id_table_type;
    name_table name_table_type;
    start_time number(10);
    end_time number(10);
    begin
    for i in 1..5000 loop
    id_table(i):=i;
    name_table(i):='Name'||to_char(i);
    end loop;
    start_time:=dbms_utility.get_time;
    for i in 1..id_table.count loop
    insert into demo values(id_table(i),name_table(i));
    end loop;
    end_time:=dbms_utility.get_time;
    dbms_output.put_line('总时间'||to_char((end_time-start_time)/100));
    end;


    2)、使用批量绑定
    declare
    type id_table_type id table of number(6) index by binary_integer;
    type name_table_type is table of varchar2(10) index by binary_integer;
    id_table id_table_type;
    name_table name_table_type;
    start_time number(10);
    end_time number(10);
    begin
    for i in 1..5000 loop
    id_table(i):=i;
    name_table(i):='Name'||to_char(i);
    end loop;
    start_time:=dbms_utility.get_time;
    --批量绑定的使用开始
    forall i in 1..id_table.count
    insert into demo values(id_table(i),name_table(i));
    --批量绑定的使用结束
    end_time:=dbms_utility.get_time;
    dbms_output.put_line('总时间'||to_char((end_time-start_time)/100));
    end;


    2、FORALL语句
    说明:当要在PL/SQL中执行批量insert、update、delete操作,可以使用forall语句。
    9i前forall语句必须是连续的元素;10g后通过增加indices of子句和values of子句,可以使用不连续的集合元素。
    注意:for语句时循环语句,forall不是循环语句。如下面语法,9i只有语法一,10g后新增了语法二、三。


    语法一:forall i in lower_bound..upper_bound sql_statement;
    其中,i是隐含定义的整数变量(将作为集合元素下标被引用),lower_bound和upper_bound是集合元素上下界。


    语法二:forall i in indices of collection [between lower_bound and upper_bound] sql_statement;
    其中,indices of指定只取对应于collection集合元素下标的i值。


    语法三:forall i in values of index_collection sql_statement;
    其中,values of指定i值从集合变量index_collection中取得。


    1)、DML语句上使用批量绑定
    说明:使用批量绑定为数据库表插入数据时,首先需要给集合元素赋值,然后使用forall语句执行批量绑定插入操作。
    declare
    type id_table_type id table of number(6) index by binary_integer;
    type name_table_type is table of varchar2(10) index by binary_integer;
    id_table id_table_type;
    name_table name_table_type;
    begin
    for i in 1..50 loop
    id_table(i):=i;
    name_table(i):='Name'||to_char(i);
    end loop;
    forall i in 1..id_table.count
    insert into demo values(id_table(i),name_table(i));
    update demo set name=name_table(i) where id=id_table(i);
    delete from demo where id=id_table(i);
    commit;
    end;


    2)、FORALL语句中使用部分集合连续的元素
    说明:上面说到的都是集合的所有元素,forall可以使用部分元素。
    declare
    type id_table_type id table of number(6) index by binary_integer;
    id_table id_table_type;
    begin
    for i in 1..50 loop
    id_table(i):=i;
    end loop;
    forall i in 8..10
    insert into demo values(id_table(i),name_table(i));
    commit;
    end;


    3)、forall语句上使用indices of子句
    说明:indices of子句是10g新增,用于跳过集合中的null元素。
    declare
    type id_table_tyoe is table of number(6);
    id_table id_table_type;
    begin
    id_table:=id_table_type(1,null,3,null,5);
    forall i in indicrs of id_table
    delete from demo where id=id_table(i);
    end;


    4)、forall语句上使用values of子句
    说明:values of子句是10g新增,用于从其他集合中取得集合下标的值。
    declare
    type id_table_type is table of demo.id%type;
    type name_table_type is table of demo.name%type;
    id_table id_table_type;
    name_table name_table_type;
    type index_pointer_type is table of pls_integer;
    index_pointer index_pointer_type;
    begin
    select * bulk collect into id_table,name_table from demo;
    index_pointer:=index_pointer_type(6,8,10);
    forall i in values of index_pointer
    insert into new_demo_table2 values(id_table(i),name_table(i));
    end;
    --结果是向表中插入了6 8 10的数据


    5)、forall语句上使用SQL%BULK_ROWCOUNT属性
    说明:专门为forall语句提供的属性,用于取得执行批量绑定是第i个元素所作用的行数。
    declare
    type dno_table_type is table of number(3);
    dno_table dno_table_type:=dno_table_type(10,20);
    begin
    forall i in 1..dno_table.count
    update emp set sal=sal*1.1 where deptno=dno_table(i);
    dbms_output.put_line('第2个元素更新的行数:'||SQL%BULK_ROWCOUNT(2));
    end;


    3、BULK COLLECT子句
    说明:用于取得批量数据,将批量数据存放到PL/SQL集合变量中,只用于select into、fetch into和dml返回子句中。
    语法:。。。BULK COLLECT into collection_name[,collection_name]。。。
    其中,collection_name指定存放的集合变量名


    1)、在select into语句中使用bulk collect
    说明:9i前,select into必须且只能返回1行数据,否者抛异常,9i开始通过在elect into使用bulk collect子句可以
    一次将select语句的多行结果检索到集合变量。
    declare
    type emp_table_type is table of emp%rowtype index by binary_integer;
    emp_table emp_table_type;
    begin
    select * bulk collect into emp_table from emp where deptno=&no;
    for i in 1..emp_table.count loop
    dnms_output.put_line('emp_table(i).ename');
    end;


    2)、在DML的返回子句中使用bulk collect子句
    说明:为了取得DML操作所改变的数据,可以使用returning子句;为了取得DML所作用的多行数据,需要使用bulk collect子句。
    declare
    type ename_table_type is table of emp.ename%type;
    ename_table ename_table_type;
    begin
    delete from emp where deptnp=&no returning ename bulk collect into ename_table;
    dbms_output.put('雇员名:');
    for i in 1..ename_table.count loop
    dbms_output.put(ename_table(i));
    end loop;
    end;




    展开全文
  • PLSQL Developer 分析存储过程

    千次阅读 2018-08-27 06:36:15
    本人新手,在完成存储过程的编写后,感觉需要对存储过程中各个语句的执行时间进行分析,以便 对整个存储过程进行优化。  由于用的是PLSQL Developer 客户端工具,而网上大多介绍的是通过手工方法应用Profiler,...

     最近应公司需要,需要编写ORACLE存储过程。本人新手,在完成存储过程的编写后,感觉需要对存储过程中各个语句的执行时间进行分析,以便

    对整个存储过程进行优化。

      由于用的是PLSQL Developer 客户端工具,而网上大多介绍的是通过手工方法应用Profiler,使用相对比较烦杂,大致是通过命令行,以SYS用户

    首先创建dbms_profiler包,而且还要创建一个用于存放跟踪信息的用户,及其prof表和序列的同义词,最后用profiler用户创建prof表和序列,并赋权。

    这里对这种方法就不做介绍了。(如有需要,将会另写一篇关于此方法的介绍)

     

    下面将详细介绍在PLSQL DEVELOPER 应用Profiler:

    1、右键存储过程,单击测试

    2、单击测试后,会弹出一个测试窗口,如下图,此时点击"创建概览图报告"

    3、点击"执行" 或者  F8 执行存储过程

     

    4、执行完后 点击"测试窗口" 上的 概览图 会出现如下:

    每列的详细意义如下:
    unit        --单元名称,即执行的存储过程,包括其调用的过程
    line        --代码行号
    total time    --此行执行时间(颜色长度表示本行代码的执行时间与最长代码执行时间的百分比图)
    occurrences    --此行执行次数
    text        --对应代码行,对于加密的代码,将不能显示
    Average time    —平均运行时间
    maximum time    --最大运行时间
    minimum time    --最小运行时间(以上三个时间默认不显示,可以通过配置对话框选择显示,参加4.4)

    列表中显示的源代码只显示一行,如果要定位则可以在对应的行中打开右键,选择[Go to unit line] ,这样就会直接跳到对应的源代码位置。

    Profiler面板的工具栏说明:
    a、显示配置对话框
    b、刷新
    c、删除当前运行号的数据
    d、Run 显示当前的系统的所有Profiler列表,缺省为当前的跟踪
    e、Unit 显示本次跟踪的单元列表信息(执行时间),缺省为所有单元的执行时间

    5.4、Profiler配置对话框

    这样 就可以对存储过程做一个很好的分析,从而对其进行优化。

     

    展开全文
  • plsql 分析 执行计划

    千次阅读 2013-02-17 10:32:00
    基于oracle的应用系统很多性能问题,是由应用系统sql性能低劣引起的,所以,sql的性能优化很重要,分析与优化sql的性能我们一般通过查看该sql的执行计划,本文就如何看懂执行计划,以及如何通过分析执行计划对sql...
    基于oracle的应用系统很多性能问题,是由应用系统sql性能低劣引起的,所以,sql的性能优化很重要,分析与优化sql的性能我们一般通过查看该sql的执行计划,本文就如何看懂执行计划,以及如何通过分析执行计划对sql进行优化做相应说明。
    
    一、什么是执行计划(explain plan)
    
    执行计划:一条查询语句在oracle中的执行过程或访问路径的描述。
    
    二、如何查看执行计划
    
    1.set autotrace on
    
    2.explain plan for sql语句;
    
    select plan_table_output from table(dbms_xplan.display());
    
    3.通过第3方工具,如plsql developer(f5查看执行计划)、toad等;
    
    三、看懂执行计划
    
    1.执行计划中字段解释
    
    SQL> select * from scott.emp a,scott.emp b where a.empno=b.mgr;   已选择13行。       执行计划   ----------------------------------------------------------   Plan hash value: 992080948   ---------------------------------------------------------------------------------------   | Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |   ---------------------------------------------------------------------------------------   |   0 | SELECT STATEMENT             |        |    13 |   988 |     6  (17)| 00:00:01 |   |   1 |  MERGE JOIN                  |        |    13 |   988 |     6  (17)| 00:00:01 |   |   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   532 |     2   (0)| 00:00:01 |   |   3 |    INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |   |*  4 |   SORT JOIN                  |        |    13 |   494 |     4  (25)| 00:00:01 |   |*  5 |    TABLE ACCESS FULL         | EMP    |    13 |   494 |     3   (0)| 00:00:01 |   ---------------------------------------------------------------------------------------       Predicate Information (identified by operation id):   ---------------------------------------------------      4 - access("A"."EMPNO"="B"."MGR")          filter("A"."EMPNO"="B"."MGR")      5 - filter("B"."MGR" IS NOT NULL)       统计信息   ----------------------------------------------------------             0  recursive calls             0  db block gets            11  consistent gets             0  physical reads             0  redo size          2091  bytes sent via SQL*Net to client           416  bytes received via SQL*Net from client             2  SQL*Net roundtrips to/from client             1  sorts (memory)             0  sorts (disk)            13  rows processed   SQL> 对上面执行计划列字段的解释:
    
    Id: 执行序列,但不是执行的先后顺序。执行的先后根据Operation缩进来判断(采用最右最上最先执行的原则看层次关系,在同一级如果某个动作没有子ID就最先执行。 一般按缩进长度来判断,缩进最大的最先执行,如果有2行缩进一样,那么就先执行上面的。)
    
    如:上面执行计划的执行顺序为:3--》2--》5--》4--》1
    
    Operation: 当前操作的内容。
    
    Name:操作对象
    
    Rows:也就是10g版本以前的Cardinality(基数),Oracle估计当前操作的返回结果集行数。
    
    Bytes:表示执行该步骤后返回的字节数。
    
    Cost(CPU):表示执行到该步骤的一个执行成本,用于说明SQL执行的代价。
    
    Time:Oracle 估计当前操作的时间。
    
     
    
    2.谓词说明:
    
    Predicate Information (identified by operation id):
    
    ---------------------------------------------------
    
    4 - access("A"."EMPNO"="B"."MGR")
    
    filter("A"."EMPNO"="B"."MGR")
    
    5 - filter("B"."MGR" IS NOT NULL)
    
     
    
    Access: 表示这个谓词条件的值将会影响数据的访问路劲(全表扫描还是索引)。
    
    Filter:表示谓词条件的值不会影响数据的访问路劲,只起过滤的作用。
    
    在谓词中主要注意access,要考虑谓词的条件,使用的访问路径是否正确。
    
     
    
    四、 动态分析
    
    如果在执行计划中有如下提示:
    
    Note
    
    ------------
    
    -dynamic sampling used for the statement
    
     
    
    这提示用户CBO当前使用的技术,需要用户在分析计划时考虑到这些因素。 当出现这个提示,说明当前表使用了动态采样。 我们从而推断这个表可能没有做过分析。
    
    这里会出现两种情况:
    
    (1) 如果表没有做过分析,那么CBO可以通过动态采样的方式来获取分析数据,也可以或者正确的执行计划。
    
    (2) 如果表分析过,但是分析信息过旧,这时CBO就不会在使用动态采样,而是使用这些旧的分析数据,从而可能导致错误的执行计划。
    
     
    
    五、表访问方式
    
    1.Full Table Scan (FTS) 全表扫描
    
    2.Index Lookup 索引扫描
    
    There are 5 methods of index lookup:
    
    index unique scan --索引唯一扫描
    
    Method for looking up a single key value via a unique index. always returns a single value, You must supply AT LEAST the leading column of the index to access data via the index.
    
     
    
    index range scan --索引局部扫描
    
    Index range scan is a method for accessing a range values of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. Can be used for range operations (e.g. > < <> >= <= between) .
    
     
    
    index full scan --索引全局扫描
    
    Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.
    
     
    
    index fast full scan --索引快速全局扫描,不带order by情况下常发生
    
    Scans all the block in the index, Rows are not returned in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i/o, can be executed in parallel, can be used to access second column of concatenated indexes. This is because we are selecting all of the index.
    
     
    
    index skip scan --索引跳跃扫描,where条件列是非索引的前导列情况下常发生
    
    Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column(s) during the search.
    
     
    
    3.Rowid 物理ID扫描
    
    This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in. --Rowid扫描是最快的访问数据方式
    
     
    
    六、表连接方式
    
    请参照另一篇文章:Oracle 表连接方式详解
    
    http://www.fengfly.com/plus/view-210420-1.html
    
     
    
    七、运算符
    
    1.sort --排序,很消耗资源
    
    There are a number of different operations that promote sorts:
    
    (1)order by clauses (2)group by (3)sort merge join –-这三个会产生排序运算
    
     
    
    2.filter --过滤,如not in、min函数等容易产生
    
    Has a number of different meanings, used to indicate partition elimination, may also indicate an actual filter step where one row source is filtering, another, functions such as min may introduce filter steps into query plans.
    
     
    
    3.view --视图,大都由内联视图产生(可能深入到视图基表)
    
    When a view cannot be merged into the main query you will often see a projection view operation. This indicates that the 'view' will be selected from directly as opposed to being broken down into joins on the base tables. A number of constructs make a view non mergeable. Inline views are also non mergeable.
    
     
    
    4.partition view --分区视图
    
    Partition views are a legacy technology that were superceded by the partitioning option. This section of the article is provided as reference for such legacy systems.
    
     
    
    附:oracle优化器(Optimizer)
    
     
    
    Oracle 数据库中优化器(Optimizer)是SQL分析和执行的优化工具,它负责指定SQL的执行计划,也就是它负责保证SQL执行的效率最高,比如优化器决定Oracle 以什么样的方式来访问数据,是全表扫描(Full Table Scan),索引范围扫描(Index Range Scan)还是全索引快速扫描(INDEX Fast Full Scan:INDEX_FFS);对于表关联查询,它负责确定表之间以一种什么方式来关联,比如HASH_JOHN还是NESTED LOOPS 或者MERGE JOIN。 这些因素直接决定SQL的执行效率,所以优化器是SQL 执行的核心,它做出的执行计划好坏,直接决定着SQL的执行效率。
    
    Oracle 的优化器有两种:
    
    RBO(Rule-Based Optimization): 基于规则的优化器
    
    CBO(Cost-Based Optimization): 基于代价的优化器
    
    从Oracle 10g开始,RBO 已经被弃用,但是我们依然可以通过Hint 方式来使用它。 
    
     
    
    在Oracle 10g中,CBO 可选的运行模式有2种:
    
    (1) FIRST_ROWS(n)
    
    Oracle 在执行SQL时,优先考虑将结果集中的前n条记录以最快的速度反馈回来,而其他的结果并不需要同时返回。
    
    (2) ALL_ROWS -- 10g中的默认值
    
    Oracle 会用最快的速度将SQL执行完毕,将结果集全部返回,它和FIRST_ROWS(n)的区别在于,ALL_ROWS强调以最快的速度将SQL执行完毕,并将所有的结果集反馈回来,而FIRST_ROWS(n)则侧重于返回前n条记录的执行时间。
    
    修改CBO 模式的三种方法:
    
    (1) SQL 语句:
    
    Sessions级别:
    
    SQL> alter session set optimizer_mode=all_rows;
    
    (2) 修改pfile 参数:
    
    OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS
    
    (3) 语句级别用Hint(/* + ... */)来设定
    
    Select /*+ first_rows(10) */ name from table;
    
    Select /*+ all_rows */ name from table;
    
    
    

     原文网址:http://www.fengfly.com/plus/view-210418-1.html

    展开全文
  • PLSQL程序优化和性能分析方法

    热门讨论 2010-07-19 12:27:33
    2.4.19 批量数据插入 15 2.5 索引使用优化 16 2.5.1 避免在索引列上使用函数或运算 16 2.5.2 避免改变索引列的类型. 17 2.5.3 避免在索引列上使用NOT 17 2.5.4 用>=替代> 18 2.5.5 避免在索引列上使用IS NULL和IS ...
  • PLSQL

    2016-11-29 11:02:00
    plsql只有第一次执行的时候才会做分析操作,以后每次执行调用即可。语句可以保留 二、语法 1、匿名plsql declare --- 声明部分(可选) ..... (变量,常量,数据类型,游标 需要声明时,必须用declare) ...
  • 2014-06-02 Created By BaoXinjian 一、总论 1. 字段 ...二、具体分析 1. 字段 (1). 标准字段 l_wip_entity_id NUMBER; (2). 基于表字段 l_wip_entity_id wip_entities.wip_entity_id%TYP...
  • 初识PLSQL

    万次阅读 多人点赞 2018-07-19 21:43:21
    PLSQL的认识  通过本文将学习到 怎么安装PLSQLDeveloper PLSQL的优势 PLSQL的定义 PLSQL的基础 PLSQL的编写块 PLSQL的变量定义 PLSQL的 1、安装PLSQL的客户端。 从我个人的理解来看PLSQL更像一个IDEA+...
  • PLSQL==>分析函数

    2014-11-23 18:44:45
    1.创建示例数据: drop table sales_fact; CREATE table sales_fact AS  SELECT country_name country,country_subRegion region, prod_name product, calendar_year year, calendar_week_number week,  SUM...
  • plsql 分析函数 窗口函数篇

    千次阅读 2018-04-24 16:22:34
    目录=========================================1....窗口函数进阶-比较相邻记录一、窗口函数简介:到目前为止,我们所学习的分析函数在计算/统计一段时间内的数据时特别有用,但是假如计算/统计...
  • 本人新手,在完成存储过程的编写后,感觉需要对存储过程中各个语句的执行时间进行分析,以便 对整个存储过程进行优化。  由于用的是PLSQL Developer 客户端工具,而网上大多介绍的是通过手工方法应用Profiler,...
  • Elasticsearch 是一个非常通用的平台,支持各种各样的用例,并且为数据组织和复制策略提供了巨大灵活性。这种灵活性使得作为 ELK 新手的你将数据组织成索引和分片变得困难。虽然不一定会在首次启动时出现问题,但...
  • PLSQL使用

    2016-10-28 15:09:11
    登录后默认自动选中My Objects默认情况下,PLSQL Developer登录后,Brower里会选择All objects,如果你登录的用户是dba,要展开tables目录,正常情况都需要Wait几秒钟,而选择My Objects后响应速率则是以毫秒计算的...
  • 问题分析: 1.确保cvs字段名与表字段名一致,不要有空格 2.cvs字段对应表字段的大写,确保表字段都是大写 3.如果字段能对应上,plsql会自动识别出来 转载于:https://www.cnblogs.com/code4app/p/9935365.html...
  • PLSQL用spool导出CSV数据command

    千次阅读 2016-08-19 09:47:03
    导出查询出的数据到此csv文件。 导出路径为PLSQL安装路径。 spool /tmp/sgainfo.lst //指定路径与文件名 spool e:/wo.csv spool的语法格式: spool { file_name[.ext] [create|replace|append]| off | out } file_...
  • plsql 使用

    2019-06-19 13:58:55
    1.安装PLSQL Developer Oracle数据库和PL/SQL Developer的安装步骤,这里就不做叙述了,百度安装方法的时候有说在安装PL/SQL Developer软件时,不要安装在Program Files (x86)目录下,不然无法启动PL/SQL Developer...
  • 由于用的是PLSQL Developer 客户端工具,而网上大多介绍的是通过手工方法应用Profiler,使用相对比较烦杂,大致是通过命令行,以SYS用户首先创建dbms_profiler包,而且还要创建一个用于存放跟踪信息的用户,及其prof...
  • 在Java的性能调优分析中,可以使用 JProfiler 分析JVM运行时的CPU消耗、Memory占用、Thread情况等信息。对于Java代码中调用的Oracle的存储过程、函数它也能输出调用时间、调用次数等信息,但对于过程、函数内部的...
  • 聚合函数是我们所熟知的非常好用的...分析函数又称窗口函数,但实际上分析函数一般含有三个要素:分区子句、排序子句和窗口子句。这里的分区和分组的概念十分相像,同样是将若干特征一致的行分到一个分区中。但与 GROUP
  • PLSQL优化

    2017-10-20 16:30:29
    目录(?)[-] 优化的理论基础通过Select Count进入优化之旅 SELECT Count的知识 ...优化器的优化模式CBO模式RBO模式一起来看看oracle优化器的发展历程 ...索引按内部结构分类索引按功能分类索引按索引对象...PLSQL优化
  • Oracle PLSQL编程

    2018-06-22 15:20:57
    可有利于系统的移植,部分的数据处理和分析可以共享。过程化编程语言,用来编写包含SQL语句的程序。可以向数据库应用程序中加入业务逻辑处理功能。pl/sql结构在PL/SQL中只能用 SQL语句中的DML部分,不能使用DDL...
  • PLSQL事务

    2019-09-28 02:46:58
    1 使用set transaction设置事务属性 ... 4 在进行数据统计分析工作时,一般都会查询数据库中的多个表,此时可以将查询统计 5 工作定义为只读事务, 防止进行DML操作 6 --只读事务示例 7 declare 8 ...
  • plsql用于数据库

    2018-06-22 10:25:25
    用于连接数据库,处理数据分析,非常好用的破解版
  • plsql使用

    千次阅读 2014-08-02 11:27:54
    工欲善其事,必先利其器,最近研究了一下各种编程工具的使用,发掘了不少新功能,能够大大提升我们日常的工作效率,下面是转载的一篇PLSQL使用技巧,个人感觉很有用,分享一下。 1、PL/SQL Developer记住登陆...
  • PLSQL插上飞翔的翅膀-PLSQL优化

    万次阅读 多人点赞 2016-03-30 13:06:58
    来看看下面的案例分析吧: 以案例来说明 PLSQL优化>一个不走索引的优化案例 这个例子说明了,如果你有一字参于WHERE条件查询的字段,但是它参于了运算符,因此它在ORACLE的内部执行计划中是不会走索引的,因此我们做...
  • plsql例外 异常可能是最被滥用的Java语言功能。 这就是为什么 让我们打破一些神话。 没有牙仙子。 圣诞老人不是真实的。 TODO评论。 finalfinalversion-final.pdf。 无皂肥皂。 而且…例外实际上是例外。 后者...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 7,293
精华内容 2,917
关键字:

plsql大数据分析