--sqlplus之提示,用于功能注解
SQL> prompt
SQL> prompt choose a value
choose a value
SQL>
SQL> desc t_sqlplus;
Name Null? Type
----------------------------------------- -------- --------------
A NUMBER(38)
B NUMBER(38)
SQL> select * from t_sqlplus;
A B
---------- ----------
1 1
2 2
--new_value即把列值作为一个变量保存起来,此列变量名为a_var
SQL> column a new_value a_var noprint
---ttitle为标题;本例为a列标题,后跟上述的column的变量a_var,skip表示本标题行与下面的行的间隔行是几行
SQL> ttitle left 'a value :' a_var skip 2
SQL> break on a skip page
SQL> btitle off
SQL> select a,b from t_sqlplus;
a value : 1
B
----------
1
a value : 2
B
----------
2
SQL>
--为如下的 from dual别名初始化变量dflt_dir
column dflt_dir new_value dflt_dir noprint;
select '' dflt_dir from dual;
set termout on;
--提示作用
prompt
prompt Choose a Directory Name from the list above (case-sensitive).
prompt
set heading off;
--格式化如下from dual的别名列
column directory_name new_value directory_name noprint;
--巧用nvl初始化directory_name值,nvl引用了上述的变量dflt_dir,在此为配置指定导出的目录,其实就是用了&,会弹出交互窗口,让输入变量的值
--如下为若变量directory_name为空,则用变量dlft_dir初始化变量
select 'Using the dump directory: ' || nvl('&&directory_name','&dflt_dir')
, nvl('&&directory_name','&dflt_dir') directory_name
from sys.dual;
--示例:
SQL> column a new_value a_var noprint
SQL> select '' a_var from dual;
A
-
SQL> select nvl('&&value1','&a_var') from dual;
Enter value for value1: zxy
Enter value for a_var:
old 1: select nvl('&&value1','&a_var') from dual
new 1: select nvl('zxy','') from dual
NVL('Z
------
zxy
--再次运行,则引用上次变量的值,仍是zxy;这便是&&的作用
SQL> select nvl('&&value1','&a_var') from dual;
Enter value for a_var:
old 1: select nvl('&&value1','&a_var') from dual
new 1: select nvl('zxy','') from dual
NVL('Z
------
zxy
--即便你多次运行,还是首次提供哪个值;
SQL> select nvl('&&value1','&a_var') from dual;
Enter value for a_var: abc
old 1: select nvl('&&value1','&a_var') from dual
new 1: select nvl('zxy','abc') from dual
NVL('Z
------
zxy
小结:1,若给&&初始提供值,则多次运行,仍沿用首次的值
2, 若未给&&提供值,则采用&a_var变量的值;此之前已经提供了值
--小结:&&表示为在sqlplus同一个变量;而&变示不同的变量,即便变量名称相同
SQL> select 1+ &v+ &v from dual;
Enter value for v: 3
Enter value for v: 4
old 1: select 1+ &v+ &v from dual
new 1: select 1+ 3+ 4 from dual
1+3+4
----------
8
SQL> select 1+&&v+&&v from dual;
Enter value for v: 2
old 1: select 1+&&v+&&v from dual
new 1: select 1+2+2 from dual
1+2+2
----------
5
--如下为一个sql脚本示示例:
prompt
column a new_value a_var noprint;
select 'zxy' a_var from dual;
var x varchar2(30);
begin
:x:='&a_var';
dbms_output.put_line(:x||'haha');
end;
/
undefine a_var
小结:必须在脚本最后用undefine取消变量定义,不然脚本运行就会产生异常
sql脚本中的var变量必须在begin and中加工,不然报错
--上述脚本的修正,经过很久排查,变量x未引用new_value变量的原因是new_value的变量名必须与select 列名相同,要不同,则不会引用new_value变量的值
prompt
prompt
prompt .....creating user_name user
set serveroutput on
rem create user user_name identified by system;
rem alter user user_name account unlock;
rem grant resource,connect to user_name;
prompt
column a new_value a noprint;
select 'zxy' a from dual;
var x varchar2(30);
begin
:x:='&&a';
dbms_output.put_line(:x||'haha');
end;
/ --这个符号不能少,一旦没有,脚本运行就始终卡在这儿,其实相当于终止符
exec dbms_output.put_line('non include begin end');--加在exec则plsql内置包或过程可以在begin end之外运行
undefine a_var
--运行结果:
SQL> @c:\learn.sql
.....creating user_name user
old 2: :x:='&&a';
new 2: :x:='zxy';
zxyhaha
PL/SQL procedure successfully completed.