题目如下:
将学生选课成绩从百分制改为等级制代码如下:create or replace procedure chage_critical
as
chgrade varchar2(2);
curecord RECORD;
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';
else
if curecord.grade<70 then
chgrade:='D';
else
if curecord.grade<80 then
chgrade:='C';
else
if 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;
ALTER TABLE SC DROP COLUMN GRADE;
ALTER TABLE CS RENAME NEWGRADE TO GRADE;
END;
/错误如下:
SQL> show err;
PROCEDURE CHAGE_CRITICAL 出现错误:LINE/COL ERROR
-------- --------------------------------------------
25/6 PLS-00103: 出现符号 "LOOP"在需要下列之一时:
if
麻烦各位帮忙了。
将学生选课成绩从百分制改为等级制代码如下:create or replace procedure chage_critical
as
chgrade varchar2(2);
curecord RECORD;
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';
else
if curecord.grade<70 then
chgrade:='D';
else
if curecord.grade<80 then
chgrade:='C';
else
if 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;
ALTER TABLE SC DROP COLUMN GRADE;
ALTER TABLE CS RENAME NEWGRADE TO GRADE;
END;
/错误如下:
SQL> show err;
PROCEDURE CHAGE_CRITICAL 出现错误:LINE/COL ERROR
-------- --------------------------------------------
25/6 PLS-00103: 出现符号 "LOOP"在需要下列之一时:
if
麻烦各位帮忙了。
解决方案 »
- 设置事务的隔离级别本质上是不是就是启动了oracle默认的封锁机制?
- 安装oracle报错 ,Java Virtual Machine Launcher:Fatal exception occurred.Program .....
- 求树关系的sql语句 急切
- [急]Oracle 如何實現一列數據整合到一個字段里
- 关于数据转移问题!!!!请教!!!
- 请教一个简单的SQL问题
- sqlserver中的text字段,在oracle建表的时候应该用什么类型?
- 哪位老师帮忙解释一下函数纯度的问题
- 不是因为难,而是不会!--- 帮忙解决一下!
- 急急!!oracle数据恢复,在线
- 请教 获取当前用户可以操作的某个数据库内所有表名称列表?
- 请教创建用户和赋予权限的问题
--if判断格式错了
if ... then
elsif ... then
elsif ... then
else
....
end if;
--还有你最后的DDL语句也错了
--这样:
execute immediate 'ALTER TABLE SC DROP COLUMN GRADE';
execute immediate 'ALTER TABLE CS RENAME NEWGRADE TO GRADE';
----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 createdSQL>
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 createdSQL>
可是现在成这样了
呜呜呜。
create or replace procedure chage_critical
as
chgrade varchar2(2);
curecord RECORD;
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';
elseif curecord.grade<70 then
chgrade:='D';
elseif curecord.grade<80 then
chgrade:='C';
elseif 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;
/错误
-------- --------------------------------------------------------
11/9 PLS-00103: 出现符号 "CURECORD"在需要下列之一时:
:= . ( @ % ;
符号 ":=" 被替换为 "CURECORD" 后继续。11/27 PLS-00103: 出现符号 "THEN"在需要下列之一时:
* & - + ; / at mod
remainder rem <an exponent (**)> and or || multiset13/9 PLS-00103: 出现符号 "CURECORD"在需要下列之一时:
:= . ( @ % ;
符号 ":=" 被替换为 "CURECORD" 后继续。LINE/COL ERROR
-------- --------------------------------------------------------13/27 PLS-00103: 出现符号 "THEN"在需要下列之一时:
* & - + ; / at mod
remainder rem <an exponent (**)> and or || multiset15/9 PLS-00103: 出现符号 "CURECORD"在需要下列之一时:
:= . ( @ % ;
符号 ":=" 被替换为 "CURECORD" 后继续。15/27 PLS-00103: 出现符号 "THEN"在需要下列之一时:
* & - + ; / at modLINE/COL ERROR
-------- --------------------------------------------------------
remainder rem <an exponent (**)> and or || multiset20/2 PLS-00103: 出现符号 "UPDATE"
22/2 PLS-00103: 出现符号 "END"
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;
2.UPDATE语句里的内容包含了使用动态sql添加的列,不能直接使用,必须也使用动态sql,具体请参考3l