PL/SQL 쿼리 쉼표로 구분된 문자열

PL/SQL 쿼리 쉼표로 구분된 문자열

저는 오라클 APEX에서 어플리케이션을 개발하고 있습니다.쉼표로 구분된 사용자 ID를 가진 문자열이 있습니다. 이렇게 보입니다.

45,4932,20,19

이 문자열은 다음과 같이 저장됩니다.

:P5_USER_ID_LIST

이 목록 내에 있는 모든 사용자를 찾을 수 있는 쿼리를 원합니다. 쿼리는 다음과 같습니다.

SELECT * FROM users u WHERE u.user_id IN (:P5_USER_ID_LIST);

Oracle 오류가 계속 발생합니다.번호가 올바르지 않습니다.문자열을 쿼리에 하드 코드화하면 작동합니다.다음과 같은 경우:

SELECT * FROM users u WHERE u.user_id IN (45,4932,20,19);

이것이 왜 문제가 되는지 아는 사람?

바인딩 변수는 값을 바인딩합니다. 이 경우 문자열 '45,4932,20,19'입니다.Randy가 제안한 대로 동적 SQL 및 연결을 사용할 수 있지만 사용자가 이 값을 수정할 수 없도록 주의해야 합니다. 그렇지 않으면 SQL 주입 문제가 발생합니다.

보다 안전한 경로는 PL/SQL 프로세스에서 Apex 컬렉션에 ID를 넣는 것입니다.

declare
    array apex_application_global.vc_arr2;
begin
    array := apex_util.string_to_table (:P5_USER_ID_LIST, ',');
    apex_collection.create_or_truncate_collection ('P5_ID_COLL');
    apex_collection.add_members ('P5_ID_COLL', array);
end;

그런 다음 쿼리를 다음으로 변경합니다.

SELECT * FROM users u WHERE u.user_id IN 
(SELECT c001 FROM apex_collections
 WHERE collection_name = 'P5_ID_COLL')

더 쉬운 해결책은 사용하는 것입니다.instr:

SELECT * FROM users u 
WHERE instr(',' || :P5_USER_ID_LIST ||',' ,',' || u.user_id|| ',', 1) !=0;

트릭:

',' || :P5_USER_ID_LIST ||','

의 끈을 에 ,45,4932,20,19,

',' || u.user_id|| ','

가지다, 즉,32,.32에 있는 것,4932,

저는 이런 상황에 여러 번 직면했고 여기에 제가 사용한 것이 있습니다.

SELECT * 
  FROM users u 
 WHERE ','||to_char(:P5_USER_ID_LIST)||',' like '%,'||to_char(u.user_id)||',%'

나는 같은 연산자를 사용했지만 당신은 여기서 당신의 아이템 P5_USER_라는 한가지 측면을 조금 조심해야 합니다.ID_LIST는 "'45,4932,20,19"이어야 하며 이는 정확한 숫자 "'45,'와 비교됩니다.

이와 같이 사용할 때 선택자는 실수하지 않을 것입니다: 5와 15, 155, 55.

한번 해보고 진행 상황 알려주세요;)

건배, 알렉스

"create"를 사용하지 않고 네이티브 쿼리 만들기쿼리/작성 Named쿼리"

이것이 문제가 되는 이유는 원하는 방식으로 인리스트를 바인딩할 수 없기 때문입니다. 거의 모든 사람이 Oracle(그리고 아마도 SQL!)을 학습할 때 최소한 한 번은 이러한 오류를 범하기 때문입니다.

문자열 '32,64,128'을 바인딩하면 다음과 같은 쿼리가 됩니다.

select ...
from t
where t.c1 in ('32,64,128')

Oracle의 경우 다음과 완전히 다릅니다.

select ...
from t
where t.c1 in (32,64,128)

첫 번째 예는 인 리스트에 단일 문자열 값이 있고 두 번째 예는 인 리스트에 3개의 숫자가 있습니다.잘못된 숫자 오류가 발생하는 이유는 Oracle이 문자열 '32,64,128'을 숫자로 캐스트하려고 시도하기 때문인데, 이는 문자열의 쉼표 때문에 수행할 수 없습니다.

이 "목록을 묶는 방법" 질문의 변형이 최근에 여기에 꽤 많이 등장했습니다.

일반적으로, 그리고 어떠한 PLSQL에도 의존하지 않고, SQL 주입을 걱정하거나 쿼리를 올바르게 바인딩하지 않는 경우, 다음과 같은 방법을 사용할 수 있습니다.

with bound_inlist
  as
  (
  select
    substr(txt,
           instr (txt, ',', 1, level  ) + 1,
           instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
           as token
    from (select ','||:txt||',' txt from dual)
  connect by level <= length(:txt)-length(replace(:txt,',',''))+1
  )
  select *
from bound_inlist a, users u
where a.token = u.id;

가능하다면 csv에 사용자 ID를 저장하지 않는 것이 가장 좋습니다!그들을 테이블에 놓거나 그 배열에 실패하는 등.csv 필드는 숫자로 바인딩할 수 없습니다.

WHERE ','|to_char(:P5_USER_)를 사용하지 마십시오.ID_LIST)||'|'','%'와 마찬가지로 '%',|to_char(u.user_id)|',%'는 사용자 테이블의 경우 전체 테이블 검색을 강제하기 때문에 영향이 적을 수 있지만 엔터프라이즈 환경의 다른 테이블에 대해서는 문제가 됩니다.

편집: regex 방식과 와일드카드 방식의 차이점을 설명하기 위해 스크립트를 만들었습니다.regex가 더 빠를 뿐만 아니라 훨씬 더 견고합니다.

-- Create table
create table CSV_TEST
(
  NUM NUMBER not null,
  STR VARCHAR2(20)
);


create sequence csv_test_seq;

begin
  for j in 1..10 loop
    for i in 1..500000 loop
     insert into csv_test( num, str ) values ( csv_test_seq.nextval, to_char( csv_test_seq.nextval ));
    end loop;
    commit;
  end loop;
end;
/

-- Create/Recreate primary, unique and foreign key constraints 
alter table CSV_TEST
  add constraint CSV_TEST_PK primary key (NUM)
  using index ;
alter table CSV_TEST
  add constraint CSV_TEST_FK unique (STR)
  using index;

select sysdate from dual;
select *
from csv_test t
where t.num in ( Select Regexp_Substr('100001, 100002,   100003   ,      100004, 100005','[^,]+', 1, Level) From Dual
                 Connect By Regexp_Substr('100001, 100002,100003, 100004, 100005', '[^,]+', 1, Level) Is Not Null);
select sysdate from dual;

select *
from csv_test t
where ('%,' || '100001,100002,   100003,  100004  ,100005' || ',%') like '%,' || num || ',%';
select sysdate from dual;
select *
from csv_test t
where t.num in ( Select Regexp_Substr('100001, 100002,   100003   ,      100004, 100005','[^,]+', 1, Level) From Dual
                 Connect By Regexp_Substr('100001, 100002,100003, 100004, 100005', '[^,]+', 1, Level) Is Not Null);
select sysdate from dual;

select *
from csv_test t
where ('%,' || '100001,100002,   100003,  100004  ,100005' || ',%') like '%,' || num || ',%';
select sysdate from dual;

drop table csv_test;
drop sequence csv_test_seq;

토니 앤드류스의 해결책이 제게 도움이 됩니다."페이지처리" >> "제출 후" > "프로세스"에 프로세스를 추가해야 합니다.

사용자 ID를 문자열로 저장하고 있으므로 다음을 사용하여 문자열을 쉽게 일치시킬 수 있습니다.Like아래와 같이

SELECT * FROM users u WHERE u.user_id LIKE '%'||(:P5_USER_ID_LIST)||'%'

예를들면

:P5_USER_ID_LIST  = 45,4932,20,19

쿼리는 반드시 사용자 테이블에 일치하는 1개의 사용자 ID 중 하나를 반환합니다.

이렇게 하면 문제가 확실히 해결됩니다.

이를 동적 SQL로 실행해야 합니다.

전체 문자열을 만든 다음 동적으로 실행합니다.

언급URL : https://stackoverflow.com/questions/7040741/pl-sql-query-in-comma-deliminated-string