2. 首页 > Oracle教程 > 正文

Oracle教程FG066-PL/SQL基础

教程整理：风哥教程  |  更新时间：2026-01-05  |  教程分类：Oracle教程  |  文档学习：24

1.2.1 匿名块

没有名称的PL/SQL块，直接在SQL*Plus或其他工具中执行。

1.2.2 命名块

有名称的PL/SQL块，包括存储过程、函数、触发器、包等。

1.3 PL/SQL数据类型

PL/SQL支持以下数据类型：

1.3.1 标量数据类型

  • 数字类型：NUMBER、IN
    TEGER、BIN
    ARY_IN
    TEGER、PLS_IN
    TEGER
  • 字符类型：VARCHAR2、CHAR、NCHAR、NVARCHAR2
  • 日期类型：DATE、TIMESTAMP、IN
    TERVAL
  • 布尔类型：BOOLEAN
  • RAW类型：RAW、LONG RAW

1.3.2 复合数据类型

  • 记录类型：RECORD
  • 集合类型：TABLE
    、VARRAY、NESTED TABLE

1.3.3 引用数据类型

  • 游标变量：REF CURSOR
  • 对象类型：OBJECT

1.4 PL/SQL变量与常量

1.4.1 变量声明

SET SERVEROUTPUT ON;
DECLARE
variable_name datatype [NOT NULL] [:=
initial_value];

1.4.2 常量声明

SET SERVEROUTPUT ON;
DECLARE
constant_name CONSTANT datatype :=
value;

1.4.3 %TYPE
属性

使用%TYPE
属性可以使变量的数据类型与表列的数据类型保持一致。

SET SERVEROUTPUT ON;
DECLARE
v_employee_id employees.employee_id%TYPE
;

1.4.4 %ROWTYPE
属性

使用%ROWTYPE
属性可以创建一个记录类型，其结构与表的行结构一致。

SET SERVEROUTPUT ON;
DECLARE
v_employee employees%ROWTYPE
;

1.5 PL/SQL控制结构

1.5.1 条件语句

— IF
语句
IF
condition THEN
statements;
ELSIF
condition THEN
statements;
ELSE
statements;
END IF
;

— CAS
E语句
CAS
E expression
WHEN value1 THEN
statements;
WHEN value2 THEN
statements;
ELSE
statements;
END
CAS
E;

1.5.2 循环语句

— LOOP
语句
LOOP
statements;
EXIT WHEN condition;
END LOOP
;

— WHILE
语句
WHILE
condition LOOP
statements;
END LOOP
;

— FOR
语句
FOR
counter IN
[REVERSE] start..end LOOP
statements;
END LOOP
;

1.5.3 控制语句

  • EXIT：退出循环
  • CONTIN
    UE    ：跳过当前循环的剩余部分，开始下一次循环
  • GOTO：跳转到指定的标签

Part02-生产环境规划与建议

2.1 PL/SQL编程最佳实践

  • 使用命名规范：变量名、常量名、过程名、函数名等应遵循一致的命名规范
  • 使用注释：为复杂的代码添加注释，提高代码可读性
  • 使用异常处理：捕获并处理可能的异常
  • 使用绑定变量：提高性能，避免SQL注入
  • 使用游标：对于结果集处理，使用游标
  • 使用包：将相关的过程、函数、变量等组织到包中
  • 使用批量操作：对于大量数据处理，使用批量操作提高性能

2.2 PL/SQL性能考虑

  • 避免硬解析：使用绑定变量，避免在循环中拼接SQL语句
  • 减少网络往返：使用批量操作，减少SQL语句的执行次数
  • 优化游标：使用 FOR
    循环游标，避免显式打开和关闭游标
  • 使用并行执行：对于大型处理，考虑使用并行执行
  • 监控执行计划：使用EXPLAIN
    PLAN分析SQL语句的执行计划

2.3 PL/SQL异常处理

PL/SQL异常处理是PL/SQL编程的重要部分，它可以捕获和处理运行时错误。PL/SQL异常分为：

2.3.1 预定义异常

  • NO_DATA_FOUND：查询没有返回数据
  • TOO_MANY_ROWS：查询返回多行数据
  • VALUE_ERROR：数据类型转换错误
  • ZERO_DIVIDE：除零错误
  • DUP_VAL_ON_INDEX：违反唯一约束

2.3.2 用户定义异常

SET SERVEROUTPUT ON;
DECLARE
e_custom_exception EXCEPTION
;
PRAGMA EXCEPTION_INIT(e_custom_exception, -20001);
BEGIN
IF
condition THEN
RAISE e_custom_exception;
END IF
;
EXCEPTION
WHEN e_custom_exception THEN
DBMS_OUTPUT.PUT_LINE(‘Custom exception raised’);
END
;

Part03-生产环境项目实施方案

3.1 PL/SQL在存储过程中的应用

存储过程是命名的PL/SQL块，存储在数据库中，可以被多次调用。存储过程的优点包括： from oracle:www.itpux.com

  • 提高代码重用性
  • 提高性能
  • 增强安全性
  • 简化应用开发

3.2 PL/SQL在函数中的应用

函数是返回值的PL/SQL块，存储在数据库中，可以被多次调用。函数的优点包括：

  • 返回值
  • 可以在SQL语句中使用
  • 提高代码重用性
  • 简化应用开发

3.3 PL/SQL在触发器中的应用

触发器是在特定事件发生时自动执行的PL/SQL块。触发器的应用场景包括： 学习交流加群风哥微信: itpux-com

  • 数据验证
  • 数据审计
  • 数据同步
  • 业务规则实现

Part04-生产案例与实战讲解

4.1 PL/SQL基本语法实战

示例1：简单的PL/SQL匿名块

— 简单的PL/SQL匿名块
SET SERVEROUTPUT ON;
DECLARE
v_message VARCHAR2(50) :=
‘Hello, PL/SQL!’;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_message);
END
;
/
Hello, PL/SQL!

示例2：使用%TYPE
和%ROWTYPE

— 使用%TYPE
和%ROWTYPE
SET SERVEROUTPUT ON;
DECLARE
v_employee_id employees.employee_id%TYPE
;
v_employee employees%ROWTYPE
;
BEGIN
— 获取员工ID为100的员工信息
SELECT
* IN
TO v_employee
FROM employees
WHERE employee_id = 100;

— 输出员工信息
DBMS_OUTPUT.PUT_LINE(‘Employee ID: ‘ || v_employee.employee_id);
DBMS_OUTPUT.PUT_LINE(‘Employee Name: ‘ || v_employee.first_name || ‘ ‘ || v_employee.last_name);
DBMS_OUTPUT.PUT_LINE(‘Job ID: ‘ || v_employee.job_id);
DBMS_OUTPUT.PUT_LINE(‘Salary: ‘ || v_employee.salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘No employee found’);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘Too many employees found’);
END
;
/

Employee ID: 100
Employee Name: Steven King
Job ID: AD_PRES
Salary: 24000

4.2 PL/SQL控制结构实战

示例1： IF
– ELSE
语句

— IF
– ELSE
语句
SET SERVEROUTPUT ON;
DECLARE
v_salary NUMBER :=
15000;
BEGIN
IF
v_salary > 20000 THEN
DBMS_OUTPUT.PUT_LINE(‘High salary’);
ELSIF
v_salary > 10000 THEN
DBMS_OUTPUT.PUT_LINE(‘Medium salary’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Low salary’);
END IF
;
END
;
/
Medium salary

示例2： FOR
循环

— FOR
循环
SET SERVEROUTPUT ON;
BEGIN
FOR
i IN
1..5 LOOP
DBMS_OUTPUT.PUT_LINE(‘Iteration: ‘ || i);
END LOOP
;
END
;
/
Iteration: 1
Iteration: 2
Iteration: 3
Iteration: 4
Iteration: 5

示例3：CAS
E语句

— CAS
E语句
SET SERVEROUTPUT ON;
DECLARE
v_job_id VARCHAR2(10) :=
‘AD_PRES’;
BEGIN
CAS
E v_job_id
WHEN ‘AD_PRES’ THEN
DBMS_OUTPUT.PUT_LINE(‘President’);
WHEN ‘AD_VP’ THEN
DBMS_OUTPUT.PUT_LINE(‘Vice President’);
WHEN ‘IT_PROG’ THEN
DBMS_OUTPUT.PUT_LINE(‘IT Programmer’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Other job’);
END
CAS
E;
END
;
/
President

4.3 PL/SQL异常处理实战

示例1：预定义异常处理

— 预定义异常处理
SET SERVEROUTPUT ON;
DECLARE
v_employee_id employees.employee_id%TYPE
:=
9999;
v_employee employees%ROWTYPE
;
BEGIN
SELECT
* IN
TO v_employee
FROM employees
WHERE employee_id = v_employee_id;
DBMS_OUTPUT.PUT_LINE(‘Employee found: ‘ || v_employee.first_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Error: No employee found with ID ‘ || v_employee_id);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error: ‘ || SQLCODE || ‘ – ‘ || SQLERRM);
END
;
/
Error: No employee found with ID 9999

示例2：用户定义异常处理

— 用户定义异常处理
SET SERVEROUTPUT ON;
DECLARE
e_invalid_salary EXCEPTION
;
PRAGMA EXCEPTION_INIT(e_invalid_salary, -20001);
v_salary NUMBER :=
5000;
BEGIN
IF
v_salary < 10000 THEN RAISE e_invalid_salary;
END IF
;
DBMS_OUTPUT.PUT_LINE(‘Salary is valid: ‘ || v_salary);
EXCEPTION
WHEN e_invalid_salary THEN
DBMS_OUTPUT.PUT_LINE(‘ Error: Salary must be at least 10000’);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error: ‘ || SQLCODE || ‘ – ‘ || SQLERRM);
END
;
/
Error: Salary must be at least 10000

4.4 PL/SQL游标实战

示例1：显式游标

— 显式游标
SET SERVEROUTPUT ON;
DECLARE
CURSOR c_employees IS
SELECT
employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 90;
v_employee_id employees.employee_id%TYPE
;
v_first_name employees.first_name%TYPE
;
v_last_name employees.last_name%TYPE
;
v_salary employees.salary%TYPE
;
BEGIN
OPEN c_employees;
LOOP
FETCH c_employees IN
TO v_employee_id, v_first_name, v_last_name, v_salary;
EXIT WHEN c_employees%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘ ID: ‘ || v_employee_id || ‘ ,
Name: ‘ || v_first_name || ‘ ‘ || v_last_name || ‘ , Salary: ‘ || v_salary);
END LOOP
;
CLOSE c_employees;
END
;
/
ID: 100, Name: Steven King, Salary: 24000
ID: 101, Name: Neena Kochhar, Salary: 17000
ID: 102, Name: Lex De Haan, Salary: 17000

示例2： FOR
循环游标

— FOR
循环游标
SET SERVEROUTPUT ON;
BEGIN
FOR
c_emp IN
(
SELECT
employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 90
) LOOP
DBMS_OUTPUT.PUT_LINE(‘ ID: ‘ || c_emp.employee_id || ‘ ,
Name: ‘ || c_emp.first_name || ‘ ‘ || c_emp.last_name || ‘ , Salary: ‘ || c_emp.salary);
END LOOP
;
END
;
/
ID: 100, Name: Steven King, Salary: 24000
ID: 101, Name: Neena Kochhar, Salary: 17000
ID: 102, Name: Lex De Haan, Salary: 17000

Part05-风哥经验总结与分享

5.1 PL/SQL编程技巧

  • 使用命名规范：变量名使用v_前缀，常量名使用c_前缀，游标名使用c_前缀
  • 使用注释：为复杂的代码添加注释，提高代码可读性
  • 使用异常处理：捕获并处理可能的异常，提高代码的健壮性
  • 使用绑定变量：提高性能，避免SQL注入
  • 使用批量操作：对于大量数据处理，使用BULK COLLECT和 FOR
    ALL提高性能
  • 使用包：将相关的过程、函数、变量等组织到包中，提高代码的可维护性
  • 使用游标变量：对于动态查询，使用游标变量

5.2 常见错误与解决方案

  • NO_DATA_FOUND异常：查询没有返回数据，使用 EXCEPTION
    块捕获
  • TOO_MANY_ROWS异常：查询返回多行数据，确保查询只返回一行数据
  • VALUE_ERROR异常：数据类型转换错误，确保数据类型匹配
  • ZERO_DIVIDE异常：除零错误，在除法操作前检查除数是否为零
  • DUP_VAL_ON_INDEX异常：违反唯一约束，确保插入的数据不违反唯一约束
  • 游标未关闭：确保显式游标在使用后关闭
  • 变量未初始化：在使用变量前初始化变量

5.3 性能调优建议

  • 使用绑定变量：避免硬解析，提高性能
  • 使用批量操作：对于大量数据处理，使用BULK COLLECT和 FOR
    ALL提高性能
  • 使用并行执行：对于大型处理，考虑使用并行执行
  • 优化SQL语句：使用EXPLAIN
    PLAN分析SQL语句的执行计划，优化SQL语句
  • 使用索引：为经常查询的列创建索引
  • 减少网络往返：使用批量操作，减少SQL语句的执行次数
  • 监控性能：使用Oracle的性能监控工具监控PL/SQL代码的性能
风哥提示：

本文档风哥主要介绍Oracle PL/SQL的基础知识，包括PL/SQL的基本结构、变量、常量、数据类型、控制结构等，由风哥教程参考Oracle官方文档Development内容编写，适合DBA人员和开发人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

学习交流加群风哥QQ113257174

生产环境建议：请根据实际情况调整配置和参数，确保生产环境的安全性和稳定性。

风哥提示：更多学习教程公众号风哥教程itpux_com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息