如何在PL/SQL中从嵌套表中更新列? [英] How can i update a column from a nested table in pl/sql?

问题描述

我正在尝试在表中创建一个可以存储多个这样的值的列.

I'm trying to make a column in a table that can store multiple values like this.

我有一个学生，该学生有一个id_std和一个名为marks的列，该列可以采用多个值，例如2,3,4.我想更新此列表以添加另一个标记来制作2,3,4,5，但我不知道如何.

I have an student that has an id_std and a column named marks that can take several values like 2,3,4. I want to update this list to add another mark to make 2,3,4,5 but I don't know how.

如何在不删除先前值的情况下更新列marks以添加新标记?这是我的代码:

How i can update the column marks to add a new mark without erasing the previous values? Here is my code:

CREATE OR REPLACE TYPE NumberList IS TABLE OF number(5);
/
DROP TABLE test;

CREATE TABLE test ( 
   id_std int
   , marks NumberList )
Nested table marks store as numere_necesare;

insert into test(id_std, marks )
values (1,NumberList(6,7,8));

推荐答案

语法有点晦涩，但是从根本上讲，您需要使用MULTISET运算符来操纵嵌套表.

The syntax is a bit obscure, but basically you need to manipulate the nested table with the MULTISET operator.

update test 
set marks = marks multiset union all numberlist(42) 
where id_std = 1 

这会将现有标记集与一组新标记(在本例中为一组标记)连接在一起.这是一个LiveSQL演示(需要免费的Oracle Technet帐户).

This concatenates the existing set of marks with a new set (in this case a set of one). Here is a LiveSQL demo (free Oracle Technet account required).

此方法的优点是可以轻松一次添加多个值:

The advantage of this approach is that it's easy to add multiple values at once:

update test 
set marks = marks multiset union all numberlist(23, 69) 
where id_std = 1 

嵌套表列在原则上很聪明，但在实践中却很尴尬.在SQL语句中使用它们可能会导致某些难看的SQL.如果要使用它们，则需要熟悉所有的MULTISET运算符. 了解更多信息.

Nested table columns are clever in principle but awkward in practice. Using them in SQL statements can lead to some ugly SQL. If you're going to use them you will need to familiarise yourself with all the MULTISET operators. Find out more.

这篇关于如何在PL/SQL中从嵌套表中更新列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！