CREATE OR REPLACE PROCEDURE GCAUSER.PROC_CERT_VALIDATION(V_DN IN VARCHAR,V_CERTSTAT OUT VARCHAR) AS
/******************************************************************************
NAME: PROC_CERT_VALIDATION
PURPOSE: 인증서를 경로 풀검증
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
0.5 2010-04-23 강혁 1.인증서검증
******************************************************************************/
v_issuerdn VARCHAR2(500);
v_dnloop VARCHAR2(500);
v_stat VARCHAR2(32);qui
v_loop NUMBER;
BEGIN
SELECT ISSUERDN,STAT into v_issuerdn ,v_stat
FROM PKIENTITYPKC
WHERE DN=V_DN;
--PRINT :v_stat;
IF v_stat!='GOOD' THEN
BEGIN
V_CERTSTAT := v_stat;
RETURN;
END;
END IF;
v_loop := 1;
IF v_stat='GOOD' THEN
BEGIN
WHILE v_loop=1 LOOP
BEGIN
SELECT ISSUERDN,STAT,DN into v_issuerdn ,v_stat,v_dnloop
FROM A A,B B
WHERE B.DN=v_issuerdn AND B.SID=A.ASID;
IF v_stat!='GOOD' THEN
BEGIN
V_CERTSTAT := 'FAIL';
RETURN;
END;
END IF;
IF v_issuerdn=v_dnloop THEN
BEGIN
IF v_stat!='GOOD' THEN
BEGIN
V_CERTSTAT := 'FAIL';
RETURN;
END;
ELSE
BEGIN
V_CERTSTAT := v_stat;
v_loop := 0;
RETURN;
END;
END IF;
END;
END IF;
END;
END LOOP;
END;
END IF;
/*EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;*/
END PROC_CERT_VALIDATION;
/
===========================================================================
sqlplus 에 접속해서
variable KKK VARCHAR2(32);
EXECUTE PROC_CERT_VALIDATION('인증서 DN값',:KKK);
print kkk
차례대로 입력한뒤 하면 결과값이 GOOD 또는 FAIL이 나오는 프로시져
