a我考网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

查看: 351|回复: 2

[考试辅导] oracle认证应用技术学习资料汇总18

[复制链接]
发表于 2012-8-4 14:06:19 | 显示全部楼层 |阅读模式
利用替换变量提高Oracle数据库系统的交互性
6 O, V) B5 a2 @# d" K% j0 P6 W! r数据库管理员在写SQL命令的时候,总希望SQL语句能够满足不同环境的需要。为此,可以通过交互式命令来提高SQL语句的灵活性。当在一个SQL语句中,加入了交互式变量之后,在语句运行之时,数据库就会提醒用户输入相关的变量。 - z5 P* B0 J$ D. v1 u

3 {2 c2 k  N, c' N  不过在实际工作中,往往把这个SQL语句保存为脚本。如此的话,可以进一步提高脚本文件的可重用性。有时会,在数据库管理的时候,结合替换变量,写一些有用的脚本文件,可以起到画龙点睛的作用。笔者在平时数据库开发与管理中,就喜欢采用这种方式来简化工作。在这篇文章中,笔者就结合自己的工作经验,给大家谈谈如何在Oracle数据库中利用可替换变量来提高SQL语句的灵活性。以及在使用替换变量时的一些注意点。 8 E; c3 a# p5 H$ t) e
/ V/ \& Y% \8 F/ i  g
  一、 定义与使用替换变量。 & A9 U0 H4 {" k$ @* }9 @6 C
  在Oracle提供的相关工具中,如SQL PLUS管理平台中,允许数据库管理员采用替换变量来提高SQL语句的灵活性。替换变量的定义跟普通变量的定义类似,只需要在变量名前面加入&符号即可。如上面例子中的“&name_filed”就表示一个替换变量。当运行一段有替换变量的SQL语句时,考试.大提示数据库系统会自动提醒用户要输入哪些变量值。
! j1 P1 H. g. @& N' `
5 h. R" m# @/ i3 X1 L( _/ [) u  如在如下这条语句中,&name_filed表示用户想要显示的字段值。若用户想要显示某个表中的多个字段的话,则可以在这里定义多个替换变量。而条件语句中的’&NAME’则表示一个限制条件,或者说一个查询参数。这条语句的大意就是用户输入员工的名字,然后让系统查询出用户想了解的信息,如用户的身份证号码等等。
& ]0 E' g' ~- {) o) Z( c
" K4 g  Z( A- N& N( |0 j5 T& k  SQL> select &name_filed from dtmfg.ad_user t where t.name like ’&NAME’;
1 Z9 n  B% e' {; ]  细心的读者或许会发现,在查询条件中的替换变量,为什么要用单引号括起来呢?这是因为,我们在写SQL语句的时候,若字段时字符型的话,就需要用单引号括起来。所以,在定义替换变量的时候,跟平时写SQL语句类型。若数据类型是字符或者日期型的,则需要在定义替换变量的时候,用单引行括起来。否则的话,在用户执行这条语句、输入变量的时候,每次都需要把括号添加进去。那就非常的麻烦。相比起来,还不如在定义这条SQL语句的时候,就加入单引号。如此,在输入对应值的时候,就不用用单引号括起来了,可以直接输入。
' X+ k: H" s  b7 k; U% ?5 H
" X! o  c/ \0 M2 Y" p5 }! a9 E. {  另外,替换变量不但在Select语句中可以使用,在Update等语句中也可以普遍应用。 . ?6 H8 `& m" Z( f6 D
, S, G/ p# P2 {$ w
  二、 定义默认值。 2 Y$ y1 u6 p+ M" S* @1 G
  有时候,对于替换变量我们可能会有默认值。如果数据库管理员已经知道替换变量的具体值,又想避免运行脚本文件时反复出现,则可以给替换变量定义一个默认值。在SQL Plus平台中,数据库管理员可以利用Define命令来定义替换变量的默认值。
; j3 H1 `. G2 \8 I6 D2 l/ _% [5 j
  在定义替换变量默认值的时候,需要注意几个问题。 0 D8 A% @9 i* r; B0 R
  一是要合理使用单引号。该用单引号括起来的,就要使用单引号;不需要用的时候,坚决不用。否则的话,会造成SQL语句的运行错误。那么什么时候该用单引号呢?这主要是根据SQL语句来的。平时在写SQL语句的过程中要用到单引号的,则在定义默认值的时候,也要用单引号括起来。如果平时不用的话,则就可以不用。如在定义字段名称的默认值时,就不需要用单引号括起来。画蛇添足,反而会导致SQL语句的执行错误。 & b4 y' {% U2 h9 n) }: o- Q  i
1 N  {: I' U$ R5 N/ w5 s2 j
  二是这只是定义了一个默认值。当定义来一个默认值之后,系统就不会提示用户输入相关的参数,而直接采用默认值。如在上面这个例子中,若利用define NAME=’SuperUser’这条语句定义了NAME的默认值之后,在执行这条Select语句的时候,就不会再提示用户输入NAME值。我们可以利用Define命令来查看当前环境中所有的可替换变量的值。也可以利用Define NAME来查看可替换变量NAME的默认值。
7 u+ o5 h9 n5 ^) k. l
8 W; ?& D4 h" s; b, U$ C  三是若用户采用的是脚本文件的话,则可以直接把这个定义默认值的语句放在脚本文件的一开始。如此的话,这个默认值的定义就会随着脚本文件而走。用户不用每次使用脚本文件的时候,都先去定义默认值。这个这个默认值的作用域只是适用于当前的会话。当数据库管理员结束当前的会话后,这个变量的默认值也就失效了。为了在下次重新会话的时候,仍然可以利用这个默认值,故在脚本文件一开始定义可替换变量的默认值,也是非常有必要的。
! `" n+ w0 A5 n* ]1 b8 S$ c' G! U$ l8 `  {! W9 P% y. B! v1 i1 W
  四是利用Define命令来定义可替换变量的时候,有一个限制条件。即可替换变量必须是字符型的数据。若用户想定义的可替换变量是数字型的,如员工的薪水,此时,就需要采用ACCEPT命令。否则的话,就是一个无效的默认值。
回复

使用道具 举报

 楼主| 发表于 2012-8-4 14:06:20 | 显示全部楼层

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

  若我们有时候不需要这个默认值,需要取消他,改如何处理呢?此时,我们就可以利用undefine命令,来取消某个替换变量的默认值。当然,我们也可以利用Define命令来给某个替换变量设置一个新的值。在实际工作中,可能还是直接采用Define命令来为其设置一个默认值来的方便。 $ p! Y; S* {4 J& M* M, Q' l
6 ]5 c* o. }! _. f7 n
  三、 测试完毕后,保存为脚本文件。 + B" q2 }7 Y% d/ o) x
  为了增强这个SQL语句的可重用性,我们往往会把写好的SQL语句保存为脚本文件。如此的话,下次需要用到的时候,直接执行这个脚本语句即可。在保存的时候,为了有更好的可读性,笔者有一些小小的建议。
% j& u; A* i9 f* _1 `: l6 D+ M3 O6 i- ~! s, ]7 x
  一是最好在脚本文件的开头,注释一些这条语句的具体用途。俗话说,好记性不如烂笔头。在几个月后,很难确保你还记得这条语句的用途。 + U: G4 x  A! D0 f$ y6 t$ I; s0 ]

" M; h' r" R! g1 ]% i5 U& x2 B' \  二是给可替换变量取一些比较直观的名字。否则的话,当使用数据库管理员可能直接都不知道某个替换变量所代表的含义。另外,列的替换变量跟查询语句中的替换变量最好进行区分,如可以利用前缀进行区别。如此做的话,这个语句的可阅读性就会增强。 ; ?8 v- b* R* G! a# v
# [4 |( [' u/ i1 c. u. Z! F
  三是可利用Prompt命令来输出一些提示信息。在编写替换变量的时候,要像编写其他程序一样,养成为每个可替换变量做好注释的习惯。这时作为数据库管理员一个必备的技能之一。 2 d! Z9 _6 H1 V4 @4 X) J6 t: \/ J8 ^

3 ]) `2 v' m8 J* [( V  四、 其他解决方案。 7 s; {0 H- y7 W- |' X4 F
  针对Oracle数据库SQL语句的交互性而言,它还提供了一些其他的解决方案。 3 I+ G7 q# L3 a  I$ r
  如利用ACCEPT、PAUSE、PROMPT三个命令结合使用提供一个模拟的用户输入界面。ACCEPT命令可以让数据库管理员指定替换变量的类型。因为Define定义替换变量默认值的时候,只能够接受字符型数据的默认值。若是其他数据类型的默认值的话,就要用ACCEPT来定义。而PAUSE顾名思义,可以让脚本语句暂时运行,以提供一个更加友好的用户界面。如可以让用户通过输入回车键来执行剩下的脚本语句等等。如此,就给用户一个“喘息”核对数据的机会。
. A" }& v2 v% H4 q% L5 J# W) r
# ^, R% ~0 |7 X7 x1 S; I  如可以通过绑定变量的方式,来实现替换变量的功能。绑定变量只在当前会话中存在,同名的绑定变量一最后一个定义为准。绑定变量在赋值的时候,比较麻烦,需要采用Excute命令来给绑定变量进行赋值。所以,在实际应用中,不是很广泛。 - P/ H( y( L2 {, q" Z; c
4 i/ O/ \. e/ V7 @: n
  在这集中解决方案中,比较喜欢采用前面两种方式。即利用Define与Accept语句来定义替换变量,同时采用Prompt与Pause命令来给用户以及自己设计一个人性化的输入界面。 0 Z/ O7 C; r% T2 e
配置10g中SQL*Plus的autotrace 1 k6 t! A& c- @" f
  $ W' S* `2 _$ T7 w% u& w& n
AUTOTRACE 是SQL*Plus 中一个工具,可以显示所执行查询的解释计划(explain plan)以及所用的资源。这本书中大量使用了AUTOTRACE 工具。
- F5 q2 V% ?7 b5 R6 y% y: J( _1 ^6 G0 s( l5 t" e6 `1 a, L
  配置AUTOTRACE 的方法不止一种,以下是我采用的方法:
% [) `1 A% X1 t& N! K  1、
& S& S) M4 V- _2 x: `4 E% ~# M) i  (1)cd [ORACLE_HOME]/sqlplus/admin;
6 I4 y8 y. j" r) t, d- ^+ K: ], H! g  (2)作为SYS 或SYSDBA 登录SQL*Plus;
! z# ]6 I$ C% W% w* Y3 {  (3)运行@/plustrce.sql;
% g/ N: Y# z5 D3 [  (4)运行GRANT PLUSTRACE TO PUBLIC。 ) K0 K1 ~- b  m+ b
  Examda提示: 可以把GRANT TO PUBLIC 中的PUBLIC 替换为某个用户。 . n6 A, r: ^, @" y, w. F
  通过将PLAN_TABLE置为public,任何人都可以使用SQL*Plus 进行跟踪。这么一来,就不需要每个用户都安装自己的计划表。
5 D* }. |. s1 k+ _) e4 U" H  2、还有一种做法是,在想要使用AUTOTRACE 的每个模式中分别运行@utlxplan。
. I" M3 j' r$ u, @  下一步是创建并授予PLUSTRACE 角色:
回复 支持 反对

使用道具 举报

 楼主| 发表于 2012-8-4 14:06:21 | 显示全部楼层

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

  (1)cd [ORACLE_HOME]/rdbms/admin / |+ l: ^* H: F6 R) ]
  (2)作为SYS 登录SQL*Plus; % m& D; U" R8 }/ W3 ~
  (3)运行@utlxplan.sql; ) y' G/ \9 U& \$ }& C3 f
  (4)运行CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE; # ]( p8 U% W* p6 S( I+ R
  (5)运行GRANT ALL ON PLAN_TABLE TO PUBLIC。 - x) g8 e* c9 \: \* k0 J4 q
  重申一遍,如果愿意,可以把GRANT 命令中PUBLIC 替换为每个用户。
5 @" p7 N4 g( c5 l  附实例: . M7 M+ Y+ \* }0 ~( ^
  想在scott下使用autotrace
" V5 P1 K( c6 {9 k7 ?  SQL> set autotrace on; 8 H$ _; ]0 ~1 |8 O
  SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled 6 Z/ S8 Q4 C# e$ j) x6 `% ?
  SP2-0611: Error enabling STATISTICS report   s2 \& P$ b3 C6 ]$ P1 [
  SQL> @/u01/app/oracle/product/10.2.0/db_1/sqlplus/admin/plustrce.sql
7 \9 e3 r+ S9 E+ K  SQL>  4 b3 d: p2 `5 u; X# C! e
  SQL> drop role plustrace; ( I0 k. R% i/ Z" l' P$ q
  Role dropped.
6 P. _3 O, e7 Z  f( H' v0 Z  SQL> create role plustrace;
8 g0 B0 e* r& Z7 R: q" r! s2 e  Role created.
7 Y1 J% c; _! {% a( O& a  SQL>  
8 |: O- f  k) G& {5 [# W6 c7 _  SQL> grant select on v_$sesstat to plustrace;
1 G/ s3 V+ |( }4 \# o) U) ]! O  Grant succeeded. 1 R! a+ |2 J8 g9 b; S% p+ L
  SQL> grant select on v_$statname to plustrace; * [; s/ n. K2 m
  Grant succeeded.
& J3 P" r  H- ?  SQL> grant select on v_$mystat to plustrace;
& C% M% S9 C' l! |. [! X- U  Grant succeeded.
, Y# A6 O+ a6 u; o5 K& S, f& b  SQL> grant plustrace to dba with admin option; " k6 u" I; Q: z5 B; o* h
  Grant succeeded. 5 g' o) O+ ?: S8 V  U' y0 l
  SQL>  
7 a1 V" ?0 r5 n$ {) A% t  SQL> set echo off ) |, M# ]0 o8 S2 m# l
  SQL>  3 b" d. `! l* {' Q
  SQL>  
/ W' s" \5 @+ j0 }3 k" W* a  SQL>  ; s5 ~, @* S: R* v3 Y* v
  SQL> grant plustrace to scott; & H+ O+ p+ i5 F% A" ]) z: r
  Grant succeeded.
5 ?& ~. f/ c/ P& g3 f  再次设置
  F( S! |* z5 z3 z/ {  [oracle@oracle admin]$ sqlplus scott/tiger
9 ]3 A8 O0 O5 `0 ]+ t4 A* l  S: ]  SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 24 14:22:40 2008
$ p. ]8 s  l6 D4 X) T' D  Copyright (c) 1982, 2005, Oracle. All rights reserved.   K: |6 V2 Q% e
  Connected to: / w( O0 G- Z+ T" i
  Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production ' \  w) y& l* R" I9 p
  With the Partitioning, OLAP and Data Mining options
  ~9 f0 Y* f8 B  SQL> set autotrace on; 7 |: K/ J" M* V$ \+ A$ \
  SQL> set autotrace traceonly;
! K' N# S" C# R$ C0 R; c  SQL> set timing on;
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Archiver|手机版|小黑屋|Woexam.Com ( 湘ICP备18023104号 )

GMT+8, 2024-6-27 02:39 , Processed in 0.409222 second(s), 26 queries .

Powered by Discuz! X3.4 Licensed

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表