创建一个存储过程 lowest3,对公司最低收入的前三个员工进行工资上 10% 的调整手头暂时只有oracle express版本,用来学点基础语法=.=create or replace procedure lowest_test
as
begin
update emp_test set sal=sal*1.1 where empno in (select empno from (select * from emp_test order by sal desc) where rownum<=3);
commit;
end lowest_test;为啥这样写会有编译错误?
as
begin
update emp_test set sal=sal*1.1 where empno in (select empno from (select * from emp_test order by sal desc) where rownum<=3);
commit;
end lowest_test;为啥这样写会有编译错误?
(empno number(4),
sal number(7,2))insert into emp values(1,1.5)
insert into emp values(3,3.5)
insert into emp values(4,1.7)
insert into emp values(6,1.8)
2 as
3 begin
4 update emp
5 set sal=sal*1.1
6 where empno in (
7 select empno from
8 (
9 select * from emp order by sal desc
10 )
11 where rownum <=3
12 );
13 end lowest_test;
14 /过程已创建。SQL> begin lowest_test; end;
2 /PL/SQL 过程已成功完成。SQL> select * from emp; EMPNO SAL
---------- ----------
1 1.5
3 3.85
4 1.87
6 1.98
---------- ----------
1 1.5
3 3.85
4 1.87
6 1.98SQL> create or replace procedure lowest_test
2 as
3 begin
4 update emp
5 set sal=sal*1.1
6 where empno in (
7 select empno from
8 (
9 select * from emp order by sal
10 )
11 where rownum <=3
12 );
13 end lowest_test;
14 /过程已创建。SQL>
SQL> begin lowest_test; end;
2 /PL/SQL 过程已成功完成。SQL>
SQL> select * from emp; EMPNO SAL
---------- ----------
1 1.65
3 3.85
4 2.06
6 2.18SQL>
汗一个,这个错误也犯了,感谢指出=.=应该是asc才对,现在主要问题就是单条update语句能执行,但是放入存储过程-.-
警告: 创建的过程带有编译错误。SQL> show err;
PROCEDURE LOWEST_TEST 出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1 PL/SQL: SQL Statement ignored
9/17 PL/SQL: ORA-00942: table or view does not exist --这里会告诉你那行那个位置有错的。
SQL> create or replace procedure lowest_test
2 as