17,377
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure chage_critical
as
chgrade varchar2(20);
BEGIN
execute immediate 'ALTER TABLE SC ADD COLUMN(newgrade VARCHAR2(2))';
FOR curecord in (select * from sc) loop
if curecord.grade<60 then
chgrade:='E';
elsif curecord.grade<70 then
chgrade:='D';
elsif curecord.grade<80 then
chgrade:='C';
elsif curecord.grade<90 then
chgrade:='B';
ELSE
chgrade:='A';
END IF;
UPDATE SC SET NEWGRADE=CHGRADE WHERE
SNO=CURECORD.SNO AND CNO=CURECORD.CNO;
END LOOP;
execute immediate 'ALTER TABLE SC DROP COLUMN GRADE';---要是你的字段有记录是不能删除列的
execute immediate 'ALTER TABLE CS RENAME NEWGRADE TO GRADE';
END;
----try it
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
SQL> create table sc(grade number,sno number,cno number);
Table created
SQL>
SQL> create or replace procedure chage_critical as
2 chgrade varchar2(2);
3 curecord sys_refcursor;
4 BEGIN
5 execute immediate 'ALTER TABLE SC ADD newgrade VARCHAR2(2)';
6 FOR curecord in (select * from sc) loop
7 if curecord.grade < 60 then
8 chgrade := 'E';
9 elsif curecord.grade < 70 then
10 chgrade := 'D';
11 elsif curecord.grade < 80 then
12 chgrade := 'C';
13 elsif curecord.grade < 90 then
14 chgrade := 'B';
15 ELSE
16 chgrade := 'A';
17 END IF;
18 execute immediate 'UPDATE SC SET NEWGRADE = CHGRADE WHERE SNO = :1 AND CNO = :2'
19 using curecord.sno, curecord.cno;
20 END LOOP;
21 execute immediate 'ALTER TABLE SC DROP COLUMN GRADE';
22 execute immediate 'ALTER TABLE sc RENAME column NEWGRADE TO GRADE';
23 END;
24 /
Procedure created
SQL>
--还有你最后的DDL语句也错了
--这样:
execute immediate 'ALTER TABLE SC DROP COLUMN GRADE';
execute immediate 'ALTER TABLE CS RENAME NEWGRADE TO GRADE';
--if判断格式错了
if ... then
elsif ... then
elsif ... then
else
....
end if;