SQLPLUS 自助导数及email

 

目的：为应付日益繁重的数据处理开发的自助脚本

数据导出方式：sqlplus

email 方式：vb

系统环境：win2003

整个脚本分 数据导出、验证sql,导出sql, 邮件发送四个部分。

数据导出bat

echo off

echo ******************** 数据导出********************

SET db_user=******
SET db_pwd=*******
SET HOST=*******
set db_sid=***********
set shell_path=E:\AUTO\SHELL\数据需求
set data_path=E:\AUTO\DATA
set outfile=数据.html
set zipfile=数据.7z

rem SET /p days=输入提取最近几天数据(默认1天):
if "%days%"=="" (set days=1)

rem 验证数据量 如果数据量为0 则不导出

sqlplus @%shell_path%\check.sql %days%

echo %ERRORLEVEL%
if %errorlevel%==0 goto :end
rem 构造SQLPLUS执行脚本

echo %TIME%:开始导出数据

sqlplus   @%shell_path%\export.sql %days% %data_path%\%outfile%
echo %TIME%:导出成功

rem 压缩数据

set path=C:\Program Files\7-Zip;%path%
set txt_file=%data_path%\%outfile%
set zip_file=%data_path%\%zipfile%

if exist %zip_file%  del %zip_file%

echo %TIME%:开始压缩%txt_file%至%zip_file%
7z a %zip_file% %txt_file%
echo %TIME%:压缩成功

if exist %txt_file% del %txt_file%

 

********************************** check.sql**********************************

SET ECHO OFF;

SET FEEDBACK OFF;

set termout OFF ;

var i number;

exec select count(*) into :i FROM TBLAE_NAME t WHERE T. DT >= &1 ;

exit :i;

********************************** export.sqll**********************************

set linesize 200

set term off verify off feedback off pagesize 999

set markup html on entmap ON spool on preformat off

SPOOL &2

 SELECT  *

  FROM  TABLE_NAME T;

  

SPOOL OFF

QUIT

********************************** SENDMAIL.VBS*******************************

'On Error Resume Next

FromAddress="****"

toAddress="*****"

CcAddress="*****"

EmailSubject="数据"

UserName="*****"

password="*****"

mailServerAddress="*****"

Dim dataContext

dataContext=""

Const ForReading = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("E:\AUTO\SHELL\数据需求\数据邮件正文.txt", ForReading)

Do Until objFile.AtEndOfStream

    strNextLine = objFile.ReadLine

    dataContext =dataContext+strNextLine+"
"

Loop

objFile.Close

'wscript.echo body

'wscript.echo dataContext

call SendEmail(FromAddress,ToAddress,CcAddress,EmailSubject,dataContext,UserName,Password,mailServerAddress)

Sub SendEmail(FromAdd,ToAdd,CcAdd,ssSubject,ssBody,sUserName,sPassword,smailServerAddress)

Set objEmail = CreateObject("CDO.Message")

objEmail.From = FromAdd

objEmail.To = ToAdd

objEmail.CC = CcAdd

objEmail.AddAttachment "E:\AUTO\DATA\数据.7z"

objEmail.Subject = ssSubject

objEmail.HTMLBody = ssBody

objEmail.BodyPart.CharSet = "gb2312"

'objEmail.con

objEmail.Configuration.Fields.Item _

    ("") = 2

objEmail.Configuration.Fields.Item _

    ("") = smailServerAddress

objEmail.Configuration.Fields.Item _

    ("port") = 25

      

objEmail.Configuration.Fields.Item _  

       ("") = 1

objEmail.Configuration.Fields.Item _

       ("") = sUserName

objEmail.Configuration.Fields.Item _

       ("") = sPassword

objEmail.Configuration.Fields.Update

objEmail.Send

Set objEmail=Nothing

end Sub

创建一个main.bat，配置定时任务