IN 절에 대한 매개 변수가 있는 Oracle 저장 프로시저

IN 절에 대한 매개 변수가 있는 Oracle 저장 프로시저

IN 절을 공급하는 데 사용되는 변수 값의 가변 개수를 수용하는 Oracle 저장 프로시저를 만들려면 어떻게 해야 합니까?

이것이 제가 성취하고자 하는 것입니다.업데이트할 행의 기본 키 변수 목록을 PLSQL에서 전달하는 방법을 알 수 없습니다.

FUNCTION EXECUTE_UPDATE
  ( <parameter_list>
   value IN int)
  RETURN  int IS
BEGIN 
    [...other statements...]
    update table1 set col1 = col1 - value where id in (<parameter_list>) 

    RETURN SQL%ROWCOUNT ;
END;

또한 C#에서 이 절차를 호출하고 싶으므로 와 호환되어야 합니다.NET 기능.

고마워요, 로버트

요소 자체에 문자열이 포함되지 않는다는 것을 100% 확신할 수 있다고 가정할 때 CSV를 사용하는 것이 가장 간단한 방법일 것입니다.

사용자 정의 유형을 문자열 테이블로 만드는 것이 더 강력할 수도 있습니다.문자열이 100자를 넘지 않는 경우 다음을 사용할 수 있습니다.

CREATE TYPE string_table AS TABLE OF varchar2(100);

그런 다음 이 유형의 변수를 저장 프로시저로 전달하고 직접 참조할 수 있습니다.당신의 경우, 다음과 같은 것이 있습니다.

FUNCTION EXECUTE_UPDATE(
    identifierList string_table,
    value int)
RETURN int
IS
BEGIN

    [...other stuff...]

    update table1 set col1 = col1 - value 
    where id in (select column_value from table(identifierList));

    RETURN SQL%ROWCOUNT;

END

그table()함수는 사용자 정의 유형을 단일 열 "COLUMN_VALUE"가 있는 테이블로 변환합니다. 그러면 다른 테이블처럼 처리할 수 있습니다(이 경우 조인 또는 하위 선택).

Oracle이 생성자를 생성하므로 저장 프로시저를 호출할 때 다음과 같이 간단히 작성할 수 있습니다.

execute_update(string_table('foo','bar','baz'), 32);

C#에서 이 명령어를 프로그래밍 방식으로 작성할 수 있을 것으로 생각합니다.

그 외에도, 우리 회사에는 문자열, 더블, 인트 등의 목록에 대한 표준으로 정의된 여러 가지 사용자 지정 유형이 있습니다.또한 Oracle JPublisher를 사용하여 이러한 유형에서 해당 Java 개체로 직접 매핑할 수 있습니다.저는 주변을 빠르게 둘러보았지만 C#에 대한 직접적인 동등한 것을 볼 수 없었습니다.자바 개발자들이 이 질문을 마주칠 경우를 대비해 언급하고 싶습니다.

변수 개수가 다양한 절차를 직접 생성할 수 있는 방법은 없다고 생각합니다.그러나 여기에 설명된 문제에 대한 일부 해결책이 있습니다.

1. 일반적인 통화 유형이 있는 경우 절차를 오버로드하면 도움이 될 수 있습니다.
2. 매개변수 수에 상한이 있고 해당 유형도 미리 알려진 경우 매개변수의 기본값이 도움이 될 수 있습니다.
3. 최상의 옵션은 데이터베이스 커서에 대한 포인터인 커서 변수를 사용하는 것입니다.

유감스럽게도 저는 경험이 없습니다.NET 환경.

저는 A 마크의 다음 글을 찾았습니다.이 실에 도움이 될 것으로 생각했던 윌리엄스.이 문서에서는 연관 배열을 사용하여 C#에서 PL/SQL 프로세스로 배열을 전달하는 좋은 예를 제공합니다(type myType is TABLE OF myTable.row%).TYPE INDEX by PLS_INTEGER):

마크 A의 훌륭한 기사입니다.윌리엄스

긴 매개 변수 목록을 사용하여 값을 테이블 생성자에 로드하는 것이 어떻습니까?여기 이 트릭에 대한 SQL/PSM이 있습니다.

UPDATE Foobar
   SET x = 42
 WHERE Foobar.keycol
      IN (SELECT X.parm
            FROM (VALUES (in_p01), (in_p02), .., (in_p99)) X(parm)
           WHERE X.parm IS NOT NULL);

쉼표로 구분된 문자열로 변수를 분할합니다.

declare
schema_n VARCHAR2 (30);
schema_size          number;
schemas       VARCHAR2 (30);
begin
schema_n := 'USER_PROD01,USER_PROD01' ;

select sum(bytes)/1024/1024 INTO schema_size FROM dba_segments where owner in (
with rws as (
  select ''||schema_n||'' str from dual
)
  select regexp_substr (
           str,
           '[^,]+',
           1,
           level
         ) value
  from   rws
  connect by level <= 
    length ( str ) - length ( replace ( str, ',' ) ) + 1

) ;
DBMS_OUTPUT.PUT_LINE ('PAY ATTENTION : PROD '||schemas||' user size is in total of : '||schema_size||' MBs .');
end;
/

주의: PROD 사용자 크기는 총 18.8125MB입니다.

PL/SQL 프로시저가 성공적으로 완료되었습니다.

Oracle용으로 한 것은 아니지만 SQL Server를 사용하면 CSV 문자열을 테이블로 변환하는 함수를 사용할 수 있으며, 이 함수는 IN 절에서 사용할 수 있습니다.Oracle을 위해 이를 다시 작성하는 것은 간단해야 합니다(제 생각에는!)

CREATE Function dbo.CsvToInt ( @Array varchar(1000)) 
returns @IntTable table 
    (IntValue nvarchar(100))
AS
begin

    declare @separator char(1)
    set @separator = ','

    declare @separator_position int 
    declare @array_value varchar(1000) 

    set @array = @array + ','

    while patindex('%,%' , @array) <> 0 
    begin

      select @separator_position =  patindex('%,%' , @array)
      select @array_value = left(@array, @separator_position - 1)

        Insert @IntTable
        Values (Cast(@array_value as nvarchar))

      select @array = stuff(@array, 1, @separator_position, '')
    end

    return
end

그런 다음 CSV 문자열(예: '0001,0002,0003')을 전달하고 다음과 같은 작업을 수행할 수 있습니다.

UPDATE table1 SET 
       col1 = col1 - value 
WHERE id in (SELECT * FROM csvToInt(@myParam)) 

AskTom 웹 사이트에는 CSV 문자열을 구문 분석하고 이를 SQL Server 예제와 유사한 방식으로 사용하는 함수를 만드는 방법을 보여주는 기사가 있습니다.

질문탐 참조

언급URL : https://stackoverflow.com/questions/242771/oracle-stored-procedure-with-parameters-for-in-clause