PROCEDURE SIMMR.Check_ORStatus_For_EcfFtnV2
(
ftn IN VARCHAR2(20) ,
m_result OUT NUMBER ,
m_Status OUT NUMBER
)
IS
BEGIN DECLARE
err_num NUMBER;
ftn_max_length NUMBER;
ftn_min_length NUMBER;
ftn_length NUMBER;
err_msg VARCHAR2(100);
ftn_prefix VARCHAR2(20);
BEGIN SELECT max(length('PK_OR_FTN_PREFIXV2')) into ftn_max_length, min(length('PK_OR_FTN_PREFIXV2')) into ftn_min_length from SIMM_OR_FTN_PREFIXV2;
ftn_length := length(ftn);
if ftn is NULL or ftn_min_length >ftn_length then
m_result := 0 ;
m_Status := 0 ;
else
if length(ftn) < ftn_max_length then
ftn_max_length := ftn_length;
end if
while ftn_min_length < ftn_max_length
ftn_prefix := SUBSTR(ftn,0,ftn_max_length);
SELECT COUNT(*) INTO m_result FROM SIMM_OR_FTN_PREFIXV2 WHERE PK_OR_FTN_PREFIX =ftn_prefix AND ENABLE = 1 ;
m_Status := 1 ;
if m_result >=1 then
ftn_max_length := min_length-1;
else
ftn_max_length := ftn_max_length-1;
end if
end loop;
end if
EXCEPTION WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100); INSERT INTO
SIMM_ERROR_LOG (TIMESTAMP , SP_NAME , ORACLE_ERR_NUM , ORACLE_ERR_DESCR ,USER_ERR_DESCR )
VALUES
(SYSDATE , 'Check_ORStatus_For_EcfFtnV2' , err_num , err_msg , m_cc ) ;
m_result := 0 ;
m_Status := 0 ;
COMMIT ; END ;END Check_ORStatus_For_EcfFtnV2 ;create table SIMM_OR_FTN_PREFIXV2
(
PK_OR_FTN_PREFIXV2 varchar(20) primary key,
ENABLE int not null,
PROVINCENO varchar(10),
CCNDC varchar(8)
);
(
PK_OR_FTN_PREFIX varchar(20) primary key,
ENABLE int not null,
PROVINCENO varchar(10),
CCNDC varchar(8)
);
PROCEDURE SIMMR.Check_ORStatus_For_EcfFtn
(
m_ftn IN VARCHAR2(20) ,
m_result OUT NUMBER ,
m_Status OUT NUMBER
)
IS
BEGIN DECLARE
err_num NUMBER;
ftn_max_length NUMBER;
ftn_min_length NUMBER;
ftn_length NUMBER;
err_msg VARCHAR2(100);
ftn_prefix VARCHAR2(20);
BEGIN SELECT MAX(LENGTH('PK_OR_FTN_PREFIX')) INTO ftn_max_length, MIN(LENGTH('PK_OR_FTN_PREFIX')) INTO ftn_min_length from SIMM_OR_FTN_PREFIX;
ftn_length := length(m_ftn);
IF ftn IS NULL OR ftn_min_length >ftn_length THEN
m_result := 0 ;
m_Status := 0 ;
ELSE
IF length(ftn) < ftn_max_length THEN
ftn_max_length := ftn_length;
END IF;
WHILE ftn_min_length < ftn_max_length
ftn_prefix := SUBSTR(m_ftn,0,ftn_max_length);
SELECT COUNT(*) INTO m_result FROM SIMM_OR_FTN_PREFIX WHERE PK_OR_FTN_PREFIX =ftn_prefix AND ENABLE = 1 ;
m_Status := 1 ;
IF m_result >=1 THEN
ftn_max_length := min_length-1;
ELSE
ftn_max_length := ftn_max_length-1;
END IF;
END LOOP;
END IF;
EXCEPTION WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100); INSERT INTO
SIMM_ERROR_LOG (TIMESTAMP , SP_NAME , ORACLE_ERR_NUM , ORACLE_ERR_DESCR ,USER_ERR_DESCR )
VALUES
(SYSDATE , 'Check_ORStatus_For_EcfFtn' , err_num , err_msg , m_cc ) ;
m_result := 0 ;
m_Status := 0 ;
COMMIT ; END ;END Check_ORStatus_For_EcfFtn ;
MIN(LENGTH('PK_OR_FTN_PREFIX')) INTO ftn_min_length
from SIMM_OR_FTN_PREFIX;SELECT INTO用法错误,
应该这样写
SELECT MAX(LENGTH('PK_OR_FTN_PREFIX')) ,MIN(LENGTH('PK_OR_FTN_PREFIX'))
INTO ftn_max_length,ftn_min_length
from SIMM_OR_FTN_PREFIX;
PROCEDURE SIMMR.Check_ORStatus_For_EcfFtn
(
m_ftn IN VARCHAR2(20) ,
m_result OUT NUMBER ,
m_Status OUT NUMBER
)
IS
err_num NUMBER;
ftn_max_length NUMBER;
ftn_min_length NUMBER;
ftn_length NUMBER;
err_msg VARCHAR2(100);
ftn_prefix VARCHAR2(20);
BEGIN SELECT MAX(LENGTH('PK_OR_FTN_PREFIX')), MIN(LENGTH('PK_OR_FTN_PREFIX')) INTO ftn_max_length,ftn_min_length from SIMM_OR_FTN_PREFIX;
ftn_length := length(m_ftn);
IF ftn IS NULL OR ftn_min_length >ftn_length THEN
m_result := 0 ;
m_Status := 0 ;
ELSE
IF length(ftn) < ftn_max_length THEN
ftn_max_length := ftn_length;
END IF;
WHILE ftn_min_length < ftn_max_length
ftn_prefix := SUBSTR(m_ftn,0,ftn_max_length);
SELECT COUNT(*) INTO m_result FROM SIMM_OR_FTN_PREFIX WHERE PK_OR_FTN_PREFIX =ftn_prefix AND ENABLE = 1 ;
m_Status := 1 ;
IF m_result >=1 THEN
ftn_max_length := min_length-1;
ELSE
ftn_max_length := ftn_max_length-1;
END IF;
END LOOP;
END IF;
EXCEPTION WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100); INSERT INTO
SIMM_ERROR_LOG (TIMESTAMP , SP_NAME , ORACLE_ERR_NUM , ORACLE_ERR_DESCR ,USER_ERR_DESCR )
VALUES
(SYSDATE , 'Check_ORStatus_For_EcfFtn' , err_num , err_msg , m_cc ) ;
m_result := 0 ;
m_Status := 0 ;
COMMIT ; END ;
--去掉开头的变量申明以及
SELECT max(length('PK_OR_FTN_PREFIXV2')) into ftn_max_length, min(length('PK_OR_FTN_PREFIXV2')) into ftn_min_length from SIMM_OR_FTN_PREFIXV2;
改成
SELECT MAX(LENGTH('PK_OR_FTN_PREFIX')), MIN(LENGTH('PK_OR_FTN_PREFIX')) INTO ftn_max_length,ftn_min_length from SIMM_OR_FTN_PREFIX;