甲骨文:插入到表类型中

Question

CREATE OR REPLACE TYPE tabela is TABLE OF number;

我创建了这个表，现在我想插入值，以便我可以在函数中返回它。如何插入到这种表中？

Answer 1

SQL Fiddle

Oracle 11g R2 架构设置：

CREATE OR REPLACE TYPE tabela is TABLE OF number;
CREATE TABLE test ( val ) AS
          SELECT 1 FROM DUAL
UNION ALL SELECT 2 FROM DUAL
UNION ALL SELECT 4 FROM DUAL
UNION ALL SELECT 6 FROM DUAL;

查询 1：

-- In SQL
SELECT tabela( 1, 2, 4, 6 ) FROM DUAL

结果：

| TABELA(1,2,4,6) |
|-----------------|
|         1,2,4,6 |

查询 2：

-- In SQL, getting value from a table
SELECT CAST( COLLECT( val ) AS tabela ) AS vals FROM test

结果：

|    VALS |
|---------|
| 1,2,4,6 |

查询 3：

-- In PL/SQL
DECLARE
  t tabela := tabela();
BEGIN
  t.EXTEND(4);
  t(1) := 1;
  t(2) := 2;
  t(3) := 4;
  t(4) := 6;
  -- Do stuff with t.
END;

查询 4：

-- In PL/SQL, getting values from a table:
DECLARE
  t tabela;
BEGIN
  SELECT val
  BULK COLLECT INTO t
  FROM test;
  -- Do stuff with t.
END;