【赛迪网－论坛整理】在实际应用中，sqlplus中的new_value作用是很大的，利用它可以解决许多的问题。

引用如下：

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=cmpr1

export oracle_home=/app/oracle/product/9205

export path=$oracle_home/bin:$path

sqlplus -s/nolog <

conn / as sysdba

column inst_num new_value ninst_num format 99999;

column inst_name new_value ninst_name format a12;

column db_name new_value ndb_name format a12;

column dbid new_value ndbid format 9999999999;

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='&1';

variable dbid number;

variable inst_num number;

begin

:dbid := &2;

:inst_num := &3;

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;

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