Oracle SQL 函数与操作详解-优快云博客

本文链接：https://blog.youkuaiyun.com/thinktotings/article/details/84127251

oracle 函数

数字

----------------------------------------------------------------------------------------------------------------------------------

select ceil(192.125) from dual;--向上取整 193
select floor(192.125) from dual;--向下取整 192
select round(0.125,2) from dual;--四舍五入,精确两位小数 0.13
select round(1/3,3) from dual;--四舍五入,精确三位小数0.333

----------------------------------------------------------------------------------------------------------------------------------

字符串

---------------------------------------------------------------------------------------------------------------------------

/*
instr 函数:从某字符串中查找指字的字符串，所大的位置

'1268464;126872;1268764;126877-126878'    需要计算的字符串A
';' 需要查找的字符串B
2    查找B字符串时在A字符串中的开始查找位置(索引以1开始)
1    查找到第几个匹配的字符串
**/
select instr('1268464;126872;1268764;126877-126878',';',2,1) instring from dual;

-- instring 8

----------------------------------------------------------------------------------------------------------------------------------

日期

----------------------------------------------------------------------------------------------------------------------------------

更新表操作

----------------------------------------------------------------------------------------------------------------------------------

/*
更新表 t, t2用来关联数据,符合条件的，进行某操作，不符合条件的进行另外的操作
**/
      MERGE INTO test t
      USING (select t2.* from test t2 where rownum=3 ) aaron
      ON (t.a = aaron.a) --条件
      WHEN MATCHED THEN
      UPDATE SET t.b='aaaa'
      WHEN NOT MATCHED THEN
      INSERT (t.a, t.b ) VALUES ('11','111');