有条件地将 Oracle 表中的列值更新为 1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29932947/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update column value by 1 in Oracle table conditionally
提问by csr
I have Oracle table with 4 columns (Name, Phone, Email, Count)
我有 4 列的 Oracle 表（姓名、电话、电子邮件、计数）
If user is updating value of 'Name' column then I need to increment Count column value by 1.
如果用户正在更新“名称”列的值，那么我需要将 Count 列值增加 1。
If user is updating values other than column then I no need to increment Count column value by 1.
如果用户正在更新列以外的值，那么我不需要将 Count 列值增加 1。
Initially, when the record is inserted, count should be 0. And every time when 'Name' column updated, the Count should be increment by 1 (like 1, 2, 3 .....)
最初，当插入记录时，计数应为 0。每次更新“名称”列时，计数应增加 1（如 1、2、3 .....）
How we can achieve this, I am very new to Database
我们如何实现这一点，我对数据库很陌生
thanks a lot for your help.
非常感谢你的帮助。
回答by eckes
You can do that in an update and insert trigger or in your program. The later however requires all possible programs to cooperate. The former (triggers) is black art.
您可以在更新和插入触发器中或在您的程序中执行此操作。然而，后者需要所有可能的程序进行合作。前者（触发器）是黑色艺术。
A program can do it like this:
一个程序可以这样做：
UPDATE Person SET Count=Count+1, Phone='123' WHERE name=`csr` and Phone <> '123';
This will update one or no record (i.e. if phone was already 123 it will do nothing).
这将更新一条记录或不更新记录（即，如果手机已经是 123，它将什么也不做）。
BTW: there is no nice solution to insert it if it was missing.
顺便说一句：如果它丢失，没有很好的解决方案来插入它。
Hmm, you changed your question, updating the Name is problematic if you do not have another primary key, is that really what you want?
嗯，你改变了你的问题，如果你没有另一个主键，更新 Name 是有问题的，这真的是你想要的吗？
回答by anudeepks
If you are looking at plsql procedure, then you can use this method, I am using 3 input variables,
如果您正在查看 plsql 程序，那么您可以使用此方法，我使用的是 3 个输入变量，
1.) column name to edit
1.) 要编辑的列名
2.) old value
2.) 旧值
3.) New value to be updated
3.) 要更新的新值
SQL> create or replace procedure updateval (colname varchar2,oldval varchar2,newval varchar2) is
2 l_prop varchar2(10);
3 l_newval varchar2(10);
4 l_old_val varchar2(10);
5 begin
6 l_prop:=colname;
7 l_newval:=newval;
8 l_old_val:=oldval;
9 IF (upper(l_prop)='NAME') THEN
10 update TESTING123 set name=l_newval,count=count+1 where name=l_old_val;
11 elsif (upper(l_prop)='PHONE') THEN
12 update TESTING123 set PHONE=l_newval ,count=count+1where PHONE=l_old_val;
13 elsif (upper(l_prop)='EMAIL') THEN
14 update TESTING123 set EMAIL=l_newval ,count=count+1 where EMAIL=l_old_val;
15 END IF;
16 end;
17 /
Procedure created.
SQL>
SQL> select * from testing123;
NAME PHONE EMAIL COUNT
---------- ---------- ---------- ----------
abc1 12345 [email protected] 1
xyz 3435 [email protected] 0
SQL> exec updateval ('NAME','abc1','newabc1');
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from testing123;
NAME PHONE EMAIL COUNT
---------- ---------- ---------- ----------
newabc1 12345 [email protected] 2
xyz 3435 [email protected] 0
