Oracle某些函数学习记录（2018-05-21备忘）

发表于 | 分类于 | | 浏览 次
字数统计: 3.2k 字 | 阅读时长 ≈ 14 分钟

oracle12c

最近需要了解一下oracle的有关函数，故寻觅一番，总结如下。

更多内容参考Oracle官网。
以下参考网络资源学习、摘录总结。

本文使用的本地oracle环境对参考资源进行了学习，当前版本为：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
         0

PL/SQL Release 12.2.0.1.0 - Production
         0

CORE    12.2.0.1.0      Production
         0


BANNER
--------------------------------------------------------------------------------
    CON_ID
----------
TNS for Linux: Version 12.2.0.1.0 - Production
         0

NLSRTL Version 12.2.0.1.0 - Production
         0

1. DBMS_OBFUSCATION_TOOLKIT.MD5和Utl_Raw.Cast_To_Raw

DBMS_OBFUSCATION_TOOLKIT.MD5是MD5编码的数据包函数，可以直接在SQL中进行调用。

1
2
3
4
5
SQL> select Utl_Raw.Cast_To_Raw(sys.dbms_obfuscation_toolkit.md5(input_string => '111')) from dual;

UTL_RAW.CAST_TO_RAW(SYS.DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING=>'111'))
--------------------------------------------------------------------------------
698D51A19D8A121CE581499D7B701668

其结果返回的字串为RAW类型，要正确显示的话，需要经过Utl_Raw.Cast_To_Raw转换。
关于RAW类型，可详见本文第2节说明。

2. RAW类型

oracle中用于保存位串的数据类型是RAW或者LONG RAW。
RAW类似于CHAR，声明方式为RAW(L)，L为单位，以字节表示，作为数据库列最大为2000，作为变量最大32767字节。
LONG RAW，类似于LONG，作为数据库列最大存储2G字节的数据，作为变量最大32760字节
RAW类型的好处就是：在网络中的计算机之间传输 RAW 数据时，或者使用 Oracle 实用程序将 RAW 数据从一个数据库移到另一个数据库时，Oracle服务器不执行字符集转换。存储实际列值所需要的字节数大小随每行大小而异，最多为 2000字节。可能这样的数据类型在数据库效率上会提高，而且对数据由于字符集的不同而导致的不一致的可能性在这边也排除了。
RAW保存的为16进制数，对应每个字符的ASCII码。常见的两个函数为utl_raw.cast_to_raw([varchar2]) 和utl_raw.cast_to_varchar2([raw])。如下：

1
2
3
4
5
6
7
8
9
10
11
12
SQL> select utl_raw.cast_to_raw('甲骨文') name from dual;

NAME
--------------------------------------------------------------------------------
E794B2E9AAA8E69687

SQL> 
SQL> select utl_raw.cast_to_varchar2('E794B2E9AAA8E69687') name  from dual;

NAME
--------------------------------------------------------------------------------
甲骨文

题外话：使用SQL语句时，注意SERVER端和CLIENT端的字符集得一致，否则查询可能中文乱码。
SERVER端：

1
2
3
4
5
SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_CHINA.AL32UTF8

CLIENT终端调整为显示一致：(如下临时调整)

1
export NLS_LANG="AMERICAN_CHINA.AL32UTF8"

调整之后，对应终端能够正常显示中文。

上述1,2节内容亦可参照以下链接：
https://blog.csdn.net/lwei_998/article/details/6070689
https://blog.csdn.net/john2522/article/details/8124087
(或者https://blog.csdn.net/afzaici/article/details/51669495
http://blog.chinaunix.net/uid-90674-id-2436668.html
)

3. XMLTYPE操作，学习extract()和extractvalue()

3.1 特性概述

对于文件等复杂且大体积的数据对象，Oracle通常采用LOB类型的变量来进行存储。对于XML数据文件，Oracle提供了XMLTYPE的数据类型。xmltype提供了适合的保存、检索和操作的支持。以下为一些特性介绍：
作为xmltype，Oracle会在数据表上建立一个clob类型的系统列，用于协助保存数据。
可参考“xmltype类型浅析”一文：
https://www.linuxidc.com/Linux/2017-03/141678.htm
对于LOB类型，可参考“Oracle LOB类型介绍”一文：
https://blog.csdn.net/bbliutao/article/details/19707169

3.2 XMLTYPE简单操作实例

3.2.1 创建test.xml文件

文件路径及名称：/home/oracle/xml/test.xml
xml格式文件，文件内容如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?xml version="1.0" encoding="UTF-8" ?>
<collection xmlns="">
  <record>
    <leader>-----nam0-22-----^^^450-</leader>
    <datafield tag="200" ind1="1" ind2=" ">
      <subfield code="a">抗震救灾</subfield>
       <subfield code="f">奥运会</subfield>
    </datafield>
    <datafield tag="209" ind1=" " ind2=" ">
      <subfield code="a">经济学</subfield>
       <subfield code="b">计算机</subfield>
       <subfield code="c">10001</subfield>
       <subfield code="d">2005-07-09</subfield>
    </datafield>
    <datafield tag="610" ind1="0" ind2=" ">
       <subfield code="a">计算机</subfield>
       <subfield code="a">笔记本</subfield>
    </datafield>
  </record>
</collection>

3.2.2 创建存放XML文件的表

建表

1
2
3
4
5
6
7
SQL> create table xmlexample(
    ID varchar2(60),
    name varchar2(60),
    data xmltype   
    );

Table created.

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
SQL> create directory XMLPATH_TEST as '/home/oracle/xml';

Directory created.

insert into xmlexample(id,name,data)
values(sys_guid(),'my document',
        xmltype
        (
         bfilename('XMLPATH_TEST','test.xml'),
         nls_charset_id('AL32UTF8')
        )
);

查询插入结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> select * from xmlexample;

ID
------------------------------------------------------------
NAME
------------------------------------------------------------
DATA
--------------------------------------------------------------------------------
6CB32CE193E2D536E055000000000001
my document
<?xml version="1.0" encoding="UTF-8"?>
<collection xmlns="">
  <record>
    <lea
SQL>

3.2.3 extractvalue()函数的使用

Oracle提供对XML文件的检索功能（extractvalue），extractvalue只能返回一个节点的一个值，具体操作方法如下：

1
2
3
4
5
6
7
8
9
10
11
SQL> select id,name,extractvalue(x.data,'/collection/record/leader') as A from xmlexample x;

ID
------------------------------------------------------------
NAME
------------------------------------------------------------
A
--------------------------------------------------------------------------------
6CB32CE193E2D536E055000000000001
my document
-----nam0-22-----^^^450-

如果该节点有两个值，则系统提示错误。

3.2.4 extract()函数的使用

如果想查询所有subfield的值就要用到extract()，它可以返回一个节点下的所有值。操作如下：

1
2
3
4
5
6
7
8
9
10
11
12
SQL> select id,name, extract(x.data,'/collection/record/datafield/subfield') as A from xmlexample x;

ID
------------------------------------------------------------
NAME
------------------------------------------------------------
A
--------------------------------------------------------------------------------
6CB32CE193E2D536E055000000000001
my document
<subfield code="a">抗震救灾</subfield>
<subfield code="f">奥运会</subfiel

可以看到它返回的是XML格式的。如果我们想只返回它的值就要是用两个函数了。

3.2.5 table和XMLSequence

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> select extractValue(value(i),'/subfield') xx 
  2   from xmlexample x,
  3   table(XMLSequence(extract(x.data,'/collection/record/datafield/subfield'))) i;

XX
--------------------------------------------------------------------------------
抗震救灾
奥运会
经济学
计算机
10001
2005-07-09
计算机
笔记本

8 rows selected.

SQL>

3.2.6 检索出特定的节点的特定值

有时候我们在实际操作的时候并不是检索出所有值，而是根据条件查询出我们所需要的信息。如果我们想检索出

1
2
<datafield tag="209" ind1=" " ind2=" ">
<subfield code="a">经济学</subfield>

中的值-经济学
操作如下：

1
2
3
4
5
6
7
8
9
10
11
12
SQL> select id,name,extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A 
  2  from xmlexample x;

ID
------------------------------------------------------------
NAME
------------------------------------------------------------
A
--------------------------------------------------------------------------------
6CB32CE193E2D536E055000000000001
my document
经济学

3.2.7 XMLTYPE小结

Oracle对于XMLType的操作有很多种，还要靠大家自己去发现。数据库对XML的检索就是把XML的节点当作一个列来检索，而不同的是表里装的是二维的数据，而XML中可以装N维。还有就是，表中列不存在就会提示无效标识符，如果节点不存在，则检索出NULL，不会报错。所以，对与XML文件的操作通常是通过视图来完成。

以上学习内容参照：https://www.cnblogs.com/millen/archive/2011/12/28/2304410.html
关于Oracle中XML函数的接口介绍，可参见下文：
http://huangronaldo.iteye.com/blog/1457567

4. dbms_job用法

dbms_job，用于安排和管理作业队列，通过作业控制，使oracle数据库定期执行特定的任务。

4.1 dbms_job涉及到的知识点

1、创建job
variable jobno number;
dbms_job.submit(:jobno, —-job号
‘your_procedure;’,—-执行的存储过程, ‘;’不能省略
next_date, —-下次执行时间
‘interval’ —-每次间隔时间，interval以天为单位
);
–- 系统会自动分配一个任务号jobno。

2、删除job: dbms_job.remove(jobno);

3、修改要执行的操作: job:dbms_job.what(jobno, what);

4、修改下次执行时间：dbms_job.next_date(jobno, next_date);

5、修改间隔时间：dbms_job.interval(jobno, interval);

6、启动job: dbms_job.run(jobno);

7、停止job: dbms.broken(jobno, broken, nextdate); –broken为boolean值

4.2 初始化相关参数job_queue_processes

1、job_queue_process表示oracle能够并发的job的数量，当job_queue_process值为0时表示全部停止oracle的job。

2、查看job_queue_processes参数
方法一：
show parameter job_queue_process;
方法二：
select * from v$parameter where name=’job_queue_processes’;

3、修改job_queue_processes参数（感觉非必须，初始应该有合理值，mark察其用法 by xj）
alter system set job_queue_processes = 10;

4.3 user_jobs表结构

字段（列） 类型 描述
job number 任务的唯一标示号
log_user varchar2(30) 提交任务的用户
priv_user varchar2(30) 赋予任务权限的用户
schema_user varchar2(30) 对任务作语法分析的用户模式
last_date date 最后一次成功运行任务的时间
last_sec varchar2(8) 如hh24:mm:ss格式的last_date日期的小时，分钟和秒
this_date date 正在运行任务的开始时间，如果没有运行任务则为null
this_sec varchar2(8) 如hh24:mm:ss格式的this_date日期的小时，分钟和秒
next_date date 下一次定时运行任务的时间

4.4 一个dbms_job使用的具体案例

4.4.1 在plsql中创建表

1
2
3
4
5
6
create table tt(
 id varchar2(30),
 name varchar2(30)
);

Table created.

4.4.2 在plsql中创建存储过程

1
2
3
4
5
6
7
8
create or replace procedure proce_t is
begin
 insert into tt(id, name) values('1', to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
 commit;
end proce_t;
/

Procedure created.

4.4.3 创建job任务(1分钟执行一次)

1
2
3
4
5
6
7
8
variable jobno number;
begin
 dbms_job.submit(:jobno,'proce_t;', sysdate, 'sysdate+1/24/60');
 commit;
end;
/

PL/SQL procedure successfully completed.

4.4.4 跟踪任务的情况(查看任务队列)

1
2
3
4
5
6
7
SQL> select job, next_date, next_sec, failures, broken from user_jobs;

       JOB NEXT_DATE          NEXT_SEC                           FAILURES B
---------- ------------------ -------------------------------- ---------- -
         1 21-MAY-18          00:26:45                                  0 N

SQL>

查看定时任务执行情况。

1
2
3
4
5
6
7
SQL> select * from tt;

ID                             NAME
------------------------------ ------------------------------
1                              2018-05-21 00:25:45
1                              2018-05-21 00:26:50
1                              2018-05-21 00:27:55

4.4.5 停止定时任务

4.4.5.1 查看定时任务的job号

1
2
3
4
5
6
7
SQL> select job, next_date, next_sec, failures, broken from user_jobs;

       JOB NEXT_DATE          NEXT_SEC                           FAILURES B
---------- ------------------ -------------------------------- ---------- -
         1 21-MAY-18          00:30:00                                  0 N

SQL>

4.4.5.2 停止一个已启动的定时任务

1
2
3
4
5
6
begin
 dbms_job.broken(1, true, sysdate);
 commit;
end;
/
PL/SQL procedure successfully completed.

4.4.5.3 查看定时任务是否已停止成功

1
2
3
4
5
6
7
SQL> select job, next_date, next_sec, failures, broken from user_jobs;

       JOB NEXT_DATE          NEXT_SEC                           FAILURES B
---------- ------------------ -------------------------------- ---------- -
         1 01-JAN-00          00:00:00                                  0 Y

SQL>

broken值为y，表示定时任务已停止。

4.4.6 启动定时任务

4.4.6.1 查看停止定时任务

1
2
3
4
5
6
7
SQL> select job, next_date, next_sec, failures, broken from user_jobs;

       JOB NEXT_DATE          NEXT_SEC                           FAILURES B
---------- ------------------ -------------------------------- ---------- -
         1 01-JAN-00          00:00:00                                  0 Y

SQL>

broken值为y，表示定时任务已停止。

4.4.6.2 启动定时任务

1
2
3
4
5
6
begin
 dbms_job.run(1);
 commit;
end;
/
PL/SQL procedure successfully completed.

4.4.6.3 查看定时任务是否已启动

1
2
3
4
5
6
7
SQL> select job, next_date, next_sec, failures, broken from user_jobs;

       JOB NEXT_DATE          NEXT_SEC                           FAILURES B
---------- ------------------ -------------------------------- ---------- -
         1 21-MAY-18          00:34:40                                  0 N

SQL>

broken值为n，表示定时任务启动成功。

4.4.7 查看进程数

1
2
3
4
5
6
SQL> show parameter job_queue_processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     4000
SQL>

必须大于0，否则执行下面的命令修改：

1
alter system set job_queue_processes=10;

4.4.8 再创建一个任务(每5分钟执行一次)

创建

1
2
3
4
5
6
variable jobno number;
begin
 dbms_job.submit(:jobno, 'proce_t;', sysdate, 'sysdate+1/24/12'); --interval是以天为单位的
 commit;
end;
/

执行

1
2
3
4
5
6
7
8
SQL> select job,next_date,next_sec,failures,broken from user_jobs;

       JOB NEXT_DATE          NEXT_SEC                           FAILURES B
---------- ------------------ -------------------------------- ---------- -
         1 21-MAY-18          00:36:50                                  0 N
         2 21-MAY-18          00:41:25                                  0 N

SQL>

4.5 job运行时间的总结

1:每分钟执行
Interval => TRUNC(sysdate,’mi’) + 1/(2460)
2:每天定时执行
例如：每天的凌晨1点执行
Interval => TRUNC(sysdate) + 1 +1/(24)
3:每周定时执行
例如：每周一凌晨1点执行
Interval => TRUNC(next_day(sysdate,’星期一’))+1/24
4:每月定时执行
例如：每月1日凌晨1点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24
5:每季度定时执行
例如每季度的第一天凌晨1点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),’Q’) + 1/24
6:每半年定时执行
例如：每年7月1日和1月1日凌晨1点
Interval => ADD_MONTHS(trunc(sysdate,’yyyy’),6)+1/24
7:每年定时执行
例如：每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,’yyyy’), 12)+1/24
job的运行频率设置
1.每天固定时间运行，比如早上8:10分钟：Trunc(Sysdate+1) + (860+10)/2460
2.Toad中提供的：
每天：trunc(sysdate+1)
每周：trunc(sysdate+7)
每月：trunc(sysdate+30)
每个星期日：next_day(trunc(sysdate),’星期日’)
每天6点：trunc(sysdate+1)+6/24
半个小时：sysdate+30/(2460)
3.每个小时的第15分钟运行，比如：8:15，9:15，10:15…：trunc(sysdate,’hh’)+(60+15)/(24*60) 。

上文参见：
oracle数据库定时任务dbms_job的用法详解
http://www.jb51.net/article/92575.htm

更多详情可参见以下两篇文章，有关于CHANGE的介绍。

更多dbms_job包的说明：
http://wallimn.iteye.com/blog/519924

PS:
change实际就是改变当前某个job设置的工作参数，包括job、what、next_date和interval参数。
job参数是一个整数值，它唯一标识此工作。
what参数是由此工作运行的一块PL/SQL代码块。
next_date参数指示何时此工作将被执行。
interval参数指示一个工作重执行的频度。

关于一些参数的解释可以参照一下：
https://www.cnblogs.com/chenjunjie/p/5054415.html

觉得文章不错的话，也可以支持一下哦~
-------------本文结束感谢您的阅读-------------
分享到： 收藏夹 复制网址 邮件 微信 QQ空间 腾讯微博 豆瓣 一键分享 更多
0%