Oracle数据库SQL与PL/SQL实践教程

原创 于 2025-08-23 16:48:01 发布 · 362 阅读 · 16 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文还有配套的精品资源，点击获取 menu-r.4af5f7ec.gif

简介：Oracle数据库作为流行的大型关系型数据库管理系统，通过本教程你可以学习到如何结合使用SQL和PL/SQL两种语言进行数据库操作和应用开发。教程涵盖了创建和管理数据库对象、执行数据查询和更新操作，以及使用聚合函数和高级查询技巧。同时，深入学习PL/SQL的过程化编程，包括变量声明、控制流程、异常处理和编写存储过程及函数。本教程还提供了实例任务和代码示例，帮助用户巩固理论知识，并应用于实际工作中，包括索引优化、事务管理、并发控制等高级主题。此外，教程还会介绍Oracle的其他特性，如视图、触发器和游标，以及使用SQL Developer和PL/SQL Developer等开发工具。

1. Oracle数据库概述

Oracle数据库简介

Oracle 数据库是由 Oracle 公司开发的大型关系型数据库管理系统（RDBMS）。它是业界最流行的数据存储解决方案之一，广泛应用于金融、电信、制造等众多行业。Oracle 数据库以其高度的可靠性、安全性和可扩展性在全球范围内赢得了众多企业和开发者的青睐。

核心特性

Oracle 数据库的核心特性包括事务管理、数据并发控制、数据完整性保证和数据恢复等。它提供了先进的SQL查询语言支持、强大的数据处理能力以及全面的事务控制。此外，Oracle 还提供了丰富的数据安全特性，如角色管理和细粒度的访问控制。

应用场景

在 IT 行业，Oracle 数据库不仅支持在线事务处理（OLTP），还支持复杂的数据仓库和数据挖掘应用。其多版本并发控制（MVCC）和自动 Undo 管理是处理大数据量和高并发环境下的重要优势。Oracle 数据库经常用于关键任务应用，如银行交易系统、供应链管理、ERP 系统等。

2. SQL基本操作和查询技巧

2.1 SQL基础语法回顾

2.1.1 数据定义语言（DDL）

数据定义语言（DDL）是用于定义或修改数据库结构的SQL语句集，包括创建、修改和删除数据库对象。核心的DDL操作包括 CREATE 、 ALTER 和 DROP 。

创建表： 

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100),
    phone_number VARCHAR2(20),
    hire_date DATE,
    job_id VARCHAR2(10),
    salary NUMBER(8,2),
    commission_pct NUMBER(2,2),
    manager_id NUMBER,
    department_id NUMBER
);

逻辑分析：
- CREATE TABLE 是DDL语句，用于创建新表。
- employees 是表名。
- 括号内定义了表的结构，包括列名和数据类型。
- PRIMARY KEY 定义了主键约束。

修改表结构： 

ALTER TABLE employees
ADD (job_title VARCHAR2(35));

逻辑分析：
- ALTER TABLE 用于修改表。
- ADD 是添加列的操作。
- job_title 列被添加到 employees 表中。

删除表： 

DROP TABLE employees;

逻辑分析：
- DROP TABLE 用于从数据库中删除表。
- 会删除表结构和所有相关数据。

2.1.2 数据操纵语言（DML）

数据操纵语言（DML）涉及对数据库中数据进行操作的SQL语句，允许用户插入、更新、删除和查询数据库中的数据。核心DML操作包括 INSERT 、 UPDATE 、 DELETE 和 SELECT 。

插入数据： 

INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (100, 'Steven', 'King', 'steven.king@example.com', '515.123.4567', SYSDATE, 'AD_PRES', 24000, NULL, NULL, 90);

逻辑分析：
- INSERT INTO 用于向表中添加新行。
- 列名和值都必须匹配表的结构。
- SYSDATE 是Oracle中获取当前日期和时间的函数。

更新数据： 

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 90;

逻辑分析：
- UPDATE 语句用于更新表中的数据。
- SET salary = salary * 1.10 将 salary 列的值增加10%。
- WHERE 子句限定了哪些行将被更新。

删除数据： 

DELETE FROM employees
WHERE employee_id = 100;

逻辑分析：
- DELETE FROM 用于删除表中的行。
- WHERE 子句指定了删除条件，这里删除 employee_id 为100的行。
- 如果没有 WHERE 子句，将会删除表中的所有行。

2.2 SQL高级查询技巧

2.2.1 多表连接查询

多表连接查询允许您通过共同列将两个或多个表中的数据组合在一起。这在数据分散在多个表中的情况下非常有用。SQL提供了几种连接类型，其中最常用的是内连接（INNER JOIN）和外连接（LEFT JOIN、RIGHT JOIN、FULL JOIN）。

内连接： 

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

逻辑分析：
- INNER JOIN 返回两个表中匹配的行。
- ON 子句指定了如何将 employees 表和 departments 表关联起来。
- e 和 d 是表别名，用于简化查询。

左外连接： 

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;

逻辑分析：
- LEFT OUTER JOIN 返回左表的所有行，即使右表中没有匹配的行也会返回，右表中不匹配的行将用NULL填充。
- 此查询显示了所有员工以及与他们相关联的部门名称，如果某个员工没有分配部门，则其部门名称会显示为NULL。

2.2.2 子查询的使用

子查询是一个嵌套在其他SQL语句内的查询。它可以在SELECT、INSERT、UPDATE或DELETE语句中使用。子查询用于复杂的数据提取。

单行子查询： 

SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

逻辑分析：
- 子查询 (SELECT AVG(salary) FROM employees) 计算了 employees 表中所有员工的平均薪资。
- 外层查询列出了那些薪资高于平均薪资的员工信息。
- 此子查询返回单个值，因此它是一个标量子查询。

多行子查询： 

SELECT employee_id, first_name, last_name
FROM employees
WHERE job_id IN (SELECT job_id FROM jobs WHERE job_title LIKE '%MANAGER%');

逻辑分析：
- 子查询 (SELECT job_id FROM jobs WHERE job_title LIKE '%MANAGER%') 识别所有与 ‘MANAGER’ 相关的职位。
- 外层查询列出了所有担任了这些职位的员工的ID、名和姓。
- 此子查询返回多行数据，因此它是一个多行子查询。

2.2.3 分组与排序技巧

分组（GROUP BY）和排序（ORDER BY）是数据分析中经常使用的技巧，它们能帮助我们更好地理解数据，并提取出需要的信息。

分组： 

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

逻辑分析：
- GROUP BY 子句按 department_id 对员工进行分组。
- COUNT(*) 函数计算每个分组中的员工数量。
- 查询结果将显示每个部门及其员工数量。

排序： 

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

逻辑分析：
- ORDER BY 子句将结果按 salary 列降序排列。
- DESC 关键字表示降序，若要按升序排列，可以使用 ASC 。
- 这样我们能够得到所有员工的姓名和薪水列表，从最高薪水开始。

结构化内容的表格形式总结

操作类型 SQL语句 描述
DDL CREATE TABLE 创建一个新表
DDL ALTER TABLE 修改表结构
DDL DROP TABLE 删除表
DML INSERT INTO 插入新数据
DML UPDATE 更新数据
DML DELETE FROM 删除数据
连接查询 INNER JOIN 内连接，返回两个表中匹配的行
连接查询 LEFT OUTER JOIN 左外连接，返回左表的所有行
子查询 标量子查询 返回单个值的子查询
子查询 多行子查询 返回多行数据的子查询
分组与排序 GROUP BY 按指定列对查询结果进行分组
分组与排序 ORDER BY 对结果进行排序，ASC为升序，DESC为降序

结构化内容的流程图展示 

flowchart TD
    DDL[DDL操作]
    DML[DML操作]
    JOIN[多表连接查询]
    SUBQUERY[子查询使用]
    GROUPSORT[分组与排序技巧]
    DDL -->|CREATE TABLE| TABLECREATE
    DDL -->|ALTER TABLE| TABLEALTER
    DDL -->|DROP TABLE| TABLEDROP
    DML -->|INSERT INTO| DATAINSERT
    DML -->|UPDATE| DATAUPDATE
    DML -->|DELETE FROM| DATADELETE
    JOIN -->|INNER JOIN| INNER
    JOIN -->|LEFT OUTER JOIN| LEFTOUTER
    SUBQUERY -->|单行子查询| SINGLE
    SUBQUERY -->|多行子查询| MULTIPLE
    GROUPSORT -->|GROUP BY| GROUP
    GROUPSORT -->|ORDER BY| SORT

通过以上章节，我们详细地探讨了SQL基础语法的回顾、数据操纵语言（DML）的基本操作，并且通过表格和流程图对这些概念进行了结构化内容的总结。本章节内容对初学者而言，为他们提供了一个扎实的理论基础，并向经验丰富的数据库管理员展示了一些复杂查询技巧的实践应用。

3. PL/SQL过程化编程

3.1 PL/SQL编程基础

3.1.1 PL/SQL程序结构

PL/SQL是一种过程化语言，它允许我们使用块结构来编写程序。每个PL/SQL块都有三个主要部分：声明部分、执行部分和异常处理部分。这种结构使PL/SQL代码组织有序，易于理解和维护。

  • 声明部分 ：此部分用于声明变量、常量、游标和子程序。
  • 执行部分 ：此部分包含PL/SQL的可执行语句。
  • 异常处理部分 ：此部分处理执行部分抛出的任何异常。

为了更好地理解，让我们看一个简单的PL/SQL程序块的示例代码： 

DECLARE
  -- 声明变量
  v_counter NUMBER(3) := 0;
-- 可以在此声明更多变量和/或常量
BEGIN
  -- 执行部分开始
  FOR i IN 1..5 LOOP
    v_counter := v_counter + 1;
    DBMS_OUTPUT.PUT_LINE('Counter value: ' || TO_CHAR(v_counter));
  END LOOP;
  -- 执行部分结束
EXCEPTION
  -- 异常处理部分开始
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No Data Found Exception raised');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected exception occurred');
  -- 异常处理部分结束
END;
/

在上面的代码中，我们声明了一个名为 v_counter 的变量并初始化为0。执行部分中的循环将变量 v_counter 增加1，并使用 DBMS_OUTPUT.PUT_LINE 输出其值。异常处理部分通过 WHEN 语句捕获特定的异常。注意，这只是一个基础示例，真实情况下，您需要根据实际业务逻辑编写PL/SQL代码。

3.1.2 数据类型与变量

在PL/SQL中，变量和常量必须在声明部分中声明，并且可以具有各种数据类型，包括数字、字符、日期和布尔值。用户自定义的记录和表也可以作为数据类型。

声明变量的基本语法是： 

variable_name datatype [NOT NULL] := value;

例如，声明一个日期类型的变量，可以这样做： 

DECLARE
  v_date DATE := SYSDATE;
BEGIN
  -- 使用v_date变量
END;
/

如果变量未赋初值，则其默认值为 NULL 。需要注意的是， NOT NULL 约束可以添加到变量声明中，以确保变量不能为 NULL 。在实际的PL/SQL编程实践中，合理利用各种数据类型和变量，可以大大提高代码的可读性和可维护性。

3.2 PL/SQL控制流结构

3.2.1 条件控制语句

在PL/SQL中，我们使用条件控制语句如 IF 、 CASE 、 EXIT WHEN 等来控制程序的执行流程。这些语句使我们能够根据条件执行不同的代码块。

以 IF 语句为例，基础语法是： 

IF condition THEN
  -- 条件为真时执行的代码
ELSIF condition THEN
  -- 条件为真时执行的代码
ELSE
  -- 所有条件都为假时执行的代码
END IF;

CASE 语句提供了另一种基于多个条件执行不同代码块的方式。它类似于其他编程语言中的 switch 语句。

例如，使用 CASE 语句的代码如下： 

DECLARE
  v_day_of_week NUMBER(2);
BEGIN
  v_day_of_week := TO_NUMBER(TO_CHAR(SYSDATE, 'D'));
  CASE v_day_of_week
    WHEN 1 THEN
      DBMS_OUTPUT.PUT_LINE('Today is Monday.');
    WHEN 7 THEN
      DBMS_OUTPUT.PUT_LINE('Today is Sunday.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Today is some other day.');
  END CASE;
END;
/

在这个例子中，我们根据 v_day_of_week 变量的值输出当天是星期几。

3.2.2 循环控制语句

在需要重复执行某个代码块时，循环控制语句是必不可少的。PL/SQL支持多种类型的循环： FOR 循环、 WHILE 循环和 LOOP 循环。

FOR 循环 的基本语法： 

FOR i IN start .. end LOOP
  -- 循环执行的代码
END LOOP;

WHILE 循环 的基本语法： 

WHILE condition LOOP
  -- 循环执行的代码
END LOOP;

LOOP 循环 是最基本的循环结构，它允许循环直到显式退出： 

LOOP
  -- 循环执行的代码
  EXIT WHEN condition; -- 退出条件
END LOOP;

使用循环控制语句时，一定要确保循环条件会在某个时刻被满足，否则会导致无限循环。通常，循环控制语句用于执行重复的数据操作，例如在数据库表中插入大量数据。

在下一节中，我们将深入探讨PL/SQL中聚合函数与分组查询的应用，以及如何通过存储过程和函数实现业务逻辑的复用。

4. 实例任务与代码示例

4.1 实际案例分析

4.1.1 业务需求解析

在考虑数据库解决方案时，了解业务需求至关重要。例如，假设一家在线零售企业希望追踪其产品的销售情况，并需要实时生成销售报告。分析此类业务需求时，我们需要考虑以下关键要素：

  • 数据模型：设计一个可以存储产品、销售记录和客户信息的数据模型。
  • 性能：如何确保即使在高负载的情况下，查询和报告生成也能快速响应。
  • 可扩展性：随着业务的增长，数据库解决方案应能轻松扩展。
  • 安全性：确保数据访问控制严格，避免敏感信息泄露。

4.1.2 解决方案设计

一旦业务需求得到充分理解，我们就可以设计一个解决方案。针对上述在线零售业务案例，解决方案可能包含以下组件：

  • 数据库设计 ：创建产品表、销售记录表和客户表，每张表都应设计适当的字段以存储所需信息。
  • 索引优化 ：为了加快查询速度，为经常用于搜索和关联的字段建立索引。
  • 触发器和存储过程 ：用于自动执行复杂的业务逻辑，如库存更新和销售记录生成。
  • 数据报告 ：开发定制报告工具或使用Oracle内置的报告工具如BI Publisher。

4.2 代码示例与解释

4.2.1 案例代码展示

以下是一个简单的Oracle SQL代码示例，展示了如何创建一个“产品”表，并插入一些初始产品数据。 

-- 创建产品表
CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(255) NOT NULL,
    product_price NUMBER(10, 2) NOT NULL CHECK (product_price > 0),
    product_description VARCHAR2(1000),
    product_stock NUMBER CHECK (product_stock >= 0)
);

-- 插入产品数据
INSERT INTO products (product_id, product_name, product_price, product_description, product_stock) VALUES (1, 'Product A', 19.99, 'Description for Product A', 100);
INSERT INTO products (product_id, product_name, product_price, product_description, product_stock) VALUES (2, 'Product B', 29.99, 'Description for Product B', 50);

4.2.2 代码逻辑与结果分析

在上述代码中，我们首先使用 CREATE TABLE 语句定义了一个名为 products 的新表，该表包含五列： product_id （产品ID）、 product_name （产品名称）、 product_price （产品价格）、 product_description （产品描述）和 product_stock （产品库存）。我们还为这些字段指定了数据类型，并为 product_id 列添加了主键约束，为 product_price 和 product_stock 列添加了检查约束以确保价格和库存是正数。

接下来，我们使用 INSERT INTO 语句向 products 表中插入了两条产品记录。每条记录代表了一个不同的产品，包括其ID、名称、价格、描述和库存。

为确保我们的表创建和数据插入操作成功执行，我们可以查询 products 表来查看插入的数据： 

SELECT * FROM products;

通过查询我们能够验证数据是否正确存储，这为后续的业务逻辑提供了基础数据。

以上示例展示了数据库表的创建和数据插入操作的代码逻辑，以及如何通过查询验证数据的正确性。在实际项目中，我们还需考虑错误处理、事务控制以及更复杂的查询操作，但本章节的示例旨在帮助读者理解基础的数据库操作。在实际应用中，需要将这些基础知识与业务需求结合起来，设计和实现更加复杂和健壮的数据库解决方案。

5. 数据库对象创建与管理

5.1 数据库表的创建与修改

5.1.1 表的创建

数据库表是存储数据的基础单位，因此掌握如何创建和管理表是每位数据库管理员必须具备的技能。在Oracle数据库中，创建表通常是通过SQL语言实现的。以下是创建表的基本语法： 

CREATE TABLE table_name (
    column1 datatype1,
    column2 datatype2,
    ...
    table_constraints
);
  • table_name 是新创建的表的名称。
  • column1 , column2 等代表表中的列。
  • datatype1 , datatype2 等指定了每列的数据类型，例如 VARCHAR2, NUMBER, DATE 等。
  • table_constraints 是可选的表级约束，如主键、外键等。

例如，创建一个员工信息表 employees 如下： 

CREATE TABLE employees (
    employee_id NUMBER(6) NOT NULL,
    first_name VARCHAR2(20),
    last_name VARCHAR2(25) NOT NULL,
    email VARCHAR2(25) NOT NULL,
    phone_number VARCHAR2(20),
    hire_date DATE NOT NULL,
    job_id VARCHAR2(10) NOT NULL,
    salary NUMBER(8,2),
    commission_pct NUMBER(2,2),
    manager_id NUMBER(6),
    department_id NUMBER(4),
    CONSTRAINT employees_pk PRIMARY KEY (employee_id),
    CONSTRAINT salary_check CHECK (salary > 0)
);

在上述示例中，创建了一个名为 employees 的表，其中包含多个字段，并且有主键和检查约束。

5.1.2 表的修改与约束

随着时间的推移，业务需求可能会发生变化，这将导致数据库结构需要相应的调整。Oracle 提供了多种SQL语句来修改表结构，包括添加、修改或删除列，修改约束等。

添加列的语法如下： 

ALTER TABLE table_name
ADD (column_name column_datatype);

删除列的语法如下： 

ALTER TABLE table_name
DROP COLUMN column_name;

修改列数据类型的语法： 

ALTER TABLE table_name
MODIFY (column_name new_datatype);

添加或修改约束，例如添加主键约束： 

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

修改约束的细节需要根据具体的约束类型来进行相应的操作。在执行这些操作时，需要注意可能影响到的数据完整性问题以及对应的索引和存储开销。

5.2 索引和视图的管理

5.2.1 索引的作用与创建

索引是提高数据库查询性能的关键数据库对象之一。它是一种数据结构，可以帮助快速定位到表中某行的数据，类似于书籍的目录。在创建索引之前，应当考虑数据的访问模式以及索引对DML操作性能的影响。

创建索引的基本语法是： 

CREATE [UNIQUE] INDEX index_name ON table_name (column_name);
  • UNIQUE 关键字表示创建唯一索引。
  • index_name 是索引的名称。
  • table_name 是需要创建索引的表名。
  • column_name 是索引对应的列名。

例如，为 employees 表的 last_name 列创建一个索引： 

CREATE INDEX idx_employee_last_name ON employees (last_name);

5.2.2 视图的创建和使用

视图可以被看作是虚拟表，它是基于一个或多个表的查询结果集。视图可以用来封装复杂的SQL语句，简化操作，提高安全性。创建视图的语法如下： 

CREATE [OR REPLACE] VIEW view_name AS
SELECT column_name1, column_name2, ...
FROM table_name
[WITH CHECK OPTION];
  • OR REPLACE 关键字表示如果视图已存在，则替换它。
  • view_name 是视图的名称。
  • SELECT 子句定义了视图将返回哪些列。
  • WITH CHECK OPTION 确保所有DML操作都会遵守视图的定义。

下面是一个视图创建的例子： 

CREATE VIEW employees_with_salary AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 5000;

该视图 employees_with_salary 将只显示薪资超过5000的员工信息。视图是只读的，但当视图基于一个或多个可更新的表时，可以通过视图执行DML操作。

接下来，第六章将介绍数据的增删改查操作，深入探讨如何在Oracle数据库中对数据进行有效的管理。

6. 数据操作：查询、插入、更新、删除

6.1 数据的增删改查操作

6.1.1 插入数据

在数据库中，插入数据是最基础的操作之一。使用 INSERT 语句，可以将新的数据记录添加到表中。基本语法如下： 

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

对于简单的插入操作，只需要指定要插入数据的表名以及各列对应的值。如果表中的所有列都已经有默认值或允许空值，那么可以省略列名。

示例代码 
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
VALUES (208, 'John', 'Doe', 'john.doe@example.com', SYSDATE, 'IT_PROG');

6.1.2 更新数据

更新操作通过 UPDATE 语句实现，它允许我们修改表中的现有数据。基本语法如下： 

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

在这里， SET 子句用于指定要更新的列和新值，而 WHERE 子句则用于限定哪些行需要被更新。如果没有 WHERE 子句，所有的行都会被更新，这通常是不希望发生的。

示例代码 
UPDATE employees
SET salary = 8000
WHERE employee_id = 208;

6.1.3 删除数据

数据的删除操作通过 DELETE 语句执行。基本语法如下： 

DELETE FROM table_name WHERE condition;

WHERE 子句在此同样重要，因为它定义了哪些行将被删除。如果省略 WHERE 子句，将会删除表中的所有行，这是一个危险的操作，应该避免。

示例代码 
DELETE FROM employees WHERE employee_id = 208;

6.2 高级数据操作技巧

6.2.1 使用事务控制数据操作

事务是一组逻辑上相关的SQL操作，要么全部成功，要么全部失败。在Oracle中，使用 COMMIT 和 ROLLBACK 语句来控制事务。

  • COMMIT ：提交事务，使事务中的所有操作成为数据库的永久部分。
  • ROLLBACK ：回滚事务，撤销事务中的所有操作。
事务操作的逻辑
  1. 执行数据修改操作（INSERT, UPDATE, DELETE）。
  2. 在必要时可以手动调用 SAVEPOINT 来创建事务中的一个保存点。
  3. 如果需要，使用 ROLLBACK 到特定的保存点或整个事务。
  4. 在确认所有操作无误后，使用 COMMIT 来提交事务。
示例代码 
-- 开始事务
UPDATE employees SET salary = salary + 100 WHERE employee_id = 100;
SAVEPOINT my_savepoint; -- 创建保存点
UPDATE employees SET salary = salary + 100 WHERE employee_id = 101;

-- 某些条件下需要回滚
ROLLBACK TO my_savepoint; -- 回滚到保存点

-- 提交事务
COMMIT;

6.2.2 使用并发控制优化数据访问

在多用户环境中，数据并发访问是数据库管理的一个关键方面。为了确保数据的一致性，Oracle提供了几种并发控制机制，其中最核心的是锁定（Locking）机制。

  • 锁定可以防止多个用户同时修改同一数据。
  • Oracle数据库管理系统会自动处理大部分的锁定工作，但在某些复杂情况下，可能需要手动介入。
锁定模式和类型
  • Oracle使用不同类型的锁来支持不同级别的并发控制。
  • 常见的锁类型包括共享锁（Shared Locks）和排他锁（Exclusive Locks）。
  • 表锁和行锁是锁的范围，表锁锁定整个表，而行锁只锁定表中的某些行。
并发控制最佳实践
  • 在必要时使用更细粒度的锁来减少锁的冲突。
  • 避免长时间的事务，这样可以减少锁定资源的时间。
  • 使用乐观并发控制，比如通过版本号来检测数据冲突。
示例代码 
-- 查询当前会话锁定的资源
SELECT t.* FROM v$locked_object l
JOIN dba_objects t ON t.object_id = l.object_id;

在实际使用时，应该尽量避免复杂的锁定需求，通过合理的数据库设计和应用程序逻辑来减少并发访问冲突的可能性。此外，数据库管理员（DBA）常常需要监控锁定活动，以确保系统资源的合理利用。

7. PL/SQL高级主题

7.1 聚合函数与分组查询

聚合函数在数据库管理中扮演着重要的角色，它们用于对一组值执行计算并返回单个值。在Oracle中常用的聚合函数包括 SUM , COUNT , AVG , MIN , MAX 。

7.1.1 聚合函数的应用

使用聚合函数可以轻松计算出销售总额、平均工资等数据。例如，如果要计算某个销售表中所有记录的销售额总和，可以使用 SUM 函数。 

SELECT SUM(sales_amount) AS total_sales
FROM sales;

聚合函数不仅限于单一列。它们可以与其他函数一起使用，例如： 

SELECT AVG(unit_price * quantity) AS avg_price
FROM order_details;

7.1.2 分组查询的高级应用

分组查询通常与 GROUP BY 子句结合使用，以对数据集的子集执行聚合函数。 HAVING 子句常用于限制这些分组的结果。

例如，查询每个部门的平均工资，只显示平均工资高于某个特定值的部门： 

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 10000;

7.2 PL/SQL控制流程与异常处理

7.2.1 流程控制的高级用法

PL/SQL提供了多种流程控制结构，如 IF-THEN-ELSE , CASE , LOOP , WHILE 和 FOR 循环。这些结构使得PL/SQL程序可以根据条件执行不同的代码块。

以下是一个使用 LOOP 的例子，用于重复执行操作直到满足某个条件： 

DECLARE
  counter NUMBER := 0;
BEGIN
  LOOP
    counter := counter + 1;
    DBMS_OUTPUT.PUT_LINE('This loop has executed ' || TO_CHAR(counter) || ' times.');
    IF counter > 10 THEN
      EXIT; -- 退出循环
    END IF;
  END LOOP;
END;
/

7.2.2 异常处理机制

异常处理是任何编程语言中不可或缺的一部分，PL/SQL同样提供了完善的异常处理机制。开发者可以使用 EXCEPTION 关键字来处理程序运行时可能出现的错误。 

DECLARE
  v_counter NUMBER;
BEGIN
  v_counter := 1/0; -- 故意制造一个除零异常
EXCEPTION
  WHEN division_by_zero THEN
    DBMS_OUTPUT.PUT_LINE('Error: Division by zero!');
END;
/

7.3 存储过程和函数的实现

存储过程和函数是数据库开发中用来封装逻辑的两种不同方式。它们有助于重用代码，并提供了一种组织复杂SQL和PL/SQL代码的方法。

7.3.1 存储过程的编写与调用

存储过程可以接受参数，并可能返回结果，但与函数不同的是，存储过程不返回值，仅执行代码。 

CREATE OR REPLACE PROCEDURE update_salary(
    p_min_salary IN employees.salary%TYPE,
    p_max_salary IN employees.salary%TYPE,
    p_increment IN NUMBER
) AS
BEGIN
  UPDATE employees
  SET salary = salary + p_increment
  WHERE salary BETWEEN p_min_salary AND p_max_salary;
  COMMIT;
END update_salary;
/

调用存储过程的例子： 

EXECUTE update_salary(1000, 5000, 500);

7.3.2 函数的编写与应用

函数与存储过程类似，但必须返回一个值。这使得它们适合执行计算或转换，并返回单个结果。 

CREATE OR REPLACE FUNCTION calculate_bonus(
    p_salary IN employees.salary%TYPE,
    p_rating IN VARCHAR2
) RETURN NUMBER AS
BEGIN
  IF p_rating = 'A' THEN
    RETURN p_salary * 0.10; -- 如果评级为A，则返回工资的10%
  ELSE
    RETURN p_salary * 0.05; -- 否则返回工资的5%
  END IF;
END calculate_bonus;
/

使用函数的例子： 

SELECT employee_id, salary, calculate_bonus(salary, rating) AS bonus
FROM employees;

7.4 Oracle特性深入介绍

7.4.1 触发器的创建与应用

触发器是数据库中自动执行的程序，它们在满足特定条件时，如数据变更时触发。Oracle触发器可以定义为 BEFORE 、 AFTER 或 INSTEAD OF 类型，并可以应用于表或视图。

创建一个在表数据更新前检查条件的触发器示例： 

CREATE OR REPLACE TRIGGER check_salary
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  IF :NEW.salary < :OLD.salary THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary can not be decreased');
  END IF;
END;
/

7.4.2 游标的概念与使用

游标用于处理SQL语句返回的多行数据集。它们分为显式游标和隐式游标，以及静态游标和动态游标。在PL/SQL中，游标提供了一种方法来逐行处理查询结果。

使用显式游标的一个基本示例： 

DECLARE
  CURSOR emp_cursor IS
    SELECT employee_id, salary FROM employees;
  vEmp emp_cursor%ROWTYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO vEmp;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || vEmp.employee_id || ', Salary: ' || vEmp.salary);
  END LOOP;
  CLOSE emp_cursor;
END;
/

7.5 Oracle开发工具实操

7.5.1 SQL Developer的使用技巧

Oracle SQL Developer是Oracle提供的集成开发环境（IDE），它集成了代码编辑、数据库管理、SQL命令和报表工具等多功能。使用SQL Developer可以更方便地进行SQL编写、调试和执行。

SQL Developer中的一个有用特性是能够连接到不同的数据库实例，并对它们执行数据库操作，如运行查询、查看表数据、编辑表结构等。

7.5.2 PL/SQL Developer的高级功能

PL/SQL Developer是一款专注于PL/SQL代码开发的工具，它提供了代码编辑、调试和性能分析等功能。它特别适合编写、测试和维护PL/SQL存储过程和函数。

PL/SQL Developer中有一个“输出”窗口，用于显示来自 DBMS_OUTPUT.PUT_LINE 的消息。此外，它还支持数据库对象的比较、版本控制集成，以及对特定数据库操作提供图形化界面。

请注意，这些高级功能和工具的使用可以大大提升开发效率，但仍然需要对Oracle数据库及其PL/SQL语言有深入的理解。

本文还有配套的精品资源，点击获取 menu-r.4af5f7ec.gif

简介：Oracle数据库作为流行的大型关系型数据库管理系统，通过本教程你可以学习到如何结合使用SQL和PL/SQL两种语言进行数据库操作和应用开发。教程涵盖了创建和管理数据库对象、执行数据查询和更新操作，以及使用聚合函数和高级查询技巧。同时，深入学习PL/SQL的过程化编程，包括变量声明、控制流程、异常处理和编写存储过程及函数。本教程还提供了实例任务和代码示例，帮助用户巩固理论知识，并应用于实际工作中，包括索引优化、事务管理、并发控制等高级主题。此外，教程还会介绍Oracle的其他特性，如视图、触发器和游标，以及使用SQL Developer和PL/SQL Developer等开发工具。


本文还有配套的精品资源，点击获取
menu-r.4af5f7ec.gif

馥郁恒久
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值