如何将列表作为参数从Powershell插入SqlPlus

2024-05-10 • 问答

我在文件中有一个类似这样的查询：

script.sql

select * 
from test_table
where name in (&1)

这是我要查询的示例

我用一个像这样的Power Shell脚本执行它：

$names = '''Name1'',''Name2'''
$params = '"' + $names + '"'
sqlplus -silent $username/$password@$tnsalias "@script.sql" $params

请注意，$names具有可变的名称列表

但是在执行脚本时，参数将像这样被替换：

former : where name in (&1)
new    : where name in (Name1)

当然，SQL会引发错误。

为什么要这样替换参数？

我如何实现我想做的事情，因此第一个参数是将在where name in(&1)子句中使用的字符串列表。我的目标是将sql where name in ('Name1','Name2')

如果有帮助，可以在Oracle 11上执行SQL。

powershell示例

$username_dba = "system"
$password_dba = "manager"
$tnsalias_db = "es"
$names = "'''Name1'',''Name2'',''X'''"
$params = '"' + $names + '"'
$sqlfile = "@sqltest.sql"

Write-Host  $names
Write-Host  $params

C:\oracle\instantclient_11_2\sqlplus  $username_dba/$password_dba@$tnsalias_db  $sqlfile  $params

输出Powershell：

C:\upwork\stackoverflow\param_sql>powershell .\sql_param.ps1
'''Name1'',''X'''
"'''Name1'',''X'''"

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 15 11:46:49 2019

Copyright (c) 1982,2013,Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

old   1: select sysdate from dual where DUMMY in (&&1 )
new   1: select sysdate from dual where DUMMY in ('Name1','Name2','X' )

SYSDATE
---------
15-NOV-19

Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

bat文件示例。

@echo off
set user_name=system
set password=manageresmd
set net_service_name=esmd
set param1='''test1'',''test22'',''X'''

C:\oracle\instantclient_11_2\sqlplus.exe  %user_name%/%password%@%net_service_name%  @sqltest.sql %param1%
pause

输出蝙蝠文件：

C:\upwork\stackoverflow\param_sql>sqltest1.bat

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 15 11:50:58 2019

Copyright (c) 1982,Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

old   1: select sysdate from dual where DUMMY in (&&1 )
new   1: select sysdate from dual where DUMMY in ('test1','test22','X' )

SYSDATE
---------
15-NOV-19

Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

如何将列表作为参数从Powershell插入SqlPlus

qq394496014 回答：如何将列表作为参数从Powershell插入SqlPlus

大家都在问