반응형
24.5.2. 함수 전환하기
Example 24/5. 간단한 함수
아래의 예는 Oracle의 함수입니다.
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name IN varchar, v_version IN 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과의 차이점을 알아보자
OR REPLACE 부분은 PL/pgSQL에서 사용하지 않는다. PL/pgSQL에서는 확실하게 이미 생성된 함수를 삭제하고 생성해야 비슷한 결과는 낼 수 있다.
PostgreSQL는 매개변수가 이름을 갖지 않는다. 전환할 때 함수 내에서 명확하게 alias를 이용하여 변수를 선언해야한다.
Oracle은 함수에 값을 넘길 때 IN, OUT 그리고 INOUT 매개변수를 갖는다. 여기서 INOUT은 값을 받고 그 변수를 통해 값을 반환하는 역할을 한다. 그러나 PostgreSQL은 오직 "IN" 매개변수만을 갖고 오직 1개의 값만을 반환한다.
Oracle에서 함수를 정의할 때 사용하는 RETURN 키워드는 PostgreSQL에서는 RETURNS로 사용한다.
PostgreSQL에서 함수는 작은 따옴표를 이용하여 생성되기 때문에 함수 내부의 따옴표에 대해서 조심해서 잘 사용해야한다. (Section 24.5.1.1 참조 바람)
"/show"는 PostgreSQL에는 없기 때문에 오류를 야기한다.
자.. 이제 PostgreSQL에서 변환되고 나서 어떻게 보이는지 한번 보자.
DROP FUNCTION cs_fmt_browser_version(varchar, varchar);
CREATE FUNCTION cs_fmt_browser_version(varchar, varchar)
RETRUNS varchar AS '
DECLARE
v_name ALIAS FOR $1;
v_version ALIAS FOR $2;
BEGIN
IF v_version IS NULL THEN
return v_name;
END IF;
RETURN v_name || ''/'' || v_version;
END;
' LANGUAGE 'plpgsql';
Example 24/6. 다른 함수를 생성하는 함수
다음의 procedure는 SELECT 문에서 결과를 받은 다음 IF문 안에서 결과를 가지고 효율성을 위해 커다란 함수를 만든다. 다음의 예로부터 커서, FOR 반복문, PostgreSQL의 작은 따옴표 문제 등의 차이점을 주목해서 보기 바란다.
create or replace procedure cs_update_referrer_type_proc is
cursor referrer_keys is
select * from cs_referrer_keys
order by try_order;
a_output varchar(4000);
begin
a_output := '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
a_output := a_output || ' if v_' || referrer_key.kind || ' like ''' ||
referrer_key.key_string || ''' then return ''' || referrer_key.referrer_type ||
'''; end if;';
end loop;
a_output := a_output || ' return null; end;';
execute immediate a_output;
end;
/
show errors
변환된 PostgreSQL:
CREATE FUNCTION cs_update_referrer_type_proc() RETURNS integer AS '
DECLARE
referrer_keys RECORD; // FOR에서 사용하기 위한 일반 RECORD 선언
a_output varchar(4000);
BEGIN
a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar,varchar,varchar)
RETURNS varchar AS ''''
DECLARE
v_host ALIAS FOR $1;
v_domain ALIAS FOR $2;
v_url ALIAS FOR $3; '';
//
// 어떻게 FOR 반복문에서 쿼리의 결과를 가져오는지 주목하라
// 여기서는 FOR <record> 를 사용하였다..
//
FOR referrer_keys IN select * from cs_referrer_keys order by try_order LOOP
a_output := a_output || '' if v_'' || referrer_keys.kind || '' like ''''''''''
|| referrer_keys.key_string || '''''''''' then return ''''''
|| referrer_keys.referrer_type || ''''''; end if;'';
END LOOP;
a_output := a_output || '' return null; end; '''' language ''''plpgsql'''';'';
// 여기서는 변후를 치환하지 않았기 때문에 적법하다.
// 그러지 않으면 실패를 할 것이다. 함수를 작동시기기 위한 다른 방법인 PERFORM 을 볼것.
EXECUTE a_output;
end;
' LANGUAGE 'plpgsql';
Example 24/7. 많은 문자열을 처리하는 함수와 OUT 매개변수
다음에 나오는 Oracle PL/SQL procedure는 URL을 받아 이를 몇몇개의 부분 (host, path, query)로 쪼게 반환하는 함수이다. 이것은 procedure 이어야 하는 까닭은 PL/pgSQL 함수는 오직 하나의 값만을 반환할 수 잇기 때문이다. (24.5.3 절 참조)
PostgreSQL에서 이를 해결하는 방법의 3개의 다른 함수로 procedure로 쪼개야한다. 하나는 host를 반환, 다른하나는 path, 그리고 또다른 하나는 query 식으로....
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, '//'); // PostgreSQL는 이 함수가 없다.
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;
PostgreSQL로 변환한 뒤:
drop function cs_parse_url_host(varchar);
create function cs_parse_url_host(varchar) returns varchar as '
declare
v_url ALIAS FOR $1;
v_host varchar;
v_path varchar;
a_pos1 integer;
a_pos2 integer;
a_pos3 integer;
begin
v_host := NULL;
a_pos1 := instr(v_url,''//'');
if a_pos1 = 0 then
return ''''; // Return a blank
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 v_host;
end if;
v_host := substr(v_url, a_pos1 + 2, a_pos2 / a_pos1 / 2 );
return v_host;
end;
' language 'plpgsql';
주목: PostgreSQL는 instr함수가 없기 때문에 다른 함수를 조합하여 구현하도록 한다. 나는 나는 그렇게 하기가 귀찮아서 나만의 오라클의 기능과 같은 instr함수를 만들었다. ( 24.5.6 참조)
24.5.3. Procedure
Oracle의 procedure는 좀더 많은 유연성을 제공한다. 왜냐하면 반환값을 명시해줄 필요가 없기 때문이긴 하지만 INOUT 또는 OUT을 사용하여 값을 반환할 수는 있다.
예:
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; // 이미 있어도 걱정말것(4)
end;
commit;
end;
/
show errors
위의 예와같은 Procedure는 쉽게 INTEGER를 반환하는 PostgreSQL 함수로 변환할 수 있다. 위의 예는 상당히 재미있게도 몇가지 가르쳐 주는 바가 있다.
(1) postgreSQL에는 'pragma' 문이 없다.
(2) PL/pgSQL에서 LOCK TABLE을 사용하면 잠금상태는 트랜젝션이 종료될 때 까지 유지될 것이다.
(3) PL/pgSQL procedure 내에는 또 다른 트렌젝션을 가질 수 없다. 함수는 (그리고 여기서 호출된 다른 함수) 하나의 트랜젝션 속에서 실행이 된다. 그래서 어떤 문제가 발생하였을 때 PL/pgSQL는 결과를 roll back한다. 그래서 오직 하나의 BEGIN문 만을 사용할 수 있다.
(4) IF문을 쓸 수 있다면 예외처리보다 나을 듯...
아래의 코드는 PL/pgSQL procedure로 전환한 것이다.
drop function cs_create_job(integer);
create function cs_create_job(integer) returns integer as ' declare
v_job_id alias for $1;
a_running_job_count integer;
a_num integer;
// pragma autonomous_transaction;
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
// commit; // free lock
raise exception ''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);
SELECT count(*) into a_num FROM cs_jobs WHERE job_id=v_job_id;
IF NOT FOUND THEN // If nothing was returned in the last query
// This job is not in the table so lets insert it.
insert into cs_jobs(job_id, start_stamp) values(v_job_id, sysdate());
return 1;
ELSE
raise NOTICE ''Job already running.'';(1)
END IF;
return 0;
end;
' language 'plpgsql';
(1) PL/pgSQL에서 어떻게 하면 Notice나 error을 사용할 수 있는지 볼것...
24.5.4. Package
주목: 나는 이 패키지로 그리 많은 일을 하지 않았다. 그러니 아래의 코드에 오류가 있을 수 있다.
Package는 Oracle이 제공하는 PL/SQL문과 함수들을 하나로 묶어 은패(encapsulate)하는 방법을 제공한다. (자바에서 클래스처럼 함수와 오브젝트를 정의 하는 식의....)
그리고 이러한 오브젝트와 함수들은 점 '.'을 이용하여 접근할 수 있다.
아래에 ACS 4(the ArsDigita Community System)에서 사용하는 Oracle Package가 있다.
create or replace package body acs
as
function add_user (
user_id in users.user_id%TYPE default null,
object_type in acs_objects.object_type%TYPE
default 'user',
creation_date in acs_objects.creation_date%TYPE
default sysdate,
creation_user in acs_objects.creation_user%TYPE
default null,
creation_ip in acs_objects.creation_ip%TYPE default null,
...
) return users.user_id%TYPE
is
v_user_id users.user_id%TYPE;
v_rel_id membership_rels.rel_id%TYPE;
begin
v_user_id := acs_user.new (user_id, object_type, creation_date,
creation_user, creation_ip, email,
...
return v_user_id;
end;
end acs;
/
show errors
여기서는 다른 오프젝트를 생성하여 변환을 하는데 일반적인 이름을 갖는 함수를 정의하여 Oracle Package를 대신하였다. 여기서 몇가지 주의해야하는데, PostgreSQL 함수의 기본 매개변수의 부족 같은 것이다. 위의 package는 아래와 같은 방식으로 바뀔수 있다.
CREATE FUNCTION acs__add_user(integer,integer,varchar,datetime,integer,integer,...)
RETURNS integer AS '
DECLARE
user_id ALIAS FOR $1;
object_type ALIAS FOR $2;
creation_date ALIAS FOR $3;
creation_user ALIAS FOR $4;
creation_ip ALIAS FOR $5;
...
v_user_id users.user_id%TYPE;
v_rel_id membership_rels.rel_id%TYPE;
BEGIN
v_user_id := acs_user__new(user_id,object_type,creation_date,creation_user,creation_ip, ...);
...
return v_user_id;
END;
' LANGUAGE 'plpgsql';
24.5.5. 눈여겨 봐야할 기타
24.5.5.1. EXECUTE
PostgreSQL의 EXECUTE는 잘 작동하지만 Section 24.2.5.3에서 설명하였지만 quote_literal(TEXT) 와 quote_string(TEXT)를 사용할 때는 명심해야하는 것이 있다. EXECUTE ''SELECT * from $1'';를 사용하는 것은 앞에서 말한 함수를 사용하지 않으면 제대로 작동하지 않을 것이다.
24.5.5.2. PL/pgSQL 함수의 최적화
PostgreSQL에는 두가지의 함수 최적화 옵션이 있다.
iscachable (매개변수 값이 같으면 함수는 항상 같은 값을 반환한다.) 와 isstrict (매개변수 중에 NULL이 있으면 함수는 항상 NULL을 반환한다.). 좀더 자세한 것은 CREATE FUNCTION 참고서를 보기바란다.
이 최적화를 위한 옵션을 사용하기 위해서, WITH 문을 CREATE FUNCTION문과 함께 다음과 같이 사용한다.
CREATE FUNCTION foo(...) RETURNS integer AS '
...
' LANGUAGE 'plpgsql'
WITH (isstrict, iscachable);
24.5.6. 부록
24.5.6.1. instr 함수 코드
//
// Oracle의 instr 함수와 같은 역할을 하는 함수.
// 사용법: instr(string1,string2,[n],[m]) [] 부분은 옵션.
//
// string2에서 n번째의 문자부터 탐색하여 string1을 찾는다. n 이 음수이면
// 뒤로부터 찾는다. 만일 m이 넘겨지지 않았으면 1로 가정한다.
// (처음부터 찾는다)
//
//
// by Roberto Mello (rmello@fslc.usu.edu)
// modified by Robert Gaszewski (graszew@poland.com)
// Licensed under the GPL v2 or later.
//
DROP FUNCTION instr(varchar,varchar);
CREATE FUNCTION instr(varchar,varchar) RETURNS integer AS '
DECLARE
pos integer;
BEGIN
pos:= instr($1,$2,1);
RETURN pos;
END;
' language 'plpgsql';
DROP FUNCTION instr(varchar,varchar,integer);
CREATE FUNCTION instr(varchar,varchar,integer) RETURNS integer AS '
DECLARE
string ALIAS FOR $1;
string_to_search ALIAS FOR $2;
beg_index ALIAS FOR $3;
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';
//
// Written by Robert Gaszewski (graszew@poland.com)
// Licensed under the GPL v2 or later.
//
DROP FUNCTION instr(varchar,varchar,integer,integer);
CREATE FUNCTION instr(varchar,varchar,integer,integer) RETURNS integer AS '
DECLARE
string ALIAS FOR $1;
string_to_search ALIAS FOR $2;
beg_index ALIAS FOR $3;
occur_index ALIAS FOR $4;
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';
////////////////////////////////////////////////////////////////////////////
휴.... 여기 까지 입니다. 바로 앞글에서 보시다시피 잠시 옆길로 세서 테스트 프로그램 까지 만들어 보았습니다.
번역해보니 Oracle의 PL/SQL보다는 기능이 좀 부족하긴 하지만 그래도 독립적인 함수를 만들어 기능을 제공할 수가 있네요. 또한 단순히 PL/pgSQL만이 아니고 PL/TCL, PL/PERL 같은 언어도 쓸수가 있으니....
어쨌거나 도움이 되었기를 바라며,
그럼 즐겁게 DB를 디비십시요.
Example 24/5. 간단한 함수
아래의 예는 Oracle의 함수입니다.
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name IN varchar, v_version IN 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과의 차이점을 알아보자
OR REPLACE 부분은 PL/pgSQL에서 사용하지 않는다. PL/pgSQL에서는 확실하게 이미 생성된 함수를 삭제하고 생성해야 비슷한 결과는 낼 수 있다.
PostgreSQL는 매개변수가 이름을 갖지 않는다. 전환할 때 함수 내에서 명확하게 alias를 이용하여 변수를 선언해야한다.
Oracle은 함수에 값을 넘길 때 IN, OUT 그리고 INOUT 매개변수를 갖는다. 여기서 INOUT은 값을 받고 그 변수를 통해 값을 반환하는 역할을 한다. 그러나 PostgreSQL은 오직 "IN" 매개변수만을 갖고 오직 1개의 값만을 반환한다.
Oracle에서 함수를 정의할 때 사용하는 RETURN 키워드는 PostgreSQL에서는 RETURNS로 사용한다.
PostgreSQL에서 함수는 작은 따옴표를 이용하여 생성되기 때문에 함수 내부의 따옴표에 대해서 조심해서 잘 사용해야한다. (Section 24.5.1.1 참조 바람)
"/show"는 PostgreSQL에는 없기 때문에 오류를 야기한다.
자.. 이제 PostgreSQL에서 변환되고 나서 어떻게 보이는지 한번 보자.
DROP FUNCTION cs_fmt_browser_version(varchar, varchar);
CREATE FUNCTION cs_fmt_browser_version(varchar, varchar)
RETRUNS varchar AS '
DECLARE
v_name ALIAS FOR $1;
v_version ALIAS FOR $2;
BEGIN
IF v_version IS NULL THEN
return v_name;
END IF;
RETURN v_name || ''/'' || v_version;
END;
' LANGUAGE 'plpgsql';
Example 24/6. 다른 함수를 생성하는 함수
다음의 procedure는 SELECT 문에서 결과를 받은 다음 IF문 안에서 결과를 가지고 효율성을 위해 커다란 함수를 만든다. 다음의 예로부터 커서, FOR 반복문, PostgreSQL의 작은 따옴표 문제 등의 차이점을 주목해서 보기 바란다.
create or replace procedure cs_update_referrer_type_proc is
cursor referrer_keys is
select * from cs_referrer_keys
order by try_order;
a_output varchar(4000);
begin
a_output := '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
a_output := a_output || ' if v_' || referrer_key.kind || ' like ''' ||
referrer_key.key_string || ''' then return ''' || referrer_key.referrer_type ||
'''; end if;';
end loop;
a_output := a_output || ' return null; end;';
execute immediate a_output;
end;
/
show errors
변환된 PostgreSQL:
CREATE FUNCTION cs_update_referrer_type_proc() RETURNS integer AS '
DECLARE
referrer_keys RECORD; // FOR에서 사용하기 위한 일반 RECORD 선언
a_output varchar(4000);
BEGIN
a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar,varchar,varchar)
RETURNS varchar AS ''''
DECLARE
v_host ALIAS FOR $1;
v_domain ALIAS FOR $2;
v_url ALIAS FOR $3; '';
//
// 어떻게 FOR 반복문에서 쿼리의 결과를 가져오는지 주목하라
// 여기서는 FOR <record> 를 사용하였다..
//
FOR referrer_keys IN select * from cs_referrer_keys order by try_order LOOP
a_output := a_output || '' if v_'' || referrer_keys.kind || '' like ''''''''''
|| referrer_keys.key_string || '''''''''' then return ''''''
|| referrer_keys.referrer_type || ''''''; end if;'';
END LOOP;
a_output := a_output || '' return null; end; '''' language ''''plpgsql'''';'';
// 여기서는 변후를 치환하지 않았기 때문에 적법하다.
// 그러지 않으면 실패를 할 것이다. 함수를 작동시기기 위한 다른 방법인 PERFORM 을 볼것.
EXECUTE a_output;
end;
' LANGUAGE 'plpgsql';
Example 24/7. 많은 문자열을 처리하는 함수와 OUT 매개변수
다음에 나오는 Oracle PL/SQL procedure는 URL을 받아 이를 몇몇개의 부분 (host, path, query)로 쪼게 반환하는 함수이다. 이것은 procedure 이어야 하는 까닭은 PL/pgSQL 함수는 오직 하나의 값만을 반환할 수 잇기 때문이다. (24.5.3 절 참조)
PostgreSQL에서 이를 해결하는 방법의 3개의 다른 함수로 procedure로 쪼개야한다. 하나는 host를 반환, 다른하나는 path, 그리고 또다른 하나는 query 식으로....
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, '//'); // PostgreSQL는 이 함수가 없다.
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;
PostgreSQL로 변환한 뒤:
drop function cs_parse_url_host(varchar);
create function cs_parse_url_host(varchar) returns varchar as '
declare
v_url ALIAS FOR $1;
v_host varchar;
v_path varchar;
a_pos1 integer;
a_pos2 integer;
a_pos3 integer;
begin
v_host := NULL;
a_pos1 := instr(v_url,''//'');
if a_pos1 = 0 then
return ''''; // Return a blank
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 v_host;
end if;
v_host := substr(v_url, a_pos1 + 2, a_pos2 / a_pos1 / 2 );
return v_host;
end;
' language 'plpgsql';
주목: PostgreSQL는 instr함수가 없기 때문에 다른 함수를 조합하여 구현하도록 한다. 나는 나는 그렇게 하기가 귀찮아서 나만의 오라클의 기능과 같은 instr함수를 만들었다. ( 24.5.6 참조)
24.5.3. Procedure
Oracle의 procedure는 좀더 많은 유연성을 제공한다. 왜냐하면 반환값을 명시해줄 필요가 없기 때문이긴 하지만 INOUT 또는 OUT을 사용하여 값을 반환할 수는 있다.
예:
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; // 이미 있어도 걱정말것(4)
end;
commit;
end;
/
show errors
위의 예와같은 Procedure는 쉽게 INTEGER를 반환하는 PostgreSQL 함수로 변환할 수 있다. 위의 예는 상당히 재미있게도 몇가지 가르쳐 주는 바가 있다.
(1) postgreSQL에는 'pragma' 문이 없다.
(2) PL/pgSQL에서 LOCK TABLE을 사용하면 잠금상태는 트랜젝션이 종료될 때 까지 유지될 것이다.
(3) PL/pgSQL procedure 내에는 또 다른 트렌젝션을 가질 수 없다. 함수는 (그리고 여기서 호출된 다른 함수) 하나의 트랜젝션 속에서 실행이 된다. 그래서 어떤 문제가 발생하였을 때 PL/pgSQL는 결과를 roll back한다. 그래서 오직 하나의 BEGIN문 만을 사용할 수 있다.
(4) IF문을 쓸 수 있다면 예외처리보다 나을 듯...
아래의 코드는 PL/pgSQL procedure로 전환한 것이다.
drop function cs_create_job(integer);
create function cs_create_job(integer) returns integer as ' declare
v_job_id alias for $1;
a_running_job_count integer;
a_num integer;
// pragma autonomous_transaction;
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
// commit; // free lock
raise exception ''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);
SELECT count(*) into a_num FROM cs_jobs WHERE job_id=v_job_id;
IF NOT FOUND THEN // If nothing was returned in the last query
// This job is not in the table so lets insert it.
insert into cs_jobs(job_id, start_stamp) values(v_job_id, sysdate());
return 1;
ELSE
raise NOTICE ''Job already running.'';(1)
END IF;
return 0;
end;
' language 'plpgsql';
(1) PL/pgSQL에서 어떻게 하면 Notice나 error을 사용할 수 있는지 볼것...
24.5.4. Package
주목: 나는 이 패키지로 그리 많은 일을 하지 않았다. 그러니 아래의 코드에 오류가 있을 수 있다.
Package는 Oracle이 제공하는 PL/SQL문과 함수들을 하나로 묶어 은패(encapsulate)하는 방법을 제공한다. (자바에서 클래스처럼 함수와 오브젝트를 정의 하는 식의....)
그리고 이러한 오브젝트와 함수들은 점 '.'을 이용하여 접근할 수 있다.
아래에 ACS 4(the ArsDigita Community System)에서 사용하는 Oracle Package가 있다.
create or replace package body acs
as
function add_user (
user_id in users.user_id%TYPE default null,
object_type in acs_objects.object_type%TYPE
default 'user',
creation_date in acs_objects.creation_date%TYPE
default sysdate,
creation_user in acs_objects.creation_user%TYPE
default null,
creation_ip in acs_objects.creation_ip%TYPE default null,
...
) return users.user_id%TYPE
is
v_user_id users.user_id%TYPE;
v_rel_id membership_rels.rel_id%TYPE;
begin
v_user_id := acs_user.new (user_id, object_type, creation_date,
creation_user, creation_ip, email,
...
return v_user_id;
end;
end acs;
/
show errors
여기서는 다른 오프젝트를 생성하여 변환을 하는데 일반적인 이름을 갖는 함수를 정의하여 Oracle Package를 대신하였다. 여기서 몇가지 주의해야하는데, PostgreSQL 함수의 기본 매개변수의 부족 같은 것이다. 위의 package는 아래와 같은 방식으로 바뀔수 있다.
CREATE FUNCTION acs__add_user(integer,integer,varchar,datetime,integer,integer,...)
RETURNS integer AS '
DECLARE
user_id ALIAS FOR $1;
object_type ALIAS FOR $2;
creation_date ALIAS FOR $3;
creation_user ALIAS FOR $4;
creation_ip ALIAS FOR $5;
...
v_user_id users.user_id%TYPE;
v_rel_id membership_rels.rel_id%TYPE;
BEGIN
v_user_id := acs_user__new(user_id,object_type,creation_date,creation_user,creation_ip, ...);
...
return v_user_id;
END;
' LANGUAGE 'plpgsql';
24.5.5. 눈여겨 봐야할 기타
24.5.5.1. EXECUTE
PostgreSQL의 EXECUTE는 잘 작동하지만 Section 24.2.5.3에서 설명하였지만 quote_literal(TEXT) 와 quote_string(TEXT)를 사용할 때는 명심해야하는 것이 있다. EXECUTE ''SELECT * from $1'';를 사용하는 것은 앞에서 말한 함수를 사용하지 않으면 제대로 작동하지 않을 것이다.
24.5.5.2. PL/pgSQL 함수의 최적화
PostgreSQL에는 두가지의 함수 최적화 옵션이 있다.
iscachable (매개변수 값이 같으면 함수는 항상 같은 값을 반환한다.) 와 isstrict (매개변수 중에 NULL이 있으면 함수는 항상 NULL을 반환한다.). 좀더 자세한 것은 CREATE FUNCTION 참고서를 보기바란다.
이 최적화를 위한 옵션을 사용하기 위해서, WITH 문을 CREATE FUNCTION문과 함께 다음과 같이 사용한다.
CREATE FUNCTION foo(...) RETURNS integer AS '
...
' LANGUAGE 'plpgsql'
WITH (isstrict, iscachable);
24.5.6. 부록
24.5.6.1. instr 함수 코드
//
// Oracle의 instr 함수와 같은 역할을 하는 함수.
// 사용법: instr(string1,string2,[n],[m]) [] 부분은 옵션.
//
// string2에서 n번째의 문자부터 탐색하여 string1을 찾는다. n 이 음수이면
// 뒤로부터 찾는다. 만일 m이 넘겨지지 않았으면 1로 가정한다.
// (처음부터 찾는다)
//
//
// by Roberto Mello (rmello@fslc.usu.edu)
// modified by Robert Gaszewski (graszew@poland.com)
// Licensed under the GPL v2 or later.
//
DROP FUNCTION instr(varchar,varchar);
CREATE FUNCTION instr(varchar,varchar) RETURNS integer AS '
DECLARE
pos integer;
BEGIN
pos:= instr($1,$2,1);
RETURN pos;
END;
' language 'plpgsql';
DROP FUNCTION instr(varchar,varchar,integer);
CREATE FUNCTION instr(varchar,varchar,integer) RETURNS integer AS '
DECLARE
string ALIAS FOR $1;
string_to_search ALIAS FOR $2;
beg_index ALIAS FOR $3;
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';
//
// Written by Robert Gaszewski (graszew@poland.com)
// Licensed under the GPL v2 or later.
//
DROP FUNCTION instr(varchar,varchar,integer,integer);
CREATE FUNCTION instr(varchar,varchar,integer,integer) RETURNS integer AS '
DECLARE
string ALIAS FOR $1;
string_to_search ALIAS FOR $2;
beg_index ALIAS FOR $3;
occur_index ALIAS FOR $4;
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';
////////////////////////////////////////////////////////////////////////////
휴.... 여기 까지 입니다. 바로 앞글에서 보시다시피 잠시 옆길로 세서 테스트 프로그램 까지 만들어 보았습니다.
번역해보니 Oracle의 PL/SQL보다는 기능이 좀 부족하긴 하지만 그래도 독립적인 함수를 만들어 기능을 제공할 수가 있네요. 또한 단순히 PL/pgSQL만이 아니고 PL/TCL, PL/PERL 같은 언어도 쓸수가 있으니....
어쨌거나 도움이 되었기를 바라며,
그럼 즐겁게 DB를 디비십시요.
반응형