--测试表Tt1
create table t1 as
(
select '123' phone,to_date('2000-02-01 08:10','yyyy-mm-dd hh24:mi') dt from dual union all
select '123',to_date('2000-02-01 08:30','yyyy-mm-dd hh24:mi') from dual union all
select '456',to_date('2000-02-02 08:10','yyyy-mm-dd hh24:mi') from dual union all
select '456',to_date('2000-02-02 08:30','yyyy-mm-dd hh24:mi') from dual
);
--测试表t2
create table t2 as
select * from t1
where 1=0;
--添加数据
insert into t2
select * from t1 t_1
where exists(
select 1 from t1 t_2
where t_1.phone=t_2.phone and
t_1.dt>t_2.dt);
select phone,to_char(dt,'yyyy-mm-dd hh24:mi') dt from t2;
PHONE DT
----- ----------------
123 2000-02-01 08:30
456 2000-02-02 08:30
--根据员工姓名,查看员工所在部门和工作
create or replace procedure pro_show_employee(ename_in in emp.ename%type)
as
v_sign number;
v_job emp.job%type;
v_dname dept.dname%type;
begin
select 1 into v_sign
from dual
where exists(select count(*) from emp where ename=ename_in);
if v_sign=1 then
select e.job,d.dname into v_job,v_dname
from emp e,dept d
where ename=ename_in and
e.deptno=d.deptno;
dbms_output.put_line('指定雇员所在的部门为:'||v_dname||' 雇员职位:'||v_job);
end if;
exception
when others then
dbms_output.put_line('no data found');
end pro_show_employee;SQL> exec pro_show_employee('SCOTT');
指定雇员所在的部门为:RESEARCH 雇员职位:ANALYST
PL/SQL procedure successfully completedSQL> select dept.dname,emp.job
2 from dept,emp
3 where dept.deptno=emp.deptno and
4 emp.ename='SCOTT';DNAME JOB
-------------- ---------
RESEARCH ANALYSTSQL> exec pro_show_employee('yeexun');
no data found
PL/SQL procedure successfully completedhttp://blog.csdn.net/BOBO12082119/archive/2011/04/13/6321197.aspx
1、可在系统中设定排程实现
2、Oracle Job Oracle JOB 用法小结