40.5. 사설 토토 진술

40.5.1. 과제

값의 할당pl/pgsql변수는 다음과 같이 작성되었습니다.

변수 { := | = } 표현;

앞에서 설명한 바와 같이, 그러한 진술의 표현은 SQL을 통해 평가됩니다.select사설 토토은 기본 데이터베이스 엔진으로 전송됩니다. 표현식은 단일 값을 산출해야합니다 (변수가 행 또는 레코드 변수 인 경우 행 값 일 수 있음). 대상 변수는 간단한 변수 (선택적으로 블록 이름으로 자격이있는), 행 또는 레코드 변수 필드 또는 간단한 변수 또는 필드 인 배열 요소 일 수 있습니다. 동일한 (=) PL/SQL 호환 대신 사용할 수 있습니다:=.

표현식의 결과 데이터 유형이 변수의 데이터 유형과 일치하지 않거나 변수에 특정 크기/정밀도가 있습니다 (좋아요char (20)), 결과 값은에 의해 암시 적으로 변환됩니다.pl/pgsql결과 유형의 출력 기능 및 변수 유형의 입력 기능을 사용한 통역사. 결과 값의 문자열 양식이 입력 함수에 허용되지 않는 경우 입력 함수에 의해 생성 된 런타임 오류가 발생할 수 있습니다..

예 :

세금 : = 하위 토탈 * 0.06;
my_record.user_id : = 20;

40.5.2. 결과가없는 사설 토토 실행

예를 들어 행을 반환하지 않는 SQL 사설 토토의 경우삽입a반환조항, a 내 사설 토토을 실행할 수 있습니다.PL/PGSQL사설 토토을 작성하는 것만으로 기능합니다.

anypl/pgsql사설 토토 텍스트에 나타나는 변수 이름은 매개 변수로 취급 된 다음 변수의 현재 값이 실행 시간에 매개 변수 값으로 제공됩니다. 이것은 표현에 대해 앞에서 설명한 처리와 똑같습니다. 자세한 내용은 참조섹션 40.10.1.

이러한 방식으로 SQL 사설 토토을 실행할 때pl/pgsql에서 논의 된대로 사설 토토에 대한 실행 계획을 캐시하고 재사용 할 수 있습니다.섹션 40.10.2.

때로는 표현식 또는를 평가하는 것이 유용합니다.select쿼리 그러나 결과를 버리십시오. 예를 들어 부작용이 있지만 유용한 결과 값이없는 함수를 호출 할 때.pl/pgsql, 사용공연진술 :

공연쿼리;

이것은 실행쿼리결과를 버립니다. 쓰기쿼리SQL을 작성하는 것과 같은 방식select사설 토토이지만 초기 키워드를 대체selectwith공연. 을 위한with쿼리, 사용공연그런 다음 쿼리를 괄호 안에 놓습니다. (이 경우 쿼리는 한 행만 반환 할 수 있습니다.)PL/PGSQL변수는 결과를 반환하지 않는 사설 토토과 마찬가지로 쿼리로 대체되며 계획은 같은 방식으로 캐시됩니다. 또한 특수 변수발견쿼리가 적어도 하나의 행을 생성 한 경우, 또는 행이 생성되지 않은 경우 False (참조섹션 40.5.5).

예 :

create_mv ( 'cs_session_page_requests_mv', my_query); 수행

40.5.3. 단일 줄 결과로 쿼리 실행

SQL 사설 토토의 결과 단일 행 (여러 열의 가능성)을 산출하는 결과는 레코드 변수, 행 유형 변수 또는 스칼라 변수 목록에 할당 될 수 있습니다. 이것은 기본 SQL 사설 토토을 작성하고 추가를 추가하여 수행됩니다.into절. 예를 들어,

selectselect_expressions[에엄격한] 대상에서 ...;
삽입 ... 반환표현[에엄격한] 대상;
업데이트 ... 반환표현[에엄격한] 대상;
삭제 ... 반환표현[ 내엄격한] 대상;

여기서대상레코드 변수, 행 변수 또는 간단한 변수 및 레코드/행 필드의 쉼표로 구분 된 목록이 될 수 있습니다.pl/pgsql변수는 쿼리의 나머지 부분으로 대체되며 계획은 행을 반환하지 않는 사설 토토에 대해 위에서 설명한대로 캐시됩니다. 이것은 |select, 삽입/업데이트/삭제with반환및 ROW 세트 결과를 반환하는 유틸리티 사설 토토 (예 :설명). 을 제외하고in조항, SQL 사설 토토은 외부에 기록 될 것과 동일합니다pl/pgsql.

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

theinto​​조항은 SQL 사설 토토의 거의 모든 곳에 표시 될 수 있습니다. 관습 적으로 그것은 | 목록 직전이나 바로 뒤에 쓰여졌다select_expressionsinselect사설 토토 또는 다른 사설 토토 유형에 대한 사설 토토의 끝에. 의 경우이 컨벤션을 준수하는 것이 좋습니다.pl/pgsql향후 버전에서 파서가 더 엄격 해집니다.

if엄격한에 지정되어 있지 않습니다.into조항,대상쿼리로 반환 된 첫 번째 행 또는 쿼리가 행이 반환되지 않은 경우 Nulls로 설정됩니다. ("첫 번째 줄"사용하지 않는 한 잘 정의되지 않았습니다주문 by.) 첫 번째 행 후 결과 행이 폐기됩니다. 스페셜을 확인할 수 있습니다발견변수 (참조섹션 40.5.5) 행이 반환되었는지 여부를 결정하려면 :

empname = myName에서 EMP에서 MyRec에 * 선택 *;
찾을 수없는 경우
    예외 '직원 % 찾을 수 없음', MyName;
끝 If;

IF엄격한옵션이 지정되고 쿼리는 정확히 하나의 행을 반환해야합니다. 그렇지 않으면 런 타임 오류가보고됩니다.no_data_found(줄 없음) 또는TOO_MANY_ROWS(하나 이상). 예를 들어 오류를 잡으려는 경우 예외 블록을 사용할 수 있습니다 (예 :

시작
    Empname = myName에서 Emp에서 Strict Myrec으로 * 선택하십시오.
    예외
        그렇다면 no_data_found 일 때
            예외 '직원 % 찾을 수 없음', MyName;
        언제 너무 _many_rows
            예외를 제기하는 '직원 % 고유하지 않음', MyName;
끝;

사설 토토의 성공적인 실행엄격한항상 설정발견to true.

for삽입/업데이트/삭제with반환, pl/pgsql|엄격한지정되지 않았습니다. 와 같은 옵션이 없기 때문입니다.주문 by반환 해야하는 영향을받는 행을 결정하기 위해.

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

함수 만들기 get_userid (사용자 이름 텍스트) int를 반환합니다
$$로
#print_strict_params on
선언하다
userId int;
시작하다
    사용자를 엄격한 userID로 선택하십시오
        user.username = get_userid.username이있는 사용자로부터;
    반환 userID;
끝
$$ 언어 plpgsql;

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

오류 : 쿼리는 행이 없습니다
세부 사항 : 매개 변수 : $ 1 = 'Nosuchuser'
컨텍스트 : pl/pgsql 함수 get_userid (텍스트) SQL 문 6 행

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

40.5.4. 동적 사설 토토 실행

종종 당신은 당신의 내부에서 동적 사설 토토을 생성하고 싶을 것입니다pl/pgsql함수, 즉 실행할 때마다 다른 테이블 또는 다른 데이터 유형이 포함되는 사설 토토.pl/pgsql의 사설 토토에 대한 계획을 캐시하려는 정상적인 시도 (섹션 40.10.2)는 그러한 시나리오에서 작동하지 않습니다. 이런 종류의 문제를 처리하려면execute진술이 제공됩니다 :

executeCommand-string [[ 내엄격한] 대상 ] [사용표현 [, ... ] ];

여기서Command-string(유형의 문자열을 산출하는 표현식입니다.텍스트) 실행 할 사설 토토을 포함합니다. 선택 사항대상는 레코드 변수, 행 변수 또는 간단한 변수 및 레코드/행 필드의 쉼표로 구분 된 목록입니다. 선택 사항사용사설 토토에 삽입 할 표현식 공급 값.

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

또한,execute. 대신, 사설 토토문이 실행될 때마다 사설 토토은 항상 계획됩니다. 따라서 사설 토토 문자열은 기능 내에서 동적으로 생성되어 다른 테이블과 열에서 동작을 수행 할 수 있습니다.

thein조항은 SQL 사설 토토의 결과를 반환하는 행의 결과를 지정해야합니다. 행 또는 가변 목록이 제공되면 쿼리 결과의 구조와 정확히 일치해야합니다 (레코드 변수를 사용하는 경우 결과 구조와 자동으로 일치하도록 구성). 여러 행이 반환되면 첫 번째 행만에 할당됩니다.in변수. 행이 반환되지 않으면 NULL이에 할당됩니다.in변수. 그렇지 않은 경우into조항이 지정되어 있으며 쿼리 결과는 폐기됩니다.

인 경우엄격한옵션이 제공되며, 쿼리가 정확히 하나의 행을 생성하지 않는 한 오류 가보고됩니다.

사설 토토 문자열은 매개 변수 값을 사용할 수 있으며 사설 토토에$1, $2등.이 기호는에 제공된 값을 나타냅니다.사용절. 이 방법은 종종 텍스트로 사설 토토 문자열에 데이터 값을 삽입하는 것보다 선호됩니다. 값을 텍스트와 뒤로 변환하는 런 타임 오버 헤드를 피하며 인용 또는 이스케이프 할 필요가 없기 때문에 SQL 주입 공격에 훨씬 덜 걸립니다. 예는 다음과 같습니다.

execute 'inserted_by = $ 1이고 삽입 된 mytable에서 count (*)를 선택하십시오. <= $ 2'
   c
   Checked_user 사용, Checked_date;

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

execute 'count (*)에서'
    || tabname :: regclass
    || 'inserted_by = $ 1이고 삽입 된 <= $ 2'
   c
   Checked_user 사용, Checked_date;

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

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

선택현재 내부에 지원되지 않습니다execute; 대신, 평범한 실행select사설 토토 및 지정in의 일부로execute자체.

실행 'TBL 세트 업데이트'
        || quote_ident (colname)
        || '='
        || quote_literal (newValue)
        || 'key ='
        || quote_literal (keyvalue);

실행 'TBL 세트 업데이트'
        || quote_ident (colname)
        || '='
        || quote_nullable (newValue)
        || 'key ='
        || quote_nullable (keyvalue);

'여기서 key ='|| quote_nullable (keyvalue)

'키가'|| quote_nullable (keyvalue)

실행 'TBL 세트 업데이트'
        || quote_ident (colname)
        || '= $$'
        || NewValue
        || '$$ key ='
        || quote_literal (keyvalue);

형식 실행 ( 'TBL 세트 %i = %l 여기서 key = %l', colname, newValue, keyValue);

형식 실행 ( 'TBL 세트 업데이트 %i = $ 1 key = $ 2', colname)
   NewValue 사용, keyValue;

동적 사설 토토의 훨씬 더 큰 예와execute예 40-9, A를 구축하고 실행하는함수 만들기새 함수를 정의하는 사설 토토.

40.5.5. 결과 상태 얻기

사설 토토의 효과를 결정하는 몇 가지 방법이 있습니다. 첫 번째 방법은를 사용하는 것입니다.진단 받기사설 토토, 양식이 있습니다.

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

이 사설 토토은 시스템 상태 표시기를 검색 할 수 있습니다.현재소음 단어입니다 (그러나 참조스택 진단 받기in섹션 40.6.6.1). 각항목지정된 상태 값을 식별하는 핵심 단어가변(이를 수신하기에 적합한 데이터 유형이어야 함). 현재 사용 가능한 상태 항목은에 표시됩니다.표 40-1. 결장 평등 (:=) SQL 표준 대신 사용할 수 있습니다=토큰. 예 :

진단 받기 integer_var = row_count;

사설 토토의 효과를 결정하는 두 번째 방법은 명명 된 특수 변수를 확인하는 것입니다.발견, 유형의​​부울. 발견각각 내에서 거짓을 시작합니다pl/pgsql함수 호출. 다음 각 유형의 진술에 의해 설정됩니다.

• A 성명서 세트발견행이 지정된 경우 true 행이 반환되지 않으면 false입니다.

• A 공연성명서 세트발견하나 이상의 행을 생성하고 폐기하는 경우 true 행이 생성되지 않으면 거짓.

• 업데이트, 삽입및삭제진술 세트발견적어도 하나의 행에 영향을 미치는 경우, 행에 영향을 미치지 않으면 거짓.

• A Fetch사설 토토문 세트발견행을 반환하는 경우 true 행이 반환되지 않으면 false입니다.

• A 움직임사설 토토문 세트발견그것이 커서를 성공적으로 재배치하면 그렇지 않으면 false.

• A for또는foreach진술 세트발견하나 이상을 반복하는 경우 true false.발견루프가 종료 될 때이 방식으로 설정됩니다. 루프 실행 내부에서발견루프 본문 내에서 다른 문의 실행에 의해 변경 될 수 있지만 루프 문에 의해 수정되지 않았다..

• return query및반환 쿼리 실행진술 세트발견쿼리가 하나 이상의 행을 반환하는 경우, 행이 반환되지 않으면 거짓.

기타pl/pgsql진술은 상태를 변경하지 않습니다발견. 특히execute출력 변경진단 받기, 그러나 변경되지는 않습니다발견.

발견각각의 로컬 변수입니다PL/PGSQL함수; 그것에 대한 모든 변경 사항은 현재 함수에만 영향을 미칩니다.

40.5.6. 전혀 아무것도하지 않는다

때로는 아무것도하지 않는 자리 표시 자 진술이 유용합니다. 예를 들어, if/the/else 체인의 한 팔이 의도적으로 비어 있음을 나타낼 수 있습니다. 이 목적을 위해 사용하십시오.NULL진술 :

null;

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

시작
    Y : = x / 0;
예외
    Division_by_zero가있을 때
        널;  - 오류를 무시하십시오
끝;

시작
    Y : = x / 0;
예외
    Division_By_Zero 일 때 - 오류를 무시하십시오
끝;

바람직한 것은 맛의 문제입니다.