我的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()

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18