代码之家 › 专栏 › 技术社区 › spikeTJ

如何在过程plsql中测试更新前的值

plsqldeveloper plsql stored-procedures oracle

spikeTJ · 技术社区 · 11 年前

我有一个程序,用另一个表中的数据更新我的客户。而且,一些客户不会因为其价值观的失败而更新。

这是引发以下错误的错误:ORA-12899:值对于列“TABLE_EXAMPLE”“BRANCH_CODE”太大(当前值:3,最大值:2)。

现在这是程序:

PROCEDURE p_update_customers AS

CURSOR customer_data IS
  SELECT a.code_cust code_cust,
         '0' || a.branch_code branch_code,
         a.costumer_id,
         a.name name,
         b.description
    FROM customer a, description b
   WHERE a.costumer_id = b.costumer_id;

reg_customer_data customer_data%ROWTYPE;

BEGIN
BEGIN
  OPEN customer_data;
  LOOP
    FETCH customer_data
      INTO reg_customer_data;
    EXIT WHEN customer_data%NOTFOUND;
    BEGIN

我认为在这部分中,我必须使用IF或CASE语句测试分支代码。我只需要值的最后两位数字。(在BEGIN和UPDATE之间)。

UPDATE table_example
         SET code_cust      = reg_customer_data.code_cust,
             branch_code    = reg_customer_data.branch_code,
             costumer_id    = reg_customer_data.costumer_id,
             name           = reg_customer_data.name,
             description    = reg_customer_data.description
       WHERE code_cust      = reg_customer_data.code_cust;
    END;
  END LOOP;
END;
END p_update_customers;

因此,我需要在更新之前测试branch_code,因为我只需要最后两个数字(09或58)。

costumer_id name      branch_code
  1         jose       09
  2         peter      09
  3         jhon       09
  4         charlie   058

如果branch_code有3位,请删除第一位并传递2位值,然后继续更新。

注意:我不能更改或删除光标CUSTOMER_DATA中使用的concat,因为TABLE_EXAMPLE上的BRANCH_CODE必须是两位数。

2 回复 | 直到 11 年前

Ed Gibbs 11 年前

代替 '0' || a.branch_code branch_code 基于 customer.branch_code 数据类型。。。

如果是数字:

TO_CHAR(a.branch_code, 'FM00') branch_code

如果是varchar/varchar2:

SUBSTR('0' || a.branch_code, -2, 2) branch_code

补遗 :OP注意到光标无法更改,因此需要将解决方案移至更新:

UPDATE table_example
     SET code_cust      = reg_customer_data.code_cust,
         branch_code    = SUBSTR(reg_customer_data.branch_code, -2, 2),
         costumer_id    = reg_customer_data.costumer_id,
         name           = reg_customer_data.name,
         description    = reg_customer_data.description
   WHERE code_cust      = reg_customer_data.code_cust;

Alex Poole 11 年前

如果无法更改光标,仍可以使用负位置值 the substr() function 如Ed Gibbs所示,从字符串末尾向后计数,但与光标结果集相反:

with t as (
  select 1 as costumer_id, 'jose' as name, '09' as branch_code from dual
  union all select 2, 'peter ', '09' from dual
  union all select 3, 'jhon', '09' from dual
  union all select 4, 'charlie', '058' from dual
)
select costumer_id, name, branch_code,
  substr(branch_code, -2) as new_branch_code
from t;

| COSTUMER_ID |    NAME | BRANCH_CODE | NEW_BRANCH_CODE |
|-------------|---------|-------------|-----------------|
|           1 |    jose |          09 |              09 |
|           2 |  peter  |          09 |              09 |
|           3 |    jhon |          09 |              09 |
|           4 | charlie |         058 |              58 |

SQL Fiddle .

因此,您可以使用相同的调用更改更新以设置值:

UPDATE table_example
         SET code_cust      = reg_customer_data.code_cust,
             branch_code    = substr(reg_customer_data.branch_code, -2),
...