如何使用new

引用如下

Oracle SQL*Plus has a very useful new subparameter 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 ddbid dbid

dname db_name

iinstance_number inst_num

iinstance_name inst_name

from v$database d

v$instance i;

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

select dbidname 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_nameinstance_number from v$instance where instance_number=:inst_num;

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

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

@cssql &ndb_name &ndbid &ninst_num

Exit

EOF

[/app/oracle/utils/scripts]$ cat cssql

select dbidname from v$database where name=&;

variable dbid number;

variable inst_num number;

begin

:dbid := &;

:inst_num := &;

end;

/

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

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

variable dbid number;

variable inst_num number;

begin

:dbid := &ndbid;

:inst_num := &ninst_num;

end;

/

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

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

上一篇：第二季大量订单使甲骨文亚太区保持强劲势头

下一篇：autoconf手册（七）