函式重調SysRefcursor-有解無憂

函式重調 Sys Refcursor 如何在 select 陳述句中呼叫回傳 sys refcursor 的函式。我已經創建了一個這樣的函式，我想在 select 陳述句中呼叫回傳來自函式的兩個值。所以我在這樣的查詢中使用了，但它回傳游標而不是列值。

Function HCLT_GET_TASK_DATES(i_ownerid IN NUMBER, i_itemid IN NUMBER)
  RETURN SYS_REFCURSOR IS
  o_DATACUR SYS_REFCURSOR;
begin
  open o_DATACUR for
    select nvl(TO_CHAR(min(pref_start), 'DD-MON-YYYY'), '') AS MIN_DATE,
           nvl(TO_CHAR(max(pref_finish), 'DD-MON-YYYY'), '') AS MAX_DATE
      from autoplanallocation
     WHERE project_id = i_ownerid
       AND task_id = i_itemid;
  RETURN o_DATACUR;
END;
/

SELECT HCLT_GET_TASK_DATES(267157, 15334208),
       tv.taskid,
       tv.wbs_code AS wbscode,
       tv.taskcode,
       tv.act_name,
       ltrim(regexp_replace(tv.stageactorlovs, '[^#]*#(\d ?),', ',\1'), ',') as stageactorlovs,
       tv.createdat,
       tv.pushedtoTaskModule,
       tv.OVERALLSTATUS AS overallstatus1,
       tv.ACTIVITY_CODE_ID,
       tv.wbs_code,
       TO_CHAR(tv.pref_st, 'DD-MON-YYYY') AS pref_st,
       TO_CHAR(tv.pref_fn, 'DD-MON-YYYY') AS pref_fn,
       tv.ACTL_EFFORT,
       tv.rollup_effort,
       tv.overAllStatus,
       tv.FIELD5,
       tv.FIELD4,
       tv.activity_code_id
  FROM task_view tv, autoplanallocation al
 WHERE al.project_id = tv.ownerid( )
   and al.task_id = tv.taskid( )
   and tv.ownertype = 'Prj'
   AND tv.ownerid = 267157
   AND (tv.overAllStatus = 'All' OR 'All' = 'All')
   AND (TaskId IN
       ((SELECT xyz
            FROM (SELECT ToItemID xyz
                    FROM ItemTraceability it
                   WHERE it.FromOwnerType = 'Prj'
                     AND it.FromOwnerID = 267157
                     AND it.FromItemType = it.FromItemType
                     AND it.FromChildItemType = 'USTRY'
                     AND it.FromItemID = 15334208
                     AND it.ToOwnerType = 'Prj'
                     AND it.ToOwnerID = 267157
                     AND it.ToItemType = it.ToItemType
                     AND it.ToChildItemType = 'Tsk'
                  UNION ALL
                  SELECT FromItemID
                    FROM ItemTraceability it
                   WHERE it.ToOwnerType = 'Prj'
                     AND it.ToOwnerID = 267157
                     AND it.ToItemType = it.ToItemType
                     AND it.ToChildItemType = 'USTRY'
                     AND it.ToItemID = 15334208
                     AND it.FromOwnerType = 'Prj'
                     AND it.FromOwnerID = 267157
                     AND it.FromItemType = it.FromItemType
                     AND it.FromChildItemType = 'Tsk'))))
 ORDER BY UPPER(wbs_code) ASC;

uj5u.com熱心網友回復：

我認為沒有使用 SQL 或 PL/SQL 代碼決議嵌套游標的本機方式。

在帶有 Oracle JDBC 資料庫驅動程式的 Java 中，您可以：

用于oracle.jdbc.driver.OraclePreparedStatement.executeQuery獲取java.sql.ResultSet
可以轉換為oracle.jdbc.driver.OracleResultSet
然后，您可以遍歷結果集的行，并為每一行oracle.jdbc.driver.OracleResultSet.getCursor()獲取嵌套游標。
- 然后，您可以以與迭代外部游標以從嵌套游標中提取行的方式完全相同的方式迭代該嵌套游標。
- 然后您應該關閉嵌套游標（盡管當包含父游標關閉時它會自動關閉）。
最后，關閉父游標。

如果您需要 SQL 解決方案，則不要回傳游標，而是回傳嵌套表集合資料型別。

或者，對于具有多列的單行，回傳一個物件型別：

CREATE TYPE date_range_obj AS OBJECT(
  start_date DATE,
  end_date   DATE
)
/

CREATE FUNCTION HCLT_GET_TASK_DATES(
  i_ownerid IN autoplanallocation.project_id%TYPE,
  i_itemid  IN autoplanallocation.task_id%TYPE
)
RETURN date_range_obj
IS
  v_range date_range_obj;
begin
  SELECT date_range_obj(MIN(pref_start), MAX(pref_finish))
  INTO   v_range
  FROM   autoplanallocation
  WHERE  project_id = i_ownerid
  AND    task_id = i_itemid;

  RETURN v_range;
END;
/

然后，例如：

SELECT HCLT_GET_TASK_DATES(1,2).start_date,
       HCLT_GET_TASK_DATES(1,2).end_date
FROM   DUAL;

db<>在這里擺弄

uj5u.com熱心網友回復：

如果您能夠更改此設計，那么最好在簡單的連接和聚合中進行（或者可能left join lateral在低基數輸入的情況下）。

dbms_xmlgen但是在 11g 及更高版本中，有一種方法可以使用包處理任意游標的能力，使用純 SQL 來實作所需的結果。下面是代碼：

create table t_lkp (id,dt)
as
select
  trunc(level/4   1)
  , date '2022-01-01'   level
from dual
connect by level < 11

create or replace function f_lkp (
  p_id in int
)
return sys_refcursor
as
  o_res sys_refcursor;
begin
  open o_res for
    select
      min(dt) as dtfrom
      , max(dt) as dtto
    from t_lkp
    where id = p_id;
  
  return o_res;
end;
/

with a as (
  select
    level as i,
    dbms_xmlgen.getxmltype(
      /*ctx doesn't accept sys_refcursor, so we had to create a context*/
      ctx => DBMS_XMLGEN.NEWCONTEXT(f_lkp(level))
    ) as val
  from dual
  connect by level < 6
)
select
  i
  , xmlquery(
    '/ROWSET/ROW/DTFROM/text()'
    passing a.val returning content null on empty
  ) as dtfrom
  , xmlquery(
    '/ROWSET/ROW/DTTO/text()'
    passing a.val returning content null on empty
  ) as dtto
from a

我 | DTFROM | DTTO               
-: | :----------------- | :-----------------
 1 | 2022-01-02 00:00:00 | 2022-01-04 00:00:00
 2 | 2022-01-05 00:00:00 | 2022-01-08 00:00:00
 3 | 2022-01-09 00:00:00 | 2022-01-11 00:00:00
 4 | null                | null               
 5 | null                | null

db<>fiddle here

Please note, that it will open too many cursors in case of large input dataset and parallel processing, which will dramatically consume resourses. So it would be much better to use plain join.

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/448732.html

標籤：甲骨文功能系统引用

上一篇：如何將varchar和日期更改為Oracle時間戳

下一篇：我想將游標中的資料插入到物件表中，但我不能