Tham khảo tài liệu 'oracle pl/sql for dummies phần 10', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | 378 Part VI The Part of Tens Unless you re in the debugging or development mode never use an exception I I handler like this especially in production instances of a system. All exception handlers that have WHEN OTHERS without additional activity you might need to have that exception should look like this function f_assignManager_tx i_empNo NUMBER i_mgr NUMBER return VARCHAR2 is v_job_tx VARCHAR2 10 begin Update employee update emp set mgr i_mgr where empNo i_empNo returning job into v_job_tx -- If person is managing analysis - there will be no -- commissions. Give 5 raise per person to the manager if v_job_tx ANALYST then update emp set sal sal where empNo i_mgr end if return OK exception when others then p_log f_assignManager_tx i_empNo i_mgr ERROR sqlerrm return ERROR end Here you aren t raising an exception if something goes wrong but instead returning ERROR rather than OK and logging a real error see the P_LOG procedure in Chapter 15 . You can use this logic if because of front-end restrictions you can t throw Oracle exceptions for example in a Web-based environment . This technique is a cleaner way of notifying the front end that something has gone wrong without destroying performance and it also provides useful debugging information. This exception handler includes a call to the logging routine to which you are passing the current function name its parameters and the SQL error message. This is the minimum information that should be logged but you could add the current user the client s IP address global parameter settings and other data. Don t hesitate to add a lot of information to debugging messages. When you re trying to identify and solve a problem you never know what data you might need. These debugging statements shouldn t be executed at all but even if they are executed the performance impact is negligible. Chapter 16 Ten Common Mistakes to Avoid in PL SQL 379 Forgetting to Handle NULL Values .
