建立PL/SQL块,将工资最高的前N个雇员的姓名和工资插入到TOP_DOGS表
a . 使用SQL*Plus替代变量输入数字n
b. 循环取得工资最高的N个雇员的姓名和工资,并插入到TOP_DOGS表
c. 每次测试后清空TOP_DOGS表
SQL> DECLARE
2 NUM number(2):=&topn;
3 CURSOR C1 IS SELECT ENAME,SAL
4 FROM EMP ORDER BY SAL DESC;
5 BEGIN
6 FOR TOP_DOGS IN CI LOOP
7 exit when C1%ROWCOUNT=NUM;
8 END LOOP;
9 execute immediate 'truncate table TOP_GOGS ';
10 END;
11 /
ORA-06550: line 9, column 7:
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following: if
ORA-06550: line 13, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: end not pragma final instantiable order overriding static
member constructor map
a . 使用SQL*Plus替代变量输入数字n
b. 循环取得工资最高的N个雇员的姓名和工资,并插入到TOP_DOGS表
c. 每次测试后清空TOP_DOGS表
SQL> DECLARE
2 NUM number(2):=&topn;
3 CURSOR C1 IS SELECT ENAME,SAL
4 FROM EMP ORDER BY SAL DESC;
5 BEGIN
6 FOR TOP_DOGS IN CI LOOP
7 exit when C1%ROWCOUNT=NUM;
8 END LOOP;
9 execute immediate 'truncate table TOP_GOGS ';
10 END;
11 /
ORA-06550: line 9, column 7:
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following: if
ORA-06550: line 13, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: end not pragma final instantiable order overriding static
member constructor map
并且我看了下,代码也没什么语法上的错误。
可能是你执行的方式有问题。
你在sqlplus环境下执行看看。
把你的代码保存到一个.sql文件中。
然后在cmd环境下用sqlplus连接。
进入sqlplus环境后执行@你的.sql文件(路径+名字)。至于功能方面,你上面的代码肯定是不够的,至少loop中还有个插入功能。
另外,需求方面存在问题,测试后清空,那你怎么看你的插入运行效果呢?
建议在下次执行前进行清空
也就是把execute immediate ‘’需要放到loop之前
自己再试试
希望对你有帮助
declare
num number:=⊤
cursor c1 is select ename,sal from emp order by sal desc;
begin
for emp_rec in c1 loop
insert into top_dogs values emp_rec;
exit when c1%rowcount=num;
end loop;
commit; execute immediate 'truncate table top_dogs';
end;
/
NUM NUMBER(2) := &topn;
CURSOR C1 IS
SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC;
BEGIN
FOR c IN C1 LOOP
INSERT INTO TOP_GOGS (ename, sal) VALUES (c.ename, c.sal);
EXIT WHEN C1%ROWCOUNT = NUM;
END LOOP;
COMMIT;
EXECUTE IMMEDIATE 'truncate table TOP_GOGS ';
END;
/
num number:=⊤
cursor c1 is select ename,sal from emp order by sal desc;
emp_rec c1%rowtype;
begin
for emp_rec in c1 loop
insert into top_dogs values emp_rec;
exit when c1%rowcount=num;
end loop;
commit;
execute immediate 'truncate table top_dogs';修改楼上,追加定义
end;
/
//基本上可以实现楼主的功能了,但是如果输入的参数很大,我们一般需要及时提交insert。commit;语句最好写在insert紧跟后面。
num number:=&topn;
cursor c1 is SELECT num,name,sal from emps order by sal desc;
begin
for emp_rec in c1 loop
insert into t11 values emp_rec;
exit when c1%rowcount=num;
end loop;
commit; execute immediate 'truncate table t11';
end;运行没错
NUM number(2):=&topn;
CURSOR C1 IS SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC;
BEGIN
FOR emp_rec IN C1 LOOP
insert into TOP_DOGS(ENAME,sal) values(emp_rec.ENAME,emp_rec.SAL);
exit when C1%ROWCOUNT=NUM;
END LOOP;
commit;
execute immediate 'truncate table TOP_GOGS ';
END;