如何通过游标修改!

Question

Connected to Oracle8i Enterprise Edition Release 8.1.7.4.0 
Connected as dbmgrSQL> 
SQL> create table customer(username varchar2(50),user_num number,user_birthday date);Table createdSQL> insert into customer values('Mikle',200,sysdate);1 row insertedSQL> insert into customer values('Kate',200,sysdate-25);1 row insertedSQL> insert into customer values('Jone',200,sysdate-55);1 row insertedSQL> insert into customer values('Tom',200,sysdate-40);1 row insertedSQL> insert into customer values('Tom',200,sysdate-140);1 row insertedSQL> insert into customer values('Tom',200,sysdate-120);1 row insertedSQL> insert into customer values('Tom',200,sysdate-110);1 row insertedSQL> commit;Commit completeSQL> select * from customer;USERNAME                                             USER_NUM USER_BIRTHDAY
-------------------------------------------------- ---------- -------------
Mikle                                                     200 2006-12-18 20
Kate                                                      200 2006-11-23 20
Jone                                                      200 2006-10-24 20
Tom                                                       200 2006-11-8 20:
Tom                                                       200 2006-7-31 20:
Tom                                                       200 2006-8-20 20:
Tom                                                       200 2006-8-30 20:7 rows selectedSQL> 
SQL> create or replace procedure update_customer is
  2    cursor update_customer_cur is
  3    select username,user_num from customer where to_char(user_birthday,'mmdd')>=0810 and to_char(user_birthday,'mmdd')<=1010;
  4    v_username       varchar2(50);
  5    v_user_num       number;
  6  begin
  7      open update_customer_cur;
  8      loop
  9      fetch update_customer_cur into v_username,v_user_num;
 10      exit when update_customer_cur%NOTFOUND;
 11      execute immediate 'update customer set user_num='||v_user_num||'+10';
 12      execute immediate 'update customer set username='''||'特殊'||v_username||'''';
 13      end loop;
 14      close update_customer_cur;
 15       return;
 16  end update_customer;
 17  /Procedure createdSQL> set serveroutput on
SQL> exec update_customer;PL/SQL procedure successfully completedSQL> select * from customer;USERNAME                                             USER_NUM USER_BIRTHDAY
-------------------------------------------------- ---------- -------------
特殊Tom                                                   210 2006-12-18 20
特殊Tom                                                   210 2006-11-23 20
特殊Tom                                                   210 2006-10-24 20
特殊Tom                                                   210 2006-11-8 20:
特殊Tom                                                   210 2006-7-31 20:
特殊Tom                                                   210 2006-8-20 20:
特殊Tom                                                   210 2006-8-30 20:7 rows selected

Answer 1

update就可以实现为什么要用游标若 用游标 可以把他付给变量 然后UPDATE  但是直接UPDATE 游标有点不可能

Answer 2

修改一下create or replace procedure update_customer is
  cursor update_customer_cur is
  select username,user_num from customer where to_char(user_birthday,'mmdd')>=0810 and to_char(user_birthday,'mmdd')<=1010;
  v_username       varchar2(50);
  v_user_num       number;
begin
    open update_customer_cur;
    loop
    fetch update_customer_cur into v_username,v_user_num;
    exit when update_customer_cur%NOTFOUND;
    execute immediate 'update customer set user_num='||v_user_num||'+10 where to_char(user_birthday,''mmdd'')>=0810 and to_char(user_birthday,''mmdd'')<=1010';
    execute immediate 'update customer set username='''||'特殊'||v_username||''' where to_char(user_birthday,''mmdd'')>=0810 and to_char(user_birthday,''mmdd'')<=1010';
    end loop;
    close update_customer_cur;
     return;
end update_customer;
/结果如下
SQL> create table customer(username varchar2(50),user_num number,user_birthday date);Table createdSQL> insert into customer values('Mikle',200,sysdate);1 row insertedSQL> insert into customer values('Kate',200,sysdate-25);1 row insertedSQL> insert into customer values('Jone',200,sysdate-55);1 row insertedSQL> insert into customer values('Tom',200,sysdate-40);1 row insertedSQL> insert into customer values('Tom',200,sysdate-140);1 row insertedSQL> insert into customer values('Tom',200,sysdate-120);1 row insertedSQL> insert into customer values('Tom',200,sysdate-110);1 row insertedSQL> insert into customer values('Tom',200,sysdate-85);1 row insertedSQL> commit;Commit completeSQL> set serveroutput on
SQL> exec update_customer;PL/SQL procedure successfully completedSQL> select * from customer;USERNAME                                             USER_NUM USER_BIRTHDAY
-------------------------------------------------- ---------- -------------
Mikle                                                     200 2006-12-18 21
Kate                                                      200 2006-11-23 21
Jone                                                      200 2006-10-24 21
Tom                                                       200 2006-11-8 21:
Tom                                                       200 2006-7-31 21:
特殊Tom                                                   210 2006-8-20 21:
特殊Tom                                                   210 2006-8-30 21:
特殊Tom                                                   210 2006-9-24 21:8 rows selected

Answer 3

比较简单的sql方式SQL> create table customer(username varchar2(50),user_num number,user_birthday date);Table createdSQL> insert into customer values('Mikle',200,sysdate);1 row insertedSQL> insert into customer values('Kate',200,sysdate-25);1 row insertedSQL> insert into customer values('Jone',200,sysdate-55);1 row insertedSQL> insert into customer values('Tom',200,sysdate-40);1 row insertedSQL> insert into customer values('Tom',200,sysdate-140);1 row insertedSQL> insert into customer values('Tom',200,sysdate-120);1 row insertedSQL> insert into customer values('Tom',200,sysdate-110);1 row insertedSQL> insert into customer values('Tom',200,sysdate-85);1 row insertedSQL> commit;Commit completeupdate：SQL> update customer set user_num=user_num+10 where to_char(user_birthday,'mmdd')>=0810 and to_char(user_birthday,'mmdd')<=1010;3 rows updatedSQL> commit;Commit completeSQL> select * from customer;USERNAME                                             USER_NUM USER_BIRTHDAY
-------------------------------------------------- ---------- -------------
Mikle                                                     200 2006-12-18 21
Kate                                                      200 2006-11-23 21
Jone                                                      200 2006-10-24 21
Tom                                                       200 2006-11-8 21:
Tom                                                       200 2006-7-31 21:
特殊Tom                                                   220 2006-8-20 21:
特殊Tom                                                   220 2006-8-30 21:
特殊Tom                                                   220 2006-9-24 21:8 rows selected

Answer 4

SQL> drop table customer;Table droppedSQL> create table customer(username varchar2(50),user_num number,user_birthday date);Table createdSQL> insert into customer values('Mikle',200,sysdate);1 row insertedSQL> insert into customer values('Kate',200,sysdate-25);1 row insertedSQL> insert into customer values('Jone',200,sysdate-55);1 row insertedSQL> insert into customer values('Tom',200,sysdate-40);1 row insertedSQL> insert into customer values('Tom',200,sysdate-140);1 row insertedSQL> insert into customer values('Tom',200,sysdate-120);1 row insertedSQL> insert into customer values('Tom',200,sysdate-110);1 row insertedSQL> insert into customer values('Tom',200,sysdate-85);1 row insertedSQL> commit;Commit complete测试：SQL> update customer set user_num=user_num+10 where to_char(user_birthday,'mmdd')>=0810 and to_char(user_birthday,'mmdd')<=1010;3 rows updated
SQL> update customer set username=substr('特殊',1,4)||to_char(substr(username,1,9))  where to_char(user_birthday,'mmdd')>=0810 and to_char(user_birthday,'mmdd')<=1010
  2  /3 rows updatedSQL> commit;Commit completeSQL> select * from customer;USERNAME                                             USER_NUM USER_BIRTHDAY
-------------------------------------------------- ---------- -------------
Mikle                                                     200 2006-12-18 21
Kate                                                      200 2006-11-23 21
Jone                                                      200 2006-10-24 21
Tom                                                       200 2006-11-8 21:
Tom                                                       200 2006-7-31 21:
特殊Tom                                                   210 2006-8-20 21:
特殊Tom                                                   210 2006-8-30 21:
特殊Tom                                                   210 2006-9-24 21:8 rows selected