Oracle之PL/SQL的变量小结

最新推荐文章于 2021-04-05 17:37:25 发布

eric_za

最新推荐文章于 2021-04-05 17:37:25 发布

阅读量625

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

PLSQL变量有四种类型，分别是：

－标量类型（Scalar）

－复合类型（Composite）

－参照类型（Reference）

－ LOB类型（Large Object）

1、标量类型：
－只能存放单个数值的变量
－定义时，必须要指定标量的数据类型

1.1常用标量类型
（1）VARCHAR2(n)
定义可变长度的字符串
n指定字符串最大长度
n最大值是32767字节
使用时必须指定长度
当在PLSQL块钟使用该数据类型操纵VARCHAR2表列时，起数值长度不应超过4000字节

（2）CHAR(n)
定义固定长度字符串
n指定字符串的最大长度
n最大值是32767自己
使用时指定长度，若没指定，则使用默认值1
当在PLSQL块钟使用该数据类型操纵CHAR2表列时，起数值长度不应超过2000字节

（3）NUMBER(p，s)
定义固定长度的整数和浮点数
p表示精度，用于指定数字的总位数
s表示标度，用于指定小数点后的数字位数

（4）DATE
定义日期和数据数据

（5）BOOLEAN
定义布尔变量
值为：TRUE、FALSE、NULL
为PLSQL独有数据类型，表列不能采用该数据类型
NULL表示missing、inapplicable或者unknown

（6）LONG和LONG ROW
long：数据类型用于定义变长字符串，类似于VARCHAR2数据类型，但其字符串的最大长度为32760字节
long row：数据类型用于定义变长的二进制数据，其数据最大长度为32760字节

（7）BINARY_INTEGER
定义整数
数值范围-2147483647和2174483647之间

（8）BINARY_FLOAT和BINARY_DOUBLE
BINARY_FLOAT定义单精度浮点数（oracle 10g新增加）
BINARY_DOUBLE定义双精度浮点数（oracle 10g新增加）

（9）TIMESTAMP
定义时间和日期数据（oracle 9i新增加）
对其赋值方法与对date变量赋值方法完全相同
当显示TIMESTAMP变量数据时，不仅会显示日期，而且还会显示时间和上下午标记

1.2、定义标量类型
语法：
identifier [CONSTANT] datatype [NOT NULL] [ :=|DEFAULT expr]
identifier：指定变量或常量名称
CONSTANT：指定是常量
datatype：用于指定变量或常量的类型
NOT NULL：非空，有它是必须初始化
:= ：赋值符合
DEFAULT：默认值
expr：初始值

1.3、标量定义规则
每行声明一个变量
变量的赋值使用：identifier := value;
变量名称最多30个字符

1.4使用%TYPE属性
安装数据库列或其他变量来确定新变量的类型和长度（具有动态性和绑定性）
语法：identifier Table.column_name%TYPE;
或 identifier other_variable_name%TYPE;

2、复合变量指用于存放多个值的变量
当定义复合变量时，必须要使用PL/SQL的复合数据类型
PL/SQL的复合数据类型包括：
PL/SQL记录
PL/SQL表（索引表）
PL/SQL嵌套表
VARRAY
关于这些复合数据类型，由相关章节给出

3、参照变量指用于存放数值指针的变量
可以通过参照变量来共享相同对象，从而降低占用空间
两种参照变量：
游标变量（REF CURSOR）
对象类型变量（REF obj_type）
关于这些参照变量，由相关章节给出

4、LOB变量用于存储大批量数据的变量
分为两种：
内部LOB：包括CLOB、BLOB、NCLOB，它们的数据被存储在数据库中，并且支持事务操作
外部LOB：BFILE，该类型数据被存储在OS文件中，并且不支持事务操作

CLOB：存储大批量字符数据
NCLOB：存储大批量字符数据，unicode编码
BLOB：存储大批量二进制数据
BFILE：存储指向OS文件的指针

5、非PL/SQL变量5.1、替换变量（仅用于SQL *Plus或者用于原理和SQL *Plus相同的开发工具）：
临时存储值
利用它可以达到创建通用脚本的目的
利用它可以达到和用户交互，故在SQL *Plus中又称交互式命令
替换变量的格式式在变量名称前加一个&，以便在运行SQL命令时提示用户输入替换数据，然后按输入数据运行SQL命令
语法：
（1）& ：“&变量名”eg：&name;
生命周期：单次引用中，不需要声明，如果替换字符或日期类型，最好用单引号扩起
使用范围：where、order by、列表达式、表名、整个SELECT 语句中

（2）&& ：“&&变量名”eg：&&name;
生命周期：整个会话（session连接）,不需要声明

（3）define ：“define 变量名=变量值”eg：DEFINE a = clark;
生命周期：整个会话，预先声明，使用时用&引用声明的变量
define variable=用户创建的CHAR类型的值：define 变量名=值；
define column_name(变量名)：查看变量命令。
undefine 变量名：清除变量
define：查看在当前会话中所有的替换变量和它们的值

（4）accept

生命周期：整个会话
预先声明，可以客户化提示信息，使用时用&引用声明的变量。
定义：
accept 变量名name number/char/date prompt '提示信息内容'即：ACC[EPT] variable [NUM[BER] | CHAR | DATE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]
解释：
PROMPT命令：用于输出提示用户的信息，以便使用户了解脚本文件的功能和运行情况
PAUSE命令：用于暂停脚本文件的运行
HIDE选项：用于隐藏用户的输入，使别人不可见，安全
这条命令的意思是：当plsql程序段执行到变量name的时候，此时需要用户的交互才能继续执行下去，plsql程序段会显示“提示信息内容”让用户输入相关信息（如果指定hide选项，那么在接下去用户输入的东西将被用星号显示出来增加安全，有点像输入密码），用户输入的内容被接收到并且把它付给name，关于在“提示信息内容”下用户输入的内容的类型，plsql程序段开发人员来通过number/char/date指定，变量name得到正确的值以后，继续执行相关下面的程序！
例：accept a char prompt '请输入员工的雇佣时间(yyyy-mm-dd):' hide
例：accept a char prompt 'input a:' hide

替换变量非常的依赖SQL *Plus的环境，当环境变量verify被关闭的时候它就不可用，只有通过打开它才能使用：
set verify(环境变量) off;关闭调试命令（关掉替换过程）
set verify(环境变量) on;打开调试命令(可以看到替换过程)

5.2、SQL *PLUS环境变量（仅用于SQL *Plus或者用于原理和SQL *Plus相同的开发工具）：
ECHO 显示回显
HEADING {OFF/ON}是否显示列标题
ARRAYSIZE{20/n}每一次从查询得到的返回量的大小
FEEDBACK{OFF/ON}回馈，反馈信息
LONG{80/n}on/text} LONG类型
LINESIZE 行的宽度
SET LINESIZE n(最好是在200之内)
PAGESIZE ：设置页的大小。SET PAGESIZE N
wrap{off/on} 折行
SET 修改
SHOW 显示

SQL *PLUS中格式化显示的命令：
COLUMN[column option]可以设置字段或字段别名的格式
COLUMN last_name HEADING employee|name '|'代表换行
col 字段名查看命令
CLE[AR]：清除列的格式
HEA[DING] TEXT：设置列标题
FOR[MAT] FORMAT：格式化显示列的值，对字符和数字有效，对日期无效，eg：column salary justify left format ＄999,999.00，其中justify left:左对齐；col manager_id format 999999999
限制字符串的长度有A+数字限制
限制数字的长度有9，有几为9就限制成几位。
NOPRINT/PRINT NOPRINT：把一个字段从输出上屏蔽掉（返回但不显示）。
col 字段名 noprint/print.
NULL如果有NULL值，显示什么。
col name null 'on employee'
TTITLE[text/off/on]设置报表的表头
BTITLE[text/off/on]设置报表的表尾
做报表的时候要先想好PAGESIZE的大小。
BREAK ON [REPORT_ELEMENT]
压制重复值的显示，只能跟一个字段名才有效，eg：
select department_id,last_name
from employees
where rownum<30
order by 1,2;
break on department_id