oracle mysql 函数的使用方法_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 ;