35.11. ��Oracle�� PL/SQL ��ֲ

���ڽ�����Oracle�� PL/SQL ��PostgreSQL�� PL/pgSQL����֮��IJ�� ϣ���ܶ���Щ��Oracle®��PostgreSQL��ֲӦ�õ�������������

PL/pgSQL�� PL/SQL ����෽�涼�dz����ơ� ����һ�ֿ�ṹ�ģ���ʹ�����������ԣ������Բ��ұ����������б����� ��ֵ��ѭ���������ȶ������ơ� �ڴ�Oracle��PostgreSQL��ֲ��ʱ������סһЩ���飺

35.11.1. ��ֲ����

Example 35-4 ��ʾ����δ� PL/SQL �� PL/pgSQL ��ֲһ���򵥵ĺ�����

Example 35-4. �� PL/SQL �� PL/pgSQL ��ֲһ���򵥵ĺ���

������һ��Oracle PL/SQL������

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��֮�IJ�ͬ��

  • Oracle������ IN��OUT�� �� INOUT �������ݸ����������磬 INOUT ����˼�Ǹò���������һ����ֵ���ҷ�������һ���� PostgreSQLֻ�� "IN" ���������û�������IJ������͵�������

  • �ں���ԭ����� 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;

Example 35-5 ��ʾ�������ֲһ����������һ�������ĺ����ķ������Լ���ʾ����δ����������ݵ����⡣

Example 35-5. ��PL/SQL �� PL/pgSQL ��ֲһ���������������ĺ���

����Ĺ��̴�һ�� SELECT �����ץȡ�����У� Ȼ��Ϊ�����Ч�ʣ����� IF ����еĽ��������һ���޴�ĺ����� ���ر�ע�����α�� FOR ѭ������IJ�ͬ��

���� 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;
    RETURN;
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 35-6 ��ʾ�������ֲһ������ OUT �������ִ�����ĺ�����PostgreSQL ����û�� instr ���������������������������������ƿ����� �� Section 35.11.3 ����һ�� PL/pgSQL �� instr ʵ�֣� ����������������ֲ��ø���Щ��

Example 35-6. �� PL/SQL �� PL/pgSQL ��ֲһ���ִ�������OUT�����Ĺ���

�����OraclePL/SQL �������ڷ���һ�� URL ���ҷ������ɸ�Ԫ�� ��������·���������PL/pgSQL����ֻ�ܷ���һ����ֵ�� һ���ƿ�������޵ķ����ǰѷ���ֵ���ɸ������ͣ������ͣ���

������ 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 TYPE cs_parse_url_result AS (
    v_host VARCHAR,
    v_path VARCHAR,
    v_query VARCHAR
);

CREATE OR REPLACE FUNCTION cs_parse_url(v_url VARCHAR)
RETURNS cs_parse_url_result AS $$
DECLARE
    res cs_parse_url_result;
    a_pos1 INTEGER;
    a_pos2 INTEGER;
BEGIN
    res.v_host := NULL;
    res.v_path := NULL;
    res.v_query := NULL;
    a_pos1 := instr(v_url, '//');

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

    res.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
        res.v_path := substr(v_url, a_pos2);
        RETURN res;
    END IF;

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

�������������ô�ã�

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

Example 35-7 ��ʾ�����һ��ʹ�ø��� Oracle ���е����ԵĹ��̡�

Example 35-7. �� 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 �̺��Ž���������ִ�С� ��������������ⳡ���£����Dz���Ҫ���ˣ���Ϊ LOCK TABLE ��ȡ�������������׳������ʱ���ͷš�
(2)
EXCEPTION WHEN ���ò���һ�� IF ��������

���������ǰ����������ֲ��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)
            -- don't worry if it already exists
    END;

    RETURN;
END;
$$ LANGUAGE plpgsql;

(1)
RAISE ���﷨�� Oracle ������������൱���ԡ�
(2)
PL/pgSQL ��֧�ֵ���������ֺ� Oracle �IJ�ͬ�� PL/pgSQL ���õ�������Ҫ��Ķࣨ���� Appendix A���� Ŀǰ�����������û��������������
�������̺� Oracle �ĵ�Ч����Ҫ�Ĺ����Ͳ���ǣ��� cs_jobs �ϳ��е������������ֵ����õ���������� ͬ������������ߺ����˳�������˵��Ϊ���󣩣�������̵�Ч�������ع�����

35.11.2. ����Ҫע��Ķ���

���ڽ��ͼ����� Oracle PL/SQL ������ PostgreSQL ��ֲ�ļ���������������顣

35.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����ô����Ҫ�����ˡ�

35.11.2.2. EXECUTE

PostgreSQL�汾�� EXECUTE ��ת������ PL/SQL �ģ� ����������סҪ�� Section 35.6.4 �������������� quote_literal(text) �� quote_string(text)�� ����㲻����Щ��������ô�� EXECUTE 'SELECT * FROM $1'; �����Ĺ����Dz�����ת�ġ�

35.11.2.3. �Ż�PL/pgSQL����

PostgreSQL���������������������δ������Ż�ִ�У� "volatility���ױ�ģ�"���ڸ����IJ�����ͬʱ�� �������Ƿ�����ͬ������� strictness���ϸ�ģ�������κβ����� NULL����ô�������� NULL���� �ο� CREATE FUNCTION ���ֲ��ȡϸ�ڡ�

���Ҫʹ����Щ�Ż����ԣ���ô��� CREATE FUNCTION �����ܿ�������������

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

35.11.3. ��¼

���ڰ��� Oracle-���ݵ� instr������������������ֲ���̡�

--
-- ģ�� Oracle ����� instr ����
-- �﷨: instr(string1,string2,[n],[m]) ����� [] ��ʾ��ѡ����
-- 
-- �� string1 �ĵ� n ���ַ���ʼѰ�� string2 �ĵ� m �����֡�
-- ��� n �Ǹ�������Ӻ���ǰ�š����û�д��� m���ٶ�θ 1���ӵ�һ���ַ���ʼ�ң���
-- 
--

CREATE OR REPLACE 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 $$
    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
    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  STRICT IMMUTABLE;