40.5. 토토 캔 진술

40.5.1. 과제

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

변수 := 표현;

앞서 설명했듯이, 그러한 진술의 표현은 다음과 같습니다: SQL을 통해 평가됨선택명령이 기본 데이터베이스 엔진으로 전송되었습니다. 표현은 다음과 같아야합니다 단일 값(변수가 행 또는 행인 경우 행 값일 수 있음) 레코드 변수). 대상 변수는 단순 변수일 수 있습니다. (선택적으로 블록 이름으로 한정됨), 행의 필드 또는 레코드 변수 또는 간단한 배열 요소 변수 또는 필드.

식의 결과 데이터 유형이 일치하지 않는 경우 변수의 데이터 유형이거나 변수에 특정 크기/정밀도가 있습니다. (좋다문자(20)), 결과 값은 다음과 같습니다. 에 의해 암시적으로 변환됨PL/pgSQL결과 유형을 사용하는 해석기 출력 기능 및 변수 유형의 입력 기능. 참고하세요 이로 인해 잠재적으로 런타임 오류가 발생할 수 있습니다. 입력 함수, 결과 값의 문자열 형식이 아닌 경우 입력 기능에 허용됩니다.

예:

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

40.5.2. 다음을 사용하여 명령 실행 결과 없음

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

모두PL/pgSQL변수 이름 명령 텍스트에 나타나는 것은 매개변수로 처리되며, 변수의 현재 값이 매개변수로 제공됩니다. 런타임 시 값. 이는 설명된 처리와 정확히 같습니다. 표현의 경우 이전; 자세한 내용은 참조섹션 40.10.1.

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

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

수행질의;

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

예:

create_mv('cs_session_page_requests_mv', my_query) 수행;

40.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로 변환됩니다. (참고하세요"첫 번째 행"잘 정의되지 않았습니다. 당신이 사용하지 않는 한주문 기준.) 모든 결과 첫 번째 행 이후의 행은 삭제됩니다. 특집을 확인하실 수 있습니다발견변수(참조섹션 40.5.5) 행이 반환되었는지 확인하려면 다음을 수행하세요.

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

만약엄격옵션이 지정되면 쿼리는 정확히 하나의 행을 반환해야 합니다. 그렇지 않으면 런타임 오류가 발생합니다. 보고됨, 둘 중 하나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아님 지정. 와 같은 옵션이 없기 때문입니다.주문 기준영향을 받은 행을 결정하는 데 사용됩니다. 반납해야 합니다.

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

40.5.4. 동적 실행 중 토토 캔

종종 내부에서 동적 토토 캔을 생성하고 싶을 때가 있습니다 당신의PL/pgSQL함수, 즉, 다른 테이블이나 다른 데이터 유형을 포함하는 토토 캔 실행될 때마다.PL/pgSQL의 정상적인 캐시 계획 시도는 다음과 같습니다. 명령(에서 설명한 대로)섹션 40.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' 실행
    || 탭 이름::regclass
    || ' 삽입된_by = $1 AND 삽입된 <= $2'
   INTO c
   확인된_사용자, 확인된_날짜 사용;

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

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

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

'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(키값);

EXECUTE format('UPDATE tbl SET %I = %L WHERE key = %L', colname, newvalue, keyvalue);

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

동적 토토 캔의 훨씬 더 큰 예와실행다음에서 볼 수 있음예 40-9, 빌드하고 실행합니다.함수 생성새 함수를 정의하는 토토 캔입니다.

40.5.5. 결과 얻기 상태

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

GET [현재] 진단변수 = 항목 [ , ... ];

이 토토 캔은 시스템 상태 표시기를 검색할 수 있습니다. 각항목은(는) 지정된 변수에 할당될 상태 값( 이를 수신할 수 있는 올바른 데이터 유형이어야 합니다). 현재 사용 가능한 상태 항목은ROW_COUNT, 개수 마지막으로 처리된 행SQL명령이 다음으로 전송되었습니다.SQL엔진, 그리고RESULT_OID, 마지막 행의 OID 가장 최근에 삽입됨SQL토토 캔. 참고하세요RESULT_OID만 다음 이후에 유용함삽입토토 캔을 OID가 포함된 테이블입니다.

예:

진단 받기 정수_var = ROW_COUNT;

명령의 효과를 결정하는 두 번째 방법은 다음과 같습니다. 이름이 지정된 특수 변수를 확인하세요.발견, 유형입니다.부울. 발견각각에서 거짓으로 시작함PL/pgSQL함수 호출. 각각 설정되어 있어요 다음 유형의 진술:

• A 선택문 세트발견행이 할당되면 true, 행이 할당되지 않으면 false 돌아왔습니다.

• A 수행문 세트발견하나 이상을 생성(및 폐기)하는 경우 true 행, 행이 생성되지 않으면 false입니다.

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

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

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

• A for또는FOREACH문 세트발견한 번 이상 반복되면 true이고, 그렇지 않으면 거짓.발견루프가 실행될 때 이 방식으로 설정됩니다. 종료; 루프 실행 내부에서,발견은 루프 문에 의해 수정되지 않습니다. 다른 문을 실행하면 변경될 수 있지만 루프 본문 내에서.

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

기타PL/pgSQL문은 다음과 같습니다 상태를 변경하지 않음발견. 메모 특히 그실행출력을 변경합니다 의진단 받기, 그러나 변경되지 않음발견.

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

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

가끔 아무 작업도 하지 않는 자리 표시자 문이 유용할 때가 있습니다. 예를 들어, if/then/else 체인의 한쪽 팔이 일부러 비어있습니다. 이를 위해 다음을 사용하십시오.NULL진술:

NULL;

예를 들어, 다음 두 코드 조각은 동등한:

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

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

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