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

公司郵箱通訊錄的更新

本儲存過程實現的功能：

把一個員工在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

公司郵箱通訊錄的更新

相關文章