|
【赛迪网－论坛整理】在实际应用中，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;
|
