41.5. 사설 토토 진술

41.5.1. 과제

a에 대한 값 할당PL/pgSQL변수는 다음과 같이 작성됩니다:

변수 { := | = } 표현;

앞서 설명한 바와 같이, 그러한 문의 표현식은 SQL을 통해 평가됩니다.선택사설 토토이 주 데이터베이스 엔진으로 전송되었습니다. 표현식은 단일 값(변수가 행 또는 레코드 변수인 경우 행 값일 수 있음)을 생성해야 합니다. 대상 변수는 단순 변수(선택적으로 블록 이름으로 한정됨), 행 또는 레코드 변수의 필드, 단순 변수 또는 필드인 배열의 요소일 수 있습니다. 같음(=11935_11979:=.

식의 결과 데이터 유형이 변수의 데이터 유형과 일치하지 않으면 할당 캐스트에 의해 값이 강제로 적용됩니다(참조메이저 토토 사이트 : 문서 : 9.6 : 가치 저장소). 관련된 데이터 유형 쌍에 대해 알려진 할당 캐스트가 없는 경우,PL/pgSQL인터프리터는 결과 유형의 출력 함수와 변수 유형의 입력 함수를 적용하여 결과 값을 텍스트로 변환하려고 시도합니다. 결과 값의 문자열 형식이 입력 함수에서 허용되지 않는 경우 입력 함수에서 런타임 오류가 생성될 수 있습니다.

예:

세금 := 소계 * 0.06;
my_record.user_id := 20;

41.5.2. 결과 없이 사설 토토 실행

예를 들어 행을 반환하지 않는 SQL 사설 토토의 경우삽입a 없이돌아오는 중절, 다음 내에서 사설 토토을 실행할 수 있습니다.PL/pgSQL사설 토토어만 작성하면 기능이 작동됩니다.

모두PL/pgSQL사설 토토어 텍스트에 나타나는 변수 이름은 매개변수로 처리되며, 런타임 시 변수의 현재 값이 매개변수 값으로 제공됩니다. 이는 앞에서 표현식에 대해 설명한 처리와 똑같습니다. 자세한 내용은 참조섹션 41.10.1.

이런 방식으로 SQL 사설 토토을 실행할 때,PL/pgSQL에 설명된 대로 사설 토토에 대한 실행 계획을 캐시하고 재사용할 수 있습니다.섹션 41.10.2.

때때로 표현식을 평가하는 것이 유용하거나선택질의하지만 결과를 삭제합니다. 예를 들어 부작용이 있지만 유용한 결과 값이 없는 함수를 호출하는 경우입니다. 이를 수행하려면PL/pgSQL, 다음을 사용하세요.수행성명:

수행질의;

이것은 실행됩니다질의그리고 결과를 삭제합니다. 를 쓰세요질의SQL을 작성하는 것과 같은 방법선택사설 토토을 수행하지만 초기 키워드를 바꿉니다.선택와수행. 에 대한함께쿼리, 사용수행그런 다음 검색어를 괄호 안에 넣으세요. (이 경우 쿼리는 하나의 행만 반환할 수 있습니다.)PL/pgSQL변수는 결과를 반환하지 않는 사설 토토과 마찬가지로 쿼리로 대체되며 계획은 동일한 방식으로 캐시됩니다. 또한 특수 변수발견쿼리가 하나 이상의 행을 생성하면 true로 설정되고 행이 생성되지 않으면 false로 설정됩니다(참조섹션 41.5.5).

예:

create_mv('cs_session_page_requests_mv', my_query) 수행;

41.5.3. 단일 행 결과로 쿼리 실행

단일 행(아마도 여러 열)을 생성하는 SQL 사설 토토의 결과는 레코드 변수, 행 유형 변수 또는 스칼라 변수 목록에 할당될 수 있습니다. 이는 기본 SQL 사설 토토을 작성하고INTO절. 예를 들어,

선택select_expressionsINTO [STRICT] 대상발신 ...;
삽입... 반환 중표현INTO [STRICT] 대상;
업데이트 ... 복귀 중표현INTO [STRICT] 대상;
삭제 ... 복귀 중표현INTO [STRICT] 대상;

어디에서대상레코드 변수, 행 변수 또는 쉼표로 구분된 단순 변수 및 레코드/행 필드의 목록일 수 있습니다.PL/pgSQL변수는 쿼리의 나머지 부분으로 대체되며 행을 반환하지 않는 사설 토토에 대해 위에서 설명한 것처럼 계획이 캐시됩니다. 이것은 작동합니다.선택, 삽입/업데이트/삭제와돌아오는 중및 행 집합 결과를 반환하는 유틸리티 사설 토토(예:설명). 제외하고INTO절, SQL 사설 토토은 외부에 작성된 것과 동일합니다.PL/pgSQL.

행 또는 변수 목록이 대상으로 사용되는 경우 쿼리의 결과 열은 숫자 및 데이터 유형에 있어서 대상의 구조와 정확하게 일치해야 합니다. 그렇지 않으면 런타임 오류가 발생합니다. 레코드 변수가 대상인 경우 쿼리 결과 열의 행 유형으로 자동 구성됩니다.

그INTO절은 SQL 사설 토토의 거의 모든 위치에 나타날 수 있습니다. 관례적으로 다음 목록 바로 앞이나 뒤에 기록됩니다.select_expressions에선택사설 토토, 또는 다른 사설 토토 유형의 경우 사설 토토 끝에. 다음과 같은 경우에는 이 규칙을 따르는 것이 좋습니다.PL/pgSQL파서는 향후 버전에서 더욱 엄격해집니다.

만약STRICT다음에 지정되지 않았습니다.INTO절, 그렇다면대상은 쿼리에서 반환된 첫 번째 행으로 설정되거나 쿼리에서 행이 반환되지 않은 경우 null로 설정됩니다. (참고하세요"첫 번째 행"다음을 사용하지 않으면 잘 정의되지 않습니다.주문 기준.) 첫 번째 행 이후의 모든 결과 행은 삭제됩니다. 특집을 확인하실 수 있습니다발견변수(참조섹션 41.5.5) 행이 반환되었는지 확인하려면 다음을 수행하세요.

SELECT * INTO myrec FROM emp WHERE empname = myname;
발견되지 않은 경우
    RAISE EXEPTION '직원 %를 찾을 수 없음', myname;
종료하면;

만약STRICT옵션이 지정되면 쿼리는 정확히 하나의 행을 반환해야 하며 그렇지 않으면 런타임 오류가 보고됩니다.NO_DATA_FOUND(행 없음) 또는TOO_MANY_ROWS(둘 이상의 행). 오류를 포착하려면 예외 블록을 사용할 수 있습니다. 예를 들면 다음과 같습니다.

시작
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
    예외
        NO_DATA_발견된 시점
            RAISE EXEPTION '직원 %를 찾을 수 없음', myname;
        다음에는 TOO_MANY_ROWS개일 때
            RAISE EXEPTION '직원 %가 고유하지 않음', myname;
끝;

다음 사설 토토어를 성공적으로 실행했습니다.STRICT항상 설정발견참으로.

용삽입/업데이트/삭제함께돌아오는 중, PL/pgSQL다음 경우에도 두 개 이상의 반환된 행에 대해 오류를 보고합니다.STRICT지정되지 않았습니다. 와 같은 옵션이 없기 때문입니다.주문 기준영향을 받은 행을 반환해야 하는지 결정하는 데 사용됩니다.

만약print_strict_params함수에 대해 활성화된 다음 다음의 요구 사항으로 인해 오류가 발생하는 경우STRICT충족되지 않았습니다.세부정보오류 메시지의 일부에는 쿼리에 전달된 매개변수에 대한 정보가 포함됩니다. 변경할 수 있습니다.print_strict_params설정으로 모든 기능에 대한 설정plpgsql.print_strict_params, 하지만 후속 함수 컴파일만 영향을 받습니다. 컴파일러 옵션을 사용하여 함수별로 활성화할 수도 있습니다. 예를 들면 다음과 같습니다.

CREATE FUNCTION get_userid(사용자 이름 텍스트) RETURNS int
AS $$
#print_strict_params 켜짐
선언
사용자 ID int;
시작
    users.userid를 STRICT 사용자 ID로 선택하세요.
        FROM 사용자 WHERE users.username = get_userid.username;
    RETURN 사용자 ID;
끝;
$$ 언어 plpgsql;

실패 시 이 함수는 다음과 같은 오류 메시지를 생성할 수 있습니다.

오류: 쿼리에서 행이 반환되지 않았습니다.
세부사항: 매개변수: $1 = 'nosuchuser'
CONTEXT: PL/pgSQL 함수 get_userid(text) SQL 문의 6행

SQL 쿼리에서 여러 결과 행을 처리해야 하는 경우를 처리하려면 다음을 참조하세요.섹션 41.6.4.

41.5.4. 동적 사설 토토 실행

종종 당신은 당신의 내부에서 동적 사설 토토을 생성하고 싶을 것입니다PL/pgSQL함수, 즉 실행될 때마다 다른 테이블이나 다른 데이터 유형을 포함하는 사설 토토입니다.PL/pgSQL의 일반적인 사설 토토 캐시 계획 시도(에서 논의됨)섹션 41.10.2)은 이러한 시나리오에서는 작동하지 않습니다. 이런 종류의 문제를 처리하려면,실행문이 제공되었습니다:

실행사설 토토어-문자열 [INTO [STRICT] 대상 ] [사용 중표현 [, ... ] ];

어디에서사설 토토어-문자열(유형의 문자열을 생성하는 표현식입니다.)텍스트) 실행할 사설 토토이 포함되어 있습니다. 선택사항대상은 사설 토토 결과가 저장될 레코드 변수, 행 변수 또는 단순 변수와 레코드/행 필드의 쉼표로 구분된 목록입니다. 선택사항사용 중표현식은 사설 토토에 삽입될 값을 제공합니다.

대체 없음PL/pgSQL변수는 계산된 사설 토토 문자열에서 수행됩니다. 필수 변수 값은 사설 토토 문자열이 생성될 때 삽입되어야 합니다. 또는 아래 설명된 대로 매개변수를 사용할 수 있습니다.

또한 다음을 통해 실행되는 사설 토토에 대한 계획 캐싱이 없습니다.실행. 대신 사설 토토문이 실행될 때마다 사설 토토이 항상 계획됩니다. 따라서 함수 내에서 사설 토토 문자열을 동적으로 생성하여 다양한 테이블과 열에 대한 작업을 수행할 수 있습니다.

그INTO절은 행을 반환하는 SQL 사설 토토의 결과가 할당되어야 하는 위치를 지정합니다. 행 또는 변수 목록이 제공되는 경우 쿼리 결과의 구조와 정확히 일치해야 합니다. 레코드 변수를 사용하면 자동으로 결과 구조와 일치하도록 자체 구성됩니다. 여러 행이 반환되면 첫 번째 행만에 할당됩니다.INTO변수. 반환된 행이 없으면 NULL이에 할당됩니다.INTO변수. 그렇지 않은 경우INTO절이 지정되면 쿼리 결과가 삭제됩니다.

만약에STRICT옵션이 주어지면 쿼리가 정확히 하나의 행을 생성하지 않으면 오류가 보고됩니다.

사설 토토 문자열은 사설 토토에서 다음과 같이 참조되는 매개변수 값을 사용할 수 있습니다.$1, $2등. 이러한 기호는에 제공된 값을 나타냅니다.사용 중절. 이 방법은 사설 토토 문자열에 데이터 값을 텍스트로 삽입하는 것보다 더 선호되는 경우가 많습니다. 값을 텍스트로 변환하거나 그 반대로 변환하는 런타임 오버헤드를 방지하고 인용이나 이스케이프가 필요하지 않기 때문에 SQL 주입 공격이 발생할 가능성이 훨씬 적습니다. 예는 다음과 같습니다:

EXECUTE 'SELECT count(*) FROM mytable WHERE 삽입된_by = $1 AND 삽입된 <= $2'
   INTO c
   확인된_사용자, 확인된_날짜 사용;

매개변수 기호는 데이터 값에만 사용될 수 있다는 점에 유의하십시오. 동적으로 결정된 테이블 또는 열 이름을 사용하려면 이를 사설 토토 문자열에 텍스트로 삽입해야 합니다. 예를 들어, 동적으로 선택된 테이블에 대해 이전 쿼리를 수행해야 하는 경우 다음을 수행할 수 있습니다.

'SELECT count(*) FROM' 실행
    || quote_ident(탭 이름)
    || ' 삽입된_by = $1 AND 삽입된 <= $2'
   INTO c
   확인된_사용자, 확인된_날짜 사용;

더 깔끔한 접근 방식은 다음을 사용하는 것입니다.형식()'s%I테이블 또는 열 이름 지정(줄바꿈으로 구분된 문자열은 연결됨):

EXECUTE 형식('%I에서 선택 개수(*)'
   'WHERE insert_by = $1 AND 삽입된 <= $2', 탭 이름)
   INTO c
   확인된_사용자, 확인된_날짜 사용;

매개변수 기호에 대한 또 다른 제한은 다음에서만 작동한다는 것입니다.선택, 삽입, 업데이트및삭제사설 토토. 다른 문 유형(일반적으로 유틸리티 문이라고 함)에서는 데이터 값일지라도 텍스트로 값을 삽입해야 합니다.

안실행간단한 상수 사설 토토 문자열 및 일부사용 중매개변수는 위의 첫 번째 예에서와 같이 사설 토토을 직접 작성하는 것과 기능적으로 동일합니다.PL/pgSQL및 교체 허용PL/pgSQL변수가 자동으로 발생합니다. 중요한 차이점은 다음과 같습니다.실행실행할 때마다 사설 토토을 다시 계획하여 현재 매개변수 값에 특정한 계획을 생성합니다. 반면PL/pgSQL그렇지 않으면 일반 계획을 생성하고 재사용을 위해 캐시할 수 있습니다. 최상의 계획이 매개변수 값에 크게 좌우되는 상황에서는 다음을 사용하는 것이 도움이 될 수 있습니다.실행일반 계획이 선택되지 않았는지 확실히 확인합니다.

선택현재 내에서는 지원되지 않습니다.실행; 대신 일반을 실행하십시오.선택사설 토토 및 지정INTO의 일부로실행그 자체.

EXECUTE 형식('UPDATE tbl SET %I = $1'
   'WHERE 키 = $2', colname) USING newvalue, keyvalue;

'UPDATE TBL SET' 실행
        || quote_ident(열이름)
        || ' = '
        || quote_literal(새 값)
        || ' WHERE 키 = '
        || quote_literal(키값);

'UPDATE TBL SET' 실행'
        || quote_ident(열이름)
        || ' = '
        || quote_nullable(새 값)
        || ' WHERE 키 = '
        || quote_nullable(키값);

'WHERE 키 = ' || quote_nullable(키값)

'키가 다음과 구별되지 않는 곳' || quote_nullable(키값)

'UPDATE TBL SET' 실행
        || quote_ident(열이름)
        || ' = $$'
        || 새로운 값
        || '$$ WHERE 키 = '
        || quote_literal(키값);

실행 형식('UPDATE tbl SET %I = %L '
   'WHERE 키 = %L', 열 이름, 새 값, 키 값);

EXECUTE 형식('UPDATE tbl SET %I = $1 WHERE 키 = $2', 열 이름)
   새로운 값, 키값 사용;

동적 사설 토토의 훨씬 더 큰 예이며실행다음에서 볼 수 있음예 41-9, 다음을 빌드하고 실행합니다.함수 생성새 기능을 정의하는 사설 토토입니다.

41.5.5. 결과 상태 가져오기

사설 토토의 효과를 결정하는 방법에는 여러 가지가 있습니다. 첫 번째 방법은진단 받기사설 토토, 형식은 다음과 같습니다.

GET [현재] 진단변수 { = | := } 항목 [ , ... ];

이 사설 토토을 사용하면 시스템 상태 표시기를 검색할 수 있습니다.현재불필요한 단어입니다(그러나 참조스택 진단 받기in섹션 41.6.6.1). 각각항목지정된 항목에 할당될 상태 값을 식별하는 키워드입니다.변수(수신하려면 올바른 데이터 유형이어야 함). 현재 사용 가능한 상태 항목은 다음과 같습니다.표 41-1. 콜론 같음(:=)은 SQL 표준 대신 사용할 수 있습니다.=토큰. 예:

진단 받기 정수_var = ROW_COUNT;

사설 토토의 효과를 결정하는 두 번째 방법은 이름이 지정된 특수 변수를 확인하는 것입니다.발견, 유형은부울. 발견각각에서 거짓으로 시작함PL/pgSQL함수 호출. 이는 다음 각 유형의 문에 의해 설정됩니다.

• A 선택문 세트발견행이 할당되면 true, 반환된 행이 없으면 false입니다.

• A 수행문 세트발견하나 이상의 행을 생성(및 삭제)하면 true이고, 행이 생성되지 않으면 false입니다.

• 업데이트, 삽입및삭제문 세트발견적어도 하나의 행이 영향을 받으면 true이고, 영향을 받은 행이 없으면 false입니다.

• A 가져오기문 세트발견행을 반환하면 true이고, 행이 반환되지 않으면 false입니다.

• A 이동문 세트발견커서 위치를 성공적으로 변경하면 true이고, 그렇지 않으면 false입니다.

• A for또는FOREACH문 세트발견한 번 이상 반복되면 true이고, 그렇지 않으면 false입니다.발견38673_38747발견루프 본문 내에서 다른 사설 토토문을 실행하면 변경될 수 있지만 루프 문에 의해 수정되지 않습니다.

• 반환 쿼리그리고반환 쿼리 실행문 세트발견쿼리가 하나 이상의 행을 반환하면 true이고, 반환된 행이 없으면 false입니다.

기타PL/pgSQL문은 상태를 변경하지 않습니다.발견. 특히 다음 사항에 유의하세요.실행다음의 출력을 변경합니다진단 받기, 그러나 변경되지 않음발견.

발견각각의 지역 변수입니다PL/pgSQL함수; 변경 사항은 현재 기능에만 영향을 미칩니다.

41.5.6. 전혀 아무것도 하지 않음

가끔 아무 작업도 수행하지 않는 자리 표시자 사설 토토문이 유용할 때가 있습니다. 예를 들어 if/then/else 체인의 한쪽 부분이 의도적으로 비어 있음을 나타낼 수 있습니다. 이를 위해 다음을 사용하십시오.NULL진술:

NULL;

예를 들어, 다음 두 코드 조각은 동일합니다:

시작
    y := x / 0;
예외
    WHEN Division_by_zero THEN
        NULL;  -- 오류를 무시하세요
끝;

시작
    y := x / 0;
예외
    WHEN Division_by_zero THEN -- 오류를 무시합니다.
끝;

어느 쪽이 더 좋은지는 취향의 문제입니다.