Oracle變數定義的三種方式(define,variable,declare)

Oracle變數定義的三種方式(define,variable,declare)
轉：

    1、define（即host變數）
    Host變數主要作用是起到一個替代變數的作用，是主機環境可以和oracle進行互動的一個媒介。 透過define定義host變數的時候必須指定變數名和變數的值，如果變數名已經存在於host變數中，則自動覆蓋，這個值不可以指定型別，一律按char儲存。 DEFINE 變數只在當前session環境中有效。

    (1).語法：
    define variable_name = value

    (2).宣告和初始化DEFINE變數
    //宣告define變數的時候必須同時初始化賦值變數
    SQL> define num=1;
    另外可以使用define命令顯示單個(define variable_name，不能顯示多個)或全部(define)的host變數的值和型別(型別都是char)。

    (3).顯示指定的DEFINE變數值和型別
    //使用define def_name命令顯示指定DEFINE變數的值和型別(DEFINE變數型別都為char)
    SQL> define num;
    DEFINE num              = "1" (CHAR)

    (4).顯示所有的DEFINE變數值和型別
    //使用define顯示所有DEFINE變數的值和型別(DEFINE變數型別都為char)
    SQL> define
    DEFINE _CONNECT_IDENTIFIER = "TOPPROD" (CHAR)
    DEFINE _SQLPLUS_RELEASE = "902000100" (CHAR)
    DEFINE _EDITOR         = "Notepad" (CHAR)
    DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Producti
    With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
    DEFINE _O_RELEASE      = "1102000300" (CHAR)

    瞭解對於host變數啟用和關閉的命令是：set define on和set define off。
    (5).關閉DEFINE變數定義功能
    //關閉define
    SQL> set define off;
    SQL> define num=1;
    SQL> select * from occ_file where rownum=#
    select * from occ_file where rownum=&num
    ORA-01008: 並非所有變數都已繫結

    (6).開啟DEFINE變數定義功能
    //開啟define
    SQL> set define on;
    SQL> define num=1;
    SQL> select * from occ_file where rownum=#
    OCC01      OCC02
    ---------- ---------------
    020040     松榮五金-SRWJ

    (7).引用DEFINE變數
    這在sqlplus和plsql developer的command window中都可以使用，在sql或plsql中引用host變數，使用&符號，相當於一個簡單的替換動作，比如
    //錯誤案例
    SQL> define x=occ_file;
    SQL> select * from '&x' where rownum=1;
    select * from 'occ_file' where rownum=1
    ORA-00903: 表名無效
    報錯是因為x被替換為occ_file，語句變為select * from ’occ_file’，’occ_file’表是不存在的，多了個單引號。

    //正確案例
    SQL> define x=occ_file;
    SQL> select * from &x where rownum=1;
    OCC01      OCC02
    ---------- ---------------
    020040     松榮五金-SRWJ

    (8).引用臨時DEFINE變數
    可以不預先宣告初始化define變數，直接在sql或plsql中在字串前面加&符號，會提示你輸入變數的值，然後替換。這種情況下是臨時的變數，define命令查不到。
    //不預先宣告和初始化define變數直接使用&引用
    SQL> select * from &y where rownum=1;
    //彈框提示輸入變數y的值，輸入occ_file,後列印出一下資訊
    OCC01      OCC02
    ---------- ---------------
    020040     松榮五金-SRWJ

    經常使用host變數的場合是在寫指令碼的時候，指令碼的有些地方經常變化，其他地方固定,那麼可以使用&引用。

==============================================================================================================================================
    2、Variable（即邦定變數）

    繫結變數主要是在sql中使用，達到sql解析一次，執行多次，從而提高效率的目的。繫結變數和host變數一樣，也有定義的，也有臨時性的。(臨時性的在動態sql或動態plsql中透過佔位符和using使用)，其他的如sql會自動使用繫結變數的情況，此處不專門討論。定義的繫結變數也是在當前session中有效。 繫結變數以下特點：

    繫結變數在sql和plsql中直接引用必須加字首 :。如要引用繫結變數a，則是 :a；
    在真正執行的PLSQL程式中，比如自動執行，有自己的一套機制；
    初始化和應用繫結變數,初始化定義的繫結變數，可以使用過程和函式，其中過程可以內部給繫結變數賦值、也可以引數是繫結變數，引數模式為out返回。使用函式和過程差不多，還可以使用call
 
    函式來賦值。
    sqlplus中可以使用大於等於3個字元表示一個命令，這裡我們用var，var命令是宣告一個繫結變數，只能給予名稱和型別，定義的時候不能賦值，賦值可以在plsql中或者採用函式賦值(而host變數定義的時候必須賦值)。

    (1).語法：
    var var_name type

    (2).宣告繫結變數
    //使用var宣告兩number型別的變數num1、num2
    SQL> var num1 number;
    SQL> var num2 number;

    (3).顯示指定繫結變數
    //var num1命令顯示指定繫結變數名稱、資料型別
    SQL> var num1
    variable num1
    datatype NUMBER

    (4).顯示所有繫結變數

    //var命令顯示所有繫結變數的變數名稱、資料型別
    SQL> var
    variable num1
    datatype NUMBER

    variable num2
    datatype NUMBER

    (5).使用PL/SQL初始化繫結變數
    //給繫結變數賦值
    SQL> begin
         :num1:=1;
         :num2:=2;
         end;
         /

    PL/SQL procedure successfully completed
    num1
    ---------
    1
    num2
    ---------
    2


    (6).使用EXECUTE初始化繫結變數
    //使用execute初始化，初始化多個用分號隔開
    SQL> exec :num1:=1;:num2:=2

    PL/SQL procedure successfully completed
    num1
    ---------
    1
    num2
    ---------
    2

    (7).顯示指定的繫結變數的值
    //使用print var_name命令輸出指定的繫結變數值
    SQL> print num1;
    num1
    ---------
    1

    SQL> print num2;
    num2
    ---------
    2

    (8).顯示所有繫結變數的值
    //print 命令輸出所有繫結變數的值
    SQL> print
    num1
    ---------
    1

    num2
    ---------
    2

    (9).引用繫結變數
    //使用:var_name引用繫結變數
    SQL> select :num1 from dual;

         :NUM1
    ----------
             1
    num1
    ---------
    1

    (10).儲存過程初始化繫結變數
    //宣告繫結變數m
    SQL> var m number;
    //建立一個帶輸出引數的儲存過程test_pro
    SQL> create or replace procedure test_pro(num out number)
         as
         begin
         num:=10;
         end;
         /

    Procedure created

    //使用儲存過程返回變數(引用繫結變數記得帶上:)
    SQL> exec test_pro(num=>:m);
    PL/SQL procedure successfully completed
    m
    ---------
    10

    //輸出繫結變數m的值
    SQL> print m
    m
    ---------
    10

    (11).遊標初始化繫結變數
    繫結變數是REFCURSOR型別。一個引數使用refcursor，透過繫結變數型別定義為REFCURSOR，然後傳入過程，列印繫結變數(效果和查詢語句一樣)。
    //宣告遊標型別的繫結變數
    //注意：在plsql dev的command window無法使用以下宣告，需要再sqlplus中才有效
    SQL> var curinfo refcursor

    //建立帶輸出引數，引數型別為sys_refcursor的儲存過程
    SQL> create or replace procedure cur_pro(cur_msg out sys_refcursor)
         as
         begin
         open cur_msg for select occ01,occ02 from occ_file where rownum<3;
         end;
         /

    Procedure created

    //exec執行儲存過程
    SQL> exec cur_pro(cur_msg=>:cur_info);
    PL/SQL 過程已成功完成。

    //輸出繫結變數cur_info的值
    SQL> print cur_info

    OCC01          OCC02
    ---------------------------
    020040         松榮五金-SRWJ
    ---------------------------
    020041         路得記-LDJ

    由上面可以知道，print可以直接把refcursor的結果列印出來，不需要迭代查詢。

    (12).函式初始化繫結比那裡

    //建立函式
    SQL> create or replace function test_fun(a in varchar2,b in varchar2) return varchar2
         as
         c varchar2(255);
         begin
         c:=a||b;
         return c;
         end;
         /

    Function created

    //宣告繫結變數fun_info
    SQL> var fun_info varchar2(255);


    //使用call呼叫函式將返回值賦值給繫結變數
    SQL> call test_fun('a','b') into :fun_info;

    Method called
    fun_info
    ---------
    ab

    //顯示繫結變數的值

    SQL> print fun_info
    fun_info
    ---------
    ab

    當然也可以將函式的引數定義為out模式來初始化，我們這裡使用call命令呼叫函式把結果傳給繫結變數，

    語法：

    call function(引數列表) into ：繫結變數

    pl/sql中的引數和定義的變數(包括全域性變數和臨時變數)都會內部轉為繫結變數，所以儘量在pl/sql中儘量使用靜態sql，而不要使用動態sql，如果使用動態sql，要儘量加上繫結變數。

    (13).繫結變數的基本作用
    繫結變數主要是sql的執行過程中，在解析sql之後會進行共享池(SGA)的檢查，看最佳化器有沒有分析最佳化過這個sql，環境必須完全一致才可以（包括大小寫的一致,session情況一致等）。那麼可以達到一次分析，多次執行的目的，這就是軟解析，否則要經過解析，最佳化，行資源生成等一系列sql執行的過程，因為sql最佳化需要耗費很多資源，如果硬解析，sql效能會下降。
    如果查詢一跑幾個小時，根本沒必要做繫結變數，因為解析的消耗微乎其微，而且繫結變數對最佳化器判斷執行路徑也有負面影響。

    看SGA中的sql是否是軟解析被呼叫多次，可以檢視vsql或vsqlarea檢視，檢視列sql_text,executions,如：

    select sql_text,executions from v$sql where sql_text like '%trademark%';

    SQL_TEXT                              EXECUTIONS<被執行的次數>
    select * from trademark where id=:tid        6

===================================================================================================================================
    3、declare

在sql語句中新增變數。
declare @local_variable data_type

宣告時需要指定變數的型別，可以使用set和select對變數進行賦值，在sql語句中就可以使用@local_variable來呼叫變數。
 
宣告中可以提供值，否則宣告之後所有變數將初始化為NULL。

例如：declare @id int
         declare @id int = 3
         set @id=2
         select id
         select @id = column_id from table where column_user = 'ABC'