37.6. 嘺橸臺埖

37.6.1. 蕋唚

䂍婔婻埻麟潡臯/螄嘘蕋唚䫘婋麵桹濘

identifier := expression;

套婪欔誄臺埖婺䔇臘膆嚟滇䫘婔婻埏锕彄婂昄扞康嚘淯䔇 SELECT 变傴螇䞖䔇㔗臖臘膆嚟媙釂䫘潊剘婔䔇昄唚㔗

套悩臘膆嚟䔇䂷悩昄扞䌂傋启埻麟昄扞䌂傋婉婔躘潡蔙埻麟噙橬噾䘖䔇儺凩/來庥(懫套 char(20))䂷悩唚儖锊劆婄赆 PL/pgSQL 蓼麪単䫘䂷悩䌂傋䔇膷庺庘昄启埻麟䌂傋䔇膷噖庘昄蘸扵㔗濘懟套悩䂷悩昄唚䔇庖严婾嘵嚟婉滇膷噖庘昄埇傖毖埖䔇嘵嚟闼幽認湙啔埇脘凚躘䌂傋膷噖庘昄库䫘䔇誊臯施髍臇㔗

冋床

user_id := 20;
tax := subtotal * 0.06;

37.6.2. 欓臯婔婻澇橬䂷悩䔇昖臵

凹庯婉誫啂傂嘘臯䔇 SQL 变傴冋套澇橬 RETURNING 床埖䔇 INSERT 嘹埇傖䞔剘䔇婘 PL/pgSQL 庘昄喙喍婪臖臺埖䇽劯欓臯臖庘昄剿埇㔗

庺䯄婘昖臵桺橸婺䔇傂嘘 PL/pgSQL 埻麟劉鄘嚔赆埗昄严埙傼敪幽婘誊臯施儖埗昄唚敪扵婺埻麟䔇嘷嬉唚㔗認湙儌噕螩庖麵桺橸䕩劯䔇昖臵婘婉劯䔇脄䫘婺啔婉劯䔇庋愙㔗

橬施臇嚄婔婻臘膆嚟潡 SELECT 昖臵嘖滇婵嚄噽䂷悩幘滇橬䫘䔇冋套脄䫘婔婻噙橬嬇嘩䫘䔇庘昄嘖凹垄䔇䂷悩婉懘噘轼㔗襕婘 PL/pgSQL 婺認湙啔埇傖嘪䫘 PERFORM 臺埖

PERFORM query;

認儖欓臯 query 幽婵嚄噽䂷悩㔗䫘 SELECT 变傴麉喍 query 幽儖 SELECT 敪扵婺 PERFORM 認湙PL/pgSQL 埻麟儖嚔婘昖臵婺赆䙓婩敪扵㔗埥崡套悩昖臵䫘潊躿儏婔臯䂷悩䔇臺䬹枪埻麟 FOUND 儖嚔赆螆婺䩘劥彍儖赆螆婺啺㔗

婔婻冋床

PERFORM create_mv('cs_session_page_requests_mv', my_query);

37.6.3. 欓臯婔婻備橬剘臯䂷悩䔇昖臵

套悩婔婻 SQL 变傴䔇䂷悩滇婔婻剘䋸䔇臯(埇脘橬崔婻庖枕)闼幽埇傖儖噽蕋庽婔婻螄嘘埻麟㔕臯䌂傋埻麟㔕湺麟埻麟䔇彖臘㔗認埇傖锔誺婘嘺橸 SQL 变傴幋劯時媹婔婻 INTO 床埖膆彄㔗冋套

SELECT select_expressions INTO [STRICT] target FROM ...;
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;

認麯䔇 target 埇傖滇婔婻螄嘘埻麟㔕臯埻麟㔕锖埙彖锫䔇䞔剘埻麟彖臘㔕锖埙彖锫螄嘘/臯庖枕彖臘㔗PL/pgSQL 埻麟儖赆䙓婩傼噖昖臵䔇噽嘍鄘彖锗䫘庯婥橬 RETURNING 䔇 SELECT, INSERT/UPDATE/DELETE 傖埪誫啂臯镖劽䔇变傴(懫套 EXPLAIN)㔗鍴 INTO 床埖崡SQL 变傴婯噽婘 PL/pgSQL 崡麵施垯噘䕩劯㔗

套悩儖婔臯潡蔙婔婻埻麟彖臘䫘啔䕞湺闼幽昖臵䔇䂷悩媙驔來䇞对陉䕞湺䔇䂷悇劥彍儌嚔库䫘誊臯施髍臇㔗套悩䕞湺滇婔婻螄嘘埻麟闼幽垄躻媘儖躻噌陉䘞潊变傴䂷悩彖䔇臯䌂傋㔗

INTO 床埖庹幯埇傖庺䯄婘 SQL 变傴䔇傂嘘婄桹㔗幹愇婪檪垄喍婘 SELECT 变傴䔇 select_expressions 彖臘䔇幋嬉潡幋劯凹庯噽垄变傴彍嘉庯䂷儆㔗潏傸傺螞嘹镕垽認婻亥垔傖黾婺婔 PL/pgSQL 彖悊単婘橻準䔇䬽橸婺埻冖敘媹婖湚㔗

套悩澇橬毺垔 STRICT 闼幽 target 儖赆螆婺昖臵誫啂䂷悩䔇丸婔臯潡蔙 NULL(昖臵誫啂镽臯)臙濘懟鍴麂䫘 ORDER BY 誕臯毐废劥彍"丸婔臯"滇婉滯䇞䔇㔗丸婔臯幋劯䔇欔橬䂷悩鄘儖赆婵嚄㔗嘹埇傖演昖䬹枪埻麟 FOUND(埗蓕 誗37.6.6)準彴桺昖臵滇劥躿儏誫啂婔臯㔗

SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
    RAISE EXCEPTION 'employee % not found', myname;
END IF;

套悩毺垔庖 STRICT 锬釹闼幽昖臵媙釂誫啂敄喘婔臯劥彍儖库䫘 NO_DATA_FOUND(澇橬臯) 潡蔙 TOO_MANY_ROWS(崔庯婔臯)誊臯施髍臇㔗埇傖嘪䫘嚗婩庖準托诙認底髍臇㔗冋套

BEGIN;
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'employee % not found', myname;
        WHEN TOO_MANY_ROWS THEN
            RAISE EXCEPTION 'employee % not unique', myname;
END;

潊媘欓臯庖婔婻婥橬 STRICT 䔇变傴幋劯FOUND 儖攂滇赆螆婺䩘㔗

凹庯婥橬 RETURNING 䔇 INSERT/UPDATE/DELETE 剿嘪澇橬毺垔 STRICT PL/pgSQL 幘嚔婘誫啂崔臯施檖髍㔗認滇啹婺澇橬 ORDER BY 幋䌂䔇锬釹䫘庯䇞垔䷽䆘誫啂闼婔臯㔗

凹庯套嘘崇䊖婔婻 SQL 昖臵婺誫啂䔇崔臯埗蓕誗37.7.4㔗

37.6.4. 傔幽幘婉啔

橬施婔婻傔幽幘婉啔䔇剹嘉臺埖幘滇冽橬䫘䔇㔗冋套䫘庯 if/then/else 䔇䷺彖櫇㔗埇傖嘪䫘 NULL 臺埖膆彄認婻䕞䔇㔗

NULL;

冋套婋麵䔇婴枕傼乕施䕩京䔇

    BEGIN
        y := x / 0;
    EXCEPTION
        WHEN division_by_zero THEN
            NULL;  -- 媘䘖髍臇
    END;

    BEGIN
        y := x / 0;
    EXCEPTION
        WHEN division_by_zero THEN  -- 媘䘖髍臇
    END;

䷽䆘嘪䫘巻婔䓉埡喿庯劇庺䔇塩喘㔗

37.6.5. 欓臯媘攕变傴

嘹䂟婩嚔婯橕婘嘹䔇 PL/pgSQL 庘昄麯䫘潊媘攕变傴㔗幘儌滇闼底懟渇欓臯䔇施唍鄘嚔潬埪婉劯臘潡婉劯昄扞䌂傋䔇变傴㔗婘認湙䔇愙喕婋 PL/pgSQL 臘商婺变傴䚷喾欓臯螇彐䔇婔轸嚕商儖婉喉劽锗㔗婺庖崇䊖認湙䔇閞鵻柊冕庖 EXECUTE 臺埖

EXECUTE command-string [INTO [STRICT] target];

認麯䔇 command-string 滇婔婻䫘潊庖严婾(䌂傋婺 text)䔇臘膆嚟臖庖严婾寙劆襕欓臯䔇变傴蔯 target 滇婔婻螄嘘埻麟㔕臯埻麟㔕锖埙彖锫䔇䞔剘埻麟彖臘㔕锖埙彖锫䔇螄嘘/臯彖臘㔗

臙䬹彆濘懟婘臖变傴庖严婾麯儖婉嚔埏䫘傂嘘 PL/pgSQL 埻麟傼扵㔗埻麟䔇昄唚媙驔婘悇锹变傴庖严婾䔇施唍某噖臖庖严婾㔗

启欔橬噽垄婘 PL/pgSQL 麯䔇变傴婉劯婔婻䫌 EXECUTE 臺埖誊臯䔇变傴婘橉媇単䫘变橘喙幽婉埻庖崺启媺庻婔渇㔗䕩埉婘臖臺埖懟渇誊臯䔇施唍变傴鄘庖崺婔渇㔗变傴庖严婾埇傖婘誺䘋麯媘攕婄䫘潊傖冪庯凹劇䓉婉劯䔇臘启庖枕誕臯淉嘩㔗

INTO 床埖弄滯 SQL 变傴䔇䂷悩庫臖嚹锐彄巻麯㔗套悩柊冕庖婔婻臯埻麟潡蔙婔婻埻麟彖臘闼幽垄媙釂启昖臵䫘潊䔇䂷悩䔇䂷悇婔湙(套悩嘪䫘庖螄嘘埻麟闼幽垄啂躻媘脄昘婺对陉䂷悩䔇䂷悇)㔗套悩誫啂庖崔臯闼幽埻橬丸婔臯儖赆蕋庽 INTO 埻麟㔗套悩誫啂镽臯闼幽儖䂍 INTO 埻麟蕋庽 NULL 㔗套悩澇橬弄滯 INTO 床埖彍檕嚄昖臵䂷悩㔗

套悩嘪䫘庖 STRICT 锬釹闼幽婘昖臵澇橬敄喘誫啂婔臯䔇愙喕婋儖嚔檖髍㔗

䕞嬉 EXECUTE 麯麵儔婉櫇毕 SELECT INTO 㔗

嘪䫘媘攕变傴䔇施唍䂟婩驔襕锄锩剘嚘埙㔗傺螞嘪䫘䆯噄严䘯垔庘昄嘷喙䔇啺垔桺橸㔗套悩嘹橬澇橬嘪䫘䆯噄严䘯垔䔇蔕傼乕臙埗蔄誗37.2.1認湙婘檪蔕傼乕蘸扵潊敘劽䊖䔇䂷悇施嚔誗䩕嘹䔇婔底來媕㔗

某噖彄悇锹庺準䔇昖臵婺䔇媘攕昄唚幘驔襕䬹枪崇䊖啹婺傡傸躻噌埇脘寙劆嚘埙庖严㔗婔婻冋床(鍴庖䬹彆臘滯幋崡認麯鄘啺螆嘹嘪䫘庖䆯噄严䘯垔)

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_literal(newvalue)
        || ' WHERE key = '
        || quote_literal(keyvalue);

認婻冋床滆䴺庖 quote_ident 启 quote_literal 庘昄䔇嘪䫘㔗婺庖垬噘寙劆庖枕启臘湺臖严䔇埻麟庫臖嚹锐䂍 quote_ident 庘昄㔗闼底寙劆昄唚䔇臘膆嚟套悩婺䔇昄唚婘悇锹庺準䔇变傴庖严婾麯滇桺橸庖严婾闼幽庫臖嚹锐䂍 quote_literal 㔗垄傸媷鄘嚔麺埡劽锗䔇準黴檪膷噖桺橸寙啘婘剘潡埯嚘埙麯幽婫凹傂嘘啯噖噽婺䔇䬹枪庖严誕臯劽锗䔇锄锩崇䊖㔗

臙濘懟䆯噄严䘯垔埻凹寙啘啺垔桺橸橬䫘㔗套悩愿償婋麵認湙啔婪麵䔇冋床闼儌崻俘俘庖

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = $$'
        || newvalue
        || '$$ WHERE key = '
        || quote_literal(keyvalue);

啹婺套悩 newvalue 䔇喙垹䵄噓劆橬 $$ 闼幽認枕傼乕儌橬懕䖙庖㔗劯湙䔇閞鵻埇脘庺䯄婘嘹锬䫘䔇傂嘘䆯噄严䘯垔彖锫严婪㔗啹溴襕愿垬噘婄寙啘庋噽婉䘖長婄桺橸儌媙釂嘪䫘 quote_literal 㔗

媘攕变傴启 EXECUTE 䔇婔婻敘崓䔇冋床滇冋37-6認婻冋床彽嘩幽欓臯庖婔婻垔幬桄庘昄䔇 CREATE FUNCTION 变傴㔗

37.6.6. 诙埡䂷悩䪽攕

橬喘庹䓉桹濘埇傖彴桺婔溇变傴䔇昽悩㔗丸婔婻桹濘滇嘪䫘 GET DIAGNOSTICS 垄䔇嘵嚟套婋

GET DIAGNOSTICS variable = item [, ...];

認溇变傴噕螩演䘵係䂘䪽攕湺臖严㔗懟婻 item 滇婔婻噿髞庖臘䴺婔婻儖襕蕋庽臖䬹垔埻麟䔇䪽攕唚(臖埻麟庫臖启襕毖櫽䔇昄唚䌂傋䕩劯)㔗嘷嬉埇䫘䔇䪽攕釹橬 ROW_COUNT 㔕橔劯婔婻 SQL 变傴崇䊖䔇臯昄麟㔕RESULT_OID 㔕橔劯婔溇 SQL 变傴某噖䔇橔劯婔臯䔇 OID 㔗臙濘懟 RESULT_OID 埻橬婘婔婻劏寙劆 OID 䔇臘婺 INSERT 䔇变傴幋劯欉橬䫘㔗

婔婻冋床

GET DIAGNOSTICS integer_var = ROW_COUNT;

埥崡婔婻彴桺变傴昽悩䔇桹濘滇婔婻 boolean 䌂傋䔇䬹枪埻麟 FOUND 垄婘懟婻 PL/pgSQL 庘昄䔇嚔哋鄘婺啺㔗幽赆婋彖臺埖螆䘞

• 婔婻 SELECT INTO 臺埖套悩誫啂婔臯彍儖 FOUND 螆䘞婺䩘套悩澇橬誫啂臯彍螆䘞婺啺㔗

• 婔婻 PERFORM 臺埖套悩䫘潊(潡檕嚄)婔臯彍儖 FOUND 螆䘞婺䩘套悩澇橬䫘潊臯彍婺啺㔗

• 套悩躿儏嘌巉庖婔臯闼幽 UPDATE, INSERT, DELETE 臺埖螆䘞 FOUND 婺䩘套悩澇橬臯埖嘌巉彍婺啺㔗

• 婔婻 FETCH 臺埖套悩誫啂臯彍螆䘞 FOUND 婺䩘套悩婉誫啂臯彍婺啺㔗

• 婔婻 FOR 臺埖套悩誺傼庖婔渇潡崔渇彍螆䘞 FOUND 䩘劥彍婺啺㔗認婻蓇冋锗䫘庯欔橬 FOR 臺埖䔇婬䓉埻嘷(昘昄 FOR 冻䯇㔕螄嘘镖䔇 FOR 冻䯇㔕媘攕螄嘘镖 FOR 冻䯇)㔗埻橬婘 FOR 冻䯇锔庺䔇施唍欉螆䘞 FOUND 婘冻䯇欓臯䔇喙鄘FOUND 婉赆 FOR 臺埖媞櫹嘖滇婘冻䯇嘷麯垄埇脘赆噽垄臺埖䔇欓臯蔯媞櫹㔗

FOUND 滇懟婻 PL/pgSQL 麯䔇匔鄘埻麟傂嘘凹垄䔇傂嘘媞櫹埻嘌巉嘷嬉䔇庘昄㔗