+ -
当前位置:首页 → 问答吧 → forall中使用buLK

forall中使用buLK

时间:2011-09-13

来源:互联网

报错:
SQL命令为正确结束
定位在13行

[Copy to clipboard] [ - ]
CODE:
--forall
DECLARE
  TYPE ename_table_name IS TABLE OF emp.ename%TYPE ;
  TYPE sal_table_name IS TABLE OF emp.sal%TYPE;
  ename_table ename_table_name;
  sal_table   sal_table_name;
  sql_stat    VARCHAR2(200);
BEGIN
  ename_table:=ename_table_name('SCOTT','SMITH','CLARK');
  
  sql_stat := 'update emp set sal=sal*1.1 where ename=:1'||'returing sal into :2';
--12
FORALL i IN 1 .. ename_table.count
EXECUTE IMMEDIATE sql_stat USING ename_table(i)
RETURNING BULK COLLECT INTO sal_table;
   
  FOR j IN 1 .. ename_table.COUNT LOOP
    dbms_output.put_line('employee: ' || ename_table(j) || ' New salary: ' ||
                         sal_table(j));
  END LOOP;
END;

FORALL i IN 1 .. ename_table.count
EXECUTE IMMEDIATE sql_stat USING ename_table(i)
RETURNING BULK COLLECT INTO sal_table;
这个语句的使用 有什么问题嘛?

作者: it_china_110   发布时间: 2011-09-13



代码如下:


SQL> select ename, sal, sal*1.1 new_sal from emp where ename in('SCOTT', 'SMITH', 'CLARK');

ENAME            SAL    NEW_SAL
---------- --------- ----------
SMITH         800.00        880
CLARK        2450.00       2695
SCOTT        3000.00       3300

SQL>
SQL> DECLARE
  2    TYPE ename_table_name IS TABLE OF emp.ename%TYPE;
  3    TYPE sal_table_name IS TABLE OF emp.sal%TYPE;
  4    ename_table ename_table_name;
  5    sal_table   sal_table_name;
  6    sql_stat    VARCHAR2(200);
  7  BEGIN
  8    ename_table := ename_table_name('SCOTT', 'SMITH', 'CLARK');
  9  
10    sql_stat := 'update emp set sal=sal*1.1 where ename=:1 returning sal into :2';
11  
12    FORALL i IN 1 .. ename_table.count
13       EXECUTE IMMEDIATE sql_stat USING ename_table(i) RETURNING BULK COLLECT INTO sal_table;
14  
15  
16    FOR j IN 1 .. ename_table.COUNT LOOP
17      dbms_output.put_line('employee: ' || ename_table(j) || ' New salary: ' || sal_table(j));
18    END LOOP;
19  
20  END;
21  /

employee: SCOTT New salary: 3300
employee: SMITH New salary: 880
employee: CLARK New salary: 2695

PL/SQL procedure successfully completed

SQL>



作者: bell6248   发布时间: 2011-09-13