变量包含受先前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语句之后和提交之前，可以将SQL％ROWCOUNT存储在NUMBER类型的变量中.请记住，COMMIT或ROLLBACK会将SQL％ROWCOUNT的值重置为0，因此您必须在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 )

07年4月7日，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.

关于skarthi

也许:

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.获取结果状态有几种方法可以确定命令的效果.第一种方法是使用GET DIAGNOSTICS命令，其格式为:

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变量= 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，类型为boolean. 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语句产生(并丢弃)a，则将FOUND设置为true row，如果没有行产生，则返回false.

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

UPDATE，INSERT和DELETE语句将FOUND设置为true 受影响的行，如果不影响任何行，则为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设置为true，否则 错误的.这适用于FOR语句的所有三个变体 (整数FOR循环，记录集FOR循环和动态记录集FOR 循环).当FOR循环退出时，以这种方式设置FOUND.在 - 的里面 循环执行，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屋！