PL/SQL

PL/SQL 是块结构语言; PL/SQL 程序划分成几个部分，并在每个部分中写入逻辑代码块。
每个块由三个子部分组成：
声明部分
可执行命令
异常处理
基本结构：
DECLARE

BEGIN

EXCEPTION

只要是 BEGIN 和 END 之间，就可以使用 DBMS_OUTPUT.PUT_LINE(output);输出信息。

PL/SQL的基本语法

PL/SQL 标识符

PL/SQL 标识符是常量，变量，异常，过程，游标和保留字。标识符包括一个字母，可选择后面跟多 个字母，数字，美元符号，下划线和数字符号，不得超过 30 个字符

PL/SQL 分隔符

分隔符是具有特殊含义的符号

• 算术运算符

  • +、-、*、/

• 关系运算符
  关系运算符比较两个表达式或值，并返回一个布尔结果。

  • 、《、>=、<=、=

• 比较运算符

  • LIKE 运算符

    • 常与存储过程结合进行模糊比较

  • BETWEEN 运算符

    • 常用与条件，包括边界值

  • IN

    • 常用与比较条件（条件 in（给定已知集合））

  • IS NULL 运算符

    • 常用与比较条件（条件 isnull）

• 逻辑运算符
  所有这些运算符都使用布尔运算符并产生布尔运算结果

  • 与（and）
  • 或（or）
  • 非（not）

• 字符串运算符

• 属性绑定

  • %

• 赋值运算符

  • ;=

• 关联运算符

  • =>

• 连接运算符

  • ||

• 指数运算符

  ---

• 范围运算符

  • ..

PL/SQL 变量

• 变量声明

  • variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]
    例：sales number(10, 2);

• 变量初始化

  • DEFAULT 关键字
    b integer default 20;

  • 分配运算符

• 变量作用域

  • 局部变量

    • 内部块中声明的变量，外部块不可访问

  • 全局变量

    • 在最外部块或包中声明的变量

• 将 SQL 查询结果分配给 PL/SQL 变量
  对于 SELECT 列表中的每个项目，INTO 列表中必须有一个对应的类型兼容变量

  • 使用 SQL 的 SELECT INTO 语句将值分配给 PL/SQL 变量
    DECLARE
    c_id employee.id%type := 1;
    c_name employee.name%type;
    c_addr employee.address%type;
    c_sal employee.salary%type;
    BEGIN
    SELECT name, address, salary INTO c_name, c_addr, c_sal
    FROM employee
    WHERE id = c_id;
    dbms_output.put_line (‘员工’||c_name||’来自’||c_addr||’，薪水’||c_sal);
    END;

• 常量

  • CONSTANT 关键字声明常量

数据类型

• 标量(SCALAR)类型
  用来存储单个值，例如：NUMBER，DATE 或 BOOLEAN 等

  • 数字数据类型

    • 存储数据为日期

  • 字符数据类型

    • 存储字符串或字符数据

  • 日期数据类型

    • 存储日期和时间值

  • 布尔数据类型

    • 布尔逻辑值

• 属性数据类型
  用于引用数据库列的数据类型，以及表示表中一行的记录类型

  • 列类型

    • 变量 表名.字段%type
      引用变量和数据库中列数据的类型

  • 行类型

    • 变量 表名.字段%rowtype
      提供表示数据库中一行数据的类型

• 大对象(LOB)数据类型

  • 指向与其他数据项(例如：文本，图形图像，视频剪辑和声音波形)分开存 储的大对象的指针。

• 复合数据类型

  • 具有可单独访问的内部组件的数据项。例如，集合和记录。

• 引用数据类型

  • 指向其他数据项

条件控制

• IF-THEN 语句
  IF condition THEN S;
  END IF;
  condition 是布尔或关系条件，S 是简单或复合语句

• IF-THEN-ELSE 语句
  IF condition THEN
  S1;
  ELSE
  S2;
  END IF;

• IF-THEN-ELSIF-THEN-ELSE 语句
  多分支语句

• CASE 语句
  CASE selector
  WHEN ‘value1’ THEN S1;
  WHEN ‘value2’ THEN S2;
  WHEN ‘value3’ THEN S3;
  …
  ELSE Sn; — default case
  END CASE;

• 可搜索 CASE 语句
  CASE
  WHEN selector = ‘value1’ THEN S1;
  WHEN selector = ‘value2’ THEN S2;
  WHEN selector = ‘value3’ THEN S3;
  …
  ELSE Sn; — default case
  END CASE;

  • 可搜索的 CASE 语句没有选择器，语句中的 when 子句包含给出布尔值的搜索条件
• 嵌套 IF-THEN-ELSE 语句
  IF (boolean_expression1) THEN
  — executes when the boolean expression 1 is true
  IF (boolean_expression2) THEN
  — executes when the boolean expression 2 is true
  sequence-of-statements;
  END IF;
  ELSE
  — executes when the boolean expression 1 is not true
  else-statements;
  END IF;

循环语句

• 基本 LOOP 循环
  LOOP
  Sequence of statements;
  END LOOP;
  语句序列(Sequence of statements;)可以是单个语句或一组语句。需要一个 EXIT 语句或一个 EXIT WHEN 语句来中断循环。

  • 基本循环结构包含 LOOP 和 END LOOP 语句之间的语句序列。通过每次迭代，执行语句序列，然后在循环顶部继续控制。 基本循环语法：

• while…loop 循环语句
  WHILE condition LOOP
  sequence_of_statements
  END LOOP;

  • 只要给定条件为真，则会执行

• for…loop 循环语句
  FOR counter IN initial_value .. final_value LOOP
  sequence_of_statements;
  END LOOP;
  循环变量或计数器的 initial_value 和 final_value 可以是文字，变量或表达式，但必须对数字求值， initial_value 初始值可以不为1，但循环计数器增量(或减量)必须为 1

  • FOR LOOP 语句是一种重复控制结构，可以有效地编写一个需要执行特定次数的循环

• 嵌套循环

  • PL/SQL 中嵌套的基本 LOOP 语句的语法
    LOOP
    Sequence of statements1
    LOOP
    Sequence of statements2
    END LOOP;
    END LOOP;

  • PL/SQL 中嵌套 FOR LOOP 语句的语法
    FOR counter1 IN initial_value1 .. final_value1
    LOOP sequence_of_statements1
    FOR counter2 IN initial_value2 .. final_value2
    LOOP sequence_of_statements2
    END LOOP;
    END LOOP;

  • PL/SQL 中嵌套的 WHILE LOOP 循环语句
    WHILE condition1 LOOP
    sequence_of_statements1
    WHILE condition2 LOOP
    sequence_of_statements2
    END LOOP;
    END LOOP;

• 循环控制语句

  • exit 语句

    • 结束循环

  • continue 语句

    • 跳过本次循环，进行下一次循环

  • goto 语句

    • 转移控制到标记语句

异常处理

• 分类

  • 系统预定义的异常
    当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发

  • 用户自定义异常
    用户可以在 PL/SQL 块的声明部分自定义异常，自定义异常可以通过 raise 语句显示引发

• 异常处理的语法
  DECLARE
  
  BEGIN
  
  EXCEPTION
  
  WHEN exception1 THEN
  exception1-handling-statements
  WHEN exception2 THEN
  exception2-handling-statements
  WHEN exception3 THEN
  exception3-handling-statements
  ……..
  WHEN others THEN
  exception3-handling-statements
  END;

  • 如果不确定异常，可以用 others 处理。 异常处理可以按任意次序排列,但 others 必须放在最后。

• 系统预定义异常

  • 预定义的异常（PL/SQL提供的异常）
    DECLARE
    c_id customers.id%type := 100;
    c_name customerS.name%type;
    c_addr customers.address%type;
    BEGIN
    SELECT name, address INTO c_name,c_addr FROM customers
    WHERE id = c_id;
    DBMS_OUTPUT.PUT_LINE (‘姓名: ‘|| c_name);
    DBMS_OUTPUT.PUT_LINE (‘地址: ‘ || c_addr);
    EXCEPTION
    WHEN no_data_found THEN
    dbms_output.put_line(‘没有找到符合条件的客户信息!’);
    WHEN others THEN
    dbms_output.put_line(‘Error!’);
    END;

    • INVALID_NUMBER

      • 非法数字

    • NO_DATA_FOUND

      • 未查找到数据

    • PROGRAM_ERROR

      • 程序错误

    • TOO_MANY_ROWS

      • 查询多行错误

    • ZERO_DIVIDE

      • 除数为0错误

    • LOGIN_DENIED

      • 登陆受限

    • ACCESS_INTO_NULL

      • 未初始化对象

  • 引发异常
    DECLARE
    exception_name EXCEPTION;
    BEGIN
    IF condition THEN
    RAISE exception_name;
    END IF;
    EXCEPTION
    WHEN exception_name THEN
    statement;
    END;

    • 只要有内部数据库错误，数据库服务器就会自动产生(引发)异常，但程序员可以使用命令 RAISE 明确地引发异常

  • 区别

    • 引发异常需要程序员声明，然后由RAISE引发异常，预定义无需声明，在异常处理直接使用

• 用户自定义异常

  • 用户定义的异常必须声明，然后使用 RAISE 语句显式地引发
  • 声明异常的语句：DECLARE my-exception EXCEPTION;

触发器

语句级的触发器可以在某些语句执行前或执行后被触发。而行级触发器则是在定义的了触发的表中的行数据改变时就会被触发一次

定义

• 是与表有关的数据库对象

举例说名

1、在一个表中定义的语句级的触发器，当这个表被删除时，程序就会自动执行触发器里面定义的操作过程。这个就是删除表的操作就是触发器执行的条件了。
2、 在一个表中定义了行级的触发器，那当这个表中一行数据发生变化的时候，比如删除了一行记录，那触发器也会被自动执行了。

再比如你现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的 log 日
志，如果在不使用触发器的情况下，你需要编写程序语言逻辑才能实现，但是如果你定义了一个触发器，触发器
的作用就是当你在用户表中插入一条数据的之后帮你在日志表中插入一条日志信息。

触发器的操作

触发器不能重名

• 创建触发器
   trigger_name：触发器的名称, 由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。
   tirgger_time：触发时机，为 BEFORE（表示在数据库动作之前触发器执行）或者 AFTER（表示在数据库动作 之后触发器执行）
   trigger_event：触发事件（指明哪些数据库动作会触发此触发器），为 INSERT、DELETE 或者 UPDATE
   tb_name：表示建立触发器的表名，就是在哪张表上建立触发器
   for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。
   trigger_stmt：触发器的程序体，可以是一条 SQL 语句或者是用 BEGIN 和 END 包含的多条语句
  其中，触发器名参数指要创建的触发器的名字。
  BEFORE 和 AFTER 参数指定了触发执行的时间，在事件之前或是之后。
  FOR EACH ROW 表示任何一条记录上的操作满足触发事件都会触发该触发器

  • CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt;

• 案例
  CREATE TABLE t_dept
  (
  deptno INTEGER,
  dname VARCHAR2(20)
  );
  CREATE TABLE t_diary (
  tablename VARCHAR2(20),
  diarytime date
  );
  — 创建触发器
  CREATE or replace TRIGGER tri_diarytime
  BEFORE INSERT
  ON t_dept FOR EACH ROW
  BEGIN
  INSERT INTO t_diary(tablename,diarytime)
  VALUES(‘t_dept’,sysdate);
  END;
  — 检验效果
  INSERT INTO t_dept(deptno,dname)
  VALUES(1,’教学部’);
  select from t_dept;
  select from t_diary;

• Oracle 创建的DML 相关触发器

  • 1. BEFORE INSERT
  • 1. BEFORE DELETE
  • 1. BEFORE UPDATE
  • 1. AFTER INSERT
  • 1. AFTER DELETE
  • 1. AFTER UPDATE

• 删除触发器

  • DROP TRIGGER trigger_name;

触发器应用

• 案例（使用触发器进行 id 自增）

  • 建表说明
    CREATE TABLE demo
    (
    id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR2(50),
    code VARCHAR2(50),
    clazz VARCHAR2(50)
    );

  • 创建序列
    create sequence demo_seq increment by 1 start with 1 ;

  • 创建触发器
    create or replace trigger demo_trigger
    before inserton demo
    for each row
    begin
    — :new —为一个引用最新的列值;
    select demo_seq.nextval into :new.id from dual;
    end;
    INSERT INTO demo(name,code,clazz) VALUES(1,1,1)
    select * from demo;

子程序和程序包

存储过程(子程序)

• 定义

  • 从根本上讲，过程就是命名的 PL/SQL 程序块，这种做法被称为“模块化设计”

• PL/SQL 函数与存储过程的区别

  • 函数 - 这些子程序返回单个值; 主要用于计算和返回值。
  • 存储过程(程序) - 这些子程序不直接返回值; 主要用于执行动作

• 创建存储过程

  • CREATE [OR REPLACE] PROCEDURE PROCEDURE_NAME
    CREATE [OR REPLACE] PROCEDURE procedure_name
    [(parameter_name [IN | OUT | IN OUT] type [, …])]
    {IS |AS}
    BEGIN < procedure_body >
    END procedure_name;
    procedure-name 是要创建的存储过程的名称。
     [OR REPLACE]选项允许修改现有的过程。
     可选参数列表包含参数的名称，模式和类型。IN 表示将从外部传递的值，OUT 表示将用于返回过程外的值的参数。
     procedure-body 包含可执行部分

  • 案例
    CREATE OR REPLACE PROCEDURE greetings
    AS
    BEGIN
    dbms_output.put_line(‘Hello World!’);
    END;

• 执行存储过程

  • 调用方式
    （1）在 sql plus 中测试，可以使用
    exec greetings;
    或者
    EXECUTE greetings;
    （2）在 PL/SQL Developer 开发工具中，可以在 sql window 窗 口中
    — 执行存储过程
    begin
    greetings();
    end;

    • 使用 EXECUTE 关键字
    • 从 PL/SQL 块调用过程的名称

• 删除存储过程

  • DROP PROCEDURE procedure-name;
    —删除存储过程
    DROP PROCEDURE greetings;

PL/SQL 子程序中的参数模式

• 参数类型

  • IN

    • IN 参数允许将值传递给子程序

  • OUT

    • OUT 参数返回一个值给调用程序

  • IN OUT

    • IN OUT 参数将初始值传递给子程序，并将更新的值返回给调用者。

• 传递参数的方法

  • 位置符号

  • 命名符号

    • 实际参数与使用箭头符号(=>)的形式参数相关联
  • 混合符号

• 参数传递模式

  • IN 和OUT模式

  • IN OUT 模式

  • 案例
    DECLARE a number; b number; c number;
    PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
    BEGIN
    IF
    x < y THEN
    z:= x;
    ELSE
    z:= y;
    END IF;
    END;
    BEGIN
    a:= 12;
    b:= 35;
    findMin(a, b, c);
    dbms_output.put_line(‘两个数：12, 35 中的最小值是 : ‘ || c);
    END;

函数

函数是可以返回返回值的 PL/SQL 子程序，与过程(也叫子程序)类似，只不过函数有返回一个值，而过程没有返回 值

• 创建函数
  CREATE [OR REPLACE] FUNCTION function_name
  [(parameter_name [IN ] type [, …])]
  RETURN return_datatype
  {IS |AS}
  BEGIN
  < function_body >
  END [function_name];
  function-name 是指定要创建的函数的名称。
  [OR REPLACE]选项指示是否允许修改现有的函数。
  可选参数列表包含参数的名称，模式和类型。 IN 表示将从外部传递的值。
  函数必须包含一个返回(RETURN)语句。
  RETURN 子句指定要从函数返回的数据类型。
  function-body 包含可执行部分。

  • 案例
    在以前的表customer中测试的
    CREATE OR REPLACE FUNCTION totalCustomers
    RETURN number IS
    total number(2) := 0;
    BEGIN
    SELECT count(*) into total
    FROM customers;
    RETURN total;
    END;

• 调用函数
  调用一个函数，只需要传递所需的参数和函数名，如果函数返回一个值，那么可以存储返回的值

  • 案例
    DECLARE
    c number(2);
    BEGIN
    c := totalCustomers();
    dbms_output.put_line(‘当前客户的总数为: ‘ || c);
    END;

游标

PL/SQL 是用游标来管理 SQL 的 SELECT 语句的。游标是为了处理这些语句而分配的一大块内存。它提供了对一 个结果集进行逐行处理的能力，可看作是一种特殊的指针。它与某个查询结果集相关联，可以指向结果集的任意
位置，以便对指定置的数据进行处理。使用它可以在查询数据的时对数据进行处理。
可以命名一个游标，以便在程序中引用它来获取和处理 SQL 语句返回的行，一次处理一个(行)。

分类

• 隐式游标
  当执行 SQL 语句时，如果语句没有显式游标，则 Oracle 会自动创建隐式游标。程序员无法控制隐式游标及其信
  息。
  每当发出 DML 语句(INSERT，UPDATE 和 DELETE)时，隐式游标与此语句相关联。 对于 INSERT 操作，游标保存需要插入的数据。对于 UPDATE 和 DELETE 操作，游标将保存受到影响的行。

  • 属性

    • SQL%FOUND
      如果 INSERT，UPDATE 或 DELETE 语句影响一行或多行，或 SELECT INTO 语句返回一行或多行，则返回 TRUE，否则返回 FALSE。

    • SQL%NOTFOUND
      与%FOUND 的逻辑相反。 如果 INSERT，UPDATE 或 DELETE 语句没有影响任何行，或 SELECT INTO 语句 未返回任何行，则返回 TRUE。

    • SQL%ISOPEN
      由于 Oracle 在执行关联的 SQL 语句后会自动关闭 SQL 游标，因此总是为隐式游标返回 FALSE。

    • SQL%ROWCOUNT
      返回受 INSERT，UPDATE 或 DELETE 语句，或者受 SELECT INTO 语句影响的行数

  • 案例
    1、创建customers表
    CREATE TABLE customers
    ( id number(10) NOT NULL,
    name varchar2(50) NOT NULL,
    age number(2) NOT NULL,
    address varchar2(50),
    salary float NOT NULL,
    CONSTRAINT customers_pk PRIMARY KEY (id)
    );
    2、给customers表插入数据
    INSERT INTO customers (id,name,age,address,salary)
    VALUES(1, ‘白龙马’,32,’北京’, 22999.00);
    INSERT INTO customers (id,name,age,address,salary)
    VALUES(2, ‘孙悟空’,25,’海口’, 5999.00);
    INSERT INTO customers (id,name,age,address,salary)
    VALUES(3, ‘牛魔王’,22,’广州’, 9800.98);
    INSERT INTO customers (id,name,age,address,salary)
    VALUES(4, ‘唐僧’,26,’北京’, 18700.00);
    INSERT INTO customers (id,name,age,address,salary)
    VALUES(5, ‘三太子’,28,’上海’, 18999.00);
    INSERT INTO customers (id,name,age,address,salary)
    VALUES(6, ‘沙悟净’,42,’深圳’, 32999.00);
    commit;
    3、更新员工的薪资
    DECLARE
    total_rows number(2);
    BEGIN
    UPDATE customers SET salary = salary + 500;
    —中间加提交，sql%notfound 值为 true
    —commit;
    IF sql%notfound THEN
    dbms_output.put_line(‘没有找到客户信息~’);
    ELSIF sql%found THEN
    total_rows := sql%rowcount;
    dbms_output.put_line(‘一共有：’ || total_rows || ‘ 个客户的工资被更新！ ‘);
    END IF;
    END;

• 显式游标
  显式游标是由 PL/SQL 程序员定义和命名的游标

  • 使用步骤

    • 1、声明游标初始化内存

      • CURSOR 游标名 IS SELECT 字段1,字段2, 字段3，… FROM 表名;
        CURSOR c_customers IS SELECT id, name, address FROM customers;

    • 2、打开游标分配内存
      SQL 语句返回的行记录数据提取到其中

      • OPEN 游标名;
        OPEN c_customers;

    • 3、从游标获取数据
      获取游标，仅访问一行

      • FETCH 游标名 INTO 游标对应表的字段1, 游标对应表的字段2, 游标对应表的字段3,…;
        FETCH c_customers INTO c_id, c_name, c_addr;

    • 4、关闭游标以释放分配的内存
      关闭游标意味着释放分配的内存

      • CLOSE 游标名;
        CLOSE c_customers;

  • 案例
    DECLARE
    c_id customers.id%type;
    c_name customers.name%type;
    c_addr customers.address%type;
    CURSOR c_customers is
    SELECT id, name, address FROM customers;
    BEGIN
    OPEN c_customers;
    LOOP
    FETCH c_customers into c_id, c_name, c_addr;
    EXIT WHEN c_customers%notfound;
    dbms_output.put_line(c_id || ‘ ‘ || c_name || ‘ ‘ || c_addr);
    END LOOP;
    CLOSE c_customers;
    END;

• REF 游标

XMind - Trial Version