侧边栏壁纸
  • 累计撰写 131 篇文章
  • 累计创建 13 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

【ORACLE】收集一些较为少见但很有用的SQL函数及写法.part3

DarkAthena
2021-09-21 / 0 评论 / 0 点赞 / 1098 阅读 / 5690 字

接上篇
【ORACLE】收集一些较为少见但很有用的SQL函数及写法.part2


18.DBMS_TF (Polymorphic Table Functions PTF)

多态表,把查询或者表作为参数,返回表,返回表的字段和值都可以进行自定义,比如指定列不显示、对所有VARCHAR2字段进行trim、对所有number字段保留4位小数、增加自定义值的字段等
由于篇幅过长,详见我的另一篇文章 【ORACLE】关于多态表函数PTF(Polymorphic Table Functions)的使用


19.approx_count_distinct

此函数源自于一些大数据工具,是oracle 12c新添加的函数,主要作用类似于 count(distinct()) ,但是返回的是近似值。在对某些数据进行去重统计时(比如统计消费了某个品类商品的单量或者自然人数),如果这个数据是个海量数据,使用常规的 count(distinct()),效率极低。有时候只是想看个大概的数字以便做一些决策,就算统计的数字上下浮动一点也没关系,这个时候就可以用到approx_count_distinct这个函数了。我曾用过十几组样本数据测试,准确率大概在85%左右。

select col1,approx_count_distinct(col2) from tab1 group by col1;

20.unistr

将unicode编码转换为中文显示
详见我的另一篇文章【ORACLE】关于ORACLE数据库中UNISTR函数转换UNICODE编码字符串为中文的思考


21.dbms_crypto

作用:各种加密解密
例:计算字符串的MD5值

select sys.dbms_crypto.Hash(UTL_RAW.CAST_TO_RAW('123') ,2 ) from dual;
--或
select sys.dbms_crypto.Hash(UTL_I18N.STRING_TO_RAW ('123', 'AL32UTF8') ,2 ) from dual;

hash函数的第一个参数可以传入raw、blob、clob类型,也就是说varchar2和number不能直接传入,传入的话可能会被解析成十六进制数据。既然支持blob,那么就当然能变向的支持二进制文件了。
第二个参数是指的hash方式,这个可以在dbms_crypto的包说明中找到对应的值

-- Hash Functions
HASH_MD4 CONSTANT PLS_INTEGER := 1;
HASH_MD5 CONSTANT PLS_INTEGER := 2;
HASH_SH1 CONSTANT PLS_INTEGER := 3;
HASH_SH256 CONSTANT PLS_INTEGER := 4;
HASH_SH384 CONSTANT PLS_INTEGER := 5;
HASH_SH512 CONSTANT PLS_INTEGER := 6;

当然这个包不仅仅有hash,其他常用的类型基本都能支持

-- MAC Functions
HMAC_MD5 CONSTANT PLS_INTEGER := 1;
HMAC_SH1 CONSTANT PLS_INTEGER := 2;
HMAC_SH256 CONSTANT PLS_INTEGER := 3;
HMAC_SH384 CONSTANT PLS_INTEGER := 4;
HMAC_SH512 CONSTANT PLS_INTEGER := 5;
-- Block Cipher Algorithms
ENCRYPT_DES CONSTANT PLS_INTEGER := 1; -- 0x0001
ENCRYPT_3DES_2KEY CONSTANT PLS_INTEGER := 2; -- 0x0002
ENCRYPT_3DES CONSTANT PLS_INTEGER := 3; -- 0x0003
ENCRYPT_AES CONSTANT PLS_INTEGER := 4; -- 0x0004
ENCRYPT_PBE_MD5DES CONSTANT PLS_INTEGER := 5; -- 0x0005
ENCRYPT_AES128 CONSTANT PLS_INTEGER := 6; -- 0x0006
ENCRYPT_AES192 CONSTANT PLS_INTEGER := 7; -- 0x0007
ENCRYPT_AES256 CONSTANT PLS_INTEGER := 8; -- 0x0008
-- Block Cipher Chaining Modifiers
CHAIN_CBC CONSTANT PLS_INTEGER := 256; -- 0x0100
CHAIN_CFB CONSTANT PLS_INTEGER := 512; -- 0x0200
CHAIN_ECB CONSTANT PLS_INTEGER := 768; -- 0x0300
CHAIN_OFB CONSTANT PLS_INTEGER := 1024; -- 0x0400
-- Block Cipher Padding Modifiers
PAD_PKCS5 CONSTANT PLS_INTEGER := 4096; -- 0x1000
PAD_NONE CONSTANT PLS_INTEGER := 8192; -- 0x2000
PAD_ZERO CONSTANT PLS_INTEGER := 12288; -- 0x3000
PAD_ORCL CONSTANT PLS_INTEGER := 16384; -- 0x4000
-- Stream Cipher Algorithms
ENCRYPT_RC4 CONSTANT PLS_INTEGER := 129; -- 0x0081
-- Convenience Constants for Block Ciphers
DES_CBC_PKCS5 CONSTANT PLS_INTEGER := ENCRYPT_DES
+ CHAIN_CBC
+ PAD_PKCS5;
DES3_CBC_PKCS5 CONSTANT PLS_INTEGER := ENCRYPT_3DES
+ CHAIN_CBC
+ PAD_PKCS5;
AES_CBC_PKCS5 CONSTANT PLS_INTEGER := ENCRYPT_AES
+ CHAIN_CBC
+ PAD_PKCS5;


22.BFILENAME

作用:读取数据库本地操作系统上的文件
例:读取操作系统上的tnsnames配置文件

select bfilename('NETWORK_ADMIN','tnsnames.ora') from dual;

image.png

第一个参数是数据库中创建的映射到操作系统的目录名称,第二个参数是文件名。
这个函数返回的是bfile类型,可以根据实际内容来转换成BLOB类型或者CLOB类型来进行后续其他处理。
有时候只有数据库权限但没操作系统权限,又要从操作系统中取出一些文件来检查问题的时候,就可以通过这种方式来实现

0
  1. 支付宝打赏

    qrcode alipay
  2. 微信打赏

    qrcode weixin
博主关闭了所有页面的评论