Oracle - 在过程中选择并删除

I need to return a rowset from an Oracle procedure, and then delete them in that same procedure. Is there a neat way of doing this without temp tables? Something like an in-memory cursor maybe?

我需要从Oracle过程返回一个rowset,然后在同一过程中删除它们。有没有临时表这样做的简洁方法?像内存中的游标那样的东西可能吗?

Basically I'm popping the records off a queue, and I want to avoid two round trips because it's a very frequent process.

基本上我是从队列中弹出记录,我想避免两次往返,因为这是一个非常频繁的过程。

6 个解决方案

#1

Actually, you can do it without a SELECT these days. You can simply DELETE the records you are interested in and use the RETURNING clause to fetch those records into a local variable as they are deleted.

实际上,这些天你可以在没有SELECT的情况下完成。您可以简单地删除您感兴趣的记录,并使用RETURNING子句将这些记录删除到局部变量中。

DELETE FROM my_table
  WHERE <whatever conditions>
  RETURNING column1, column2, ...
  INTO array1, array2, ...

The slightly annoying part of this method is that you need to fetch each column into a separate variable. You can't use a record type in this context. So if you have a lot of columns it can get cumbersome.

这种方法略显烦人的部分是你需要将每个列提取到一个单独的变量中。您不能在此上下文中使用记录类型。因此,如果你有很多列,它会变得很麻烦。

#2

You can use a cursor for update, e.g.

您可以使用游标进行更新,例如

DECLARE
  CURSOR c_updates
  IS
    SELECT *
    FROM table1 t1
    LEFT JOIN table2 t2 ON t1.field = t2.field
    WHERE t2.field IS NULL
  FOR UPDATE OF t1.field;

  l_record c_updates%ROWTYPE;
BEGIN
  OPEN c_updates;

  LOOP
    FETCH c_updates INTO l_record;
    EXIT WHEN c_updates%NOTFOUND;

    --Do what you want with l_record

    DELETE FROM table1
    WHERE CURRENT OF c_updates;
  END LOOP;

  CLOSE c_updates;
END;

#3

You can return a cursor from a procedure or an anonymous block:

您可以从过程或匿名块返回游标:

BEGIN
        OPEN :cur FOR
        SELECT  *
        FROM    table
        WHERE   condition;

        DELETE
        FROM    table
        WHERE   condition;

END;

The cursor will persist after delete.

删除后光标将保持不变。

See the entry in my blog for detailed explanations:

有关详细说明,请参阅我博客中的条目:

Oracle: returning deleted records

Oracle:返回已删除的记录

, and here is this entry in a nutshell:

,简而言之,这是一个条目:

CREATE TABLE t_deleter (id INT NOT NULL PRIMARY KEY, value VARCHAR2(50))
/
INSERT
INTO    t_deleter (id, value)
VALUES (1, 'Value 1')
/
INSERT
INTO    t_deleter (id, value)
VALUES (2, 'Value 2')
/
COMMIT
/
SELECT  *
FROM    t_deleter
/
VAR cur REFCURSOR
BEGIN
        OPEN    :cur FOR
        SELECT  *
        FROM    t_deleter
        WHERE   id = 1;
        DELETE
        FROM    t_deleter
        WHERE   id = 1;
END;
/
PRINT cur
SELECT  *
FROM    t_deleter
/

Table created.


1 row created.


1 row created.


Commit complete.


        ID VALUE
---------- --------------------------------------------------
         1 Value 1
         2 Value 2


PL/SQL procedure successfully completed.


/*
   PRINT CUR
   This is what returned to the client
*/

        ID VALUE
---------- --------------------------------------------------
         1 Value 1

/*
   SELECT  *
   FROM    t_deleter

   This is what's left after the procedure completed
*/


        ID VALUE
---------- --------------------------------------------------
         2 Value 2

#4

Populate the data into an TYPE and return that?

将数据填充到TYPE并返回?

e.g.

CREATE TYPE blah as (data-columns-go-here)
/

CREATE TYPE blah_table AS TABLE OF blah;
/

#5

Building on reubenpeeris' answer and cagcowboy's answer:

建立在reubenpeeris的回答和cagcowboy的回答:

WARNING: I don't have access to a PL/SQL compiler at the moment, so there is a chance that something is wrong.

警告:我目前无法访问PL / SQL编译器,因此可能出现问题。

TYPE popped_records_table_type IS TABLE OF my_table%ROWTYPE INDEX BY BINARY_INTEGER;

FUNCTION pop_records(...) RETURN popped_records_table_type IS
    popped_records popped_records_table_type;
    popped_record my_table%ROWTYPE;
    next_popped_record_index BINARY_INTEGER;

    CURSOR popped_records_cursor IS
        SELECT * FROM my_table WHERE ... FOR UPDATE;
BEGIN
    next_popped_record_index := 1;

    OPEN popped_records_cursor;

    LOOP
        FETCH popped_records_cursor INTO popped_record;
        EXIT WHEN popped_records_cursor%NOTFOUND;

        DELETE FROM my_table WHERE CURRENT OF popped_records_cursor;

        popped_records(next_popped_record_index) := popped_record;
        next_popped_record_index := next_popped_record_index + 1;
    END LOOP;

    CLOSE popped_records_cursor;

    RETURN popped_records;
END;

Edit: I believe this will also work with a stored procedure, so long as you provide an instance of the popped_records_table_type as an IN/OUT parameter:

编辑:我相信这也适用于存储过程,只要您提供popped_records_table_type的实例作为IN / OUT参数:

PROCEDURE pop_records(popped_records IN OUT popped_records_table_type, ...) IS
    -- Pretty much the same as above

#6

Oracle has something call advanced queuing, maybe it is better to use that feature than building your own queuing system.

Oracle有一些调用高级排队的东西,也许最好使用该功能而不是构建自己的排队系统。

#1