​[20221130]PLSQL的變數作用範圍(linux).txt

lfree發表於2022-12-02

[20221130]PLSQL的變數作用範圍(linux).txt

--//我個人很少編寫PL/SQL程式碼,主要原因是我的工作基本不需要寫PL/SQL程式碼.
--//我發現甲方寫的PL/SQL有點不合理,在同一過程裡面呼叫相同的sql語句4次.
--//我看了對方寫的PL/SQL程式碼,想看看看PLSQL的變數作用範圍是否可以減少呼叫次數.

1.環境:
SCOTT@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> GRANT SELECT ON SYS.V_$SESSION TO SCOTT;
Grant succeeded.

2.編寫執行程式碼:
--//直接從原始碼抄一段並且做了改寫測試看看,去掉許多不必要的程式碼.
CREATE OR REPLACE package testgent is

  --get login app name
  function get_app_name return varchar2;
  --get login app module name
  --function get_app_module_name return varchar2;
  --get login app type
  --function get_app_type return varchar2;
  --get app process number
  --function get_app_process return number;
 
  --try log in
  procedure try_login;

end testgent;
/
--//註解了get_app_module_name,get_app_type,get_app_process函式定義在包體的spec部分.

CREATE OR REPLACE package body testgent is

  l_appname      varchar2(128);
  l_module       varchar2(128);
  l_type         varchar2(16);
  l_process      number;
  l_osuser       varchar2(30);
  l_machine      varchar2(64);
  l_SCHEMANAME   varchar2(30);
  l_username     varchar2(30);
  l_service_name varchar2(30);
  l_sid          number;
  l_serial#      number;

  procedure collect_app_info is
  begin
      select upper(nvl(program, 'null')),
             upper(module),
             type,
             decode(nvl(instr(process, ':'), 0),
                    0,
                    nvl(process, 1234),
                    substr(process, 1, instr(process, ':') - 1)),
             osuser,
             machine,
             SCHEMANAME,
             USERNAME,
             SERVICE_NAME,
             sid,
             serial#
        into l_appname,
             l_module,
             l_type,
             l_process,
             l_osuser,
             l_machine,
             l_SCHEMANAME,
             l_username,
             l_service_name,
             l_sid,
             l_serial#
        from sys.v_$session
       where sid = sys_context('userenv', 'sid');
  end;

  --get login app name
  function get_app_name return varchar2 is
  begin
    collect_app_info;
    return l_appname;
  end;

  --get login app module name
  function get_app_module_name return varchar2 is
  begin
    collect_app_info;
    return l_module;
  end;

  function is_type_testgent return varchar2 is
  begin
    return l_type;
  end;
 
  procedure try_login is
    v_app_name       varchar2(128);
    begin
        DBMS_OUTPUT.put_line ('typex='||is_type_testgent);
        v_app_name  := get_app_name;
        DBMS_OUTPUT.put_line ('module='||l_module);
        DBMS_OUTPUT.put_line ('process='||l_process);
        DBMS_OUTPUT.put_line ('appname='||l_appname);
        DBMS_OUTPUT.put_line ('type='||l_type);
        DBMS_OUTPUT.put_line ('module='||get_app_module_name);
        DBMS_OUTPUT.put_line ('moduleX='||l_module);
        DBMS_OUTPUT.put_line ('processX='||l_process);
        DBMS_OUTPUT.put_line ('appnameX='||l_appname);
        DBMS_OUTPUT.put_line ('typex='||is_type_testgent);
    end;

begin
  -- Initialization
  null;
end testgent;
/

--//簡單說明:過程collect_app_info 定義並沒有出現在包testgent的spec部分.
--//函式get_app_module_name 定義沒有出現在包testgent的spec部分.
--//而原始的程式get_app_module_name 定義出現在包testgent的spec部分,我這裡僅僅為了測試需要註解了spec處的程式碼.
--//變數l_process,l_module,l_appname 定義在包testgent的body部分.
--//我自己增加is_type_testgent函式,看看返回type是否正確,注意這樣寫不是很嚴謹,僅僅為了測試需要.

--//經常需要執行如下語句,寫成文字/tmp/a.txt:
$ cat /tmp/a.txt
SELECT UPPER(NVL(PROGRAM, 'null'))
, UPPER(MODULE)
, TYPE
, DECODE(NVL(INSTR(PROCESS, ':'), 0), 0, NVL(PROCESS, 1234), SUBSTR(PROCESS, 1, INSTR(PROCESS, ':') - 1)) aa
, OSUSER
, MACHINE
, SCHEMANAME
, USERNAME
, SERVICE_NAME
, SID
, SERIAL#
FROM V$SESSION
WHERE
SID = SYS_CONTEXT('userenv', 'sid');

3.測試:
SCOTT@book> SET SERVEROUTPUT On
SCOTT@book> select testgent.get_app_name c30 from dual ;
C30
------------------------------
SQLPLUS@XXXXXX4 (TNS V1-V3)
--//呼叫函式testgent.get_app_name沒有問題.

SCOTT@book> select testgent.get_app_module_name c20 from dual ;
select testgent.get_app_module_name c20 from dual
       *
ERROR at line 1:
ORA-00904: "TESTGENT"."GET_APP_MODULE_NAME": invalid identifier
--//而函式get_app_module_name 僅僅定義在包 testgent的body部分,僅僅在包體內部有效.報錯正常!!

SCOTT@book> exec testgent.try_login()
typex=USER
module=SQL*PLUS
process=57496
appname=SQLPLUS@XXXXXX4 (TNS V1-V3)
type=USER
module=SQL*PLUS
moduleX=SQL*PLUS
processX=57496
appnameX=SQLPLUS@XXXXXX4 (TNS V1-V3)
typex=USER
PL/SQL procedure successfully completed.
--//注意下劃線已經有值,開始這裡我非常困惑,僅僅因為我前面呼叫了函式testgent.get_app_name,就已經實現了l_type的賦值.

SCOTT@book> @ /tmp/a.txt
...
SCOTT@book> @ pr
==============================
UPPER(NVL(PROGRAM,'NULL'))    : SQLPLUS@XXXXXX4 (TNS V1-V3)
UPPER(MODULE)                 : SQL*PLUS
TYPE                          : USER
AA                            : 57496
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
OSUSER                        : oracle
MACHINE                       : XXXXXX4
SCHEMANAME                    : SCOTT
USERNAME                      : SCOTT
SERVICE_NAME                  : SYS$USERS
SID                           : 53
SERIAL#                       : 3079
PL/SQL procedure successfully completed.
--//可以看出呼叫testgent.try_login() ,執行v_app_name  := get_app_name賦值時,l_*的變數也獲得了對應值.

--//而對方的原始程式碼存在如下執行如下:
v_app_name  := standardization_app_name(v_app_name, get_app_module_name, get_app_process);

--//完全可以修改如下傳參,完全不需要呼叫函式get_app_module_name,get_app_process.
v_app_name  := standardization_app_name(v_app_name, l_module, l_process);

--//對方程式碼的執行結果導致執行1次 testgent.try_login(),呼叫collect_app_info裡面的sql語句執行4次.而且寫的還有問題.
--//可以看出對方的程式碼缺乏嚴格的測試.

4.繼續測試:
--//如果退出執行如下,測試1:
SCOTT@book> SET SERVEROUTPUT On
SCOTT@book> exec testgent.try_login()
typex=
~~~~~~~~~~
module=SQL*PLUS
process=57507
appname=SQLPLUS@XXXXXX4 (TNS V1-V3)
type=USER
module=SQL*PLUS
moduleX=SQL*PLUS
processX=57507
appnameX=SQLPLUS@XXXXXX4 (TNS V1-V3)
typex=USER

PL/SQL procedure successfully completed.
--//注意下劃線開始l_type並沒有賦值.

--//如果退出執行如下,測試2:
SCOTT@book> SET SERVEROUTPUT On
SCOTT@book> select testgent.get_app_name c30 from dual ;
C30
------------------------------
SQLPLUS@XXXXXX4 (TNS V1-V3)

SCOTT@book> exec testgent.try_login()
typex=USER
~~~~~~~~~~~
module=SQL*PLUS
process=57515
appname=SQLPLUS@XXXXXX4 (TNS V1-V3)
type=USER
module=SQL*PLUS
moduleX=SQL*PLUS
processX=57515
appnameX=SQLPLUS@XXXXXX4 (TNS V1-V3)
typex=USER

PL/SQL procedure successfully completed.
--//注意下劃線開始l_type已經賦值,因為前面呼叫函式testgent.get_app_name.

--//如果退出執行如下,測試3,中間改變module看看:
SCOTT@book> SET SERVEROUTPUT On
SCOTT@book> exec testgent.try_login()
typex=
module=SQL*PLUS
process=57521
appname=SQLPLUS@XXXXXX4 (TNS V1-V3)
type=USER
module=SQL*PLUS
moduleX=SQL*PLUS
processX=57521
appnameX=SQLPLUS@XXXXXX4 (TNS V1-V3)
typex=USER

PL/SQL procedure successfully completed.

SCOTT@book> set appinfo testZZZZZ
SCOTT@book> exec testgent.try_login()
typex=USER
module=TESTZZZZZ
process=57521
appname=SQLPLUS@XXXXXX4 (TNS V1-V3)
type=USER
module=TESTZZZZZ
moduleX=TESTZZZZZ
processX=57521
appnameX=SQLPLUS@XXXXXX4 (TNS V1-V3)
typex=USER

PL/SQL procedure successfully completed.

SCOTT@book> set appinfo testPPPP
SCOTT@book> exec testgent.try_login()
typex=USER
module=TESTPPPP
process=57521
appname=SQLPLUS@XXXXXX4 (TNS V1-V3)
type=USER
module=TESTPPPP
moduleX=TESTPPPP
processX=57521
appnameX=SQLPLUS@XXXXXX4 (TNS V1-V3)
typex=USER
PL/SQL procedure successfully completed.



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2926305/,如需轉載,請註明出處,否則將追究法律責任。

相關文章