关于数据库oracle存储过程的匹配算法

[ThinkBank_Chen]

表结构
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语句直接能算出结果的方法？

[ThinkBank_Chen]

CREATE OR REPLACE PROCEDURE SIMMR.Check_ORStatus_For_EcfFtn
(
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 ;

[ks_reny]

substr（）

[iqlife]

select * from SIMM_OR_FTN_PREFIX where PK_OR_FTN_PREFIX like 'ftn%'

???
有点看不太明白..

[cyousor]

select count（1）into vv from SIMM_OR_FTN_PREFIX where PK_OR_FTN_PREFIX like 'ftn%'

vv> 0 有
vv = 0 没有


？？？？
不太明白

[ThinkBank_Chen]

用SIMM_OR_FTN_PREFIX来匹配ftn

[Quote=引用 2 楼 java3344520 的回复:]

select * from SIMM_OR_FTN_PREFIX where PK_OR_FTN_PREFIX like 'ftn%'

???
有点看不太明白..
[/Quote]

[xiaohu8855]

sql:='select * from SIMM_OR_FTN_PREFIX where PK_OR_FTN_PREFIX like '||chr(39)||ftn||'%'||chr(39);

完了后执行open sql for 游标

[kingkingzhu]

create or replace procedure t(ftn in varchar2, flag out number) is

cur sys_refcursor;
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;

[kingkingzhu]

记得关游标 忘了