包含受先前 DELETE 影响的行数的变量?(在函数中) [英] Variable containing the number of rows affected by previous DELETE? (in a function)

问题描述

我有一个用作 INSERT 触发器的函数.此函数删除与插入的行中的 [序列号] 冲突的行.它工作得很好，所以我真的不想争论这个概念的优点.

I have a function that is used as an INSERT trigger. This function deletes rows that would conflict with [the serial number in] the row being inserted. It works beautifully, so I'd really rather not debate the merits of the concept.

DECLARE
re1 feeds_item.shareurl%TYPE;
BEGIN
SELECT regexp_replace(NEW.shareurl, '/[^/]+(-[0-9]+.html)$','/[^/]+\1') INTO re1;
RAISE NOTICE 'DELETEing rows from feeds_item where shareurl ~ ''%''', re1;

DELETE FROM feeds_item where shareurl ~ re1;
RETURN NEW;
END;

我想在通知中添加一个指示有多少行受到影响(又名:删除).我该怎么做(使用 LANGUAGE 'plpgsql')?

I would like to add to the NOTICE an indication of how many rows are affected (aka: deleted). How can I do that (using LANGUAGE 'plpgsql')?

更新:根据厨房里的鸡"的一些出色指导，我已将其更改为:

UPDATE: Base on some excellent guidance from "Chicken in the kitchen", I have changed it to this:

DECLARE
re1 feeds_item.shareurl%TYPE;
num_rows int;
BEGIN
SELECT regexp_replace(NEW.shareurl, '/[^/]+(-[0-9]+.html)$','/[^/]+\1') INTO re1;

DELETE FROM feeds_item where shareurl ~ re1;
IF FOUND THEN
    GET DIAGNOSTICS num_rows = ROW_COUNT;
    RAISE NOTICE 'DELETEd % row(s) from feeds_item where shareurl ~ ''%''', num_rows, re1;
END IF;
RETURN NEW;
END;

推荐答案

在Oracle PL/SQL中，存储删除/插入/更新行数的系统变量为:

In Oracle PL/SQL, the system variable to store the number of deleted / inserted / updated rows is:

SQL%ROWCOUNT

在 DELETE/INSERT/UPDATE 语句和 BEFORE COMMITTING 之后，您可以将 SQL%ROWCOUNT 存储在类型为 NUMBER 的变量中.请记住，COMMIT 或 ROLLBACK 会将 SQL%ROWCOUNT 的值重置为零，因此您必须在 COMMIT 或 ROLLBACK 之前将 SQL%ROWCOUNT 值复制到变量中.

After a DELETE / INSERT / UPDATE statement, and BEFORE COMMITTING, you can store SQL%ROWCOUNT in a variable of type NUMBER. Remember that COMMIT or ROLLBACK reset to ZERO the value of SQL%ROWCOUNT, so you have to copy the SQL%ROWCOUNT value in a variable BEFORE COMMIT or ROLLBACK.

示例:

BEGIN
   DECLARE
      affected_rows   NUMBER DEFAULT 0;
   BEGIN
      DELETE FROM feeds_item
            WHERE shareurl = re1;

      affected_rows := SQL%ROWCOUNT;
      DBMS_OUTPUT.
       put_line (
            'This DELETE would affect '
         || affected_rows
         || ' records in FEEDS_ITEM table.');
      ROLLBACK;
   END;
END;

我也发现了这个有趣的解决方案(来源:http://markmail.org/message/grqap2pncqd6w3sp )

I have found also this interesting SOLUTION (source: http://markmail.org/message/grqap2pncqd6w3sp )

在 4/7/07，Karthikeyan Sundaram 写道:

On 4/7/07, Karthikeyan Sundaram wrote:

I am using 8.1.0 postgres and trying to write a plpgsql block.  In that I am inserting a row.  I want to check to see if the row has been

插入与否.

在oracle中我们可以这样说

In oracle we can say like this

begin
  insert into table_a values (1);
  if sql%rowcount > 0
  then
    dbms.output.put_line('rows inserted');
  else
    dbms.output.put_line('rows not inserted');
 end if;  end;

postgres 中是否有等于 sql%rowcount 的东西?请帮忙.

Is there something equal to sql%rowcount in postgres? Please help.

问候斯卡蒂

也许:

http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

点击上面的链接，你会看到这个内容:

Click on the link above, you'll see this content:

37.6.6.获取结果状态 有多种方法可以确定命令的效果.第一种方法是使用GETDIAGNOSTICS 命令，格式为:

37.6.6. Obtaining the Result Status There are several ways to determine the effect of a command. The first method is to use the GET DIAGNOSTICS command, which has the form:

GET DIAGNOSTICS variable = item [ , ... ];该命令允许检索系统状态指示器.每一项都是一个关键词标识要分配给指定变量的状态值(这应该是接收它的正确数据类型).目前可用状态项为 ROW_COUNT，处理的行数发送到 SQL 引擎的最后一条 SQL 命令，以及 RESULT_OID，最近的 SQL 命令插入的最后一行的 OID.注意RESULT_OID 仅在将 INSERT 命令插入表后才有用包含 OID.

GET DIAGNOSTICS variable = item [ , ... ];This command allows retrieval of system status indicators. Each item is a key word identifying a state value to be assigned to the specified variable (which should be of the right data type to receive it). The currently available status items are ROW_COUNT, the number of rows processed by the last SQL command sent down to the SQL engine, and RESULT_OID, the OID of the last row inserted by the most recent SQL command. Note that RESULT_OID is only useful after an INSERT command into a table containing OIDs.

示例:

获取诊断信息 integer_var = ROW_COUNT;第二种方法确定一个命令的效果是检查特殊变量名为 FOUND，它是布尔类型的.FOUND 开始时是错误的每个 PL/pgSQL 函数调用.它由以下每种类型设置语句:

GET DIAGNOSTICS integer_var = ROW_COUNT; The second method to determine the effects of a command is to check the special variable named FOUND, which is of type boolean. FOUND starts out false within each PL/pgSQL function call. It is set by each of the following types of statements:

SELECT INTO 语句将 FOUND 设置为 true 如果指定了一行，则为 false不返回任何行.

A SELECT INTO statement sets FOUND true if a row is assigned, false if no row is returned.

如果 PERFORM 语句产生(并丢弃)一个行，如果没有生成行，则为 false.

A PERFORM statement sets FOUND true if it produces (and discards) a row, false if no row is produced.

UPDATE、INSERT 和 DELETE 语句将 FOUND 设置为真，如果至少有一个行受到影响，如果没有行受到影响，则为 false.

UPDATE, INSERT, and DELETE statements set FOUND true if at least one row is affected, false if no row is affected.

如果 FETCH 语句返回一行，则将 FOUND 设置为 true，如果没有行，则设置为 false被退回.

A FETCH statement sets FOUND true if it returns a row, false if no row is returned.

如果 FOR 语句迭代一次或多次，则将 FOUND 设置为真，否则错误的.这适用于 FOR 语句的所有三个变体(整数 FOR 循环、记录集 FOR 循环和动态记录集 FOR循环).FOUND 在 FOR 循环退出时以这种方式设置；在 - 的里面循环的执行，FOUND 没有被 FOR 语句修改，尽管它可能会因执行其他语句而改变循环体.

A FOR statement sets FOUND true if it iterates one or more times, else false. This applies to all three variants of the FOR statement (integer FOR loops, record-set FOR loops, and dynamic record-set FOR loops). FOUND is set this way when the FOR loop exits; inside the execution of the loop, FOUND is not modified by the FOR statement, although it may be changed by the execution of other statements within the loop body.

FOUND 是每个 PL/pgSQL 函数中的局部变量；任何变化它只影响当前功能.

FOUND is a local variable within each PL/pgSQL function; any changes to it affect only the current function.

这篇关于包含受先前 DELETE 影响的行数的变量?(在函数中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！