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'
轉：
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'
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29519108/viewspace-2125801/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- sqlplus中define定義的常量和variable定義的變數的區別！SQL變數
- 在oracle sqlplus中通過new_value及define傳遞或定義變數variableOracleSQL變數
- PHP核心定義變數的方式PHP變數
- oracle 繫結變數（bind variable）Oracle變數
- smarty中三種變數的訪問方式變數
- python中定義引數的四種方式Python
- 向檢視輸出變數的三種方式變數
- PL/SQL 02 宣告變數 declareSQL變數
- C++巨集定義#defineC++
- 記住Python變數型別的三種方式Python變數型別
- Linux架構27 Ansible變數, 定義變數的方式, 變數註冊, facts快取Linux架構變數快取
- [SQL]oracle 繫結變數(bind variable)[轉載]SQLOracle變數
- C++中巨集定義#define的用法C++
- 059.Define 巨集定義的巢狀巢狀
- React元件方法的兩種定義方式React元件
- 碎片化學習Java（三）-- Java定義變數Java變數
- 變數的定義和使用變數
- Linux2.6.14.3核心中宏定義 #define DEFINE(轉)Linux
- define巨集定義和const常量定義之間的區別
- 簡單介紹SQLserver中的declare變數用法SQLServer變數
- 三種交換變數的方法變數
- 變數定義錯誤變數
- 三分鐘學會go語言的變數定義Go變數
- 預定義的SQL*Plus變數SQL變數
- Oracle的三種表連線方式Oracle
- oracle sqr之多個procedure過程變數傳遞及同名變數定義Oracle變數
- 三種改變 PriorityQueue 排序方式的辦法排序
- TCL: 變數和過程variable變數
- 【Oracle】利用線上重定義的方式改變普通表為分割槽表Oracle
- MySQL中變數的定義和變數的賦值使用MySql變數賦值
- Linux各種變數的含義Linux變數
- oracle客戶端連線server 端, tnsnames的三種設定方式Oracle客戶端Server
- PHP 定義常量 define 和 const的區別PHP
- php定義常量陣列有幾種方式PHP陣列
- Go定時器的三種實現方式Go定時器
- XSD中自定義型別的三種方式型別
- 【OH】SET System Variable Summary SQLPLUS 系統變數設定SQL變數
- C/C++定義全域性變數/常量幾種方法的區別C++變數