Oracle PL/SQL开发入门篇知识汇总-CSDN博客

本文链接：https://blog.csdn.net/qq_51831335/article/details/147918218

第一章　Oracle 11g数据库系统

1.1　什么是关系型数据模型

关系型数据库简而言之就是使用关系或二维表存储信息。
关系型数据库设计人员通过使用实体关系模型进行数据库建模。
例如人员信息表可以分为员工表和部门表，通过部门编号进行关联，使得获取和维护数据变得容易，这就是关系型数据模型。
它的3个关键组件分别为：
- 实体：需要了解的信息，比如部门和员工信息；
- 属性：一般也成为列或字段，描述实体必须火可选的信息，比如员工表中的工号和姓名等；
- 关系：实体之间指定的关联，比如员工的部门编号关联到了部门表的编号属性。

1.2　数据库系统范式

第一范式（1NF）：字段必须具有单一数据特性，不可再拆分；
第二范式（2NF）：表要具有唯一性的主键列；
第三范式（3NF）：表中的字段不能包含在其他表中已出现的非主键字段。

1.3　关系型数据库管理系统

关系型数据库管理系统，简称DBMS，是基于关系型数据库理论而开发的软件系统。目前比较热门的关系型数据库管理系统有：Oracle、Microsoft SQL Server、Access、MySQL、PostgreSQL等。
一个数据库管理系统通常要提供以下几项功能：

- 定义数据库结构：DBMS提供数据定义语言（DDL）来定义数据库结构，用来搭建数据库框架，并被保存在数据字典中。
- 存取数据库内容：DBMS提供数据操纵语言（DML），实现对数据库数据的基本存取操作，即检索、插入、修改和删除等。
- 数据库的运行管理：DBMS提供数据控制功能，即数据的安全、完整性和并发控制等，对数据库运行进行有效的控制和管理，以确保数据正确有效。
- 数据库的建立和维护：包括数据库初识数据的装入，数据库的转储、恢复、重组织，系统性能监视、分析等功能。
- 数据库的传输：DBMS提供处理数据的传输，实现用户程序与DBMS之间的通信，通常与操作系统协调完成。

1.4　使用SQL语句与数据库管理系统通信

关系型数据库管理系统提供了SQL语言，允许用户操纵数据库。SQL语言的全称是结构化查询语言（Structured Query Language），是高级的非过程化编程语言，允许用户在高层数据结构上工作。
SQL语言主要分为：

- DML数据操纵语言，主要是完成数据的增、删、改、查的操作。
- DDL数据定义语言，主要用来创建和修改表、视图、存储过程及用户等。
- DCL数据控制语言。

下面有更加详细的解释，DCL不重要不做解释。

一、DDL（Data Definition Language，数据定义语言）：DDL是用来定义数据库结构和对象的SQL语句，主要负责创建、修改和删除数据库中的表、索引、视图、用户等对象。DDL语句主要用于定义和管理数据库的结构，它们会改变数据库的元数据（数据字典），执行后通常会自动提交（commit），无法回滚。

二、DML（Data Manipulation Language，数据操作语言）：DML是用来操作数据库中已有数据的SQL语句，主要负责查询、插入、更新和删除数据。DML语句主要用于操作和管理数据库中的数据，执行后不会自动提交，需要显式提交（commit）或回滚（rollback）事务。

PL/SQL块中可以直接使用DML语句，比如INSERT、UPDATE、DELETE、SELECT INTO等，用于操作数据。
DDL语句不能直接写在PL/SQL块中执行，因为DDL语句会隐式提交事务，Oracle不允许在PL/SQL块中直接执行DDL语句，否则会报“绑定错误”或“语法错误”。

1.5　Oracle体系结构

一个Oracle数据库服务器包括如下两个方面：

- 存储Oracle数据的物理数据库，即保存Oracle数据库数据的一系列物理文件，包含控制文件、数据文件、日志文件和其他文件。
- Oracle实例：这是物理数据库和用户之间的一个中间层，用来分配内存，运行各种后台进程，这些分配的内存区和后台进程统称为Oracle实例。

当用户在客户端连接并使用数据库时，实际上是连接到该数据库的实例，由实例来连接、使用数据库。

1.6　比较Oracle数据库与SQL Server数据库

        Oracle的体系结构与SQL Server有着本质上的不同。
        Oracle数据库系统是一个跨平台的数据库管理系统，可以运行在Windows、UNIX、Linux等操作系统上，而SQL Server只能运行与微软的操作系统平台。
        在SQL Server中，当用户使用企业管理器连接到某个SQL Server实例后，可以同时管理多个数据库。这是因为SQL Server中，实例就是SQL Server服务器引擎，每个引擎都有一套不为其他实例共享的系统及数据库，因此一个实例可以建多个数据库。
        在Oracle中，实例是由一系列的进程和服务组成的，与数据库可以是一对一的关系，也就是说一个实例可以管理一个数据库；也可以是多对一的关系，也就是说多个实例可以管理一个数据库，其中多个实例组成一个数据库的架构成为集群，简称为RAC（Oracle Application Clusters）。
在文件存储、日志管理、方案管理、事务处理、安全性管理等方面，两者都存在非常大的区别，这里先不详细说了。

第二章　PL/SQL基本概念

2.1　什么是PL/SQL

标准的SQL语言提供了定义和操纵数据库对象的能力，但是并没有提供程序设计语言所具有的诸多特性。PL/SQL（Procedural Language/SQL）就是为标准SQL语言添加了过程化功能的一门程序设计语言。这让SQL语句拥有了结构化程序设计的特性，使得开发人员可以直接使用PL/SQL进行复杂业务逻辑的编写。

在 PL/SQL 中，DECLARE-BEGIN 块（实际应为 DECLARE-BEGIN-EXCEPTION-END 块）是编写匿名 PL/SQL 代码的基本结构单元。它允许你直接在 SQL 工具（如 SQL Developer、PL/SQL Developer）中执行逻辑代码，而无需创建存储过程或函数。

DECLARE
  -- 声明部分（可选）
  -- 定义变量、常量、游标、异常等
BEGIN
  -- 执行部分（必需）
  -- 编写要执行的逻辑代码
EXCEPTION
  -- 异常处理部分（可选）
  -- 处理运行时错误
END;
/

2.1.1　功能特点

结构化程序设计

顺序结构
分支结构
循环结构

（１）与SQL语言整合：要注意的是，在PL/SQL代码中只能直接使用DML语句，如果在PL/SQL代码中直接使用SQL的DDL语句，Oracle将会提示绑定错误，例如这样就是错误的：

BEGIN
    CREATE TABLE BOOKS(ID int NOT NULL,BOOKNAME varchar2(100) NULL);
END;

执行以上代码会报错，这是由PL/SQL的早期绑定特性所决定的，在编译时PL/SQL引擎发现BOOKS表不存在，会引起编译时错误。如果修改为以下写法，就没问题了：

DECLARE
  --定义SQL DDL语句
  v_SQLStr VARCHAR(200):=' CREATE TABLE BOOKS(ID int NOT NULL,BOOKNAME varchar2(100) NULL) ';
BEGIN
  EXECUTE IMMEDIATE v_SQLStr;  --执行DDL语句
END;

（２）面向对象开发：PL/SQL可以基于对象类型来定义对象表，或者将对象类型作为Oracle表列进行保存。

（３）模块化应用程序开发：PL/SQL提供块、子程序和包3个程序单元，可以将程序分成多个部分，将复杂的问题划分开来，做到模块化。

（４）提高应用程序性能：PL/SQL的块结构运行一次性向数据库发送多条SQL语句，可以显著的提升应用程序的性能。在使用Java、.NET之类语言开发程序时，往往是一次一条SQL语句的方式操作数据库，这将会产生多次网络传输交互，是的服务器需要使用较多的资源来处理SQL语句，同时产生了一定的网络流量。

2.1.2　语言特性

（１）PL/SQL块结构：
块分为两种：

- 匿名块，没有名称的PL/SQL块，这种块不会保存在数据库中
- 命名块，具有名称的PL/SQL块

命名块又分为三种：

- 使用<<块名称>>进行标识的块
- 由函数或过程组成的子程序块，这种块保存在数据库中
- 数据库触发器块

（２）变量和类型
        标量变量：指能存放但是数值的变量，这是最常用的变量。标量变量的数据类型包含数字、字符、日期和布尔类型，比如VARCHAR2、CHAR、NUMBER、DATE等类型。
        复合变量：指用于存放多个值的变量，比如PL/SQL记录、PL/SQL表、嵌套表及VARRAY等类型。
        参照变量：指用于存放数值指针的变量，比如游标变量和对象变量。
        LOB变量：指用于存放大批量数据的变量。
例如：

DECLARE
  v_DeptName VARCHAR2(10);        --定义标量变量
  v_LoopCounter BINARY_INTEGER;   --使用PL/SQL类型定义标量变量
  --定义记录类型
  TYPE t_Employee IS RECORD (EmpName VARCHAR2(20),EmpNo NUMBER(7),Job VARCHAR2(20));
  v_Employee t_Employee;          --定义记录类型变量
  TYPE csor IS REF CURSOR;        --定义游标变量
  v_date DATE NOT NULL DEFAULT SYSDATE;--定义变量并指定默认值
BEGIN
 NULL;
END;
/

（３）程序控制语句
        条件控制语句：IF-THEN-ELSE，CASE-WHEN
        循环控制语句：LOOP-END简单循环，数字式FOR循环，WHILE循环
        过程、函数与包
        过程。函数与包都属于命名块，过程和函数统称为子程序。

（４）触发器
与包或其他子程序不同的是，触发器不能被显式的调用，而是在数据库时间发生时隐式的运行的，并且触发器不能接收参数。

（５）结构化异常处理
异常处理块以EXCEPTION开始。在错误发生时，Oracle预定义的异常会被系统隐式的抛出，对于用户自定义的异常，可以使用RAISE语句手动抛出。当异常被抛出后，异常处理器将捕捉到这些异常，然后执行异常处理代码。

（６）集合与记录
        集合与记录都是用复合类型。
        集合允许将类型相同的多个变量当做一个整体进行处理，类似于Java或C语言中的数组，可以同时处理单行多列的数据。
        记录允许将多个不同类型的变量当做一个整体进行处理，类似于数据库中的一条记录。一个记录类型可以包含多个简单类型的值或复杂类型的值。

（７）游标
        游标是一个指向上下文区域的指针，这个上下文区域是PL/SQL语句块在执行SELECT语句或DML数据操纵语句时分配的。游标分为两种：
        - 隐式游标：由PL/SQL自动为DML语句或SELECT-INTO语句分配的游标，包括只返回一条记录的查询操作。
        - 显式游标：在PL/SQL块的声明区域中显示定义的，用来处理返回多行记录查询的游标。

（８）动态SQL
PL/SQL是使用早期绑定来执行SQL语句的，早期绑定要求所处理的数据库对象必须存在并且是已知的。
动态SQL是指在运行时由字符串拼合而成的SQL，比如在PL/SQL块中不能执行DDL语句和DCL语句，那么可以使用EXECUTE IMMEDIATE来执行动态拼合而成的SQL语句。

第三章　变量

3.1　变量的声明

变量的声明位于DECLARE下使用，对于单条数据模板如下：

variable_name datatype [NOT NULL] [:= initial_value];

variable_name：变量名，遵循标识符命名规则。
datatype：变量的数据类型，如NUMBER、VARCHAR2、DATE等。
NOT NULL：可选，表示变量不能为空。
:= initial_value：可选，声明时给变量赋初值。

举例使用：

DECLARE
   v_empname    VARCHAR2 (20);   --定义员工名称变量
   v_deptname   VARCHAR2 (20);   --定义部门名称变量
   v_hiredate   DATE         NOT NULL := SYSDATE;  --定义入职日期变量
   v_empno      INT          NOT NULL DEFAULT 7369;--定义员工编码变量
BEGIN
   NULL;                       --不执行任何代码
END;

3.2　变量的赋值

变量赋值使用:=符号。
赋值语句通常写在BEGIN ... END;块中。

在为变量赋值时，需要注意变量的类型。如：

DECLARE 
   v_string VARCHAR2(200);
   v_hire_date DATE;
   v_bool BOOLEAN;                       --PL/SQL布尔类型
BEGIN
   v_bool:=TRUE;                         --布尔类型赋值
   v_hire_date:=to_date('2011-12-13','yyyy-mm-dd');  --使用函数为日期赋值   
   v_hire_date:=SYSDATE;                --使用日期函数赋值
   v_hire_date:=date'2011-12-14';       --直接赋静态日期值    
   v_string:='This is a string';        --赋静态字符串    
END;

3.3　使用％TYPE

%TYPE用于声明一个变量的数据类型与某个表的某个字段（列）或另一个变量的数据类型相同。

自动继承表字段的数据类型。
如果表字段的数据类型发生变化，PL/SQL变量的数据类型会自动更新，避免代码出错。
使代码更灵活、易维护。

使用格式：

variable_name table_name.column_name%TYPE;

使用案例：

DECLARE
   v_empno emp.empno%TYPE;    --使用%TYPE定义emp表empno列类型的变量
   v_empno2 v_empno%TYPE;     --定久与v_empno相同的变量
   v_salary NUMBER(7,3) NOT NULL:=1350.5;  --定义薪水变量
   v_othersalary v_salary%TYPE;            --定义与v_salary相同类型的变量
BEGIN
   NULL;
END;

3.4　使用%ROWTYPE

%ROWTYPE用于声明一个记录类型变量，它的结构与某个表或游标的整行数据结构完全相同。

%TYPE仅绑定到单个数据库列的类型，而%ROWTYPE绑定到一整行的所有列类型，可以将使用%ROWTYPE定义的变量看作是一条记录类型。

变量可以一次性存储表中一整行的所有列数据。
方便处理整行数据，避免逐列声明变量。
结构自动与表结构同步。

使用格式：

variable_name table_name%ROWTYPE;

使用案例：

DECLARE
   v_emp   emp%ROWTYPE;                               --定义emp表的所有列类型
BEGIN
   SELECT *                               --查询emp表并将结果写入到v_emp记录中
     INTO v_emp
     FROM emp
    WHERE empno = &empno;

   --输出结果信息
   DBMS_OUTPUT.put_line (v_emp.empno || CHR (13) || CHR (10) || v_emp.ename);
END;

DECLARE
   v_emp emp%ROWTYPE;              --定义emp表列类型的记录
BEGIN
   v_emp.empno:=8000;              --为记录类型赋值
   v_emp.ename:='张三丰';
   v_emp.job:='掌门';
   v_emp.mgr:=7902;
   v_emp.hiredate:=date'2010-12-13';
   v_emp.sal:=8000;
   v_emp.deptno:=20;
   INSERT INTO emp VALUES v_emp;   --将记录类型插入到数据表
END;

3.5 常量的定义

常量是值在程序执行期间不可改变的变量。
一旦赋值，常量的值不能被修改。
常量通常用来表示固定不变的数值，比如税率、折扣率、最大限制等。

【常量的作用】：

提高代码可读性：用有意义的名字代替魔法数字（magic numbers）。
防止误修改：避免程序中意外修改关键数值。
方便维护：修改常量值只需改一处，程序其他地方自动生效。
增强程序健壮性：减少硬编码错误。

常量的定义格式：

constant_name CONSTANT datatype := value;

constant_name：常量名，通常用大写字母表示，便于区分。
CONSTANT：关键字，表示这是一个常量。
datatype：数据类型，如NUMBER、VARCHAR2等。
:= value：必须赋初值，且以后不可更改。

使用案例：

DECLARE
   c_salary_rate   CONSTANT NUMBER (7, 2) := 0.25;  --定义加薪常量值
   v_salary                 NUMBER (7, 2);          --定义保存薪资结果的变量
BEGIN
   SELECT sal * (1 + c_salary_rate)                 --查询数据库，返回架薪后的结果
     INTO v_salary
     FROM emp
    WHERE empno = &empno;
   --输出屏幕消息
   DBMS_OUTPUT.put_line ('加薪后的薪资：' || v_salary);
END;

第四章　数据类型

4.1　字符类型

（１）［ｃｈａｒ］

DECLARE
   v_name CHAR(2 BYTE);
   v_name2 CHAR(2 CHAR);
   v_name3 CHAR;
   v_name4 CHAR(20);
BEGIN
  v_name:='ab';       --正确，2个字节的字符串
  v_name:='中国';   --错误，大于2个字节 
  v_name2:='中国';    --正确，2个字符
  v_name3:=1;         --正确，单个字节
  v_name4:='This is string';  --为CHAR赋字符串值
  DBMS_OUTPUT.put_line(LENGTH(v_name4));--输出字符串长度
END;

（２）VARCHAR2

DECLARE
  v_name VARCHAR2(25);
  v_name1 VARCHAR2(25 BYTE);
  v_name2 VARCHAR2(25 CHAR);   
  --v_name3 VARCHAR2;          --错误，必须要为VARCHAR2指定长度值
BEGIN
  v_name:='中华人民共和国';    --为变量赋值，并输出变量的长度
  DBMS_OUTPUT.put_line('v_name变量的长度为：'||LENGTH(v_name)||'字节');
  v_name1:='中华人民共和国';
  DBMS_OUTPUT.put_line('v_name1变量的长度为：'||LENGTH(v_name1)||'字节');  
  v_name2:='中华人民共和国';
  DBMS_OUTPUT.put_line('v_name1变量的长度为：'||LENGTH(v_name2)||'字节');    
END;

（３）　LONG和LONG RAW类型

在Oracle 11g中，LONG和LONG RAW数据类型仅是为了保持向后兼容性，我们应该尽量避免使用这两个数据类型。
对于LONG类型，可以使用VARCHAR2(32760)、BLOB、CLOB或NCLOB来代替。
对于LONG RAW类型可以使用BLOB来代替。
LONG类型和VARCHAR2非常相似，但是LONG类型的最大长度是32760字节，比VARCHAR2的最大长度少了7字节。
LONG RAW类型用来存储二进制数据和字节字符串，LONG RAW数据和LONG数据相似，最大字节数也为32760。
Oracle数据库同样提供了LONG和LONG RAW列类型，与PL/SQL不同的是，这两个类型的最大存储数据量是2GB。
在SQL语句中，PL/SQL将LONG类型的值当做VARCHAR2进行处理而不是LONG类型，如果长度超过VARCHAR2允许的最大长度即4000字节时，Oracle会自动转换成LONG类型。

（４）　ROWID和UROWID类型：

每个Oracle数据表都有一个名为ROWID的伪列，这个伪列用来存放每一行数据的存储地址的二进制值。
每个ROWID的值是由18个字符组合进行表示的，ROWID又有物理ROWID和逻辑ROWID之分。物理ROWID用来标识普通数据表中的一行信息，而逻辑ROWID能够标识索引组织表中的一行信息。
物理的ROWID可以显著的加速数据检索的性能，因为只要行存在，物理ROWID值就不会改变。

（５）NCHAR和NVARCHAR2类型：这两个类型是CHAR和VARCHAR2的Unicode版本，通常在开发多语言程序时非常有用。

4.2　数字类型

（１）　NUMBER类型

DECLARE
   v_num1 NUMBER:=3.1415926;           --结果：3.1415926
   v_num2 NUMBER(3):=3.1415926;        --四舍五入等于3
 --v_num2_1 NUMBER(3):=3145.1415926;   --错误，精度太高：ORA-06502:PL/SQL:数字或值错误:数值精度太高   
   v_num3 NUMBER(4,3):=3.1415926;      --结果：3.142
 --v_num3_1 NUMBER(4,3):=314.123;      --错误，精度太高：ORA-06502:PL/SQL:数字或值错误:数值精度太高   
   v_num4 NUMBER(8,3):=31415.9267;     --四舍五入2位小数，结果为：31415.927
   v_num5 NUMBER(4,-3):=3145611.789;   --由于为负3,要小数点左侧进行舍入3位结果为31000
   v_num5_1 NUMBER(4,-3):=314.567895;  --舍入后的结果为0
   v_num6 NUMBER(4,-1):=31451;         --舍入后结果31450  
 --v_num6_1 NUMBER(4,-1):=3145123;     --错误，精度太高：ORA-06502:PL/SQL:数字或值错误:数值精度太高   
BEGIN
  DBMS_OUTPUT.put_line('v_num1:='||v_num1);
  DBMS_OUTPUT.put_line('v_num2:='||v_num2);
  DBMS_OUTPUT.put_line('v_num3:='||v_num3);
  DBMS_OUTPUT.put_line('v_num4:='||v_num4);      
  DBMS_OUTPUT.put_line('v_num5:='||v_num5);    
  DBMS_OUTPUT.put_line('v_num5_1:='||v_num5_1);  
  DBMS_OUTPUT.put_line('v_num6:='||v_num6);          
END;

（２）　PLS_INTEGER和BINARY_INTEGER类型

PLS_INTEGER和BINARY_INTEGER具有相同的取值范围，都是从-2147483647到+2147483647，PLS_INTEGER相对于NUMBER来说需要更少的空间来存储数据，在计算方面也比NUMBER更有效率。 NUMBER数据类型是以十进制格式进行存储的，为了进行算术运算，NUMBER必须要转换为二进制类型，因此效率比较慢。BINARY_INTEGER以2的补码二进制形式进行存储，可以直接进行计算二无需转换。 PLS_INTEGER与BINARY_INTEGER类似，也是使用2的补码格式进行计算，他们的区别是，如果在为PLS_INTEGER类型的变量赋的值溢出时，会触发异常；而为BINARY_INTEGER类型的变量赋的值溢出时，会将结果指派为NUMBER类型的拥有最大精度的类型，不会触发异常。

4.3　日期和时间类型

（１）##DATE

DATE类型用来存储时间和日期信息，包含世纪、年、月、日、时、分、秒，但是不包含秒的小数部分。DATE类型从世纪到秒每一部分是一个字节，占用7个字节。
DATE类型的有效日期范围是从公元前4712年1月1号到公元9999年12月31号，默认的日期格式是由Oracle的初始换参数NLS_DATA_FORMAT来设置的。

（２)　TIMESTAMP

TIMESTAMP与DATE类似，存储了年、月、日、时、分、秒，而且还存储秒字段的小数部分。

（３）　TIMESTAMP WITH TIME ZONE类型

TIMESTAMP WITH TIME ZONE是TIMESTAMP的扩展，包含了时区偏移信息，时区偏移部分就是指当前时间和格林威治时间的差异部分。
可以通过查询V$TIMEZONE_NAMES系统视图来获取更多的时区的字符串表示形式，或者查询SESSIONTIMEZONE来获取当前会话的时区：SELECT SESSIONTIMEZONE FROM DUAL;

（４）　TIMESTAMP WITH LOCAL TIME ZONE

该类型存储的是数据库的时区，不管在PL/SQL代码中用的时区是什么，它总是使用数据库的时区。

（５）　INTERVAL类型

INTERVAL类型用于存储两个时间戳之间的时间间隔，分为两种：

INTERVAL YEAR TO MONTH：用来存储和操纵年和月之间的时间间隔。
INTERVAL DAY TO SECOND：用来存储和操纵天数、时、分和秒之间的时间间隔。
用法如下：

DECLARE
   v_start      TIMESTAMP;                         --定义起始与结束时间戳类型
   v_end        TIMESTAMP;
   v_interval   INTERVAL YEAR TO MONTH;
   v_year       NUMBER;
   v_month      NUMBER;
BEGIN
   v_start := TO_TIMESTAMP ('2010-05-12', 'yyyy-MM-dd');  --赋指定的时间戳值
   v_end := CURRENT_TIMESTAMP;                            --赋当前的时间戳值
   v_interval := (v_end - v_start) YEAR TO MONTH;         --YEAR TO MONTH是INTERVAL表达式语法。
   v_year := EXTRACT (YEAR FROM v_interval);              --提取年份和月份
   v_month := EXTRACT (MONTH FROM v_interval);
   --输出当前的INTERVAL类型的值
   DBMS_OUTPUT.put_line ('当前的INTERVAL值为：' || v_interval);
   --输出年份与月份值
   DBMS_OUTPUT.put_line (   'INTERVAL年份为：'
                         || v_year
                         || CHR (13)
                         || CHR (10)
                         || 'INTERVAL月份为：'
                         || v_month
                        );
   v_interval := INTERVAL '01-03' YEAR TO MONTH;            --直接为INTERVAL赋值
   --输出INTERVAL的值
   DBMS_OUTPUT.put_line ('当前的INTERVAL值为：' || v_interval);
   v_interval := INTERVAL '01' YEAR;                        --直接为INTERVAL赋年份值
   DBMS_OUTPUT.put_line ('当前的INTERVAL值为：' || v_interval);
   --提取年份和月份
   v_year := EXTRACT (YEAR FROM v_interval);
   v_month := EXTRACT (MONTH FROM v_interval);
   --输出值
   DBMS_OUTPUT.put_line (   'INTERVAL年份为：'
                         || v_year
                         || CHR (13)
                         || CHR (10)
                         || 'INTERVAL月份为：'
                         || v_month
                        );
   v_interval := INTERVAL '03' MONTH;                      --直接为INTERVAL赋月份
   --输出月份值
   DBMS_OUTPUT.put_line ('当前的INTERVAL值为：' || v_interval);
END;
/

4.4 布尔类型

Oracle数据库并不包含布尔类型，多数情况下使用CHAR(1)来代替布尔值，PL/SQL为了结构化程序的需要包含了布尔值，不能忘数据库中插入或者从数据库中检索出布尔类型的值。
BOOLEAN类型可以存储True、False和NULL值。

4.5　LOB对象类型

LOB类型又称为大型对象类型，包含了BFILE、BLOB、CLOB和NCLOB等类型，LOB类型最大可存储4GB的非结构数据，通常用来存储文本、图像、声音和视频等大型数据。
LOB类型和LONG、LONG RAW类型相比，不同之处在于：

LOB类型可以作为对象类型的属性，但LONG类型不可以。
LOB类型的最大值是4GB，但LONG只有2GB。
LOB支持随机访问数据，但LONG只支持顺序访问。

第５章　运算符和表达式　

5.1　【运算符类型】赋值运算符

赋值运算符使用:=，如：

DECLARE
   v_variable1           VARCHAR2 (200) := 'This is a '; --定义变量变赋初值
   v_variable2           VARCHAR2 (100);                 --定义变量
   v_result              VARCHAR2 (500);
   v_constant   CONSTANT VARCHAR2 (10)  := 'CONSTANT';   --定义常量赋常量值
BEGIN
   v_variable2 := 'VARIABLE';                            --使用操作数为变量赋值
   v_result := v_variable1 || v_constant;                --使用表达式为变量赋值
   DBMS_OUTPUT.put_line (v_result);                   --输出变量结果值
END;

5.2　【运算符类型】连接运算符

连接运算符使用||，如：

DECLARE
   x   VARCHAR2 (8) := '你好，';       --定义字符串变量并赋初值
   y   VARCHAR2 (8) := '中国';
BEGIN
   DBMS_OUTPUT.put_line (x || y);  --输出字符串变量值
END;

连接运算符会忽略NULL值。

5.3　【运算符类型】逻辑运算符

逻辑运算符有AND、OR和NOT。

5.4【运算符类型】比较运算符

比较运算符用于比较两个表达式，结果总是True、False或NULL，如果表达式中任意一个值为NULL，则整个比较结果也为NULL。

比较运算符	描述
`=`	等于
`<>,!=,~=,^=`	不等于
`<`	小于
`>`	大于
`<=`	小于等于
`>=`	大于等于
`IS [NOT] NULL`	是否为空
`LIKE`	通配符比较
`BETWEEN`	范围比较
`IN`	判断值是否在某个指定的结果集中运算符的优先级优先级顺序运算符描述 1 ** 乘方 2 +,- 一元操作符正、负 3 *,/ 乘、除 4 `+,-, 5 =,<,>,<=,>=,<>,!=,~=,^=,IS NULL,LIKE,BETWEEN,IN 比较 6 NOT 逻辑否 7 AND 逻辑否 8 OR 逻辑或不过在开发中，我们不需要记得这么多优先级，想要优先的运算用小括号()括起来就行了，避免混乱。

5.5 表达式类型

表达式按照计算的结果值的数据类型划分，分为4种：

- 数值型表达式，比如5+6。
- 字符型表达式，如"This is a" || "ecpression"。
- 日期型表达式，如SYSDATE - 10。
- 布尔型表达式，如x > 0 AND y < 0。

第６章　控制语句

6.1　分支控制语句　

IF-THEN-ELSE

DECLARE
   v_count   NUMBER (10) := 0;                               --定义计数器变量
   v_empno   NUMBER (4)  := 7888;                              --定义员工编号
BEGIN
   SELECT COUNT (1)                           --首先查询指定的员工编号是否存在
     INTO v_count
     FROM emp
    WHERE empno = v_empno;

   --使用IF语句判断，如果员工编号不存在，结果为0
   IF v_count = 0
   THEN
      --则执行INSERT语句，插入新的员工记录
      INSERT INTO emp
                  (empno, ename, job, hiredate, sal, deptno
                  )
           VALUES (v_empno, '张三', '经理', TRUNC (SYSDATE), 1000, 20
                  );
   END IF;

   --向数据库提交更改
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);                          --输出异常信息
END;

IF-THEN-ELSIF

如：

DECLARE
   v_character   CHAR(1) :=&tmpVar;    --定义替换变量
BEGIN
   IF v_character = 'A'                --判断字符是否为'A'，如果不是，则跳到下一个ELSIF
   THEN
      DBMS_OUTPUT.put_line ('当前输出字符串：' || v_character);
   ELSIF v_character = 'B'             --判断字符是否为'B'，如果不是，则跳到下一个ELSIF
   THEN
      DBMS_OUTPUT.put_line ('当前输出字符串：' || v_character);
   ELSIF v_character = 'C'             --判断字符是否为'C'，如果不是，则跳到下一个ELSIF
   THEN
      DBMS_OUTPUT.put_line ('当前输出字符串：' || v_character);
   ELSIF v_character = 'D'             --判断字符是否为'D'，如果不是，则跳到ELSE语句
   THEN
      DBMS_OUTPUT.put_line ('当前输出字符串：' || v_character);
   ELSE
      DBMS_OUTPUT.put_line ('不是A-D之间的字符');
   END IF;
END;

CASE语句

-- 根据员工职位计算奖金（带异常处理）
DECLARE
   v_empno    NUMBER(4)  := 7900;       -- 员工编号
   v_job      VARCHAR2(20);             -- 职位
   v_sal      NUMBER(7,2);              -- 工资
   v_bonus    NUMBER(7,2) := 0;         -- 奖金
BEGIN
   -- 查询员工信息
   SELECT job, sal 
     INTO v_job, v_sal
     FROM emp
    WHERE empno = v_empno;

   -- 使用CASE语句判断奖金
   CASE 
      WHEN v_job = 'PRESIDENT' THEN
         v_bonus := v_sal * 0.3;
      WHEN v_job IN ('MANAGER','ANALYST') THEN
         v_bonus := v_sal * 0.2;
      WHEN v_job = 'CLERK' THEN
         v_bonus := v_sal * 0.1;
      ELSE
         v_bonus := v_sal * 0.05;
   END CASE;

   -- 更新奖金字段
   UPDATE emp SET comm = v_bonus WHERE empno = v_empno;
   DBMS_OUTPUT.PUT_LINE('奖金更新成功：' || v_bonus);
   COMMIT;

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('员工不存在');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('错误代码：' || SQLCODE || ' 信息：' || SQLERRM);
END;
/

6.2 循环控制语句

6.2.1 基本循环

-- 批量插入部门数据（带提交控制）
DECLARE
   v_deptno   NUMBER(2) := 50;         -- 初始部门编号
   v_counter  NUMBER(3) := 0;          -- 循环计数器
BEGIN
   LOOP
      EXIT WHEN v_counter >= 10;       -- 循环退出条件
      
      INSERT INTO dept(deptno, dname, loc)
           VALUES (v_deptno, 'DEPT_' || v_deptno, 'CITY_' || v_deptno);
      
      v_deptno := v_deptno + 10;       -- 部门号递增
      v_counter := v_counter + 1;      -- 计数器递增
      
      -- 每插入3条提交一次
      IF MOD(v_counter, 3) = 0 THEN
         COMMIT;
      END IF;
   END LOOP;
   
   COMMIT;  -- 最终提交
   DBMS_OUTPUT.PUT_LINE('成功插入' || v_counter || '条记录');

EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      DBMS_OUTPUT.PUT_LINE('部门编号重复：' || v_deptno);
      ROLLBACK;
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('错误代码：' || SQLCODE);
      ROLLBACK;
END;
/

6.2.2 WHILE循环

-- 工资阶梯式调整（带游标操作）
DECLARE
   CURSOR c_emp IS                     -- 声明游标
      SELECT empno, sal FROM emp WHERE deptno = 20;
   v_count  NUMBER := 0;               -- 更新计数器
BEGIN
   OPEN c_emp;                         -- 打开游标
   
   FETCH c_emp INTO v_empno, v_sal;    -- 首次获取数据
   
   WHILE c_emp%FOUND LOOP              -- 循环条件判断
      -- 根据工资范围调整
      IF v_sal < 2000 THEN
         UPDATE emp SET sal = sal * 1.1 WHERE empno = v_empno;
      ELSIF v_sal BETWEEN 2000 AND 4000 THEN
         UPDATE emp SET sal = sal * 1.05 WHERE CURRENT OF c_emp;
      ELSE
         UPDATE emp SET sal = sal * 1.02 WHERE CURRENT OF c_emp;
      END IF;
      
      v_count := v_count + 1;
      FETCH c_emp INTO v_empno, v_sal; -- 获取下一条数据
   END LOOP;
   
   CLOSE c_emp;                        -- 关闭游标
   COMMIT;
   DBMS_OUTPUT.PUT_LINE('共更新' || v_count || '条记录');

EXCEPTION
   WHEN OTHERS THEN
      IF c_emp%ISOPEN THEN CLOSE c_emp; END IF;
      DBMS_OUTPUT.PUT_LINE('异常终止：' || SQLERRM);
      ROLLBACK;
END;
/

6.2.3 FOR循环

-- 批量生成考勤记录（带日期计算）
DECLARE
   v_start_date DATE := TO_DATE('2023-01-01', 'YYYY-MM-DD');
BEGIN
   -- 隐式游标FOR循环
   FOR emp_rec IN (SELECT empno FROM emp WHERE deptno = 30) 
   LOOP
      FOR day_counter IN 1..22 LOOP    -- 循环22个工作日
         INSERT INTO attendance(empno, work_date, status)
              VALUES (emp_rec.empno, 
                     v_start_date + day_counter,
                     CASE 
                        WHEN MOD(day_counter, 10) = 0 THEN '缺勤' 
                        ELSE '正常'
                     END);
      END LOOP;
   END LOOP;
   
   COMMIT;
   DBMS_OUTPUT.PUT_LINE('考勤数据生成完成');

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      ROLLBACK;
END;
/

6.3 顺序控制语句

6.3.1 GOTO语句

-- 紧急数据清理（谨慎使用）
DECLARE
   v_error_flag BOOLEAN := FALSE;
BEGIN
   -- 第一步：验证数据
   IF NOT EXISTS(SELECT 1 FROM temp_table) THEN
      v_error_flag := TRUE;
      GOTO cleanup;  -- 跳转到清理环节
   END IF;

   -- 正常处理流程
   <<process_data>>
   BEGIN
      UPDATE temp_table SET status = 'PROCESSED';
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('数据处理成功');
      RETURN;
   END;

   <<cleanup>>
   IF v_error_flag THEN
      DBMS_OUTPUT.PUT_LINE('发现空表，终止处理');
      ROLLBACK;
   END IF;
END;
/

6.3.2 NULL语句

-- 占位符使用示例
DECLARE
   v_status VARCHAR2(10) := 'PENDING';
BEGIN
   CASE v_status
      WHEN 'APPROVED' THEN
         UPDATE orders SET flag = 'Y';
      WHEN 'REJECTED' THEN
         UPDATE orders SET flag = 'N';
      ELSE
         NULL;  -- 待补充处理逻辑
   END CASE;
   
   DBMS_OUTPUT.PUT_LINE('处理完成');
END;
/

第七章练习题

7.1 题目

编译器是PLSQL developer。
给出执行步骤用plsql 建两个表，关于图书馆图书管理系统的。
要求：（1）往里面写10万条数据，按照一定条件循环搜大概20条，需要打印出来
（2）运行时间不能太长，不能超过10秒吧
（3）最少2个表.

7.2 答案

-- ===========================================
-- 1. 删除旧表（如果存在）
-- ===========================================
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE borrow_records CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL; END;
/

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE books CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL; END;
/

-- ===========================================
-- 2. 创建表结构
-- ===========================================
CREATE TABLE books (
    book_id NUMBER PRIMARY KEY,
    title VARCHAR2(100),
    author VARCHAR2(50),
    category VARCHAR2(30),
    isbn VARCHAR2(20),
    status VARCHAR2(10) CHECK(status IN ('AVAILABLE', 'BORROWED'))
);

CREATE TABLE borrow_records (
    borrow_id NUMBER PRIMARY KEY,
    book_id NUMBER REFERENCES books(book_id),
    borrower_name VARCHAR2(50),
    borrow_date DATE,
    return_date DATE
);

-- ===========================================
-- 3. 创建索引
-- ===========================================
CREATE INDEX idx_books_title ON books(title);
CREATE INDEX idx_books_author ON books(author);
CREATE INDEX idx_books_category ON books(category);
CREATE INDEX idx_books_status ON books(status);

CREATE INDEX idx_borrow_book_id ON borrow_records(book_id);
CREATE INDEX idx_borrow_borrow_date ON borrow_records(borrow_date);

-- ===========================================
-- 4. 创建批量数据生成存储过程
-- ===========================================
CREATE OR REPLACE PROCEDURE generate_library_data IS
    TYPE book_tab IS TABLE OF books%ROWTYPE;
    l_books book_tab := book_tab();

    TYPE borrow_tab IS TABLE OF borrow_records%ROWTYPE;
    l_borrows borrow_tab := borrow_tab();

    v_batch_size CONSTANT NUMBER := 1000;
BEGIN
    -- 生成图书数据
    FOR i IN 1..100000 LOOP
        l_books.EXTEND;
        l_books(l_books.LAST).book_id := i;
        l_books(l_books.LAST).title := 'Book ' || DBMS_RANDOM.STRING('U', 10);
        l_books(l_books.LAST).author := 'Author ' || MOD(i, 1000);
        l_books(l_books.LAST).category := CASE MOD(i,5)
                                            WHEN 0 THEN 'Science'
                                            WHEN 1 THEN 'Literature'
                                            WHEN 2 THEN 'History'
                                            WHEN 3 THEN 'Technology'
                                            ELSE 'Art'
                                         END;
        l_books(l_books.LAST).isbn := DBMS_RANDOM.STRING('X', 13);
        l_books(l_books.LAST).status := CASE WHEN DBMS_RANDOM.VALUE < 0.8 THEN 'AVAILABLE' ELSE 'BORROWED' END;

        IF MOD(i, v_batch_size) = 0 THEN
            FORALL j IN 1..l_books.COUNT
                INSERT INTO books VALUES l_books(j);
            COMMIT;
            l_books.DELETE;
        END IF;
    END LOOP;

    IF l_books.COUNT > 0 THEN
        FORALL j IN 1..l_books.COUNT
            INSERT INTO books VALUES l_books(j);
        COMMIT;
        l_books.DELETE;
    END IF;

    -- 生成借阅记录数据
    FOR i IN 1..100000 LOOP
        l_borrows.EXTEND;
        l_borrows(l_borrows.LAST).borrow_id := i;
        l_borrows(l_borrows.LAST).book_id := TRUNC(DBMS_RANDOM.VALUE(1, 100000));
        l_borrows(l_borrows.LAST).borrower_name := 'User ' || MOD(i, 5000);
        l_borrows(l_borrows.LAST).borrow_date := SYSDATE - TRUNC(DBMS_RANDOM.VALUE(1, 3650));
        l_borrows(l_borrows.LAST).return_date := CASE WHEN DBMS_RANDOM.VALUE < 0.9 THEN SYSDATE - TRUNC(DBMS_RANDOM.VALUE(1, 1800)) ELSE NULL END;

        IF MOD(i, v_batch_size) = 0 THEN
            FORALL j IN 1..l_borrows.COUNT
                INSERT INTO borrow_records VALUES l_borrows(j);
            COMMIT;
            l_borrows.DELETE;
        END IF;
    END LOOP;

    IF l_borrows.COUNT > 0 THEN
        FORALL j IN 1..l_borrows.COUNT
            INSERT INTO borrow_records VALUES l_borrows(j);
        COMMIT;
        l_borrows.DELETE;
    END IF;
END;
/

-- ===========================================
-- 5. 创建查询并打印存储过程
-- ===========================================
CREATE OR REPLACE PROCEDURE search_and_print_books IS
    CURSOR c_books IS
        SELECT b.book_id, b.title, b.author, b.category, b.status, br.borrow_date
        FROM books b
        LEFT JOIN borrow_records br ON b.book_id = br.book_id
        WHERE b.category = 'Science' AND b.status = 'AVAILABLE'
        ORDER BY b.book_id
        FETCH FIRST 20 ROWS ONLY;
BEGIN
    FOR rec IN c_books LOOP
        DBMS_OUTPUT.PUT_LINE(
            'ID: ' || rec.book_id || ', Title: ' || rec.title || ', Author: ' || rec.author ||
            ', Category: ' || rec.category || ', Status: ' || rec.status ||
            ', Last Borrowed: ' || NVL(TO_CHAR(rec.borrow_date, 'YYYY-MM-DD'), 'Never')
        );
    END LOOP;
END;
/

-- ===========================================
-- 6. 执行示例
-- ===========================================

-- 生成数据（执行时请耐心等待）
BEGIN
    generate_library_data;
END;
/

-- 开启DBMS输出并执行查询打印
SET SERVEROUTPUT ON SIZE 1000000;
BEGIN
    search_and_print_books;
END;
/