PLSql返回值 [英] PLSql return values

问题描述

我在这里再次使用一些PLSql.

Here I go again with some PLSql..

我想知道，是否有任何办法可以像选择一样使用以下函数，而不必将其转换为函数或过程(这样我就可以从包含它的脚本中查看代码).

I want to know, if there's any way I can use the following function like a select without having to turn it into a function or procedure (so I can see the code from the script where it is contained).

代码如下:

DECLARE
    outpt  VARCHAR2(1000) := ''; 
    flow_rI  VARCHAR2(50); 
    CURSOR flow_r IS
      select flow_run_id
        from table
        where CREATED_DATE < sysdate - 32
        and rownum < 10
        order by 1 desc;
BEGIN 
    OPEN flow_r;
    LOOP 
        FETCH flow_r INTO flow_rI; 
        EXIT WHEN flow_r%notfound; 
        BEGIN 
            outpt := outpt ||  ',' || flow_rI;
        EXCEPTION 
            WHEN no_data_found THEN 
              dbms_output.Put_line(outpt); 
        END; 
    END LOOP;
    dbms_output.Put_line(outpt); 
    outpt := '';
    CLOSE flow_r;
END;

这个想法很简单，我只想从我的table中获取一系列代码，但是将结果格式化为"1234,2434,424,45,767,43"之类的东西，而不是查询的表结果.稍后将在代码中将其用于各种目的，包括可以简单地执行in ([variable with that string])的另一个查询.

The idea is simple, I just want to get a series of codes from my table but having the results formatted like "1234,2434,424,45,767,43" sort of thing, rather than the table result from a query. It will be used later in the code for various purposes including another queries where I could simply do an in ([variable with that string]).

问题是，使用dbms_output.Put_line(outpt);时，我无法从应用程序层访问它，而且看来在plsql中，如果不将return转换为函数，就无法使用它.

Thing is that using the dbms_output.Put_line(outpt);, I can't access it from my application layer and seems that in plsql I cannot use the return without turning it into a function.

有人会有主意吗?只要我可以在脚本中包含整个代码，就不必像这样的PlSql.

Would anyone have an idea? It doesn't need to be a PlSql like that as long as I can have the whole code in the script.

谢谢！

f.

推荐答案

为什么不只使用sql:

Why not use just sql:

   SELECT MAX(ltrim(sys_connect_by_path(flow_run_id, ','), ','))
   FROM
   (
    select flow_run_id, rownum rn
    from table
    where CREATED_DATE < sysdate - 32
    and rownum < 10
    order by 1 desc
   )
   START WITH rn = 1
   CONNECT BY PRIOR rn = rn - 1

这篇关于PLSql返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！