有没有办法在没有用户输入的情况下在sqlplus脚本中设置参数的默认值?
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';
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>
当我被要求输入参数时,我只能为参数传递一个空值,或者我可以在脚本名称之后通过“”传递一个空参数.但这种行为非常烦人.某种IF定义的“& 1”将非常有用.
你是否有任何提示或技巧如何在sqlplus脚本中应用WHERE条件来定义或不使用不必要的用户交互?
解
根据马丁链接的文章,我修改了以前的脚本,无需参数值即可工作:
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
请阅读
“On SQL*Plus Defines”以获得问题的答案.