如有两条sql:
update serv set cust_gp='gk' where user_kind_id=0;
commit;
update serv set cust_gp='nk' where user_kind_id=1;
commit;如果使用动态sql
c_sql:='update serv set cust_gp=''gk'' where user_kind_id=0'
execute immediate c_sql;
commit;
c_sql:='update serv set cust_gp=''nk'' where user_kind_id=1'
execute immediate c_sql;
commit;
这样写太复杂了,能否写在一个c_sql='' 里?
update serv set cust_gp='gk' where user_kind_id=0;
commit;
update serv set cust_gp='nk' where user_kind_id=1;
commit;如果使用动态sql
c_sql:='update serv set cust_gp=''gk'' where user_kind_id=0'
execute immediate c_sql;
commit;
c_sql:='update serv set cust_gp=''nk'' where user_kind_id=1'
execute immediate c_sql;
commit;
这样写太复杂了,能否写在一个c_sql='' 里?
update serv set cust_gp=''nk'' where user_kind_id=1';
declare
sql_txt varchar2(2000);
begin
sql_txt:='update serv set cust_gp=''gk'' where user_kind_id=0;update serv set cust_gp=''nk'' where user_kind_id=1;';
dbms_output.put_line(sql_txt);
commit;
end;
update emp e1
set e1.job=( select decode(e2.job,'CLERK','CLERK-1','SALESMAN','SALESMAN-1','')
from emp e2 WHERE e2.empno=e1.empno )
where exists( select 1 from emp e3 where e3.job in ('CLERK','SALESMAN') and e3.empno=e1.empno );
set e1.job=( select decode(e2.job,'CLERK','CLERK-1','SALESMAN','SALESMAN-1','')
from emp e2 WHERE e2.empno=e1.empno )
where exists( select 1 from emp e3 where e3.job in ('CLERK','SALESMAN') and e3.empno=e1.empno );
and e1.job IN ('CLERK','SALESMAN');
update serv s1
set s1.cust_gp=( select decode(s2.user_kind_id,0,'gk',1,'nk','')
from serv s2 whereupdate emp e1
set e1.ename=( select e2.ename||' '||to_char(e2.deptno)||' '||decode(e2.deptno,10,'Accounting',20,'Research',30,'Sales','NULL')
from emp e2 where e2.empno=e1.empno )
where exists ( select 1 from emp e3 where e3.empno=e1.empno );
DEPTNO DNAME LOC
---------- ---------------------------- ------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONscott@SZTYORA> select empno, ename, deptno from emp; EMPNO ENAME DEPTNO
---------- -------------------- ----------
7876 ADAMS 30
7521 WARD 30
7566 JONES 30
7499 ALLEN 30
7839 KING 10
7902 FORD 30
7698 BLAKE 30
7844 TURNER 30
7369 SMITH 30
7900 JAMES 30
7934 MILLER 10
7782 CLARK 10
7788 SCOTT 30
7654 MARTIN 30已选择14行。scott@SZTYORA> update emp e1
2 set e1.ename=( select e2.ename||' '||to_char(e2.deptno)||' '||decode(e2.deptno,10,'Accountin
3 from emp e2 where e2.empno=e1.empno )
4 where exists ( select 1 from emp e3 where e3.empno=e1.empno );已更新14行。scott@SZTYORA> select empno, ename, deptno from emp; EMPNO ENAME DEPTNO
---------- -------------------- ----------
7876 ADAMS 30 Sales 30
7521 WARD 30 Sales 30
7566 JONES 30 Sales 30
7499 ALLEN 30 Sales 30
7839 KING 10 Accounting 10
7902 FORD 30 Sales 30
7698 BLAKE 30 Sales 30
7844 TURNER 30 Sales 30
7369 SMITH 30 Sales 30
7900 JAMES 30 Sales 30
7934 MILLER 10 Accounting 10
7782 CLARK 10 Accounting 10
7788 SCOTT 30 Sales 30
7654 MARTIN 30 Sales 30已选择14行。
条件能找到规律。
如果传入的两个条件字段的值有规律,用forall批量绑定,定义相关集合,就 ok了。如果字段来源于其他表,update的表不大,可以考虑搂上的
将多条sql写到一条,试试如下
update serv set cust_gp=case user_kind when 0 then 'gk'
when 1 then 'nk' end
where user_kind_id in (0,1);
建议你用循环来做:for i in 1..3 loop
execute 'update serv set cust_gp='||i||'' where user_kind_id=''||i||'';
endloop中间i的部分用对象object数组来实现
update serv set cust_gp=(case when user_kind_id=0 then 'gk' when user_kind_id=1
then 'nk' else null end);
commit;