我的oracle筆記三（系統函數和系統包使用方面）

1.十進制和十六進制轉換
(oracle 8i以后)
select to_char(125,'XXXXX') from dual
-----------
 7D
select to_char(125,'xxxxx') from dual
-----------
 7d

select to_number('7D','XXXXX') from dual
-----------
125

2. ORACLE產生隨機函數
DBMS_RANDOM.RANDOM

3、調度程序  DBMS_JOB
  
    broken       中止一個任務調度
    change       修改任務的屬性
    internal     改變間隔
    submit       任務發送到任務隊列中去
    next_date    改變任務的運行時間
    remove       刪除一個任務
    run          立即執行一個任務
    submit       提交一個任務
    user_export  任務說明
    what         改變任務運行的程序
查詢
   select * from user_job;
   建立一存儲過程
   create or replace procedure log_proc  as
     begin
      insert into test(aa) values(sysdate);
      commit;
     end;
    提交一個任務
     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> 上面程序從當前開始，間隔5秒執行一次。
      2> 如果每天幾點執行，可以寫為(比如從2004-09-13開始執行，每天7點執行)
                      next_date => to_date('13-09-2004 07:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'trunc(sysdate)+(7+24)/24')
        3> 如果是每個月幾號開始執行。比如每月2號21點執行。
         add_months(trunc(sysdate,'MONTH'),1) + 2-1 + 21/24
      
    移走任務
     begin
       dbms_job.remove(1);
     end;
    中止任務
      begin
        dbms_job.broken(1,true);
      end;
    查詢正在執行的job
    select * from dba_jobs_running
    如果運行比較慢，加
    select /*+ rule */* from dba_jobs_running
4.UTL_FILE包
 在PL/SQL 3.3以上的版本中，UTL_FILE包允許用戶通過PL/SQL讀寫操作系統文件。如下：

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中寫操作系統文件，如寫日志
可以利用utl_file包，但是，在此之前，要注意設置好Utl_file_dir初始化參數
/**************************************************************************
parameter:textContext in varchar2 日志內容
desc: ·寫日志,把內容記到服務器指定目錄下
·必須配置Utl_file_dir初始化參數，并保證日志路徑與Utl_file_dir路徑一致或者是其中一個
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_Write_log(text_context VARCHAR2)
IS
file_handle utl_file.file_type;
Write_content VARCHAR2(1024);
Write_file_name VARCHAR2(50);
BEGIN
--open file
write_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 file
IF utl_file.is_open(file_handle) THEN
utl_file.put_line(file_handle,write_content);
END IF;
--close file
utl_file.fclose(file_handle);
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF utl_file.is_open(file_handle) THEN
utl_file.fclose(file_handle);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END sp_Write_log;

5.SYS_CONTEXT的詳細用法
select
SYS_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_data
from dual

6.怎么樣在過程中暫停指定時間
DBMS_LOCK包的sleep過程
如：dbms_lock.sleep(5);表示暫停5秒。

7.怎么在Oracle中發郵件
可以利用utl_smtp包發郵件，以下是一個發送簡單郵件的例子程序
/****************************************************************************
parameter: Rcpter in varchar2 接收者郵箱
Mail_Content in Varchar2 郵件內容
desc: ·發送郵件到指定郵箱
·只能指定一個郵箱，如果需要發送到多個郵箱，需要另外的輔助程序
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_send_mail( rcpter IN VARCHAR2,
mail_content IN VARCHAR2)
IS
conn utl_smtp.connection;

--write title
PROCEDURE send_header(NAME IN VARCHAR2, HEADER IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(conn, NAME||': '|| HEADER||utl_tcp.CRLF);
END;

BEGIN
--opne connect
conn := 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 title
send_header('From', 'Oracle Database');
send_header('To', '"Recipient" ');
send_header('Subject', 'DB Info');
--write mail content
utl_smtp.write_data(conn, utl_tcp.crlf || mail_content);
--close connect
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
EXCEPTION
 WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
 BEGIN
  utl_smtp.quit(conn);
 EXCEPTION
  WHEN OTHERS THEN
   NULL;
 END;
 WHEN OTHERS THEN
 NULL;
END sp_send_mail;

8.怎么樣獲取對象的DDL語句
第三方工具就不說了主要說一下9i以上版本的dbms_metadata
<1>獲得單個對象的DDL語句
set heading off
set echo off
set feedback off
set pages off
set long 90000
select dbms_metadata.get_ddl('TABLE','TABLE_NAME','SCAME') from dual;
比如
select dbms_metadata.get_ddl('TABLE','CM_USER','AICBS') from dual;
<2>.如果獲取整個用戶的腳本，可以用如下語句
select dbms_metadata.get_ddl('TABLE',u.table_name) from user_tables u;
當然，如果是索引，則需要修改相關table到index
<3>.還有
dbms_metadata.get_xml()