Oracle 创建存储过程以返回列表-解网

问：

我有一个任务表，其中有 3 列（id、TimeStamp、Is_procssed）。我在 oracle 中创建了一个存储过程，如下所示。

    CREATE OR REPLACE PROCEDURE AUDITING.fetchTasksByTimestamp(
    p_timestamp IN TIMESTAMP,
    p_limit IN NUMBER,
    updated_ids OUT SYS_REFCURSOR
) IS
    TYPE NumberTableType IS TABLE OF tasks.id%TYPE;
    l_updated_ids NumberTableType;
BEGIN
    -- Select the IDs of the records to be updated in a thread-safe manner
    SELECT id BULK COLLECT INTO l_updated_ids
    FROM tasks
    WHERE is_processed = 0
    AND timestamp_column <= p_timestamp
    FOR UPDATE SKIP LOCKED;
    
    -- Limit the number of records using p_limit
--    IF l_updated_ids.COUNT > p_limit THEN
--        l_updated_ids.DELETE(p_limit + 1, l_updated_ids.COUNT);
--    END IF;
--
--    -- If no records are found, return an empty cursor
--    IF l_updated_ids.COUNT = 0 THEN
--        OPEN updated_ids FOR SELECT * FROM DUAL WHERE 1=0; -- Empty result set
--        RETURN;
--    END IF;

    -- Update the tasks that were locked
   
    for indx in l_updated_ids loop
    UPDATE tasks SET is_processed = 1 WHERE id = l_updated_ids(indx);
    END LOOP;

--     Open the updated_ids cursor for the collected IDs
--    OPEN updated_ids FOR
--    SELECT * FROM TABLE(l_updated_ids);

    COMMIT;
END fetchTasksByTimestamp;

编译时出现错误：“29/17 PLS-00456：项目'L_UPDATED_IDS'不是光标”。

我的要求是在多线程环境中从java spring boot调用上述存储过程。由于部署了多个实例，并且有一个 cron 将调用 SP。我希望每个线程都获得不同的记录列表。因此，我也使用了skip locked。

但是上面的 SP 返回已更新的行列表。我尝试了各种方法，但无法解决。

如果有人能帮助我解决问题，那将会很有帮助。

SQL Oracle 存储过程 PLSQL

@BogdanDincescu所以我有 n 个 java cron 实例在运行，它将根据时间戳和限制从任务表中获取唯一的行。每个线程都应该有唯一的行。因此，我采用了上述方法，在该方法中，我将创建一个 SP，每个线程将调用该 SP，并且它们将获得唯一的行。你有其他方法可以处理这个场景吗？

答：

0赞 MT0 11/6/2023 #1

不能在 SQL 中使用本地 PL/SQL 集合（直到 Oracle 23），并且需要在 SQL 作用域中定义集合。

CREATE TYPE int_list IS TABLE OF NUMBER(10,0);

您还可以使用简化问题。UPDATE ... RETURNING ... BULK COLLECT INTO ...

CREATE PROCEDURE AUDITING.fetchTasksByTimestamp(
    p_timestamp IN TIMESTAMP,
    p_limit IN NUMBER,
    updated_ids OUT SYS_REFCURSOR
)
IS
  l_updated_ids int_list;
BEGIN
  UPDATE tasks
  SET    is_processed = 1
  WHERE  ROWID IN (
           SELECT ROWID
           FROM   tasks
           WHERE  is_processed = 0
           AND    timestamp_column <= p_timestamp
           FETCH FIRST p_limit ROWS ONLY
         )
  RETURNING id BULK COLLECT INTO l_updated_ids;

  -- Open the updated_ids cursor for the collected IDs
  OPEN updated_ids FOR
    SELECT COLUMN_VALUE AS id
    FROM   TABLE(l_updated_ids);
END fetchTasksByTimestamp;
/

给定示例数据：

CREATE TABLE tasks (id, is_processed, timestamp_column) AS
SELECT LEVEL, 0, TIMESTAMP '1970-01-01 00:00:00' + (LEVEL - 1) * INTERVAL '1' DAY
FROM   DUAL
CONNECT BY LEVEL <= 10;

然后：

DECLARE
  v_cur SYS_REFCURSOR;
  v_id  tasks.id%TYPE;
BEGIN
  fetchTasksByTimestamp(TIMESTAMP '1970-01-08 00:00:00', 5, v_cur);
  COMMIT;
  LOOP
    FETCH v_cur INTO v_id;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_id);
  END LOOP;
END;
/

输出：

注意 1：不应在过程中执行 COMMIT，因为它会阻止您将多个过程链接在一起，如果在后续过程中出现错误，则将它们全部回滚。相反，应在用于调用该过程的代码中执行 COMMIT。

注 2：您的代码在获取要更新的 id时未指定 ORDER BY 子句;巧合的是，此处返回了前 5 个行，如果没有 ORDER BY 子句，可以按任何顺序获取行。

小提琴

艺术

CREATE PROCEDURE AUDITING.fetchTasksByTimestamp(
    p_timestamp IN TIMESTAMP,
    p_limit IN NUMBER,
    updated_ids OUT SYS_REFCURSOR
)
IS
  l_updated_ids int_list;
BEGIN
  SELECT id
  BULK COLLECT INTO l_updated_ids
  FROM   tasks
  WHERE  is_processed = 0
  AND    timestamp_column <= p_timestamp
  AND    ROWNUM <= p_limit
  FOR UPDATE SKIP LOCKED;

  FORALL i IN 1 .. l_updated_ids.COUNT
    UPDATE tasks
    SET    is_processed = 1
    WHERE  id = l_updated_ids(i);

  -- Open the updated_ids cursor for the collected IDs
  OPEN updated_ids FOR
    SELECT COLUMN_VALUE AS id
    FROM   TABLE(l_updated_ids);
END fetchTasksByTimestamp;
/

小提琴

Oracle 创建存储过程以返回列表

Oracle Create Stored Procedure to return list

评论

评论