17.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;
   

2. 可执行部分：

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

3. 异常处理部分：

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

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;
   /
   

     在这个例子中，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通常用于以下情况：

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

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

3. 动态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;

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

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