oracle中的define，declare，variable的区别

1：define（即host变量） 
Host变量主要作用是起到一个替代变量的作用，是主机环境可以和oracle进行交互的一个媒介 
通过define定义host变量的时候必须指定变量名和变量的值，如果变量名已经存在于host变量中，则自动覆盖，这个值不可以指定类型，一律按char存储。 

定义的格式是：define variable_name = value（必须定义的时候就赋值,否则define variable_name是显示已经存在的host变量值，不存在会报告未定义。）    

另外可以使用define命令显示单个(define variable_name，不能显示多个)或全部(define)的host变量的值和类型(类型都是char)。 
使用 
首先要了解host变量只是在当前session环境中有效。了解对于host变量启用和关闭的命令是：set define on和set define off。这在sqlplus和plsql developer的command window中都可以使用，在sql或plsql中引用host变量，使用&符号，只是做一个简单的替换动作，比如 
define x=TEST 
Select * from ‘&x’;那么会报表找不到的错误，因为x被替换为test，那么’TEST’表是不存在的，TEST表存在，多了个单引号。 
也可以不定义，直接在sql或plsql中在字符串前面加&符号，会提示你输入变量的值，然后替换。这种情况下是临时的变量，define命令查不到。 

※经常使用host变量的场合是在写脚本的时候，脚本的有些地方经常变化，其他地方固定,那么可以使用&引用。 

2，Variable（即绑定变量） 
定义： 
    绑定变量主要是在sql中使用，达到sql解析一次，执行多次，从而提高效率的目的。绑定变量和host变量一样，也有定义的，也有临时性的。(临时性的在动态sql或动态plsql中通过占位符和using使用)，其他的如sql会自动使用绑定变量的情况，此处不专门讨论。定义的绑定变量也是在当前session中有效。 

Sqlplus中可以使用大于等于3个字符表示一个命令，这里我们用var，var命令是声明一个绑定变量，只能给予名称和类型，定义的时候不能赋值，赋值可以在plsql中或者采用函数赋值(而host变量定义的时候必须赋值)。 
Var var_name type   声明一个指定类型的绑定变量，类型上面列表中已经列出。 

对于var或var var_name只是显示所有的绑定变量或者指定名称的绑定变量的name和type，不显示其值，显示其值用print命令，可以采用help print查看。 

Print是输出所有绑定变量的值，print name1 name2 ….是输出指定的绑定变量的值，多个用空格分割。对于host变量的define要么输出全部，要么输出单个，没有指定数目的。Print后面的绑定变量名也可以加前缀冒号，效果一样。 

例： 
使用： 
1.        绑定变量在sql和plsql中直接引用必须加前缀 :。如要引用绑定变量a，则是 :a。 
2.        在真正运行的PLSQL程序中，比如自动运行，有自己的一套机制。 
3.        初始化和应用绑定变量,初始化定义的绑定变量，可以使用过程和函数，其中过程可以内部给绑定变量赋值、也可以参数是绑定变量，参数模式为out返回。使用函数和过程差不多，还可以使用call 函数来赋值，另外还可以使用SQLPLUS中的execute命令直接赋值,多个绑定变量用分号隔开。 
4.        注意NULL值不可赋值给绑定变量，这在NDS中也成立。 

使用过程和sqlplus命令初始化绑定变量： 


WORKGROUP\DINGJUN-PC>begin 
  2  :a := 1; 
  3  end; 
  4  / 

PL/SQL 过程已成功完成。 
WORKGROUP\DINGJUN-PC>print a 

         A 
-------------------------------------- 
         1 

WORKGROUP\DINGJUN-PC>select :a from dual; 

        :A 
--------------------------------------- 
         1 

###使用execute初始化，初始化多个用分号隔开 
SQL> var x number; 
SQL> var y number; 
SQL> exec :x :=1; :y :=2; 
PL/SQL procedure successfully completed 
x 
--------- 
1 
y 
--------- 
2 

下面看下NULL值赋值给绑定变量，会报错 
WORKGROUP\DINGJUN-PC>exec :a:='aa',:b:=null; 
BEGIN :a:='aa',:b:=null; END; 

再如下面的例子，使用过程返回绑定变量，定义一个Var x number; 


WORKGROUP\DINGJUN-PC>var x number; 
WORKGROUP\DINGJUN-PC>Create or replace procedure pt( m out number) 
  2   As 
  3  Begin 
  4  m:=10; 
  5  End; 
  6  / 

过程已创建。 

WORKGROUP\DINGJUN-PC>exec pt(m =>:x); 

PL/SQL 过程已成功完成。 

WORKGROUP\DINGJUN-PC>print x 

         X 
------------------------- 
        10 

绑定变量是REFCURSOR类型。一个参数使用ref cursor，通过绑定变量类型定义为REFCURSOR，然后传入过程，打印绑定变量(效果和查询语句一样)。 

WORKGROUP\DINGJUN-PC>var x refcursor; 
WORKGROUP\DINGJUN-PC>create or replace procedure pt(cur out SYS_REFCURSOR) 
  2  AS 
  3   BEGIN 
  4      OPEN cur for select *  from test where rownum<3; 
  5  END; 
  6  / 
过程已创建。 

WORKGROUP\DINGJUN-PC>exec pt(cur =>:x); 
PL/SQL 过程已成功完成。 
WORKGROUP\DINGJUN-PC>print x 
SJ           ID 
---- ---------- 
01            1 
02            2 

由上面可以知道，print可以直接把refcursor的结果打印出来，不需要迭代查找。 
下面看看如何用函数初始化定义的绑定变量： 
WORKGROUP\DINGJUN-PC>create or replace function concatestring(a in varchar2, b in varchar2) return varchar2 
  2  as 
  3  c varchar2(100); 
  4  begin 
  5  c := a||b; 
  6  return c; 
  7  end; 
  8  / 

函数已创建。 

WORKGROUP\DINGJUN-PC>variable x varchar2(10); 
WORKGROUP\DINGJUN-PC>call concatestring('a','b') into :x; 

调用完成。 

WORKGROUP\DINGJUN-PC>print x 

X 
---------------------------------------------------------------- 
ab 
        
当然也可以将函数的参数定义为out模式来初始化，我们这里使用call命令调用函数把结果传给绑定变量，调用格式为：call function(参数列表) into ：绑定变量。 

pl/sql中的参数和定义的变量(包括全局变量和临时变量)都会内部转为绑定变量，所以尽量在pl/sql中尽量使用静态sql，而不要使用动态sql，如果使用动态sql，要尽量加上绑定变量。 

绑定变量的基本作用 

绑定变量主要是sql的执行过程中，在解析sql之后会进行共享池(SGA)的检查，看优化器有没有分析优化过这个sql，环境必须完全一致才可以（包括大小写的一致,session情况一致等）。那么可以达到一次分析，多次执行的目的，这就是软解析，否则要进过解析，优化，行资源生成等一系列sql执行的过程，因为sql优化需要耗费很多资源，如果硬解析，sql性能会下降。 



看SGA中的sql是否是软解析被调用多次，可以查看v$sql或v$sqlarea视图，查看列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

 是用在匿名pl/sql块中的，用法如下:

--学习定义变量（了解数据类型）及赋值
--了解：各种PL/SQL块的类型

--块的结构
--案例：根据工号，输出员工姓名
SELECT * FROM emp;

--块！
DECLARE
  v_empno NUMBER(4);
  v_ename VARCHAR2(10);
BEGIN
   v_empno := &请输入工号;
   --必须要将查询结果放到变量
   SELECT ename INTO v_ename FROM emp WHERE empno=v_empno;
   dbms_output.put_line(v_ename);
END;

--异常
DECLARE
  v_empno NUMBER(4);
  v_ename VARCHAR2(10);
BEGIN
   v_empno := &请输入工号;
   --必须要将查询结果放到变量
   SELECT ename INTO v_ename FROM emp WHERE empno=v_empno;
   dbms_output.put_line(v_ename);
  
   EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line('执行出错了，老板！');
END;

--小结
DECLARE
   --变量区(不能在begin..end中定义）
BEGIN
    --业务代码区
    excetipn
    --异常捕获区
END;  

--定义变量（了解数据类型）
--2种：保存一个值＝简单变量；保存多个值＝复合变量

--简单变量类型:char,varchar2,number,date(同时也列类型),boolean,表.字段%type
--复合变量:表%rowtype,record
DECLARE
  v_empno NUMBER(4);
  v_ename VARCHAR2(10);
  v_job emp.job%TYPE; --如果字段类型改变，脚本不需要改变
BEGIN
   v_empno := 7566;
   SELECT ename,job INTO v_ename,v_job FROM emp WHERE empno=v_empno;
   dbms_output.put_line(v_ename);
   IF(v_job='MANAGER')THEN
      dbms_output.put_line('经理，吃过饭没。');
   END IF;
END;

--需要同上，将多个变量放到一个记录对象中
DECLARE
  v_empno NUMBER(4);
  my_row emp%ROWTYPE;
BEGIN
   v_empno := 7566;
   SELECT * INTO my_row FROM emp WHERE empno=v_empno;
   dbms_output.put_line(my_row.ename||','||my_row.sal);
END;

--有时不需要一整列，只需要其中几个列。此时用record
DECLARE
    --自定义复合类型 my_emp
   TYPE my_emp_type IS RECORD(
      yg_name emp.ename%TYPE,
      yg_sal emp.sal%TYPE
   );
   my_row my_emp_type; --变量名  数据类型
BEGIN
   SELECT ename,sal INTO my_row FROM emp WHERE empno=7788;
   dbms_output.put_line(my_row.yg_name||','||my_row.yg_sal);
END;

--扩展：Oracle没有boolean类型？
--思考：性别 true=男,false=女。缺点？性别＝保密。所以Oracle建立用char(1)替代，1＝男2＝女3＝保密4＝人妖

--Oracle的各种集合类型，对应 java的数组。
--引用变量：在讲解存储过程以后再讲解。

--绑定变量(命令窗口中执行，绑定变量用了 :）
SQL> var name varchar2(10)
SQL> execute :name :='hello';
PL/SQL procedure successfully completed
name
---------
hello

SQL> print name;
name
---------
hello