oracle 加解密函数

加密函数 

CREATE OR REPLACE FUNCTION ENCODE_FUNCTION(
        IN_CODE VARCHAR DEFAULT NULL
) RETURN VARCHAR AS pragma autonomous_transaction;
CODE VARCHAR(2000);
OUTPARAMS VARCHAR2(2000);
BEGIN
    CODE := '密钥';
    select utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(CODE || IN_CODE))) INTO OUTPARAMS from dual;
    RETURN OUTPARAMS;
END;

解密函数 

CREATE OR REPLACE FUNCTION DECODE_FUNCTION(
        IN_CODE VARCHAR DEFAULT NULL
) RETURN VARCHAR AS pragma autonomous_transaction;
CODE VARCHAR(2000);
OUTPARAMS VARCHAR2(2000);
BEGIN
    CODE := '密钥';
    select substr((select utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(IN_CODE))) from dual), LENGTH(CODE)+1) INTO OUTPARAMS from dual;
    RETURN OUTPARAMS;
END;

创建一个更新数据，更新成功的 

CREATE OR REPLACE FUNCTION WAGE_ENCODE_FUNCTION(
    IN_YSNY VARCHAR DEFAULT NULL,
        IN_YSBM VARCHAR DEFAULT NULL
) RETURN VARCHAR AS pragma autonomous_transaction;
YSNYVAR VARCHAR(10);
BEGIN
    YSNYVAR := NVL(IN_YSNY, to_char(sysdate, 'yyyy-mm'));
    
    UPDATE UF_XYSBZ_FYMX_ZG_ENCODE SET YT = (
         SELECT ENCODE_FUNCTION(YT) FROM dual
    ) WHERE YSNY = YSNYVAR AND TXRBMID = IN_YSBM;
    commit;
    RETURN '数据更新成功';
END;

CREATE OR REPLACE FUNCTION     创建或替换一个函数。后面跟函数名称

WAGE_ENCODE_FUNCTION                       函数名称

IN_YSNY 、IN_YSBM   入参 默认值为null

RETURN '数据更新成功';               返回值 函数的返回值

AS pragma autonomous_transaction;   事物自治，进行更新、删除、插入操作是要commit的

YSNYVAR := NVL(IN_YSNY, to_char(sysdate, 'yyyy-mm'));                          给变量赋值

SELECT ENCODE_FUNCTION(YT) FROM dual   调用函数ENCODE_FUNCTION函数名

传入三个参数(字符串，想要截取的值,截取后第几个‘下标从0开始’)

create or replace function GetElementFromArrayByIndex(
Liststr in varchar2,
sPlitVal in varchar2,
iPos integer) return varchar2 is
 
/*
 
Liststr--传入将要被分割的字符串
 
sPlitVal--用来分割的字符串
 
iPos--获取分割后的数组中该位置的元素值
 
*/
 
type tt_type is table of varchar2(100) INDEX BY BINARY_INTEGER;
 
V1 tt_type;
 
--FieldNames转化为数组
 
TmpStr varchar2(100);
 
Str varchar2(4000);
 
j integer;
 
begin
 
Str := Liststr;
 
j := 0;
 
IF Instr(Liststr, sPlitVal, 1, 1) = 0 THEN
 
V1(j) := Liststr;
 
j := j + 1;
 
else
 
While Instr(str, sPlitVal, 1, 1) > 0 Loop
 
TmpStr := Substr(str, 1, Instr(str, sPlitVal, 1, 1) - 1);
 
V1(j) := TmpStr;
 
str := SubStr(Str, Instr(str, sPlitVal, 1, 1) + length(sPlitVal), length(str));
 
j := j + 1;
 
end loop;
 
if not str is null then
 
--将最后一个保存
 
V1(j) := str;
 
j := j + 1;
 
end if;
 
end if;
 
if iPos>j-1 or iPos<0 then
 
 
 
return '';
 
end if;
 
return V1(ipos);
 
end;

删除函数：

DROP FUNCTION 函数名;

 查看函数中的sql

SELECT text FROM ALL_SOURCE  where TYPE='FUNCTION'  AND name LIKE '%函数名%'