​[20221130]PLSQL的变量作用范围(linux).txt

[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.