sqlplus之prompt_&&_&_new_value

--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.