PostgreSQL 8.0.0 �����ĵ���PostgreSQL �й� ������ | ||||
---|---|---|---|---|
Prev | Fast Backward | Chapter 35. PL/pgSQL - SQL �������� | Fast Forward | Next |
���ڽ�����Oracle�� PL/SQL ��PostgreSQL�� PL/pgSQL����֮��IJ�� ϣ���ܶ���Щ��Oracle®��PostgreSQL��ֲӦ�õ�������������
PL/pgSQL�� PL/SQL �����涼�dz����ơ� ����һ�ֿ�ṹ�ģ���ʹ�����������ԣ������Բ��ұ����������б����� ��ֵ��ѭ���������ȶ������ơ� �ڴ�Oracle��PostgreSQL��ֲ��ʱ������סһЩ���飺
��PostgreSQL�в���û��ȱʡֵ��
�������PostgreSQL�����غ����� ������Գ������ƿ�ȱ��ȱʡ���������⡣
��PL/pgSQL�ﲻ��Ҫ�α꣬ ֻҪ���������FOR�����Ϳ����ˣ���Example 35-5����
��PostgreSQL������Ҫʹ����Ԫ����Χ�������ݺ���������ĵ����š� �� Section 35.2.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� ���Ƕ���������ر����Ȥ����Ϊ�����Խ�����һЩ������
���������ǰ����������ֲ��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;
���ڽ��ͼ����� Oracle PL/SQL ������ PostgreSQL ��ֲ�ļ���������������顣
�� 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����ô����Ҫ�����ˡ�
PostgreSQL�汾�� EXECUTE ��ת������ PL/SQL �ģ� ����������סҪ�� Section 35.6.4 �������������� quote_literal(text) �� quote_string(text)�� ����㲻����Щ��������ô�� EXECUTE 'SELECT * FROM $1'; �����Ĺ����Dz�����ת�ġ�
PostgreSQL���������������������δ������Ż�ִ�У� "volatility���ױ�ģ�"���ڸ����IJ�����ͬʱ�� �������Ƿ�����ͬ������� strictness���ϸ�ģ�������κβ����� 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 ���ַ���ʼѰ�� 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;