从Varray Oracle中删除元素

Question

我将Varray创建为：

创建类型mytype是VARCHAR2(50)的VARRAY (4)；/

然后创建如下表：

创建表tbl( a数字，b VARCHAR2(30)，c mytype)；/

插入的值如下：

插入tbl(a，b，c)值(1，'Eng'，mytype('qq'，'rr'，'yy'，‘ttt’)；

我怎么能只删除元素'ttt'?？谢谢!！

Answer 1

Oracle文档指出，这不可能直接通过SQL实现：

5.3.2.3在VARRAY和嵌套表上执行原子变化 您可以对嵌套表和VARRAY进行原子性更改。 注意:虽然嵌套表也可以以分段的方式进行更改，但是varray不能。 示例5-25展示了如何使用PL/SQL语句操作SQL对象类型。在本例中，varray在PL/SQL变量和SQL表之间传输。您可以插入包含集合的表行，更新一行以替换其集合，并在PL/SQL变量中选择集合。 但是，不能使用SQL直接更新或删除单个varray元素；您必须从表中选择varray，在PL/SQL中对其进行更改，然后更新表以包含新的varray。您也可以使用嵌套表来完成此操作，但是嵌套表可以选择执行分段更新和删除。

此外，您还不能只删除VARRAY中的随机给定元素，因为它们是密集数组(在它们中没有空白)。虽然在您的示例中可以使用VARRAY函数从TRIM中删除最后一个条目，因此可以删除ttt，但是不能继续下去，例如，删除rr而不使用一些聪明的PL/SQL来创建一个包含该条目的新VARRAY。下面是聪明的PL/SQL：

CREATE OR REPLACE FUNCTION delete_entry(p_record IN mytype, p_val IN VARCHAR2)
RETURN mytype
IS
   v_ret  mytype := mytype();
BEGIN
   FOR n IN p_record.FIRST..p_record.LAST LOOP
      IF p_record(n) != p_val THEN
        v_ret.EXTEND;
        v_ret(v_ret.LAST) := p_record(n);
      END IF;
   END LOOP;
   RETURN v_ret;
END;
/

这导致了所有这一切：

SQL> CREATE TYPE mytype IS VARRAY(4) OF VARCHAR2(50);
  2  /

Type created.

SQL> CREATE TABLE tbl( a NUMBER, b VARCHAR2(30), c mytype);

Table created.

SQL> INSERT INTO tbl(a, b, c)
  VALUES (1,'Eng', mytype('qq','rr', 'yy', 'ttt'));
  2
1 row created.

SQL> select * from tbl;

     A B
---------- ------------------------------
C
--------------------------------------------------------------------------------
     1 Eng
MYTYPE('qq', 'rr', 'yy', 'ttt')


SQL> CREATE OR REPLACE FUNCTION delete_entry(p_record IN mytype, p_val IN VARCHAR2)
RETURN mytype
IS
   v_ret  mytype := mytype();
BEGIN
   FOR n IN p_record.FIRST..p_record.LAST LOOP
      IF p_record(n) != p_val THEN
        v_ret.EXTEND;
        v_ret(v_ret.LAST) := p_record(n);
      END IF;
   END LOOP;
   RETURN v_ret;
END;
/
  2    3    4    5    6    7    8    9   10   11   12   13   14
Function created.

SQL> UPDATE tbl SET c = delete_entry(c, 'ttt');

1 row updated.

SQL> select * from tbl;

     A B
---------- ------------------------------
C
--------------------------------------------------------------------------------
     1 Eng
MYTYPE('qq', 'rr', 'yy')