Oracle多态函数(PTF)-从另一个函数传递参数

ubbxdtey 于 2023-04-29 发布在 Oracle

关注(0)|答案(1)|浏览(135)

作为一个基于https://livesql.oracle.com/apex/livesql/file/content_HQK7TYEKEXFZOB62KONZXS3PY.html的简单测试，让我们考虑这个修改后的示例

CREATE OR REPLACE PACKAGE poly_pkg AS  
  FUNCTION describe ( p_tbl IN OUT DBMS_TF.TABLE_T,  
                      p_var NUMBER )  
                      RETURN DBMS_TF.DESCRIBE_T;  
END; 
/
CREATE OR REPLACE PACKAGE BODY poly_pkg AS  
  
  FUNCTION describe ( p_tbl IN OUT DBMS_TF.TABLE_T,  
                      p_var NUMBER )  
                      RETURN DBMS_TF.DESCRIBE_T AS  
  BEGIN  
    -- simply display the value passed in      
    DBMS_TF.TRACE( 'VAR = ' || p_var);
    RETURN NULL;  
  END;  
  
END; 
/

CREATE OR REPLACE FUNCTION poly_func(p_tbl IN TABLE,  
                                     p_var NUMBER )  
  RETURN TABLE PIPELINED  
  ROW POLYMORPHIC USING poly_pkg; 
/  
  
-- for "bla" any table

SELECT * FROM poly_func(bla,33);
/*
will return
VAR = 33
*/

SELECT * FROM poly_func(bla,TO_NUMBER(TO_CHAR(sysdate,'YYYY')));
/*
will return null (but if you put fetch_rows, the value was set in fetch rows but in describe still null!)
VAR = 
*/

我不确定我是否错过了什么。PTF机构中的职能必须是特殊的（哪种？）？也许这打破了多态与动态的概念。
Oracle版本Oracle Database 21 c Enterprise Edition Release 21。0.0.0.0 -生产版本21。3.0.0.0
@edit -带fetch的版本

CREATE OR REPLACE PACKAGE poly_pkg AS  
  FUNCTION describe ( p_tbl IN OUT DBMS_TF.TABLE_T,  
                      p_var NUMBER )  
                      RETURN DBMS_TF.DESCRIBE_T;  
    procedure fetch_rows(p_var NUMBER) ;
END; 
/
CREATE OR REPLACE PACKAGE BODY poly_pkg AS  
  
  FUNCTION describe ( p_tbl IN OUT DBMS_TF.TABLE_T,  
                      p_var NUMBER )  
                      RETURN DBMS_TF.DESCRIBE_T AS  
  BEGIN  
    -- simply display the value passed in      
    DBMS_TF.TRACE( 'VAR = ' || p_var);
    RETURN NULL;  
  END;  
  procedure fetch_rows (p_var NUMBER ) as     
  begin     
     DBMS_TF.TRACE( 'VAR IN FETCH = ' || p_var);     
  end;  
  
END; 
/

然后运行相同的查询

VAR = 333
VAR IN FETCH = 333
VAR = 
VAR IN FETCH = 2023

oracle

来源：https://stackoverflow.com/questions/76072858/oracle-polymorphic-function-ptf-pass-parameters-from-another-function

1条答案

按热度按时间

bhmjp9jg1#

describe函数确定结果集- i的形状。即它的列和它们的数据类型。它只在游标编译/解析期间处理。
这有几个后果：

对同一SQL文本的多次执行只会执行describe一次（假设它仍然在游标缓存中）
使用绑定变量、函数等以确定如果游标无效，行集形状可能会产生意外/不需要的结果

为了缓解第二点：
当常量标量值按原样传递给DESCRIBE函数时，所有其他值都作为NULL传递。
www.example. com
如果要检查（非columns）参数值，请在fetch_rows过程中执行：

CREATE OR REPLACE PACKAGE poly_pkg AS  
  FUNCTION describe ( p_tbl IN OUT DBMS_TF.TABLE_T,  
                      p_var NUMBER )  
                      RETURN DBMS_TF.DESCRIBE_T;  
                      
  PROCEDURE fetch_rows ( p_var NUMBER  );
END; 
/
CREATE OR REPLACE PACKAGE BODY poly_pkg AS  
  
  FUNCTION describe ( p_tbl IN OUT DBMS_TF.TABLE_T,  
                      p_var NUMBER )  
                      RETURN DBMS_TF.DESCRIBE_T AS  
  BEGIN  
    -- simply display the value passed in      
    DBMS_TF.TRACE( 'DESCRIBE VAR = ' || p_var);
    RETURN NULL;  
  END;  
  
  PROCEDURE fetch_rows ( p_var NUMBER  ) AS
  BEGIN
    DBMS_TF.TRACE( 'FETCH VAR = ' || p_var);
  END fetch_rows;
  
END; 
/

CREATE OR REPLACE FUNCTION poly_func(p_tbl IN TABLE,  
                                     p_var NUMBER )  
  RETURN TABLE PIPELINED  
  ROW POLYMORPHIC USING poly_pkg; 
/  
  

SELECT * FROM poly_func(dual,33);
/*
DESCRIBE VAR = 33
FETCH VAR = 33
*/
SELECT * FROM poly_func(dual,33);
/*
FETCH VAR = 33
*/

SELECT * FROM poly_func(dual,TO_NUMBER(TO_CHAR(sysdate,'YYYY')));
/*
DESCRIBE VAR = 
FETCH VAR = 2023
*/

var v number;
exec :v := 2;
SELECT * FROM poly_func(dual,:v) p;
/*
DESCRIBE VAR = 
FETCH VAR = 2
*/

赞(0）回复(0）举报 2023-04-29

我来回答

Oracle多态函数(PTF)-从另一个函数传递参数

1条答案

相关问题

热门标签

最新问答