1.十進(jìn)制和十六進(jìn)制轉(zhuǎn)換(Oracle 8i以后)select to_char(125,'XXXXX') from dual-----------7Dselect to_char(125,'xxxxx') from dual-----------7dselect to_number('7D','XXXXX') from dual-----------1252. ORACLE產(chǎn)生隨機(jī)函數(shù)DBMS_RANDOM.RANDOM3、調(diào)度程序; DBMS_JOB broken;;;;中止一個(gè)任務(wù)調(diào)度 change;;;;修改任務(wù)的屬性 internal;;改變間隔 submit;;;;任務(wù)發(fā)送到任務(wù)隊(duì)列中去 next_date;改變?nèi)蝿?wù)的運(yùn)行時(shí)間 remove;;;;刪除一個(gè)任務(wù) run; 立即執(zhí)行一個(gè)任務(wù) submit;;;;提交一個(gè)任務(wù) user_eXPort; 任務(wù)說(shuō)明 what 改變?nèi)蝿?wù)運(yùn)行的程序查詢(xún) select * from user_job; 建立一存儲(chǔ)過(guò)程 create or replace procedure log_proc; as begin insert into test(aa) values(sysdate); commit; end; 提交一個(gè)任務(wù) declare job_num; number; begin dbms_job.submit(job_num,'log_proc;',sysdate,sysdate+5/(24*60*60),false); dbms_output.put_line('Job numer='to_char(job_num)); end; ;1> 上面程序從當(dāng)前開(kāi)始，間隔5秒執(zhí)行一次。 ;2> 假如天天幾點(diǎn)執(zhí)行，可以寫(xiě)為(比如從2004-09-13開(kāi)始執(zhí)行，天天7點(diǎn)執(zhí)行) next_date => to_date('13-09-2004 07:00:00', 'dd-mm-yyyy hh24:mi:ss'), interval => 'trunc(sysdate)+(7+24)/24') 3> 假如是每個(gè)月幾號(hào)開(kāi)始執(zhí)行。比如每月2號(hào)21點(diǎn)執(zhí)行。 ;add_months(trunc(sysdate,'MONTH'),1) + 2-1 + 21/24 ; 移走任務(wù) begin dbms_job.remove(1); end; 中止任務(wù) begin dbms_job.broken(1,true); ;;;end; 查詢(xún)正在執(zhí)行的job ;select * from dba_jobs_running ;假如運(yùn)行比較慢，加 ;select /*+ rule */* from dba_jobs_running4.UTL_FILE包在PL/SQL 3.3以上的版本中，UTL_FILE包答應(yīng)用戶(hù)通過(guò)PL/SQL讀寫(xiě)操作系統(tǒng)文件。如下： DECALRE FILE_HANDLE UTL_FILE.FILE_TYPE; BEGIN FILE_HANDLE:=UTL_FILE.FOPEN('C:','TEST.TXT','A'); UTL_FILE.PUT_LINE(FILE_HANDLE,'HELLO,IT iS A TEST TXT FILE'); UTL_FILE.FCLOSE(FILE_HANDLE); END;比如：怎么樣在Oracle中寫(xiě)操作系統(tǒng)文件，如寫(xiě)日志可以利用utl_file包，但是，在此之前，要注重設(shè)置好Utl_file_dir初始化參數(shù)/***************************************************parameter:textContext in varchar2 日志內(nèi)容desc: ·寫(xiě)日志,把內(nèi)容記到服務(wù)器指定目錄下·必須配置Utl_file_dir初始化參數(shù)，并保證日志路徑與Utl_file_dir路徑一致或者是其中一個(gè)****************************************************/ CREATE OR REPLACE PROCEDURE sp_Write_log(text_context VARCHAR2)ISfile_handle utl_file.file_type;Write_content VARCHAR2(1024);Write_file_name VARCHAR2(50);BEGIN--open filewrite_file_name := 'db_alert.log';file_handle := utl_file.fopen('/u01/logs',write_file_name,'a');write_content := to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')''text_context;--write fileIF utl_file.is_open(file_handle) THENutl_file.put_line(file_handle,write_content);END IF;--close fileutl_file.fclose(file_handle);EXCEPTIONWHEN OTHERS THENBEGINIF utl_file.is_open(file_handle) THENutl_file.fclose(file_handle);END IF;EXCEPTIONWHEN OTHERS THENNULL;END;END sp_Write_log;5.SYS_CONTEXT的具體用法selectSYS_CONTEXT('USERENV','TERMINAL') terminal,SYS_CONTEXT('USERENV','LANGUAGE') language,SYS_CONTEXT('USERENV','SESSIONID') sessionid,SYS_CONTEXT('USERENV','INSTANCE') instance,SYS_CONTEXT('USERENV','ENTRYID') entryid,SYS_CONTEXT('USERENV','ISDBA') isdba,SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,SYS_CONTEXT('USERENV','NLS_DATE_formAT') nls_date_format,SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,SYS_CONTEXT('USERENV','CURRENT_USER') current_user,SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,SYS_CONTEXT('USERENV','SESSION_USER') session_user,SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,SYS_CONTEXT('USERENV','DB_NAME') db_name,SYS_CONTEXT('USERENV','HOST') host,SYS_CONTEXT('USERENV','OS_USER') os_user,SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name, SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_datafrom dual6.怎么樣在過(guò)程中暫停指定時(shí)間DBMS_LOCK包的sleep過(guò)程如：dbms_lock.sleep(5);表示暫停5秒。7.怎么在Oracle中發(fā)郵件可以利用utl_smtp包發(fā)郵件，以下是一個(gè)發(fā)送簡(jiǎn)單郵件的例子程序/****************************************************parameter: Rcpter in varchar2 接收者郵箱Mail_Content in Varchar2 郵件內(nèi)容desc: ·發(fā)送郵件到指定郵箱·只能指定一個(gè)郵箱，假如需要發(fā)送到多個(gè)郵箱，需要另外的輔助程序*****************************************************/CREATE OR REPLACE PROCEDURE sp_send_mail( rcpter IN VARCHAR2,mail_content IN VARCHAR2)IS conn utl_smtp.connection;--write titlePROCEDURE send_header(NAME IN VARCHAR2, HEADER IN VARCHAR2) ASBEGINutl_smtp.write_data(conn, NAME': ' HEADERutl_tcp.CRLF);END;BEGIN--opne connectconn := utl_smtp.open_connection('smtp.com');utl_smtp.helo(conn, 'oracle');utl_smtp.mail(conn, 'oracle info');utl_smtp.rcpt(conn, Rcpter);utl_smtp.open_data(conn);--write titlesend_header('From', 'Oracle Database');send_header('To', ''Recipient' ');send_header('Subject', 'DB Info');--write mail contentutl_smtp.write_data(conn, utl_tcp.crlf mail_content);--close connect utl_smtp.close_data(conn);utl_smtp.quit(conn);EXCEPTIONWHEN utl_smtp.transient_error OR utl_smtp.permanent_error THENBEGINutl_smtp.quit(conn);EXCEPTIONWHEN OTHERS THENNULL;END;WHEN OTHERS THENNULL;END sp_send_mail;8.怎么樣獲取對(duì)象的DDL語(yǔ)句第三方工具就不說(shuō)了主要說(shuō)一下9i以上版本的dbms_metadata<1>獲得單個(gè)對(duì)象的DDL語(yǔ)句set heading offset echo offset feedback offset pages offset long 90000select dbms_metadata.get_ddl('TABLE','TABLE_NAME','SCAME') from dual;比如select dbms_metadata.get_ddl('TABLE','CM_USER','AICBS') from dual;<2>.假如獲取整個(gè)用戶(hù)的腳本，可以用如下語(yǔ)句select dbms_metadata.get_ddl('TABLE',u.table_name) from user_tables u;當(dāng)然，假如是索引，則需要修改相關(guān)table到index<3>.還有dbms_metadata.get_XML()