PL-SQL
此條 |
PL/SQL(Procedural Language/SQL)
歷史
编辑
特性
编辑
SQL连接操作
编辑
Oracle连接
select * from t1,t2 where t1.id=t2.id(+)
物 化 视图
编辑
数 据 类型
编辑
数 值类型
编辑
variable_name number[([P][, S])] = 0;
- NUMBER
可 选指定 precision (P)与 scale (S)。精度 表示 十进制有效数字的个数,最多 不能 超 过38个有效 数字 (实际支持 39-40位 十 进制数字 )[註 1]。Scale的 范围为[-84,127]。Scale为正数 时,表示 从小数 点 到 最 不 重要 的 十进制有效数字的个数;为负数 时,其绝对值表示 从最不 重要 的 十进制有效数字到小数点的位数。如果没 有 指定 精度 ,precision与 scale默 认为最大 的 取 值区间。如果指定 了 精度 ,没 有 指定 scale,scale默 认为0。内部 存 储格式 是 变长阿拉 伯 数字 的 字 节数组:首 字 节为长度值,最大 22;如果为NULL,则该字 节值为255(0xFF)第 二字节是符号和指数字节(sign bit/exponent),其最高 比 特 为符号 位 ,1表示 正数 ,0表示 负数;其余7比 特 构成基 为100的 指数 值,取 值范围[-65,62],NUMBER数 据 类型的 取 值范围为[10-130,10126);第 二字节值大于128,则:指数 值=字 节值 - 128 - 64=字 节值-192,即 去 除 符号 比 特 后 偏 移 了 64。字 节值最大 为254第 二字节值等于128,则NUMBER数 据 类型表示 值0第 二字节值小于128,则:指数 值=(255-字 节值)-128-64=63-ZV,即 取 反 后 去 除 符号 比 特 再 偏 移 64
- 其余
字 节保存 了 基数 为100的 数 值00-99- 对于
正数 :实际值=存 储值-1 - 对于负数:实际值=
存 储值-101;字 节值102 (0x66)标志字 节数组的结束。[1] - 两个
字 节255与 101表示 正 无穷 - 单字节0
表示 负无穷
- 对于
- INTEGER
是 NUMBER的 子 类型,它等同 于NUMBER(38,0),用 来 存 储整数 。若 插入 、更新 的 数 值有小数 ,则会被 四 舍 五 入 。 - FLOAT类型也是NUMBER
的 子 类型。Float(n),数 n指示 位 的 精度 ,可 以存储的值的数 目 。n 值的范围可 以从 1到 126。若 要 从二进制转换为十进制的精度,请将 n乘 以 0.30103。要 从十进制转换为二进制的精度,请用 3.32193乘 小数 精度 。126位 二进制精度的最大值是大约相当于 38位 小数 精度 。 - BINARY_FLOAT
是 32位 、 单精度 浮点数字 数 据 类型。可 以支持 至 少 6位 精度 ,每 个 BINARY_FLOAT的 值需要 5 个字节,包括 长度字 节。 - BINARY_DOUBLE
是 为 64位 ,双 精度 浮点数字 数 据 类型。每 个 BINARY_DOUBLE的 值需要 9 个字节,包括 长度字 节。
其它
字 符 类型
编辑
固定 长度类型:CHAR/NCHAR,自 动补足 空 格 ,最多 可 以存储2,000字 节可 变长度 类型:VARCHAR2/NVARCHAR2,最大 字 节数都 是 4000,自 动删除 首尾 的 空 格
chr(0)
- CHAR类型: CHAR(size [BYTE | CHAR])
固定 长度字 符 串 ; - NCHAR类型:
根 据 字 符 集 而定的 UNICODE格式 固定 长度字 符 串 最大 长度2000 bytes。 - VARCHAR类型:
不 建 议使用 。虽然VARCHAR数 据 类型目前 是 VARCHAR2的 同 义词,VARCHAR数 据 类型将 被 重 新定 义为一个单独的数据类型用于可变长度的字符串相比,与 VARCHAR2具有 不同 的 比 较语义 - varchar2类型:变长
字 符 串 - nvarchar2()类型:
包含 UNICODE格式 数 据 的 变长字 符 串
-- 字 段 translated_name是 NCHAR类型,则需要 如下书写:
SELECT translated_description FROM product_descriptions
WHERE translated_name = N'LCD Monitor 11/PM';
variable_name varchar2(20) = 'Text';
-- e.g.:
address varchar2(20) := 'lake view road';
日 期 类型
编辑
variable_name date = to_date('01-01-2005 14:20:23', 'DD-MM-YYYY hh24:mi:ss');
- Date类型
可 以表示 日 期 与 时间。精度 到 秒 。日 期 范围可 以是公 元 前 4712年 1月 1日 至 公 元 9999年 12月31日 。占 用 7个字节的存 储空间。第 1字 节:世 纪+100;第 2字 节:年 ;第 3字 节:月 ;第 4字 节:天 ;第 5字 节:小 时+1;第 6字 节:分 +1;第 7字 节:秒 +1。其中时间可 以忽略 。但 无法只 表示 时间而忽略 日 期 。Oracle Datatypes(页面存 档备份,存 于互联网档案 馆) - TIMESTAMP类型:7
字 节或11字 节的定 宽日期 /时间数 据 类型。可 以包含 小 数 秒 ,小 数 位 数 可 以指定 为0-9,默 认为6位 ,所以 最高 精度 可 以到ns(纳秒).如果精度 为0,则用7字 节存储,与 date类型功 能 相 同 ,如果精度 大 于0则用11字 节存储。 - TIMESTAMP WITH TIME ZONE类型:TIMESTAMP类型
的 变种,它包含 了 时区偏 移 量的 值 - TIMESTAMP WITH LOCAL TIME ZONE类型:
- INTERVAL YEAR TO MOTH:
- INTERVAL DAY TO SECOND:
TO_DATE
to_date('31-12-2004', 'dd-mm-yyyy')
to_date ('31-Dec-2004', 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE = American')
TO_CHAR (date_string, format_string)
PL/SQL
WHERE dateField BETWEEN DATE '2004-12-30' - INTERVAL '1-6' YEAR TO MONTH
AND DATE '2004-12-30'
create table T
(
C1 DATE,
C2 TIMESTAMP(9)
);
insert into t(c1,c2) values(date'2010-2-12',timestamp'2010-2-12 13:24:52.234123211');
insert into t(c1,c2) values(
to_date('2010-2-12 10:20:30','YYYY-MM-DD HH24:MI:SS'),
to_timestamp('2010-2-12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6')
);
SQL> select c1,dump(c1) c1_d,c2,dump(c2) c2_d from t;
- sysdate--
返 回 当 前 系 统日期 和 时间,精 确到秒 - systimestamp--
返 回 当 前 系 统日期 和 时间,精 确到毫秒
- sysdate+1--
取 明天 的 当 前 时间 - sysdate-1/24--
取 当 前 时间的 前 一 个小时
LOB类型
编辑- BLOB
数 据 类型:存 储非结构化 的 二进制数据大对象,它可以被认为是 没 有 字 符 集 语义的 比 特 流 ,一般 是 图像、声音 、视频等 文 件 。BLOB对象最多 存 储(4 gigabytes-1) * (database block size)的 二 进制数 据 。 - CLOB
数 据 类型:存 储单字 节和多 字 节字符 数 据 。支持 固定 宽度和 可 变宽度 的 字 符 集 。CLOB对象可 以存储最多 (4 gigabytes-1) * (database block size)大小 的 字 符 - NCLOB
数 据 类型:存 储UNICODE类型的 字 符 数 据 ,支持 固定 宽度和 可 变宽度 的 字 符 集 ,NCLOB对象可 以存储最多 (4 gigabytes-1) * (database block size)大小 的 文 本数 据 。 - BFILE
数 据 类型:存 储在数 据 库外的 二 进制文 件 ,只 读,最大 长度4G
LONG类型,RAW类型,LONG RAW类型
编辑- LONG类型
存 储变长字符 串 ,最多 达2G的 字 节数据 。存 储在LONG 类型中 的 文 本 要 进行字 符 集 转换。支持 LONG列 只 是 为了保 证向后 兼 容 性 。LONG类型的 限 制 如下:- 一个表中只有一列可以为LONG
型 。 - LONG
列 不能 定 义为主 键或唯一 约束 不能 建立 索引 - LONG
数 据 不能 指定 正 则表达式。 函数 或 存 储过程 不能 接受 LONG数 据 类型的 参 数 。- LONG
列 不能 出 现在WHERE子 句 或 完 整 性 约束(除 了 可能 会 出 现NULL和 NOT NULL约束)
- 一个表中只有一列可以为LONG
- LONG RAW 类型,
能 存 储2GB的 原始 二 进制数 据 (不用 进行字 符 集 转换的 数 据 ) - RAW类型
用 于存储二进制或字符类型数据,变长二进制数据类型,这说明 采 用 这种数 据 类型存 储的数 据 不 会 发生字 符 集 转换。这种类型最多 可 以存储2,000字 节的信 息
ROWID & UROWID类型
编辑ORACLE
创建
指定 列 的 数 据 类型
编辑
Variable_name Table_name.Column_name%type;
自 定 义类型
编辑
type data_type is record (field_1 type_1 = xyz, field_2 type_2 := xyz, ..., field_n type_n = xyz);
declare
type t_address is record (
name address.name%type,
street address.street%type,
street_number address.street_number%type,
postcode address.postcode%type);
v_address t_address;
begin
select name, street, street_number, postcode into v_address from address where rownum = 1;
end;
v_address.street = 'High Street';"
自 增 长数据 类型
编辑
Oracle
基本 程 式
编辑
条件 语句
编辑
IF x = 1 THEN
sequence_of_statements_1;
ELSIF x = 2 THEN
sequence_of_statements_2;
ELSIF x = 3 THEN
sequence_of_statements_3;
ELSIF x = 4 THEN
sequence_of_statements_4;
ELSIF x = 5 THEN
sequence_of_statements_5;
ELSE
sequence_of_statements_N;
END IF;
CASE语句简化
CASE
WHEN x = 1 THEN sequence_of_statements_1;
WHEN x = 2 THEN sequence_of_statements_2;
WHEN x = 3 THEN sequence_of_statements_3;
WHEN x = 4 THEN sequence_of_statements_4;
WHEN x = 5 THEN sequence_of_statements_5;
ELSE sequence_of_statements_N;
END CASE;
CASE语句
CASE x
WHEN 1 THEN sequence_of_statements_1;
WHEN 2 THEN sequence_of_statements_2;
WHEN 3 THEN sequence_of_statements_3;
WHEN 4 THEN sequence_of_statements_4;
WHEN 5 THEN sequence_of_statements_5;
ELSE sequence_of_statements_N;
END CASE;
陣列
编辑
PL/SQL
- 关联
数 组(索引 表 ) 嵌 套表- Varray(
可 变大小数 组)
关联数 组(索引 表 )
编辑
对于
嵌 套表
编辑
对于
Varray(可 变大小数 组)
编辑
对于 Varray,
迴圈
编辑从过
如果
exit:该格式 的 语句用 于无条件 强迫 终止循环。 exit...when:该格式 用 于有条件 终止循环,首 先 检测when的 条件 是 否 满足。
loop if...then exit; end if; end loop;
loop exit when; end loop;
LOOP语句
编辑<<parent_loop>>
LOOP
statements
<<child_loop>>
loop
statements
exit parent_loop when <condition>; -- Terminates both loops
exit when <condition>; -- Returns control to parent_loop
end loop child_loop;
if <condition> then
continue; -- continue to next iteration
end if;
exit when <condition>;
END LOOP parent_loop;
LoopEXIT
关键
FOR循环
编辑DECLARE
var NUMBER;
BEGIN
/* N.B. for loop variables in PL/SQL are new declarations, with scope only inside the loop */
FOR var IN 0 .. 10 LOOP
DBMS_OUTPUT.PUT_LINE(var);
END LOOP;
IF var IS NULL THEN
DBMS_OUTPUT.PUT_LINE('var is null');
ELSE
DBMS_OUTPUT.PUT_LINE('var is not null');
END IF;
END;
输出:
0 1 2 3 4 5 6 7 8 9 10 var is null
for...loop循环
declare div_name varchar2(20); div_num integer:=1; begin for div_num in 1..9 loop select name into div_name from div_tab where div_author='A000'||to_char(div_num); end loop; end;
declare div_name varchar2(20); div_num integer=1; begin for div_num in 1..9 loop EXIT WHEN div_num >7; DBMS_OUTPUT.put_line(div_num); end loop; end;
Cursor FOR循环
编辑FOR RecordIndex IN (SELECT person_code FROM people_table)
LOOP
DBMS_OUTPUT.PUT_LINE(RecordIndex.person_code);
END LOOP;
Cursor-for循环
DECLARE
CURSOR cursor_person IS
SELECT person_code FROM people_table;
BEGIN
FOR RecordIndex IN cursor_person
LOOP
DBMS_OUTPUT.PUT_LINE(recordIndex.person_code);
END LOOP;
END;
FOR 循环
RecordIndex.person_code
while循环
编辑while...loop;
while...loop循环
declare div_name varchar2(20); div_num integer=1; begin while div_num<10 loop select name into div_name from div_tab where div_author ='A000'||to_char(div_num); div_num=div_num+1; end loop; end;
游 标
编辑
create or replace procedure test() IS
cusor_1 Cursor is select std_name from student where ...; --Cursor 的 使用 方式 1
cursor_2 Cursor;
begin
select class_name into cursor_2 from class where ...; --Cursor 的 使用 方式 2
-- 可 使用 For x in cursor LOOP .... end LOOP; 来 实现对Cursor 的 遍 历
end test;
-- SYS_REFCURSOR 型 游 标,该游标是Oracle 以预先 定 义的游 标,可 作出 参 数 进行传递
create or replace procedure test1(rsCursor out SYS_REFCURSOR) IS
cursor SYS_REFCURSOR;
name varhcar(20);
begin
OPEN cursor FOR select name from student where ... --SYS_REFCURSOR 只 能 通 过OPEN 方法 来 打 开和赋值
LOOP
fetch cursor into name -- SYS_REFCURSOR 只 能 通 过fetch into 来 打 开和遍 历
exit when cursor%NOTFOUND;
--SYS_REFCURSOR 中 可 使用 三 个状态属性 :
---%NOTFOUND( 未 找到记录信 息 ) %FOUND( 找到记录信 息 )
---%ROWCOUNT( 然 后 当 前 游 标所指向 的 行 位置 )
dbms_output.putline(name);
end LOOP;
rsCursor := cursor;
end test1;
存 储过程
编辑
查看
select text
from USER_SOURCE
where name='My_Store_Precedure_Name'
order by LINE;
類似 的 語 言
编辑
- Sybase ASE
- Microsoft SQL Server
的 Transact-SQL - PostgreSQL
資料 庫 的 PL/pgSQL(模 仿PL/SQL) - IBM DB2
的 SQL PL[5] 符合 ISO SQL的 SQL/PSM標準 。
PL/SQL
PL/SQL
PL/SQL 还允许定义类并将它们实例
注 释
编辑
- ^ p
是 精度 ,即 十进制数字的有效位数,其中最 重要 有效 数字 是 在 最 左 边的非 零 数字 ,最 不 重要 有效 数字 是 最 右 边的数字 。Oracle保 证数字 可 移植 性 ,其精度 可 达基于100进制的 20个数字 (centesimal digit),这等效 于依赖小数 点 位置 的 39位 或 40位 十 进制数字 。原文 :p is the precision, or the total number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit. Oracle guarantees the portability of numbers with precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits depending on the position of the decimal point.
參考 文献
编辑
- ^ Oracle Ducuments about Data Type. [2017-08-14]. (
原始 内容 存 档于2017-07-10). - ^ Literals. Oracle Database SQL Reference 10g Release 2 (10.2). Oracle. [2009-03-20]. (
原始 内容 存 档于2009-03-18). - ^ Database PL/SQL User's Guide and Reference. download.oracle.com. [2023-10-17]. (
原始 内容 存 档于2011-10-28). - ^ Feuerstein, Steven. Working with Cursors. oracle.com. [2023-10-17]. (
原始 内容 存 档于2018-03-30). - ^ SQL PL