Oracle存储过程中使用参数输出游标类型结合动态SQL

前提

今天在编写业务的存储过程时，需要使用到AND条件的拼接，而根据业务逻辑要求存在多达9种排列组合

以往只有两三种排列组合时，我会选择直接使用了PL/SQL的IF语句分支进行判断，这样更加简洁明了，易于理解

在存储过程中使用动态SQL的好处：

• 提高SQL的复用性，减少重复编写SQL
• 根据业务逻辑进行条件拼接，减少排列组合带来的代码冗余
• 后续需要进行扩展时，更加方便和可维护

问题

使用参数游标，即: [参数名] OUT sys_refcursor

游标最基本的使用方式，PL/SQL如下：

    OPEN myCur FOR
    SELECT * FROM person;

那如果需要在存储过程中使用输出游标结合动态sql拼接，该如何做呢？

解决方法

例子如下：

DECLARE
   p_person_id varchar(255);
   p_person_phone varchar(255);
   SQL_Text varchar2(32760) := 'SELECT * FROM person WHERE 1 = 1 ';
   myCur sys_refcursor;
BEGIN
   IF p_person_id IS NOT NULL THEN 
       SQL_Text := SQL_Text || ' AND person_id = '' ' || p_person_id || ''' ';
   END IF;
   IF p_person_phone IS NOT NULL THEN 
       SQL_Text := SQL_Text || ' AND person_phone = '' ' || p_person_phone || ''' ';
   END IF;
   dbms_output.put_line(SQL_Text);
   OPEN myCur FOR SQL_Text;
END;

注意事项

• 使用参数游标时，OPEN-FOR 可以直接使用来代替 EXECUTE IMMEDIATE
• 动态SQL（SQL_Text）的长度是有限制的，保证自己的动态SQL拼接完毕之后长度不会溢出（网上也有其他方式以支持更长长度，请自行查询）
• WHERE 1 = 1 用于方便拼接条件，并且注意空格的合理应用
• 开发时使用输出语句 dbms_output.put_line(SQL_Text) 打印一下动态SQL，查看语句是否正确