包中CONTANT变量的Oracle 11g PL/SQL位置 [英] Oracle 11g PL/SQL Positions of CONTANT variables in PACKAGE

问题描述

我有严格的优化问题.当多次调用过程/函数时，我应该在包中的什么地方放置CONSTANT变量?

I have strictly optimization problem. where in my PACKAGE I should place CONSTANT variables when procedure/function is being called many times ?

让我们看看这个:

CREATE OR REPLACE PACKAGE WB_TEST IS
  PROCEDURE TEST;
END WB_TEST;

CREATE OR REPLACE PACKAGE BODY WB_TEST IS
  FUNCTION PARSER(IN_PARAM IN VARCHAR2) RETURN VARCHAR2 IS
    LC_MSG   CONSTANT VARCHAR2(80) := 'Hello USERNAME! How are you today?';
    LC_PARAM CONSTANT VARCHAR2(10) := 'USERNAME';
  BEGIN
    RETURN REPLACE(LC_MSG, LC_PARAM, IN_PARAM);
  END PARSER;

  PROCEDURE TEST IS
  BEGIN
    FOR I IN 1 .. 1000 LOOP
      DBMS_OUTPUT.PUT_LINE(PARSER(TO_CHAR(I)));
    END LOOP;
  END TEST;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
END WB_TEST;
/

或者最好做这样的事情:

Or is better to do something like that:

CREATE OR REPLACE PACKAGE WB_TEST IS
  PROCEDURE TEST;
END WB_TEST;

CREATE OR REPLACE PACKAGE BODY WB_TEST IS
  GC_MSG   CONSTANT VARCHAR2(80) := 'Hello USERNAME! How are you today?';
  GC_PARAM CONSTANT VARCHAR2(10) := 'USERNAME';

  FUNCTION PARSER(IN_PARAM IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN REPLACE(GC_MSG, GC_PARAM, IN_PARAM);
  END PARSER;

  PROCEDURE TEST IS
  BEGIN
    FOR I IN 1 .. 1000 LOOP
      DBMS_OUTPUT.PUT_LINE(PARSER(TO_CHAR(I)));
    END LOOP;
  END TEST;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
END WB_TEST;

推荐答案

从性能的角度来看，这几乎是不可能的.在两种情况下，PL/SQL编译器生成的代码应该完全相同-常量几乎可以肯定在引用它们的位置进行内联编译.

It is extremely unlikely to matter from a performance standpoint. The code the PL/SQL compiler generates should be identical in both cases-- the constants will almost certainly get compiled inline where they are referenced.

与其他代码相比，首选的唯一原因是代码清晰和变量作用域.如果常量确实是PARSER函数的局部常量-如果它们不太可能对包中的其他方法有用，则应将其声明为该函数的一部分.另一方面，如果它们可能对包中的其他方法有用，则应将它们声明为包主体的一部分.如果它们可能对包外的方法有用，则应将它们声明为包规范的一部分.

The only reason to prefer one over the other would be code clarity and variable scoping. If the constants are really local to the PARSER function-- if they aren't likely to be useful to other methods in the package, they ought to be declared as part of the function. If, on the other hand, they are likely to be useful to other methods in the package, they ought to be declared as part of the package body. If they are likely to be useful to methods outside the package, they ought to be declared as part of the package specification.

这篇关于包中CONTANT变量的Oracle 11g PL/SQL位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！