oracle 无法将数据加载到嵌套表(集合)中

e0bqpujr 于 2023-01-08 发布在 Oracle

关注(0)|答案(2)|浏览(122)

我想从不同的游标将数据加载到一个集合中。下面你可以看到一个相同的小例子。简单地说，我需要从不同的表中取列并将其加载到单个表中。最初我们使用的是直接插入语句，它工作得很好，但是最近它引起了一些问题。所以我们尝试引入一个集合来收集数据，然后从那里将数据加载到表中。

declare
    vin date;
    cursor c1 is select emp_id.. from emp;
    cursor c2 is select dept_id , ... from dept;
    type t3 is table of t%rowtype;
    t1 t3 := t3();
    begin
    for i in (select emp_id,dept_id,vin from dual)
    loop
    t1.extend;
    t1(t1.count).load_date := i.vin;
    t1(t1.count).emp_id := i.emp_id;
     t1(t1.count).dept_no := i.dept_id;
    end loop;
    forall  j in 1..t1.last
     insert into t values (t1(j).emp_id,t1(j).dept_id,t1(j).load_date);
     end;

当我尝试运行这个程序时，我得到一个错误，声明PLS00308：该构造函数不允许作为赋值的源函数。

oracle

来源：https://stackoverflow.com/questions/75019623/unable-to-load-the-data-into-a-nested-tablecollection

2条答案

按热度按时间

fdx2calv1#

尝试如下：

SET SERVEROUTPUT ON
Declare
    TYPE type_a_tbl is Table Of A_TBL%ROWTYPE INDEX BY BINARY_INTEGER;
    i   BINARY_INTEGER := 0;
    tab_a_tbl  type_a_tbl;
    --
    CURSOR c IS
        Select      e.EMPNO, e.ENAME, d.DEPTNO, d.DNAME, d.LOC, e.MGR
        From        EMP e
        Inner Join  DEPT d ON(d.DEPTNO = e.DEPTNO)
        Where       d.DEPTNO = 30;
    cSet    c%ROWTYPE;
    --
Begin
    OPEN c;
    LOOP
        FETCH c Into cSet;
        EXIT WHEN c%NOTFOUND;
        i := i + 1;
        tab_a_tbl(i).EMPNO := cSet.EMPNO; 
        tab_a_tbl(i).ENAME := cSet.ENAME;
        tab_a_tbl(i).DEPTNO := cSet.DEPTNO;
        tab_a_tbl(i).DNAME := cSet.DNAME;
        tab_a_tbl(i).LOC := cSet.LOC;
        tab_a_tbl(i).MGR := cSet.MGR;
        --
        Insert Into A_TBL VALUES(tab_a_tbl(i).EMPNO, 
                                 tab_a_tbl(i).ENAME, 
                                 tab_a_tbl(i).DEPTNO, 
                                 tab_a_tbl(i).DNAME,
                                 tab_a_tbl(i).LOC, 
                                 tab_a_tbl(i).MGR);
                                 
        dbms_output.put_line('Row inserted: --> ' || tab_a_tbl(i).EMPNO || Chr(9) || Chr(9) || 
                                                     LPAD(tab_a_tbl(i).ENAME, 8, ' ') || Chr(9) || Chr(9) || 
                                                     tab_a_tbl(i).DEPTNO || Chr(9) || Chr(9) || 
                                                     tab_a_tbl(i).DNAME || Chr(9) || Chr(9) || 
                                                     tab_a_tbl(i).LOC || Chr(9) || Chr(9) || 
                                                     tab_a_tbl(i).MGR);
        --
    END LOOP;
    CLOSE c;
End;
/
/*
anonymous block completed
Row inserted: --> 7499         ALLEN        30      SALES       CHICAGO     7698
Row inserted: --> 7521          WARD        30      SALES       CHICAGO     7698
Row inserted: --> 7654        MARTIN        30      SALES       CHICAGO     7698
Row inserted: --> 7698         BLAKE        30      SALES       CHICAGO     7839
Row inserted: --> 7844        TURNER        30      SALES       CHICAGO     7698
Row inserted: --> 7900         JAMES        30      SALES       CHICAGO     7698
*/

...或使用两个游标（结果相同）

SET SERVEROUTPUT ON
Declare
    TYPE type_a_tbl is Table Of A_TBL%ROWTYPE INDEX BY BINARY_INTEGER;
    i   BINARY_INTEGER := 0;
    tab_a_tbl  type_a_tbl;
    --
    CURSOR depts IS
        Select d.DEPTNO, d.DNAME, d.LOC From DEPT d Where DEPTNO = 30;
    deptSet     depts%ROWTYPE;
        --
        CURSOR c IS
            Select      e.EMPNO, e.ENAME, e.MGR
            From        EMP e
            Where       e.DEPTNO = deptSet.DEPTNO;
        cSet    c%ROWTYPE;
        --
Begin
    OPEN depts;
    LOOP
        FETCH depts Into deptSet;
        EXIT WHEN depts%NOTFOUND;
        --
            OPEN c;
            LOOP
                FETCH c Into cSet;
                EXIT WHEN c%NOTFOUND;
                i := i + 1;
                tab_a_tbl(i).EMPNO := cSet.EMPNO; 
                tab_a_tbl(i).ENAME := cSet.ENAME;
                tab_a_tbl(i).DEPTNO := deptSet.DEPTNO;
                tab_a_tbl(i).DNAME := deptSet.DNAME;
                tab_a_tbl(i).LOC := deptSet.LOC;
                tab_a_tbl(i).MGR := cSet.MGR;
                --
                Insert Into A_TBL VALUES(tab_a_tbl(i).EMPNO, 
                                         tab_a_tbl(i).ENAME, 
                                         tab_a_tbl(i).DEPTNO, 
                                         tab_a_tbl(i).DNAME,
                                         tab_a_tbl(i).LOC, 
                                         tab_a_tbl(i).MGR);
                                         
                dbms_output.put_line('Row inserted: --> ' || tab_a_tbl(i).EMPNO || Chr(9) || Chr(9) || 
                                                             LPAD(tab_a_tbl(i).ENAME, 8, ' ') || Chr(9) || Chr(9) || 
                                                             tab_a_tbl(i).DEPTNO || Chr(9) || Chr(9) || 
                                                             tab_a_tbl(i).DNAME || Chr(9) || Chr(9) || 
                                                             tab_a_tbl(i).LOC || Chr(9) || Chr(9) || 
                                                             tab_a_tbl(i).MGR);
                --
            END LOOP;
            CLOSE c;
        --
    END LOOP;
    CLOSE depts;
End;
/

赞(0）回复(0）举报 2023-01-08

qgzx9mmu2#

不要使用PL/SQL、游标或集合。只使用INSERT INTO ... SELECT ...和JOIN表：

INSERT INTO t (emp_id, dept_id, load_date)
SELECT e.emp_id,
       d.dept_id,
       SYSDATE -- or the column where you stored the date
FROM   emp e
       INNER JOIN dept d
       ON (e.dept_id = d.dept_id) -- or however you want to join the tables.

赞(0）回复(0）举报 2023-01-08

我来回答

oracle 无法将数据加载到嵌套表(集合)中

2条答案

相关问题

热门标签

最新问答