如何将变量从外壳脚本传递到sqlplus [英] how to pass variable from shell script to sqlplus

问题描述

我有一个调用file.sql的shell脚本

I have a shell script that calls file.sql

我正在寻找一种将一些参数传递给我的file.sql的方法.

I am looking for a way to pass some parameters to my file.sql.

如果我不将具有某个值的变量传递给sql脚本，则必须使用SELECT语句创建多个.sql文件，而更改的内容将是很少的单词.

If I don't pass a variable with some value to the sql script, I will have to create multiple .sql files with SELECT statement and all that would change is few words.

我的shell脚本调用file.sql:

My shell script calls file.sql:

sqlplus -S user/pass@localhost
echo " Processing triples"
./load_triples.sh BUILDING/Mapping_File BUILDING Y >> load_semantic.log

@/opt/D2RQ/file.sql
exit;
EOF

这是我的file.sql的样子:

And this is how my file.sql looks like:

SET ECHO ON;
SPOOL count.log

SELECT COUNT(*) as total_count
FROM TABLE(SEM_MATCH(
'{
        ?s rdf:type :ProcessSpec .
        ?s ?p ?o
}',SEM_Models('BUILDING'),NULL,
SEM_ALIASES(SEM_ALIAS('','http://VISION/DataSource/SEMANTIC_CACHE#')),NULL));

SPOOL OFF;

我可以修改我的shell脚本以使其传递变量名吗?

Can I modify my shell script so that it passes variable names?

即:模型=建筑物" 并将其传递给file.sql?

I.e.: model = "BUILDING" and pass this on to file.sql?

有没有类似的东西?

推荐答案

您似乎有一个heredoc，其中包含一个SQL * Plus命令，尽管它看起来不像注释中所指出的那样正确.您可以在heredoc:

You appear to have a heredoc containing a single SQL*Plus command, though it doesn't look right as noted in the comments. You can either pass a value in the heredoc:

sqlplus -S user/pass@localhost << EOF
@/opt/D2RQ/file.sql BUILDING
exit;
EOF

，或者如果脚本中BUILDING是$2:

sqlplus -S user/pass@localhost << EOF
@/opt/D2RQ/file.sql $2
exit;
EOF

如果您的file.sql在结尾处带有exit，那么它将变得更加简单，因为您不需要heredoc:

If your file.sql had an exit at the end then it would be even simpler as you wouldn't need the heredoc:

sqlplus -S user/pass@localhost @/opt/D2RQ/file.sql $2

然后在您的SQL中，您可以使用替换变量引用位置参数 /a>:

In your SQL you can then refer to the position parameters using substitution variables:

...
}',SEM_Models('&1'),NULL,
...

&1将被传递给SQL脚本BUILDING的第一个值替换；因为这是一个字符串，所以仍然需要将其括在引号中.如果在输出中显示替换项，则可能要set verify off停止.

The &1 will be replaced with the first value passed to the SQL script, BUILDING; because that is a string it still needs to be enclosed in quotes. You might want to set verify off to stop if showing you the substitutions in the output.

您可以传递多个值，并按顺序引用它们，就像在Shell脚本中使用位置参数一样-第一个传递的参数是&1，第二个传递的是&2，依此类推.您可以在以下位置的任何地方使用替换变量SQL脚本，因此它们可以毫无问题地用作列别名-您只需要小心添加一个额外的参数，即可将其添加到列表的末尾(这可能会使脚本中的编号乱序)或调整所有内容以使其匹配:

You can pass multiple values, and refer to them sequentially just as you would positional parameters in a shell script - the first passed parameter is &1, the second is &2, etc. You can use substitution variables anywhere in the SQL script, so they can be used as column aliases with no problem - you just have to be careful adding an extra parameter that you either add it to the end of the list (which makes the numbering out of order in the script, potentially) or adjust everything to match:

sqlplus -S user/pass@localhost << EOF
@/opt/D2RQ/file.sql total_count BUILDING
exit;
EOF

或:

sqlplus -S user/pass@localhost << EOF
@/opt/D2RQ/file.sql total_count $2
exit;
EOF

如果将total_count传递给您的Shell脚本，则只需使用其位置参数$4或其他任何参数即可.然后您的SQL将是:

If total_count is being passed to your shell script then just use its positional parameter, $4 or whatever. And your SQL would then be:

SELECT COUNT(*) as &1
FROM TABLE(SEM_MATCH(
'{
        ?s rdf:type :ProcessSpec .
        ?s ?p ?o
}',SEM_Models('&2'),NULL,
SEM_ALIASES(SEM_ALIAS('','http://VISION/DataSource/SEMANTIC_CACHE#')),NULL));

如果传递许多值，您可能会发现使用位置参数定义命名参数更加清晰，因此所有排序问题都在脚本开始时进行了处理，在此位置更易于维护:

If you pass a lot of values you may find it clearer to use the positional parameters to define named parameters, so any ordering issues are all dealt with at the start of the script, where they are easier to maintain:

define MY_ALIAS = &1
define MY_MODEL = &2

SELECT COUNT(*) as &MY_ALIAS
FROM TABLE(SEM_MATCH(
'{
        ?s rdf:type :ProcessSpec .
        ?s ?p ?o
}',SEM_Models('&MY_MODEL'),NULL,
SEM_ALIASES(SEM_ALIAS('','http://VISION/DataSource/SEMANTIC_CACHE#')),NULL));

---

从您单独的问题中，也许您只是想要:

---

From your separate question, maybe you just wanted:

SELECT COUNT(*) as &1
FROM TABLE(SEM_MATCH(
'{
        ?s rdf:type :ProcessSpec .
        ?s ?p ?o
}',SEM_Models('&1'),NULL,
SEM_ALIASES(SEM_ALIAS('','http://VISION/DataSource/SEMANTIC_CACHE#')),NULL));

...因此别名将与您要查询的值相同(答案的原始部分中的$2或BUILDING中的值).您可以根据需要多次引用替换变量.

... so the alias will be the same value you're querying on (the value in $2, or BUILDING in the original part of the answer). You can refer to a substitution variable as many times as you want.

如果您多次运行它，可能不容易使用，因为它会在输出的每一位中显示为计数值上方的标头.也许以后会更解析:

That might not be easy to use if you're running it multiple times, as it will appear as a header above the count value in each bit of output. Maybe this would be more parsable later:

select '&1' as QUERIED_VALUE, COUNT(*) as TOTAL_COUNT

如果您set pages 0和set heading off，则您的重复通话可能会出现在整洁的列表中.您可能还需要set tab off并可能使用rpad('&1', 20)或类似方法，以使该列始终具有相同的宽度.或通过以下方式获取CSV格式的结果:

If you set pages 0 and set heading off, your repeated calls might appear in a neat list. You might also need to set tab off and possibly use rpad('&1', 20) or similar to make that column always the same width. Or get the results as CSV with:

select '&1' ||','|| COUNT(*)

取决于您将结果用于...

Depends what you're using the results for...

这篇关于如何将变量从外壳脚本传递到sqlplus的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！