Oracle 11g PL/SQL Positions of CONTANT variables i

2019-07-21 00:34发布

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

Let's look at this:

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;

1条回答
劫难
2楼-- · 2019-07-21 00:47

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.

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.

查看更多
登录 后发表回答