浅谈sql输出及变量输入及传递

 有时候需要得到sql结果，将结果发布出去；
而且同时需要将不同的参数传递给 sql语句，根据不同的条件得到不同的值，或者有时得到一个参数，要在多个地方都调用。


下面给一个我所采用的比较典型的例子：

例子的内容是在某个数据库中监控其他数据库的表空间状况

1. db_list 表存放其他数据库的配置信息；并且建立了 连接到其他数据库的dblink

SQL> desc  db_list
Name          Type         Nullable Default Comments                                                                  
------------- ------------ -------- ------- --------------------------------------------------------------------------
DB_ID         NUMBER                        该监控数据库的dbid，来自于v$database 视图                                 
DB_SID        VARCHAR2(20)                  该监控数据库的sid                                                         
HOST_NAME     VARCHAR2(20) Y                该监控数据库所在机器的主机名                                              
VIP           VARCHAR2(15) Y                该监控数据库所使用的浮动ip                                                
PORT          CHAR(4)      Y                该监控数据库所使用的端口                                                  
CONN_MON_FLAG CHAR(1)               'N'     该监控数据库是否进行连通性监控的标志位，如果为'Y'表示监控，为'N'表示不监控
TS_MON_FLAG   CHAR(1)               'N'     该监控数据库是否进行表空间监控的标志位，如果为'Y'表示监控，为'N'表示不监控




SQL> select *  from user_db_links;

DB_LINK                                                                          USERNAME                       HOST                                                                             CREATED
-------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- -----------
FMS                                                                              DBMON                          fms                                                                              2007-12-18
FMSTEST                                                                          DBMON                          fmstest                                                                          2007-12-18
FMSUAT                                                                           DBMON                          fmsuat                                                                           2007-12-18

SQL>





其他数据库中的接口用户为dbmon，它下面有个视图来得到数据库的表空间情况

create or replace view dbmon.v_ts_status as
select tablespace_name ,
       sum(total_size) total,
       sum(surplus) remain,
       sum(max_continue) max_lianxu,
       round(sum(surplus)/sum(total_size)*100) remain_rate
from
   (
      (select tablespace_name,(0)total_size,round(sum(bytes)/1024/1024,0)surplus,round(max(bytes)/1024/1024,0)max_continue
         from dba_free_space
        group by tablespace_name
      )
      union
      (select tablespace_name,round(sum(bytes)/1024/1024,0),0,0
         from dba_data_files
        group by tablespace_name
      )
    )
group by tablespace_name



2. 配置脚本

ts_monitor.sh  shell 脚本是主逻辑，它调用get_db_list.sql, get_ts_info.sql ,get_mine_tsinfo.sql
get_db_list.sql 中得到的每个数据库sid 都以变量形式传递给get_ts_info.sql .


test@cwdata>ls
db_list.txt  get_db_list.sql  get_mine_tsinfo.sql  get_ts_info.sql  temp.txt  test.sh  tmp.txt  ts_info.txt  ts_monitor.sh
test@cwdata>



test@cwdata>cat ts*.sh
cd $HOME
. ./.profile
cd $HOME/monitor/ts

sqlplus '/  as sysdba' <<EOF
set head off
set feedback off
set pages 0
@get_db_list.sql
exit
EOF

rm ts_info.txt
cat db_list.txt | while read DB_SID
do
echo "$DB_SID:" >> ts_info.txt

sqlplus /nolog @get_ts_info.sql $DB_SID
cat temp.txt >>ts_info.txt
echo -e "/n/n/n" >>ts_info.txt
done

echo "MONITOR:" >> ts_info.txt
sqlplus /nolog @get_mine_tsinfo.sql
cat temp.txt >>ts_info.txt

cat ts_info.txt |  mailx -s "DB TABLESPACE INFO" -r DB_MONITOR user@test.com


test@cwdata>



test@cwdata>cat get_db_list.sql
spool db_list.txt
select db_sid from dbmon.db_list where ts_mon_flag='Y';
spool off
test@cwdata>cat get_ts_info.sql
connect dbmon/dbmon;
set head on
set feedback off
set pages 100
set lines 120
set wrap on
set ver off
col tablespace_name for a30
col need_increased for a20
spool temp.txt
select a.*,decode(sign(10-remain_rate)+sign(80-max_lianxu),2,'Y','') need_increased from v_ts_status@&1 a order by need_increased,remain_rate;
spool off
exit
test@cwdata>cat get_mine_tsinfo.sql
connect dbmon/dbmon;
set head on
set feedback off
set pages 100
set lines 120
set wrap on
set ver off
col tablespace_name for a30
col need_increased for a20
spool temp.txt
select a.*,decode(sign(10-remain_rate)+sign(80-max_lianxu),2,'Y','') need_increased from v_ts_status a order by need_increased,remain_rate;
spool off
exit
test@cwdata>


3.小结：

a.在shell 脚本中调用 sql，一般可以有两种方式
 一种是 直接将sql脚本单独写好，直接 sqlplus /nolog @get_ts_info.sql $DB_SID 调用即可，可以加上shell变量
 一种是 用在shell 中进入sqlplus 命令模式，在两个EOF之间将sql 脚本列举，比如
 sqlplus '/  as sysdba' <<EOF
set head off
set feedback off
set pages 0
@get_db_list.sql
exit
EOF

当然了在sqlplus 情景模式下，sql也可以去调用一个写好的sql 脚本



b. sql 结果的输出比较简单的是采用 spool 的方式。当然了还可以采用utl_file 包等手段来直接写数据到某个文本中。
   spool 在使用时，结合sqlplus 中的各种环境变量控制，我们就能很好的控制输出。
比如：
set head on          标题行   打开
set feedback off     反馈     关闭
set pages 100        每页     100行
set lines 120        每行     120字符
set wrap on          当选择的行长度超过 linesize 时，    wrap 显示打开
set ver off          列举出变量替换前后的sql statement   ，  关闭
col tablespace_name for a30   定制  tablespace_name 字段的长度为30个字符
col need_increased for a20     定制 need_increased  字段的长度为20个字符



c. 对于sql 中变量的输入，如果是要在sql 执行过程中不采用交互式的，那么可以采用  @get_ts_info.sql $DB_SID 的方式来将变量写死
   对于这种方式，因为变量是来自于shell 脚本的，因此，在shell 用合适的逻辑来判断DB_SID 的内容，也能很好的实现需求。
  
   对于如果可以在sql 执行过程中交互式的，那么就直接执行 @get_ts_info.sql ，执行过程会提示用户输入变量1 的内容
  
  
  
d.对于sql逻辑内如 变量的传递，可以使用 variable，define，columne new_value 等方式
 
  1).一个session内部可以调用同一个 variable 变量，variable 变量的赋值要在pl/sql 块中，其引用要 以 :var 的方式
 
    fmsora:cwprd>vi auto*.sql
"auto_generate_report.sql" 17 lines, 516 characters
connect perfstat/perfstat;
variable  v1 number;
variable v2 number;
variable  v_str varchar2(20);
declare
begin
select min(snap_id),max(snap_id)  into  :v1,:v2  from  stats$snapshot a where  a.snap_time >= trunc(sysdate)+9/24 and a.snap_t
ime <= sysdate  and to_char(snap_time,'HH24MI' ) IN ( '0900','1900');

:v_str := 'sp_fms_'||to_char(sysdate-1,'MMDD')||'.txt';


dbms_output.put_line(to_char(:v1)||','||to_char(:v2)||','||:v_str);
end;
/
@/test/fms/data/fmsora/dbcheck/statspack/sql/myrepins.sql  :v1 :v2

exit


   2). 可以在sql 中用 define 定义一个常量（或者需要输入），然后 &var 调用，最后undefine 来释放变量
  
   define top_n_events = 5;
  
            order by time desc, waits desc
       )
 where rownum <= &&top_n_events;

  undefine top_n_events


   3).  用prompt 来提示用户输入变量，然后在后面引用变量
  
  
   --获取变量  begin_snap 和end_snap
   prompt
prompt
prompt Specify the Begin and End Snapshot Ids
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt Begin Snapshot Id specified: &&begin_snap
prompt
prompt End   Snapshot Id specified: &&end_snap



--将 begin_snap 再赋给 variable  ，有这个必要吗？  &&begin_snap 不是一样调用？

variable bid        number;
variable eid        number;
begin
  :bid       :=  &&begin_snap;
  :eid       :=  &&end_snap;
end;
/



4). 当一个var 没有被显式定义，而出现 &var 引用时，就会出现要求用户输入的交互式过程，用户可以输入为空或者任意值；
    column dflt_name new_value dflt_name  格式定义了一个dflt_name，并且指定 dflt_name 字段的第一行值就是dflt_name 变量的值。
   
    如下的逻辑实现了给出一个默认的报表名称，当用户在交互式操作中不为空时，就按用户的名称，当用户的操作为空时，就按照默认的文件名，
    这是用nvl 实现的，是和column new_value 结合。


fchora:cwuat >cat 1.sql
column dflt_name new_value dflt_name noprint;
select 'sp_test_' dflt_name from dual;
set termout on;

prompt
prompt Specify the Report Name
prompt ~~~~~~~~~~~~~~~~~~~~~~~
prompt The default report file name is &dflt_name..  To use this name,
prompt press <return> to continue, otherwise enter an alternative.

set heading off;
column report_name new_value report_name noprint;
select 'Using the report name ' || nvl('&&report_name','&dflt_name')
     , nvl('&&report_name','&dflt_name') report_name
  from sys.dual;
spool &report_name;
set heading on;
select sysdate  from dual;
spool off
fchora:cwuat >



5).sql 脚本中调用sql 脚本，相当于是一个sql 脚本，只不过用另一个sql 脚本来代替中间部分内容；
   variable 是 整个session 有效的；
   define 的变量是在被undefine 之前有效；
   column new_value 定义的变量也是整个会话期间有效；