精华内容
参与话题
问答
  • 安装anaconda之后,进入环境执行conda install ×××或者conda update --all 全文报错信息如下: CondaHTTPError: HTTP 000 CONNECTION FAILED for url <...

    安装anaconda之后,进入环境执行conda install  ×××或者conda update --all 全文报错信息如下:

    CondaHTTPError: HTTP 000 CONNECTION FAILED for url <http://mirrors.tuna.tsinghua.edu.cn/anaconda/cloud/conda-forge/noarch/repodata.json>
    Elapsed: -

    An HTTP error occurred when trying to retrieve this URL.
    HTTP errors are often intermittent, and a simple retry will get you on your way.
    ProxyError(MaxRetryError("HTTPConnectionPool(host='127.0.0.1', port=45433): Max retries exceeded with url: http://mirrors.tuna.tsinghua.edu.cn/anaconda/cloud/conda-forge/noarch/repodata.json (Caused by ProxyError('Cannot connect to proxy.', NewConnectionError('<urllib3.connection.HTTPConnection object at 0x7f66c8186650>: Failed to establish a new connection: [Errno 111] Connection refused',)))",),)


    按照网上说的更新清华源并不管用。想到之前用过科学上网工具蓝灯,所以感觉是代理的问题。仔细看报错信息

      host='127.0.0.1', port=45433  由上面的报错信息,查看网络是否走了代理,在命令行输入命令: export | grep -i prox

    打印信息如下:

    declare -x HTTPS_PROXY="http://127.0.0.1:45433/"         需要关闭
    declare -x HTTP_PROXY="http://127.0.0.1:45433/"
    declare -x NO_PROXY="localhost,127.0.0.0/8,::1"
    declare -x http_proxy="http://127.0.0.1:45433/"
    declare -x https_proxy="http://127.0.0.1:45433/"                    需要关闭
    declare -x no_proxy="localhost,127.0.0.0/8,::1"


    然后使用命令关闭代理:

    unset HTTPS_PROXY

    在次使用命令关闭代理:

     unset https_proxy


    然后重新使用命令   conda install ×××     ;就不会在报HTTP ERROR了。

    本人承接各种软件、硬件、算法、各种小程序,javva、C++、python、机器学习等等程序代写业务。欢迎加微信

                                                                        

     

     

     

    展开全文
  • 升级完AndroidStudio3.6.1...Caused by: org.gradle.api.internal.artifacts.ivyservice.DefaultLenientConfiguration$ArtifactResolveException: Could not resolve all artifacts for configuration ‘:classpa...

    升级完AndroidStudio3.6.1编译项目报错:

    Caused by: org.gradle.api.internal.artifacts.ivyservice.DefaultLenientConfiguration$ArtifactResolveException: Could not resolve all artifacts for configuration ‘:classpath’.

    提示错误:Read Time out

    即使开了梯子依然下载超时,我们更换为阿里云的maven库,下载速度会更快一点。

    将原来配置:

    buildscript {
    	repositories {
    	        google()
    	        jcenter()  
    	    }
        }
    allprojects {
    	repositories {
    	        google()      
    	        jcenter()
    	        }
        }
    

    改为:

    buildscript {
    	repositories {
    	        google()
    	        maven{url 'http://maven.aliyun.com/nexus/content/groups/public/'}
    	        jcenter()
    	       } 
        }
    allprojects {
    	repositories {
    	        google()
    	        maven{url 'http://maven.aliyun.com/nexus/content/groups/public/'}
    	        jcenter()
    	        }
        }
    

    等待一小会Gradle sync成功
    在这里插入图片描述

    展开全文
  • ORACLE FORALL介绍

    千次阅读 2013-07-07 15:41:09
    FORALL

    ORACLE 10G OFFICIAL DOCUMNET 

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

    一 介绍:

    1、语法

    for all statement ::=

    Description of forall_statement.gif follows

    bounds_clause ::=


    Description of bounds_clause.gif follows


    2、关键字与参数介绍:

    ==index_name:一个无需声明的标识符,作为集合下标使用;

    ==sql_statement:静态语句,例如:UPDATE或者DELETE;或者动态(EXECUTE IMMEDIATE)DML语句。

    ==SAVE EXCEPTIONS:可选关键字,表示即使一些DML语句失败,直到FORALL loop执行完毕才抛出异常。可以使用SQL%BULK_EXCEPTIONS 查看异常信息。

    ==lower_bound .. upper_bound:数字表达式,来指定一组连续有效的索引数字。该表达式只需解析一次。

    ==INDICES OF collection_name:用于指向稀疏数组的实际下标

    ==VALUES OF index_collection_name:用于指向集合的一个子集的下标数组


    二 使用FORALL:

    1、循环中声明删除语句(Issuing DELETE Statements in a Loop

    CREATE TABLE employees_temp AS SELECT * FROM employees;
    DECLARE
       TYPE NumList IS VARRAY(20) OF NUMBER;
       depts NumList := NumList(10, 30, 70);  -- department numbers
    BEGIN
       FORALL i IN depts.FIRST..depts.LAST
          DELETE FROM employees_temp WHERE department_id = depts(i);
       COMMIT;
    END;
    /

    2、循环中声明插入语句(Issuing INSERT Statements in a Loop)

    CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));
    CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15));
    DECLARE
      TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;
      TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;
      pnums  NumTab;
      pnames NameTab;
      iterations CONSTANT PLS_INTEGER := 50000;
      t1 INTEGER;
      t2 INTEGER;
      t3 INTEGER;
    BEGIN
      FOR j IN 1..iterations LOOP  -- load index-by tables
         pnums(j) := j;
         pnames(j) := 'Part No. ' || TO_CHAR(j);
      END LOOP;
      t1 := DBMS_UTILITY.get_time;
      FOR i IN 1..iterations LOOP  -- use FOR loop
         INSERT INTO parts1 VALUES (pnums(i), pnames(i));
      END LOOP;
      t2 := DBMS_UTILITY.get_time;
      FORALL i IN 1..iterations  -- use FORALL statement
         INSERT INTO parts2 VALUES (pnums(i), pnames(i));
      t3 := DBMS_UTILITY.get_time;
      DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
      DBMS_OUTPUT.PUT_LINE('---------------------');
      DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR((t2 - t1)/100));
      DBMS_OUTPUT.PUT_LINE('FORALL:   ' || TO_CHAR((t3 - t2)/100));
      COMMIT;
    END;
    /

    FORALL要明显快于FOR..LOOP结构:

    Execution Time (secs)
    ---------------------
    FOR loop: 5.14
    FORALL:   .56
    
    PL/SQL 过程已成功完成。

    3、集合部分元素使用FORALL(Using FORALL with Part of a Collection)

    DROP TABLE employees_temp;
    CREATE TABLE employees_temp AS SELECT * FROM employees;
    DECLARE
       TYPE NumList IS VARRAY(10) OF NUMBER;
       depts NumList := NumList(5,10,20,30,50,55,57,60,70,75);
    BEGIN
       FORALL j IN 4..7  -- use only part of varray
          DELETE FROM employees_temp WHERE department_id = depts(j);
       COMMIT;
    END;
    /

    4、对非连续索引值使用FORALL(Using FORALL with Non-Consecutive Index Values)

    -- Create empty tables to hold order details
    CREATE TABLE  valid_orders (cust_name VARCHAR2(32), amount NUMBER(10,2));
    CREATE TABLE  big_orders AS SELECT * FROM valid_orders WHERE 1 = 0;
    CREATE TABLE  rejected_orders AS SELECT * FROM valid_orders WHERE 1 = 0;
    

    DECLARE
    -- Make collections to hold a set of customer names and order amounts.
       SUBTYPE cust_name IS valid_orders.cust_name%TYPE;
       TYPE cust_typ IS TABLe OF cust_name;
       cust_tab cust_typ;
       SUBTYPE order_amount IS valid_orders.amount%TYPE;
       TYPE amount_typ IS TABLE OF NUMBER;
       amount_tab amount_typ;
    -- Make other collections to point into the CUST_TAB collection.
       TYPE index_pointer_t IS TABLE OF PLS_INTEGER;
       big_order_tab index_pointer_t := index_pointer_t();
       rejected_order_tab index_pointer_t := index_pointer_t();
       PROCEDURE setup_data IS BEGIN
     -- Set up sample order data, including some invalid orders and some 'big' orders.
         cust_tab := cust_typ('Company1','Company2','Company3','Company4','Company5');
         amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL);
       END;
       
    BEGIN
       setup_data(); --initialization
       DBMS_OUTPUT.PUT_LINE('--- Original order data ---');
       FOR i IN 1..cust_tab.LAST LOOP
         DBMS_OUTPUT.PUT_LINE('Customer #' || i || ', ' || cust_tab(i) || ': $' ||
                               amount_tab(i));
       END LOOP;
    -- Delete invalid orders (where amount is null or 0).
       FOR i IN 1..cust_tab.LAST LOOP
         IF amount_tab(i) is null or amount_tab(i) = 0 THEN
            cust_tab.delete(i);
            amount_tab.delete(i);
         END IF;
       END LOOP;
       DBMS_OUTPUT.PUT_LINE('--- Data with invalid orders deleted ---');
       FOR i IN 1..cust_tab.LAST LOOP
         IF cust_tab.EXISTS(i) THEN
           DBMS_OUTPUT.PUT_LINE('Customer #' || i || ', ' || cust_tab(i) || ': $' ||
                                 amount_tab(i));
          END IF;
       END LOOP;
    -- Because the subscripts of the collections are not consecutive, use
    -- FORALL...INDICES OF to iterate through the actual subscripts, 
    -- rather than 1..COUNT
       FORALL i IN INDICES OF cust_tab
         INSERT INTO valid_orders(cust_name, amount) 
            VALUES(cust_tab(i), amount_tab(i));
       
    -- Now process the order data differently
    -- Extract 2 subsets and store each subset in a different table
       setup_data(); -- Initialize the CUST_TAB and AMOUNT_TAB collections again.
       FOR i IN cust_tab.FIRST .. cust_tab.LAST LOOP
         IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN
           rejected_order_tab.EXTEND; -- Add a new element to this collection
    -- Record the subscript from the original collection
           rejected_order_tab(rejected_order_tab.LAST) := i; 
         END IF;
         IF amount_tab(i) > 2000 THEN
            big_order_tab.EXTEND; -- Add a new element to this collection
    -- Record the subscript from the original collection
            big_order_tab(big_order_tab.LAST) := i;
         END IF;
       END LOOP;
    -- Now it's easy to run one DML statement on one subset of elements, 
    -- and another DML statement on a different subset.
       FORALL i IN VALUES OF rejected_order_tab
         INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i));
       FORALL i IN VALUES OF big_order_tab
         INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i));
       COMMIT;
    END;
    /

    --- Original order data ---
    Customer #1, Company1: $5000.01
    Customer #2, Company2: $0
    Customer #3, Company3: $150.25
    Customer #4, Company4: $4000
    Customer #5, Company5: $
    --- Data with invalid orders deleted ---
    Customer #1, Company1: $5000.01
    Customer #3, Company3: $150.25
    Customer #4, Company4: $4000
     
    PL/SQL procedure successfully completed

    -- Verify that the correct order details were stored
    SELECT cust_name "Customer", amount "Valid order amount" FROM valid_orders;
    SELECT cust_name "Customer", amount "Big order amount" FROM big_orders;
    SELECT cust_name "Customer", amount "Rejected order amount" FROM rejected_orders;


    5、使用%BULK_ROWCOUNT返回受影响的记录行数

    CREATE TABLE emp_temp AS SELECT * FROM employees;
    DECLARE
       TYPE NumList IS TABLE OF NUMBER;
       depts NumList := NumList(30, 50, 60);
    BEGIN
       FORALL j IN depts.FIRST..depts.LAST
          DELETE FROM emp_temp WHERE department_id = depts(j);
    -- How many rows were affected by each DELETE statement?
       FOR i IN depts.FIRST..depts.LAST
       LOOP
          DBMS_OUTPUT.PUT_LINE('Iteration #' || i || ' deleted ' ||
             SQL%BULK_ROWCOUNT(i) || ' rows.');
       END LOOP;
    END;
    /



    6、FORALL与BULK COLLECT 一起使用(Using FORALL With BULK COLLECT)

    CREATE TABLE emp_temp AS SELECT * FROM employees;
    DECLARE
       TYPE NumList IS TABLE OF NUMBER;
       depts NumList := NumList(10,20,30);
       TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
       TYPE dept_t IS TABLE OF employees.department_id%TYPE;
       e_ids enum_t;
       d_ids dept_t;
    BEGIN
      FORALL j IN depts.FIRST..depts.LAST
        DELETE FROM emp_temp WHERE department_id = depts(j)
           RETURNING employee_id, department_id BULK COLLECT INTO e_ids, d_ids;
      DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
      FOR i IN e_ids.FIRST .. e_ids.LAST
      LOOP
        DBMS_OUTPUT.PUT_LINE('Employee #' || e_ids(i) || ' from dept #' || d_ids(i));
      END LOOP;
    END;
    /






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

    Present   by   dylan.

    展开全文
  • 批量 SQL 之 FORALL 语句

    万次阅读 2012-05-05 10:26:09
    对PL/SQL而言,任何的PL/SQL块或者子程序都是PL/SQL引擎来处理,而其中包含的SQL语句则由PL/SQL引擎发送SQL语句转交到SQL...因此为减少性能的FORALL与BULK COLLECT的子句应运而生。即仅仅使用一次切换次执行来降低
        对PL/SQL而言,任何的PL/SQL块或者子程序都是PL/SQL引擎来处理,而其中包含的SQL语句则由PL/SQL引擎发送SQL语句转交到SQL引擎来处
    理,SQL引擎处理完毕后向PL/SQL引擎返回数据。Pl/SQL与SQL引擎之间的通信则称之为上下文切换。过多的上下文切换将带来过量的性能负载。
    因此为减少性能的FORALL与BULK COLLECT的子句应运而生。即仅仅使用一次切换多次执行来降低上下文切换次数。本文主要描述FORALL子句。

    一、FORALL语法描述


        FORALL loop_counter IN bounds_clause            -->注意FORALL块内不需要使用loop, end loop
        SQL_STATEMENT [SAVE EXCEPTIONS];
        
        bounds_clause的形式
        lower_limit .. upper_limit                                     -->指明循环计数器的上限和下限,与for循环类似
        INDICES OF collection_name BETWEEN lower_limit .. upper_limit  -->引用特定集合元素的下标(该集合可能为稀疏)
        VALUES OF colletion_name                                       -->引用特定集合元素的值
        
        SQL_STATEMENT部分:SQL_STATEMENT部分必须是一个或者多个集合的静态或者动态的DML(insert,update,delete)语句。
        SAVE EXCEPTIONS部分:对于SQL_STATEMENT部分导致的异常使用SAVE EXCEPTIONS来保证异常存在时语句仍然能够继续执行。

    二、使用 FORALL 代替 FOR 循环提高性能
    -->下面的示例使用了FOR循环与FORALL循环操作进行对比,使用FORALL完成同样的功能,性能明显提高
    CREATE TABLE t
    (
       col_num   NUMBER
      ,col_var   VARCHAR2( 10 )
    );
    
    DECLARE
       TYPE col_num_type IS TABLE OF NUMBER            -->声明了两个联合数组
                               INDEX BY PLS_INTEGER;
    
       TYPE col_var_type IS TABLE OF VARCHAR2( 10 )
                               INDEX BY PLS_INTEGER;
    
       col_num_tab    col_num_type;
       col_var_tab    col_var_type;
       v_start_time   INTEGER;
       v_end_time     INTEGER;
    BEGIN
       FOR i IN 1 .. 5000                    -->使用FOR循环向数组填充元素
       LOOP
          col_num_tab( i ) := i;
          col_var_tab( i ) := 'var_' || i;
       END LOOP;
    
       v_start_time := DBMS_UTILITY.get_time;  -->获得FOR循环向表t插入数据前的初始时间
    
       FOR i IN 1 .. 5000                   -->使用FOR循环向表t插入数据
       LOOP
          INSERT INTO t
          VALUES ( col_num_tab( i ), col_var_tab( i ) );
       END LOOP;
    
       v_end_time  := DBMS_UTILITY.get_time;
       DBMS_OUTPUT.put_line( 'Duration of the FOR LOOP: ' || ( v_end_time - v_start_time ) );
       v_start_time := DBMS_UTILITY.get_time;
    
       FORALL i IN 1 .. 5000          -->使用FORALL循环向表t插入数据
          INSERT INTO t
          VALUES ( col_num_tab( i ), col_var_tab( i ) );
    
       v_end_time  := DBMS_UTILITY.get_time;
       DBMS_OUTPUT.put_line( 'Duration of the FORALL STATEMENT: ' || ( v_end_time - v_start_time ) );
       COMMIT;
    END;
    
    Duration of the FOR LOOP: 68           -->此处的计时单位为百分之一秒,即0.68s,下同
    Duration of the FORALL STATEMENT: 18
    
    PL/SQL procedure successfully completed.
    三、SAVE EXCEPTIONS
             对于任意的SQL语句执行失败,将导致整个语句或整个事务会滚。而使用SAVE EXCEPTIONS可以使得在对应的SQL语句异常的情形下,FORALL
    仍然可以继续执行。如果忽略了SAVE EXCEPTIONS时,当异常发生,FORALL语句就会停止执行。因此SAVE EXCEPTIONS使得FORALL子句中的DML下
    产生的所有异常都将记录在SQL%BULK_EXCEPTIONS的游标属性中。SQL%BULK_EXCEPTIONS属性是个记录集合,其中的每条记录由两个字段组成,

    ERROR_INDEX和ERROR_CODE。ERROR_INDEX字段会存储发生异常的FORALL语句的迭代编号,而ERROR_CODE则存储对应异常的ORACLE错误代码。类似于这样:(2,01400),(6,1476)和(10,12899)。存放在%BULK_EXCEPTIONS中的值总是与最近一次FORALL语句执行的结果相关,异常的个数存放在%BULK_EXCEPTIONS的COUNT属性中,%BULK_EXCEPTIONS有效的下标索引范围在1到%BULK_EXCEPTIONS.COUNT之间。

    1、%BULK_EXCEPTIONS的用法
    CREATE TABLE tb_emp AS              -->创建表tb_emp
       SELECT empno, ename, hiredate
       FROM   emp
       WHERE  1 = 2;
    
    ALTER TABLE tb_emp MODIFY(empno NOT NULL);   -->为表添加约束
    
    DECLARE
       TYPE col_num_type IS TABLE OF NUMBER            -->一共定义了3个联合数组类型
                               INDEX BY PLS_INTEGER;
    
       TYPE col_var_type IS TABLE OF VARCHAR2( 100 )
                               INDEX BY PLS_INTEGER;
    
       TYPE col_date_type IS TABLE OF DATE
                                INDEX BY PLS_INTEGER;
    
       empno_tab      col_num_type;
       ename_tab      col_var_type;
       hiredate_tab   col_date_type;
       v_counter      PLS_INTEGER := 0;
       v_total        INTEGER := 0;
       errors         EXCEPTION;                      -->声明异常
       PRAGMA EXCEPTION_INIT( errors, -24381 );
    BEGIN
       FOR rec IN ( SELECT empno, ename, hiredate FROM emp )   -->使用for循环将数据填充到联合数组
       LOOP
          v_counter   := v_counter + 1;
          empno_tab( v_counter ) := rec.empno;
          ename_tab( v_counter ) := rec.ename;
          hiredate_tab( v_counter ) := rec.hiredate;
       END LOOP;
    
       empno_tab( 2 ) := NULL;                                -->对部分数据进行处理以产生异常
       ename_tab( 5 ) := RPAD( ename_tab( 5 ), 15, '*' );
       empno_tab( 10 ) := NULL;
    
       FORALL i IN 1 .. empno_tab.COUNT                      -->使用forall将联合数组中的数据插入到表tb_emp
       SAVE EXCEPTIONS
          INSERT INTO tb_emp
          VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) );
    
       COMMIT;
    
       SELECT COUNT( * ) INTO v_total FROM tb_emp;
    
       DBMS_OUTPUT.put_line( v_total || ' rows were inserted to tb_emp' );
    EXCEPTION
       WHEN errors THEN
          DBMS_OUTPUT.put_line( 'There are ' || SQL%bulk_exceptions.COUNT || ' exceptions' );
    
          FOR i IN 1 .. SQL%bulk_exceptions.COUNT            -->SQL%bulk_exceptions.COUNT记录异常个数来控制迭代
          LOOP
             DBMS_OUTPUT.
              put_line(
                           'Record '
                        || SQL%bulk_exceptions( i ).error_index
                        || ' caused error '
                        || i
                        || ': '
                        || SQL%bulk_exceptions( i ).error_code
                        || ' '
                        || SQLERRM( -SQL%bulk_exceptions( i ).error_code ) );   -->使用SQLERRM根据错误号抛出具体的错误信息
          END LOOP;
    END;
    
    There are 3 exceptions
    Record 2 caused error 1: 1400 ORA-01400: cannot insert NULL into ()
    Record 5 caused error 2: 12899 ORA-12899: value too large for column  (actual: , maximum: )
    Record 10 caused error 3: 1400 ORA-01400: cannot insert NULL into ()
    
    PL/SQL procedure successfully completed.
    
    2、%BULK_ROWCOUNT
    	%BULK_ROWCOUNT也是专门为FORALL设计的,用于保存第i个元素第i次insert或update或delete所影响到的行数。如果第i次操作没有行被影
    响,则%BULK_ROWCOUNT返回为零值。FORALL语句和%BULK_ROWCOUNT属性使用同样的下标索引。如果FORALL使用下标索引的范围在5到8的话,那么
    %BULK_ROWCOUNT的也是5到8。需要注意的是一般情况下,对于insert .. values而言,所影响的行数为1,即%BULK_ROWCOUNT的值为1。而对于
    insert .. select方式而言,%BULK_ROWCOUNT的值就有可能大于1。update与delete语句存在0,1,以及大于1的情形。
    
    DECLARE
       TYPE dept_tab_type IS TABLE OF NUMBER;
    
       dept_tab   dept_tab_type := dept_tab_type( 10, 20, 50 );    -->声明及初始化嵌套表
    BEGIN
       FORALL i IN dept_tab.FIRST .. dept_tab.LAST                 -->使用FORALL更新
          UPDATE emp
          SET    sal          = sal * 1.10
          WHERE  deptno = dept_tab( i );
    
       -- COMMIT;
       FOR i IN 1 .. dept_tab.COUNT                               -->循环输出每次执行SQL语句影响的行数
       LOOP
          DBMS_OUTPUT.put_line( 'Dept no ' || dept_tab( i ) || ' has ' || SQL%bulk_rowcount (i) || ' rows been updated' );
       END LOOP;
    
       -- Did the 3rd UPDATE statement affect any rows?
       IF SQL%bulk_rowcount (3) = 0 THEN
          DBMS_OUTPUT.put_line( 'The deptno 50 has not child record' );
       END IF;
    END;
    
    Dept no 10 has 3 rows been updated
    Dept no 20 has 5 rows been updated
    Dept no 50 has 0 rows been updated
    The deptno 50 has not child record
    
    PL/SQL procedure successfully completed.
    四、INDICES OF 选项
        INDICES OF 选项用于处理稀疏集合类型。即当集合(嵌套表或联合数组)中的元素被删除之后,对稀疏集合实现迭代。
    -->下面的脚本同前面的示例基本相似,所不同的是使用了delete方式删除其中的部分记录,导致集合变得稀疏。
    -->其次在forall子句处使用indices OF方式来控制循环。		
    TRUNCATE TABLE tb_emp;
    
    DECLARE
       TYPE col_num_type IS TABLE OF NUMBER
                               INDEX BY PLS_INTEGER;
    
       TYPE col_var_type IS TABLE OF VARCHAR2( 100 )
                               INDEX BY PLS_INTEGER;
    
       TYPE col_date_type IS TABLE OF DATE
                                INDEX BY PLS_INTEGER;
    
       empno_tab      col_num_type;
       ename_tab      col_var_type;
       hiredate_tab   col_date_type;
       v_counter      PLS_INTEGER := 0;
       v_total        INTEGER := 0;
    BEGIN
       FOR rec IN ( SELECT empno, ename, hiredate FROM emp )
       LOOP
          v_counter   := v_counter + 1;
          empno_tab( v_counter ) := rec.empno;
          ename_tab( v_counter ) := rec.ename;
          hiredate_tab( v_counter ) := rec.hiredate;
       END LOOP;
    
       empno_tab.delete( 2 );       -->此处删除了数组中的第二个元素,导致数组变为稀疏型
       ename_tab.delete( 2 );
       hiredate_tab.delete( 2 );
    
       FORALL i IN indices OF empno_tab   -->此处使用了indices OF empno_tab,则所有未被delete的元素都将进入循环
          INSERT INTO tb_emp
          VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) );
    
       COMMIT;
    
       SELECT COUNT( * ) INTO v_total FROM tb_emp;
    
       DBMS_OUTPUT.put_line( v_total || ' rows were inserted to tb_emp' );
    END;
    
    13 rows were inserted to tb_emp
    
    PL/SQL procedure successfully completed.
    五、VALUES OF 选项
        VALUES OF选项可以指定FORALL语句中循环计数器的值来自于指定集合中元素的值。
        VALUES OF选项使用时有一些限制
              如果VALUES OF子句中所使用的集合是联合数组,则必须使用PLS_INTEGER和BINARY_INTEGER进行索引
              VALUES OF 子句中所使用的元素必须是PLS_INTEGER或BINARY_INTEGER
              当VALUES OF 子句所引用的集合为空,则FORALL语句会导致异常               
    TRUNCATE TABLE tb_emp;
    				
    CREATE TABLE tb_emp_ins_log AS                    -->创建一张与tb_emp结构类似的表tb_emp_ins_log
       SELECT *
       FROM   tb_emp
       WHERE  1 = 0;
    
    ALTER TABLE tb_emp_ins_log MODIFY(ename VARCHAR2(50));   -->修改列ename的长度
    
    DECLARE
       TYPE col_num_type IS TABLE OF tb_emp.empno%TYPE
                               INDEX BY PLS_INTEGER;
    
       TYPE col_var_type IS TABLE OF VARCHAR2( 100 )
                               INDEX BY PLS_INTEGER;
    
       TYPE col_date_type IS TABLE OF tb_emp.hiredate%TYPE
                                INDEX BY PLS_INTEGER;
    
       TYPE ins_log_type IS TABLE OF PLS_INTEGER         -->此处较之前的示例多声明了一个联合数组
                               INDEX BY PLS_INTEGER;     -->用于填充异常记录的元素值
    
       empno_tab      col_num_type;
       ename_tab      col_var_type;
       hiredate_tab   col_date_type;
       ins_log_tab    ins_log_type;
       v_counter      PLS_INTEGER := 0;
       v_total        INTEGER := 0;
       errors         EXCEPTION;
       PRAGMA EXCEPTION_INIT( errors, -24381 );
    BEGIN
       FOR rec IN ( SELECT empno, ename, hiredate FROM emp )
       LOOP
          v_counter   := v_counter + 1;
          empno_tab( v_counter ) := rec.empno;
          ename_tab( v_counter ) := rec.ename;
          hiredate_tab( v_counter ) := rec.hiredate;
       END LOOP;
    
       ename_tab( 2 ) := RPAD( ename_tab( 2 ), 15, '*' );    -->使记录2与记录5的ename列长度变长而产生异常
       ename_tab( 5 ) := RPAD( ename_tab( 5 ), 15, '*' );
       empno_tab( 6 ) := NULL;                          -->使第6条记录的empno为NULL值,由于表tb_emp的empno不允许为NULL而产生异常
    
       FORALL i IN 1 .. empno_tab.COUNT
       SAVE EXCEPTIONS
          INSERT INTO tb_emp
          VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) );
    
       COMMIT;
    EXCEPTION
       WHEN errors THEN
          FOR i IN 1 .. SQL%bulk_exceptions.COUNT
          LOOP
             ins_log_tab( i ) := SQL%bulk_exceptions( i ).error_index;   -->异常记录的索引值将填充ins_log_type联合数组
          END LOOP;                                    -->此处的结果是ins_log_tab(1)=2,  ins_log_tab(2)=5,  ins_log_tab(2)=6
    
          FORALL i IN VALUES OF ins_log_tab   -->使用VALUES OF子句为ins_log_type联合数组中的元素值
             INSERT INTO tb_emp_ins_log
             VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) );  -->因此values中的i分别为2和5
    
          COMMIT;
    END;
    
    PL/SQL procedure successfully completed.
    
    -->异常的记录被插入到表tb_emp_ins_log
    select * from tb_emp_ins_log;
    
         EMPNO ENAME                                              HIREDATE
    ---------- -------------------------------------------------- ---------
          7369 Henry**********                                    17-DEC-80
          7566 JONES**********                                    02-APR-81
               MARTIN                                             28-SEP-81
    六、INDICES OF 与 VALUES OF 的综合运用
    -->下面的例子来自Oracle		http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm
    -- Create empty tables to hold order details
    
    CREATE TABLE valid_orders
    (
       cust_name   VARCHAR2( 32 )
      ,amount      NUMBER( 10, 2 )
    );
    
    CREATE TABLE big_orders AS
       SELECT *
       FROM   valid_orders
       WHERE  1 = 0;
    
    CREATE TABLE rejected_orders AS
       SELECT *
       FROM   valid_orders
       WHERE  1 = 0;
    
    DECLARE
       -- Make collections to hold a set of customer names and order amounts.
       SUBTYPE cust_name IS valid_orders.cust_name%TYPE;
    
       TYPE cust_typ IS TABLE OF cust_name;
    
       cust_tab             cust_typ;
    
       SUBTYPE order_amount IS valid_orders.amount%TYPE;
    
       TYPE amount_typ IS TABLE OF NUMBER;
    
       amount_tab           amount_typ;
    
       -- Make other collections to point into the CUST_TAB collection.
       TYPE index_pointer_t IS TABLE OF PLS_INTEGER;
    
       big_order_tab        index_pointer_t := index_pointer_t( );
       rejected_order_tab   index_pointer_t := index_pointer_t( );
    
       PROCEDURE setup_data IS
       BEGIN
          -- Set up sample order data, including some invalid orders and some 'big' orders.
          cust_tab    :=
             cust_typ( 'Company1','Company2','Company3','Company4','Company5' );
          amount_tab  :=
             amount_typ( 5000.01,0,150.25,4000.00,NULL );
       END;
    BEGIN
       setup_data( );
       DBMS_OUTPUT.put_line( '--- Original order data ---' );
    
       FOR i IN 1 .. cust_tab.LAST
       LOOP
          DBMS_OUTPUT.put_line( 'Customer #' || i || ', ' || cust_tab( i ) || ': $' || amount_tab( i ) );
       END LOOP;
    
       -- Delete invalid orders (where amount is null or 0).
       FOR i IN 1 .. cust_tab.LAST
       LOOP
          IF amount_tab( i ) IS NULL OR amount_tab( i ) = 0 THEN
             cust_tab.delete( i );
             amount_tab.delete( i );
          END IF;
       END LOOP;
    
       DBMS_OUTPUT.put_line( '--- Data with invalid orders deleted ---' );
    
       FOR i IN 1 .. cust_tab.LAST
       LOOP
          IF cust_tab.EXISTS( i ) THEN
             DBMS_OUTPUT.put_line( 'Customer #' || i || ', ' || cust_tab( i ) || ': $' || amount_tab( i ) );
          END IF;
       END LOOP;
    
       -- Because the subscripts of the collections are not consecutive, use
       -- FORALL...INDICES OF to iterate through the actual subscripts,
       -- rather than 1..COUNT
       FORALL i IN indices OF cust_tab
          INSERT INTO valid_orders( cust_name, amount )
          VALUES ( cust_tab( i ), amount_tab( i ) );
    
       -- Now process the order data differently
       -- Extract 2 subsets and store each subset in a different table
       setup_data( );                    -- Initialize the CUST_TAB and AMOUNT_TAB collections again.
    
       FOR i IN cust_tab.FIRST .. cust_tab.LAST
       LOOP
          IF amount_tab( i ) IS NULL OR amount_tab( i ) = 0 THEN
             rejected_order_tab.EXTEND;                          -- Add a new element to this collection
             -- Record the subscript from the original collection
             rejected_order_tab( rejected_order_tab.LAST ) := i;
          END IF;
    
          IF amount_tab( i ) > 2000 THEN
             big_order_tab.EXTEND;                            -- Add a new element to this collection
             -- Record the subscript from the original collection
             big_order_tab( big_order_tab.LAST ) := i;
          END IF;
       END LOOP;
    
       -- Now it's easy to run one DML statement on one subset of elements,
       -- and another DML statement on a different subset.
       FORALL i IN VALUES OF rejected_order_tab
          INSERT INTO rejected_orders
          VALUES ( cust_tab( i ), amount_tab( i ) );
    
       FORALL i IN VALUES OF big_order_tab
          INSERT INTO big_orders
          VALUES ( cust_tab( i ), amount_tab( i ) );
    
       COMMIT;
    END;
    
    --- Original order data ---
    Customer #1, Company1: $5000.01
    Customer #2, Company2: $0
    Customer #3, Company3: $150.25
    Customer #4, Company4: $4000
    Customer #5, Company5: $
    --- Data with invalid orders deleted ---
    Customer #1, Company1: $5000.01
    Customer #3, Company3: $150.25
    Customer #4, Company4: $4000
    
    PL/SQL procedure successfully completed.
    
    SELECT cust_name "Customer", amount "Valid order amount" FROM valid_orders;
    
    Customer                         Valid order amount
    -------------------------------- ------------------
    Company1                                    5000.01
    Company3                                     150.25
    Company4                                       4000
    
    SELECT cust_name "Customer", amount "Big order amount" FROM big_orders;
    
    Customer                         Big order amount
    -------------------------------- ----------------
    Company1                                  5000.01
    Company4                                     4000
    
    SELECT cust_name "Customer", amount "Rejected order amount" FROM rejected_orders;
    
    Customer                         Rejected order amount
    -------------------------------- ---------------------
    Company2                                             0
    Company5
    
    --Author: Robinson Cheng
    --Blog : http://blog.csdn.net/robinson_0612
         
    --	上面的例子对订单进行分类,并将其保存到三张不同类型的表中。
    --	1、首先定义了两个嵌套表cust_tab,amount_tab用于保存原始数据,setup_data( )则用来初始化数据。
    --	2、第一个for循环用于输出所有的订单,第二个for循环则用来将删除amount_tab中为NULL或0值的记录。
    --	3、第三个for循环则用来输出经过删除之后剩余的记录,使用exists方法判断。
    --	4、使用forall子句将所有有效的记录插入到valid_orders,注意此时使用了indices of,因此此时的两个嵌套表已为稀疏表。
    --	5、在这之后,使用setup_data( )重新初始化数据。
    --	6、将无效订单的下标记录到rejected_order_tab嵌套表,将amount > 2000订单的下标记录到big_order_tab。
    --	7、使用VALUES OF 子句将两个嵌套表中对应下表的记录插入到对应的表中。
    七、更多参考

    批量 SQL 之 BULK COLLECT 子句

    PL/SQL 集合的初始化与赋值
    PL/SQL 联合数组与嵌套表
    PL/SQL 变长数组
    PL/SQL --> PL/SQL记录

    SQL tuning 步骤

    高效SQL语句必杀技

    父游标、子游标及共享游标

    绑定变量及其优缺点

    dbms_xplan之display_cursor函数的使用

    dbms_xplan之display函数的使用

    执行计划中各字段各模块描述

    使用 EXPLAIN PLAN 获取SQL语句执行计划

    启用 AUTOTRACE 功能

    函数使得索引列失效

    Oracle 绑定变量窥探

    Oracle 自适应共享游标                     
    展开全文
  • Android Studio报Could not resolve all artifacts for configuration ':app:classpath' 异常
  • 有时候, 如果makefile没有写好, 那么当修改啦.cpp后, 重新make all, 会提示nothing to be done for all, 此时怎么办呢?   如果蛮力make clean再make all, 那显然是费力不讨好了, 需要耗费太长的时间, 那...
  • 官方文档I have spent the last few days learning about how to use the new Android PreferenceFragment which requires PreferenceActivity to override a new v11 (Honeycomb) method called onBuildHeaders()....
  • PLSQL批量Forall操作性能提升详解

    千次阅读 2012-12-20 01:02:37
    首先创建测试表 create table test1 (c1 number , c2 number ,c3 number) ; create table test2 (c1 number , c2 number ,c3 number) ; 开始测试 ... 2 l_stat_sql varchar2(2000) := 'select valu
  • FORALL 之 SAVE EXCEPTIONS 子句应用一例

    千次阅读 2012-08-12 20:06:42
    对于大批量的DML操作中出现的错误,除了使用...DML error logging特性的使用较FORALL之 SAVE EXCEPTIONS相对简单,也存在一些不足,如每一被操作的DML对象需要创建相应的对应的日志表,不利于集中管理。本文对DML er
  • 1、错误描述2017-01-17 18:04:21,687 [http-apr-8080-exec-1] ERROR [core.interceptor.ExceptionResolverCustom] - nested exception is org.apache.ibatis.type.TypeException: JDBC requires that the JdbcType ...
  • 如果有大数据量的DML操作事务,在OLAP报表等低并发库里. 并且强制归档模式中.采用BULK 和FORALL 会比较快! open cur_COLUMN_USER; loop fetch cur_COLUMN_USER bulk collect into l_ARY_statedate,
  • Spring MVC 项目在使用 DeferredResult 实现异步接口时出现错误。完整错误信息如下:十一月 03, 2017 8:59:53 上午 org.apache.catalina...严重: Servlet.service() for servlet [SpringMVC] in context with path [/s
  • make[1]: Nothing to be done for `all-am'.

    千次阅读 2018-11-10 14:24:49
    1.这句提示是说明你已经编译好了,而且没有对代码进行任何改动。 若想重新编译,可以先删除以前编译产生的目标文件: make clean make 2.也可以 ...make: Nothing to be done for `all’ 解决方法 ...
  • 摘要:本文提出了一种用卷积神经网络(CNN)构建的图像去雾模型,称为一体化除雾网络(AOD-Net)。它是基于重新配制的大气散射模型设计的。 AOD-Net不是像大多数先前模型那样分别估算传输矩阵和...在合成和自然模糊图...
  • make: Nothing to be done for `all' 解决方法 1.这句提示是说明你已经编译好了,而且没有对代码进行任何改动。 若想重新编译,可以先删除以前编译产生的目标文件: make clean然后再 make 2.出现这种情况解决方法...
  • 1. 创建分区表 create table range_part_range(id number, deal_date date, contents varchar2(1000)) partition by range(deal_date) ... partition p1 values less than (to_date('2015-01-21', 'yyyy-mm-dd'...
  • NgRx/Store 4 + Angular 5,调试时报这错误:Uncaught Error: Can't resolve all parameters for ApplicationModule。 解决办法:polyfills.ts文件里导入这包, import 'core-js/es7/reflect'; ...
  • Sysstat是一非常方便的工具,它带有众多的系统资源监控工具,用于监控系统的性能和使用情况。我们在日常使用的工具中有相当一部分是来自sysstat工具包的。同时,它还提供了一种使用cron表达式来制定性能和活动数据...
  • 类型转换错误 错误关键词: recipe 错误提示比如: make[2]: *** [CMakeFiles/stereo_kitti.dir/Examples/Stereo/stereo_kitti.cc.o] Error 1 [ 93%] Linking CXX executable ../Examples/Stereo/stereo_euroc ...
  • MATLAB 时间序列预测算法的实现

    千次阅读 2020-01-27 17:30:39
    最近看了一篇关于时间序列模型的论文 以下是关于matlab代码的部分...%clear all; %y=[533.8 574.6 606.9 649.8 705.1 772.0 816.4 892.7 963.9 1015.1 1102.7]; %m=length(y); %n=[4,5]; % 自定义 %for i=1:lengt...
  • np.stack 和 np.concatenate两函数都是用来连接数组的, 但是他们之间还是有一些探讨之处,直接上代码,一看便知:   import numpy as np ...# for np.stack:all input arrays must have th...
  • plsql forall 详解

    千次阅读 2019-01-22 08:16:04
    文章目录1 概述1.1 图示2 forall 的三种用法2.1 forall i in index_min .. index_max2.2 forall i in indices of collection2.3 forall i in values of collection3 forall 效率对比 1 概述 1. 作用:提高在 pl/sql ...
  • repo forall

    千次阅读 2017-07-05 20:34:22
    repo forall可以遍历每repo仓库并执行同样的命令 用法为: repo forall [...] -c [...] -c后可以直接跟任何shell命令 eg: repo forall -p -c git checkout branch_name repo forall -h Usage: repo ...
  • ABAP中使用for all entries in小结

    万次阅读 2015-06-15 09:52:26
    ABAP开发中,使用for all entries in语句将不能使用join的聚集表(例如BSEG)或者需要使用select的内表与内表串联。 以BSEG为例: select belnr hkont from bsis into corresponding fields of table itab1 where .......
  • 报错信息: Bean 'org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration' of type [org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration$$...
  • for循环和forall循环的区别

    千次阅读 2013-02-20 14:21:54
    2、使用批量绑定优化PL/SQL ...例如,下面的UPDATE语句就在FOR语句中不断发送到SQL引擎: DECLARE  TYPE numlist IS VARRAY(20) OF NUMBER;  depts numlist := numlist(10, 30, 70, ..
  • oracle forall 介绍

    千次阅读 2008-09-16 19:05:00
    oracle forallFORALL语句的一关键性改进,它可...1:用FORALL来增强DML的处理能力Oracle为Oracle8i中的PL/SQL引入了两新的数据操纵语言(DML)语句:BULK COLLECT和FORALL。这两语句在PL/SQL内部进行一种数组处理

空空如也

1 2 3 4 5 ... 20
收藏数 222,445
精华内容 88,978
关键字:

forall