PostgreSQL 8.2.3 婺桺桺懼
劯锔媆锔䆹37. PL/pgSQL - SQL 誺䘋臺蘔媆誕嬉誕

37.11. 傯 Oracle PL/SQL 誕臯䓂洉

橸誗蓼麪庖 Oracle 䔇 PL/SQL 启 PostgreSQL 䔇 PL/pgSQL 臺蘔幋閘䔇噞彆婯橕脘凹闼底傯 Oracle 劏 PostgreSQL 䓂洉庫䫘䔇庺橬欔婞媷㔗

PL/pgSQL 婯 PL/SQL 婘螩崔桹麵鄘麂婩䌂嚚㔗垄滇婔䓉庖䂷悇䔇䖽嘪臺愫(变傴攓)䔇臺蘔幽婫媙釂弄滯欔橬埻麟㔗蕋唚㔕冻䯇㔕溇傽京鄘冽䌂嚚㔗婘傯 Oracle 劏 PostgreSQL 䓂洉䔇施唍媙釂螄嘟婔底庋愙

37.11.1. 䓂洉湙冋

冋37-5 暫䴺庖套嘘傯 PL/SQL 劏 PL/pgSQL 䓂洉婔婻䞔剘䔇庘昄㔗

冋37-5. 傯 PL/SQL 劏 PL/pgSQL 䓂洉婔婻䞔剘䔇庘昄

婋麵滇婔婻 Oracle PL/SQL 庘昄

CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
                                                  v_version varchar)
RETURN varchar IS
BEGIN
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
/
show errors;

螷潏傸臂婔镉認婻庘昄䇽劯䩋䩋 PL/pgSQL 婯幋䔇婉劯

  • 婘庘昄寘傋麯䔇 RETURN(婉滇庘昄嘷麯䔇)噿髞庖彄庖 PostgreSQL 麯儌滇 RETURNS 㔗誻橬IS 埻潊 AS 幽婫嘹誻驔襕嵂媹婔婻 LANGUAGE 床埖啹婺 PL/pgSQL 幽麂嫇婔埇䫘䔇庘昄臺蘔㔗

  • 婘 PostgreSQL 麯庘昄嘷赆螴婺滇婔婻庖严婾桺橸欔傖嘹驔襕嘪䫘剘嚘埙潡蔙䆯噄严䘯垔垄認婻寙啘严傼敪庖 Oracle 橔劯䔇闼婻 /

  • 婘 PostgreSQL 麯澇橬 show errors 变傴婉驔襕認婻变傴滇啹婺髍臇滇躻媘檖只䔇㔗

婋麵滇認婻庘昄䓂洉彄 PostgreSQL 幋劯䔇湙床

CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
                                                  v_version varchar)
RETURNS varchar AS $$
BEGIN
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
$$ LANGUAGE plpgsql;

冋37-6暫䴺庖套嘘䓂洉婔婻录傺埥崡婔婻庘昄䔇庘昄䔇桹濘傖埪暫䴺庖套嘘崇䊖嚘埙锄锩䔇閞鵻㔗

冋37-6. 傯 PL/SQL 劏 PL/pgSQL 䓂洉婔婻录傺噽垄庘昄䔇庘昄

婋麵䔇誺䘋傯婔婻 SELECT 臺埖婺檷埡苖幾臯䇽劯婺庖柊醻昽䯺埽䫘 IF 臺埖婺䔇䂷悩彽嘩庖婔婻噘崓䔇庘昄㔗臙䬹彆濘懟婘橩湺启 FOR 冻䯇麯麵䔇婉劯㔗

認滇 Oracle 䔇䬽橸

CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
    CURSOR referrer_keys IS 
        SELECT * FROM cs_referrer_keys 
        ORDER BY try_order;

    func_cmd VARCHAR(4000); 
BEGIN 
    func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR,
                 v_domain IN VARCHAR, v_url IN VARCHAR) RETURN VARCHAR IS BEGIN'; 

    FOR referrer_key IN referrer_keys LOOP 
        func_cmd := func_cmd ||
          ' IF v_' || referrer_key.kind
          || ' LIKE ''' || referrer_key.key_string
          || ''' THEN RETURN ''' || referrer_key.referrer_type
          || '''; END IF;'; 
    END LOOP; 

    func_cmd := func_cmd || ' RETURN NULL; END;'; 

    EXECUTE IMMEDIATE func_cmd; 
END; 
/ 
show errors;

麵滇認婻庘昄婘 PostgreSQL 麯麵䔇湙床

CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$
DECLARE
    referrer_key RECORD;  -- 弄滯婔婻婘 FOR 麯䫘䔇锔䫘䔇螄嘘
    func_body text;
    func_cmd text;
BEGIN 
    func_body := 'BEGIN';

    -- 臙濘懟滇套嘘婘婔婻 FOR 冻䯇婺嘪䫘 FOR <record> 悇锹欆柟劇溇䂷悩䔇

    FOR referrer_key IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
        func_body := func_body ||
          ' IF v_' || referrer_key.kind
          || ' LIKE ' || quote_literal(referrer_key.key_string)
          || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
          || '; END IF;' ;
    END LOOP; 

    func_body := func_body || ' RETURN NULL; END;';

    func_cmd :=
      'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
                                                        v_domain varchar,
                                                        v_url varchar) 
        RETURNS varchar AS '
      || quote_literal(func_body)
      || ' LANGUAGE plpgsql;' ;

    EXECUTE func_cmd;
END;
$func$ LANGUAGE plpgsql;

臙濘懟庘昄嘷滇套嘘䋸䆋彽嘩幽婫嚹锐䂍 quote_literal 凹噽婺䔇剘嚘埙崉彽埯傘䔇㔗驔襕認婻檔噓滇啹婺方濘嘪䫘䆯噄严䘯垔垔幬桄庘昄澇濘媺臕 referrer_key.key_string 庖枕誺準䔇庖严婾嚔蓼悊潊傔幽湙床㔗埇傖啺螆 referrer_key.kind 滇埻橬 host, domain, url 嘖滇 referrer_key.key_string埇脘滇傂嘘婩薪䬹彆滇垄埇脘寙劆䆯噄严㔗認婻庘昄垂鍙婪滇凹寘準 Oracle 䬽橸䔇婔婻櫹誕啹婺套悩婘 referrer_key.key_string 潡 referrer_key.referrer_type 寙劆剘嚘埙䔇施唍垄婉嚔䫘潊橬懕䖙䔇傼乕㔗

冋37-7暫䴺庖套嘘䓂洉婔婻婥橬 OUT 埗昄启庖严婾崇䊖䔇庘昄㔗PostgreSQL 麯麵澇橬喙䘞 instr 庘昄嘖滇嘹埇傖䫘噽垄庘昄䔇䂇劽準䂘嚔垄㔗婘誗37.11.3麯橬婔婻 PL/pgSQL 䔇 instr 垂䯄嘹埇傖䫘垄螷嘹䔇䓂洉埻冖敘䞔剘底㔗

冋37-7. 傯 PL/SQL 劏 PL/pgSQL 䓂洉婔婻庖严婾淉嘩启 OUT 埗昄䔇誺䘋

婋麵䔇 Oracle PL/SQL 誺䘋䫘庯彖悊婔婻 URL 幽婫誫啂苖幾婻噄䘹(婂橺㔕虇冇㔕变傴)㔗

婋麵滇 Oracle 䔇䬽橸

CREATE OR REPLACE PROCEDURE cs_parse_url(
    v_url IN VARCHAR,
    v_host OUT VARCHAR,  -- 認婻埻麟滇襕嚹啂䔇
    v_path OUT VARCHAR,  -- 認婻幘滇
    v_query OUT VARCHAR) -- 誻橬認婻
IS
    a_pos1 INTEGER;
    a_pos2 INTEGER;
BEGIN
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
    a_pos1 := instr(v_url, '//');

    IF a_pos1 = 0 THEN
        RETURN;
    END IF;
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
    IF a_pos2 = 0 THEN
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
        RETURN;
    END IF;

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
    a_pos1 := instr(v_url, '?', a_pos2 + 1);

    IF a_pos1 = 0 THEN
        v_path := substr(v_url, a_pos2);
        RETURN;
    END IF;

    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
END;
/
show errors;

婋麵儌滇檪認婻誺䘋䪂臏潊 PL/pgSQL 埇脘䔇湙床

CREATE OR REPLACE FUNCTION cs_parse_url(
    v_url IN VARCHAR,
    v_host OUT VARCHAR,  -- 認婻儖赆嚹啂
    v_path OUT VARCHAR,  -- 認婻幘嚹啂
    v_query OUT VARCHAR) -- 誻橬認婻
AS $$
DECLARE
    a_pos1 INTEGER;
    a_pos2 INTEGER;
BEGIN
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
    a_pos1 := instr(v_url, '//');

    IF a_pos1 = 0 THEN
        RETURN;
    END IF;
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
    IF a_pos2 = 0 THEN
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
        RETURN;
    END IF;

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
    a_pos1 := instr(v_url, '?', a_pos2 + 1);

    IF a_pos1 = 0 THEN
        v_path := substr(v_url, a_pos2);
        RETURN;
    END IF;

    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
END;
$$ LANGUAGE plpgsql;

認婻庘昄埇傖認幽䫘

SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');

冋37-8暫䴺庖套嘘婔婻嘪䫘劇䓉 Oracle 婷橬䬹攓䔇誺䘋㔗

冋37-8. 傯 PL/SQL 劏 PL/pgSQL 䓂洉婔婻誺䘋

Oracle 䬽橸

CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
    a_running_job_count INTEGER;
    PRAGMA AUTONOMOUS_TRANSACTION;(1)
BEGIN
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;(2)

    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;

    IF a_running_job_count > 0 THEN
        COMMIT; -- free lock(3)
        raise_application_error(-20000, 'Unable to create a new job: a job is currently running.');
    END IF;

    DELETE FROM cs_active_job;
    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);

    BEGIN
        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, sysdate);
    EXCEPTION
        WHEN dup_val_on_index THEN NULL; -- 套悩噾䂟庻婘彆拙媄
    END;
    COMMIT;
END;
/
show errors

償認湙䔇誺䘋埇傖冽垹滷䫘誫啂 void 䔇庘昄䓂洉彄 PostgreSQL 麯㔗凹認婻誺䘋䬹彆懘噘轼滇啹婺垄埇傖昍婔底婩薪

(1)
婘 PostgreSQL 麯澇橬 PRAGMA 臺埖㔗
(2)
套悩嘹婘 PL/pgSQL 麯啔婔婻 LOCK TABLE 闼幽認婻髕婘脄䫘臖变傴䔇庋媇垯潊幋嬉儖婉嚔麪櫆㔗
(3)
嘹婉脘婘 PL/pgSQL 庘昄麯埏庺 COMMIT 㔗庘昄滇婘崡北䔇庋媇麯誊臯䔇啹溴 COMMIT 蘘潕五䂷溘庘昄䔇欓臯㔗婉誺婘認婻䬹枪婺劽婋認滇婉媙襕䔇庖啹婺 LOCK TABLE 诙埡䔇髕儖婘檕庺髍臇䔇施唍麪櫆㔗

婋麵滇檪認婻誺䘋䓂洉彄 PL/pgSQL 麯䔇婔䓉桹濘

CREATE OR REPLACE FUNCTION cs_create_job(v_job_id integer) RETURNS void AS $$
DECLARE
    a_running_job_count integer;
BEGIN
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;

    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;

    IF a_running_job_count > 0 THEN
        RAISE EXCEPTION 'Unable to create a new job: a job is currently running';(1)
    END IF;

    DELETE FROM cs_active_job;
    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);

    BEGIN
        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
    EXCEPTION
        WHEN unique_violation THEN (2)
            -- 套悩噾䂟庻婘彆拙媄
    END;
END;
$$ LANGUAGE plpgsql;

(1)
RAISE 䔇臺濘启Oracle䔇䌂嚚臺埖噞彆䕩嘷滯滆㔗
(2)
PL/pgSQL 麯櫇毕䔇嚗婩䔇劉庖启 Oracle 䔇婉劯㔗 PL/pgSQL 喙䘞䔇嚗婩劉襕崓䔇崔(埗黙鍇嘘A)㔗䕞嬉誻婉脘弄滯䫘潙垔幬䔇嚗婩劉㔗
昘婻誺䘋启 Oracle 䔇京昽䔇婂襕䔇媘脘傋噞彆滇婘 cs_jobs 婪毕橬䔇毐傡髕儖媺毕彄脄䫘䔇庋媇䂷溘㔗劯湙套悩脄䫘蔙劯準锔庺(懫套臘啹婺髍臇)認婻誺䘋䔇昽悩儖赆啂悔毬㔗

37.11.2. 噽垄濘懟庋釹

橸誗蓼麪庹婻傯 Oracle PL/SQL 庘昄劏 PostgreSQL 䓂洉䔇庹婻噽垄桹麵䔇庋愙㔗

37.11.2.1. 嚗婩劯䔇锊劆啂悔

婘 PL/pgSQL 麯套悩婔婻嚗婩赆 EXCEPTION 床埖托诙闼幽欔橬躻認婻庖䔇 BEGIN 傖準䔇昄扞康櫹埻鄘嚔赆躻媘啂悔㔗幘儌滇臘認婻臯婺京庯嘹婘 Oracle 麯䔇

    BEGIN
        SAVEPOINT s1;
        ... code here ...
    EXCEPTION
        WHEN ... THEN
            ROLLBACK TO s1;
            ... code here ...
        WHEN ... THEN
            ROLLBACK TO s1;
            ... code here ...
    END;

套悩嘹婘檪認幽嘪䫘 SAVEPOINT 启 ROLLBACK TO 䔇 Oracle 誺䘋䪂臏誺準闼幽嘹䔇昂凪冽喘幾埻襕䩕䘖 SAVEPOINT 启 ROLLBACK TO 剿埇㔗套悩嘹襕䪂臏䔇誺䘋嘪䫘庖婉劯䔇 SAVEPOINT 启 ROLLBACK TO 闼幽儌驔襕愿愿庖㔗

37.11.2.2. EXECUTE

PostgreSQL 䬽橸䔇 EXECUTE 誊蘸冖䌂嚚 PL/SQL 婉悩嘹媙釂螄嘟襕償誗37.6.5麯柟誄䔇闼湙䫘 quote_literal 启 quote_ident 㔗套悩嘹婉䫘認底庘昄闼幽償 EXECUTE 'SELECT * FROM $1'; 認湙䔇悇锹滇婉嚔誊蘸䔇㔗

37.11.2.3. Optimizing PL/pgSQL Functions

PostgreSQL 䂍嘹婴婻录傺庘昄䔇媞閄臉䫘準嚻寡欓臯"volatility"(滷埻䔇婘䂍庺䔇埗昄䕩劯施庘昄攂滇誫啂䕩劯䂷悩)启"strictness"(婖湚䔇套悩傂嘘埗昄滇 NULL 闼幽庘昄誫啂 NULL)㔗埗蔄 CREATE FUNCTION 䔇欋喯诙埡䂖誗㔗

套悩襕嘪䫘認底嚻寡匂攓闼幽嘹䔇 CREATE FUNCTION 臺埖埇脘䩋蕙準償認湙

CREATE FUNCTION foo(...) RETURNS integer AS $$
...
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

37.11.3. 鍇嘘

橸誗寙劆 Oracle 噚垹䔇 instr 庘昄嘹埇傖䫘垄䞔寡嘹䔇䓂洉誺䘋㔗

--
-- 昇拘 Oracle 楗媕䔇 instr 庘昄
-- 臺濘: instr(string1, string2, [n], [m])   認麯䔇 [] 臘䴺埇锬埗昄
-- 
-- 傯 string1 䔇丸 n 婻庖严嚔哋凂欆 string2 䔇丸 m 婻庺䯄㔗
-- 套悩 n 滇蘘昄彍傯劯劏嬉五㔗套悩澇橬嚹锐 m 啺垔婺 1(傯丸婔婻庖严嚔哋欆)㔗
--

CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
DECLARE
    pos integer;
BEGIN
    pos:= instr($1, $2, 1);
    RETURN pos;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;


CREATE FUNCTION instr(string varchar, string_to_search varchar, beg_index integer)
RETURNS integer AS $$
DECLARE
    pos integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    length integer;
    ss_length integer;
BEGIN
    IF beg_index > 0 THEN
        temp_str := substring(string FROM beg_index);
        pos := position(string_to_search IN temp_str);

        IF pos = 0 THEN
            RETURN 0;
        ELSE
            RETURN pos + beg_index - 1;
        END IF;
    ELSE
        ss_length := char_length(string_to_search);
        length := char_length(string);
        beg := length + beg_index - ss_length + 2;

        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            pos := position(string_to_search IN temp_str);

            IF pos > 0 THEN
                RETURN beg;
            END IF;

            beg := beg - 1;
        END LOOP;

        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;


CREATE FUNCTION instr(string varchar, string_to_search varchar,
                      beg_index integer, occur_index integer)
RETURNS integer AS $$
DECLARE
    pos integer NOT NULL DEFAULT 0;
    occur_number integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    i integer;
    length integer;
    ss_length integer;
BEGIN
    IF beg_index > 0 THEN
        beg := beg_index;
        temp_str := substring(string FROM beg_index);

        FOR i IN 1..occur_index LOOP
            pos := position(string_to_search IN temp_str);

            IF i = 1 THEN
                beg := beg + pos - 1;
            ELSE
                beg := beg + pos;
            END IF;

            temp_str := substring(string FROM beg + 1);
        END LOOP;

        IF pos = 0 THEN
            RETURN 0;
        ELSE
            RETURN beg;
        END IF;
    ELSE
        ss_length := char_length(string_to_search);
        length := char_length(string);
        beg := length + beg_index - ss_length + 2;

        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            pos := position(string_to_search IN temp_str);

            IF pos > 0 THEN
                occur_number := occur_number + 1;

                IF occur_number = occur_index THEN
                    RETURN beg;
                END IF;
            END IF;

            beg := beg - 1;
        END LOOP;

        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
劯锔饡釕嬉誕
蓥埏単誺䘋婪婔亓PL/Tcl - Tcl 誺䘋臺蘔