Oracle PL/SQL:如何在长程序包中查找未使用的变量?

问题描述

请假设您有一个包含约200,000行代码的Oracle PL/SQL软件包.

是否有任何快速的方法来检测已声明但未在程序包中使用的变量?

在此先感谢您的帮助.

编辑(2014年4月7日):我正在使用Oracle 10G.

我正在寻找一个纯粹的PL/SQL解决方案.

解决方案

以下内容仅适用于11g R2.看起来 PL/Scope已在11g R1中提供.

通过PLSQL_WARNINGS='ENABLE:ALL'您将不会获得有关未使用变量的信息:

SQL> !cat test.sql
set serveroutput on

alter session set plsql_warnings = 'ENABLE:ALL';

create or replace procedure foo is
  v_a number;
  v_b varchar2(10);
begin
  dbms_output.put_line('hello world!');
end;
/
show errors

exec foo

SQL> @test

Session altered.


SP2-0804: Procedure created with compilation warnings

Errors for PROCEDURE FOO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05018: unit FOO omitted optional AUTHID clause; default
         value DEFINER used

hello world!

PL/SQL procedure successfully completed.

SQL>

您可以看到，仅报告的警告与未使用的变量完全无关.相反，必须使用 PL/Scope . >

以下示例源于 Oracle 11g –使用PL/Scope生成PL/SQL编译器警告(Java样式):

SQL> alter session set plscope_settings = 'identifiers:all';

Session altered.

SQL> alter procedure foo compile;

SP2-0805: Procedure altered with compilation warnings

SQL> show errors
Errors for PROCEDURE FOO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05018: unit FOO omitted optional AUTHID clause; default
         value DEFINER used

SQL> @plsql-unused-variables.sql
Enter value for name: foo
old  10:   where  object_name = upper('&name')
new  10:   where  object_name = upper('foo')
Enter value for type: procedure
old  11:   and    object_type = upper('&type')
new  11:   and    object_type = upper('procedure')

COMPILER_WARNING
--------------------------------------------------------------------------------
V_B: variable is declared but never used (line 3)
V_A: variable is declared but never used (line 2)

SQL>

脚本plsql-unused-variables.sql只是上述博客文章中的剪切和粘贴.因为我发现它很有用，所以我还在中提供了该脚本位桶.

Please suppose you have an Oracle PL/SQL package of about 200,000 rows of code.

Is there any fast way to detect variables declared, but not used in the package?

Thank you in advance for your kind help.

EDIT (April 7th, 2014): I am using Oracle 10G.

EDIT: I am looking for a pure PL/SQL solution.

解决方案

The following only applies to 11g R2. It looks like PL/Scope has become available in 11g R1.

You won't get information about unused variables with PLSQL_WARNINGS='ENABLE:ALL':

SQL> !cat test.sql
set serveroutput on

alter session set plsql_warnings = 'ENABLE:ALL';

create or replace procedure foo is
  v_a number;
  v_b varchar2(10);
begin
  dbms_output.put_line('hello world!');
end;
/
show errors

exec foo

SQL> @test

Session altered.


SP2-0804: Procedure created with compilation warnings

Errors for PROCEDURE FOO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05018: unit FOO omitted optional AUTHID clause; default
         value DEFINER used

hello world!

PL/SQL procedure successfully completed.

SQL>

As you can see the only reported warning is not related to the unused variables at all. Instead PL/Scope has to be used.

The following example has bee derived from Oracle 11g – Generating PL/SQL Compiler Warnings (Java style) using PL/Scope:

SQL> alter session set plscope_settings = 'identifiers:all';

Session altered.

SQL> alter procedure foo compile;

SP2-0805: Procedure altered with compilation warnings

SQL> show errors
Errors for PROCEDURE FOO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05018: unit FOO omitted optional AUTHID clause; default
         value DEFINER used

SQL> @plsql-unused-variables.sql
Enter value for name: foo
old  10:   where  object_name = upper('&name')
new  10:   where  object_name = upper('foo')
Enter value for type: procedure
old  11:   and    object_type = upper('&type')
new  11:   and    object_type = upper('procedure')

COMPILER_WARNING
--------------------------------------------------------------------------------
V_B: variable is declared but never used (line 3)
V_A: variable is declared but never used (line 2)

SQL>

The script plsql-unused-variables.sql is just a cut and paste from the blog post mentioned above. Because I found it useful I have also made the script available in Bitbucket.

这篇关于Oracle PL/SQL:如何在长程序包中查找未使用的变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！