Oracle 使用多个绑定变量立即执行，并选择返回单个记录但返回多个列

你好，

我正在构建一个始终接收3个输入值的函数。这些传入的值在组装SQL查询字符串时用作绑定变量，该SQL查询字符串稍后使用EXECUTE IMMEDIATE语句执行。

SQL查询字符串包含一个SELECT INTO语句，该语句返回一个包含3列的单个记录作为其输出。如何使用多个绑定变量将它们提供到SQL查询字符串中，以及如何捕获使用执行立即执行sql查询字符串返回的3列值？

这是我尝试过但不起作用的:-

CREATE OR REPLACE FUNCTION fn_test(p1 IN NUMBER, p2 IN DATE, p3 IN VARCHAR2)
RETURN VARCHAR2 
IS

s_query_stmt     VARCHAR2(1000 CHAR);
r1               VARCHAR2(100 CHAR);
r2               VARCHAR2(100 CHAR);
r3               VARCHAR2(100 CHAR);

BEGIN

s_query_stmt := 'SELECT 
                       c1, c2, c3 
                 INTO  r1, r2, r3 
                 FROM t AS t1
                 LEFT JOIN t AS t2 ON t1.c7 = t2.c8  --Note: A self-join happening here!
                 WHERE t2.c8 = :x --Note: p1 value should go here!
                 AND t2.c9 = :y --Note: p2 value should go here!
                 AND t1.c1 = ( SELECT c_other FROM tb_other WHERE c_other = :x AND b_other = :y )'; --Note: p1 value should go into :x and p2 into :y bind variables again in that order!

EXECUTE IMMEDIATE s_query_stmt INTO r1, r2, r3 USING p1, p2, p1, p2;

RETURN 
       CASE LOWER(p3) 
            WHEN 'g' THEN r1
            WHEN 'u' THEN r3
            ELSE r2
       END;

EXCEPTION 
       WHEN OTHERS THEN 
            RAISE;
END;

SELECT语句 (c1，c2，c3) 返回的所有3列确实是VARCHAR2类型，需要分别转储到r1，r2，r3中。但是，此函数的最终返回结果将取决于CASE语句评估，并且仅返回一个字符串值。

基本上，运行此函数的输出应仅显示1个列值的行。

感谢任何帮助。

“Into” 超出了字符串，例如

SQL> create table t ( c1 int, c2 int, c3 int, c8 int, c9 date );

Table created.

SQL> create table t2 ( cx int, cy int, cz date );

Table created.

SQL>
SQL> insert into t values (1,1,1,1,date '2000-01-01');

1 row created.

SQL> insert into t2 values (1,1,date '2000-01-01');

1 row created.

SQL>
SQL> CREATE OR REPLACE FUNCTION fn_test(p1 IN NUMBER, p2 IN DATE, p3 IN VARCHAR2)
  2  RETURN VARCHAR2
  3  IS
  4
  5  s_query_stmt     VARCHAR2(1000 CHAR);
  6  r1               VARCHAR2(100 CHAR);
  7  r2               VARCHAR2(100 CHAR);
  8  r3               VARCHAR2(100 CHAR);
  9
 10  BEGIN
 11
 12  s_query_stmt := 'SELECT
 13                         c1, c2, c3
 14                   FROM t
 15                   WHERE c8 = :x --Note: p1 value should go here!
 16                   AND c9 = :y --Note: p2 value should go here!
 17                   AND c1 = ( SELECT cx FROM t2 WHERE cy = :x AND cz = :y )'; --Note: p1 value should go into :x and p2 into :y bind variables again in that order!
 18
 19  EXECUTE IMMEDIATE s_query_stmt INTO r1, r2, r3 USING p1, p2, p1, p2;
 20  return 'x';
 21
 22  END;
 23  /

Function created.

SQL>
SQL> select fn_test(1,date '2000-01-01',1) from dual;

FN_TEST(1,DATE'2000-01-01',1)
----------------------------------------------------------------------------------------------------------------------------------
x

你会注意到我删除了

EXCEPTION 
       WHEN OTHERS THEN 
            RAISE;

因为那只是个坏消息 :-)