13518219792
这篇文章给大家介绍MySQL和oracle中函数的作用是什么，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。
让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：申请域名、网络空间、营销软件、网站建设、武陵网站维护、网站推广。
一、nvl()函数：
(1)oracle中的nvl()函数：
语法：
nvl(expr1,expr2)
含义：：
如果expr1为空那么返回expr2，如果expr1值不为空，则返回expr1。
(2)mysql中的”nvl()”函数：
语法：
if null(expr1,expr2)
含义：：
如果expr1为空那么返回expr2，如果expr1值不为空，则返回expr1。
二、decode()函数：
(1)oracle中的decode()函数：
语法：
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,默认值)。
含义：：
当条件的值等于值1的时候，返回值1，当条件的值等于值2的时候，返回值2，当条件的值等于值n的时候，返回值n，如果条件与值1-值n都不想等，则返回默认值。相当于java中的if-else。
(2)mysql中的”decode()”函数：
语法：
ifnull(
etl(
filed(col,…)
)
)
filed函数：
语法：filed(str,str1,str2,str3……..)
含义：返回str在str1,str2,str3…..中的位置，假如str=str1，则返回1,;假如str=str2，则返回2。。。。。假如str1-strn中都没有str的值，则返回0。
注意：如果str 为NULL，则返回值为0 ，原因是NULL不能同任何值进行同等比较。
elt函数：
语法：elt(N,str1,str2,str3,…strn)
含义：如果N=1，则返回str1，N=2，则返回str2，N>n,，则返回null。
ifnull函数：
语法：if null(expr1,expr2)
含义：如果expr1为空那么返回expr2，如果expr1值不为空，则返回expr1。
总结以上三个函数：
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,默认值)
等价于：
ifnull(elt(filed(条件，值1，值2….值n)，返回值1，返回值2…..返回值n)，默认值)
三、分页：
(1)oracle中的分页：
语法：
select * from(select t.* ,rownum r from 表名 t)where r>10 and r<20;
注意：
(1)表名必须要取别名：表名 t;
(2)*前面必须要指明表的别名;
(3)rownum 这个字段必须要用别名：,rownum r ;
(4)分页的范围必须要用别名进行判断： r>10 and r<20;
(2)mysql中的分页：
语法：
SELECT * FROM 表名 LIMIT 起始值-1, 取多少条记录;
举例：
SELECT * FROM 表名 LIMIT 20, 10 ;
说明：从第21条记录开始去10条记录，即取得是21,22,23,24,25,26,27,28,29,30这10条记录。
四、字符串截取：
(1)oracle中的字符串截取：
语法：
select substr(“目标字符串”，开始坐标，结束坐标) from dual;
注意：
oracle中的下标可以从0开始，也可以从1开始，而且两个效果是一样的，但是如果从-1开始则是取最后一个，截取长度这个参数将会失效。
注意：
oracle中是没有substring这个函数的。
(2)mysql中的字符串截取：
语法一：
select substr(“目标字符串”，开始坐标，截取长度) from dual;
注意：
1、myql中的下标不能从0开始;
2、mysql下标也可以从-1开始，情况和orace一样。
语法二：
select substring(“目标字符串”，开始坐标，截取长度) from dual;
注意：
myql中的substr和substring函数是一样的效果。
语法三：
select mid(“目标字符串”，开始坐标，截取长度) from dual;
注意：
myql中的substr、mid和substring这三个函数是一样的效果。
语法四：
select left(“目标字符串”，从左侧第一个开始的截取长度) from dual;
语法五：
select right(“目标字符串”，从右侧最后一个开始的截取长度) from dual;
五、结果集拼接(用，隔开)：
(1)oracle中的wm_concat函数：
语法：
select 字段1 wm_concat(字段2) from 表名 group by 字段1;
注意：
wm_concat是分组函数，前面查询的字段需要在后面group by一下，如果不group by一个字段的话将会把所有行的字段2都用逗号拼接起来。
例子：
(2)mysql中的group_concat函数：
语法：
select 字段1 group_concat(字段2)
from 表名 group by 字段1;
注意：
1、group_concat也是分组函数，前面查询的字段同样需要在后面group by一下，如果不group by一个字段的话将会把所有行的字段2都用逗号拼接起来—–例子和oracle一样，这里就不举例了;
2、如果字段2中的值为null的时候，group_concat函数是不会留，给那个null值的，比如有三条记录，中间那条记录的字段2是null值，那么group_concat之后拼接出来的字段就只有两个值，即：记录1的字段2，记录3的字段2，而不是记录1的字段2，，记录3的字段2。
六、uuid：
(1)oracle中获取uuid：
语法：
INSERT INTO 表名 VALUES ((select lower(sys_guid()) from dual), 值2, 值3);
(2)mysql中获取uuid：
语法：
INSERT INTO 表名 VALUES (replace(uuid(), ‘-‘,”), 值2,值3);
七、字符串转数字：
(1)oracle中数字转字符串：
语法：
select * from 表 order by to_number(字段);
(2)mysql中数字转字符串：
语法一：
select * from 表 where deleted=0 order by Convert(字段,int);
语法二：
select * from 表 where deleted=0 order by CAST(字段 as int);
语法三：
select * from 表 where deleted=0 order by (ordernumber+0);
第一、row_number() over(partition by
首先要了解下oracle中这个函数的用法，看个例子。
select t.*,row_number() over(partition by t.owner order by y.createDate desc) rn from test t
这个语句的意思就是，把test这个表的数据按照owner 分组并且给每个分组的里面的数据加上一个序列号，数据格式如下：
id name owner createDate rn
1 aa 001 1
2 bb 001 2
3 cc 001 3
4 dd 002 1
5 ee 002 2
6 ff 003 1
数据搞得不太正规，但是应该能够看懂它的意思吧，但是在mysql中是没有这个函数的于是乎，找啊找，总算找了个解决方法，如下：
SELECT
heyf_tmp.*,
IF(@pdept=heyf_tmp.owner ,@rn:=@rn+1,@rn:=1) AS rn,
@pdept:=heyf_tmp.owner
FROM
(
SELECT
yv.*
FROM
test yv
ORDER BY
yv.owner ,
yv.createDate DESC
)
heyf_tmp ,
(
SELECT
@rn :=0 ,
@pdept := NULL ,
@rn:=0
)
aa
具体是什么意思，不是很清楚，不过先解决问题再说。
第二、oracle树形查询
oracle树形查询现成的方法
select distinct t.id as id, t.name
from test t
start with id=‘’
connect by prior id = parentid
但是mysql中是没有这个方法的，于是只能自己定义函数或者过程，我这里用的是过程。
如下：
CREATE PROCEDURE Pro_GetTreeList`(in pid varchar(36))
begin
declare lev int;
set lev=1;
drop table if exists tmp1;
CREATE TABLE tmp1(id VARCHAR(40),name varchar(50),parentid varchar(40) ,levv INT);
INSERT tmp1 SELECT id,name,parent_id,1 FROM `test` WHERE parent_id=pid;
while row_count()>0
do set lev=lev+1;
INSERT tmp1 SELECT t.id,t.name,t.parent_id,lev from testt join tmp1 a on t.parent_id=a.id AND levv=lev-1;--查出子节点
end while ;
INSERT tmp1 SELECT id,name,parent_id,0 FROM test WHERE id=pid; --查出当前节点
end
这个存储过程应该都能看懂吧，就不做多解释了。
第三 、函数中定义类似一维、二维数组，oracle中可以直接定义的，如：
return number is newList := NewList((1,2,3),(4,5,6));这样可以直接返回 return newList(1,2) 返回的就是2了，但是mysql中却不能这样写的，不过也有这样的方法解决：
对于一维的来说，很简单，有函数的elt函数，可以直接return elt(index,'1','2','3'); 如果elt(3,'1','2','3') 这样返回的就是 3了，不过对于二维的来说好像没有现成的函数用了，我的解决方法是改成个表来做，把数据录入到表中，然后去查表，这丫要给你也是可以实现。
第四、 mysql中获取汉字的首个大写字母
mysql中遇到了这样的一个问题。
DROP FUNCTION IF EXISTS `getPY`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `getPY`(in_string VARCHAR(65534)) RETURNS mediumtext CHARSET utf8
BEGIN
DECLARE tmp_str VARCHAR(65534) charset gbk DEFAULT '' ; #截取字符串，每次做截取后的字符串存放在该变量中，初始为函数参数in_string值
DECLARE tmp_len SMALLINT DEFAULT 0;#tmp_str的长度
DECLARE tmp_char VARCHAR(2) charset gbk DEFAULT '';#截取字符，每次 left(tmp_str,1) 返回值存放在该变量中
DECLARE tmp_rs VARCHAR(65534) charset gbk DEFAULT '';#结果字符串
DECLARE tmp_cc VARCHAR(2) charset gbk DEFAULT '';#拼音字符，存放单个汉字对应的拼音首字符
SET tmp_str = in_string;#初始化，将in_string赋给tmp_str
SET tmp_len = LENGTH(tmp_str);#初始化长度
WHILE tmp_len > 0 DO #如果被计算的tmp_str长度大于0则进入该while
SET tmp_char = LEFT(tmp_str,1);#获取tmp_str最左端的首个字符，注意这里是获取首个字符，该字符可能是汉字，也可能不是。
SET tmp_cc = tmp_char;#左端首个字符赋值给拼音字符
IF LENGTH(tmp_char)>1 THEN#判断左端首个字符是多字节还是单字节字符，要是多字节则认为是汉字且作以下拼音获取，要是单字节则不处理。
SELECT ELT(INTERVAL(CONV(HEX(tmp_char),16,10),0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC
,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA ,0xCEF4,0xD1B9,0xD4D1),
'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z') INTO tmp_cc; #获得汉字拼音首字符
END IF;
SET tmp_rs = CONCAT(tmp_rs,tmp_cc);#将当前tmp_str左端首个字符拼音首字符与返回字符串拼接
SET tmp_str = SUBSTRING(tmp_str,2);#将tmp_str左端首字符去除
SET tmp_len = LENGTH(tmp_str);#计算当前字符串长度
END WHILE;
RETURN tmp_rs;#返回结果字符串
END;;
DELIMITER ;
第四、 图片clob的修改
系统中的功能是把图片存在blob字段中的，，原来用的是oracle，java中对应的字段是Object，但是mysql中如果这样做却不能独处图片，原因可能是因为在往Object中写数据时出错了，这个号解决，只要把java中对应字段的类型改成byte[] 就行了。
1. Oracle 中的 TO_DATE ()
示例： select * from admadjustmoney t where t.sendtime> to_date(?,'yyyy-mm-dd hh34:mi:ss') and t.sendtime< to_date(?,'yyyy-mm-dd hh34:mi:ss') 转换后： SELECT * FROM `ADMADJUSTMONEY` t WHERE t.SENDTIME > STR_TO_DATE(sysdate(), '%Y-%m-%d %h:%i:%s') AND
t.SENDTIME < STR_TO_DATE(sysdate(), '%Y-%m-%d %h:%i:%s') 注意表名字段名的大小写 MYSQL 大小写敏感 sql 统一大写 hql 统一小写 5. Oracle 中的 trunc () 示例： from PUBCOINOPDETAIL where opervirtualCoin > 0 and status = 1 and operTime >= ADD_MONTHS(TRUNC(SYSDATE,'mm') ,-1) and operTime < TRUNC(SYSDATE,'mm') group by gene , geneType ,accountId 转换后： from PUBCOINOPDETAIL where opervirtualCoin > 0 and status = 1 and operTime >=DATE_SUB(date_sub(CURDATE(),INTERVAL EXTRACT( day from CURDATE())-1 day),INTERVAL 1 MONTH) [ 上个月第一天 ] and operTime;
再次提醒 mysql 大小写的问题 sql 统一大写 hql 统一小写 避免不必要的 BUG 出现。
网络资料附表：oracle 常用函数 与mysql 的对照表 s:mysql o ：oracle
1. 绝对值
S:select abs(-1) value
O:select abs(-1) value from dual
2. 取整 ( 大 )
S:select ceiling(-1.001) value
O:select ceil(-1.001) value from dual
3. 取整(小)
S:select floor(-1.001) value
O:select floor(-1.001) value from dual
4. 取整(截取)
S:select cast(-1.002 as int) value
O:select trunc(-1.002) value from dual
5. 四舍五入
S:select round(1.23456,4) value 1.23460
O:select round(1.23456,4) value from dual 1.2346
6.e 为底的幂
S:select Exp(1) value 2.7182818284590451
O:select Exp(1) value from dual 2.71828182
7. 取 e 为底的对数
S:select log(2.7182818284590451) value 1
O:select ln(2.7182818284590451) value from dual; 1
8. 取 10 为底对数
S:select log10(10) value 1
O:select log(10,10) value from dual; 1
9. 取平方
S:select SQUARE(4) value 16
O:select power(4,2) value from dual 16
10. 取平方根
S:select SQRT(4) value 2
O:select SQRT(4) value from dual 2
11. 求任意数为底的幂
S:select power(3,4) value 81
O:select power(3,4) value from dual 81
12. 取随机数
S:select rand() value
O:select sys.dbms_random.value(0,1) value from dual;
13. 取符号
S:select sign(-8) value -1
O:select sign(-8) value from dual -1
14. 圆周率
S:SELECT PI() value 3.1415926535897931
O: 不知道
15.sin,cos,tan 参数都以弧度为单位
例如： select sin(PI()/2) value 得到 1 ( SQLServer )
16.Asin,Acos,Atan,Atan2 返回弧度
17. 弧度角度互换 (SQLServer ， Oracle 不知道 )
DEGREES ：弧度 - 〉角度
RADIANS ：角度 - 〉弧度
数值间比较
18. 求集合最大值
S:select max(value) value from
(select 1 value
union
select -2 value
union
select 4 value
union
select 3 value)a
O:select greatest(1,-2,4,3) value from dual
19. 求集合最小值
S:select min(value) value from
(select 1 value
union
select -2 value
union
select 4 value
union
select 3 value)a
O:select least(1,-2,4,3) value from dual
20. 如何处理 null 值 (F2 中的 null 以 10 代替 )
S:select F1,IFNull(F2,10) value from Tbl
O:select F1,nvl(F2,10) value from Tbl
21. 求字符序号
S:select ascii('a') value
O:select ascii('a') value from dual
22. 从序号求字符
S:select char(97) value
O:select chr(97) value from dual
23. 连接
S:select '11'+'22'+'33' value
O:select CONCAT('11','22') 33 value from dual
23. 子串位置 -- 返回 3
S:select CHARINDEX('s','sdsq',2) value
O:select INSTR('sdsq','s',2) value from dual
23. 模糊子串的位置 -- 返回 2, 参数去掉中间 % 则返回 7
S:select patindex('%d%q%','sdsfasdqe') value
O:oracle 没发现，但是 instr 可以通过第四个参数控制出现次数
select INSTR('sdsfasdqe','sd',1,2) value from dual 返回 6
24. 求子串
S:select substring('abcd',2,2) value
O:select substr('abcd',2,2) value from dual
25. 子串代替 返回 aijklmnef
S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value
O:SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual
26. 子串全部替换
S: 没发现
O:select Translate('fasdbfasegas','fa',' 我 ' ) value from dual
27. 长度
S:len,datalength
O:length
28. 大小写转换 lower,upper
29. 单词首字母大写
S: 没发现
O:select INITCAP('abcd dsaf df') value from dual
30. 左补空格( LPAD 的第一个参数为空格则同 space 函数)
S:select space(10)+'abcd' value
O:select LPAD('abcd',14) value from dual
31. 右补空格( RPAD 的第一个参数为空格则同 space 函数)
S:select 'abcd'+space(10) value
O:select RPAD('abcd',14) value from dual
32. 删除空格
S:ltrim,rtrim
O:ltrim,rtrim,trim
33. 重复字符串
S:select REPLICATE('abcd',2) value
O: 没发现
34. 发音相似性比较 ( 这两个单词返回值一样，发音相同 )
S:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe')
O:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual
SQLServer 中用 SELECT DIFFERENCE('Smithers', 'Smythers') 比较 soundex 的差
返回 0-4 ， 4 为同音， 1 最高
日期函数
35. 系统时间
S:select getdate() value
O:select sysdate value from dual
36. 前后几日
直接与整数相加减
37. 求日期
S:select convert(char(10),getdate(),20) value
O:select trunc(sysdate) value from dual
select to_char(sysdate,'yyyy-mm-dd') value from dual
38. 求时间
S:select convert(char(8),getdate(),108) value
O:select to_char(sysdate,'hh34:mm:ss') value from dual
39. 取日期时间的其他部分
S:DATEPART 和 DATENAME 函数 (第一个参数决定)
O:to_char 函数 第二个参数决定
参数 --------------------------------- 下表需要补充
year yy, yyyy
quarter qq, q ( 季度 )
month mm, m (m O 无效 )
dayofyear dy, y (O 表星期 )
day dd, d (d O 无效 )
week wk, ww (wk O 无效 )
weekday dw (O 不清楚 )
Hour hh,hh22,hh34 (hh22,hh34 S 无效 )
minute mi, n (n O 无效 )
second ss, s (s O 无效 )
millisecond ms (O 无效 )
----------------------------------------------
40. 当月最后一天
S: 不知道
O:select LAST_DAY(sysdate) value from dual
41. 本星期的某一天(比如星期日)
S: 不知道
O:SELECT Next_day(sysdate,7) vaule FROM DUAL;
42. 字符串转时间
S: 可以直接转或者 select cast('2004-09-08'as datetime) value
O:SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh34-mi-ss') vaule FROM DUAL;
43. 求两日期某一部分的差(比如秒)
S:select datediff(ss,getdate(),getdate()+12.3) value
O: 直接用两个日期相减(比如 d1-d2=12.3 )
SELECT (d1-d2)*24*60*60 vaule FROM DUAL;
44. 根据差值求新的日期(比如分钟)
S:select dateadd(mi,8,getdate()) value
O:SELECT sysdate+8/60/24 vaule FROM DUAL;
关于mysql和oracle中函数的作用是什么就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
