oracle 未通过 SQLPlus 脚本的参数的默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13474899/
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
Default value for paramteters not passed SQLPlus script
提问by adrive
Is there a way to set default value of paramter in sqlplus script without user input?
有没有办法在没有用户输入的情况下在 sqlplus 脚本中设置参数的默认值？
For example, I have an SQL script sessions.sql:
例如，我有一个 SQL 脚本 session.sql：
SET VERIFY OFF
SET TERMOUT OFF
DEFINE uname = '&1'
COLUMN search_uname new_value search_uname
SELECT CASE WHEN '&uname' = '' THEN '%' ELSE UPPER('&uname') END AS search_uname
FROM dual;
SET TERMOUT ON
SELECT sid, serial, username FROM v$session WHERE username LIKE '&search_uname';
And I want to invoke it from sqlplus like this:
我想像这样从 sqlplus 调用它：
SQL> @sessions
Enter value for 1:
SID SERIAL# USERNAME
---------- ---------- ------------------------------
56 20577 CONTEXT
.....
236 rows selected.
SQL> @sessions ""
SID SERIAL# USERNAME
---------- ---------- ------------------------------
56 20577 CONTEXT
.....
236 rows selected.
SQL> @sessions SDE
SID SERIAL# USERNAME
---------- ---------- ------------------------------
113 56675 SDE
165 64881 SDE
.....
43 rows selected.
SQL>
I can only pass an empty value for parameter when I am asked to enter it, or I am able to pass an empty parameter after script name through "". But this behaviour is very annoying. Some kind of IF DEFINED "&1" will be very usefull.
当我被要求输入参数时，我只能为参数传递一个空值，或者我可以在脚本名称后通过“”传递一个空参数。但是这种行为很烦人。某种 IF DEFINED "&1" 将非常有用。
Do you have any tips or tricks how this should be achieved to apply WHERE conditions in sqlplus script wheter parameter is defined or not without unnecessary user interaction?
您是否有任何提示或技巧，应该如何实现在 sqlplus 脚本中应用 WHERE 条件，无论是否定义参数，而无需进行不必要的用户交互？
Solution
解决方案
According to the article linked by Martin I modified previous script to be working without aksing for parameter values:
根据 Martin 链接的文章，我修改了以前的脚本，使其无需请求参数值即可工作：
SET VERIFY OFF
SET TERMOUT OFF
column 1 new_value 1
SELECT '' "1" FROM dual WHERE ROWNUM = 0;
define uname = '&1'
SET TERMOUT ON
SELECT sid, serial#, username FROM v$session
WHERE username LIKE UPPER(DECODE('&uname', '', '%', '&uname'));
undefine 1
采纳答案by Martin Schapendonk
Please read "On SQL*Plus Defines"for an answer to your question.
请阅读“关于 SQL*Plus 定义”以回答您的问题。
回答by Alain Pannetier
For those who don't fancy chasing and perusing links that can go away anytime, here's a quick cut'n paste snippet.
对于那些不喜欢追逐和阅读随时可能消失的链接的人，这里有一个快速剪切的粘贴片段。
set termout on
set serveroutput on
set feedback off
set verify off
-- start
column 1 new_value 1 noprint
select '' "1" from dual where rownum = 0;
define param = &1 "default"
-- end
begin
dbms_output.put_line ( 'Param 1 value is ¶m' );
end;
/
exit 0
/
Execution:
执行：
$ sqlplus -s SCOTT/TIGER@ORCL @a.sql
Param 1 value is default
$ sqlplus -s POSF/POSF@ECMDB @a.sql nondef
Param 1 value is nondef
$ sqlplus -s SCOTT/TIGER@ORCL @a.sql
Param 1 值为默认值
$ sqlplus -s POSF/POSF@ECMDB @a.sql nondef
Param 1 值为 nondef
