����Ǥ�Oracle����PostgreSQL�إ��ץꥱ��������ܿ����볫ȯ�Ԥμ�����Ȥʤ�褦�ˡ� PostgreSQL��PL/pgSQL�����Oracle��PL/SQL����ΰ㤤�ˤĤ����������ޤ���
PL/pgSQL��¿��������PL/SQL�˻��Ƥ��ޤ��� ����ϥ֥��å��ǹ�������Ƥ��ơ����ʤʸ���Ǥ��ꡢ���Ƥ��ѿ����������ʤ���Фʤ�ʤ����Ǥ��� ������롼�ס����ʬ����Ʊ�ͤǤ��� PL/SQL����PL/pgSQL�˰ܿ�����ݤ����դ��ʤ���Фʤ�ʤ�����ʰ㤤��ʲ��˼����ޤ���
PostgreSQL�Υѥ����ˤϥǥե�����ͤ�����ޤ���
PostgreSQL�Ǥϴؿ����С������ɤ��뤳�Ȥ��Ǥ��ޤ��� ����Ϥ��Ф��Хǥե���ȥѥ����η�ǡ���䤦����˻Ȥ��ޤ���
�ؿ���ǻ��Ȥ��줿���Ʊ��̾���Υѥ�������ѤǤ��ޤ��� Oracle�Ǥϡ�function_name.paramater_name�Τ褦�˴ؿ�̾���Ѥ��ƥѥ���̾����ꤹ��С�Ʊ��̾������ѤǤ��ޤ��� ��������PL/pgSQL�Ǥϡ���̾�ȥơ��֥�̾����ꤹ�뤳�Ȥˤ����ͤ����Ǥ��ޤ���
PL/pgSQL�Ǥϥ��������ɬ�פ���ޤ��� FORʸ����䤤��碌���֤������Ǥ� ����37-6�Ȥ��Ƥ��������ˡ�
PostgreSQL �δؿ����Τ�ʸ�����ƥ��Ȥ��ƽʤ���Фʤ�ޤ��� �������äơ��ؿ����������ǥɥ���������Ѥ��뤫��ñ�������������פ���ɬ�פ�����ޤ��� ��37.2.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�ˤʤ�ޤ��� 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;
��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_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���������ޤ�����������ʥ����ɤ��������ʤ�����Ǥ���
��37-7�ϡ�OUT�ѥ����������ʸ��������Ԥ��ؿ��ΰܿ���ˡ���ޤ���
PostgreSQL�ˤ��Ȥ߹��ߤ�instr
�ؿ��Ϥ���ޤ���¾�δؿ����Ȥ߹�碌�뤳�ȤǺ����Ǥ��ޤ���
��37.11.3�ˡ��ܿ����ά���Ǥ���褦��instr
��PL/pgSQL�ˤ��������ޤ���
�� 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; / show errors;
�����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');
��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; -- ���å������(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�ؿ��˴�ñ���Ѵ����뤳�Ȥ��Ǥ��ޤ��� �ʲ��Τ褦�ʤ��Ȥ��Ƥ���뤳�Ȥ⤢�äơ����Υץ�����������äˤ��⤷������ΤǤ���
����Ǥϡ����Υץ����������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;
���Υץ����������Oracle�Ǥ���٤Ƶ�ǽŪ�ʰ㤤������ޤ��� �����cs_jobs�ơ��֥����¾Ū���å����ȥ�������ν�λ�ޤǷ�³���뤳�ȤǤ��� Ʊ�ͤˡ��ؿ��ƤӽФ��θ�ǡ��㤨�Х��顼�������ǡ˥��ܡ��Ȥ���ȡ��ץ���������αƶ��ϥ�����Хå�����ޤ���
����Ǥϡ�Oracle PL/SQL�ؿ���PostgreSQL�ؤΰܿ��ˤ����롢����¾�����ջ�����������ޤ���
PL/pgSQL�ˤ�����EXCEPTION�礬�㳰����ª����ȡ�BEGIN�ʹߤΤ��Υ֥��å��ˤ�����ǡ����١������ѹ�����ưŪ�˥�����Хå�����ޤ��� ���ʤ����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����Ѥ����ץ���������λ��ϡ�����˱��������פ�ɬ�פˤʤ�Ȼפ��ޤ���
PL/pgSQL��EXECUTE��PL/SQL�ǤȤ褯���Ƥ��ޤ���
��������37.6.5����������Ƥ���quote_literal
��quote_ident
��Ȥ����Ȥ�Ф��Ƥ����ʤ���Ф����ޤ���
�����δؿ�����Ѥ��ʤ��¤�EXECUTE ''SELECT * from $1'';�Ȥ�����ʸ��ư��ˤϡ�������������ޤ���
PostgreSQL�ˤϼ¹Ԥ��Ŭ�����뤿���2�Ĥδؿ����������Ҥ�����ޤ��� ��ư����Ʊ��������Ϳ����줿�����Ʊ����̤��֤��ޤ��ˤ�"��̩��"�ʰ����Τ����줫��NULL���ޤޤ����NULL���֤��ޤ��ˤǤ��� �ܺ٤�CREATE FUNCTION�Ȥ��Ƥ���������
�����κ�Ŭ��°�������Ѥ��뤿��ˤϡ�CREATE FUNCTIONʸ��ʲ��Τ褦�ˤ��ޤ���
CREATE FUNCTION foo(...) RETURNS integer AS $$ ... $$ LANGUAGE plpgsql STRICT IMMUTABLE;
����ˤϡ��ܿ���Ȥ��ά�����뤿��˻��ѤǤ��롢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;