oracle中new_value的使用

本人来自转载，最开始是英文文档，某人加了注释

SQL*PLUS中new_value的作用还是挺大的,多次在写脚本时用到它。使用new_value,可以方便的保存从Oracle表中选择出的数据，存为变量使用。

小结了三种使用方法，只是用到的地方不同，调用new_value的方法还是相同的：

#!/bin/sh
export ORACLE_SID=CMPR1
export ORACLE_HOME=/app/oracle/product/9205
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus -s/nolog< <EOF
conn / as sysdba
 
prompt ###############定义new_value###############
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 ###############方法一：直接作为变量调用###############
select dbid,name from v$database where name='&ndb_name';
 
 
prompt ###############方法二：传到其它变量中调用###############
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 ###############方法三：作为SQL脚本的变量调用###############
 
@cs.sql &ndb_name &ndbid &ninst_num
Exit
EOF
 
 
[/app/oracle/utils/scripts]$ cat cs.sql
----------可以数字1、２、３，表示调用变量的顺序
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;
 
----------也可以直接用new_value
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;

这里需要注意的是，new_value只能保存一个变量值，如果选择出来的数据大于一行，则只保存最后一个：
SQL> column a new_value a_value
SQL> select a from t;
 
         A
----------
         1
         3
         2
         4
         5
         6
         7
         8
         9
 
9 rows selected.
 
SQL> create table t1(b number);
 
Table created.
 
SQL> insert into t1 values (&a_value);   --返回值多于一行，最后一个值传到变量中
old   1: insert into t1 values (&a_value)
new   1: insert into t1 values (         9)
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from t where a=&a_value;
old   1: select * from t where a=&a_value
new   1: select * from t where a=         9
 
         A
----------
         9
 
SQL> select * from t1;
 
         B
----------
         9
 
SQL>