ORDS REST场景下PL/SQL动态SQL可选参数绑定方案咨询

问题解答 #

不是只能用第一种OR条件的写法，有两种成熟的方案可以实现动态拼接SQL+可选参数绑定，规避第一种写法容易出现执行计划选错的性能问题。 #

方案1：分支判断绑定变量（适合参数数量较少的场景）

你之前的写法问题在于不管参数是否实际出现在动态SQL里，都固定传入所有绑定变量，Oracle要求动态SQL里的占位符数量和USING子句的参数数量必须严格对应，才会报错。我们可以根据参数是否非空，选择对应分支执行OPEN操作即可：

DECLARE
  cur SYS_REFCURSOR;
  sqlString VARCHAR2(32767);
BEGIN
  sqlString := 'SELECT * FROM MYTABLE WHERE 1=1';
  -- 按需拼接查询条件
  IF (:param1 IS NOT NULL) THEN 
    sqlString := sqlString || ' AND COLUMN1 = :param1'; 
  END IF;
  IF (:param2 IS NOT NULL) THEN 
    sqlString := sqlString || ' AND COLUMN2 = :param2'; 
  END IF;

  -- 按实际用到的参数匹配绑定列表
  IF :param1 IS NOT NULL AND :param2 IS NOT NULL THEN
    OPEN cur FOR sqlString USING :param1, :param2;
  ELSIF :param1 IS NOT NULL THEN
    OPEN cur FOR sqlString USING :param1;
  ELSIF :param2 IS NOT NULL THEN
    OPEN cur FOR sqlString USING :param2;
  ELSE
    -- 无参数直接执行
    OPEN cur FOR sqlString;
  END IF;

  :resultSetOut := cur;
END;

这种方案逻辑简单易懂，不需要引入额外工具包，参数在3个以内时维护成本极低。 #

方案2：使用DBMS_SQL包实现完全动态绑定（适合参数数量较多的场景）

如果可选参数超过3个，写大量分支判断会导致代码冗余，我们可以用Oracle内置的DBMS_SQL包实现动态解析SQL、动态绑定变量，最后转为REF_CURSOR返回即可：

DECLARE
  cur SYS_REFCURSOR;
  sqlString VARCHAR2(32767);
  v_cursor_id NUMBER;
  v_bind_count NUMBER := 0;
BEGIN
  sqlString := 'SELECT * FROM MYTABLE WHERE 1=1';
  
  -- 拼接SQL同时记录需要绑定的参数
  IF (:param1 IS NOT NULL) THEN
    sqlString := sqlString || ' AND COLUMN1 = :p' || (v_bind_count + 1);
    v_bind_count := v_bind_count + 1;
  END IF;
  IF (:param2 IS NOT NULL) THEN
    sqlString := sqlString || ' AND COLUMN2 = :p' || (v_bind_count + 1);
    v_bind_count := v_bind_count + 1;
  END IF;
  -- 更多参数按同样规则拼接即可

  -- 用DBMS_SQL动态绑定
  v_cursor_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cursor_id, sqlString, DBMS_SQL.NATIVE);
  
  v_bind_count := 0;
  IF (:param1 IS NOT NULL) THEN
    v_bind_count := v_bind_count + 1;
    DBMS_SQL.BIND_VARIABLE(v_cursor_id, ':p'||v_bind_count, :param1);
  END IF;
  IF (:param2 IS NOT NULL) THEN
    v_bind_count := v_bind_count + 1;
    DBMS_SQL.BIND_VARIABLE(v_cursor_id, ':p'||v_bind_count, :param2);
  END IF;
  -- 更多参数按同样规则绑定即可

  -- 执行并转为REF_CURSOR
  DBMS_SQL.EXECUTE(v_cursor_id);
  cur := DBMS_SQL.TO_REFCURSOR(v_cursor_id);
  :resultSetOut := cur;
END;

这种方案可以无缝扩展支持任意数量的可选参数，不需要新增分支判断，适合参数多的复杂查询场景。 #

两种方案的优势对比原来的写法

原来的(:param IS NULL OR column = :param)写法很容易因为Oracle的绑定变量窥探问题生成错误的执行计划，大表查询时性能差距可达几十上百倍。而动态拼接SQL的写法每个查询条件组合会生成独立的执行计划，匹配度更高，性能更稳定。

内容的提问来源于stack exchange，提问作者David Min