#############################

公司邮箱通讯录的更新

本存储过程实现的功能：

把一个员工在tb_sta_emp表的c_gsyx的值，update到tb_sta_communication表里面的c_business_email字段一份；如果tb_sta_communication表里没有该员工的信息，就在tb_sta_communication表里面insert一条c_business_email记录

总之一句话，有就update，没有就insert

##############################

create or replace procedure gsyxtoemail

is

v_c_gsyx tb_sta_emp.c_gsyx%type;

v_c_oid tb_sta_emp.c_oid%type;

v_c_employee_id tb_sta_communication.c_employee_id%type;

v_c_business_email tb_sta_communication.c_business_email%type;

v_count_employee_id number;

v_C_OFFICE_TEL_emp tb_sta_emp.C_OFFICE_TEL%type;

v_C_EMAIL tb_sta_emp.C_EMAIL%type;

v_c_sj tb_sta_emp.c_sj%type;

v_C_OFFICE_TEL tb_sta_communication.C_OFFICE_TEL %type;

v_C_PER_EMAIL tb_sta_communication.C_PER_EMAIL%type;

v_C_MOBILE_TEL tb_sta_communication.C_MOBILE_TEL%type;

cursor c1 is select c_oid,c_gsyx,C_OFFICE_TEL,C_EMAIL,c_sj from tb_sta_emp;

begin

for i in c1

loop

v_c_oid:=i.c_oid;

v_c_gsyx:=i.c_gsyx;

v_C_OFFICE_TEL_emp:=i.C_OFFICE_TEL;

v_C_EMAIL:=i.C_EMAIL;

v_c_sj:=i.c_sj;

select count(c_employee_id) into v_count_employee_id from tb_sta_communication where c_employee_id=v_c_oid ;

if v_count_employee_id=0

then

insert into tb_sta_communication (C_OID,c_employee_id,c_business_email,C_OFFICE_TEL,C_PER_EMAIL,C_MOBILE_TEL) values (v_c_oid,v_c_oid,v_c_gsyx,v_C_OFFICE_TEL_emp,

v_C_EMAIL,v_c_sj);

elsif v_count_employee_id>0

then

update tb_sta_communication set c_business_email=v_c_gsyx ,C_OFFICE_TEL=v_C_OFFICE_TEL_emp, C_PER_EMAIL=v_C_EMAIL,C_MOBILE_TEL=v_c_sj where c_employee_id=v_c_oid;

end if;

commit;

end loop;

end;

/

结果验证：

select c_oid, c_gsyx from tb_sta_emp

select C_OID,c_employee_id,c_business_email,C_OFFICE_TEL,C_PER_EMAIL,C_MOBILE_TEL from tb_sta_communication

添加作业：

SQL> conn / as sysdba

Connected.

SQL> grant select on jobseq to TB52; 授予权限

SQL>conn TB52/xxx

SQL>var jobno number 帮定变量存数值的

SQL>print jobno 基于会话的

SQL>select sys.JOBSEQ.nextval from dual; 查看序列号 job从这里面取得号

begin

dbms_job.submit(:jobno,

'gsyxtoemail(); ', --定时执行存储过程

sysdate, --下一次时间

'sysdate+1/24/60'); --2分钟执行一次

commit; --执行commit才生效

end;

show parametrer job -- 0表示一个作业也跑不了把数据放大点表示能跑几个作业

alter system set job_queue_processes=10;

查看创建的作业：

col INTERVAL for a20

col what for a30

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

select job,LOG_USER,LAST_DATE,NEXT_DATE,INTERVAL,what from user_jobs;

#############

select * from tb_sta_emp --C_OFFICE_TEL,C_EMAIL,c_sj

select * from tb_sta_communication --C_OFFICLE_TEL,C_PER_EMAL,c_moble_tel

ITPub博客

公司邮箱通讯录的更新

最新文章