使用REF_CURSOR转换Oracle存储过程并将程序包全局变量打包为Postgresql或MySQL [英] Convert Oracle stored procedure using REF_CURSOR and package global variable to Postgresql or MySQL
问题描述
此软件包使用Oracle的两个独特功能REF_CURSOR和软件包全局变量.我想将功能从Oracle移植到Postgresql或MySQL.
This package uses two unique features of Oracle, REF_CURSOR and a package global variable. I would like to port the functionality from Oracle to Postgresql or MySQL.
PACKAGE tox IS
/*=======================*/
g_spool_key spool.key%TYPE := NULL;
TYPE t_spool IS REF CURSOR RETURN spool%ROWTYPE;
/*=======================*/
PROCEDURE begin_spool;
/*=======================*/
PROCEDURE into_spool
(
in_txt IN spool.txt%TYPE
);
/*=======================*/
PROCEDURE reset_spool;
/*=======================*/
FUNCTION end_spool
RETURN t_spool;
/*=======================*/
FUNCTION timestamp
RETURN VARCHAR2;
/*=======================*/
END tox;
PACKAGE BODY tox
IS
/*========================================================================*/
PROCEDURE begin_spool
AS
/*=======================*/
BEGIN
/*=======================*/
SELECT
key.NEXTVAL
INTO
g_spool_key
FROM
DUAL;
/*=======================*/
END begin_spool;
/*========================================================================*/
PROCEDURE into_spool
(
in_txt IN spool.txt%TYPE
)
AS
/*=======================*/
BEGIN
/*=======================*/
INSERT INTO
spool
VALUES
(
g_spool_key,
in_txt,
seq.NEXTVAL
);
/*=======================*/
END into_spool;
/*========================================================================*/
PROCEDURE reset_spool
AS
/*=======================*/
BEGIN
/*=======================*/
DELETE
spool
WHERE
key = g_spool_key;
COMMIT;
begin_spool;
/*=======================*/
END reset_spool;
/*========================================================================*/
FUNCTION end_spool
RETURN t_spool
AS
v_spool t_spool;
/*=======================*/
BEGIN
/*=======================*/
COMMIT;
OPEN v_spool FOR
SELECT
*
FROM
spool
WHERE
key = g_spool_key
ORDER BY
seq;
RETURN v_spool;
/*=======================*/
END end_spool;
/*========================================================================*/
FUNCTION timestamp
RETURN VARCHAR2
AS
/*-----------------------*/
v_result VARCHAR2(14);
/*=======================*/
BEGIN
/*=======================*/
SELECT
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
INTO
v_result
FROM
DUAL;
RETURN v_result;
/*=======================*/
END timestamp;
/*========================================================================*/
END tox;
您可以产生等效的代码吗?用于Postgresql?适用于MySQL?
注意:Oracle代码是线程安全的.这是关键功能.
Note: The Oracle code is thread safe. This is a key feature.
推荐答案
PostgreSQL 8.3
PostgreSQL中的问题是缺少全局(或程序包)变量,因此必须使用首先创建的临时表来解决该问题.其余的一切都很简单.
The problem in PostgreSQL is the lack of global (or package) variables, so that part has to be solved with a temp-table that is created first. The rest of it was quite easy.
如果您打算将应用程序移植到PostgreSQL或MySQL上,我建议您不要使用全局变量,因为在编码时,它们是不好的做法(至少根据我的看法:))
If you are serious about porting the application over to PostgreSQL or MySQL, I would recommend you to not use global variables at all since they are bad practice when coding (according to me at least :))
但是无论如何,这是代码:
But anyway, here is the code:
必须在运行功能之前存在该
create table spool (key integer, txt varchar(2048), seq integer);
create sequence s_key;
create sequence s_seq;
create schema tox;
create temp table globals (name varchar(10), value varchar(100), primary key(name));
将这些函数放入模式tox中以模拟程序包.
create or replace function tox.get_variable(var_name varchar) returns varchar as $$
declare
ret_val varchar(100);
begin
select value into ret_val from globals where name = var_name;
return ret_val;
end
$$ language plpgsql;
create or replace function tox.set_variable(var_name varchar, value anyelement) returns void as $$
begin
delete from globals where name = var_name;
insert into globals values(var_name, value);
end;
$$ language plpgsql;
create or replace function tox.begin_spool() returns integer as $$
begin
perform tox.set_variable('key', nextval('s_key')::varchar);
return tox.get_variable('key');
end;
$$ language plpgsql;
create or replace function tox.reset_spool() returns integer as $$
begin
delete from spool where key = tox.get_variable('key')::integer;
return tox.begin_spool();
end;
$$ language plpgsql;
create or replace function tox.into_spool(in_txt spool.txt%TYPE) returns void as $$
begin
insert into spool values(tox.get_variable('key')::integer, in_txt, nextval('s_seq'));
end;
$$ language plpgsql;
create or replace function tox.end_spool(refcursor) returns refcursor as $$
declare
begin
open $1 for select * from spool where key = tox.get_variable('key')::integer order by seq;
return $1;
end;
$$ language plpgsql;
create or replace function tox.test(txt varchar(100)) returns setof spool as $$
declare
v_spool_key integer;
cnt integer;
begin
v_spool_key = tox.begin_spool();
for cnt in 1..10 loop
perform tox.into_spool(txt || cnt);
end loop;
perform tox.end_spool('spool_cursor');
return query fetch all from spool_cursor;
end;
$$ language plpgsql;
要进行测试,只需在创建所有内容后运行它即可.
To test, just run this after everything have been created.
select * from tox.test('Test');
这篇关于使用REF_CURSOR转换Oracle存储过程并将程序包全局变量打包为Postgresql或MySQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!