CREATE OR REPLACE FUNCTION F_TEST RETURN VARCHAR2 AS

  V_SQL          VARCHAR2(4000);
  V_CONDITION    VARCHAR2(1000);
  V_PARAM        VARCHAR2(2000);
  CURSOR_NAME    NUMBER;
  ROWS_PROCESSED NUMBER;
  AREA_CODE      NUMBER;
  RESULT         VARCHAR2(4000);

BEGIN
  V_SQL       := 'SELECT t.area_code FROM S_AREA_INFO t where 1=1 ';
  V_CONDITION := ' and area_code like :a';
  V_PARAM     := '52%';
  CURSOR_NAME := DBMS_SQL.OPEN_CURSOR; --打开游标
  DBMS_SQL.PARSE(CURSOR_NAME, V_SQL || V_CONDITION, DBMS_SQL.NATIVE); --解析动态SQL语句
  DBMS_SQL.BIND_VARIABLE(CURSOR_NAME, ':a', V_PARAM); --绑定输入参数
  DBMS_SQL.DEFINE_COLUMN(CURSOR_NAME, 1, AREA_CODE); --定义列
  ROWS_PROCESSED := DBMS_SQL.EXECUTE(CURSOR_NAME); --执行动态SQL语句
  LOOP
    EXIT WHEN DBMS_SQL.FETCH_ROWS(CURSOR_NAME) <= 0; --fetch_rows在结果集中移动游标，如果未抵达末尾，返回1。
  
    DBMS_SQL.COLUMN_VALUE(CURSOR_NAME, 1, AREA_CODE); --将当前行的查询结果写入上面定义的列中
    RESULT := RESULT || AREA_CODE || ',';
  
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME); --关闭游标

  RETURN(RESULT);
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(CURSOR_NAME) THEN
      DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME);
    END IF;
  RETURN 'error';
END F_TEST;

------解决方案--------------------
用动态sql，然后用||拼接起来
定义一个变量v_sql，然后v_sql:='一堆sql语句'||wherestr||'一堆sql语句'
拼完sql以后再写一行 execute immediate v_sql

------解决方案--------------------
为什么不用open eng for呢？~ 也可以实现呀~
...
is
eng sys_refcursor;
...
begin
  result:='';
 wherestr:='where t.process_status!3';
 case  
   when(adt_type=1) then
   open eng for 'select t.prod_reg_no from t_exchange t '||wherestr;
   loop
     fetch eng into...;
    exit when eng%notfound;
    end loop;
  end case;
...

------解决方案--------------------
探讨


条件变量的值是视情况而变动的，所以我要这样设置啊