用的是oracle 10g中scott用户下的dept表做的实验
要求编写一个存储过程mypro
Ⅰ把dept表中deptno=10的数据,存到dept10中
Ⅱ把dept表中deptno=20的数据,存到dept20中
Ⅲ把dept表中deptno=30的数据,存到dept30中
Ⅳ执行该存储过程
create or replace procedure mypro
as
cursor cur_deptno is select * from dept;
deptInfo dept%rowtype;
begin
for deptInfo in cur_deptno loop
if deptInfo.deptno=10 then
insert into dept10 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
else if deptInfo.deptno=20 then
insert into dept20 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
else if deptInfo.deptno=30 then
insert into dept30 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
end if;
end loop;
end;
提示错误:PLS-00103:出现符号“loop”在需要下列之一时:if
各位高手给看看,我这种写法哪错啦,谢谢
要求编写一个存储过程mypro
Ⅰ把dept表中deptno=10的数据,存到dept10中
Ⅱ把dept表中deptno=20的数据,存到dept20中
Ⅲ把dept表中deptno=30的数据,存到dept30中
Ⅳ执行该存储过程
create or replace procedure mypro
as
cursor cur_deptno is select * from dept;
deptInfo dept%rowtype;
begin
for deptInfo in cur_deptno loop
if deptInfo.deptno=10 then
insert into dept10 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
else if deptInfo.deptno=20 then
insert into dept20 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
else if deptInfo.deptno=30 then
insert into dept30 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
end if;
end loop;
end;
提示错误:PLS-00103:出现符号“loop”在需要下列之一时:if
各位高手给看看,我这种写法哪错啦,谢谢
create or replace procedure mypro
as
cursor cur_deptno is select * from dept;
--deptInfo dept%rowtype; 这个去掉begin
for deptInfo in cur_deptno loop
if deptInfo.deptno=10 then
insert into dept10 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
else if deptInfo.deptno=20 then
insert into dept20 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
else if deptInfo.deptno=30 then
insert into dept30 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
end if;
end loop;
end;
2 as
3 cursor cur_deptno is select * from dept;
4 deptInfo dept%rowtype;
5 begin
6 for deptInfo in cur_deptno loop
7 if deptInfo.deptno=10 then
8 insert into dept10 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
9 elsif deptInfo.deptno=20 then
10 insert into dept20 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
11 elsif deptInfo.deptno=30 then
12 insert into dept30 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
13 end if;
14 end loop;
15 end;
16 /过程已创建。SQL> exec mypro;PL/SQL 过程已成功完成。SQL> select * from dept10; DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK已选择 1 行。SQL> select * from dept20; DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
20 RESEARCH DALLAS已选择 1 行。SQL> select * from dept30; DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
30 SALES CHICAGO已选择 1 行。
2 as
3 cursor cur_deptno is select * from dept;
4 deptInfo dept%rowtype;
5 begin
6 for deptInfo in cur_deptno loop
7 if deptInfo.deptno=10 then
8 insert into dept10 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
9 elsif deptInfo.deptno=20 then
10 insert into dept20 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
11 elsif deptInfo.deptno=30 then
12 insert into dept30 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
13 end if;
14 end loop;
15 end;
16 /过程已创建。SQL> exec mypro;PL/SQL 过程已成功完成。SQL> select * from dept10; DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK已选择 1 行。SQL> select * from dept20; DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
20 RESEARCH DALLAS已选择 1 行。SQL> select * from dept30; DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
30 SALES CHICAGO已选择 1 行。SQL> rollback;回退已完成。SQL> select * from dept10;未选定行
as
cursor cur_deptno is select * from dept;
deptInfo dept%rowtype;
begin
for deptInfo in cur_deptno loop
if deptInfo.deptno=10 then
insert into dept10 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
elsif deptInfo.deptno=20 then
insert into dept20 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
elsif deptInfo.deptno=30 then
insert into dept30 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
end if;
end loop;
commit;
end;
/
等待正解...
而且我的脚本在command窗口下运行提示:Warning: Procedure created with compilation errors
as
cursor cur_deptno is select * from dept;
deptInfo dept%rowtype;
begin
for deptInfo in cur_deptno loop
if deptInfo.deptno=10 then
insert into dept10 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
elsif deptInfo.deptno=20 then
insert into dept20 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
elsif deptInfo.deptno=30 then
insert into dept30 values(deptInfo.deptno,deptInfo.dname,deptInfo.loc);
end if;
end loop;
commit;
end;
/
-- 如果不加提交,看我2楼的操作!
SQL> rollback;回退已完成。SQL> select * from dept10;未选定行