充分利用 Oracle SQL Developer 报表
来源：互联网 发布：怎样增加淘宝信誉 编辑：程序博客网 时间：2024/04/28 03:36
充分利用 Oracle 的免费数据库开发工具中的报表功能。
2006 年 7 月发布
作者：Lewis R. Cunningham 
[原址:http://www.oracle.com/technology/global/cn/pub/articles/cunningham-sqldev.html]
Oracle SQL Developer（以前称为 Project Raptor）是 Oracle 针对开发人员和 DBA 新推出的多平台图形用户界面 (GUI)。与 Oracle 数据库 10g 快捷版 (XE) 以及 Oracle JDeveloper 一样，可以免费下载 Oracle SQL Developer。Oracle SQL Developer 可在 Windows、Macintosh 和 Linux 平台上运行。
Oracle SQL Developer 为开发人员提供了许多强大特性，包括用于操作数据库对象的图形对话框、SQL 工作表、PL/SQL 编辑器以及一个报表工具。它基于 Oracle JDeveloper Java IDE，具有直观的树状导航结构。
报表工具允许以两种方式创建报表：可以从一大组预定义的标准数据字典报表中进行选择，也可以创建一个用户自定义的报表。Oracle SQL Developer 报表还支持绑定变量和下钻报表。
安装、配置以及创建到数据库的连接超出了本文的讨论范围。有关这些主题的详细信息，请参阅 OTN 上的 Oracle SQL Developer 主页。
要使用报表，首先启动适合您平台的 Oracle SQL Developer。连接到一个数据库，单击 Reports 选项卡。单击 Reports 符号旁边的加号 (+)，然后单击 Data Dictionary Reports 旁边的加号。暂时先不管 User Defined Reports 项。
树状视图导航在此表现为文件夹和文件。例如，Data Dictionary Reports 目录是 Reports 文件夹的子目录，或者用导航语法表示为 Reports-%26gt;Data Dictionary Reports。
单击 Data Dictionary 文件夹中任意子文件夹旁边的加号。
Oracle 提供的报表
(注：运行某个报表时，可能会看到 Select Connection 对话框。选择您的连接，然后单击 OK。 )
Data Dictionary Reports 文件夹列出了 Oracle 提供的预定义报表集合。我们首先来看看这些标准报表中的一些报表。下面列出的是我发现在工作中很有用的一些报表。浏览这些报表，看看哪些报表可能对您有用。
All Parameters 报表 (Reports-%26gt;Data Dictionary Reports-%26gt;Database Administration-%26gt;Database Parameters-%26gt;All Parameters) 列出了所有的数据库初始化参数。
当您单击某个报表时，该报表自动运行。一些报表具有 Enter Bind Values 对话框，该对话框允许您限制对某些参数的查询。我将在下面详细讨论绑定变量。要运行该报表，选择 Apply。
选择 Free Space 报表 (Reports-%26gt;Data Dictionary Reports-%26gt;Database Administration-%26gt;Storage-%26gt;Free Space) 可查看每个表空间中的可用空间。
Active Sessions 报表 (Reports-%26gt;Data Dictionary Reports-%26gt;Database Administration-%26gt;Sessions-%26gt;Active Sessions) 显示了数据库中的所有活动会话。使用该报表可查看登录的用户及其会话的详细信息。
我发现 Top SQL 节点下的所有报表 (Reports-%26gt;Data Dictionary Reports-%26gt;Database Administration-%26gt;Top SQL-%26gt;*) 都很有价值。我喜欢对系统中进行的操作有一个整体了解，按 CPU 或 IO 查看 Top SQL 就是实现此目的的一个不错方法。
当我记不住确切的表名或列名时，User Tables 报表 (Reports-%26gt;Data Dictionary Reports-%26gt;Table-%26gt;User Tables) 就是个不错的帮手。
我发现 Quality Assurance 报表 (Reports-%26gt;Data Dictionary Reports-%26gt;Table-%26gt;Quality Assurance-%26gt;*) 在开发过程中很有价值。我倾向于手工编写大量的 DDL 代码，很可能会丢失某个索引或键。
使用 PL/SQL 搜索报表 (Reports-%26gt;Data Dictionary Reports-%26gt;PL/SQL-%26gt;Search Source Code) 可以节省大量时间。可以根据对象名称或源文本字符串进行搜索。
我们来看看该报表背后的 SQL 代码。在需要的时候运行该报表。在 Reports 窗格上，报表数据上方有三个按钮：一个红色的图钉、一个绿色的三角形，以及矩形中的文本 SQL。最后一个按钮从报表中复制 SQL，将其放在一个 SQL 工作表中。单击 SQL 按钮。
查看所提供的 SQL 是一个学习一些 SQL 技巧的好方法。其中有些技巧将帮助您创建自己的用户定义报表。
下钻报表
Oracle SQL Developer 报表的一个重要特性是下钻功能。这是我最喜欢的 Oracle SQL Developer 特性之一。我稍后将在本文中将向您介绍如编写自己的下钻报表。
打开 Reports->Data Dictionary Reports->Table->User Tables 报表，它列出您模式中的所有表。运行了该报表后，双击结果集网格中的任一行。
该操作将打开一个包含表的说明的新选项卡。如果您遵循了以上操作，现在就会看到该表的列定义。在数据窗格中，您将有两个选项卡：一个针对所运行的报表，另一个针对下钻对象。打开另一个报表，双击某一行。
下钻适用于在导航器上（在 Connections 选项卡下）具有对应节点的所有报表。如果您尝试双击 All Parameters 报表，则不会发生任何操作，这是因为 Connections 下面没有 Parameter 节点。
由于下钻功能仅限于 Navigator 节点上的项，因此从某种程度上讲，它仅限于您自己的报表。当使用 Oracle SQL Developer 的 Oracle 开发人员增加了下钻到某处的能力（即替换下钻查询）时，这将是一个真正有用的功能。
直到此时，它才对您碰巧编写的任何字典报表有用。如果您希望向正在编写的报表添加下钻功能，只需在查询中添加三个列即可。下面，我给出了一些示例。
在该图中，sdev_link_owner 是 OBJECT_OWNER，sdev_link_name 是 OBJECT_NAME，sdev_link_type 是 OBJECT_TYPE。
用相同的别名添加这三项，双击时，Oracle SQL Developer 将自动下钻。记住，只有那些在导航器窗格中具有节点的项才能在此时下钻。
简单的用户报表
注：针对下面的大多数示例，我在 Oracle 数据库 XE 安装中使用 SYSTEM 帐户和 HR 帐户。如果您有一个 Oracle 数据库 XE 数据库，按我说的做会更简单些。如果您没有 Oracle 数据库 XE 数据库，仍然可以按我说的做，但是在某些情况下可能必须选择不同的表。可以从 OTN 上免费下载 Oracle 数据库 XE。
现在，您将首先创建一个简单的报表。以 HR@XE 身份登录。单击导航器中的最后一个节点 User Defined Reports。右键单击这个展开的节点，您将看到一个具有以下选项的上下文菜单：Add Folder、Add Report、Delete、Copy、Cut 和 Paste。
Oracle SQL Developer 为导航维护一个树结构；我建议您最好进行相同的操作。使用文件夹对报表按逻辑分组。您可以选择按应用程序对报表进行分组，我对下面图 12 中显示的 Employee Reports 和 User Data Dictionary 应用程序下面的报表就是这样处理的。除了按应用程序名称进行分组外，您可能还希望按其他标准对报表进行分组，但不建议您转储 User Defined Reports 下的所有内容。
选择 Add Folder 新建一个文件夹。现在，我们将该文件夹称作 Employee Reports。输入您希望用作说明的任何内容。如果您将鼠标停留在导航器中的该项上，将显示 ToolTip 文本。单击 Apply 保存。
在 Employee Reports 上单击鼠标右键。现在的上下文菜单与前一个相比多了一个 Edit 项。选择 Edit。进行所需要的任何更改，然后单击 Apply 保存。
再次单击鼠标右键，然后选择 Add Report。将报表命名为“Number of Employees by Department”。在 Description 和 ToolTip 中输入您想要的任意文本。将下面的查询用作报表 SQL：
Select department_id, count(*)From EmployeesGroup By department_id
单击 Apply 保存。
单击 Employee Reports 节点旁边的加号，然后单击新报表。可能会看到 Select Connection 对话框。如果这样，则选择 HR 连接并单击 OK。
现在，您是您第一个用户定义报表的自豪的所有者。尽管该报表很有用，但如果它能列出部门名称而非部门 ID，用处就更大了。要添加该功能，首先在该报表上单击鼠标右键。您将注意到该报表在上下文菜单中具有以下几项：Open、Open New Window、Edit 和 Show Properties。选择 Edit。
更改您的查询，使其包括 Departments 中的部门名称：
Select d.department_name, count(*)From Employees eJoin Departments d On (e.department_id = d.department_id)Group By d.department_name
单击 Apply 保存，然后返回报表。
让我们将这个报表变得更加有趣。在该报表上单击鼠标右键，选择 Copy。如果再次单击鼠标右键，将发现没有了 Paste 选项。右键单击 Employee Reports 节点。现在可以粘贴报表了。右键单击新报表（结尾处有括号包含着数字的报表），选择 Edit。将查询更改为下面的测试：
Select e.Last_Name, e.First_name, d.department_name, count(*) over (partition by d.department_name) dept_count, count(*) over ()From Employees eJoin Departments d On (e.department_id = d.department_id)
运行报表。现在，这很有用。报表中包含一名雇员、一个部门名称、该部门中的雇员数量，以及所有部门中的雇员数量。
创建有用的报表的关键在于能够创建动态报表。如果您使用绑定变量，报表将能针对每次运行动态改变。在 Oracle SQL Developer 1.0 之前的版本中，虽然您可以修改 UserReports.xml 文件（位于 SQL Developer User Information Directory 中）来变相地添加绑定变量，并它并不直接支持使用绑定变量的用户定义报表。使用 1.0 版可以轻松地创建使用绑定变量的报表。
现在对报表进行最后更改。您要添加一个绑定变量，它将允许用户输入一个部门名称或部分名称来选择具体的部门，它也可以为空以选择所有部门。按如下所示修改 SQL：
Select e.Last_Name, e.First_name, d.department_name, count(*) over (partition by d.department_name) dept_count, count(*) over ()From Employees eJoin Departments d On (e.department_id = d.department_id)Where (:dept_name is nullor d.department_name = :dept_name)
运行该报表，然后看您的绑定变量屏幕。试着输入一些值。
现在，我们来应用报表制作技巧，创建一个将对所有开发人员和 DBA 有用的报表。
复杂报表
由于这是一个新的报表类别，因此我们在 User Defined Reports 节点下面新建一个文件夹。该文件夹将包含您自己的 Data Dictionary 报表集，因此我们就将其称为 User Data Dictionary 吧。
您的新报表将列出回收站中的所有对象以及它们所占用的空间。该报表在 Oracle 数据库 XE 中尤为方便，您可以针对您的对象将磁盘空间限制为 4GB。
运行该报表的用户需要能够访问 SYS.DBA_OBJECTS、SYS.DBA_SEGMENTS 和 SYS.DBA_RECYCLEBIN 数据库对象。我通常以 SYSDBA 用户的身份运行该报表。
新建一个用户定义的报表，将其命名为 Recycled Objects。在 Description 和 ToolTip 中输入想要的任意文本。使用该查询：
select ao.owner,ao.object_type,ar.original_name,ao.object_name recycled_name,ar.droptime,ds.bytes/1024/1024 "Megabytes",ao.owner sdev_link_owner,ao.object_name sdev_link_name,ao.object_type sdev_link_typefrom sys.dba_objects ao,sys.dba_segments ds,sys.dba_recyclebin arwhere ao.object_name like 'BIN$%'and ao.owner = ds.ownerand ao.object_name = ds.segment_nameand ar.object_name = ao.object_nameand ((ao.subobject_name is null AND ds.partition_name IS NULL)or ao.subobject_name = ds.partition_name)and ((:original_name is null or ar.original_name = :original_name)AND (:object_type is null orao.object_type = :object_type))
该报表包括 SDEV_LINK_OWNER、SDEV_LINK_NAME 和 SDEV_LINK_TYPE 别名。这意味着允许您下钻来查看所引用的项的结构。对于该查询而言，它将下钻到 RECYCLEBIN 中的项。如果将 SDEV_LINK_NAME 从 ao.object_name 更改为 ar.original_name，您将下钻到已经替换了它的项（如果有的话）。可以对该报表进行修改，以便允许在模式之间切换。
将 SDEV_LINK_NAME 行从
ao.object_name sdev_link_name,
修改为
decode( :original_or_recycle, 1, ar.original_name, ao.object_name) sdev_link_name,
现在，运行该报表时，如果希望下钻到回收的对象，不用进行任何操作即可。另一方面，如果希望下钻到初始对象（如果存在），则将 original_or_recycle 变量更改为 1。
这些技巧使 Oracle SQL Developer 中的下钻功能变得如此聪明。当 Oracle 开发人员将该功能修改为允许通过自定义的查询下钻时，这将非常有用！访问 OTN 上的 Oracle SQL Developer 论坛 (http://forums.oracle.com/forums/forum.jspa?forumID=260)，让 Oracle 知道您需要自定义查询下钻这个特性！
除了更改 original_or_recycle 变量外，还可以用对象类型和/或初始对象名称来限制返回数据集。该查询的最后四行创建了绑定变量。上面详细介绍了绑定变量，因此无需赘述。
在绑定变量对话框中为报表的终端用户提供额外的描述性信息将很有帮助，难道不是吗？如果我想与其他人共享该报表，original_or_recycle 变量确实需要额外的描述性文本。有一种提供该操作的方法。
我已经提到过，在 Oracle SQL Developer 1.0 之前的版本中，编辑 UserReports.xml 文件可以添加绑定变量。您可以在当前版本中执行相同操作来扩展绑定功能。可以添加描述性文本，更改提示，甚至可以指定默认值。下面介绍如何添加描述性文本：
您需要导航到名为 SQL Developer User Information Directory 的 OS 目录。在 MS Windows 中，打开 C:->Documents and Settings 目录。在该目录下，选择要用来登录的用户身份。例如，如果以 lcunning 身份登录，我就会导航到 C:->Documents and Settings->lcunning 目录。在该目录中，导航到 .sqldeveloper 目录。与我的用户 lcunning 保持一致，我现在在 C:->Documents and Settings->lcunning->.sqldeveloper 目录中。
在 Linux 中，您会发现该目录是 .sqldeveloper 主目录下的一个子目录。因此，对于 $HOME 为 /home/lcunning 的用户而言，UserReports.xml 应该位于 /home/lcunning/.sqldeveloper 下。
.sqldeveloper 目录中至少应该有两个文件。其中一个文件就是 UserReports.xml，它包含您的用户定义报表。您可以用最喜欢的 XML 编辑器打开它。
注：编辑 UserReports.xml 文件之前，请进行备份！如果您搞乱了 XML 语法，您还能用备份替换该文件，然后重新开始。
XML 文档的格式如下所示：
<reports><folder><name></name><description></description><report enabled="true"><name></name><description></description><tooltip></tooltip><query><sql></sql></query></report></folder></reports>
您可以从内向外理解这个文件，这是因为 SQL 查询包装在一个报表中，该报表位于一个包含在报表文档中的文件夹中；您还可以从外向内理解这个文件，这是因为报表文档包含一个或多个文件夹（具有一个名称和一个描述），而每个文件夹又包含了一个或多个报表。每个报表都用名称、描述、工具提示和查询定义。一个查询由多条 SQL 语句组成。
query 节点就是进行修改的地方。您将在 <query> 节点中紧跟 <sql> 节点后添加一个 <binds> 节点。
binds 节点应如下所示：
<binds><bind id=""><type></type><prompt></prompt><value></value><tooltip></tooltip></bind></binds>
bind 标记的 id 属性是绑定变量的名称。以这种方式创建绑定变量时，如果您包括了一个绑定变量，就必须包括该报表的所有绑定变量。
如果希望某个绑定变量有一个默认值，可以将该值放在 value 元素中。
进一步的描述放在 ToolTip 中。对于 original_or_recycle、original_name 和 object_type 这三个变量，使用下面的值：
使用 ![CDATA ]] 文本是一种保护自己的方法，因为它告诉 XML 分析器不要尝试解释方括号中的内容。如果您不熟悉 XML，应该努力总是包括该文本。
修改描述性文本和默认值有很大的价值，因此我打赌 Oracle 的 SQL Developer 团队过一段时间将对此进行改进。现在已经有了定制的能力；只不过还没有接口。
修改了 UserReports.xml 文件之后，您将需要退出并重新启动 Oracle SQL Developer。重新启动之后，运行报表。您会看到类似下面的屏幕。
共享报表
如果您想与客户、同事或其他 Oracle SQL Developer 用户共享自己的用户定义报表，可以复制 UserReports.xml 文件，然后将它移至他们计算机上的 SQL Developer User Information Directory，即可共享您的所有报表。
如果您不想共享其中某些报表，则针对特定报表删除 <report> 和 </report> 之间的文本。（请再次确保保留了文件的备份！）
导出报表数据
运行了报表后，在数据网格中单击鼠标右键，将看到一个上下文菜单。唯一的选项应该是 Export。选择 Export，将获得另一个包含了 CSV、XML、Insert、Loader 和 Text 的菜单。这些选项是报表数据的导出格式。选择以上任何选项都会弹出相同的对话框。
- CSV 是一个用逗号分隔的值文件，适用于导入到电子表格（或 Oracle Application Express）中。
- XML 是数据的 XML 文档。
- Insert 为数据集中的每条记录创建一个插入语句。
- Loader 创建一个 SQL*Loader 数据控制文件。
- Text 创建一个用制表符分隔的文本文件。
对话框中的 Columns 选项卡允许您选择导出文件中包含哪些列，Where 选项卡允许您添加一个可选的 where 子句以限制进入输出文件的记录。
SQL*Plus 该让位了
Oracle SQL Developer 是每个 Oracle 开发人员的工具包中都应有的一个工具。Oracle SQL Developer 除了允许您进行 SQL 和 PL/SQL 开发外，还提供了一个长长的有用报表的列表，以及一个创建您自己的用户定义报表的工具。
SQL*Plus 该让位了，Oracle SQL Developer 简化了自定义报表的创建，并且提供了额外的功能（如变量对话框和下钻报表）。如果您定期访问某个 Oracle 数据库，您可以将其归自己所有，下载 Oracle SQL Developer 试一下。
Lewis R. Cunningham (lewisc@rocketmail.com) 是交通运输行业的一名数据库架构师、开发人员和 DBA，并且是一名 Oracle ACE。他在 http://blogs.ittoolbox.com/oracle/guide/ 为 ITtoolbox 编写 “An Expert’s Guide to Oracle”，并且使用 Oracle 技术有 13 年了。
<binds><bind id="original_or_recycle"><type><
