oracle的存储过程转sqlserver有大神可以帮忙么？

s5535762 2017-09-18 06:15:00

create or replace
PACKAGE BODY Pck_Mk AS
MK RAW(2000) :=NULL;
V_USER_ID VARCHAR2(100):=NULL;
ENABLE_ENCRYPTION BOOLEAN := TRUE;
RAISE_ERROR BOOLEAN := TRUE;

FUNCTION trimr(VALUE RAW) RETURN RAW
IS
startidx INTEGER;
endidx INTEGER;
b_spaces BOOLEAN;
BEGIN
startidx:=1;
b_spaces:=FALSE;
LOOP
IF startidx>utl_raw.LENGTH(VALUE) THEN
startidx:=1;
b_spaces:=TRUE;
EXIT;
ELSIF SUBSTR(VALUE,startidx,2) <> '20' THEN
EXIT;
END IF;
startidx:=startidx+2;
END LOOP;
IF b_spaces THEN
RETURN NULL;
END IF;
endidx:=LENGTH(VALUE);
LOOP
IF endidx<1 THEN
endidx:=utl_raw.LENGTH(VALUE);
EXIT;
ELSIF SUBSTR(VALUE,endidx-1,2) <> '20' THEN
EXIT;
END IF;
endidx:=endidx-2;
END LOOP;
RETURN SUBSTR(VALUE,startidx,endidx-startidx+1);
END;

PROCEDURE enableMK(val BOOLEAN)
AS
BEGIN
ENABLE_ENCRYPTION:=val;
END;

FUNCTION create_master_key(MKK RAW) RETURN RAW
IS
v_num_string VARCHAR2(1000);
v_num_string_len INTEGER;
NMK VARCHAR2(2000);
EMK RAW(2000);
Val RAW(2000):=NULL;
MK_LEN CONSTANT INTEGER :=16;
BEGIN
SELECT TO_CHAR(ROUND(dbms_random.VALUE*100)) INTO v_num_string FROM dual;
v_num_string_len := LENGTH(v_num_string);
NMK := dbms_random.string('U', MK_LEN-v_num_string_len);
NMK := NMK || v_num_string;
DBMS_OUTPUT.PUT_LINE(NMK);
EMK := dbms_obfuscation_toolkit.des3encrypt(input => utl_raw.cast_to_raw(NMK) , KEY => MKK);
RETURN EMK;
END;

PROCEDURE pip_mk(EMK RAW, TIMEOUT NUMBER DEFAULT 30)
IS
date_time VARCHAR2(15);
status INTEGER;
pipe_name VARCHAR2(100);
BEGIN
status := DBMS_PIPE.REMOVE_PIPE('MK3');
SELECT TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') INTO date_time FROM DUAL;
pipe_name := RTRIM(DBMS_PIPE.UNIQUE_SESSION_NAME) || RTRIM(date_time);
DBMS_PIPE.PACK_MESSAGE(EMK);
status := DBMS_PIPE.SEND_MESSAGE('MK3', TIMEOUT);
END;

PROCEDURE pip_mk(TIMEOUT NUMBER DEFAULT 30)
IS
master_file BFILE := NULL;
dest_lob BLOB;
len INTEGER;
buffer RAW(2000);
BEGIN
master_file := BFILENAME('MK_DIR', 'mk.txt');
dbms_lob.fileopen(master_file, dbms_lob.file_readonly);
IF (dbms_lob.fileisopen(master_file)=1) THEN
len:=dbms_lob.getlength(master_file);
--dbms_lob.loadfromfile(dest_lob,master_file,32);
dbms_lob.READ(master_file,len,1,buffer);
pip_mk(buffer, TIMEOUT);
dbms_lob.fileclose(master_file);
END IF;
END;

FUNCTION load_mk(SYS_USER VARCHAR2, pwd VARCHAR2, PWDK RAW, MKK RAW, TIMEOUT NUMBER DEFAULT 30) RETURN NUMBER
IS
status INTEGER;
EMK RAW(2000);
v_sys_user VARCHAR2(100) := 'DEFAULT_USER';
v_passwd RAW(100) :='822A0953B70AB834';
check_user BOOLEAN := TRUE;
e EXCEPTION;
BEGIN
-- jd (27-Aug-2004): so that the MK will not be loaded again
IF (MK IS NOT NULL) THEN
RETURN 0;
END IF;
IF (SYS_USER = v_sys_user) THEN
check_user := v_passwd <> enc(pwd, PWDK);
END IF;

IF check_user THEN
SELECT user_id INTO v_user_id FROM SYS_USER WHERE SYS_USER = load_mk.SYS_USER AND passwd = enc(load_mk.pwd, PWDK);
END IF;
status := DBMS_PIPE.RECEIVE_MESSAGE('MK3', TIMEOUT);
IF status = 0 THEN
DBMS_PIPE.UNPACK_MESSAGE(EMK);
pip_mk(EMK, TIMEOUT);
ELSE
RAISE e;
END IF;
MK := dbms_obfuscation_toolkit.des3decrypt(input => EMK , KEY => MKK);
RETURN 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Pck_Az_Error.raise_error('ISWEB_6000');
WHEN OTHERS THEN
Pck_Az_Error.raise_error('ISWEB_6100');

--when others then raise_application_error(-20000,'Master key is not available.');
END;

FUNCTION enc(enc_fld VARCHAR2) RETURN RAW
IS
BEGIN
RETURN encr(enc_fld => utl_raw.cast_to_raw(trim (' ' FROM enc.enc_fld)));
END;

FUNCTION enc(enc_fld VARCHAR2, enc_k RAW) RETURN RAW
IS
BEGIN
RETURN encr(enc_fld => utl_raw.cast_to_raw(trim (' ' FROM enc.enc_fld) ), enc_k => enc.enc_k);
END;

FUNCTION encr(enc_fld RAW) RETURN RAW
IS
BEGIN
IF ENABLE_ENCRYPTION AND MK IS NULL THEN
IF RAISE_ERROR THEN
Pck_Az_Error.raise_error('ISWEB_6200');
END IF;
RETURN NULL;
ELSE
RETURN encr(enc_fld => encr.enc_fld, enc_k => MK);
END IF;
END;

FUNCTION encr(enc_fld RAW, enc_k RAW) RETURN RAW
IS
Val RAW(2000):=NULL;
ValTrimmed RAW(2000):=NULL;
padlen INTEGER;
BEGIN
IF enc_fld IS NOT NULL THEN
IF ENABLE_ENCRYPTION THEN
padlen := utl_raw.LENGTH(enc_fld);
IF MOD(padlen, 8) <> 0 THEN
padlen := padlen + 8 - MOD(padlen, 8);
END IF;
padlen := padlen*2;
ValTrimmed:=trimr(enc_fld);
IF ValTrimmed IS NOT NULL THEN
Val := dbms_obfuscation_toolkit.des3encrypt(input => RPAD(trimr(enc_fld),padlen,'20') , KEY => enc_k);
ELSE
Val:=NULL;
END IF;
ELSE
Val := enc_fld;
END IF;
END IF;
RETURN Val;
END;

FUNCTION DEC(dec_fld RAW) RETURN VARCHAR2
IS
BEGIN
IF ENABLE_ENCRYPTION AND MK IS NULL THEN
IF RAISE_ERROR THEN
Pck_Az_Error.raise_error('ISWEB_6200');
END IF;
RETURN NULL;
ELSE
RETURN DEC(dec_fld => DEC.dec_fld, dec_k => MK);
END IF;
END;

FUNCTION DEC(dec_fld RAW, dec_k RAW) RETURN VARCHAR2
IS
Val VARCHAR2(255):=NULL;
BEGIN
IF dec_fld IS NOT NULL THEN
IF ENABLE_ENCRYPTION THEN
Val := trim(utl_raw.cast_to_varchar2(dbms_obfuscation_toolkit.des3decrypt(input => dec_fld , KEY => dec_k)));
ELSE
Val := utl_raw.cast_to_varchar2(dec_fld);
END IF;
END IF;
RETURN Val;
END;

END Pck_Mk;

...全文