熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

如何使用new

2013-11-13 16:20:19  來源: Oracle 
在實際應用中SQLPLUS中的new_value作用是很大的利用它可以解決許多的問題

  引用如下

  

  Oracle SQL*Plus has a very useful new subparameter to the column parameter called new_value The new_value directive allows data that has been retrieved from an Oracle table to be stored as a variable inside the SQL*Plus script

  

  By using the new_value parameter you can make your SQL*Plus script behave like a real programming language storing and addressing program variables just like in PL/SQL

  

  The ability to store SQL*Plus variables and fill them with Oracle data is a very powerful feature and makes SQL*Plus scripts more efficient because database access is reduced

  使用方法如下

  #!/bin/sh

  

  export ORACLE_SID=CMPR

  

  export ORACLE_HOME=/app/oracle/product/

  

  export PATH=$ORACLE_HOME/bin:$PATH

  

  sqlplus s/nolog <

  

  conn / as sysdba

  

  column inst_num new_value ninst_num format ;

  

  column inst_name new_value ninst_name format a;

  

  column db_name new_value ndb_name format a;

  

  column dbid new_value ndbid format ;

  

  

  select ddbid dbid

  

   dname db_name

  

   iinstance_number inst_num

  

   iinstance_name inst_name

  

  from v$database d

  

  v$instance i;

  

  prompt ###############Use new_value####################

  

  select dbidname from v$database where name=&ndb_name;

  

  

  prompt ################Use variable###################

  

  variable dbid number;

  

  variable inst_num number;

  

  begin

  

  :dbid := &ndbid;

  

  :inst_num := &ninst_num;

  

  end;

  

  /

  

  

  select instance_nameinstance_number from v$instance where instance_number=:inst_num;

  

  select dbidname from v$database where dbid=:dbid;

  

  

  prompt ##############Use sql file#####################

  

  @cssql &ndb_name &ndbid &ninst_num

  

  Exit

  

  EOF

  

  

  [/app/oracle/utils/scripts]$ cat cssql

  

  select dbidname from v$database where name=&;

  

  

  variable dbid number;

  

  variable inst_num number;

  

  begin

  

  :dbid := &;

  

  :inst_num := &;

  

  end;

  

  /

  

  select instance_nameinstance_number from v$instance where instance_number=:inst_num;

  

  select dbidname from v$database where dbid=:dbid;

  

  

  variable dbid number;

  

  variable inst_num number;

  

  begin

  

  :dbid := &ndbid;

  

  :inst_num := &ninst_num;

  

  end;

  

  /

  

  select instance_nameinstance_number from v$instance where instance_number=:inst_num;

  

  select dbidname from v$database where dbid=:dbid;


From:http://tw.wingwit.com/Article/program/Oracle/201311/18174.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.