DELIMITER $$
CREATE FUNCTION `to_date`(in_str varchar(50), in_format varchar(50)) RETURNS datetime
DETERMINISTIC
BEGIN
DECLARE my_format varchar(50);
set my_format = lower(in_format);
set my_format = REPLACE(my_format, 'yyyy', '%Y');
set my_format = REPLACE(my_format, 'mm', '%m');
set my_format = REPLACE(my_format, 'dd', '%d');
set my_format = REPLACE(my_format, 'hh24', '%H');
set my_format = REPLACE(my_format, 'hh', '%H');-- str_to_date not support %h
set my_format = REPLACE(my_format, 'mi', '%i');
set my_format = REPLACE(my_format, 'ss', '%s');
IF (locate('-',in_str) = 0) THEN
set my_format = REPLACE(my_format, '-', '');
END IF;
IF (locate(':',in_str) = 0) THEN
set my_format = REPLACE(my_format, ':', '');
END IF;
return str_to_date(in_str, my_format);
end $$
;
DELIMITER $$
CREATE FUNCTION to_char(in_datetime datetime, in_format varchar(50))
RETURNS varchar(50) DETERMINISTIC
BEGIN
DECLARE my_format varchar(50);
set my_format = lower(in_format);
set my_format = REPLACE(my_format, 'yyyy', '%Y');
set my_format = REPLACE(my_format, 'mm', '%m');
set my_format = REPLACE(my_format, 'dd', '%d');
set my_format = REPLACE(my_format, 'hh24', '%H');
set my_format = REPLACE(my_format, 'hh', '%h');
set my_format = REPLACE(my_format, 'mi', '%i');
set my_format = REPLACE(my_format, 'ss', '%s');
return date_format(in_datetime,my_format);
end $$
;
DELIMITER $$
CREATE FUNCTION to_number(in_param varchar(100))
RETURNS int DETERMINISTIC
BEGIN
return cast(in_param as unsigned int);
end $$
;
DELIMITER $$
CREATE FUNCTION `nvl`(in_param varchar(255), default_value varchar(255)) RETURNS varchar(255) CHARSET utf8mb4
DETERMINISTIC
BEGIN
return ifnull(in_param, default_value);
END $$
;
DELIMITER $$
CREATE FUNCTION suboracle(in_param varchar(255), from_index int, sub_length int)
RETURNS varchar(255) DETERMINISTIC
BEGIN
IF (from_index = 0) THEN
set from_index = 1;
END IF;
return substr(in_param, from_index, sub_length);
END $$
;
DELIMITER $$
CREATE FUNCTION DECODEUNICODE(in_str text)
RETURNS text DETERMINISTIC
begin
DECLARE code1,code2 VARCHAR(4);
DECLARE s_index SMALLINT UNSIGNED DEFAULT 0;
DECLARE out_str TEXT DEFAULT '';
DECLARE temp VARCHAR(1);
SET s_index = LOCATE("\u", in_str, 1);
WHILE s_index>0 DO
SET code1 = CONV(SUBSTRING(in_str, s_index+1, 2), 16, 10);
SET code2 = CONV(SUBSTRING(in_str, s_index+3, 2), 16, 10);
SET temp = CONVERT(CHAR(code1,code2) USING 'ucs2');
SET out_str = CONCAT(ifnull(out_str,''), ifnull(temp,''));
SET s_index = LOCATE("\u", in_str, s_index+1);
END WHILE ;
RETURN out_str;
END $$
;
DELIMITER $$
CREATE FUNCTION BITENCRYPT(in_str text)
RETURNS text DETERMINISTIC
begin
DECLARE out_str TEXT DEFAULT '';
declare chars varchar(100);
declare temp1,temp2 varchar(100);
declare my_key int;
declare tmp_arr,ret_arr varchar(255);
declare tmp_i,ret_i varchar(255);
declare idx1,idx2,i,retIdx int;
declare sub_str varchar(1);
declare OFFSET int default 4;
set chars = 'DE789opqrsabcdefghijklmnt456AUVWXYBCFGHuvwxyz0123ZIJKLMNOPQRST';
set temp1 = substring(in_str, length(in_str));--
set my_key = instr(chars, temp1) - 1;
set idx1 = length(in_str);
WHILE idx1>0 DO
SET sub_str = SUBSTRING(in_str, length(in_str) - idx1 + 1, 1);
set temp2 = instr(chars, sub_str) - 1;
set tmp_arr = concat(ifnull(tmp_arr,''), ifnull(temp2,''), ',');
SET idx1 = idx1 - 1;
END WHILE ;
set i = 0;
WHILE length(tmp_arr)>0 DO
SET idx2 = LOCATE(",", tmp_arr, 1);
SET tmp_i = SUBSTRING(tmp_arr, 1, idx2-1);
if (mod(my_key, 2) = 0) then
if (mod(i, 2) = 0) then
set retIdx = case when (tmp_i - my_key) < 0 then (tmp_i - my_key + length(chars)) else (tmp_i - my_key) end;
else
set retIdx = case when (tmp_i + my_key) > length(chars) - 1 then (tmp_i + my_key - length(chars)) else (tmp_i + my_key) end;
end if;
else
if (mod(i, 2) = 0) then
set retIdx = case when (tmp_i + my_key) > length(chars) - 1 then (tmp_i + my_key - length(chars)) else (tmp_i + my_key) end;
else
set retIdx = case when (tmp_i - my_key) < 0 then (tmp_i - my_key + length(chars)) else (tmp_i - my_key) end;
end if;
end if;
set ret_i = substring(chars, retIdx+1, 1);
set ret_arr = concat(ifnull(ret_arr,''), ifnull(ret_i,''));
set tmp_arr = substring(tmp_arr, idx2+1);
set i = i+1;
END WHILE ;
set ret_i = substring(chars, case when (my_key + OFFSET) > length(chars) - 1 then (my_key + OFFSET - length(chars)) else (my_key + OFFSET) end + 1, 1);
set ret_arr = concat(ifnull(substring(ret_arr, 1, length(ret_arr)-1),''), ifnull(ret_i,''));
set out_str = ret_arr;
RETURN out_str;
END $$
;
DELIMITER $$
CREATE FUNCTION DEBITENCRYPT(in_str text)
RETURNS text DETERMINISTIC
begin
DECLARE out_str TEXT DEFAULT '';
declare chars varchar(100);
declare temp1,temp2 varchar(100);
declare my_key int;
declare tmp_arr,ret_arr,tmp_while varchar(255);
declare tmp_i,ret_i varchar(255);
declare idx1,idx2,i,retIdx int;
declare sub_str varchar(1);
declare OFFSET int default 4;
set chars = 'DE789opqrsabcdefghijklmnt456AUVWXYBCFGHuvwxyz0123ZIJKLMNOPQRST';
set temp1 = substring(in_str, length(in_str));--
set my_key = instr(chars, temp1) - 1;
set my_key = case when (my_key - OFFSET) < 0 then (my_key - OFFSET + length(chars)) else (my_key - OFFSET) end;
set idx1 = length(in_str);
WHILE idx1>0 DO
SET sub_str = SUBSTRING(in_str, length(in_str) - idx1 + 1, 1);
set temp2 = instr(chars, sub_str) - 1;
set tmp_arr = concat(ifnull(tmp_arr,''), ifnull(temp2,''), ',');
SET idx1 = idx1 - 1;
END WHILE ;
set i = 0;
set tmp_while = tmp_arr;
WHILE length(tmp_while)>0 DO
SET idx2 = LOCATE(",", tmp_while, 1);
SET tmp_i = SUBSTRING(tmp_while, 1, idx2-1);
if (mod(my_key, 2) = 0) then
if (mod(i, 2) = 0) then
set retIdx = case when (tmp_i + my_key) > length(chars) - 1 then (tmp_i + my_key - length(chars)) else (tmp_i + my_key) end;
else
set retIdx = case when (tmp_i - my_key) < 0 then (tmp_i - my_key + length(chars)) else (tmp_i - my_key) end;
end if;
else
if (mod(i, 2) = 0) then
set retIdx = case when (tmp_i - my_key) < 0 then (tmp_i - my_key + length(chars)) else (tmp_i - my_key) end;
else
set retIdx = case when (tmp_i + my_key) > length(chars) - 1 then (tmp_i + my_key - length(chars)) else (tmp_i + my_key) end;
end if;
end if;
set ret_i = substring(chars, retIdx+1, 1);
set ret_arr = concat(ifnull(ret_arr,''), ifnull(ret_i,''));
set tmp_while = substring(tmp_while, idx2+1);
set i = i+1;
END WHILE ;
set ret_i = substring(chars, my_key + 1, 1);
set ret_arr = concat(ifnull(substring(ret_arr, 1, length(ret_arr)-1),''), ifnull(ret_i,''));
set out_str = ret_arr;
RETURN out_str;
END $$
;
标签：function,tmp,set,key,format,Mysql,str,sql,my From： https://www.cnblogs.com/xieqisheng666/p/16963207.html