如何在Oracle函数中返回动态结果集 [英] how to return a dynamic result set in Oracle function

问题描述

我在网上找到了一个字符串标记器查询，并将其打包到下面的函数中，该函数返回动态的标记集。该函数编译成功，但不知何故我得到错误ORA-00933：SQL命令未正确结束。有人可以帮我调试吗？谢谢。

  CREATE OR REPLACE TYPE KEY_VALUE_TYPE is object（k varchar2（4000），v varchar2（4000））; 
 CREATE OR REPLACE TYPE KEY_VALUE_TABLE是key_value_type的表; 
创建或替换函数StrTokenizer 
（字符串IN VARCHAR2，分隔符IN VARCHAR2）
 RETURN key_value_table AS 
 v_ret key_value_table; 
 BEGIN 
 SELECT 
 CAST（
 multiset（
 SELECT 
 LEVEL k，
 SUBSTR（STRING_TO_TOKENIZE，
 DECODE（LEVEL， 1，1，INSTR（STRING_TO_TOKENIZE，DELIMITER，1，LEVEL-1）+1），
 INSTR（STRING_TO_TOKENIZE，DELIMITER，1，LEVEL）
  -  DECODE（LEVEL，1，1，INSTR（STRING_TO_TOKENIZE ，DELIMITER，1，LEVEL-1）+1））v 
 FROM 
（
 SELECT 
'：string'||'：delimiter'AS STRING_TO_TOKENIZE，'：delimiter' AS DELIMITER 
 FROM 
 DUAL 
）
 CONNECT BY INSTR（STRING_TO_ENKENIZE，DELIMITER，1，LEVEL）> 0 
 ORDER BY level ASC）
 As key_value_table）
 INTO 
 v_ret 
 FROM dual; 
 return v_ret; 
 END; 
 
 select * from strtokenizer（'a，b，c'，'，'）
 ORA-00933：SQL命令未正确结束
  

编辑：

1. select * from table（strtokenizer（'a，b，c'，'，'））给出了ORA-30732：表不包含用户可见的列。 当我作为函数所有者登录时，ORA-30732 不见了，但函数不能用传递的值替换
   '：string'||'：delimiter'值。我该如何解决这个问题？当我没有作为它的所有者登录时，如何运行一个函数？
   

解决方案

假设函数编译，请尝试：

  SELECT * 
 FROM TABLE（strtokenizer（'a，b，c'，'，'））; 
  

参考：

• 通过Oracle中的表函数返回行

该功能需要更正 - 使用：

$ pre $ CREATE OR REPLACE FUNCTION StrTokenizer（字符串IN VARCHAR2，
delimiter IN VARCHAR2）
RETURN key_value_table AS v_ret key_value_table
BEGIN
SELECT CAST（MULTISET（SELECT LEVEL k，
SUBSTR（STRING_TO_TOKENIZE，DECODE（LEVEL，1，1，INSTR（STRING_TO_TOKENIZE，DELIMITER，1，LEVEL-1）+1），
INSTR（STRING_TO_TOKENIZE，DELIMITER，1，LEVEL） - DECODE（LEVEL，1，1，INSTR（STRING_TO_TOKENIZE，DELIMITER，1，LEVEL-1）+1））v
FROM（SELECT string || delimiter AS STRING_TO_TOKENIZE ，
分隔符AS DELIMITER
FROM DUAL）
CONNECT BY INSTR（STRING_TO_KENIZE，DELIMITER，1，LEVEL）> 0
ORDER BY level ASC）AS key_value_table）
INTO v_ret
FROM DUAL;

RETURN v_ret;

END;

I found a string tokenizer query on the net and packaged it into the below function, which return the dynamic set of tokens. The function compiles successfully but somehow I get the error "ORA-00933: SQL command not properly ended". Can someone please help me debug this? Thank you.

CREATE OR REPLACE TYPE KEY_VALUE_TYPE is object (k varchar2(4000), v varchar2(4000));
CREATE OR REPLACE TYPE KEY_VALUE_TABLE is table of key_value_type;
CREATE OR REPLACE FUNCTION StrTokenizer
        (string IN VARCHAR2, delimiter IN VARCHAR2)
RETURN key_value_table AS
    v_ret key_value_table;
BEGIN
    SELECT
        CAST( 
            multiset(
            SELECT
                LEVEL  k, 
                SUBSTR(STRING_TO_TOKENIZE, 
                    DECODE(LEVEL, 1, 1, INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL-1)+1), 
                    INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL) 
                        - DECODE( LEVEL, 1, 1, INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL-1)+1)) v
            FROM
                (
                    SELECT
                        ':string'||':delimiter' AS STRING_TO_TOKENIZE , ':delimiter' AS DELIMITER
                    FROM
                        DUAL
                )
            CONNECT BY INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL)>0 
            ORDER BY level ASC) 
        As key_value_table)
    INTO
        v_ret
    FROM dual;
    return v_ret;
END;

select * from strtokenizer('a,b,c',',')
ORA-00933: SQL command not properly ended

Edit:

1. select * from table(strtokenizer('a,b,c',',')) gives "ORA-30732: table contains no user-visible columns".
2. ORA-30732 is gone when I logged on as the function owner, but the function cannot replace ':string'||':delimiter' with the passed-in values. How can I correct this? And how can I run a function when I am not logged on as its owner? Thank you.

解决方案

Assuming the function compiles, try:

SELECT * 
  FROM TABLE(strtokenizer('a,b,c',','));

Reference:

• Returning Rows Through a Table Function in Oracle

The function needs to be corrected - use:

CREATE OR REPLACE FUNCTION StrTokenizer (string IN VARCHAR2, 
                                         delimiter IN VARCHAR2)
RETURN key_value_table AS v_ret key_value_table
BEGIN
  SELECT CAST(MULTISET(SELECT LEVEL k, 
                              SUBSTR(STRING_TO_TOKENIZE, DECODE(LEVEL, 1, 1, INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL-1)+1), 
                              INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL) - DECODE( LEVEL, 1, 1, INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL-1)+1)) v
                         FROM (SELECT string || delimiter AS STRING_TO_TOKENIZE , 
                                      delimiter AS DELIMITER
                                 FROM DUAL)
                   CONNECT BY INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL)>0 
                     ORDER BY level ASC) AS key_value_table)
    INTO v_ret
    FROM DUAL;

  RETURN v_ret;

END;

这篇关于如何在Oracle函数中返回动态结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！