迁移 Oracle 到 PostgreSQL: PRAGMA SERIALLY_REUSABLE

CREATE OR REPLACE PACKAGE test_pkg_var
IS
  PRAGMA SERIALLY_REUSABLE;
  PROCEDURE function_1(test_id number);
  PROCEDURE function_2(test_id number);
END;

CREATE OR REPLACE PACKAGE BODY test_pkg_var
IS
  PRAGMA SERIALLY_REUSABLE;
  v_char VARCHAR2(20) := 'shared.airline';
  v_num number := 123;

  PROCEDURE function_1(test_id number)
  IS
  BEGIN
    dbms_output.put_line( 'v_char-'|| v_char);
    dbms_output.put_line( 'v_num-'||v_num);
    v_char:='test1';
    function_2(0);
  END;

  PROCEDURE function_2(test_id number)
  IS
  BEGIN
    dbms_output.put_line( 'v_char-'|| v_char);
    dbms_output.put_line( 'v_num-'||v_num);
  END;
END test_pkg_var;

SET serveroutput ON
EXEC test_pkg_var.function_1(1);
EXEC test_pkg_var.function_2(1);

CREATE SCHEMA test_pkg_var;

CREATE OR REPLACE FUNCTION test_pkg_var.init(pg_serialize IN INTEGER DEFAULT 0)
RETURNS void
AS $BODY$
DECLARE

BEGIN
  IF oracle_ext.is_package_initialized( 'test_pkg_var' ) AND pg_serialize = 0
  THEN
    RETURN;
  END IF;

  PERFORM oracle_ext.set_package_initialized( 'test_pkg_var' );
  PERFORM oracle_ext.set_package_variable( 'test_pkg_var', 'v_char', 'shared.airline.basecurrency'::CHARACTER VARYING(100));
  PERFORM oracle_ext.set_package_variable('test_pkg_var', 'v_num', 123::integer);
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test_pkg_var.function_1(pg_serialize int default 1)
RETURNS void
AS $BODY$
DECLARE

BEGIN
  PERFORM test_pkg_var.init(pg_serialize);
  RAISE NOTICE 'v_char%',oracle_ext.get_package_variable( 'test_pkg_var', 'v_char');
  RAISE NOTICE 'v_num%',oracle_ext.get_package_variable( 'test_pkg_var', 'v_num');
  PERFORM oracle_ext.set_package_variable( 'test_pkg_var', 'v_char', 'test1'::varchar);
  PERFORM test_pkg_var.function_2(0);
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test_pkg_var.function_2(IN pg_serialize integer default 1)
RETURNS void
AS $BODY$
DECLARE

BEGIN
  PERFORM test_pkg_var.init(pg_serialize);
  RAISE NOTICE 'v_char%',oracle_ext.get_package_variable( 'test_pkg_var', 'v_char');
  RAISE NOTICE 'v_num%',oracle_ext.get_package_variable( 'test_pkg_var', 'v_num');
END;
$BODY$
LANGUAGE plpgsql;

select test_pkg_var.function_1();
select test_pkg_var.function_2();