SQL 如何将参数传递给sql脚本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2236201/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can i pass parameter to sql script?
提问by user270203
CREATE TABLE DMS_POP_WKLY_REFRESH_20100201 NOLOGGING PARALLEL AS
SELECT wk.*,bbc.distance_km ,NVL(bbc.tactical_broadband_offer,0) tactical_broadband_offer ,
sel.tactical_select_executive_flag,
sel.agent_name,
res.DMS_RESIGN_CAMPAIGN_CODE,
pclub.tactical_select_flag
FROM spineowner.pop_wkly_refresh_20100201 wk,
dms_bb_coverage_102009 bbc,
dms_select_executive_group sel,
DMS_RESIGN_CAMPAIGN_26052009 res,
DMS_PRIORITY_CLUB pclub
WHERE wk.mpn = bbc.mpn(+)
AND wk.mpn = sel.mpn (+)
AND wk.mpn = res.mpn (+)
AND wk.mpn = pclub.mpn (+)
vijay.sql ( name given to above sql script)
vijay.sql ( 上面 sql 脚本的名称)
How can i pass a parameter for 20100201 in side the sql script, I need to change every time this date format. how can i call this sql script and run it in unix by passing date as parameter. so that date in the script should be modified in every run
我如何在 sql 脚本中传递 20100201 的参数，我每次都需要更改此日期格式。我如何调用这个 sql 脚本并通过将日期作为参数在 unix 中运行它。以便在每次运行时修改脚本中的日期
Please help me.
请帮我。
回答by Vincent Malgrat
SQL*Plus uses &1, &2... &n to access the parameters.
SQL*Plus 使用 &1、&2... &n 来访问参数。
Suppose you have the following script test.sql:
假设您有以下脚本test.sql：
SET SERVEROUTPUT ON
SPOOL test.log
EXEC dbms_output.put_line('&1 &2');
SPOOL off
you could call this script like this for example:
你可以这样调用这个脚本，例如：
$ sqlplus login/pw @test Hello World!
Edit:
编辑：
In a UNIX script you would usually call a SQL script like this:
在 UNIX 脚本中，您通常会像这样调用 SQL 脚本：
sqlplus /nolog << EOF
connect user/password@db
@test.sql Hello World!
exit
EOF
so that your login/password won't be visible with another session's ps
这样您的登录名/密码就不会在另一个会话中可见 ps
回答by jsampath
Two options save vijay.sql
两个选项保存vijay.sql
declare
begin
execute immediate
'CREATE TABLE DMS_POP_WKLY_REFRESH_'||to_char(sysdate,'YYYYMMDD')||' NOLOGGING PARALLEL AS
SELECT wk.*,bbc.distance_km ,NVL(bbc.tactical_broadband_offer,0) tactical_broadband_offer ,
sel.tactical_select_executive_flag,
sel.agent_name,
res.DMS_RESIGN_CAMPAIGN_CODE,
pclub.tactical_select_flag
FROM spineowner.pop_wkly_refresh_20100201 wk,
dms_bb_coverage_102009 bbc,
dms_select_executive_group sel,
DMS_RESIGN_CAMPAIGN_26052009 res,
DMS_PRIORITY_CLUB pclub
WHERE wk.mpn = bbc.mpn(+)
AND wk.mpn = sel.mpn (+)
AND wk.mpn = res.mpn (+)
AND wk.mpn = pclub.mpn (+)'
end;
/
The above will generate table names automatically based on sysdate. If you still need to pass as variable, then save vijay.sql as
以上将根据 sysdate 自动生成表名。如果您仍然需要作为变量传递，则将 vijay.sql 保存为
declare
begin
execute immediate
'CREATE TABLE DMS_POP_WKLY_REFRESH_'||&1||' NOLOGGING PARALLEL AS
SELECT wk.*,bbc.distance_km ,NVL(bbc.tactical_broadband_offer,0) tactical_broadband_offer ,
sel.tactical_select_executive_flag,
sel.agent_name,
res.DMS_RESIGN_CAMPAIGN_CODE,
pclub.tactical_select_flag
FROM spineowner.pop_wkly_refresh_20100201 wk,
dms_bb_coverage_102009 bbc,
dms_select_executive_group sel,
DMS_RESIGN_CAMPAIGN_26052009 res,
DMS_PRIORITY_CLUB pclub
WHERE wk.mpn = bbc.mpn(+)
AND wk.mpn = sel.mpn (+)
AND wk.mpn = res.mpn (+)
AND wk.mpn = pclub.mpn (+)'
end;
/
and then run as sqlplus -s username/password @vijay.sql '20100101'
然后作为 sqlplus -s username/password @vijay.sql '20100101' 运行
