--需要判断哪个,你就传递什么值,直接like就行! select count(*) from ( select 用户,wm_concat(物品) 物品 from table1 group by 用户 ) where 物品 like '%1,3%' and 用户 = '张三'--或者,用instr函数 select 用户,deocde(instr(物品,变量值),0,"错误","正确") from ( select 用户,wm_concat(物品) 物品 from table1 group by 用户 )
type sybs is table of number index by binary_integer;create function pd (cust varchar2,wp sybs) return varchar2 is str varchar2(50); v_num number; v_retu varchar2; begin select wm_concat(物品) into str from table where 用户=cust; for syb sybs in wp loop v_num:=instr(str,syb); if v_num=0 then v_retu:='错误'; exit; end if; v_retu:='正确'; end loop; return v_retu; end;
type sybs is table of number index by binary_integer;create function pd (cust varchar2,wp sybs)--参数为1用户,参数2为要判断的物品数组 return varchar2 is str varchar2(50); v_num number; v_retu varchar2; begin select wm_concat(物品) into str from table where 用户=cust; for syb sybs in wp loop v_num:=instr(str,syb); if v_num=0 then v_retu:='错误'; exit; end if; v_retu:='正确'; end loop; return v_retu; end;
CREATE FUNCTION CS(V_NAME VARCHAR2, V_STR VARCHAR2) RETURN VARCHAR2 IS V_NUM NUMBER; V_FLAG VARCHAR2(10); BEGIN FOR I IN (SELECT REGEXP_SUBSTR(V_STR, '[^,]+', 1, ROWNUM) STR FROM DUAL CONNECT BY ROWNUM <= LENGTH(REGEXP_REPLACE(V_STR, '[^,]+')) + 1) LOOP V_NUM:=INSTR(V_NAME,I.STR); IF V_NUM = 0 THEN V_FLAG := '错误'; EXIT; ELSE V_FLAG := '正确'; END IF; END LOOP; RETURN V_FLAG; END;SELECT NAME, CS(WM_CONCAT(物品),变量值) FROM table1 GROUP BY NAME
select (case when t.姓名 like '%'|| '物品1'||'%' and t.roleid like '%'||'物品2''||'%' then '正确' else '不正确' end) from ( select 姓名,wm_concat(物品) 物品from table group by 姓名 ) t where t.姓名= '张三'
select count(*) from (
select 用户,wm_concat(物品) 物品 from table1 group by 用户
)
where 物品 like '%1,3%'
and 用户 = '张三'--或者,用instr函数
select 用户,deocde(instr(物品,变量值),0,"错误","正确")
from (
select 用户,wm_concat(物品) 物品 from table1 group by 用户
)
return varchar2 is
str varchar2(50);
v_num number;
v_retu varchar2;
begin
select wm_concat(物品) into str from table where 用户=cust;
for syb sybs in wp loop
v_num:=instr(str,syb);
if v_num=0 then
v_retu:='错误';
exit;
end if;
v_retu:='正确';
end loop;
return v_retu;
end;
return varchar2 is
str varchar2(50);
v_num number;
v_retu varchar2;
begin
select wm_concat(物品) into str from table where 用户=cust;
for syb sybs in wp loop
v_num:=instr(str,syb);
if v_num=0 then
v_retu:='错误';
exit;
end if;
v_retu:='正确';
end loop;
return v_retu;
end;
CREATE FUNCTION CS(V_NAME VARCHAR2, V_STR VARCHAR2) RETURN VARCHAR2 IS
V_NUM NUMBER;
V_FLAG VARCHAR2(10);
BEGIN
FOR I IN (SELECT REGEXP_SUBSTR(V_STR, '[^,]+', 1, ROWNUM) STR
FROM DUAL
CONNECT BY ROWNUM <= LENGTH(REGEXP_REPLACE(V_STR, '[^,]+')) + 1) LOOP
V_NUM:=INSTR(V_NAME,I.STR);
IF V_NUM = 0 THEN
V_FLAG := '错误';
EXIT;
ELSE
V_FLAG := '正确';
END IF;
END LOOP;
RETURN V_FLAG;
END;SELECT NAME, CS(WM_CONCAT(物品),变量值) FROM table1 GROUP BY NAME
(case
when t.姓名 like '%'|| '物品1'||'%' and t.roleid like '%'||'物品2''||'%' then
'正确'
else
'不正确'
end)
from (
select 姓名,wm_concat(物品) 物品from table group by 姓名
) t where t.姓名= '张三'