加密函數?

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 '%函數名%'