第4章 PL/SQL变量的声明与使用
什么是程序设计语言中的变量呢?简单的说,一个程序设计语言中的变量就是内存中一个命了名的临时存储区,而变量中所存储的信息就是这个变量的当前值。
变量的作用:
- 数据的临时存储
- 存储值的维护
- 重用
- 维护方便
代码演示:
DECLARE
v_fname VARCHAR2(20);
-- 声明变量
BEGIN
SELECT first_name
INTO v_fname
-- 将SELECT语句查询到的数据存放到变量v_fname中
FROM emp
WHERE emp_id = 100;
DBMS_OUTPUT.PUT_LINE('The result is ' || v_fname);
-- 使⽤存放在变量v_fname中的数据
END;
在PL/SQL中变量的使用可以归纳为以下几点:
- 在声明部分声明和初始化变量(在声明段,即DECLARE部分先做变量的声明)
可以在任何程序块中、子程序(过程或函数)中,或软件包中的声明部分声明一个或多个变量。变量的声明将为一个值分配存储空间、指定该变量的数据类型和命名存储单元以便之后可以引用这个变量。在声明变量时,也可以同时赋予这个变量一个初始值,并且在该变量上加上NOT NULL约束。要注意的是,PL/SQL的变量是不允许提前引用的,即必须在引用一个变量之前先声明这个变量。 - 在执行部分为变量赋新值(在执⾏段,即BEGIN部分赋新值和使⽤)
在PL/SQL程序的执行段中,可以使用赋值语句为变量重新赋值(以新值替代原有的值)。 - 通过参数将值传入 PL/SQL 块
子程序(过程或函数)可以使用参数,可以在调用子程序时将变量作为参数传递给这个子程序。 - 通过输出变量来查看结果
可以使用变量来存储一个函数的返回值。
PL/SQL变量的命名规则
- 必须以英语字母开始
- 可以包含一个或多个英语字母或数字
- 可以包含最多30个字符
- 可以包含特殊字符——美元号($)、下划线(_)、和字母(#)
- 不能包含诸如连字符(-)、斜线(/)、和空格等特殊字符
- 不应该与数据库的表或列同名
- 不能是保留关键字
PL/SQL 变量的声明和初始化
变量需要声明,初始化则可以在声明时进⾏,也可以不在此时进⾏,但是如果声明时定义为not null的情况下,就必须初始化才能使⽤。
变量声明的格式为:
Identifier [CONSTANT]datatype [NOT NULL] [ := | DEFAULT expression ]
这⾥中括号中的内容都是可选项,具体含义如下:
- CONSTANT表⽰常量,⼀旦定义为常量,则不能在为该变量赋其他的值了;
- 如果定义了[NOT NULL],则该变量必须赋值;
- PL/SQL中赋值使⽤的是 :=,初始化时也可以使⽤DEFAULT来定义默认值。
代码演示:
DECLARE
v_desc VARCHAR2(100) := '中国妇女解放运动的先驱 —— ';
--声明了变量v_desc,并定义数据类型,且赋值初始化
v_pioneer VARCHAR2(25);
--只声明了变量,没有初始化,所以SQL语句输出结果为空
BEGIN
DBMS_OUTPUT.PUT_LINE (v_desc || v_pioneer);
--使用 DBMS_OUTPUT函数要先开启才能显示内容set serveroutput on
v_pioneer := '苏妲己';
DBMS_OUTPUT.PUT_LINE (v_desc || v_pioneer);
END;
DECLARE
v_desc VARCHAR2(100) := '中国妇女解放运动的先驱 —— ';
v_pioneer VARCHAR2(25) := '潘金莲';
--变量声明,且初始化
BEGIN
DBMS_OUTPUT.PUT_LINE (v_desc || v_pioneer);
v_pioneer := '苏妲己';
--为变量重新赋值(以新值替代原有的值)
v_desc := '中国历史上最成功和最美的职业女性 —— ';
DBMS_OUTPUT.PUT_LINE (v_desc || v_pioneer);
END;
字符串分隔符的说明与使用
在打印输出结果时经常会⽤到引号,但是单引号’有时候也作为分割符使⽤,如 lovers' day 这种情况,为了避免混淆,PL/SQL中使⽤了 q' 来进⾏分隔符的引⽤:q表⽰quotes,加上单引号‘,后⾯接任意成对的符号,都可以起到引⽤的单引号作⽤。
格式: q' !...!' 或者是 q' [文本内容]'
代码演示:
DECLARE
v_special_day VARCHAR2(250);
BEGIN
v_special_day := q'!Happy International Woman's Day on 8th March to all lovely women!';
--这⾥使⽤q’,然后加上成对的!,就可以避免把分隔符’当成单引号
DBMS_OUTPUT.PUT_LINE( v_special_day );
v_special_day := q'[ To many Chinese people, Qixi was celebrated as the "lovers' day".]';
-- 成对的特殊符号都可以,这⾥使⽤的是⼀对中括号[]
DBMS_OUTPUT.PUT_LINE( v_special_day );
END;
变量的数据类型
变量的类型
分类 | 描述 |
---|---|
标量 | 单值没有内部组件,如NUMBER, DATE 或 BOOLEAN |
大对象(LOB) | 指向其他数据项,如文本,图形图像,视频剪辑和声音等存储大对象 |
复合 | 具有可单独访问的内部组件的数据项。例如,集合和记录 |
引用 | 指向其他数据项 |
标量数据类型
- 存储单一的值
- 没有内部结构
基本标量数据类型
PL/SQL变量,常量和参数都必须有一个有效的数据类型,指定存储格式,约束和值的有效范围。
数据类型 | 描述 |
---|---|
数字 | 在其上执行的算术运算的数值 |
字符 | 代表单个字符或字符的字母数字字符串值 |
布尔 | 在其上执行的逻辑运算的逻辑值 |
日期时间 | 日期和时间 |
数字数据类型
数据类型 | 描述 |
---|---|
PLS_INTEGER | 通过2,147,483,647到-2147483648范围内有符号整数,以32位表示 |
BINARY_INTEGER | 通过2,147,483,647到-2147483648范围内的有符号整数,以32位表示 |
BINARY_FLOAT | 单精度IEEE 754格式的浮点数 |
BINARY_DOUBLE | 双精度IEEE 754格式的浮点数 |
NUMBER(prec, scale) | 定点或浮点数在范围1E-130至(但不包括)绝对值1.0E126。 NUMBER变量也可以表示0 |
DEC(prec, scale) | ANSI具体的定点类型使用38位小数最大精度 |
DECIMAL(prec, scale) | IBM特定的固定点型具有38位小数最大精度 |
NUMERIC(pre, secale) | 浮点类型具有38位小数最大精度。 |
DOUBLE PRECISION | 具有126个二进制数字最大精度ANSI特定浮点型(约38位十进制数) |
FLOAT | 具有126个二进制数字(约38位十进制数)最大精度ANSI和IBM特定的浮点型 |
INT | 具有38位小数最大精度ANSI具体的整数类型 |
INTEGER | ANSI和IBM的38位小数最大精度具体的整数类型 |
SMALLINT | ANSI和IBM的38位小数最大精度具体的整数类型 |
REAL | 具有63位二进制数字最大精度浮点型(大约18位小数) |
代码演示:
DECLARE
num1 FLOAT;
num2 INT;
num3 DOUBLE PRECISION;
BEGIN
null;
END;
/
字符数据类型
数据类型 | 描述 |
---|---|
CHAR | 具有32,767个字节的最大尺寸固定长度字符串 默认长度是1,如果内容不够用空格代替。 |
VARCHAR2 | 具有32,767个字节的最大尺寸变长字符串 没有默认值,必须给出长度。 |
RAW | 可变长度的二进制或字节字符串的32,767个字节的最大尺寸,而不是由PL/ SQL解释 |
NCHAR | 具有32,767个字节的最大尺寸的固定长度国家字符串 |
NVARCHAR2 | 具有32,767个字节的最大尺寸可变长度国家字符串 |
LONG | 具有32,760字节最大尺寸变长字符串 |
LONG RAW | 可变长度的二进制或字节字符串的32,760字节的最大尺寸,而不是由PL/SQL解释 |
ROWID | 物理行标识符,一行在一个普通的表中的地址 |
UROWID | 物理通用行标识符(物理,逻辑,或外国的行标识符) |
布尔数据类型
在逻辑操作中使用布尔数据类型存储的逻辑值。逻辑值为:true 和 false 的布尔值以及 NULL 值。
但是,SQL没有数据类型等同于布尔。因此,布尔值不能用于:
- SQL语句
- 内置SQL函数(如to_char)
- 从SQL语句调用PL/SQL函数
代码演示:
DECLARE
True_Love BOOLEAN := FALSE;
BEGIN
True_Love := NULL;
END;
日期时间和间隔类型
字段类型 | 有效日期时间值 | 有效的间隔值 |
---|---|---|
YEAR | -4712 to 9999 (年除外 0) | 任何非零整数 |
MONTH | 01 to 12 | 0 to 12 |
DAY | 01 to 31 (受制于年份和月份的数值,按日历的区域设置的规则) | 任何非零整数 |
HOUR | 00 to 23 | 0 to 23 |
MINUTE | 00 to 59 | 0 to 59 |
SECOND | 00 to 59.9(n), 其中,9(n)是时间小数秒精度 | 0 to 59.9(n), 其中,9(n)是区间小数秒精度 |
TIMEZONE_HOUR | -12 to 14 (范围可容纳夏令时更改) | 不适用 |
TIMEZONE_MINUTE | 00 to 59 | 不适用 |
TIMEZONE_REGION | 在动态性能视图找到V$TIMEZONE_NAMES | 不适用 |
TIMEZONE_ABBR | 在动态性能视图找到V$TIMEZONE_NAMES | 不适用 |
大对象(LOB)数据类型
大对象(LOB)数据类型是指大到数据项,例如文本,图形图像,视频剪辑和声音波形。 LOB数据类型允许高效的,随机的,分段访问这些数据。
以下是预定义的PL/SQL LOB数据类型:
数据类型 | 描述 | 大小 |
---|---|---|
BFILE | 用于存储大型二进制对象在操作数据库之外的系统文件 | 依赖于系统,不能超过4千兆字节(GB) |
BLOB | 用于存储大型二进制对象在数据库中 | 8到128兆兆字节(TB)的 |
CLOB | 用于存储字符大块数据在数据库中 | 8 - 128 TB |
NCLOB | 用于在数据库中存储大块NCHAR数据 | 8 - 128 TB |
% ROWTYPE属性
引用数据库表中的一行作为数据类型,即RECORD类型(记录类型),是PL/SQL附加的数据类型。表示一条记录,就相当于C#中的一个对象。可以使用“.”来访问记录中的属性。
代码演示:
DECLARE
--声明一个myemp对象,该对象表示EMP表中的一行
myemp EMP%ROWTYPE;
BEGIN
--从EMP表中查询一条记录放入myemp对象中
SELECT * INTO myemp FROM emp WHERE empno=7902;
--使用dbms_output软件包打印
dbms_output.put_line(myemp.ename);
END;
/
%TYPE属性
引用某个变量或者数据库的列的类型作为某变量的数据类型。
%TYPE可以引用表中的某列作的类型为变量的数据类型,也可以引用某变量的类型作为新变量的数据类型。
语法
identifier Table.column_name%TYPE;
代码演示:
DECLARE
--定义变量sal,引用为emp表中sal列的类型
sal emp.sal%TYPE;
mysal number(4):=3000;
-- 定义变量totalsal,引用变量mysal的类型
totalsal mysal%TYPE;
BEGIN
SELECT SAL INTO sal FROM emp WHERE empno=7934;
totalsal:=sal+mysal;
dbms_output.put_line(totalsal);
END;
使用绑定变量
为了在 PL/SQL 中引用一个绑定变量,您必须在它的名前冠以冒号 (:)。
代码演示:
VARIABLE g_dog_weight NUMBER
BEGIN
:g_dog_weight := 38;
END;
引用非 PL/SQL 变量
- 引用作为宿主变量的非 PL/SQL 变量。
- 利用前导冒号 (:) 来引用。
代码演示:
VARIABLE g_ename VARCHAR2(15)
VARIABLE g_sal NUMBER
VARIABLE g_job VARCHAR2(10)
BEGIN
SELECT ename, job, sal INTO :g_ename, :g_job, :g_sal
FROM emp WHERE empno = 7902;
END;
标 识 符 | 命 名 规 范 | 例 子 |
---|---|---|
变量 | v_name | v_job、v_sal |
常量 | c_name | c_job、c_sal |
SQL*Plus 替代变量(也称替代参数) | p_name | p_job、p_sal |
SQL*Plus 全局变量(宿主或绑定变量) | g_name | g_job、g_sal |
类型 | 说明 |
---|---|
VARCHAR2(长度) | 可变长度字符串,Oracle SQL定义的数据类型,在PL/SQL中使用时最常32767字节。 在PL/SQL中使用没有默认长度,因此必须指定。 |
TIMESTAMP | Oracle SQL定义的日期类型 |
CHAR(长度) | Oracle SQL定义的日期类型,固定长度字符,最长32767字节, 默认长度是1,如果内容不够用空格代替。 |
LONG | Oracle SQL定义的数据类型,变长字符串基本类型,最长32760字节。 在Oracle SQL中最长2147483647字节。 |
BOOLEAN(布尔变量) | PL/SQL附加的数据类型,逻辑值为TRUE、FALSE、NULL |
BINARY_INTEGER | PL/SQL附加的数据类型,介于-231和231之间的整数。 |
PLS_INTEGER | PL/SQL附加的数据类型,介于-231和231之间的整数。 类似于BINARY_INTEGER,只是PLS_INTEGER值上的运行速度更快。 |
NATURAL | PL/SQL附加的数据类型,BINARY_INTEGER子类型,表示从0开始的自然数。 |
NATURALN | 与NATURAL一样,只是要求NATURALN类型变量值不能为NULL。 |
POSITIVE | PL/SQL附加的数据类型,BINARY_INTEGER子类型,正整数。 |
POSITIVEN | 与POSITIVE一样,只是要求POSITIVE的变量值不能为NULL。 |
REAL | Oracle SQL定义的数据类型,18位精度的浮点数 |
INT,INTEGER,SMALLINT | Oracle SQL定义的数据类型,NUMBERDE的子类型,38位精度整数。 |
SIGNTYPE | PL/SQL附加的数据类型,BINARY_INTEGER子类型。值有:1、-1、0。 |
STRING | 与VARCHAR2相同。 |
编辑于 2022-05-04 21:41