从Varray Oracle中删除元素

问题描述

我将Varray创建为:

I have created Varray as :

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

然后将创建的表另存为:

Then Created table as :

创建表tbl(一个号码，b VARCHAR2(30)，c mytype)；/

插入的值为:

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

如何仅删除元素"ttt"?谢谢!!

How I can delete only the element 'ttt'?? Thanks !!

推荐答案

Oracle文档指出，这不可能直接通过SQL进行:

The Oracle documentation states that this is not possible directly via SQL:

您可以对嵌套表和VARRAY进行原子更改.

You can make atomical changes to nested tables and VARRAYs.

注意:虽然嵌套表也可以分段方式更改，但varrays不能.

Note: While nested tables can also be changed in a piecewise fashions, varrays cannot.

示例5-25显示了如何使用PL/SQL语句操纵SQL varray对象类型.在此示例中，varray在PL/SQL变量和SQL表之间传输.您可以插入包含集合的表行，更新一行以替换其集合，然后将集合选择为PL/SQL变量.

Example 5-25 shows how you can manipulate SQL varray object types with PL/SQL statements. In this example, varrays are transferred between PL/SQL variables and SQL tables. You can insert table rows containing collections, update a row to replace its collection, and select collections into PL/SQL variables.

但是，您不能直接使用SQL更新或删除单个varray元素.您必须从表中选择varray，在PL/SQL中对其进行更改，然后更新表以包括新的varray.您也可以对嵌套表执行此操作，但是嵌套表可以选择进行分段更新和删除.

However, you cannot update or delete individual varray elements directly with SQL; you have to select the varray from the table, change it in PL/SQL, then update the table to include the new varray. You can also do this with nested tables, but nested tables have the option of doing piecewise updates and deletes.

此外，您还不能只删除 VARRAY 中的随机给定元素，因为它们是密集数组(它们之间没有间隙).尽管在您的示例中可以使用 TRIM 函数从 VARRAY 中删除最后一个条目，从而删除 ttt ，但是您可以只需继续操作，例如，在没有一些聪明的PL/SQL的情况下删除 rr 即可创建一个新的 VARRAY ，该条目中没有该条目.这是聪明的PL/SQL:

Furthermore, you can also not just delete a random given element within a VARRAY as they are dense arrays (no gaps within them). Although it is possible in your example to just remove the very last entry from the VARRAY using the TRIM function, therefore removing ttt, you can't just go ahead and for example delete rr without some clever PL/SQL to create a new VARRAY that has that entry not within it. Here is the clever 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;
/

所有这些都会导致以下结果:

Which results in all of this:

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')

这篇关于从Varray Oracle中删除元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！