时间戳

首页 > 解决方案 > plsql这个代码错误有什么问题'INTO列表类型错误'

oracle - plsql这个代码错误有什么问题'INTO列表类型错误'

问题描述

有人可以帮帮我吗？为什么我会收到此错误“INTO 列表类型错误”？

DECLARE 
 TYPE v_dept_table IS TABLE OF DEPARTMENTS.DEPARTMENT_NAME%TYPE INDEX BY PLS_INTEGER;

v_record_dept_table v_dept_table;

v_loop_count NUMBER := 10;
v_dept_no NUMBER := 1;



BEGIN
FOR v_dept_no IN 1..v_loop_count LOOP 
    SELECT DEPARTMENTS.DEPARTMENT_NAME
    INTO v_record_dept_table
    FROM DEPARTMENTS 
    WHERE DEPARTMENT_ID = v_dept_no;

    v_dept_no := v_dept_no + 1;

    INSERT
    INTO v_dept_table 
    VALUES  v_record_dept_table;
END LOOP;    

FOR indx IN NVL (v_dept_table.FIRST, 0) .. NVL (v_dept_table.LAST, -1) 
LOOP
    DBMS_OUTPUT.PUT_LINE(v_dept_table(indx));
END LOOP; 
END;

ORA-06550: line 16, column 14:
PLS-00597: expression 'V_RECORD_DEPT_TABLE' in the INTO list is of wrong type

为什么是错误的类型？我正在使用 Oracle 的 hr 模式

标签： oracleplsql

解决方案

不完全一样；你做错了几件事。我试图修复你的代码（尽可能少的修改）；看看，阅读我写的评论，将其与您的代码进行比较。旁注：我使用 Scott 的DEPT桌子，因为我没有你的DEPARTMENTS.

SQL> set serveroutput on
SQL> DECLARE
  2     TYPE v_dept_table IS TABLE OF dept.dname%TYPE
  3        INDEX BY PLS_INTEGER;
  4
  5     v_record_dept_table  v_dept_table;
  6
  7     v_loop_count         NUMBER := 10;
  8     v_dept_no            NUMBER := 1;
  9  BEGIN
 10     FOR v_dept_no IN 1 .. v_loop_count
 11     LOOP
 12        BEGIN
 13           SELECT dname
 14             INTO v_record_dept_table (v_dept_no) --> you're missing "(v_dept_no)"
 15             FROM dept
 16            WHERE deptno = v_dept_no;
 17        -- Don't manually increment FOR loop variable; Oracle does it itself
 18        -- v_dept_no := v_dept_no + 1;
 19
 20        -- You can't insert into "type"; besides, you've already inserted into V_RECORD_DEPT_TABLE.
 21        -- INSERT INTO v_dept_table VALUES v_record_dept_table;
 22        EXCEPTION
 23           WHEN NO_DATA_FOUND
 24           THEN
 25              NULL;
 26        END;
 27     END LOOP;
 28
 29     -- loop through V_RECORD_DEPT_TABLE (collection), not V_DEPT_TABLE (type). No need for NVL.
 30     FOR indx IN NVL (v_record_dept_table.FIRST, 0) ..
 31                 NVL (v_record_dept_table.LAST, -1)
 32     LOOP
 33        DBMS_OUTPUT.PUT_LINE (v_record_dept_table (indx));
 34     END LOOP;
 35  END;
 36  /
ACCOUNTING

PL/SQL procedure successfully completed.

SQL>

或者，看看这是否有帮助。我使用了内置类型 ( sys.odcivarchar2list) 和BULK COLLECT INTO( 表现更好)。

SQL> DECLARE
  2     v_record_dept_table  SYS.odcivarchar2list;
  3  BEGIN
  4     SELECT dname
  5       BULK COLLECT INTO v_record_dept_table
  6       FROM dept;
  7
  8     FOR indx IN v_record_dept_table.FIRST .. v_record_dept_table.LAST
  9     LOOP
 10        DBMS_OUTPUT.PUT_LINE (v_record_dept_table (indx));
 11     END LOOP;
 12  END;
 13  /
ACCOUNTING
RESEARCH
SALES
OPERATIONS

PL/SQL procedure successfully completed.

SQL>

推荐阅读