oracle函数在mysql用

-- ----------------------------
-- Function structure for add_months
-- ----------------------------
DROP FUNCTION IF EXISTS `add_months`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `add_months`(in_string VARCHAR (255),
    in_string_month VARCHAR (255)) RETURNS varchar(255) CHARSET utf8
BEGIN

RETURN DATE_ADD(in_string, interval in_string_month month);
END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for decodeo
-- ----------------------------
DROP FUNCTION IF EXISTS `decodeo`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `decodeo`(a    VARCHAR(255),
 b VARCHAR(255),c  VARCHAR(255),d  VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
BEGIN
      
    
      RETURN if(a=b,c,d);
    
    END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for nvl
-- ----------------------------
DROP FUNCTION IF EXISTS `nvl`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `nvl`(
    in_string_one VARCHAR (255),
in_string_two VARCHAR (255)
) RETURNS varchar(255) CHARSET utf8
BEGIN

RETURN IFNULL(in_string_one,in_string_two);
END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for substr
-- ----------------------------
DROP FUNCTION IF EXISTS `substr`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `substr`(
    in_string VARCHAR (255),
    in_string_one VARCHAR (255),
in_string_two VARCHAR (255)
) RETURNS varchar(255) CHARSET utf8
BEGIN

RETURN substring(in_string,in_string_one,in_string_two);
END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for to_char
-- ----------------------------
DROP FUNCTION IF EXISTS `to_char`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `to_char`(in_string VARCHAR (255),
    in_find_str VARCHAR (255)) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE returnValue VARCHAR(255);
SET returnValue = lower(in_find_str);
IF LOCATE('yyyy', returnValue) > 0 THEN
    select REPLACE (returnValue, 'yyyy', '%Y') into returnValue;
end if;
IF LOCATE('mm', returnValue) > 0 THEN
    select REPLACE (returnValue, 'mm', '%m') into returnValue;
end if;

if
    LOCATE('dd', returnValue) > 0 THEN
        select REPLACE (returnValue, 'dd', '%d') into returnValue;
end if;

if
    LOCATE('hh24:mi:ss', returnValue) > 0 THEN
        select REPLACE (returnValue, 'hh24:mi:ss', '%H:%i:%s') into returnValue;

end if;

if
    LOCATE('W', returnValue) > 0 THEN
    set @d=in_string;
    RETURN weekofyear(@d)-weekofyear(@d-interval day(@d)-1 day)+1;

end if;

RETURN date_format(in_string,returnValue);
END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for to_date
-- ----------------------------
DROP FUNCTION IF EXISTS `to_date`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `to_date`(in_string VARCHAR (255),
    in_find_str VARCHAR (255)) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE returnValue VARCHAR(255);
SET returnValue = lower(in_find_str);
IF LOCATE('yyyy', returnValue) > 0 THEN
    select REPLACE (returnValue, 'yyyy', '%Y') into returnValue;
end if;
IF LOCATE('mm', returnValue) > 0 THEN
    select REPLACE (returnValue, 'mm', '%m') into returnValue;
end if;

if
    LOCATE('dd', returnValue) > 0 THEN
        select REPLACE (returnValue, 'dd', '%d') into returnValue;
end if;

if
    LOCATE('hh24:mi:ss', returnValue) > 0 THEN
        select REPLACE (returnValue, 'hh24:mi:ss', '%H:%i:%s') into returnValue;

end if;


RETURN STR_TO_DATE(in_string,returnValue);
END
;;
DELIMITER ;

转载于:https://www.cnblogs.com/xzhg/p/6473734.html