时间戳

首页 > 解决方案 > 删除嵌套表中的记录 (plsql)

sql - 删除嵌套表中的记录 (plsql)

问题描述

我想删除记录类型中的列（emp_sal）有 5000+ 的元素，但这给了我一个错误。我不知道我是否将 .DELETE 方法放在正确的区域。代码：

    DECLARE

    TYPE rec IS RECORD (

    emp_id HR.EMPLOYEES.EMPLOYEE_ID%TYPE,
    emp_fname HR.EMPLOYEES.FIRST_NAME%TYPE,
    emp_lname HR.EMPLOYEES.LAST_NAME%TYPE,
    emp_job HR.EMPLOYEES.JOB_ID%TYPE,
    emp_sal HR.EMPLOYEES.SALARY%TYPE );

    TYPE rec_table IS TABLE OF rec;
    rec_list rec_table := rec_table();

BEGIN

  SELECT HR.EMPLOYEES.EMPLOYEE_ID,
         HR.EMPLOYEES.FIRST_NAME,
         HR.EMPLOYEES.LAST_NAME,
         HR.EMPLOYEES.JOB_ID,
         HR.EMPLOYEES.SALARY
         BULK COLLECT INTO rec_list
         FROM HR.EMPLOYEES;

  FOR i IN rec_list.FIRST..rec_list.LAST LOOP

     IF (rec_list(i).emp_sal > 5000) THEN

       rec_list(i).DELETE();

     END IF;

    DBMS_OUTPUT.PUT_LINE('element: '||i||' '||
                         'emp id: '||rec_list(i).emp_id||
                         ' full name: '||rec_list(i).emp_fname||
                         ' '||rec_list(i).emp_lname||
                         ' job: '||rec_list(i).emp_job||
                         ' salary: '||rec_list(i).emp_sal);

  END LOOP;

END;

输出：

ORA-06550: line 28, column 20: PLS-00302: component 'DELETE' must be declared

谢谢！

标签： sqloracleplsqloracle11gplsqldeveloper

解决方案

如果你想从集合中删除一个元素，那么你使用错了

DELETE with no parameters removes all elements from a collection, setting COUNT to 0.
DELETE(n) removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
DELETE(m,n) removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is NULL, DELETE(m,n) does nothing.

所以在你的例子中正确的是

IF (rec_list(i).emp_sal > 5000) THEN

   rec_list.DELETE(i);

END IF;

推荐阅读