ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > ​[20221130]PLSQL的变量作用范围(linux).txt

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

原创 Oracle 作者：lfree 时间：2022-12-02 09:48:33 0 删除 编辑

[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2926305/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： [20221128]再谈防水墙(视图访问性能问题).txt
下一篇： [20221128]dg数据库优化问题.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4006
  • 访问量
    7969886

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593