############################# 公司郵箱通訊錄的更新
本存儲過程實現的功能： 把一個員工在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博客 ” ，鏈接：http://blog.itpub.net/28916011/viewspace-2108401/，如需轉載，請注明出處，否則將追究法律責任。

轉載于:http://blog.itpub.net/28916011/viewspace-2108401/

總結

以上是生活随笔為你收集整理的公司邮箱通讯录的更新的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。