PostgreSQL 8.3.3문서
Prev	Fast Backward	Chapter 37. PL/pgSQL - SQL수속 언어	Fast Forward	Next

37.11. Oracle PL/SQL로부터의 이식

본절에서는 Oracle로부터 PostgreSQL에 애플리케이션을 이식하는 개발자에게 도움이 되도록, PostgreSQL의 PL/pgSQL언어와 Oracle의 PL/SQL언어의 차이에 대해 설명합니다.

PL/pgSQL는 많은 점에서 PL/SQL를 닮아 있습니다. 그것은 블록으로 구성되어 있어, 엄격한 언어이며, 모든 변수는 선언되어야 합니다. 대입이나 루프, 조건 분기도 같습니다. PL/SQL로부터 PL/pgSQL에 이식할 때에 주의해야 하는, 주된 차이를 이하에 나타냅니다.

PostgreSQL의 매개변수에는 기본값이 없습니다.
PostgreSQL에서는 함수를 오버로드 할 수가 있습니다. 이것은 자주 기본값 매개변수의 결여를 보충하기 위해서 사용됩니다.
함수에서 참조된 열과 동일한 매개변수명을 사용할 수 있습니다. Oracle은 function_name.parameter_name를 사용하는 매개변수명이 요건을 갖추면 이를 할 수 있도록 허용합니다.
PL/pgSQL에서는 커서는 필요 없습니다. FOR문내에 쿼리를 둘 뿐입니다 (Example 37-6를 참조해 주세요).
PostgreSQL 의 함수 본체는 문자열 리터럴로서 쓰여야 합니다. 따라서, 함수 본문 내부에서 달러 인용부호를 사용하거나, 단일 인용부호를 회피 할 필요가 있습니다. (Section 37.2.1를 참조해 주세요).
패키지 대신에, 스키마를 사용해 함수군을 그룹에 정리해 주세요.
패키지가 없기 때문에, 패키지 레벨의 변수도 없습니다. 이것은 약간 귀찮은 일입니다. 대신, 세션마다 상태를 일시 테이블 내부에 보존할 수 있습니다.

37.11.1. 이식 예

Example 37-5에 간단한 함수의 PL/SQL로부터 PL/pgSQL로의 이식 방법을 나타냅니다.

Example 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;
/
에러를 보여줍니다.

이 함수를 통해서, PL/pgSQL와의 차이를 봅시다.

함수 prototype내의 RETURN키워드(함수 본문이 아닌)는 PostgreSQL에서는 RETURNS가 됩니다. 이와 같이 IS는 AS가 됩니다. PL/pgSQL이외의 언어에서도 함수를 기술할 수 있기 때문에, LANGUAGE구가 필요합니다.
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;

Example 37-6는, 다른 함수를 생성하는 함수를 이식하는 방법, 및, 그 결과 발생하는 인용부호 문제를 취급하는 방법을 나타냅니다.

Example 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; 
/ 
에러를 보여줍니다.

이 함수가 PostgreSQL이 되는 방법입니다.

CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$
DECLARE
    referrer_keys RECORD;  -- FOR로 사용되는 범용 레코드를 선언
    func_body text;
    func_cmd text;
BEGIN 
    func_body := 'BEGIN';

    -- FOR <record>구문을 사용한 FOR 루프로, 쿼리의 결과
    -- 를 어떻게 스캔 하고 있는지에 주의해 주세요.

    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가 인용부호를 포함할 때, 이상한 코드를 생성하지 않기 때문입니다.

Example 37-7는, OUT매개변수를 가져, 문자열 조작을 실시하는 함수의 이식 방법을 나타냅니다. PostgreSQL에는 내장된 instr함수는 없습니다만, 다른 함수를 조합하는 것으로 작성할 수 있습니다. Section 37.11.3의 에, 이식을 간략화할 수 있도록 instr의 PL/pgSQL에 의한 구현을 나타냅니다.

Example 37-7. 문자열 조작을 실시해, OUT매개변수를 가지는 PL/SQL프로시저의 PL/pgSQL로의 이식

이하의 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;
/
에러를 보여줍니다.

이것을 PostgreSQL로 기술하면, 이하와 같이 됩니다.

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');

Example 37-8는, Oracle에 특화한 많은 기능을 사용한 프로시저의 이식 방법을 나타냅니다.

Example 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; -- 락을 해제
(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와 다릅니다. 제공하는 예외의 명칭은, 훨씬 광범위합니다(Appendix A를 봐 주세요). 현재, 사용자 정의의 예외 명칭을 선언할 수 없습니다.

이 프로시저는 Oracle판에 비해 기능적인 차이가 있습니다. 그것은 cs_jobs테이블의 배타적 락이 트랜잭션의 종료까지 계속하는 것입니다. 같이 함수 호출의 후에(예를 들면 에러 때문에) 중단(abort)하면(자), 프로시저의 영향은 롤백(rollback) 됩니다.

37.11.2. 그 외의 주의사항

본절에서는, Oracle PL/SQL함수의 PostgreSQL로의 이식에 있어서의, 그 외의 주의 사항을 설명합니다.

37.11.2.1. 예외 후의 내재적 롤백(rollback)

PL/pgSQL에 대해 EXCEPTION구가 예외를 포착하면, BEGIN이후의 그 블록에 있어서의 데이터베이스의 변경이 자동적으로 롤백(rollback) 됩니다. 즉, Oracle의 PL/SQL에 의한 이하의 프로그램과 동등한 처리가 실행됩니다.

    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`

PL/pgSQL의 EXECUTE는 PL/SQL 비슷합니다. 그러나 Section 37.6.5로 설명되고 있는 quote_literal와 quote_ident를 사용하는 것을 기억해 두어야 합니다. 이러한 함수를 사용하지 않는 한 EXECUTE ''SELECT * from $1'';라는 구문의 동작에는, 신뢰성이 없습니다.

37.11.2.3. PL/pgSQL함수의 최적화

PostgreSQL에는 실행을 최적화하기 위해서 2개의 함수 생성 수식자가 있습니다. 변동성(같은 인수가 주어졌을 경우 항상 같은 결과가 돌려줍니다)과 "엄밀성"(인자의 어느 쪽에 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번째의 문자부터 m번째의 문자까지 string2를 찾습니다.
-- 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;

Prev	Home	Next
트리거 프로시저	Up	PL/Tcl - Tcl 수속형 언어

37.11. Oracle PL/SQL로부터의 이식

37.11.1. 이식 예

37.11.2. 그 외의 주의사항

37.11.2.1. 예외 후의 내재적 롤백(rollback)

37.11.2.2. EXECUTE

37.11.2.3. PL/pgSQL함수의 최적화

37.11.3. 부록

37.11.2.2. `EXECUTE`