oracle认证应用技术学习资料汇总18

会计考友

利用替换变量提高Oracle数据库系统的交互性
( e4 Y5 M2 `& {& W: x9 [数据库管理员在写SQL命令的时候，总希望SQL语句能够满足不同环境的需要。为此，可以通过交互式命令来提高SQL语句的灵活性。当在一个SQL语句中，加入了交互式变量之后，在语句运行之时，数据库就会提醒用户输入相关的变量。
2 e" s6 S; V: J( B4 ~3 C
/ Q% i: `8 Q7 j( p0 X6 ~　　不过在实际工作中，往往把这个SQL语句保存为脚本。如此的话，可以进一步提高脚本文件的可重用性。有时会，在数据库管理的时候，结合替换变量，写一些有用的脚本文件，可以起到画龙点睛的作用。笔者在平时数据库开发与管理中，就喜欢采用这种方式来简化工作。在这篇文章中，笔者就结合自己的工作经验，给大家谈谈如何在Oracle数据库中利用可替换变量来提高SQL语句的灵活性。以及在使用替换变量时的一些注意点。

* T- p2 @, h" t! a/ s6 L　　一、 定义与使用替换变量。
1 s* k8 R0 \1 b5 T" F" l( c　　在Oracle提供的相关工具中，如SQL PLUS管理平台中，允许数据库管理员采用替换变量来提高SQL语句的灵活性。替换变量的定义跟普通变量的定义类似，只需要在变量名前面加入&符号即可。如上面例子中的“&name_filed”就表示一个替换变量。当运行一段有替换变量的SQL语句时，考试.大提示数据库系统会自动提醒用户要输入哪些变量值。
" [: T  L3 J, v9 M* {( y( g1 Z1 M
, ?2 W0 K# P: ]　　如在如下这条语句中，&name_filed表示用户想要显示的字段值。若用户想要显示某个表中的多个字段的话，则可以在这里定义多个替换变量。而条件语句中的’&NAME’则表示一个限制条件，或者说一个查询参数。这条语句的大意就是用户输入员工的名字，然后让系统查询出用户想了解的信息，如用户的身份证号码等等。
3 c1 a4 u$ i" d2 K) ^0 ^( |: L
　　SQL> select &name_filed from dtmfg.ad_user t where t.name like ’&NAME’;
　　细心的读者或许会发现，在查询条件中的替换变量，为什么要用单引号括起来呢?这是因为，我们在写SQL语句的时候，若字段时字符型的话，就需要用单引号括起来。所以，在定义替换变量的时候，跟平时写SQL语句类型。若数据类型是字符或者日期型的，则需要在定义替换变量的时候，用单引行括起来。否则的话，在用户执行这条语句、输入变量的时候，每次都需要把括号添加进去。那就非常的麻烦。相比起来，还不如在定义这条SQL语句的时候，就加入单引号。如此，在输入对应值的时候，就不用用单引号括起来了，可以直接输入。

  A3 p; f1 \. _  a" |1 C& D　　另外，替换变量不但在Select语句中可以使用，在Update等语句中也可以普遍应用。

  ]& S, {1 n. y% t9 u4 U* |/ |　　二、 定义默认值。
　　有时候，对于替换变量我们可能会有默认值。如果数据库管理员已经知道替换变量的具体值，又想避免运行脚本文件时反复出现，则可以给替换变量定义一个默认值。在SQL Plus平台中，数据库管理员可以利用Define命令来定义替换变量的默认值。
, g. t: r+ D: }/ H, y' ]8 f* d' M( [
　　在定义替换变量默认值的时候，需要注意几个问题。
* N7 F6 d( K7 l. R# i6 W# ]　　一是要合理使用单引号。该用单引号括起来的，就要使用单引号;不需要用的时候，坚决不用。否则的话，会造成SQL语句的运行错误。那么什么时候该用单引号呢?这主要是根据SQL语句来的。平时在写SQL语句的过程中要用到单引号的，则在定义默认值的时候，也要用单引号括起来。如果平时不用的话，则就可以不用。如在定义字段名称的默认值时，就不需要用单引号括起来。画蛇添足，反而会导致SQL语句的执行错误。

9 c" f9 C3 s# n　　二是这只是定义了一个默认值。当定义来一个默认值之后，系统就不会提示用户输入相关的参数，而直接采用默认值。如在上面这个例子中，若利用define NAME=’SuperUser’这条语句定义了NAME的默认值之后，在执行这条Select语句的时候，就不会再提示用户输入NAME值。我们可以利用Define命令来查看当前环境中所有的可替换变量的值。也可以利用Define NAME来查看可替换变量NAME的默认值。

　　三是若用户采用的是脚本文件的话，则可以直接把这个定义默认值的语句放在脚本文件的一开始。如此的话，这个默认值的定义就会随着脚本文件而走。用户不用每次使用脚本文件的时候，都先去定义默认值。这个这个默认值的作用域只是适用于当前的会话。当数据库管理员结束当前的会话后，这个变量的默认值也就失效了。为了在下次重新会话的时候，仍然可以利用这个默认值，故在脚本文件一开始定义可替换变量的默认值，也是非常有必要的。
  b2 k1 z) b" w! j- W$ D
* s& G; ?$ }! x, p6 n# _) a" U　　四是利用Define命令来定义可替换变量的时候，有一个限制条件。即可替换变量必须是字符型的数据。若用户想定义的可替换变量是数字型的，如员工的薪水，此时，就需要采用ACCEPT命令。否则的话，就是一个无效的默认值。

会计考友

　　若我们有时候不需要这个默认值，需要取消他，改如何处理呢?此时，我们就可以利用undefine命令，来取消某个替换变量的默认值。当然，我们也可以利用Define命令来给某个替换变量设置一个新的值。在实际工作中，可能还是直接采用Define命令来为其设置一个默认值来的方便。

: o; L7 l& b) {: a, R　　三、 测试完毕后，保存为脚本文件。
- b! i8 c; [3 s, _: v　　为了增强这个SQL语句的可重用性，我们往往会把写好的SQL语句保存为脚本文件。如此的话，下次需要用到的时候，直接执行这个脚本语句即可。在保存的时候，为了有更好的可读性，笔者有一些小小的建议。
. [& E1 \0 x7 j2 `
　　一是最好在脚本文件的开头，注释一些这条语句的具体用途。俗话说，好记性不如烂笔头。在几个月后，很难确保你还记得这条语句的用途。

) u8 b( w9 i' O8 k　　二是给可替换变量取一些比较直观的名字。否则的话，当使用数据库管理员可能直接都不知道某个替换变量所代表的含义。另外，列的替换变量跟查询语句中的替换变量最好进行区分，如可以利用前缀进行区别。如此做的话，这个语句的可阅读性就会增强。
9 b. X; J8 f$ n0 }" I% C4 c
+ W0 a. l- s; `$ l# k　　三是可利用Prompt命令来输出一些提示信息。在编写替换变量的时候，要像编写其他程序一样，养成为每个可替换变量做好注释的习惯。这时作为数据库管理员一个必备的技能之一。
0 m. _8 F. s6 \% Q; E8 F6 u4 E
　　四、 其他解决方案。
  ~; b. J- N* A　　针对Oracle数据库SQL语句的交互性而言，它还提供了一些其他的解决方案。
　　如利用ACCEPT、PAUSE、PROMPT三个命令结合使用提供一个模拟的用户输入界面。ACCEPT命令可以让数据库管理员指定替换变量的类型。因为Define定义替换变量默认值的时候，只能够接受字符型数据的默认值。若是其他数据类型的默认值的话，就要用ACCEPT来定义。而PAUSE顾名思义，可以让脚本语句暂时运行，以提供一个更加友好的用户界面。如可以让用户通过输入回车键来执行剩下的脚本语句等等。如此，就给用户一个“喘息”核对数据的机会。

　　如可以通过绑定变量的方式，来实现替换变量的功能。绑定变量只在当前会话中存在，同名的绑定变量一最后一个定义为准。绑定变量在赋值的时候，比较麻烦，需要采用Excute命令来给绑定变量进行赋值。所以，在实际应用中，不是很广泛。
" Y  F& I7 c+ c3 G( P! J9 l
　　在这集中解决方案中，比较喜欢采用前面两种方式。即利用Define与Accept语句来定义替换变量，同时采用Prompt与Pause命令来给用户以及自己设计一个人性化的输入界面。
2 l& c" o' a4 a; [% O; u配置10g中SQL*Plus的autotrace
  
0 z  p- U7 a% Y$ I- Z$ ]AUTOTRACE 是SQL*Plus 中一个工具，可以显示所执行查询的解释计划（explain plan）以及所用的资源。这本书中大量使用了AUTOTRACE 工具。

1 s+ [8 a% T: k/ y: E　　配置AUTOTRACE 的方法不止一种，以下是我采用的方法：
　　1、
　　（1）cd [ORACLE_HOME]/sqlplus/admin；
　　（2）作为SYS 或SYSDBA 登录SQL*Plus；
3 X+ ]' z  U+ [& v  J6 A6 ^　　（3）运行@/plustrce.sql；
) p' `( Z$ ^: A　　（4）运行GRANT PLUSTRACE TO PUBLIC。
. D/ X7 G# I, B, w- u　　Examda提示: 可以把GRANT TO PUBLIC 中的PUBLIC 替换为某个用户。
. h0 g. y: U# b9 V　　通过将PLAN_TABLE置为public，任何人都可以使用SQL*Plus 进行跟踪。这么一来，就不需要每个用户都安装自己的计划表。
' R( z8 I& a/ [; ~) M" U; V　　2、还有一种做法是，在想要使用AUTOTRACE 的每个模式中分别运行@utlxplan。
　　下一步是创建并授予PLUSTRACE 角色：

会计考友

　　（1）cd [ORACLE_HOME]/rdbms/admin
- x' v0 h  p9 L5 M, M$ y& c0 f　　（2）作为SYS 登录SQL*Plus；
* W  J+ M! g* Y' `$ n2 N, O　　（3）运行@utlxplan.sql；
　　（4）运行CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE；
8 C& [# _$ A  X& u  M　　（5）运行GRANT ALL ON PLAN_TABLE TO PUBLIC。
　　重申一遍，如果愿意，可以把GRANT 命令中PUBLIC 替换为每个用户。
# m$ k7 Y4 \( W. L　　附实例:
　　想在scott下使用autotrace
# R1 G- v, F/ I# g. q/ C　　SQL> set autotrace on;
　　SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
　　SP2-0611: Error enabling STATISTICS report
　　SQL> @/u01/app/oracle/product/10.2.0/db_1/sqlplus/admin/plustrce.sql
( g  p! `+ c( [7 x　　SQL>  
; z  z; h0 R# `1 m7 O　　SQL> drop role plustrace;
& }+ e: U  R. \( j: z& n　　Role dropped.
7 e& ^% L0 ]5 W4 E　　SQL> create role plustrace;
' t9 i) X- J4 H. r( v2 u4 g　　Role created.
　　SQL>  
$ `; \1 ?+ }# W! A" f9 Y( s: l' g　　SQL> grant select on v_$sesstat to plustrace;
　　Grant succeeded.
5 i3 }" w( Z: m" K  ]4 `3 d　　SQL> grant select on v_$statname to plustrace;
9 [8 f% ]! z, n& D- B　　Grant succeeded.
# _# t7 V+ o) ^0 B9 [　　SQL> grant select on v_$mystat to plustrace;
　　Grant succeeded.
& z7 W! ?8 Z! `  R3 `; z　　SQL> grant plustrace to dba with admin option;
, ^4 F* k" R$ j3 g　　Grant succeeded.
　　SQL>  
　　SQL> set echo off
　　SQL>  
　　SQL>  
7 M" R  b4 N; O2 M. J, k9 H　　SQL>  
7 ]* c# p, z1 B; U+ |　　SQL> grant plustrace to scott;
9 f& Z9 v8 U8 O& _4 r　　Grant succeeded.
' B$ O! h2 I, \/ r8 b7 G　　再次设置
( c4 a/ G+ z, Z　　[oracle@oracle admin]$ sqlplus scott/tiger
　　SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 24 14:22:40 2008
# m8 I% y" ]; q/ L0 v　　Copyright (c) 1982, 2005, Oracle. All rights reserved.
5 @' z5 B5 ?+ o3 k$ |. }% P9 K　　Connected to:
) @  X( Q+ c: U3 y4 q5 h　　Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
　　With the Partitioning, OLAP and Data Mining options
　　SQL> set autotrace on;
　　SQL> set autotrace traceonly;
　　SQL> set timing on;