SYSNAME BEFORE -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SAP^OMS^自动排产^APS^SRM^LPS^WMS^检测 1^0^1^0^0^1^0^1 SAP^OMS^自动排产^APS^SRM^LPS^WMS^检测 1^0^1^0^0^1^1^1
SQL> create or replace function func_get_name(i_name varchar2,i_str varchar2) 2 return varchar2 3 as 4 v_name varchar2(4000); 5 v_str varchar2(4000); 6 v_result varchar2(4000); 7 v_num varchar2(1); 8 begin 9 if i_str is null then 10 return null; 11 end if; 12 v_name:=i_name||'^'; 13 v_str:=i_str; 14 v_result:=null; 15 for i in 1..ceil(length(i_str)+1)/2 loop 16 v_num:=substr(v_str,1,1); 17 if v_num='1' then 18 select decode(v_result,null,v_result,v_result||',') into v_result from dual; 19 v_result:=v_result||substr(v_name,1,instr(v_name,'^',1,1)-1); 20 end if; 21 v_str:=substr(v_str,3); 22 v_name:=substr(v_name,instr(v_name,'^',1,1)+1); 23 end loop; 24 return v_result; 25 end; 26 /
Function created
SQL> select t.sysname,t.before, func_get_name(t.sysname,t.before) from t_get_string t;
给出你建表的脚本和数据,以及想要的结果
怎么取的?逻辑没看懂
SYSNAME BEFORE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
SAP^OMS^自动排产^APS^SRM^LPS^WMS^检测 1^0^1^0^0^1^0^1
SAP^OMS^自动排产^APS^SRM^LPS^WMS^检测 1^0^1^0^0^1^1^1
SQL> create or replace function func_get_name(i_name varchar2,i_str varchar2)
2 return varchar2
3 as
4 v_name varchar2(4000);
5 v_str varchar2(4000);
6 v_result varchar2(4000);
7 v_num varchar2(1);
8 begin
9 if i_str is null then
10 return null;
11 end if;
12 v_name:=i_name||'^';
13 v_str:=i_str;
14 v_result:=null;
15 for i in 1..ceil(length(i_str)+1)/2 loop
16 v_num:=substr(v_str,1,1);
17 if v_num='1' then
18 select decode(v_result,null,v_result,v_result||',') into v_result from dual;
19 v_result:=v_result||substr(v_name,1,instr(v_name,'^',1,1)-1);
20 end if;
21 v_str:=substr(v_str,3);
22 v_name:=substr(v_name,instr(v_name,'^',1,1)+1);
23 end loop;
24 return v_result;
25 end;
26 /
Function created
SQL> select t.sysname,t.before, func_get_name(t.sysname,t.before) from t_get_string t;
SYSNAME BEFORE FUNC_GET_NAME(T.SYSNAME,T.BEFO
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
SAP^OMS^自动排产^APS^SRM^LPS^WMS^检测 1^0^1^0^0^1^0^1 SAP,自动排产,LPS,检测
SAP^OMS^自动排产^APS^SRM^LPS^WMS^检测 1^0^1^0^0^1^1^1 SAP,自动排产,LPS,WMS,检测
SQL>
--参照下,只是大概写了下
create or replace function TEST.YYQTEST1
RETURN Nvarchar2
IS TYPE StrArray IS TABLE OF Varchar2(12) INDEX BY BINARY_INTEGER;
lsBefor StrArray; -- BEFORE列save用数组
lsSysName StrArray; -- SYSNAME列save用数组
lsLength Number(12) -- 数组长度 wkBefore Nvarchar2(6000) --1^0^1^0 列值的临时存储
wkSysName Nvarchar2(6000) --SAP^OMS 列值的临时存储
wkLength1 Number(12) --1^0^1^0 列值的长度
wkLength2 Number(12) --SAP^OMS 列值的长度 wCnt Number(3)
wMoji Nvarchar2(60) tempStr Nvarchar2(60) --
resSubStr Nvarchar2(6000) ----游标创建
Cursor CUR_NAME IS
SELECT * FROM YYQ4; lrCUR_NAME CUR_NAME%TYPE;BEGIN
OPEN CUR_NAME;
LOOP
--循环游标
FETCH CUR_NAME INTO lrCUR_NAME;
EXIT WHEN CUR_NAME%NOTFOUND;
--
BEGIN
wkBefore = lrCUR_NAME.ID; --取得BEFORE列的值
wkSysName = lrCUR_NAME.NAME; --取得SYSNAME列的值 wkLength1 = LENGTH(wkBefore); --列长度的取得
wkLength2 = LENGTH(wkSysName);--与上面长度相同
--wkLength1 = wkLength2 wCnt := 1;
lsBefore(wCnt) := null;
--将BEFORE列的值按照‘^’进行分割,然后存到数组中
for widx in 1..wkLength1 Loop
wMoji := SUBSTR(wkBefore,widx,1);
If wMoji='^' Then
--
wCnt := wCnt + 1;
lsBefore(wCnt) := null;
Else
-- Save
lsBefore(wCnt) := lsBefore(wCnt) || wMoji;
End If;
End Loop; wCnt := 1;
lsSysName(wCnt) := null;
--将SYSNAME列的值按照‘^’进行分割,然后存到数组中
for widx in 1..wkLength2 Loop
wMoji := SUBSTR(wkSysName,widx,1);
If wMoji='^' Then
--
wCnt := wCnt + 1;
lsSysName(wCnt) := null;
Else
-- Save
lsSysName(wCnt) := lsSysName(wCnt) || wMoji;
End If;
End Loop;-------------lsBefore中存储的是BEFORE列按照‘^’符号分割后的数组值
-------------lsSysName中存储的是SYSNAME列按照‘^’符号分割后的数组值--根据数组长度,循环数组,分别取出这两个数组中对应的值,然后根据BEFORE列中分割后=‘1’的索引,取到SYSNAME列对应的值,将取到的值用逗号连接
lsLength := wCnt;
for widx in 1..lsLength Loop
tempStr := lsBefore(widx);
if tempStr = '1' Then
resSubStr := resSubStr |','|| lsSysName(widx);
End If;
End Loop;
Exception
When Others Then
resSubStr := '';
End;
End Loop; return resSubStr;End YYQTEST1;