加密函数
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 '%函数名%'