PL/SQL学习笔记

介绍

PL/SQL 编程语言是SQL 和 Oracle 关系数据库的过程扩展语言
是Oracle 编程环境中的一个工具

基本语法

S.No	Sections & Description
1	Declarations 此部分以关键字 DECLARE 开头。 它是一个可选部分，定义了程序中要使用的所有变量、游标、子程序和其他元素。
2	Executable Commands 此部分包含在关键字 BEGIN 和 END 之间，它是必填部分。 它由程序的可执行 PL/SQL 语句组成。 它应该至少有一个可执行的代码行，可能只是一个NULL 命令，表示什么都不应该被执行。
3	Exception Handling 本节以关键字 EXCEPTION 开头。 此可选部分包含处理程序中的错误的异常。

Declare （定义变量）

Begin (定义逻辑)

ExcepTion （异常处理）

End(流程结束)

每个 PL/SQL 语句都以分号 (;) 结束。 PL/SQL 块可以使用 BEGIN 和 END 嵌套在其他 PL/SQL 块中。 以下是 PL/SQL 块的基本结构 −

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)>
EXCEPTION 
   <exception handling> 
END;

标识符（java里的变量名，常量名等这一类名）

在PL/SQL中，标识符是用来命名变量、常量、游标、过程、函数、包、触发器、表类型、异常等程序元素的名称。它们是程序员为了识别和引用特定对象而自定义的名称。

举例说明：

1. 变量（Variables）

• • 示例：声明一个名为 employee_name 的变量用于存储员工姓名。

Sql
1DECLARE
2    employee_name VARCHAR2(50);
3BEGIN
4    -- 一些操作...
5    employee_name := 'John Doe';
6END;

1. 常量（Constants）

• • 示例：声明一个名为 MAX_EMPLOYEES 的常量表示公司最大员工数限制。

Sql
1DECLARE
2    MAX_EMPLOYEES CONSTANT NUMBER := 500;
3BEGIN
4    -- 使用常量进行逻辑判断
5    IF some_count <= MAX_EMPLOYEES THEN
6        -- 执行某些操作...
7    END IF;
8END;

1. 游标（Cursors）

• • 示例：声明一个名为 emp_cursor 的游标来遍历员工表中的记录。

Sql
1DECLARE
2    CURSOR emp_cursor IS
3        SELECT * FROM employees WHERE department_id = 10;
4    v_employee_row employees%ROWTYPE;
5BEGIN
6    OPEN emp_cursor;
7    LOOP
8        FETCH emp_cursor INTO v_employee_row;
9        EXIT WHEN emp_cursor%NOTFOUND;
10        -- 处理每一行数据
11        DBMS_OUTPUT.PUT_LINE(v_employee_row.first_name || ' ' || v_employee_row.last_name);
12    END LOOP;
13    CLOSE emp_cursor;
14END;

1. 过程（Procedures）

• • 示例：声明一个名为 update_salary 的过程，接受员工ID和新的薪水作为参数。

Sql
1CREATE OR REPLACE PROCEDURE update_salary (
2    p_employee_id IN employees.employee_id%TYPE,
3    p_new_salary  IN NUMBER
4) IS
5BEGIN
6    UPDATE employees SET salary = p_new_salary WHERE employee_id = p_employee_id;
7END update_salary;

1. 函数（Functions）

• • 示例：声明一个名为 get_total_salary 的函数，返回指定部门所有员工的总薪水。

Sql
1CREATE OR REPLACE FUNCTION get_total_salary (
2    p_department_id IN departments.department_id%TYPE
3) RETURN NUMBER IS
4    total_salary NUMBER := 0;
5BEGIN
6    SELECT SUM(salary) INTO total_salary FROM employees WHERE department_id = p_department_id;
7    RETURN total_salary;
8END get_total_salary;

每个标识符必须遵循Oracle PL/SQL的命名规则：

• 标识符的第一个字符必须是字母、下划线 (_) 或美元符号 ($)。
• 其余字符可以是字母、数字、下划线或美元符号。
• 标识符长度不能超过30个字符（在Oracle数据库12c及更高版本中，对于全局临时表空间中的对象，标识符长度可扩展至128个字符）。
• 标识符默认不区分大小写，但在Oracle数据库中可以通过设置数据库的初始化参数来实现区分大小写（通常不建议这样做）。

PL/SQL各种符号(; = + - 等等)

分隔符	描述
+, -, *, /	加法、减法/否定、乘法、除法
%	属性指示符
'	字符串分隔符
.	组件选择器
(,)	表达式或列表分隔符
:	主变量指示符
,	项目分隔符
"	带引号的标识符分隔符
=	关系运算符
@	远程访问指示器
;	语句终止符
:=	赋值运算符
=>	关联运算符
||	连接运算符
**	指数运算符
<<, >>	标签分隔符（开始和结束）
/*, */	多行注释分隔符（开始和结束）
--	单行注释指示器
..	范围运算符
<, >, <=, >=	关系运算符
<>, '=, ~=, ^=	不等于的不同版本

PL/SQL 注释

单行注释：--

多行注释：/* */

数据类型(等价于java的int，string这个)

SCALAR（包含NUMBER、DATE 或 BOOLEAN等）

没有内部组件的单个值，例如 NUMBER、DATE 或 BOOLEAN。

Large Object (LOB)指向型数据类型

指向与其他数据项（例如文本、图形图像、视频剪辑和声音波形）分开存储的大型对象的指针。

标量数据类型

PL/SQL 提供了数据类型的子类型。 例如，数据类型 NUMBER 有一个称为 INTEGER 的子类型。 您可以在 PL/SQL 程序中使用子类型来使数据类型与其他程序中的数据类型兼容，同时将 PL/SQL 代码嵌入到另一个程序中，例如 Java 程序。

Numeric（对其执行算术运算的数值）

Character（表示单个字符或字符串的字母数字值。）

Boolean（对其执行逻辑操作的逻辑值。）

Datetime（日期和时间。）

PL/SQL 字符数据类型和子类型

以下是 PL/SQL 预定义字符数据类型及其子类型的详细信息 −

S.No	数据类型与描述
1	CHAR 最大长度为 32,767 字节的定长字符串
2	VARCHAR2 最大长度为 32,767 字节的可变长度字符串
3	RAW 最大大小为 32,767 字节的可变长度二进制或字节字符串，不由 PL/SQL 解释
4	NCHAR 最大长度为 32,767 字节的定长国家字符串
5	NVARCHAR2 最大长度为 32,767 字节的可变长度国家字符串
6	LONG 最大长度为 32,760 字节的可变长度字符串
7	LONG RAW 最大大小为 32,760 字节的可变长度二进制或字节字符串，不由 PL/SQL 解释
8	ROWID 物理行标识，普通表中的行地址
9	UROWID 通用行标识符（物理、逻辑或外部行标识符）

PL/SQL 数值数据类型和子类型

下表列出了 PL/SQL 预定义的数值数据类型及其子类型 −

S.No	数据类型与描述
1	PLS_INTEGER -2,147,483,648 到 2,147,483,647 范围内的有符号整数，以 32 位表示
2	BINARY_INTEGER -2,147,483,648 到 2,147,483,647 范围内的有符号整数，以 32 位表示
3	BINARY_FLOAT 单精度 IEEE 754 格式浮点数
4	BINARY_DOUBLE 双精度 IEEE 754 格式浮点数
5	NUMBER(prec, scale) 绝对值在 1E-130 到（但不包括）1.0E126 范围内的定点或浮点数。 一个 NUMBER 变量也可以表示 0
6	DEC(prec, scale) ANSI 特定定点类型，最大精度为 38 位十进制数字
7	DECIMAL(prec, scale) IBM 特定定点类型，最大精度为 38 位十进制数字
8	NUMERIC(pre, secale) 浮点型，最大精度为 38 位十进制数字
9	DOUBLE PRECISION ANSI 特定浮点类型，最大精度为 126 位二进制位（约 38 位十进制位）
10	FLOAT ANSI 和 IBM 特定的浮点类型，最大精度为 126 位二进制位（大约 38 位十进制位）
11	INT ANSI 特定整数类型，最大精度为 38 位十进制数字
12	INTEGER ANSI 和 IBM 特定整数类型，最大精度为 38 位十进制数字
13	SMALLINT ANSI 和 IBM 特定整数类型，最大精度为 38 位十进制数字
14	REAL 浮点型，最大精度为 63 位二进制（约 18 位十进制）

PL/SQL 布尔数据类型

BOOLEAN 数据类型存储用于逻辑运算的逻辑值。 逻辑值是布尔值 TRUE 和 FALSE 以及值 NULL。

但是，SQL 没有与 BOOLEAN 等效的数据类型。 因此，布尔值不能用于 −

• SQL 语句
• 内置 SQL 函数（如 TO_CHAR）
• 从 SQL 语句调用的 PL/SQL 函数

PL/SQL 日期时间和间隔类型

DATE 数据类型用于存储固定长度的日期时间，其中包括自午夜以来的时间（以秒为单位）。 有效日期范围从公元前 4712 年 1 月 1 日到公元 9999 年 12 月 31 日。

默认日期格式由 Oracle 初始化参数 NLS_DATE_FORMAT 设置。 例如，默认值可能是"DD-MON-YY"，其中包括一个两位数的月份日期、月份名称的缩写和年份的最后两位数。 例如，01-OCT-12。

每个 DATE 包括世纪、年、月、日、小时、分钟和秒。 下表显示了每个字段的有效值 −

字段名称	有效日期时间值	有效区间值
YEAR	-4712 到 9999（不包括第 0 年）	任意非零整数
MONTH	01 to 12	0 to 11
DAY	01 到 31（受 MONTH 和 YEAR 的值限制，根据 locale 的日历规则）	任意非零整数
HOUR	00 到 23	0 到 23
MINUTE	00 到 59	0 到 59
SECOND	00 到 59.9(n)，其中 9(n) 是时间小数秒的精度	0 到 59.9(n)，其中 9(n) 是间隔小数秒的精度

PL/SQL 大对象 (LOB) 数据类型

大型对象 (LOB) 数据类型是指大型数据项，例如文本、图形图像、视频剪辑和声音波形。 LOB 数据类型允许对这些数据进行高效、随机、分段的访问。 以下是预定义的 PL/SQL LOB 数据类型 −

数据类型	描述	大小
BFILE	用于将大型二进制对象存储在数据库外的操作系统文件中。	取决于系统。 不能超过 4 GB。
BLOB	用于在数据库中存储大型二进制对象。	8 到 128 太字节 (TB)
CLOB	用于在数据库中存储大块字符数据。	8 到 128 TB
NCLOB	用于在数据库中存储大块 NCHAR 数据。	8 到 128 TB

PL/SQL 中的 NULL 空值

PL/SQL NULL 值表示缺失或未知数据，它们不是整数、字符或任何其他特定数据类型。 请注意，NULL 与空数据字符串或空字符值 '\0' 不同。 可以分配一个空值，但它不能等同于任何东西，包括它自己。

使用示例

DECLARE 
   num1 INTEGER; 
   num2 REAL; 
   num3 DOUBLE PRECISION; 
BEGIN 
   null; 
END; 
/ 

变量（类似于实现类定义的一个个String name = "zhangsan";）

变量是我们的程序可以操作的存储区域的名称。 PL/SQL 中的每个变量都有特定的数据类型，它决定了变量内存的大小和布局

变量声明的语法

个数
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value] 

人话：（备注：也可给变量类型添加大小）
1：变量名称   变量类型 := 变量值
2：变量名称   变量类型 default 变量值
2：变量名称   变量类型 not null default 变量值    （添加约束变量）
3：变量名称   变量类型;     （初始化后为null）


示例：
declare
    message varchar2(20) := 'hello word!';
    message varchar2(20) default 'hello word!';
    message varchar2(20) not null := 'hello word!';
    message varchar2(20);
begin
    DBMS_OUTPUT.PUT_LINE(message);
END;

DECLARE 
   a integer := 10; 
   b integer := 20; 
   c integer; 
   f real; 
BEGIN 
   c := a + b; 
   dbms_output.put_line('Value of c: ' || c); 
   f := 70.0/3.0; 
   dbms_output.put_line('Value of f: ' || f); 
END; 
/  

打印：
Value of c: 30 
Value of f: 23.333333333333333333  

PL/SQL procedure successfully completed. 

变量的作用域(等价于JAVA的作用域)

PL/SQL 允许嵌套块，即每个程序块可以包含另一个内部块。 如果变量在内部块中声明，则外部块无法访问它。

• Local 局部变量 − 在内部块中声明且外部块无法访问的变量。
• Global 全局变量 − 在最外层块或包中声明的变量。

declare
    -- 全局变量
    message1 varchar2(20)          := 'hello word!';
    message2 varchar2(20) default 'hello word!';
    message3 varchar2(20) not null := 'hello word!';
    message4 varchar2(20);
begin
    --      DBMS_OUTPUT.PUT_LINE(message1 || message5); 这里没法成功，因为message5是局部变量
    -- 局部变量
    declare
        message5 varchar2(20);
    begin
        DBMS_OUTPUT.PUT_LINE(message1 || message5);
    end;
END;

%TYPE 引用型变量(引用表字段数据类型，随表字段变化而变化)

%TYPE 关键字用于声明一个变量，并让其数据类型与数据库中的某个列相同。这样做的好处是，如果表结构发生变化（例如，列的数据类型改变），相关的变量也会自动适应新的数据类型，无需手动修改代码。
DECLARE
    -- 声明一个名为 emp_salary 的变量，其数据类型与 employees 表的 salary 列相同
    emp_salary employees.salary%TYPE;
    
    v_employee_id employees.employee_id%TYPE := 100; -- 假设我们有一个已知的员工ID
BEGIN
    SELECT salary INTO emp_salary FROM employees WHERE employee_id = v_employee_id;
    DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || emp_salary);
END;

%ROWTYPE 记录型变量（引用表 一行的 数据）

%ROWTYPE 关键字用于声明一个记录类型变量，该变量包含了对应表中一行的所有列及其相应的数据类型。它可以用来一次性处理表中的整行数据。
DECLARE
    -- 声明一个名为 emp_record 的变量，其数据类型与 employees 表的一行记录完全一致
    emp_record employees%ROWTYPE;
BEGIN
    -- 通过主键查询员工记录并填充到记录型变量中
    SELECT * INTO emp_record FROM employees WHERE employee_id = 100;

    -- 输出员工的姓名和薪水
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_record.first_name || ' ' || emp_record.last_name);
    DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || emp_record.salary);
END;

INTO（表数据赋值给变量）

SELECT INTO 语句为 PL/SQL 变量赋值。 对于SELECT 列表 中的每一项，INTO 列表 中必须有一个对应的、类型兼容的变量。

DECLARE 
   c_id customers.id%type := 1; 
   c_name  customers.name%type; 
   c_addr customers.address%type; 
   c_sal  customers.salary%type; 
BEGIN 
   SELECT name, address, salary INTO c_name, c_addr, c_sal 
   FROM customers 
   WHERE id = c_id;  
   dbms_output.put_line 
   ('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal); 
END; 
/

常量（一旦定义，不可改变）

语法

常量名  CONSTANT(常量关键字) 常量类型 := 常量值;

PI CONSTANT NUMBER := 3.141592654; 

DECLARE
2    -- 声明一个名为MAX_EMPLOYEES的常量，表示公司最大员工数限制
3    MAX_EMPLOYEES CONSTANT NUMBER := 500;
4
5    -- 声明一个名为PI的常量，用于存储圆周率
6    PI CONSTANT NUMBER := 3.14159265358979323846;
7
8BEGIN
9    -- 使用常量进行逻辑判断和计算
10    IF (SELECT COUNT(*) FROM employees) > MAX_EMPLOYEES THEN
11        DBMS_OUTPUT.PUT_LINE('公司员工数量超过最大限制：' || MAX_EMPLOYEES);
12    END IF;
13
14    -- 计算半径为1米的圆的面积
15    DECLARE
16        radius CONSTANT NUMBER := 1; -- 半径也为常量
17        circle_area NUMBER;
18    BEGIN
19        circle_area := PI * POWER(radius, 2);
20        DBMS_OUTPUT.PUT_LINE('半径为1米的圆面积是： ' || circle_area || ' 平方米');
21    END;
22END;

在字符串文字中嵌入单引号，请将两个单引号并排放置

DECLARE 
   message  varchar2(30):= 'That''s tutorialspoint.com!'; 
BEGIN 
   dbms_output.put_line(message); 
END; 
/  

That's tutorialspoint.com!  

条件判断语句

if then end if （单选）

IF (a <= 20 这个是条件表达式)
THEN c:= c+1;  如果为真执行Then后面的代码
END IF;  结束if判断

if then else end if （双选）

declare
   a number(2):=1;
Begin
    if a=1
        then a:= a+1;
        else a:= a+2;  上一个不成立，就走这给一个。经我测试，好像只能写一个else
        end if;
end;

if elsif then ...(多个elsif then) end if （多选）

declare
   a number(2):=1;
Begin
    if a=1 then a:= a+1;
    elsif (a= 2) then a:= a+2;
    elsif a= 3 then a:= a+3;
    elsif a= 4 then a:= a+4;
    end if;
end;

CASE 语句（等价IF，但是可以SQL语句用）

CASE selector 
   WHEN 'value1' THEN S1; 
   WHEN 'value2' THEN S2; 
   WHEN 'value3' THEN S3; 
   ... 
   ELSE Sn;  -- default case 
END CASE;

DECLARE 
   grade char(1) := 'B'; 
BEGIN 
   case  
      when grade = 'A' then dbms_output.put_line('Excellent'); 
      when grade = 'B' then dbms_output.put_line('Very good'); 
      when grade = 'C' then dbms_output.put_line('Well done'); 
      when grade = 'D' then dbms_output.put_line('You passed'); 
      when grade = 'F' then dbms_output.put_line('Better try again'); 
      else dbms_output.put_line('No such grade'); 
   end case; 
END; 
/

IF嵌套

DECLARE 
   a number(3) := 100; 
   b number(3) := 200; 
BEGIN 
   -- 检查布尔条件
   IF( a = 100 ) THEN 
   -- 如果条件为真，则检查以下内容
      IF( b = 200 ) THEN 
      -- 如果条件为真，则打印以下内容
      dbms_output.put_line('Value of a is 100 and b is 200' ); 
      END IF; 
   END IF; 
   dbms_output.put_line('Exact value of a is : ' || a ); 
   dbms_output.put_line('Exact value of b is : ' || b ); 
END; 
/ 

循环

Loop（java for循环）

loop 
  逻辑
exit when 终止循环条件
end loop;

declare
    x number(10) := 10;
begin
    Loop  （循环的固定格式）
        dbms_output.put_line(x); 
        x := x + 10;
        exit when x > 50; （exit when代表，如果x>50,则循环结束.每次+10，所以60结束循环）
    end loop;  （循环的固定格式）
    dbms_output.put_line('最后输出这句: x=' || x);   （这里最后输出：最后输出这句=60）
end ;

DECLARE 
   x number := 10; 
BEGIN 
   LOOP 
      dbms_output.put_line(x); 
      x := x + 10; 
      IF x > 50 THEN 
         exit; 
      END IF; 
   END LOOP; 
   -- after exit, control resumes here  
   dbms_output.put_line('After Exit x is: ' || x); 
END; 
/

WHILE （跟 java for循环 没区别）

备注：他这里不是输出刚好大于循环条件的值，而是输出最后一个符合循环条件的值。

如：条件a < 20，则a最后输出为19

while 终止循环条件 loop
  逻辑代码
end loop;

DECLARE 
   a number(2) := 10; 
BEGIN 
   WHILE a < 20 LOOP 
      dbms_output.put_line('value of a: ' || a); 
      a := a + 1; 
   END LOOP; 
END; 
/ 

备注：他这里最终会输出19.
      而不是20.如果是java先判断是19<20,然后打印语句，再接着执行19+1,然后while不成立，
      输出20.但这个pl/sql输出的是19

For（范围循环）

格式：
for  变量名  in  起始值 .. 结束值 loop

end loop 结束循环

DECLARE 
   a number(2); 
BEGIN 
   FOR a in 1 .. 3 LOOP 
      dbms_output.put_line('value of a: ' || a); 
  END LOOP; 
END; 
/

输出：value of a:1   value of a:2    value of a:3

reverse(For循环 加此 关键字 倒叙输出)

DECLARE 
   a number(2) ; 
BEGIN 
   FOR a IN REVERSE 1 .. 3 LOOP 
      dbms_output.put_line('value of a: ' || a); 
   END LOOP; 
END; 
/

输出：value of a:3   value of a:2    value of a:1

循环 关键字 解释

exit (立即结束当前循环)

Exit 语句完成循环并将控制传递给紧接在 END LOOP 之后的语句。

1:当在循环中遇到 EXIT 语句时，循环立即终止
2:嵌套循环,EXIT 语句将停止执行最内层循环并开始执行该块之后的下一行代码。

DECLARE 
   a number(2) := 10; 
BEGIN 
   -- while loop execution  
   WHILE a < 20 LOOP 
      dbms_output.put_line ('value of a: ' || a); 
      a := a + 1; 
      IF a > 15 THEN 
         -- terminate the loop using the exit statement 
         EXIT; 
      END IF; 
   END LOOP; 
END; 
/ 

value of a: 10 
value of a: 11 
value of a: 12 
value of a: 13 
value of a: 14 
value of a: 15  

exit when (立即结束当前循环)

Exit 语句完成循环并将控制传递给紧接在 END LOOP 之后的语句。

EXIT-WHEN 语句允许评估 WHEN 子句中的条件。 如果条件为真，则循环完成，控制权立即传递到 END LOOP 之后的语句。

DECLARE 
   a number(2) := 10; 
BEGIN 
   -- while loop execution  
   WHILE a < 20 LOOP 
      dbms_output.put_line ('value of a: ' || a);  
      a := a + 1; 
      -- terminate the loop using the exit when statement 
   EXIT WHEN a > 15; 
   END LOOP; 
END;   
/

value of a: 10 
value of a: 11 
value of a: 12 
value of a: 13 
value of a: 14 
value of a: 15  

continue（跳过当前，执行下一轮）