表结构
create table SIMM_OR_FTN_PREFIX
(
PK_OR_FTN_PREFIX varchar(20) primary key,
ENABLE int not null,
PROVINCENO varchar(10),
CCNDC varchar(8)
);需求:
存储过程参数:
输入参数:一个字符串ftn,
输出参数:返回SIMM_OR_FTN_PREFIX.PK_OR_FTN_PREFIX 是不是存在输入参数ftn的前缀的结果在存储过程中我该怎么实现这个匹配算法?
主要是在oracle中分割字符串和使用游标的方法不很不熟悉,请赐教。有没有直接用一般SQL语句直接能算出结果的方法?
create table SIMM_OR_FTN_PREFIX
(
PK_OR_FTN_PREFIX varchar(20) primary key,
ENABLE int not null,
PROVINCENO varchar(10),
CCNDC varchar(8)
);需求:
存储过程参数:
输入参数:一个字符串ftn,
输出参数:返回SIMM_OR_FTN_PREFIX.PK_OR_FTN_PREFIX 是不是存在输入参数ftn的前缀的结果在存储过程中我该怎么实现这个匹配算法?
主要是在oracle中分割字符串和使用游标的方法不很不熟悉,请赐教。有没有直接用一般SQL语句直接能算出结果的方法?
有点看不太明白..
vv = 0 没有
????
不太明白
用SIMM_OR_FTN_PREFIX来匹配ftn
len number;
n SIMM_OR_FTN_PREFIX.PK_OR_FTN_PREFIX %type; sub SIMM_OR_FTN_PREFIX.PK_OR_FTN_PREFIX %type;
begin
flag := 0;
open cur for
select PK_OR_FTN_PREFIX , length(PK_OR_FTN_PREFIX ) from SIMM_OR_FTN_PREFIX;
loop
fetch cur
into n, len;
exit when cur%notfound;
select substr(ftn, 1, len) into sub from dual;
if sub = n then
flag := flag+1;
end if;
end loop;
dbms_output.put_line(flag);
end;
(
m_ftn IN SIMM_OR_FTN_PREFIX.OR_FTN_PREFIX%type,
m_result OUT NUMBER,
m_Status OUT NUMBER
)
IS
BEGIN DECLARE
err_num NUMBER;
prefix_max_length NUMBER;
prefix_min_length NUMBER;
ftn_length NUMBER;
err_msg VARCHAR2(100);
ftn_prefix VARCHAR2(20);
ftn VARCHAR2(20);
min_length NUMBER;
FTN_MAX_LENGTHFROM NUMBER; CURSOR cFtnPrefix IS SELECT OR_FTN_PREFIX FROM SIMM_OR_FTN_PREFIX where ENABLE = 1; BEGIN
SELECT max(LENGTH(OR_FTN_PREFIX)) maxftnlength INTO prefix_max_length from SIMM_OR_FTN_PREFIX order by maxftnlength;
SELECT MIN(LENGTH(OR_FTN_PREFIX)) minftnlength INTO prefix_min_length from SIMM_OR_FTN_PREFIX order by minftnlength;
ftn_length := length(m_ftn); IF m_ftn IS NULL OR prefix_min_length >ftn_length THEN m_result := 0 ;
m_Status := 0 ;
ELSE
IF ftn_length < prefix_max_length THEN
prefix_max_length := ftn_length;
END IF; WHILE prefix_min_length < prefix_max_length LOOP
FOR ftnPrefix in cFtnPrefix
LOOP
if ftnPrefix.OR_FTN_PREFIX = SUBSTR(m_ftn,0,prefix_max_length) THEN
m_status := 1 ;
m_result := 1 ;
prefix_max_length := prefix_min_length ;
RETURN ;
ELSE
prefix_max_length := prefix_max_length-1;
END IF;
END LOOP;
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_ftn ) ;
COMMIT ;
END ;
END Check_ORStatus_For_EcfFtn ;