ORACLE中声明变量：define variable declare

在sqlplus 环境中，声明变量的关键字：define variable declare

一、define关键字（host变量）

host变量的作用是一个替换作用，是主机环境与oracle进行交互的变量，定义host变量时必须同时指定变量名和变量的值，定义变量不可以指定数据类型，define定义的变量默认其数据类型都是char。该变量只在当前session起作用

1、定义语法：

define var_name =value

2、define命令

>查看已定义的所有变量及其值：define

SQL> define

DEFINE _DATE = "10-3月 -00" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)

DEFINE _USER = "user" (CHAR)

DEFINE _PRIVILEGE = "" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)

DEFINE _EDITOR = "Notepad" (CHAR)

DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)

DEFINE _O_RELEASE = "1102000100" (CHAR)

>查看已定义的某一个变量

SQL> define num

DEFINE NUM = "3" (CHAR)

3、变量的引用

定义变量后，可在sql语句中直接使用，需要在变量前加“&”符号

4、关闭打开define变量定义。

SQL> set define on

SQL> define b=10

SQL> select * from user_tables where rownum=&b;

原值 1: select * from user_tables where rownum=&b

新值 1: select * from user_tables where rownum=10

SQL> set define off;

SQL> define a=10

SQL> select * from user_tables where rownum=&a;

SP2-0552: 未声明绑定变量 "A"。

5、临时变量

上述是显示的定义变量方式，另一种是隐式定义的临时变量，临时变量只是当前sql语句可用。

SQL> select * from user_tables where rownum=&var;

输入 var 的值: 10

原值 1: select * from user_tables where rownum=&var

SQL> select * from user_tables where rownum=&var;

输入 var 的值:

二、variable关键字

variable定义的变量即绑定变量，一般用于存储过程有out类型的参数时。定义时需要定于变量的数据数据类型，支持的数据类型如下。同define一样，只在当前会话中有效。

SQL> help variable

VARIABLE

--------

Declares a bind variable that can be referenced in PL/SQL, or

lists the current display characteristics for a single variable

or all variables.

VAR[IABLE] [variable [type]]

where type represents one of the following:

NUMBER CHAR CHAR (n [CHAR|BYTE])

NCHAR NCHAR (n) VARCHAR2 (n [CHAR|BYTE])

NVARCHAR2 (n) CLOB NCLOB

REFCURSOR BINARY_FLOAT BINARY_DOUBLE

1、定于语法：

var[iable] var_name type;

简写var，（sqlplus支持不少于三个字符的表示命令）。var定于的变量，必须同时指定数据类型，定义时var变量不能赋值。

2、引用

引用var定义变量，需要在前面加“：”。

3、var命令，同define。

4、var变量的初始化。

使用存储过程初始化、函数初始化。

call命令调用函数把结果传给绑定变量，

函数初始化：call function(参数列表) into ：绑定变量1

存储过程初始化:exec pro_name(var_name=>:绑定变量）；

三、declare--声明部分

plsql程序块的声明部分，内部变量，只在当前程序块有效。常量、变量、游标的声明都在declare部分。

Oracle定义变量的方式有3种：

声明并使用变量；

使用%TYPE声明变量类型；

使用%ROWTYPE声明变量类型

可以通过sys登录，使用"v$reserved_words"数据字典查看全部关键字

声明并使用变量

变量声明基本规则：

PL/SQL是强类型语言，变量都必须在它声明之后才可以使用；

变量不区分大小写；

变量都要在DECLARE部分声明；

变量名称可以由字母，数字，_（下划线），$，#组成；

所有的变量名称要求以字母开头，不能是Oracle保留字（关键字）；

变量的长度最多30个字符；

所有的变量均为局部作用域，它们只在给定的PL/SQL块中有效；

变量的默认值为NULL；

示例：

定义变量不设置默认值

DECLARE

v_result VARCHAR2(30) ; -- 此处没有赋值

BEGIN

DBMS_OUTPUT.put_line('v_result的内容〖' || v_result || '〗') ;

END ;

/

输出结果为空

变量声明语法：

变量名称 [constant] 类型 [not null] [:=value];

语句解析：

constant：定义常量，必须在声明时为其赋予默认值；

not null：表示此变量不允许为null;

:=value：在变量声明示，设置初始数据；

示例：定义变量

v_resultA NUMBER := 100 ; -- 定义一个变量同时赋值

v_resultB NUMBER ; -- 定义一个变量没有设置内容

v_resultb := 30 ; -- 没有区分大小写

DBMS_OUTPUT.put_line('计算的结果是：' || (v_resultA + v_resultB) ) ;

运行结果：130

分析：此程序采用了两种方式为声明的变量进行赋值：

1.在DECLARE中定义变量的时候直接赋值：v_resultA number := 100;

2.在DECLARE中定义变量，而后在BEGIN中为变量赋值：v_resultb:=30;

3.v_resultB在赋值时写成了v_resultb依然不影响执行结果；

定义非空变量：

v_resultA NUMBER NOT NULL := 100 ; -- 定义一个非空变量v_resultA，同时赋值

DBMS_OUTPUT.put_line('v_resultA变量内容：' || (v_resultA) ) ;

运行结果：v_resultA变量内容：100

定义常量：

v_resultA CONSTANT NUMBER NOT NULL := 100 ; -- 定义一个常量同时赋值

DBMS_OUTPUT.put_line('v_resultA常量内容：' || (v_resultA) ) ;

1.使用constant定义的常量不能在程序中对其参数进行修改。此sql使用了NOT NULL进行定义，实际上是没有任何实际意义的。

使用%TYPE声明变量类型

该声明方式，可以使某一个变量与指定数据表中某一列的类型一样；

语法：

变量定义表名称.字段名称%TYPE

示例：使用"%TYPE"定义变量

v_eno emp.empno%TYPE ; -- 与empno类型相同

v_ename emp.ename%TYPE ; -- 与ename类型相同

DBMS_OUTPUT.put_line('请输入雇员编号：') ;

v_eno := &empno ; -- 由键盘输入雇员编号

SELECT ename INTO v_ename FROM emp WHERE empno= v_eno ;

DBMS_OUTPUT.put_line('编号为：' || v_eno || '雇员的名字为：'|| v_ename) ;

运行：输入empno的值：7369

输出结果：编号为：7369雇员的名字为：SMITH

分析：此程序主要演示了%TYPE变量的使用方式。定义的eno和ename两个变量的数据类型参考了emp表的empno和ename两个字段

此标记可以定义表中一行记录的类型；

使用"select…..into…."将表中的一行记录设置到了ROWTYPE类型的变量中时，可以利用"rowtype变量.表字段"的方式取得表中每行的对应列数据

示例：使用ROWTYPE装载一行记录

v_deptRow dept%ROWTYPE ; -- 装载一行dept记录

SELECT * INTO v_deptRow FROM dept WHERE deptno=10 ;

DBMS_OUTPUT.put_line('部门编号：'|| v_deptRow.deptno || '，名称：' || v_deptRow.dname || '，位置：' || v_deptRow.loc) ;

运行结果：部门编号：10，名称：ACCOUNTING，位置：NEW YORK

分析：此sql定义了deptRow的ROWTYPE类型变量，之后使用限定查询，查询出10部门的完整信息，并将此行信息设置到deptRow变量中，然后分别利用列名称取得此行中的每列内容。

补充：通过定义专门的集合类型也可以完成与ROWTYPE类型的功能

通过自定义类型接收一行记录：

TYPE dept_type IS RECORD (

dno dept.deptno%TYPE ,

dna dept.dname%TYPE ,

dlo dept.loc%TYPE) ; -- 定义一个新的类型

v_deptRow dept_type ; -- 装载一行dept记录

DBMS_OUTPUT.put_line('部门编号：'|| v_deptRow.dno || '，名称：' || v_deptRow.dna || '，位置：' || v_deptRow.dlo) ;

​

-- 找出哪个数据库用户用什么程序在最近三天执行过delete或truncate table的操作      

SELECT c.username,
         a.program,
         b.sql_text,
         b.command_type,
         a.sample_time
    FROM dba_hist_active_sess_history a
         JOIN dba_hist_sqltext b
            ON a.sql_id = b.sql_id
         JOIN dba_users c
            ON a.user_id = c.user_id
   WHERE     a.sample_time BETWEEN SYSDATE - 3 AND SYSDATE
         AND b.command_type IN (7, 85)
ORDER BY a.sample_time DESC;      

SELECT c.username,
         a.program,
         b.sql_text,
         b.command_type,
         a.sample_time
    FROM dba_hist_active_sess_history a
         JOIN dba_hist_sqltext b
            ON a.sql_id = b.sql_id
         JOIN dba_users c
            ON a.user_id = c.user_id
   WHERE     a.sample_time BETWEEN SYSDATE - 3 AND SYSDATE
         AND b.command_type IN (7, 85)
ORDER BY a.sample_time DESC;