使用 REF_CURSOR 转换 Oracle 存储过程并将全局变量打包到 Postgresql 或 MySQL

Question

该包使用了 Oracle 的两个独特功能：REF_CURSOR 和包全局变量。 我想将功能从 Oracle 移植到 Postgresql 或 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 代码是线程安全的。 这是一个关键特征。

Answer 1

PostgreSQL 8.3

PostgreSQL 中的问题是缺少全局（或包）变量，因此必须通过首先创建的临时表来解决该部分。 剩下的事情就很简单了。

如果您真的想将应用程序移植到 PostgreSQL 或 MySQL，我建议您根本不要使用全局变量，因为它们在编码时是不好的做法（至少对我来说是:)）

但无论如何，这里是代码：

< em>这必须在运行函数之前存在：

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

函数被放入模式毒物中以模拟包。

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;

要进行测试，只需在创建所有内容后运行它即可。

select * from tox.test('Test');

Answer 2

对于 mysql：

1. 对于 ref_cursor，您可以在过程中使用常规选择。 Mysql 有一个隐式结果集，如果您发出 select 语句，该结果集会从存储过程返回。 查看我的答案这里。
2. 对于包全局变量，你可以把它放在一个表中，但从你的代码来看它是一个序列，所以它可以用 auto_increment 字段替换。 那应该很简单。

如果您可以在问题中发布假脱机表的定义，将会有所帮助。 然后我也许可以为您提供 mysql 的确切代码。

Answer 3

我很难理解您代码中的一些内容。 看起来您有一个包含两个序列的表，但其中只有一个是真正的 auto_increment 列。

在 mysql 中，仅允许表中的一列使用 auto_increment。 您是否考虑过将另一列作为另一个表的自动递增列的外键？

全局变量很棘手，因为 mysql 没有它们。 我认为唯一的解决方案是将其作为标量存储在表中，然后使用外键将数据与其绑定。

最后，正如我在之前的回答中指出的那样，返回引用光标很容易。 在提供的链接（针对不同的答案）中，您可以看到代码示例。

Answer 4

这是使用 MySQL 5.1.30 测试的解决方案。

关于您对线程安全的要求，MySQL 用户变量 机制应该有所帮助。 这允许您 SET 其状态仅限于当前会话的变量。 其他会话也可以创建同名的变量，并在其中保留不同的值。

我认为线程安全是指这样的东西——会话范围的状态。 因为数据库中确实无法拥有更细粒度的线程安全状态。 应用程序的每个线程都必须有自己的数据库会话。

MySQL 中没有包，因此 user 变量对于会话来说是全局的。 另一个存储过程碰巧使用同名变量将会发生冲突。

CREATE TABLE spool (
  `key` INT,
  txt   VARCHAR(2048),
  seq   INT AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE spool_key (
  `key` INT AUTO_INCREMENT PRIMARY KEY
);

DELIMITER $
CREATE PROCEDURE begin_spool ()
BEGIN
  DELETE FROM spool_key;
  INSERT INTO spool_key (`key`) VALUES (DEFAULT);
  SET @sp_key = LAST_INSERT_ID();
END $

CREATE PROCEDURE into_spool(IN in_txt VARCHAR(2048))
BEGIN
  INSERT INTO spool (`key`, txt, seq) VALUES
    (@sp_key, in_txt, DEFAULT);
END $

CREATE PROCEDURE reset_spool()
BEGIN
  DELETE spool FROM spool JOIN spool_key USING (`key`);
  CALL begin_spool();
END $

CREATE PROCEDURE end_spool()
BEGIN
  SELECT *
  FROM spool JOIN spool_key USING (`key`)
  ORDER BY seq;
END $
DELIMITER ;

CALL begin_spool();
CALL into_spool('now is the time');
CALL into_spool('for all good men');
CALL end_spool();
CALL reset_spool();
CALL into_spool('to come to the aid');
CALL into_spool('of their country');
CALL end_spool();

DROP FUNCTION IF EXISTS fmt_timestamp;
CREATE FUNCTION fmt_timestamp() RETURNS CHAR(14)
RETURN DATE_FORMAT(SYSDATE(), '%Y%m%d%H%i%s');

SELECT fmt_timestamp();