sql 中 sql%rowcount 判断执行SQL受影响的行数

最新推荐文章于 2022-05-28 10:13:25 发布

转载最新推荐文章于 2022-05-28 10:13:25 发布 · 2.2k 阅读

文章标签：

#sql #application

Oracle 专栏收录该内容

538 篇文章

订阅专栏

本文介绍了一个复杂的SQL更新语句示例，并利用%ROWCOUNT来判断该更新语句影响的行数是否超过预期，以确保数据的一致性和正确性。

sql 中 sql%rowcount 判断执行SQL受影响的行数

例子：
update work_order
       set order_state = '1'
     where line_id = p_line_id
       and order_state = '0'
       and order_type = 'DPY'
       and ORDER_PRIORITY = (select min(ORDER_PRIORITY)
                               from work_order
                              where order_state = '0'
                                and order_type = 'DPY'
                                and LINE_ID = p_line_id
                                and order_priority > 0
                              group by LINE_ID)
        and exists
               (select 1
                        from product_level pl
                       where pl.pid = pid
                         and pl.child_pid =
                             (select (select pid
                                        from product_maintain pm
                                       where pm.product_number =
                                             rw.material_number
                                         and pm.rstate = rw.material_rstate)
                                from routing_wip rw
                               where rw.sn = p_sn))
        and rownum <= 1;

   if sql%rowcount > 1 then    //代表上面update语句影响的行数

   raise_application_error (-20015,
                           'Work order with same priority in the waiting queue ! ');

   end if ;