Oracle函数使用

最新推荐文章于 2024-06-18 21:26:27 发布

佐氵谙

最新推荐文章于 2024-06-18 21:26:27 发布

阅读量1.3k

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/BestEternity/article/details/85330138

版权

Oracle 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

sys_guid()函数：

sys_guid()函数是Oracle 8i 后提供的函数。sys_guid()产生并返回一个全球唯一的标识符(原始值)由16个字节组成。在大多数平台，生成的标识符由主机标符，执行函数的进程或者线程标识符，和进程或线程的一个非重复的值(字节序列)。

select sys_guid() from dual;

结果：

该方法的弊端：

由于sys_guid ()生成的序列号过长，这会消耗数据库存储空间，且管理不方便。基于此，在非并行环境下的数据库应用中，应尽量避免使用sys_guid ()。

CAST()函数：

CAST()可以进行数据类型的转换。CAST()函数的参数有两部分，源值和目标数据类型，中间用AS关键字分隔。

例：

1、转换列或值

语法：cast( 列名/值 as 数据类型 );

1).转换列
将salary的类型decimal(18,2)转换为number(20)类型。

select salary as 转换前,cast(salary as number(20)) as 转换后 from employees;

结果：

2)、转换值
将字符串转换为整型。

select cast('112233' as int) as result from dual;

结果：

如果试图将一个带小数的字符串转换为整型值，又会出现什么情况呢？

select cast('123.3' as int) as result1, CAST('123.6' AS int) as result2 from dual;

结果：

从上面可以看出，CAST()函数能执行四舍五入操作。

截断小数

select cast('123.52896' as decimal(5,2)) as result from dual;

结果：

decimal(5,2)表示值总位数为5，精确到小数点后2位。

select cast('122.5' as decimal) as result from dual;

结果：

2、转换一个集合

语法：cast( multiset(查询语句) as 数据类型 );

1)转换成table

-- 学生成绩表
CREATE TABLE stu_score
(stu_no VARCHAR2(100),-- 学号
 score  number-- 总分
 );
INSERT INTO stu_score VALUES('201301',67);
INSERT INTO stu_score VALUES('201302',63);
INSERT INTO stu_score VALUES('201303',77);
INSERT INTO stu_score VALUES('201304',68);
INSERT INTO stu_score VALUES('201305',97);
INSERT INTO stu_score VALUES('201306',62);
INSERT INTO stu_score VALUES('201307',87);
commit;
-----------------------------------------------
SELECT * FROM stu_score;
-----------------------------------------------
-- 奖学金表 奖学金表规定了名次，每个名次的人数和奖金。
CREATE TABLE scholarship
(
stu_rank   VARCHAR2(100),-- 名次
stu_num    INTEGER,-- 限定人数
money      NUMBER(18,2)-- 奖金
);
INSERT INTO scholarship VALUES('1',1,'1000');
INSERT INTO scholarship VALUES('2',2,'500');
INSERT INTO scholarship VALUES('3',3,'100');
commit;
-----------------------------------------------
SELECT * FROM scholarship;
-----------------------------------------------

现在要根据成绩表的成绩降序排列，按奖学金表的名额限定人数确定排名和奖金。排名时不考虑相同成绩。

SELECT c.stu_no, c.score, b.stu_rank, b.money
  FROM (SELECT c.*, ROW_NUMBER() OVER(ORDER BY score DESC) rn
          FROM stu_score c) c,
       (SELECT b.stu_rank,
               b.money,
               ROW_NUMBER() OVER(ORDER BY b.stu_rank) rn
          FROM scholarship b,
               TABLE(CAST(MULTISET (SELECT NULL
                             FROM DUAL
                           CONNECT BY LEVEL <= b.stu_num) AS
                          SYS.ODCIVARCHAR2LIST))) b
 WHERE c.rn = b.rn;

结果：

replace()函数：

replace（'将要更改的字符串','被替换的字符串','替换的字符串'）

update 表1 t set t.列1=replace((select 列1 from 表1 a where a.主键列=t.主键列) , '/' , '-');

例：

select replace ('111333111','333','666') from dual;

输出结果： '111666111'

to_char()、to_number()和to_date()函数的区别：

to_char()：是把数字或日期转换为字符串。

to_number()：是将字符串转换为数值型的格式，与to_char()函数的作用正好相反。

to_date()：是把字符串转换为数据库中的日期类型。

使用TO_CHAR函数处理数字：TO_CHAR(number, '格式');

主要数字格式如下：
9：表示有效位数
0：数字前用0补足位数
B：用空格补足位数
$：在数字前加美元号
L：在数字前加本地货币符号"￥"
.：小数点位置显示小数点
,：分格符位置显示千位指示符
MI：负数时在最后边加负号
PR：负数用<>括起来
S：在前面加符号
EEEE：科学计数法
V：把原数字乘以10的N次方(N为V后面的位数)

例1： 9， 0 ，B 测试

SELECT to_char(123.45, '0009.9'),
       to_char(123.45, '9999.9'),
       to_char(123.45, 'B999.9')
  FROM dual;

结果：

例2：$， L 测试

SELECT to_char(123.45, '$9999.9'), to_char(123.45, 'L9999.9') FROM dual;

结果：

例3：.，, 测试

SELECT to_char(123.45, '9999.99'), to_char(1233123.45, '9,999,999.99')
  FROM dual;

结果：

例4：MI，PR 测试

SELECT to_char(-123.45, '9999.99MI'), to_char(-123.45, '9999.99PR')
  FROM dual;

结果：

例5：S，EEEE，V 测试

SELECT to_char(123.45, 'S9999.99'),
       to_char(12345, '99.99EEEE'),
       to_char(123, '9999V99')
  FROM dual;

结果：

使用TO_CHAR函数处理日期：TO_CHAR(date,’格式’);

SELECT SYSDATE FROM dual; -- 2019/01/01 00:00:00
SELECT TRUNC(SYSDATE) FROM dual; -- 2019/01/01
SELECT to_char(SYSDATE,'DS') FROM dual; -- 2019-01-01
SELECT to_char(SYSDATE,'YYYYMMDD') FROM dual; -- 20190101 到日
SELECT to_char(SYSDATE,'YYYYMMDD HH24:MI:SS') FROM dual; -- 20190101 00:00:00 到秒
SELECT to_char(SYSTIMESTAMP,'YYYYMMDD HH24:MI:SS.FF3') FROM dual; -- 20190101 00:00:00.848 到毫秒
SELECT to_char(SYSTIMESTAMP,'SS.FF3') FROM dual; -- 毫秒 
SELECT to_char(SYSDATE,'DAY') FROM dual; -- 星期几 
SELECT to_char(SYSDATE,'DY') FROM dual; -- 星期几
SELECT to_char(SYSDATE,'DL') FROM dual; -- 2019年1月1日 星期一
SELECT to_char(SYSDATE,'AD') FROM dual; -- 公元
SELECT to_char(SYSDATE,'BC') FROM dual; -- 公元 
SELECT to_char(SYSDATE,'AM') FROM dual; -- 上午  
SELECT to_char(SYSTIMESTAMP,'PM') FROM dual; -- 下午

使用TO_NUMBER函数将字符转换为数字：TO_NUMBER('char','格式');

使用TO_DATE函数将字符转换为日期：TO_DATE('char','格式');

主要日期格式参数含义说明如下：

IYYY   ISO标准的四位年份
YYYY、YYY、YY、Y   分别代表4位、3位、2位、1位的数字年
MM   数字月
MON   月份的简写
DD   月中的第几天数字日
DDD   年中的第几天
DAY   天的名字使用空格填充到9个字符
DY   天的简写名
AM   表示上午或者下午
HH12、HH24   12小时制或24小时制
MI   分钟
SS   秒钟
IW   ISO标准的年中的第几周
D   一周中的星期几
W   该月的第几个星期
WW   年中的第几个星期
SP   数字的拼写
TH   数字的序数词

日期时间间隔操作

--当前时间减去5年的时间
select sysdate,sysdate - interval '5' year from dual;
--当前时间减去5个月的时间
select sysdate,sysdate - interval '5' month from dual;
--当前时间减去5天的时间
select sysdate - interval '5' day from dual;
--当前时间减去5小时的时间
select sysdate - interval '5' hour from dual;
--当前时间减去5分钟的时间
select sysdate,sysdate - interval '5' MINUTE from dual;
--时间间隔乘以一个数字
select sysdate,sysdate - 8 *interval '2' hour from dual;

grouping()函数：

grouping()函数解释：grouping函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么GROUPING()返回0。

GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。ROLLUP是GROUP BY子句的一种扩展，可以为每个分组返回小计记录以及为所有分组返回总计记录。CUBE也是GROUP BY子句的一种扩展，可以返回每一个列组合的小计记录，同时在末尾加上总计记录。

1、在ROLLUP中对单列使用GROUPING()

select division_id, sum(salary)
  from employees
 group by rollup(division_id)
 order by division_id;

结果：

加上GROUPING来看看：

select grouping(division_id), division_id, sum(salary)
  from employees
 group by rollup(division_id)
 order by division_id;

结果：

可以看到，为空的地方返回1，非空的地方返回0。

2、使用CASE转换GROUPING()的返回值

使用CASE来将 0 和 1 转换为有意义的值：

select case grouping(division_id)
       when 1 then '小计'
       else division_id
       end as did,
       sum(salary)
  from employees
 group by rollup(division_id)
 order by division_id;

结果：

3、使用CASE和GROUPING()转换多个列的值

select case grouping(division_id)
       when 1 then 'all divisions'
       else division_id
       end as did,
       case grouping(job_id)
       when 1 then 'all jobs'
       else job_id
       end as job,
       sum(salary)
  from employees
 group by rollup(division_id, job_id)
 order by division_id, job_id;

结果：

4、CUBE与GROUPING()结合使用

select case grouping(division_id)
       when 1 then 'all divisions'
       else division_id
       end as did,
       case grouping(job_id)
       when 1 then 'all jobs'
       else job_id
       end as job,
       sum(salary)
  from employees
 group by cube(division_id, job_id)
 order by division_id, job_id;

结果：

5、使用GROUPING SETS子句

select division_id, job_id, sum(salary)
  from employees
 group by grouping sets(division_id, job_id)
 order by division_id, job_id;

结果：

转载请注明出处：BestEternity亲笔。

佐氵谙

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle函数使用

sys_guid()函数：sys_guid()函数是Oracle 8i 后提供的函数。sys_guid()产生并返回一个全球唯一的标识符(原始值)由16个字节组成。在大多数平台，生成的标识符由主机标符，执行函数的进程或者线程标识符，和进程或线程的一个非重复的值(字节序列)。select sys_guid() from dual; 结果：该方法的弊端： ...
复制链接

扫一扫

专栏目录