Oracle数据库中经常使用的函数

转x33g5p2x  于2021-12-06 转载在 Oracle  
字(6.3k)|赞(0)|评价(0)|浏览(341)

字符串类型函数

函数描述
ASCII(x)返回字符x的ASCII值。


|
| CHR(x) | 返回ASCII值为x的字符。 |

|
| CONCAT(x, y) | 连接两个字符串x和y，并返回连接后的字符串。 |

|
| INITCAP(x) | 将x中每个单词的初第一个字母转换为大写，并返回该字符串。 |

|
| INSTR(x, find_string [, start] [, occurrence]) | 在x字符串中搜索find_string子串并返回找到的位置。 |

|
| INSTRB(x) | 返回字符串x在另一个字符串中第一次再现的位置，但返回值(以字节为单位) |

|
| length(x) | 返回x中的字符数，也是计算字符串的长度。 |

|
| LENGTHB(x) | 返回单字节字符集的字符串长度(以字节为单位)。 |

|
| LOWER(x) | 将x字符串中的字母转换为小写，并返回此小写字符串 |

|
| UPPER(x) | 将x中的字母转换为大写，并返回此大写后的字符串 |

|
| lpad(x, width [, pad_string]) | 使用空格垫放在x字符串的左边，以使字符串的长度达到宽度字符 |

|
| rpad(x, width [, pad_string]) | 使用空格垫放在x字符串的右边，以使字符串的长度达到宽度字符。 |

|
| ltrim(x [, trim_string]) | 修剪x字符串左边的字符 |

|
| RTRIM(x [, trim_string]) | 从右边修剪x字符串 |

|
| TRIM([trim_char FROM) x) | 修剪x字符串的左边和右边的字符。 |


|
| NVL(x, value) | 如果x为null则返回value值; 否则返回x |

|
| NVL2(x, value1, value2) | 如果x不为null则返回值value1; 如果x为null，则返回value2 |

|
| REPLACE(x, search_string, replace_string) | 在x字符串中搜索search_string并将其替换为replace_string |

|
| SUBSTR(x, start [, length]) | 返回x字符串从指定start位置开始到一个可选指定长度(length)范围内的子字符串 |

|

数字类型函数

保留两位小数

1、ROUND(A/B,2)
ROUND()函数是会将计算结果进行四舍五入的，如果所需要的值需要进行四舍五入，就可以选择这个函数，可以有一个参数，也可以有两个参数；如果有两个param，第一个是你的计算表达式，第二个是需要保留的小数位数。例子如下：

2、TRUNC(A/B,2)
TRUNC()函数是不会将计算结果进行四舍五入的，如果所需要的值不需要进行四舍五入，就可以选择这个函数，可以有一个参数，也可以有两个参数；如果有两个param，第一个是你的计算表达式，第二个是需要保留的小数位数。例子如下：

3、TO_CHAR(A/B,’FM99990.99’)
TO_CHAR()是一个格式化函数，第一个参数是计算表达式，第二个参数是指定格式化的格式，如果保留两位小数则小数点后写两个99，这里的数字9代表的数字，也是一个占位符，表示该位置上以后会是一个数字，为什么小数点前面会是一个0，而不是9，是因为如果计算结果小于1，那么只会显示小数点和小数点之后的部分，前面的0会忽略掉

日期类型函数

日期格式转换

1、日期转化为字符串 (以2020年10月20日为例)

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')  strDateTime from dual;    
--获取年-月-日 时:分:秒 
--显示结果为：2020-10-20 12:35:21

select to_char(sysdate,'yyyymmdd hh24:mi:ss')  strDateTime from dual;    
--获取年月日 时:分:秒 
--显示结果为：20201020 13:39:25

select to_char(sysdate,'yyyymmdd')  strDateTime from dual;     
--获取年月日 
--显示结果为：20201020

select to_char(sysdate,'yyyy')  strYear from dual;    
--获取年 
--显示结果为：2020

select to_char(sysdate,'mm')   strMonth  from dual;    
--获取月 
--显示结果为：10

select to_char(sysdate,'dd')    strDay    from dual;     
--获取日
--显示结果为：20

select to_char(sysdate,'hh24')   strHour   from dual;     
--获取时 
--显示结果为：13

select to_char(sysdate,'mi')  strMinute from dual;   
--获取分 
--显示结果为：46

select to_char(sysdate,'ss')  strSecond from dual;   
--获取秒
--显示结果为：43

2、字符串和时间互转

select to_date('2010-10-20 13:23:44','yyyy-mm-dd hh24:mi:ss') dateTime from dual;
--显示结果：2010/10/20 13:23:44

select to_date('2010-10-20 13:23:44','yyyy/mm/dd hh24:mi:ss') dateTime from dual;

--显示结果：2010/10/20 13:23:44

select to_char( to_date(222,'J'),'Jsp') from dual;

--显示结果：Two Hundred Twenty-Two

--如果按照下面的例子写，会报错：ORA-01849:小时值必须介于1和12之间。(因为其中的hh是12进制，没有13所以报错)

select to_date('2005-12-25,13:25:59','yyyy-mm-dd,hh:mi:ss') from dual;

3、查询某天是星期几

select to_char(to_date('2012-10-20','yyyy-mm-dd'),'day') strDay from dual; 
--显示结果：星期六

select to_char(to_date('2012-10-20','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = English') strDay from dual;   
--显示结果：saturday

4、两个日期间的天数

select floor(sysdate - to_date('20211010','yyyymmdd')) strTime from dual; 

--其中sysdate=2021/10/28 17:10:51
--显示结果：18

5、时间为null的用法

select to_date(null) from dual;

6、月份差

select  months_between(date'2021-04-23',date'2020-04-23') days from dual;

行转列函数

1、Oracle 官方更推荐使用 listagg 函数，格式：listagg(column_nname，delimiter) within group (order by order_by_column);

column_nname：待转换的列名，如 ename

delimiter：分隔符，默认为 null，无分隔符

order_by_column：指定列值拼接顺序，如 order by ename

2、listagg 函数使用举例如下：

–查询所有员工的姓名，用 “+” 号分割，如：SCOTT+SMITH+TURNER+WARD+张三，按员工姓名排序拼接select listagg(t.ename,’+’) within group(order by t.ename) as names from emp t ;
–分组查看各部门的员工姓名，使用 “,” 分割，姓名按薪水高低排列拼接select t.deptno,listagg(t.ename,’,’) within group(order by t.sal desc) as neames from emp t group by t.deptno;
–查询指定表的所有字段,对于想要快速获取某个表的字段是非常有用的操作select listagg(t.COLUMN_NAME,’,’) within group(order by t.COLUMN_ID) from user_tab_columns t where t.TABLE_NAME = ‘EMP’;
-- 列传行
select  t1.ywlx,wm_concat(t1.ywlxmc),wm_concat(t1.thcs) as ywlxmcs from aaa t1  group by t1.ywlx;

-- 列传行
select listagg(t1.ywlxmc,',')within group(order by t1.ywlx)  from aaa t1;

select listagg(t1.column_name,',')within group(order by t1.column_id) from user_tab_columns t1 where t1.table_name='aaa';

cast 数据类型转换

1、cast() 函数用于转换数据类型，格式：cast(列名/值 as 目标数据类型)，值为 null 时不会有影响。

select cast('124' as number) + 100 as total from dual; -- 字符串转数值，输出 224

select concat(cast(t.sal as varchar2(32)),'00') as str from emp t; --数值转字符串，sal 值为 null 时，不会有影响。

collect()

collect(): 获取任何类型的列，并从所选行中创建输入类型的嵌套表。可以实现多行转换成一行
1、创建数组类型嵌套表
PL/SQL 程序可使用嵌套表类型创建具有一个或多个列和无限行的变量, 这很像数据库中的表. 声明嵌套表类型的一般语法如下:

TYPE type_name AS TABLE OF 
{datatype | {variable | table.column} % type | table%rowtype};
create or replace type str_tab as table of varchar2(2000);

2、创建format_string格式化输出函数

create or replace function format_string(v_table in str_tab) return varchar2 is
  Results varchar2(30000);
begin
  for i in 1 .. v_table.count loop
    Results :=Results||','||v_table(i);
  end loop;
 --去掉第一个逗号--
 return(ltrim(Results,','));
end format_string;

dept表初始数据

3、开始使用

select format_string(CAST(COLLECT(loc) AS str_tab)) AS loc from dept;

游标

declare 
  cursor v_cursor is select * from aaa; --1.声明游标
  v_row aaa%rowtype; --2. 声明变量v_row,变量类型为aaa表的一行
begin
  open v_cursor;  --3.打开游标
  loop --4、使用 loop 循环遍历游标
    fetch v_cursor into v_row;   --5、遍历游标 v_cursor 的每一行结果给(into)变量v_row
    exit when v_cursor%notfound;  --6、当游标 v_cursor 没有再读取到值时，则退出循环 
    --dbms_output.put_line()：plsql 的输出语句
    dbms_output.put_line('ywlxmc'||v_row.ywlxmc ||' thcs: ' ||v_row.thcs||' ywlxcj: ' ||v_row.ywlxcj ||' thszb: ' ||v_row.thszb );
  end loop;
  close v_cursor;
end;
declare
   cursor v_cursor(v_thcs number) is select * from aaa where thcs = v_thcs;
   v_row aaa%rowtype; 
begin
   open v_cursor(55);
   loop
     fetch v_cursor into v_row;
     if v_cursor%notfound then
       exit;
     end if;
     dbms_output.put_line('证券：'|| v_row.ywlxmc  || ' thcs：'|| v_row.thcs);
   end loop;
   close v_cursor;
end;

3、使用系统游标

declare 
   v_sys_cursor sys_refcursor;
   v_row aaa%rowtype;
begin
   open v_sys_cursor for select * from aaa;
   loop 
     fetch v_sys_cursor into v_row;
     exit when v_sys_cursor%notfound;
     dbms_output.put_line('证券：'|| v_row.ywlxmc  || ' thcs：'|| v_row.thcs);
   end loop;
   close v_sys_cursor;
end;

遍历游标

declare
  cursor v_cursor is select * from aaa;
begin
  for v_row in v_cursor loop
    dbms_output.put_line('证券：'|| v_row.ywlxmc  || ' thcs：'|| v_row.thcs);
  end loop;
end;

decode()和case…when

decode函数条件判断

select decode(t1.ywlxmc,'粮食基金','唐僧','无名') from aaa t1;

case…when条件判断

select t1.ywlxmc,
case
  when to_number(replace(t1.thszb,'%','')) >50   then  '合格' 
  when to_number(replace(t1.thszb,'%','')) <50   then  '不合格' 
  else '数据不准确'  
end  a
from aaa t1;
select 
 case t1.ywlxmc
   when '粮食基金' then '唐僧'
   when '5G基金' then '孙悟空'
   else '无名' 
 end  alias
 from aaa t1;

异常

declare 
   cursor v_cursor is select * from aaa ;
   number_exception exception;
begin
   for v_row in v_cursor loop
       exit when v_cursor%notfound;
       case v_row.thcs
         when 203 then dbms_output.put_line('203');    
         when 563 then dbms_output.put_line('563');  
         when 632 then dbms_output.put_line('632');
         when 55 then raise number_exception;
       end case;
   end loop;    
exception
  when number_exception then dbms_output.put_line('数据出错了');
end;

版权说明 : 本文为转载文章, 版权归原作者所有 版权申明

原文链接 : https://blog.csdn.net/qq_45297578/article/details/121012839

内容来源于网络，如有侵权，请联系作者删除！

Databaseoracle数据库

相关文章

    查看更多
    微信公众号

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新文章

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com