-- 大体如下,你试试create or replace procedure sp_copydata
as
begin
delete emp1 ;
insert into emp1
select * from emp
where salary > 5000 -- 工资大 5000
and mod(empno) = 1 -- mod 取余
end ;
as
begin
delete emp1 ;
insert into emp1
select * from emp
where salary > 5000 -- 工资大 5000
and mod(empno) = 1 -- mod 取余
end ;
create procedure sp_copydata
as
begin
insert into emp1(ID,name,salary)
select ID,name,salary from emp
where mod(ID,2)<>0 and salary>5000;
end;
但是在SQL*Plus中创建该过程时有报错,execute 时也会报SP_COPYDATA is invalid 错。代码貌似没错吧?我创的表也OK,不知为何
-- 这是完整的例子,你参考一下。
SQL> create table emp(ID int , name varchar2(20) , salary int) ;表已创建。SQL>
SQL> begin
2 insert into emp values(1,'zhangsan',1000) ;
3 insert into emp values(2,'lisi' , 6000) ;
4 insert into emp values(3,'wangwu',7000) ;
5 insert into emp values(4,'maliu',8000) ;
6 end ;
7 /PL/SQL 过程已成功完成。SQL>
SQL> create table emp1 as
2 select * from emp where 1 = 0 ;表已创建。SQL>
SQL> create procedure sp_copydata
2 as
3 begin
4 insert into emp1(ID,name,salary)
5 select ID,name,salary from emp
6 where mod(ID,2)<>0 and salary>5000;
7 end;
8 /过程已创建。SQL>
SQL> begin
2 sp_copydata;
3 end ;
4 /PL/SQL 过程已成功完成。SQL>
SQL> select * from emp ; ID NAME SALARY
---------- -------------------- ----------
1 zhangsan 1000
2 lisi 6000
3 wangwu 7000
4 maliu 8000SQL> select * from emp1 ; ID NAME SALARY
---------- -------------------- ----------
3 wangwu 7000SQL>
SQL> drop procedure sp_copydata ;过程已删除。SQL> drop table emp purge ;表已删除。SQL> drop table emp1 purge ;表已删除。SQL>