| 以下程序包对如何执行一段UPDATE语句进行了演示：CREATEORREPLACEPACKAGEBODYCUX_TESTISexp_warningEXCEPTION;exp_errorEXCEPTION;--====================--OutputLog.日志输出--====================PROCEDURElog(p_infoVARCHAR2)ISBEGIN--fnd_file.put_line(fnd_file.log,p_info);dbms_output.put_line(p_info);EXCEPTIONWHENOTHERSTHEN--fnd_file.put_line(fnd_file.log,'ExceptioninCUX_TEST.log:'||SQLERRM);dbms_output.put_line('ExceptioninCUX_TEST.log:'||SQLERRM);RAISEexp_warning;ENDlog;--================================--ProcessRequest.程序内部主应用--=================================PROCEDUREprocess_request(p_table_nameINVARCHAR2,p_key_nameINVARCHAR2,p_interface_idINNUMBER,p_column_nameINVARCHAR2,p_valueINVARCHAR2)ISl_exe_sqlVARCHAR2(4000);BEGINl_exe_sql:='UPDATE'||p_table_name||'SET'||p_column_name||'='||p_value||'WHERE'||p_key_name||'='||p_interface_id;log('执行SQL:'||l_exe_sql);EXECUTEIMMEDIATEl_exe_sql;--动态执行DDL语句EXCEPTIONWHENOTHERSTHENlog('ExceptioninCUX_TEST.process_request:'||SQLERRM);RAISEexp_warning;ENDprocess_request;--==============--MainProcedure--==============PROCEDUREmain(errbufOUTVARCHAR2,retcodeOUTVARCHAR2,p_table_nameINVARCHAR2,p_key_nameINVARCHAR2,p_interface_idINNUMBER,p_column_nameINVARCHAR2,p_valueINVARCHAR2)ISl_warning_flagNUMBER;l_error_flagNUMBER;BEGINprocess_request(p_table_name,p_key_name,p_interface_id,p_column_name,p_value);IFl_warning_flag=1THENretcode:=1;ELSIFl_error_flag=1THENretcode:=2;ELSEretcode:=0;ENDIF;EXCEPTIONWHENexp_warningTHEN--ROLLBACK;retcode:=1;WHENexp_errorTHEN--ROLLBACK;retcode:=2;WHENOTHERSTHENROLLBACK;retcode:=2;log('ExceptioninCUX_TEST.main:'||SQLERRM);END;ENDCUX_TEST;(责任编辑：润笔) |
