oracle 结果集变量,SQLPLUS中使用new_value把查询结果传给变量

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.

使用方法小结一下：[@more@]

#!/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_numformat 99999;

column inst_name new_valueninst_name format a12;

column db_namenew_value ndb_nameformat a12;

column dbidnew_value ndbidformat 9999999999;

select d.dbiddbid

, d.namedb_name

, i.instance_numberinst_num

, i.instance_name inst_name

from v$database d,

v$instance i;

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

select dbid,namefrom v$database where name='&ndb_name';

prompt ################Use variable###################

variable dbidnumber;

variable inst_numnumber;

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,namefrom v$database where name='&1';

variable dbidnumber;

variable inst_numnumber;

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 dbidnumber;

variable inst_numnumber;

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;