• 17.Oracle11g的PL/SQL基础

    Oracle11g的PL/SQL基础

    一、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 分为三个部分，声明部分、可执行部分和异常处理部分

    1. 声明部分：

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

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7

    2. 可执行部分：

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

      • 1
      • 2
      • 3

    3. 异常处理部分：

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

      • 1
      • 2

    4. 具体案例
        当创建一个存储过程时，通常会包含声明部分、可执行部分和异常处理部分。以下是一个包含这三部分的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;
/

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      • 17
      • 18
      • 19
      • 20

        在这个例子中，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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

      其中，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;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7

      示例：

      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;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11

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

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

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9

      示例：

      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;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12

    2.2.2 循环控制

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

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

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

      • 1
      • 2
      • 3
      • 4

      示例：

      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;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9

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

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

      • 1
      • 2
      • 3

      示例：

      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;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8

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

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

      • 1
      • 2
      • 3

      示例：

      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;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8

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

    2.2.3 顺序结构

    • GOTO语句：
      GOTO语句是一种无条件转移语句，它可以使程序跳转到指定的标签处继续执行。在PL/SQL中，GOTO语句的语法如下：

      GOTO label;
...
label:
-- 这里是要执行的代码块

      • 1
      • 2
      • 3
      • 4

        在上面的语法中，label是一个标签，可以是任何合法的标识符。当执行到GOTO语句时，程序会跳转到指定的标签处继续执行。GOTO语句的使用应该尽量避免，因为它会使程序的流程变得混乱，难以理解和维护。通常情况下，可以通过合理的程序设计和结构化的控制流程来避免使用GOTO语句。

    • NULL语句：
      NULL语句是一种空语句，它不执行任何操作，仅仅是占用一个语句的位置。在PL/SQL中，NULL语句可以用于占位符或者在条件控制语句中表示空语句块。例如：

      IF condition THEN
    -- 如果条件为真，则执行这里的代码块
ELSE
    NULL; -- 如果条件为假，则不执行任何操作
END IF;

      • 1
      • 2
      • 3
      • 4
      • 5

        在上面的例子中，当条件为假时，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;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10

        这是一段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通常用于以下情况：

    1. 动态生成查询条件：根据用户输入或程序运行时的条件动态生成查询条件，以实现动态的数据查询。

    2. 动态表名和列名：有时需要根据程序运行时的条件来确定表名和列名，动态SQL可以实现这样的需求。

    3. 动态DDL语句：例如创建表、修改表结构等操作，可以使用动态SQL来实现。

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

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

    • 1

      其中，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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

      在上面的示例中，首先声明了一个字符串变量sql_stmt，然后动态构建了一个SELECT语句并将其赋给sql_stmt。接着使用EXECUTE IMMEDIATE语句执行动态SQL，并将查询结果存储到emp_id和emp_name变量中。

      需要注意的是，动态SQL的使用需要谨慎，因为动态SQL可能会存在SQL注入等安全风险。在构建动态SQL时，应该尽量避免直接拼接用户输入的内容，而是应该使用绑定变量或者参数化查询的方式来构建动态SQL，以提高安全性。

  • 相关阅读:
    Mysql主从复制
    android.support.v4.view.ViewPager爆红
    【第54篇】剪枝算法：通过网络瘦身学习高效卷积网络
    FreeRTOS入门教程（信号量的具体使用）
    day02 springmvc
    两年CRUD，二本毕业，备战两个月面试阿里，侥幸拿下offer定级P6
    React基础教程(四)：组件理解
    23端口登录的Telnet命令+传输协议FTP命令
    ES6 从入门到精通 # 20：async 的用法
    第12章 PyTorch图像分割代码框架-1
  • 原文地址：https://blog.csdn.net/qq_46645079/article/details/134528053