寫Oracle Stored Procedure時, 也和一般程式開發一樣, 也會遇到邏輯例外而需要控制。 1. Oracle已存在Exception例外處理 DECLARE l_row abc%ROWTYPE; BEGIN BEGIN select * INTO l_row from abc where abc01='123@asd'; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line('--> NO_DATA_FOUND err is ' || SQLERRM); WHEN OTHERS THEN DBMS_OUTPUT.put_line('--> OTHER err is ' || SQLERRM); END; END; ** 則會顯示 "--> NO_DATA_FOUND err is ORA-01403: no data found" ** 如果沒有處理"WHEN NO_DATA_FOUND THEN", 則會跑到"OTHERS"中 1.1 Oracle已存在Exception例外處理(但自定Exception名稱) DECLARE l_row abc%ROWTYPE; my_no_data_found_exp EXCEPTION; PRAGMA EXCEPTION_INIT(my_no_data_found_exp, -01403); --no data found Exception BEGIN BEGIN select * INTO l_row from abc where abc01='123@asd'; EXCEPTION WHEN my_no_data_found_exp THEN DBMS_OUTPUT.put_line('--> NO_DATA_FOUND err is ' || SQLERRM); WHEN OTHE
1. 設定初始化參數 job_queue_processes : oracle 允許執行的最大任務數量 specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per instance (J000, ... J999). 最大值為 1000 sql> alter system set job_queue_processes=n; (n>0) 2. 查詢背景執行程序 sql> select name, description from v$bgprocess 3. 建立function or Stored Procedure 4. 執行程序 declare jobno number; begin dbms_job.submit(jobno, 'sp_test_alex;', sysdate, 'sysdate+1/1440'); dbms_output.put_line(jobno); commit; end; / 另一種寫法 declare jobno number; begin dbms_job.submit( job => :jobno, what => 'SP_SYNC_MES_ITEM;', next_date => SYSDATE, interval => 'SYSDATE + 10/1440' ); dbms_output.put_line(jobno); commit; end; 5. 查詢執行程序 sql> select * from user_jobs; 6. 移除程序 begin dbms_job.remove(141); end; / 7. 暫停程序 begin dbms_job.brok
留言
張貼留言