一、函数：
1、Oracle分页转MySQL(rownum–>row_number() over()，功能类似)：
1、rownum分页：
Oracle：
SELECT *
FROM
(SELECT T1.*,rownum AS rnum
FROM (SELECT * FROM 表名
) T1
)
WHERE rnum <= 3 and rnum >= 1
MySQL：
SELECT *
FROM
(SELECT T1.*,ROW_NUMBER() OVER() AS rnum
FROM (SELECT * FROM 表名
) T1
) T2
WHERE rnum <= 3 and rnum >= 1
2、含rowid分页
Oracle：
含rowid：
SELECT *
FROM 表名 A WHERE
ROWID IN (SELECT RID FROM (SELECT RID, rownum AS RN
FROM (SELECT ROWID RID FROM 表名 A
) T1
) T2
WHERE RN <= 3 and RN >= 1)
先转为rounum分页：
SELECT *
FROM
(SELECT T1.*,rownum AS RN
FROM (SELECT * FROM 表名
) T1
)
WHERE RN <= 3 and RN >= 1
MySQL：
SELECT *
FROM
(SELECT T1.*,ROW_NUMBER() OVER() AS RN
FROM (SELECT * FROM 表名
) T1
) T2
WHERE RN <= 3 and RN >= 1
2、1418：This function has none of DETERMINISTIC, NO SQL,or READS SQL DATA in its declaration and binary logging is enabled(you might want to use the less safe log_bin_trust_function_creators variable) 创建函数失败
查看是否开启创建函数：show variables like '%func%';
如果Value处值为OFF，则需将其开启：set global log_bin_trust_function_creators=1;
3、Oracle中行转列函数(listagg(…,…) within group(order by …))转为MySQL：
Oracle：
listagg( to_char(b.alarmdsp),';') within group (order by createtime desc)
MySQL：
group_concat( distinct b.alarmdsp order by createtime desc separator ';')
4、Oracle中regexp_count转MySQL:
Oracle统计字符串v_amt中'\-'出现的次数：regexp_count(v_amt,'\-')
转为MySQL:LENGTH(v_amt) - LENGTH(REPLACE(v_amt,'\-',''))
实现：首先计算字符串v_amt的长度，然后将字符串v_amt的所有'\-'删掉，计算删掉'\-'以后的字符串
的长度，这两个长度之差就是字符'\-'在字符串中出现的次数。
5、regexp_substr()：用于模式匹配。它从给定的字符串返回子字符串。(MySQL、Oracle)
-- 判断日期由数字组成
if regexp_substr(v_year,'^+?[0-9][0-9]*$') is null then
6、replase()：直接替换mysql数据库中某字段中的特定字符串，不再需要自己写函数去替换
实例：把'病假' 替换为 '--'：UPDATE users SET username=REPLACE(username,'病假','--') WHERE username LIKE '%病假%';
7、mysql中对应oracle中的to_char()、to_date()和to_number()函数
TO_CHAR():
CAST(123 AS CHAR(3))
TO_NUMBER():
cast( '123 ' as SIGNED INTEGER)
mysql 类似to_char() to_date()函数
mysql日期和字符相互转换方法
date_format(date,'%Y-%m-%d') -------------->oracle中的to_char();
str_to_date(date,'%Y-%m-%d') -------------->oracle中的to_date();
%Y：代表4位的年份
%y：代表2为的年份
%m：代表月, 格式为(01……12)
%c：代表月, 格式为(1……12)
%d：代表月份中的天数,格式为(00……31)
%e：代表月份中的天数, 格式为(0……31)
%H：代表小时,格式为(00……23)
%k：代表 小时,格式为(0……23)
%h： 代表小时,格式为(01……12)
%I： 代表小时,格式为(01……12)
%l ：代表小时,格式为(1……12)
%i： 代表分钟, 格式为(00……59)
%r：代表 时间,格式为12 小时(hh:mm:ss [AP]M)
%T：代表 时间,格式为24 小时(hh:mm:ss)
%S：代表 秒,格式为(00……59)
%s：代表 秒,格式为(00……59)
SELECT DATE_FORMAT(20130111191640,'%Y-%m-%d %H:%i:%s')
DATE_FORMAT(20130111191640,'%Y-%m-%d %H:%i:%s')
例子：
Oracle：
select to_char(add_months(to_date(current_date,'YYYYMM'),-1),'YYYYMMDD') from dual
改为
MySQL：
select str_to_date(ADDDATE(date_format(current_date,'%Y-%m-%d'),INTERVAL -1 month),'%Y%M%D') from dual
8、Oracle中的instr函数转为MySQL (传多个参数时，MySQL可以用regexp_instr()替代Oracle中的instr())：
Oracle：
nstr()函数的格式 （俗称：字符查找函数）
格式一：instr( string1, string2 ) // instr(源字符串, 目标字符串)
格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] ) // instr(源字符串, 目标字符串, 起始位置, 匹配序号)
解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。
MySQL：
INSTR(STR,SUBSTR) 在一个字符串(STR)中搜索指定的字符(SUBSTR),返回发现指定的字符的位置(INDEX);
STR 被搜索的字符串
SUBSTR 希望搜索的字符串
结论：在字符串STR里面,字符串SUBSTR出现的第一个位置(INDEX)，INDEX是从1开始计算，如果没有找到就直接返回0，没有返回负数的情况。
MySQL中的模糊查询 like 和 Oracle中的 instr() 函数有同样的查询效果； 如下所示：
MySQL： select * from tableName where name like '%helloworld%';
Oracle：select * from tableName where instr(name,'helloworld')>0; --这两条语句的效果是一样的
9、Oracel的start with connect by prior(递归查询函数)转为MySQL：
Oracle：
START WITH BR_NO = '00' CONNECT BY PRIOR BR_NO = UP_ONE
MySQL：
FIND_IN_SET(br_no,getChildId_bas_basic_tree('00'))
MySQL自定义函数 getChildId_bas_basic_tree()：
CREATE DEFINER=`root`@`localhost` FUNCTION `getChildId_bas_basic_tree`(rootId varchar(20)) RETURNS varchar(16000) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
DETERMINISTIC
BEGIN
DECLARE pTemp VARCHAR(16000);
DECLARE cTemp VARCHAR(1000); -- 节点ID(临时变量)
SET pTemp = '$';
SET cTemp =cast(rootId as CHAR); -- 把rootId强制转换为字符。
WHILE cTemp is not null DO
SET pTemp = concat(pTemp,',',cTemp); -- 把所有节点连接成字符串。
SELECT group_concat(br_no) INTO cTemp FROM 表名 -- 需要查询的，具有父子节点的表名
WHERE FIND_IN_SET(up_one,cTemp)>0;
END WHILE;
RETURN pTemp;
END
10、Oracle嵌套函数转MySQL：
Oracle：
SELECT COUNT(COUNT(1)) FROM S_SMS_LOG where
BR_NO IN( SELECT BR_NO FROM S_ORG_INFO START WITH BR_NO = '00000' CONNECT BY PRIOR BR_NO = UP_ONE )
GROUP BY TX_DATE,BR_NO,PRDT_GROUP,PRDT_NO
MySQL：
select count(1) from (select count(1),BR_NO from s_sms_log GROUP BY TX_DATE,BR_NO,PRDT_GROUP,PRDT_NO ) c
where BR_NO in
(SELECT BR_NO FROM S_ORG_INFO
where FIND_IN_SET(br_no,getChildId_bas_basic_tree('00000')))
11、Oracle序列实现自增，MySQL没有序列，为了程序移植方便，设置具有相同功能的方法：
-- 创建序列表
CREATE TABLE `sequence` (
`name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT '序列的名字',
`current_value` int(11) NOT NULL COMMENT '序列的当前值',
`increment` int(11) NOT NULL DEFAULT '1' COMMENT '序列的自增值',
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 创建 获取当前值的 function
DROP FUNCTION IF EXISTS currval;
DELIMITER $
CREATE FUNCTION currval (seq_name VARCHAR(50))
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE value INTEGER;
SET value = 0;
SELECT current_value INTO value
FROM sequence
WHERE name = seq_name;
RETURN value;
END
$
DELIMITER ;
-- 创建 获取下一个值的 function
DROP FUNCTION IF EXISTS nextval;
DELIMITER $
CREATE FUNCTION nextval (seq_name VARCHAR(50))
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
UPDATE sequence
SET current_value = current_value + increment
WHERE name = seq_name;
RETURN currval(seq_name);
END
$
DELIMITER ;
-- 创建 设置当前值的 function （基本不用）
DROP FUNCTION IF EXISTS setval;
DELIMITER $
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER)
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
UPDATE sequence
SET current_value = value
WHERE name = seq_name;
RETURN currval(seq_name);
END
$
DELIMITER ;
INSERT INTO sequence VALUES ('SMS_SEQID_SEQUENCE_MQ', 1, 1); -- 添加一个sequence名称和初始值，以及自增幅度
SELECT SETVAL('SMS_SEQID_SEQUENCE_MQ', 10); -- 设置指定sequence的初始值
SELECT CURRVAL('SMS_SEQID_SEQUENCE_MQ'); -- 查询指定sequence的当前值
SELECT NEXTVAL('SMS_SEQID_SEQUENCE_MQ') ; -- 查询指定sequence的下一个值
12、mysql格式化某字段(1—>00001)
update 表名 set 字段名 = LPAD(字段名, 4, 0)
13、Oracle取结果集第一行数据(rownum = 1)转换为MySQL(limit 1)
Oracle：
SELECT menu_parent
FROM S_MENU_INFO
WHERE menu_url like concat('%',#menuUrl#,'%')
AND rownum = 1
改为：
MySQL：
SELECT menu_parent
FROM S_MENU_INFO
WHERE menu_url like concat('%',#menuUrl#,'%')
limit 1
14、Oracle中的NVL()—>MySQL中的IFNULL()
在oracle中NVL（表达式1，表达式2）
NVL(E1, E2)的功能为：如果E1为NULL，则函数返回E2，否则返回E1本身
其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。
对于在MySQL中有ifnull对应
IFNULL(expression_1,expression_2);
如果expression_1不为NULL，则IFNULL函数返回expression_1; 否则返回expression_2的结果。
————————————————
版权声明：本文为CSDN博主「名字只是一个称呼」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_32003379/article/details/86501087
15、Oracle数组转为Mysql：（采用临时表，这个没有测试过）
1、声明初始化变量：
DECLARE arrLength INT DEFAULT 0;/*定义数组长度*/
DECLARE arrString VARCHAR(1000);/*定义初始数组字符*/
DECLARE sStr VARCHAR(1000);/*定义初始字符*/
2、获取传入参数数组长度：
SET arrLength = LENGTH(arrayStr) - LENGTH(REPLACE(arrayStr,sSplit,''));/*获得数组长度*/
SET arrString = arrayStr;/*赋值*/
3、创建临时表：
DROP TEMPORARY TABLE IF EXISTS list_tmp;
create temporary table list_tmp(id VARCHAR(32));/*定义临时表*/
4、截取数组字符串并依次存入到临时表中供后面业务使用：
WHILE arrLength > 0 DO
set sStr = substr(arrString,1,instr(arrString,sSplit)-1); -- 得到分隔符前面的字符串
set arrString = substr(arrString,length(sStr)+length(sSplit)+1); -- 得到分隔符后面的字符串
set arrLength = arrLength -1;
set @str = trim(sStr);
insert into list_tmp(id) values(@str);
END WHILE;
二、格式例子：
Oracle函数：
一：
CREATE OR REPLACE FUNCTION FN_AMT_CHECK
(
v_amt VARCHAR2 --日期入参
)
return number -- 返回1为正确，0为错误。
as
/*------------------------------------------------------------------------
公用函数：金额检查函数
说明：金额为数字，范围为 100~2,000,000，精度保留小数点后两位。
调用范例: select FN_AMT_CHECK('213123.33') from dual;
------------------------------------------------------------------------*/
begin
IF NOT regexp_like(v_amt,'^([0-9.-]+)$') THEN
return 0;
END IF;
IF regexp_count(v_amt,'\.')>1 THEN
RETURN 0;
END IF;
IF regexp_count(v_amt,'\-')>1 THEN
RETURN 0;
END IF;
RETURN 1;
END;
二：
CREATE OR REPLACE function check_date_format(v_datestr VARCHAR2)
return number -- 返回1为正确，0为错误
as
i_year number; --年
i_month number; --月
i_day number; --日
begin
if v_datestr is null then
return 1;
end if;
if length(trim(v_datestr)) <> 8 then
return 0;
end if;
-- 判断日期由数字组成
if regexp_substr(trim(v_datestr),'[[:digit:]]+') is null then
return 0;
end if;
-- 截取出年份
i_year:=to_number(substr(rtrim(v_datestr),1,4));
-- 截取出月份
i_month:=to_number(substr(rtrim(v_datestr),5,2));
-- 截取出日期
i_day:=to_number(substr(rtrim(v_datestr),7,2));
--年份不能小于1900
if i_year<1900 then
begin
return 0;
end;
end if;
-- 对月份进行判断，必须在1月到12月范围之内
if i_month not between 1 and 12 then
begin
return 0;
end;
end if;
-- 对日期的判断，1，3，5，7，8，10，12月最大日为31，4，6，9，11月最大日为30，2月若为闰年则为29，其它年则为28.
if i_day between 1 and 31 then
begin
if i_day=31 and i_month not in (1,3,5,7,8,10,12) then
begin
return 0;
end;
end if;
if i_month=2 then
begin
-- Rules 1：普通年能被4整除且不能被100整除的为闰年。
-- Rules 2：世纪年能被400整除的是闰年。
-- Rules 3：对于数值很大的年份,这年如果能整除3200,并且能整除172800则是闰年。如172800年是闰年，86400年不是闰年。
if ((mod(i_year,4)=0 and mod(i_year,100)<>0)
or mod(i_year,400)=0
or (mod(i_year,3200)=0 and mod(i_year,172800)=0)) then
begin
--若为闰年，则2月份最大日为29
if i_day>29 then
begin
return 0;
end;
end if;
end;
else
begin
--若不为闰年，则2月份最大日为28
if i_day>28 then
begin
return 0;
end;
end if;
end ;
end if;
end;
end if;
return 1;
end;
else
return 0;
end if;
end;
对应的MySQL函数
一：
CREATE DEFINER=`root`@`%` FUNCTION `FN_AMT_CHECK`(`v_amt` varchar(255)) RETURNS int
BEGIN
IF NOT regexp_like(v_amt,'^([0-9.-]+)$') THEN
return 0;
END IF;
IF (LENGTH(v_amt) - LENGTH(REPLACE(v_amt,'\.','')))>1 THEN
RETURN 0;
END IF;
IF (LENGTH(v_amt) - LENGTH(REPLACE(v_amt,'\-','')))>1 THEN
RETURN 0;
END IF;
RETURN 1;
END
二：
CREATE DEFINER=`root`@`%` FUNCTION `CHECK_DATE_FORMAT`(`v_datestr` varchar(255)) RETURNS int
BEGIN
declare i_year int; -- 年
declare i_month int; -- 月
declare i_day int; -- 日
if v_datestr is null then
return 1;
end if;
if length(trim(v_datestr)) <> 8 then
return 0;
end if;
-- 判断日期由数字组成
if regexp_substr(trim(v_datestr),'[[:digit:]]+') is null then
return 0;
end if;
-- 截取出年份
-- set i_year=to_number(substr(rtrim(v_datestr),1,4));
set i_year=cast((substr(rtrim(v_datestr),1,4)) as SIGNED INTEGER);
-- 截取出月份
-- set i_month=to_number(substr(rtrim(v_datestr),5,2));
set i_month=cast((substr(rtrim(v_datestr),5,2)) as SIGNED INTEGER);
-- 截取出日期
-- set i_day=to_number(substr(rtrim(v_datestr),7,2));
set i_day=cast((substr(rtrim(v_datestr),7,2)) as SIGNED INTEGER);
-- 年份不能小于1900
if i_year<1900 then
begin
return 0;
end;
end if;
-- 对月份进行判断，必须在1月到12月范围之内
if i_month not between 1 and 12 then
begin
return 0;
end;
end if;
-- 对日期的判断，1，3，5，7，8，10，12月最大日为31，4，6，9，11月最大日为30，2月若为闰年则为29，其它年则为28.
if i_day between 1 and 31 then
begin
if i_day=31 and i_month not in (1,3,5,7,8,10,12) then
begin
return 0;
end;
end if;
if i_month=2 then
begin
-- Rules 1：普通年能被4整除且不能被100整除的为闰年。
-- Rules 2：世纪年能被400整除的是闰年。
-- Rules 3：对于数值很大的年份,这年如果能整除3200,并且能整除172800则是闰年。如172800年是闰年，86400年不是闰年。
if ((mod(i_year,4)=0 and mod(i_year,100)<>0)
or mod(i_year,400)=0
or (mod(i_year,3200)=0 and mod(i_year,172800)=0)) then
begin
-- 若为闰年，则2月份最大日为29
if i_day>29 then
begin
return 0;
end;
end if;
end;
else
begin
-- 若不为闰年，则2月份最大日为28
if i_day>28 then
begin
return 0;
end;
end if;
end ;
end if;
end;
end if;
return 1;
end;
else
return 0;
end if;
END
有关Oracle序列转MySQL的方法，我是看的这个链接：https://blog.csdn.net/MonkeySun123321/article/details/83306030
1353
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
