在sqlplus中根据需要调用不同的脚本

本地需求，如果是某种类型，就需要执行相应的脚本进行验证。

参考网上资料，可以如下进行：

SQL> col scriptname noprint new_value scriptname;
SQL> select decode(coln, 'XXX', 'x.sql', null) as scriptname from tabn;

 

SQL> select '&scriptname' from dual;
old   1: select '&scriptname' from dual
new   1: select 'x.sql' from dual

'X.SQ
-----
x.sql

SQL>

接着执行“@&&scriptname”即可。

 

具体测试脚本：

[oracle@XXXXXX tmp]# cat x.sql
set serveroutput on
exec dbms_output.put_line('==== In x.sql.');

[oracle@XXXXXX tmp]# cat y.sql
set serveroutput on
exec dbms_output.put_line('==== In y.sql, before call.');

col scriptname noprint new_value scriptname;

select decode(colname, 'XXXX', 'x.sql', null) as scriptname from tablename;
@&&scriptname

exec dbms_output.put_line('==== In y.sql, afer call.');

[oracle@XXXXXX tmp]#

 

测试结果：

SQL> @y.sql
==== In y.sql, before call.

PL/SQL procedure successfully completed.

 

 

==== In x.sql.

PL/SQL procedure successfully completed.

==== In y.sql, afer call.

PL/SQL procedure successfully completed.

SQL>

 

参考文档如下：

 

sqlplus下条件判断问题！！
http://www.itpub.net/thread-654422-1-1.html
问题：

现在要在sqlplus中执行两个脚本a,b
首先会从数据库查询出条件，如select c from test
如果c=0则执行脚本a
如果c=1则执行脚本b
不知道sqlplus中能否进行类似于if else 的条件判断,以前没有接触到这样的问题，哪位碰到过？

答复1：

@>!cat aaa.sql
select 'aaa' col from dual;

@>!cat bbb.sql
select 'bbb' col from dual;

@>!cat ccc.sql
var v_c number;
col file_name new_value file_name
exec :v_c := '&a';
select case when :v_c = 1 then 'aaa' when :v_c = 0 then 'bbb' end file_name from dual;
select '&file_name' from dual;
@&file_name

@>set echo on
@>@ccc
@>var v_c number;
@>col file_name new_value file_name
@>exec :v_c := '&a';
Enter value for a: 1

PL/SQL procedure successfully completed.

@>select case when :v_c = 1 then 'aaa' when :v_c = 0 then 'bbb' end file_name from dual;

FIL
---
aaa

@>select '&file_name' from dual;
old   1: select '&file_name' from dual
new   1: select 'aaa' from dual

'AA
---
aaa

@>@&file_name
@>select 'aaa' col from dual;

COL
---
aaa

@>@ccc
@>var v_c number;
@>col file_name new_value file_name
@>exec :v_c := '&a';
Enter value for a: 0

PL/SQL procedure successfully completed.

@>select case when :v_c = 1 then 'aaa' when :v_c = 0 then 'bbb' end file_name from dual;

FIL
---
bbb

@>select '&file_name' from dual;
old   1: select '&file_name' from dual
new   1: select 'bbb' from dual

'BB
---
bbb

@>@&file_name
@>select 'bbb' col from dual;

COL
---
bbb

@>

答复2：

 

col scriptname noprint new_value scriptname; select decode(c,0,'/../a.sql',1,'/.../b.sql' , null) as scriptname  from test; @&&scriptname