如何将不同游标传入同一个Cursor FOR LOOP执行相同业务逻辑？

现有一个通过游标循环执行业务逻辑的存储过程：

PROCEDURE myproc() AS

  CURSOR mycur IS
    SELECT * FROM mytable;

BEGIN

  FOR c IN mycur LOOP

    ...business logic here...
    ...many lines of code...

  END LOOP;

END myproc;

希望创建不同的存储过程，针对不同数据集执行相同的业务逻辑：

• myproc_adopters() 对应数据集：SELECT * FROM mytable WHERE customer_type='Adopters'
• myproc_others() 对应数据集：SELECT * FROM mytable WHERE customer_type!='Adopters'

尝试编写主存储过程mainproc()封装游标循环和业务逻辑，让其他存储过程传入不同游标，但发现Cursor FOR LOOP不接受作为参数传入的游标变量：

PROCEDURE myproc_adopters() AS
  CURSOR mycur IS
    SELECT * FROM mytable WHERE customer_type='Adopters';
BEGIN
  mainproc(mycur);
END myproc_adopters;

PROCEDURE myproc_others() AS
  CURSOR mycur IS
    SELECT * FROM mytable WHERE customer_type!='Adopters';
BEGIN
  mainproc(mycur);
END myproc_others;

PROCEDURE mainproc(mycur IN SYS_REFCURSOR) AS
BEGIN
  FOR c IN mycur LOOP -- 此处不接受游标变量
  ...
  END LOOP;
END mainproc;

请问如何实现向同一个Cursor FOR LOOP传递不同的游标？

方法一：使用显式FETCH循环替代Cursor FOR LOOP #

Oracle的Cursor FOR LOOP仅支持静态游标或强类型游标变量，不兼容SYS_REFCURSOR这类弱类型游标。可以改用显式FETCH循环处理SYS_REFCURSOR：

首先定义与mytable结构匹配的记录类型，再在主存储过程中循环读取游标数据：

-- 定义匹配mytable结构的记录类型
TYPE mytable_rec IS RECORD (
  col1 mytable.col1%TYPE,
  col2 mytable.col2%TYPE,
  -- 依次列出mytable所有字段
  customer_type mytable.customer_type%TYPE
);

PROCEDURE mainproc(p_cursor IN SYS_REFCURSOR) AS
  v_rec mytable_rec;
BEGIN
  LOOP
    FETCH p_cursor INTO v_rec;
    EXIT WHEN p_cursor%NOTFOUND;
    
    -- 原业务逻辑保持不变，用v_rec替代原FOR循环中的c
    ...business logic here...
    ...many lines of code...
    
  END LOOP;
  CLOSE p_cursor;
END mainproc;

-- 调用方需将静态游标转换为SYS_REFCURSOR传入
PROCEDURE myproc_adopters() AS
  v_cursor SYS_REFCURSOR;
BEGIN
  OPEN v_cursor FOR SELECT * FROM mytable WHERE customer_type='Adopters';
  mainproc(v_cursor);
END myproc_adopters;

PROCEDURE myproc_others() AS
  v_cursor SYS_REFCURSOR;
BEGIN
  OPEN v_cursor FOR SELECT * FROM mytable WHERE customer_type!='Adopters';
  mainproc(v_cursor);
END myproc_others;

方法二：传递过滤参数而非游标（更简洁） #

如果不同数据集仅过滤条件不同，无需传递游标，直接给主存储过程传入过滤参数，内部动态生成游标即可：

PROCEDURE mainproc(p_customer_type IN VARCHAR2 DEFAULT NULL) AS
  CURSOR mycur IS
    SELECT * FROM mytable
    WHERE (p_customer_type IS NULL OR customer_type = p_customer_type)
      OR (p_customer_type = 'OTHERS' AND customer_type != 'Adopters');
BEGIN
  FOR c IN mycur LOOP
    -- 原业务逻辑完全保留
    ...business logic here...
    ...many lines of code...
  END LOOP;
END mainproc;

-- 调用方只需传入对应参数
PROCEDURE myproc_adopters() AS
BEGIN
  mainproc('Adopters');
END myproc_adopters;

PROCEDURE myproc_others() AS
BEGIN
  mainproc('OTHERS');
END myproc_others;

这种方式规避了游标传递的限制，代码更简洁易维护，适合过滤规则简单的场景。

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