PLSQL: 动态游标中传参使用USING 关键字传递多值字符串

环境： ORACLE DATABASE 19C 

 

测试程序包代码：

create or replace package cux_test_pkg is

  /* $Header: cux_test_PKG.pls 120.2.10 2013/8/28 10:26:28 OC2 ship $ */
  /**************************************************************************
  REM Copyright (c) 2026 Oracle Corporation. All rights reserved.
  REM ***********************************************************************
  REM File name                : 用于测试程序包
  REM Doc Ref(s)               : <DS020>
  REM Project                  : oracle ebs ERP Project
  REM Description: 1.
  REM              2.
  REM Change History Information
  REM --------------------------
  REM Version  Date         Author           Change Reference / Description
  REM -------  -----------  ---------------  ----------------------------------
  REM 120.2.10   2026.03.28  everyone 
  REM *************************************************************************/

  -------------------------------------------------------------------------------
  -- PROCEDURE:   main
  -- DESCRIPTION: 测试动态游标传递字符串列表，以实现IN()条件 的多个值
  -------------------------------------------------------------------------------
  
  -- Author  : EVERYONE
  -- Created : 2026/3/27 16:06:17
  -- Purpose : 
  
  -- Public type declarations
  --type <TypeName> is <Datatype>;
  
  -- Public constant declarations
  --<ConstantName> constant <Datatype> := <Value>;

  -- Public variable declarations
  --<VariableName> <Datatype>;

  -- Public function and procedure declarations
  --function <FunctionName>(<Parameter> <Datatype>) return <Datatype>;
  PROCEDURE main(errbuf  OUT VARCHAR2,
                 retcode OUT NUMBER,
                 p_user_name IN VARCHAR) ;
end CUX_TEST_pkg;

　　程序包体代码：

create or replace package body kl_zaoru_pkg is
  /* $Header: CUX_TEST_PKG.pls 120.2.10 2013/8/28 10:26:28 OC2 ship $ */
  /**************************************************************************
  REM Copyright (c) 2026 Oracle Corporation. All rights reserved.
  REM ***********************************************************************
  REM File name                : 用于测试程序包
  REM Doc Ref(s)               : <DS020>
  REM Project                  : ORACLE EBS ERP Project
  REM Description: 1.
  REM              2.
  REM Change History Information
  REM --------------------------
  REM Version  Date         Author           Change Reference / Description
  REM -------  -----------  ---------------  ----------------------------------
  REM 120.2.10   2026.03.28  everyone 
  REM *************************************************************************/
  -- Private type declarations
  --type <TypeName> is <Datatype>;
  
  -- Private constant declarations
  --<ConstantName> constant <Datatype> := <Value>;

  -- Private variable declarations
  --<VariableName> <Datatype>;

  -- Function and procedure implementations
  --function <FunctionName>(<Parameter> <Datatype>) return <Datatype> is
  --  <LocalVariable> <Datatype>;
  --begin
  --  <Statement>;
  --  return(<Result>);
  --end;

    
    PROCEDURE main(errbuf  OUT VARCHAR2,
                 retcode OUT NUMBER,
                 p_user_name IN VARCHAR) IS 
    
        
    TYPE g_user_cur IS REF CURSOR;
    cur_user  g_user_cur;
    
    
    v_list VARCHAR2(2000);
    v_sql VARCHAR2(4000);
    v_list_swap VARCHAR2(2000);
    v_user_name VARCHAR2(100);
    v_description VARCHAR2(240);
    BEGIN
      DBMS_OUTPUT.PUT_LINE('P_USER_NAME = '|| P_USER_NAME );
      -- 测试例子：user_name in (23020812,23020814,23020815,23020820,23020870 )
      v_sql := 'select fu.user_name ,fu.description from apps.fnd_user fu where user_name in (:1) ';
      
      v_list :=  chr(39)|| chr(39)|| REPLACE(p_user_name , chr(44), chr(39)||chr(44)||chr(39)  )
       ||chr(39)|| chr(39);
      DBMS_OUTPUT.PUT_LINE('v_list = '|| v_list );
      OPEN cur_user FOR v_sql USING v_list ; -- 打开游标
      LOOP 
      FETCH cur_user INTO v_user_name , v_description ; 
      EXIT WHEN cur_user%NOTFOUND;
      
       DBMS_OUTPUT.PUT_LINE('USER_NAME = '|| V_USER_NAME || ', '|| v_description);
      END LOOP;
       CLOSE cur_user; -- 关闭游标
       DBMS_OUTPUT.PUT_LINE(' 结束：USER_NAME = '|| V_USER_NAME );            
    END main     ;
begin
  -- Initialization
  --<Statement>;
  NULL;
end CUX_TEST_pkg;

　　

测试脚本：

declare
v_list varchar2(200);
begin
cux_test_pkg.main(:errbuf,
:retcode,
:p_user_name) ;
end ;

其中输入参数 ：p_user_name = '123,244,567,222908';

结果：

没有查询结果。

 

结论：

这段Oracle存储过程代码存在一个关键问题：动态SQL中的IN子句无法通过单个绑定参数传递逗号分隔的值列表。
当使用 USING v_list 时，Oracle将其视为单个字符串参数，而不是多个值的列表。这会导致SQL执行失败或返回空结果集。