PRAGMA SERIALLY_REUSABLE 让Oracle 包中的变量不保存上一次运行的值

最新推荐文章于 2021-04-04 08:15:35 发布

原创最新推荐文章于 2021-04-04 08:15:35 发布 · 713 阅读

1 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

91 篇文章

订阅专栏

本文深入探讨了Oracle中包变量的生命周期及其如何在会话间持久化。介绍了PRAGMA SERIALLY_REUSABLE的作用，即确保包状态仅在单一服务器调用期间有效，避免了会话间的变量污染。通过实例展示了如何在包声明中应用此属性，以维护跨事务的数据而不将其存储在数据库中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

首要要明白Oracle Package 变量的生命周期是会话。所以如果你在同一会话运行多次，很有可能变量会使用上一次运行的值。往往这是我们不希望看到。那么Oracle 提供了 PRAGMA SERIALLY_REUSABLE 来解决这个问题。这命令使得Oracle 只保存One call 的值。

Packaged public variables and cursors persist for the duration of a session. They can be shared by all subprograms that execute in the environment. They let you maintain data across transactions without storing it in the database.

PRAGMA SERIALLY_REUSABLE

Marks a package as serially reusable, if its state is needed only for the duration of one call to the server (for example, an OCI call to the server or a server-to-server remote procedure call). For more information, see Oracle Database Application Developer's Guide - Fundamentals.

https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/package_declaration.htm#i35376

使用方法：https://docs.oracle.com/cd/B19306_01/B14251_01/adfns_packages.htm#ADFNS009

This example has both a package specification and package body, which are serially reusable.
CREATE OR REPLACE PACKAGE TEST_pkg IS
   PRAGMA SERIALLY_REUSABLE;
   TYPE Str_table_type IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
   Num     NUMBER        := 10;
   Str     VARCHAR2(200) := 'default-init-str';
   Str_tab STR_TABLE_TYPE;
   
    PROCEDURE Print_pkg;
    PROCEDURE Init_and_print_pkg(N NUMBER, V VARCHAR2);
END TEST_pkg;
CREATE OR REPLACE PACKAGE BODY TEST_pkg IS
   -- the body is required to have the pragma because the
  -- specification of this package has the pragma
  PRAGMA SERIALLY_REUSABLE;
   PROCEDURE Print_pkg IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('num: ' || TEST_pkg.Num);
      DBMS_OUTPUT.PUT_LINE('str: ' || TEST_pkg.Str);
      DBMS_OUTPUT.PUT_LINE('number of table elems: ' || TEST_pkg.Str_tab.Count);
      FOR i IN 1..TEST_pkg.Str_tab.Count LOOP
         DBMS_OUTPUT.PUT_LINE(TEST_pkg.Str_tab(i));
      END LOOP;
   END;
   PROCEDURE Init_and_print_pkg(N NUMBER, V VARCHAR2) IS
   BEGIN
   -- init the package globals
      TEST_pkg.Num := N;
      TEST_pkg.Str := V;
      FOR i IN 1..n LOOP
         TEST_pkg.Str_tab(i) := V || ' ' || i;
   END LOOP;
   -- print the package
   Print_pkg;
   END;
 END TEST_pkg;