mysql 批处理文件传参,如何从Windows批处理文件到SQL传递变量* Plus的

I want to pass a variable from a Windows Batch File to SQLPLUS,

while displaying sql result batch variable should print along with the sql result.

Result should be stored in csv file.

How can I do that.

This is possible in Unix(shell script) how can I do that in Windows(batch script).

解决方案

I want to pass a variable from a Windows Batch File to SQLPLUS

Just pass it as an argument to the SQL script. And use substitution variables in the same order that of the arguments list &1 &2...

For example,

mybatchfile.BAT:

sqlplus -S username/password@sid

@c:\sql\mysqlfile.sql 1000 7369

mysqlfile.SQL:

update emp set sal = &1 where empno = &2

while displaying sql result batch variable should print along with the sql result.

To display the variables which you pass as arguments to the SQL script, first you need to define the bind variables, then assign the argument value and then print the value.

For example,

I have test.sql:

For NUMBER type

-- define the bind variable

var sal number

-- assign the first argument's value

exec :sal := &1

-- display the value

print :sal

-- define the bind variable

var empno number

-- assign the second argument's value

exec :empno := &2

-- display the value

print :empno

Now, let's test the script:

SQL> @D:\test.sql 1000 7369

PL/SQL procedure successfully completed.

SAL

----------

1000

PL/SQL procedure successfully completed.

EMPNO

----------

7369

SQL>

For STRING type

-- define the bind variable

var ename varchar2(10)

-- assign the argument's value

exec :ename := '&1'

-- display the value

print :ename

Now, let's test the script:

SQL> @D:\test.sql LALIT

PL/SQL procedure successfully completed.

ENAME

--------------------------------

LALIT

SQL>

Result should be stored in csv file.

Handle this in the SQL script. Use proper SQL*Plus formatting for comma separated result. To store the result set, you just need to SPOOL

For example,

set colsep , -- separate columns with a comma

set pagesize 0 -- No headers

set trimspool on -- remove trailing blanks

spool mycsvfile.csv

SELECT ....

spool off