sqlplus 传递一个包含 ' 的字符串'例如“索引1”、“索引2”、“索引3”

发布于 2024-10-02 21:37:02 字数 524 浏览 7 评论 0原文

我需要将此值传递给我的 sql 文件，因为我正在执行带有 IN 的 where 条件。例如：Delete FROM table WHERE col IN ('index1','index2','index3')

遇到问题

set INDEXES = 'index1','index2','index3'
sqlplus script %INDEXES%

当我尝试使用 sqlplus 命令从 cmd 调用此 sql 文件时 ,只有index1通过还是有问题我尝试这样做

set INDEXES = "'index1','index2','index3'"
sqlplus script %~INDEXES%

，但也有一个问题

这是我的sql：

Delete FROM table WHERE col IN (&1)

你知道我如何成功传递我需要的字符串吗？谢谢

原文

I need to pass this value to my sql file cause I am executing then a where condition with a IN.
For instance : Delete FROM table WHERE col IN ('index1','index2','index3')

I have an issue when I try to call this sql file from cmd using sqlplus command

set INDEXES = 'index1','index2','index3'
sqlplus script %INDEXES%

When I do that, only index1 is passed or there is a problem
I tried to do that

set INDEXES = "'index1','index2','index3'"
sqlplus script %~INDEXES%

but there is a problem too

Here is my sql:

Delete FROM table WHERE col IN (&1)

Do you have any idea how I can successfully pass the string I need ?
thank you

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

乖乖 2024-10-09 21:37:02

Oracle 没有附带内置的字符串标记生成器。所以，我们必须建立自己的。 SO 有几种不同的解决方案。这是我发布的，它将工作在 10g 或更高。对于早期版本，请尝试这个。

回复收藏 0 原文

作业与我同在 2024-10-09 21:37:02

事实上，你的技术是正确的。

sqlplus scott/tiger @script.sql "'index1','index2','index3'"

其中 script.sql 为：

Delete FROM table WHERE col IN (&1)

将导致 &1 被逐字替换为 'index1','index2','index3'，从而导致 sqlplus 执行

Delete FROM table WHERE col IN ('index1','index2','index3')

：我看到的问题是删除语句不以分号结尾并且脚本不提交/退出（也许这些只是在您的帖子中排除）。

因此，如果您的命令行正确插入环境变量，则会

set INDEXES = "'index1','index2','index3'"
sqlplus scott/tiger @script.sql %~INDEXES%

产生与我评论中的第一个命令相同的命令。

查看 sqlplus 对命令行参数执行的操作的一个简单方法是将 prompt 添加到脚本中 delete 行的开头：

prompt Delete FROM table WHERE col IN (&1)

Actually, your technique is correct.

sqlplus scott/tiger @script.sql "'index1','index2','index3'"

where script.sql is:

Delete FROM table WHERE col IN (&1)

will result in &1 being replaced, verbatim, with 'index1','index2','index3', resulting in sqlplus executing:

Delete FROM table WHERE col IN ('index1','index2','index3')

The problem i see is that the delete statement doesn't end in a semi-colon and the script doesn't commit/exit (maybe those were just excluded in your post).

So it follows that, if your command-line properly interpolates environment variables, then

set INDEXES = "'index1','index2','index3'"
sqlplus scott/tiger @script.sql %~INDEXES%

results in the same command as the first in my comment.

An easy way to see what sqlplus is doing with the command-line parameters is to simply add prompt to the beginning of the delete line in your script:

prompt Delete FROM table WHERE col IN (&1)

回复收藏 0 原文

云巢 2024-10-09 21:37:02

我会将其视为列表问题中的变量。这些可能很棘手，并且答案会根据您可以访问的 Oracle 版本而有所不同，

create table aaa(aaa varchar2(50));
insert into aaa values('index1');
insert into aaa values('index2');
insert into aaa values('index3');
insert into aaa values('index4');
insert into aaa values('index5');


declare
 pindexes varchar2(100) ;
begin
 pindexes := 'index1,index2,index3';



 delete aaa where aaa in (
         select substr(pindexes,
                          loc+1,
                          nvl(
                                lead(loc) over (order by loc) - loc-1,
                                length(pindexes) - loc)
                 )
        from   (
                  select distinct (instr(pindexes, ',', 1, level)) loc
                  from   dual
                  connect by level < length(pindexes)
                 )
 ); 
 end ;
/


select * from aaa;
/
--drop table aaa;

您只需将字符串作为 'index1,index2,index3' 传递即可，

这应该可以在 9i+ 上运行
http://asktom.oracle。 com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425

I would look at this as a variable in list question. These can be tricky and the answer varies based on the version of Oracle you have access to

create table aaa(aaa varchar2(50));
insert into aaa values('index1');
insert into aaa values('index2');
insert into aaa values('index3');
insert into aaa values('index4');
insert into aaa values('index5');


declare
 pindexes varchar2(100) ;
begin
 pindexes := 'index1,index2,index3';



 delete aaa where aaa in (
         select substr(pindexes,
                          loc+1,
                          nvl(
                                lead(loc) over (order by loc) - loc-1,
                                length(pindexes) - loc)
                 )
        from   (
                  select distinct (instr(pindexes, ',', 1, level)) loc
                  from   dual
                  connect by level < length(pindexes)
                 )
 ); 
 end ;
/


select * from aaa;
/
--drop table aaa;

this way you just pass in your string as 'index1,index2,index3'

this should work 9i+
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425

回复收藏 0 原文

~没有更多了~