如何通过游标修改!

[marketduan]

我现在公司要求把8月10日到10月10日出生的顾客办的会员免费加10分,同时把这个时间段的会员名前加上"特殊"两个字,请问在ORACLE中怎么样通过游标实现UPDATE呢.在线等,急!!

[arben555]

能不能把问题说详细一点!

[tgm78]

Connected to Oracle8i Enterprise Edition Release 8.1.7.4.0
Connected as dbmgr

SQL>
SQL> create table customer(username varchar2(50),user_num number,user_birthday date);

Table created

SQL> insert into customer values('Mikle',200,sysdate);

1 row inserted

SQL> insert into customer values('Kate',200,sysdate-25);

1 row inserted

SQL> insert into customer values('Jone',200,sysdate-55);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-40);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-140);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-120);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-110);

1 row inserted

SQL> commit;

Commit complete

SQL> 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 selected

SQL>
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 created

SQL> set serveroutput on
SQL> exec update_customer;

PL/SQL procedure successfully completed

SQL> 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

[wangzk0206]

update就可以实现为什么要用游标

若 用游标 可以把他付给变量 然后UPDATE 但是直接UPDATE 游标有点不可能

[tgm78]

修改一下

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 created

SQL> insert into customer values('Mikle',200,sysdate);

1 row inserted

SQL> insert into customer values('Kate',200,sysdate-25);

1 row inserted

SQL> insert into customer values('Jone',200,sysdate-55);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-40);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-140);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-120);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-110);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-85);

1 row inserted

SQL> commit;

Commit complete

SQL> set serveroutput on
SQL> exec update_customer;

PL/SQL procedure successfully completed

SQL> 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

[tgm78]

比较简单的sql方式

SQL> create table customer(username varchar2(50),user_num number,user_birthday date);

Table created

SQL> insert into customer values('Mikle',200,sysdate);

1 row inserted

SQL> insert into customer values('Kate',200,sysdate-25);

1 row inserted

SQL> insert into customer values('Jone',200,sysdate-55);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-40);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-140);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-120);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-110);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-85);

1 row inserted

SQL> commit;

Commit complete

update：

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> commit;

Commit complete

SQL> 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

[tgm78]

SQL> drop table customer;

Table dropped

SQL> create table customer(username varchar2(50),user_num number,user_birthday date);

Table created

SQL> insert into customer values('Mikle',200,sysdate);

1 row inserted

SQL> insert into customer values('Kate',200,sysdate-25);

1 row inserted

SQL> insert into customer values('Jone',200,sysdate-55);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-40);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-140);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-120);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-110);

1 row inserted

SQL> insert into customer values('Tom',200,sysdate-85);

1 row inserted

SQL> 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 updated

SQL> commit;

Commit complete

SQL> 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