此條目需要擴充。 (2011年10月12日)请協助改善这篇條目，更進一步的信息可能會在討論頁或扩充请求中找到。请在擴充條目後將此模板移除。
此條目需要精通或熟悉计算机科学的编者参与及协助编辑。 (2011年10月12日)請邀請適合的人士改善本条目。更多的細節與詳情請參见討論頁。另見其他需要计算机科学專家關注的頁面。

PL/SQL（Procedural Language/SQL）是甲骨文公司專有的SQL擴展語言，應用在甲骨文公司的Oracle数据库系統。一些的SQL数据库管理系統也提供了類似的擴展SQL語言。PL/SQL的語法非常類似於Ada，而且像1980年代的Ada編譯器一樣，PL/SQL的運作系統使用Diana作為中介語言。

重要的是PL/SQL緊密的結合在Oracle数据库裡面。

PL/SQL是Oracle數據庫使用的三種語言的其中之一，另外兩個是SQL和Java。

歷史

特性

SQL连接操作

Oracle连接操作(left join ,right join,full join)的语法确与SQL标准完全不同，没有左连接与右连接的概念，也不支持全外连接。Oracle语法如下所示： 

 select * from t1,t2 where t1.id=t2.id(+)

采用(+)来表示外连接，在Oracle中它相当于左连接。Oracle9i中增加了标准外连接的语法支持，但使用不广。

物化视图

物化视图（materialized view）或译为实体化视图。与普通视图关系不同，物化视图更像是一个表，保存了实实在在的数据，并且可以与表一样定义存储参数，可以与表一样使用select,insert,update,delete。在其它数据库中也有和物化视图相似的解决方案，DB2叫物化查询表(materialized query table)，sqlserver有索引视图，但是索引视图仅是起优化作用，与oracle的物化视图还不太一样。

数据类型

数值类型

采用本地的number类型做指数或对数运算，与标准的浮点数性能可能会相差50倍。好在Oracle10g中增加了高效的浮点类型binary_float,binary_double，从而弥补了浮点数性能的问题。 

variable_name number[([P][, S])] = 0;

其它数值类型： dec, decimal, double precision, int, numeric, real, smallint, binary_integer.

字符类型

字符串数据类型依据存储空间分为两种：

串的开头存储了串的长度。如果串的长度小于或等于250（0x01~0xFA）， Oracle 会使用1 个字节来表示长度。对于所有长度超过250 的串，都会在一个标志字节0xFE 后跟有两个字节来表示长度。

chr(0)表示的不可见字符，即我们通常所说的\0 

--  字段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');

函数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支持使用ANSI日期与时间间隔值[2] The following clause gives an 18-month range:

示例： 

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;

日期型数据可以与数值加减得到新的日期，加减数值单位为天

LOB类型

内置的LOB数据类型包括BLOB、CLOB、NCLOB、BFILE（外部存储）的大型化和非结构化数据，如文本、图像、视屏、空间数据存储。

LONG类型，RAW类型，LONG RAW类型

均为较老的数据类型，Oracle不建议使用。

ROWID & UROWID类型

在数据库中的每一行都有一个地址。然而，一些表行的地址不是物理或永久的，或者不是ORACLE数据库生成的。例如，索引组织表行地址存储在索引的叶子，可以移动。例如,外部表的ROWID（如通过网关访问DB2表）不是标准的ORACLE的rowid。

ORACLE使用通用的ROWID（UROWIDs）的存储地址的索引组织表和外表。10个字节，格式为： ********.****.****，*为0或1。NROWID类型为二进制数据表中记录的唯一行号，最大长度4000字节

索引组织表有逻辑urowids的，和外表的外urowids。UROWID这两种类型的存储在ROWID（堆组织的表的物理行id）。

创建基于逻辑的rowid在表中的主键。逻辑的rowid不会改变，只要主键不改变。索引组织表的ROWID伪UROWID数据类型。你可以访问这个伪列，你会堆组织表的ROWID伪（即使用一个SELECT ...ROWID语句）。如果你想存储的rowid索引组织表，那么你就可以定义一列的表型UROWID到列检索值的ROWID伪。

指定列的数据类型

定义一个变量，其类型是指定表的指定列的数据类型： 

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;

可以使用点表示（dot-notation）获取结构中的域： 

v_address.street = 'High Street';"

自增长数据类型

Oracle的数据类型里没有自增长（auto-incremental）字段类型，Oracle的官方解决方案是采用sequence实现。insert的时候需要用sequence.nextval。需要增加一张专用表来保存自增长字段的表和字段名，每次新增记录时都把这个记录值加1再取出使用。

基本程式

条件语句

以下的代码展示了IF-THEN-ELSIF结构。ELSIF和ELSE部分是可选的，从而可以创建更简单的IF-THEN或者IF-THEN-ELSE结构。 

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语句简化了一些大的IF-THEN-ELSE结构。 

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 将数组称为“集合”（collection）。 该语言提供三种类型的集合：

程序员必须为变量指定上限，但不需要为索引表或嵌套表指定上限。 该语言包含多种用于操作集合元素的集合方法：例如 FIRST、LAST、NEXT、PRIOR、EXTEND、TRIM、DELETE 等。索引表可用于模拟关联数组。

关联数组（索引表）

对于索引表，可以通过数字或字符串对数组进行索引。 它与Java映射类似，由键值对组成。 只有一维且无界。

嵌套表

对于嵌套表（英语：Nested SQL），程序员需要了解嵌套的内容。 在这里，创建了一个可能由多个组件组成的新类型。 然后可以使用该类型在表中创建一列，并在该列中嵌套这些组件。 With nested tables the programmer needs to understand what is nested. Here, a new type is created that may be composed of a number of components. That type can then be used to make a column in a table, and nested within that column are those components.

Varray（可变大小数组）

对于 Varray，需要了解“可变大小数组”短语中的“变量”一词并不像想象的那样适用于数组的大小。 声明数组的大小实际上是固定的。 数组中元素的数量是可变的，最多可达声明的大小。 可以说，可变大小的数组的大小并不是那么可变。

迴圈

从过程语言的，PL/SQL提供了多种迭代结构，包括基本的LOOP语句、WHILE 循环、FOR循环和Cursor FOR循环。 从 Oracle 7.3 开始，引入了REF CURSOR 类型，以允许从存储过程和函数返回记录集。 Oracle 9i引入了预定义的SYS_REFCURSOR类型，这意味着我们不再需要定义自己的 REF CURSOR 类型。

如果退出循环，则必须使用exit语句终止循环。exit语句分两种格式： 

exit：该格式的语句用于无条件强迫终止循环。
exit...when：该格式用于有条件终止循环，首先检测when的条件是否满足。

例1： 

loop
    if...then exit；
    end if；
end loop；

例2： 

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;

[3]

Loop可用EXIT关键字或抛出异常来终止。

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循环自动打开一个cursor，读入其数据并再次关闭游标。

作为替代方案，PL/SQL 程序员可以提前预定义游标的 SELECT 语句，以（例如）允许重用或使代码更易于理解（在长查询或复杂查询的情况下特别有用）。 

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 循环中 person_code 的概念用点符号（“.”）表示: 

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;

游标

游标是指向私有SQL区域的指针，该区域存储来自SELECT或数据操作语言(DML)语句（INSERT、UPDATE、DELETE 或 MERGE）的信息。游标保存SQL语句返回的行（一行或多行）。游标所保存的行集称为活动集（active set）。[4]

游标可以是显式的或隐式的。在FOR循环中，如果要重用查询，则应使用显式游标，否则首选隐式游标。如果在循环内使用游标，则在需要批量收集或需要动态SQL时，建议使用 FETCH。 

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;

類似的語言

功能近似PL/SQL的程序語言和其他關係型資料庫：

PL/SQL 的工作方式类似于与其他关系数据库关联的嵌入式过程语言。 例如，Sybase ASE 和 Microsoft SQL Server 具有 Transact-SQL，PostgreSQL 具有 PL/pgSQL（在一定程度上模拟 PL/SQL），MariaDB 包括 PL/SQL 兼容性解析器，而 IBM Db2 包括 SQL 过程语言，符合ISO SQL的SQL/PSM标准。

PL/SQL的设计者将其语法建模为Ada的语法。 Ada 和 PL/SQL都以Pascal語言作为共同的祖先，因此 PL/SQL 在大多数方面也类似于 Pascal。 然而，PL/SQL 包的结构与 Borland Delphi 或Free Pascal 单元实现的基本 Object Pascal 程序结构并不相似。 程序员可以在 PL/SQL 包中定义公共和私有全局数据类型、常量和静态变量。

PL/SQL 还允许定义类并将它们实例化为 PL/SQL 代码中的对象。 这类似于面向对象编程语言（如 Object Pascal、C++ 和 Java）中的用法。 PL/SQL 将类称为“抽象数据类型”(ADT) 或“用户定义类型”(UDT)，并将其定义为 Oracle SQL 数据类型而不是 PL/SQL 用户定义类型，从而允许 它在 Oracle SQL 引擎和 Oracle PL/SQL 引擎中的使用。 抽象数据类型的构造函数和方法是用 PL/SQL 编写的。 生成的抽象数据类型可以作为 PL/SQL 中的对象类进行操作。 此类对象还可以作为 Oracle 数据库表中的列值保留。

尽管表面上相似，但 PL/SQL 与 Transact-SQL 本质上是不同的。 将代码从一种语言移植到另一种语言通常涉及不平凡的工作，这不仅是因为两种语言的功能集存在差异，还因为 Oracle 和 SQL Server 处理并发的方式存在非常显着的差异 和锁定。

注释

  1. ^ 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.

參考文献

  1. ^ Oracle Ducuments about Data Type. [2017-08-14]. （原始内容存档于2017-07-10）. 
  2. ^ Literals. Oracle Database SQL Reference 10g Release 2 (10.2). Oracle. [2009-03-20]. （原始内容存档于2009-03-18）. 
  3. ^ Database PL/SQL User's Guide and Reference. download.oracle.com. [2023-10-17]. （原始内容存档于2011-10-28）. 
  4. ^ Feuerstein, Steven. Working with Cursors. oracle.com. [2023-10-17]. （原始内容存档于2018-03-30）. 
  5. ^ SQL PL

外部連結

参见

概念
数据库组件
SQL
分类
指令
安全
实现
类型
数据库产品
  • 对象型
    • 对比
  • 关系型
数据库组件
分类