Windows批处理文件中的SQL语句

Is there any way to have a Windows batch file directly input SQL statements without calling a script? I want the batch file to login to SQL and then enter in the statements directly.

有没有办法让Windows批处理文件直接输入SQL语句而不调用脚本?我希望批处理文件登录到SQL,然后直接输入语句。

EDIT: I'm using Oracle v10g

编辑:我正在使用Oracle v10g

7 个解决方案

#1

For a single command you can use this trick:

对于单个命令,您可以使用此技巧:

echo select * from dual; | sqlplus user/pw@db

#2

To run something on SQL server 2005/2008, you could use sqlcmd command line utility. sqlcmd -h prints the list of switches.

要在SQL Server 2005/2008上运行某些东西,可以使用sqlcmd命令行实用程序。 sqlcmd -h打印交换机列表。

#3

Short answer: No. Batch files by themselves can't do this.

简答:没有。批处理文件本身不能这样做。

Long answer: You may be able to come close, depending on which kind of database server you're using, and what the capabilities the commandline client provides.

答案很清楚:您可能会接近,具体取决于您使用的数据库服务器类型以及命令行客户端提供的功能。

What kind of database server are you using? Oracle, mySql, Sybase, Microsoft, Terradata, ???

你使用什么样的数据库服务器? Oracle,mySql,Sybase,Microsoft,Terradata,???

For example, with a Sybase database, you can use the isql commandline client to run from a batch file:

例如,对于Sybase数据库,您可以使用isql命令行客户端从批处理文件运行:

isql -S server -D database -U user -P password -i script

#4

You could use sqlcmd (for sql server) or System.Data.Odbc.OdbcCommand from powershell.

您可以使用来自powershell的sqlcmd(对于sql server)或System.Data.Odbc.OdbcCommand。

#5

The odbc command of the outwit tool suite allows you to run select statements on any database for which an appropriate ODBC data source has been defined.

outwit工具套件的odbc命令允许您在已定义适当ODBC数据源的任何数据库上运行select语句。

#6

You can use http://tekkies.co.uk/go/runsqloledb

您可以使用http://tekkies.co.uk/go/runsqloledb

e.g. RunSQLOLEDB "Provider=SQLOLEDB;Data Source=(local);..." "SELECT GetDate()" or RunSQLOLEDB @ConnectionString.txt @Query.sql

例如RunSQLOLEDB“Provider = SQLOLEDB; Data Source =(local); ...”“SELECT GetDate()”或RunSQLOLEDB @ ConnectionString.txt @ Query.sql

#7

Here is a rough example script for MSSQL which may be able to be modified for Oracle:

这是一个可以为Oracle修改的MSSQL的粗略示例脚本:

@ECHO off
SETLOCAL ENABLEDELAYEDEXPANSION
:: batch file for sql query
SET STARTDATE=20101010
SET ENDDATE=20111109
SET AGENCYNAME=Agency
SET DBNAME=AccidentDB

SET SQLSTRING=SELECT Acc.INC_ID,^
 Veh.MAKE, Veh.MODEL, Veh.LIC_NUM^
 FROM Acc,^
 lnk_Acc_Veh, Veh^
 WHERE     (INC_NUM LIKE '20115000%')^
 AND lnk_Acc_Veh.link_id=Veh.key^
 AND lnk_Acc_Veh.link_id=Acc.key^
 AND Acc.date ^> '%STARTDATE%' OR Acc.date ^< '%ENDDATE%';

CLS
@ECHO.
@ECHO.
@ECHO DBNAME is %DBNAME%
@ECHO.
@ECHO SQLSTRING is "!SQLSTRING!"
@ECHO.
@ECHO ------------------------------------------------------
@sqlcmd.exe -b -S localhost -E -d !DBNAME! -Q "!SQLSTRING!" -W
@ECHO.
@ECHO Report is done. Hit any key to close this window....
@pause>nul

#1