在大家的帮助下完成了。
create or replace procedure chage_critical as
chgrade char(1);
cursno sc.sno%TYPE;
curcno sc.cno%TYPE;
curgrade sc.grade%TYPE;
cursor mycursor is
select sno,cno,grade from sc;
BEGIN
execute immediate 'ALTER TABLE SC ADD (newgrade VARCHAR2(10))';
open mycursor;
if mycursor%isopen then
loop
fetch mycursor into cursno,curcno,curgrade;
exit when (mycursor%notfound);
if curgrade < 60 then
chgrade := 'E';
elsif curgrade < 70 then
chgrade := 'D';
elsif curgrade < 80 then
chgrade := 'C';
elsif curgrade < 90 then
chgrade := 'B';
ELSE
chgrade := 'A';
END IF;
execute immediate 'UPDATE SC SET NEWGRADE = chgrade WHERE SNO = :1 AND CNO = :2'
using cursno, curcno;END LOOP;
end if;
close mycursor;
execute immediate 'ALTER TABLE SC DROP COLUMN GRADE';
execute immediate 'ALTER TABLE sc RENAME column NEWGRADE TO GRADE';
END;
/
过程已创建;但是出现新问题SQL> call chage_critical();
call chage_critical()
*
第 1 行出现错误:
ORA-00904: "CHGRADE": 标识符无效
ORA-06512: 在 "SOFT132.CHAGE_CRITICAL", line 26继续求助求助
create or replace procedure chage_critical as
chgrade char(1);
cursno sc.sno%TYPE;
curcno sc.cno%TYPE;
curgrade sc.grade%TYPE;
cursor mycursor is
select sno,cno,grade from sc;
BEGIN
execute immediate 'ALTER TABLE SC ADD (newgrade VARCHAR2(10))';
open mycursor;
if mycursor%isopen then
loop
fetch mycursor into cursno,curcno,curgrade;
exit when (mycursor%notfound);
if curgrade < 60 then
chgrade := 'E';
elsif curgrade < 70 then
chgrade := 'D';
elsif curgrade < 80 then
chgrade := 'C';
elsif curgrade < 90 then
chgrade := 'B';
ELSE
chgrade := 'A';
END IF;
execute immediate 'UPDATE SC SET NEWGRADE = chgrade WHERE SNO = :1 AND CNO = :2'
using cursno, curcno;END LOOP;
end if;
close mycursor;
execute immediate 'ALTER TABLE SC DROP COLUMN GRADE';
execute immediate 'ALTER TABLE sc RENAME column NEWGRADE TO GRADE';
END;
/
过程已创建;但是出现新问题SQL> call chage_critical();
call chage_critical()
*
第 1 行出现错误:
ORA-00904: "CHGRADE": 标识符无效
ORA-06512: 在 "SOFT132.CHAGE_CRITICAL", line 26继续求助求助
ELSE
chgrade := 'A';
END IF;应该是
curgrade
curgrade是数字curgrade是中间变量啦
END IF;
execute immediate 'UPDATE SC SET NEWGRADE = chgrade WHERE SNO = :1 AND CNO = :2'
using cursno, curcno;报的是这个错误 我改成‘E’也不对。
是不是NEWGRADE的类型没有设置呢?
高了很久 不知道怎么改额。
using cursno, curcno;你这个chgrade 也要用变量传递进去'UPDATE SC SET NEWGRADE = :3 WHERE SNO = :1 AND CNO = :2'
using cursno, curcno,chgrade;
'UPDATE SC SET NEWGRADE = :3 WHERE SNO = :1 AND CNO = :2'
using cursno, curcno,chgrade;
这样是没有错误了。但是值依旧 无法写入。
但是 我问题已经解决了谢谢。
大家可以参考一下。虽然有点笨,但是 想不到别的了。呵呵
create or replace procedure chage_critical as
chgrade char(1);
cursno sc.sno%TYPE;
curcno sc.cno%TYPE;
curgrade sc.grade%TYPE;
cursor mycursor is
select sno,cno,grade from sc;
BEGIN
execute immediate 'ALTER TABLE SC ADD (newgrade CHAR(10))';
open mycursor;
if mycursor%isopen then
loop
fetch mycursor into cursno,curcno,curgrade;
exit when (mycursor%notfound);
if curgrade < 60 then
execute immediate 'UPDATE SC SET newgrade = 5 WHERE SNO = :1 AND CNO = :2'
using cursno, curcno;
elsif curgrade < 70 then
execute immediate 'UPDATE SC SET newgrade = 4 WHERE SNO = :1 AND CNO = :2'
using cursno, curcno;
elsif curgrade < 80 then
execute immediate 'UPDATE SC SET newgrade = 3 WHERE SNO = :1 AND CNO = :2'
using cursno, curcno;
elsif curgrade < 90 then
execute immediate 'UPDATE SC SET newgrade = 2 WHERE SNO = :1 AND CNO = :2'
using cursno, curcno;
ELSE
execute immediate 'UPDATE SC SET newgrade = 1 WHERE SNO = :1 AND CNO = :2'
using cursno, curcno;
END IF;
END LOOP;
end if;
close mycursor;
execute immediate 'ALTER TABLE SC DROP COLUMN GRADE';
execute immediate 'ALTER TABLE sc RENAME column NEWGRADE TO GRADE';
END;
/
UPDATE SC SET grade = 'A' WHERE grade ='1';
UPDATE SC SET grade = 'B' WHERE grade ='2';
UPDATE SC SET grade = 'C' WHERE grade ='3';
UPDATE SC SET grade = 'D' WHERE grade ='4';
UPDATE SC SET grade = 'E' WHERE grade ='5';