选择具有对象集合的Oracle对象而不进行批量收集 [英] Selecting Oracle object with collection of objects without bulk collect

问题描述

是否可以从jdbc的常规选择中选择/填充Oracle集合(" IS TABLE OF ")，即在中没有用于声明变量的过程用于批量收集或匿名块Oracle12c ?

Is there a way to select/fill an Oracle Collection ("IS TABLE OF") in a normal select from jdbc i.e. no procedure with declared variable for bulk collect or anonymous block in Oracle12c?

问题在于选择带有对象集合的对象，因为我找不到填充该集合的方法，因此，它将每个对象放入一个单独的集合中，而不是将它们收集到一个集合中(即，返回单行查询多于一行").

The problems is selecting objects with collection of objects since I can't find a way to fill the collection and therefore it puts every object in a separate collection instead of collecting them in one (i.e., "a single-row query returned more than one row").

长篇故事: 大多数out/services/api都具有动态搜索参数，然后将它们转换为SQL语句.

Long Story: Most of out services/apis have dynamic search parameters and then they are translated to SQL statements.

一个Java代码会解析有限的搜索/位置"，对它们进行清理和参数化，然后从oracle中选择一个对象，因此在大多数情况下，它的执行速度比oracle的硬解析要快得多，但是我找不到解决方案用对象列表填充对象.

A java code does parse the limited "search/where", sanitize and parameterize them then select an objects from oracle, so in most of the time it execute much faster than oracle's hard parse, however i can't find a solution for filling objects with list of objects.

当前的解决方法是调用过程，并让它们在声明的对象中进行批量收集，然后返回它们， 但是，由于搜索"子句的动态性质，它们大多数都执行立即执行，这对于数据库的CPU而言相当繁重.

The current work around is calling procedures and leaving them to do bulk collect in declared objects then returning them, however because of the dynamic nature of the "search" clause most of them do EXECUTE IMMEDIATE which is quite heavy for CPU of the database.

其他解决方案，例如每行多个查询以获取其列表，或者左连接并在单个ResultSet中获取所有内容，只会成倍地增加时间，因为获取通常用于大量的列，行和嵌套列表: <

Other solutions such as multiple queries per every row to fetch it's list or left join and fetch everything in single ResultSet just increase the time exponentially, since the fetches are for usually for huge number of columns, rows and nested lists :<

CREATE OR REPLACE TYPE SUB_TYPE_OBJ Force AS OBJECT
(
  SVAR1 VARCHAR2(100 CHAR);
  SVAR2 DATE,
  SVAR3 VARCHAR2(100 CHAR)
);

CREATE OR REPLACE TYPE SUB_TYPE_LST IS TABLE OF SUB_TYPE_OBJ;

CREATE OR REPLACE TYPE MAIN_TYPE_OBJ Force AS OBJECT
(
  MVAR1 VARCHAR2(100 CHAR),
  MVAR2 VARCHAR2(100 CHAR),
  MVAR3 SUB_TYPE_OBJ,
  MVAR4 SUB_TYPE_LST
);

SELECT MAIN_TYPE_OBJ (
    tab1.val1, 
    tab1.val2,
    (SELECT SUB_TYPE_OBJ( table2.val1, table2.val2, table2.val3) obj FROM table2 WHERE tab1.val1 = tab2.val1),
    (SUB_TYPE_LST ( (SELECT SUB_TYPE_OBJ( table3.val1, table3.val2, table3.val3) obj FROM table3 DYNAMIC_WHERE_WITH_LIMITED_FUNCTIONALITY)))
    /* if it return more than one row everything breaks :( */
), CNT
FROM (
SELECT table1.*, COUNT(*) OVER(table1.val1) AS CNT FROM table1
WHERE DYNAMIC_WHERE_WITH_LIMITED_FUNCTIONALITY
ORDER BY val1 ASC OFFSET ? ROWS FETCH NEXT ? ROWS ONLY) tab1;

如果table3返回多行，我们将得到单行查询返回多行" 因为我只是创建了一堆列表，而不是包含所有对象的列表，但是我不知道该怎么做；

if table3 returns more than one row we get the "a single-row query returned more than one row" because I'm just creating a bunch of lists instead of one containing all objects, but I cant figure out how to do it;

任何想法都值得赞赏，但请暂时排除 DBMS_SQL 或 NoSQL :).

Any ideas are appreciated, but please exclude DBMS_SQL or NoSQL for now :).

推荐答案

您在这里有几个选择.您可以将查询作为显式游标打开，然后将FETCH ... BULK COLLECT收集到适当的集合中；您可以使用EXECUTE IMMEDIATE ... BULK COLLECT INTO;或者，就像您说不想听到的那样，您可以使用DBMS_SQL.

You have several options here. You can open your query as an explicit cursor and then FETCH...BULK COLLECT INTO an appropriate collection; you can use EXECUTE IMMEDIATE...BULK COLLECT INTO; or, as you say you don't want to hear, you can use DBMS_SQL.

要立即使用EXECUTE ... BULK COLLECT，您可以使用

To use EXECUTE IMMEDIATE...BULK COLLECT you'd use something like

CREATE TABLE DATA_TABLE(FIELD1         NUMBER,
                        FIELD2         VARCHAR2(100));

INSERT INTO DATA_TABLE (FIELD1, FIELD2)
  SELECT 1, 'ONE' FROM DUAL UNION ALL
  SELECT 1, 'TWO' FROM DUAL UNION ALL
  SELECT 2, 'THREE' FROM DUAL UNION ALL
  SELECT 2, 'FOUR' FROM DUAL UNION ALL
  SELECT 3, 'LAST' FROM DUAL;

DECLARE
  TYPE typeCol IS TABLE OF DATA_TABLE%ROWTYPE;
  colVals        typeCol;
  strField_name  VARCHAR2(30) := 'FIELD1';
  nField_val     NUMBER := 2;

  strQuery       VARCHAR2(4000);
BEGIN
  strQuery := 'SELECT * FROM DATA_TABLE WHERE ' || strField_name || '=' || nField_val;

  DBMS_OUTPUT.PUT_LINE(strQuery);

  EXECUTE IMMEDIATE strQuery BULK COLLECT INTO colVals;

  FOR i IN colVals.FIRST..colVals.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(i || ': FIELD1=' || colVals(i).FIELD1 || '  FIELD2=' || colVals(i).FIELD2);
  END LOOP;
END;
/

此处是dbfiddle

此处的文档(来自10.1-更好地写为-高于最新版本的IMO)

这篇关于选择具有对象集合的Oracle对象而不进行批量收集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！