newborn 如何使用new

如何使用new  

　　引用如下

　　Oracle SQL*Plus has a very useful new sub parameter 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 d dbid dbid

　　 d name db_name

　　 i instance_number inst_num

　　 i instance_name inst_name

　　from v$database d

　　v$instance i;

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

　　select dbid name 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_name instance_number from v$instance where instance_number=:inst_num;

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

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

　　@cs sql &ndb_name &ndbid &ninst_num

　　Exit

　　EOF

　　[/app/oracle/utils/scripts]$ cat cs sql

　　select dbid name from v$database where name= & ;

　　variable dbid number;

　　variable inst_num number;

　　begin

　　:dbid := & ;

　　:inst_num := & ;

　　end;

　　/

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

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

　　variable dbid number;

　　variable inst_num number;

　　begin

　　:dbid := &ndbid;

　　:inst_num := &ninst_num;

　　end;

　　/

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