本人来自转载,最开始是英文文档,某人加了注释
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、2、3,表示调用变量的顺序
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>