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

1tu0hz3e 于 2023-11-17 发布在 Oracle

关注(0)|答案(1)|浏览(128)

我有一个任务表，其中有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_MANATED_IDS'不是游标”。
我的要求是在多线程环境中从java spring Boot 调用上面的存储过程。由于部署了多个示例，并且有一个cron将调用SP。我希望每个线程都能获得不同的记录列表。因此我也使用了skip locked。
但是上面的SP正在返回更新的行列表。我尝试了各种方法，但无法解决。
如果有人能帮我解决这个问题，那就太好了。

oracle

来源：https://stackoverflow.com/questions/77431171/oracle-create-stored-procedure-to-return-list

1条答案

按热度按时间

xhv8bpkk1#

您不能在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;
/

型
产出：

型

注一：你不应该在过程中使用COMMIT，因为它会阻止你将多个过程链接在一起，如果在后面的过程中出现错误，那么ROLLBACK就会将它们全部删除。相反，你应该在用于调用过程的代码中使用COMMIT。*
注二：在获取id s更新时，您的代码没有指定ORDER BY子句;这里返回前5个只是巧合，如果没有ORDER BY子句，可以以任何顺序获取行。

fiddle
或者：

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;
/

型
fiddle的

赞(0）回复(0）举报 2023-11-17

我来回答

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

1条答案

相关问题

热门标签

最新问答