17.Oracle11g的PL/SQL基础

Juvenile少年

已于 2023-12-06 09:53:32 修改

阅读量277

点赞数

分类专栏： oracle学习文章标签： sql 数据库 dba 存储过程 oracle

于 2023-11-21 14:03:20 首次发布

本文链接：https://blog.csdn.net/qq_46645079/article/details/134528053

版权

oracle学习专栏收录该内容

44 篇文章 11 订阅

订阅专栏

oracle基础系统学习目录

01.CentOS7静默安装oracle11g
02.Oracle的启动过程
 03.从简单的sql开始
 04.Oracle的体系架构
 05.Oracle数据库对象
 06.Oracle数据备份与恢复
 07.用户和权限管理
 08.Oracle的表
 09.Oracle表的分区
 10.Oracle的同义词与序列
 11.Oracle的视图
 12.Oracle的索引
 13.Oracle通过JDBC连接Java
14.Oracle中的事务
 15.Oracle11g的归档方式和日志文件的相关操作
 16.Oracle的数据字典和动态性能视图
 17.Oracle11g的PL/SQL基础
 18.Oracle的过程和函数
 19.Oracle11g中的游标
 20.Oracle11g中的触发器
 21.Oracle的程序包(Package)
22.Oracle中的临时表空间
 23.Oracle11g的UNDO表空间
 24.Oracle11g的逻辑备份与恢复
 25. Oracle的回收站
 26.Oracle11g的数据装载
 27.Oracle11g的闪回Flashback
28.Oracle11g物化视图

一、PL/SQL的体系

在这里插入图片描述

1、什么是PL/SQL

PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言。
PL/SQL 是对 SQL 的扩展支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构。
可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑。
与 Oracle 服务器和 Oracle 工具紧密集成，具备可移植性、灵活性和安全性。
PL/SQL 引擎驻留在 Oracle 服务器中,该引擎接受 PL/SQL 块并对其进行编译执行。

2、PL/SQL 的优缺点

2.1 PL/SQL的优点

支持 SQL，在 PL/SQL 中可以使用：
- 数据操纵命令
- 事务控制命令
- 游标控制
- SQL 函数和 SQL 运算符
用户把PL/SQL块整个发送到服务器端，oracle服务器端编译、运行，再把结果返回给用户
可移植性，可运行在任何操作系统和平台上的Oralce 数据库
更佳的性能，PL/SQL 经过编译执行
安全性，可以通过存储过程限制用户对数据的访问
与 SQL 紧密集成，简化数据处理。
- 支持所有 SQL 数据类型
- 支持 NULL 值
- 支持 %TYPE 和 %ROWTYPE 属性类型

2.2 PL/SQL的缺点

可读性：PL/SQL的语法比较复杂，特别是当处理复杂的逻辑和大量的代码时，可能会导致代码的可读性降低。这可能会增加代码维护和调试的难度。
性能：与使用其他编程语言进行开发相比，PL/SQL可能在性能方面存在一些局限性。某些情况下，执行复杂的计算或处理大量数据时，PL/SQL可能不如其他编程语言效率高。
约束：PL/SQL在一定程度上与Oracle数据库紧密耦合，这可能会导致代码的可移植性受到限制。如果想将代码迁移到其他数据库系统中，可能需要做一些改动和调整。
缺乏某些编程特性：相较于其他编程语言，PL/SQL在某些编程特性方面可能受到限制。例如，它可能不如其他语言提供丰富的库和框架，或者可能不支持某些现代化的编程概念和技术。

二、PL/SQL的语法

1、PL/SQL代码结构（块）

PL/SQL 块是构成 PL/SQL 程序的基本单元
将逻辑上相关的声明和语句组合在一起
PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分

声明部分：

DECLARE
    -- 声明变量、常量、游标等
BEGIN
    -- 可执行部分的代码
EXCEPTION
    -- 异常处理部分的代码
END;

可执行部分：

BEGIN
    -- 可执行部分的代码
END;

异常处理部分：

EXCEPTION
    -- 异常处理部分的代码

具体案例
当创建一个存储过程时，通常会包含声明部分、可执行部分和异常处理部分。以下是一个包含这三部分的PL/SQL存储过程的示例：

CREATE OR REPLACE PROCEDURE process_employee_data (employee_id IN NUMBER) 
IS
    v_employee_name employees.first_name%TYPE;
BEGIN
    -- 可执行部分
    SELECT first_name INTO v_employee_name
    FROM employees
    WHERE employee_id = process_employee_data.employee_id;

    -- 输出员工姓名
    DBMS_OUTPUT.PUT_LINE('Employee name is ' || v_employee_name);
    
EXCEPTION
    -- 异常处理部分
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

在这个例子中，DECLARE部分被省略了，因为在存储过程中声明部分是可选的。BEGIN和END之间的部分是可执行部分，包括了对employees表的查询和输出员工姓名的代码。EXCEPTION部分用于处理可能发生的异常情况，比如在查询中找不到员工信息或者其他错误。

在PL/SQL块中，声明部分是可选的，而可执行部分是必需的。异常处理部分也是可选的，但通常在编写PL/SQL块时都会包含异常处理部分，以处理可能发生的异常情况。

2、PL/SQL基本语法

当谈到Oracle 11g的PL/SQL基础时，有几个关键点需要注意。PL/SQL是Oracle数据库的编程语言，它结合了SQL语句和程序设计语言的特性，允许开发人员在数据库中创建存储过程、函数和触发器。

2.1 变量声明

在PL/SQL中，变量声明是用于指定变量名称、数据类型和初始值的语句，可以声明各种类型的变量，如整数、浮点数、字符等。变量声明可以出现在PL/SQL块的任何位置，但通常在声明部分进行声明。以下是PL/SQL变量声明的基本语法：

DECLARE
    variable_name [CONSTANT] data_type [NOT NULL] [:= initial_value];
    ...
BEGIN
    ...
END;

其中，variable_name是变量的名称，data_type是变量的数据类型，initial_value是变量的初始值，CONSTANT用于声明常量，NOT NULL用于声明变量不允许为空。

以下是一些常用的数据类型和示例：

NUMBER: 数字类型，可以指定精度和范围。例如：salary NUMBER(8,2);
VARCHAR2: 可变长度字符串类型，最大长度为4000个字节。例如：employee_name VARCHAR2(50);
DATE: 日期类型，包括年、月、日、时、分、秒。例如：hire_date DATE := SYSDATE;
BOOLEAN: 布尔类型，只有两个可能的值：TRUE和FALSE。例如：is_valid BOOLEAN := TRUE;
CURSOR: 游标类型，用于遍历结果集。例如：CURSOR c1 IS SELECT * FROM employees;

在PL/SQL中，变量的命名规则与其他编程语言类似，应该遵循一些基本规则，如：

变量名应该以字母开头，不能以数字或特殊字符开头。
变量名应该具有描述性，能够清楚地表达变量的用途。
变量名不能与PL/SQL关键字重名。

2.2 流程控制语法

在PL/SQL中，条件控制语句用于根据指定条件执行不同的代码块。PL/SQL中的条件控制语句包括IF语句、CASE语句和循环语句。下面分别介绍这些条件控制语句的基本语法和用法。

2.2.1 条件控制

PL/SQL提供了条件控制结构，如IF-THEN、IF-THEN-ELSE和CASE语句，可以根据条件执行不同的代码块。

IF语句
IF语句用于根据条件执行不同的代码块。基本的IF语句语法如下：

IF condition THEN
    -- 如果条件为真，则执行这里的代码块
ELSIF condition2 THEN
    -- 如果条件2为真，则执行这里的代码块
ELSE
    -- 如果以上条件都不为真，则执行这里的代码块
END IF;

示例：

DECLARE
    v_salary employees.salary%TYPE := 5000;
BEGIN
    IF v_salary > 10000 THEN
        DBMS_OUTPUT.PUT_LINE('High salary');
    ELSIF v_salary > 5000 THEN
        DBMS_OUTPUT.PUT_LINE('Medium salary');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Low salary');
    END IF;
END;

CASE语句
CASE语句用于根据一个表达式的值选择要执行的代码块。它类似于其他编程语言中的switch语句。基本的CASE语句语法如下：

CASE expression
    WHEN value1 THEN
        -- 如果表达式的值等于value1，则执行这里的代码块
    WHEN value2 THEN
        -- 如果表达式的值等于value2，则执行这里的代码块
    ...
    ELSE
        -- 如果表达式的值不等于任何值，则执行这里的代码块
END CASE;

示例：

DECLARE
    v_job employees.job_id%TYPE := 'MANAGER';
BEGIN
    CASE v_job
        WHEN 'MANAGER' THEN
            DBMS_OUTPUT.PUT_LINE('Manager');
        WHEN 'CLERK' THEN
            DBMS_OUTPUT.PUT_LINE('Clerk');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Other job');
    END CASE;
END;

2.2.2 循环控制

在PL/SQL中，循环语句用于重复执行一段代码块，直到满足某个条件或达到某个次数。PL/SQL提供了多种类型的循环语句，包括LOOP循环、WHILE循环和FOR循环。下面分别介绍这些循环语句的基本语法和用法。

LOOP循环
LOOP循环是最基本的循环语句，它会无限循环执行代码块，直到遇到EXIT语句或条件不再满足。基本的LOOP循环语法如下：

LOOP
    -- 这里是要重复执行的代码块
    -- 可以在代码块中使用EXIT来跳出循环
END LOOP;

示例：

DECLARE
    v_counter NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
        v_counter := v_counter + 1;
        EXIT WHEN v_counter > 5;
    END LOOP;
END;

WHILE循环：
WHILE循环会在每次循环之前检查条件，只有当条件为真时才会执行代码块。基本的WHILE循环语法如下：

WHILE condition LOOP
    -- 如果条件为真，则执行这里的代码块
END LOOP;

示例：

DECLARE
    v_counter NUMBER := 1;
BEGIN
    WHILE v_counter <= 5 LOOP
        DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
        v_counter := v_counter + 1;
    END LOOP;
END;

FOR循环：
FOR循环用于指定循环的次数，它会在每次循环中自动增加计数器的值。基本的FOR循环语法如下：

FOR counter IN range LOOP
    -- 这里是要重复执行的代码块
END LOOP;

示例：

DECLARE
    v_total NUMBER := 0;
BEGIN
    FOR i IN 1..5 LOOP
        v_total := v_total + i;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Total: ' || v_total);
END;

这些循环语句在PL/SQL中非常常用，可以根据不同的需求选择合适的循环类型来实现代码的重复执行。合理使用循环语句可以简化代码逻辑，提高代码的可读性和可维护性。

2.2.3 顺序结构

GOTO语句：
GOTO语句是一种无条件转移语句，它可以使程序跳转到指定的标签处继续执行。在PL/SQL中，GOTO语句的语法如下：
```
GOTO label;
...
label:
-- 这里是要执行的代码块
```
在上面的语法中，label是一个标签，可以是任何合法的标识符。当执行到GOTO语句时，程序会跳转到指定的标签处继续执行。GOTO语句的使用应该尽量避免，因为它会使程序的流程变得混乱，难以理解和维护。通常情况下，可以通过合理的程序设计和结构化的控制流程来避免使用GOTO语句。
NULL语句：
NULL语句是一种空语句，它不执行任何操作，仅仅是占用一个语句的位置。在PL/SQL中，NULL语句可以用于占位符或者在条件控制语句中表示空语句块。例如：
```
IF condition THEN
    -- 如果条件为真，则执行这里的代码块
ELSE
    NULL; -- 如果条件为假，则不执行任何操作
END IF;
```
在上面的例子中，当条件为假时，NULL语句表示不执行任何操作。NULL语句通常用于在条件控制语句中表示空语句块，或者作为占位符使用。

总的来说，顺序控制是程序执行的基本方式，而GOTO语句应该尽量避免使用，因为它会使程序的流程变得混乱，难以理解和维护。而NULL语句则可以在一些特殊情况下使用，例如在条件控制语句中表示空语句块。

oracle11g新增了continue语句
continue语句可在循环中使用。该语句可将逻辑移到循环结尾，然后再移到循环开头。例如：
```
declare
  j number:=1;
begin
  loop
      j:=j+1;
      exit      when j>8;
      continue  when j>4;
      dbms_output.put_line(to_char(j)||'---');
  end loop;
end;
```
这是一段PL/SQL代码，它使用了循环控制语句来控制程序的流程。具体来说，这段代码使用了loop、exit和continue三个关键字来实现循环控制。

首先，代码声明了一个变量j，并将其初始化为1。然后使用loop关键字开始一个循环，循环体内部包含三个语句：j:=j+1、exit和continue。其中，j:=j+1表示每次循环j的值加1；exit表示当j>8时退出循环；continue表示当j>4时跳过本次循环，继续执行下一次循环。

三、oracle的动态SQL

动态SQL是指在程序运行时动态构建SQL语句并执行的一种技术。在Oracle数据库中，动态SQL通常通过使用EXECUTE IMMEDIATE语句来实现。动态SQL的主要特点是可以根据程序运行时的条件动态生成SQL语句，从而实现更灵活的数据操作。

动态SQL通常用于以下情况：

动态生成查询条件：根据用户输入或程序运行时的条件动态生成查询条件，以实现动态的数据查询。
动态表名和列名：有时需要根据程序运行时的条件来确定表名和列名，动态SQL可以实现这样的需求。
动态DDL语句：例如创建表、修改表结构等操作，可以使用动态SQL来实现。

在Oracle中，可以使用EXECUTE IMMEDIATE语句来执行动态SQL，其基本语法如下：

EXECUTE IMMEDIATE dynamic_sql_string [INTO {define_variable[, define_variable]... | record}];

其中，dynamic_sql_string是一个包含动态SQL语句的字符串，可以是任意合法的SQL语句，如SELECT、INSERT、UPDATE、DELETE等。INTO子句用于将查询结果存储到变量或记录中。

以下是一个简单的动态SQL的示例：

DECLARE
  sql_stmt VARCHAR2(200);
  emp_name employees.last_name%TYPE;
  emp_id employees.employee_id%TYPE;
BEGIN
  sql_stmt := 'SELECT employee_id, last_name FROM employees WHERE employee_id = :id';
  EXECUTE IMMEDIATE sql_stmt INTO emp_id, emp_name USING 100;
  DBMS_OUTPUT.PUT_LINE('Employee name is ' || emp_name);
END;