SQL从Oracle 11g数据库中的VARRAY检索对象

问题描述

我在Oracle 11g数据库中建立了一个架构RTRD_W，该数据库包含一个表rtrd_pri，该表具有列NOMNL，该列的类型定义为 VARRAY(10)OF KPS_ADM.NUMBER_T (已定义的对象)作为KPS_ADM架构中的一种类型).我正在尝试运行查询以检索varray中的KPS_ADM.NUMBER_T对象，但是我一直收到SQL语法返回的错误.我构建的功能发布在下面

I have a schema RTRD_W built in an Oracle 11g database that contains a table rtrd_pri that has a column NOMNL which is has a type defined as VARRAY(10) OF KPS_ADM.NUMBER_T (an object defined as a type in the KPS_ADM schema). I am trying to run a query to retrieve the KPS_ADM.NUMBER_T object inside the varray but I keep getting an error returned in my SQL syntax. The function I built is posted below

CREATE or replace function RETRIEVEPRIREF RETURN KPS_ADM.NUMBER_T AS
REF1 KPS_ADM.NUMBER_T;
BEGIN
SELECT KPS_ADM.NUMBER_T INTO REF1 from table(NOMNL) WHERE (SELECT NOMNL FROM RTRD_W.rtrd_pri WHERE (syst_id like '%0516%'));
RETURN REF1;
END RETRIEVEPRIREF;

我知道查询:SELECT NOMNL FROM RTRD_W.rtrd_pri WHERE (syst_id like '%0516%')可以工作，并且确实返回了一个带有单个KPS_ADM.NUMBER_T对象的varray，但是我似乎无法正确地在varray内进行搜索以检索该对象的语法.

I know the query :SELECT NOMNL FROM RTRD_W.rtrd_pri WHERE (syst_id like '%0516%') works and does return a varray with a single KPS_ADM.NUMBER_T object inside it, but I can't seem to get the syntax right for searching inside the varray to retrieve the object.

任何人都可以显示正确的语法来做到这一点吗?

Can anyone show the proper syntax to do this?

推荐答案

-- an object defined as a type in the KPS_ADM schema
CREATE TYPE number_t        AS OBJECT (object_value NUMBER);
CREATE TYPE varray_number_t AS VARRAY(10) OF number_t;

CREATE TABLE rtrd_pri
(
    column_one NUMBER
,   column_two varray_number_t
);

INSERT INTO rtrd_pri VALUES(1, (varray_number_t(number_t(11), number_t(22))));
INSERT INTO rtrd_pri VALUES(2, (varray_number_t(number_t(33), number_t(44))));

SELECT  column_one, OBJECT_VALUE AS column_two
FROM    rtrd_pri, TABLE(rtrd_pri.column_two)
;

COLUMN_ONE  COLUMN_TWO
1           11
1           22
2           33
2           44

这篇关于SQL从Oracle 11g数据库中的VARRAY检索对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！