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