主页 / 问题 / 1269399
Accepted
Alex Konkin
Asked:2022-04-13 03:05:30 +0000 UTC2022-04-13 03:05:30 +0000 UTC

创建 piplined 函数时出错：PLS-00321：表达式 <数据类型> 不适合作为赋值语句的左侧

  • 772

我有一个包含以下示例数据的表：

with t as (
select 123456 DRAW_KEY, 1 WINCAT_NUM,0 WIN_AMOUNT,0 WIN_COUNT
from dual
union all
select 123456 DRAW_KEY, 2 WINCAT_NUM,0 WIN_AMOUNT,0 WIN_COUNT
from dual
union all
select 123456 DRAW_KEY, 3 WINCAT_NUM,0 WIN_AMOUNT,0 WIN_COUNT
from dual
union all
select 123456 DRAW_KEY, 4 WINCAT_NUM,30000 WIN_AMOUNT,12 WIN_COUNT
from dual
union all
select 123456 DRAW_KEY, 5 WINCAT_NUM,6000 WIN_AMOUNT,73 WIN_COUNT
from dual
) select * from t

基于这些数据，我创建了一个piplined函数：

CREATE TYPE l_result AS OBJECT (
  DRAW_KEY number,
  WINCAT_NUM  number,
  WIN_AMOUNT number,
  WIN_COUNT number);
/
CREATE TYPE table_pip IS TABLE OF l_result;

CREATE FUNCTION MyFunction1(v_draw_key in number) RETURN table_pip Pipelined IS
  v_query clob;
  v_cols clob;
BEGIN
  select listagg(wincat_num ,', ') within group (order by wincat_num)
  into v_cols
  from customers1
  where draw_key = 123456; --v_draw_key!
  v_query := 'SELECT * FROM (
    select draw_key,wincat_num,win_amount,win_count
    from customers1
    where draw_key = 123456) PIVOT (
      sum(win_count) as win_count,sum(win_amount) as win_amount
      FOR wincat_num IN ('||v_cols||'))';
  EXECUTE IMMEDIATE v_query into l_result;
  pipe row(l_result);
END;

但它给出了错误：

PLS-00321：表达式“L_RESULT”不适合作为赋值语句的左侧

和

PLS-00306：调用“L_RESULT”时参数的数量或类型错误。

我写错了什么，请告诉我？

функции

1 个回答

  1. Best Answer

    原因： pipe row(l_result);，这里期望的是对象的实体，而不是对象类型本身。

    使用问题中的数据查看它如何与可重现的示例一起使用：

    create type resultRow as object (
    draw_key number, wincat_num  number, win_amount number, win_count number)
/
create type resultRows is table of resultRow
/
create or replace function pipefunc (draw_key in number) return resultRows pipelined is
begin
    for r in (
        select * 
        from customers1 
        where draw_key = pipefunc.draw_key
    ) loop
        pipe row (resultRow (r.draw_key, r.wincat_num, r.win_amount, r.win_count));
    end loop;
    return;
end;
/

    调用和结果：

    select * from pipefunc (123456)
/

  DRAW_KEY WINCAT_NUM WIN_AMOUNT  WIN_COUNT
---------- ---------- ---------- ----------
    123456          1          0          0
    123456          2          0          0
    123456          3          0          0
    123456          4      30000         12
    123456          5       6000         73

    PS
    此答案未涵盖函数中的动态请求，因为它与问题无关。请参阅使用动态列计数转置的相关主题。

    • 3

相关问题