PL/SQL 入门

1. 概述

• PL/SQL(Procedure Language/SQL)是 Oracle 对 sql 语言的过程化扩展,指在 SQL 命令语言中增加了
  过程处理语句(如分支,循环等),使 SQL 语言具有过程处理能力.

2. 语法

// 输出 hello world
    declare
        // 说明部分
    begin  // 程序开始
        程序执行语句(DML 语句)
        dbms_output.put_line('Hello World!');  // 调用程序包,完成输出工作
    exception
        异常处理部分
    end; // 程序结束
    /    // 反斜线表示退出并执行程序

2.1 变量和常量

var   char(15);
married   boolean := true;   // ":=" 表示赋值, "=" 表示"=="
psal    number(7,2);
my_name  emp.ename%type;   // 引用型变量,即 my_name 的类型与 emp 表中 ename 列的类型一样;
emp_rec  emp%rowtype;    // 记录型变量,代表一行; 可以理解为数组;

// 示例: 查询并打印 7839 的姓名和薪水
    declare
        pename emp.ename%type;
        psal   emp.sal%type;
    begin
        // 查询 7839 并赋值给变量
        select ename,sal into pename,psal from emp where empno=7839;

        // 打印
        dbms_output.put_line(pename||'的薪水是'||psal);
    end;

    // 记录型变量的使用
    declare
        emp_rec  emp%rowtype;
    begin
        select * into emp_rec from emp where empno=7839;

        dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
    end;

2.2 IF 语句

// 判断用户从键盘输入的数字
    //接受键盘输入
    // num: 地址值,在该地址上保存了输入的值
    accept num prompt '请输入一个数字:';

    declare
        // 定义一个变量保存数字
        pnum number := #
    begin
        if pnum = 0 then dbms_output.put_line('您输入的是0');
            elsif pnum = 1 then dbms_output.put_line('您输入的是1');
            else  pnum = 2 then dbms_output.put_line('您输入的是其他数字');
        end if;
    end;

2.3 循环

// 打印 1~10
    declare
        pnum number := 1;
    begin
        loop
            // 退出条件
            exit when pnum > 10;

            dbms_output.put_line(pnum);

            // 加一
            pnum := pnum + 1;
        end loop;
    end;

2.4 游标(Cursor)

1. Java 程序中有集合的概念,在 pl/sql 中也会用到多条记录,这时需要用到游标, 游标可以存储查询返回的多条数据;
2. 语法:
   • CURSOR 游标名 IS SELECT 语句;
   • 示例: cursor cl is select ename from emp;
3. 使用步骤
   • 打开游标: open cl;
   • 取一行游标的值,赋给变量: fetch cl into pename;
   • 关闭游标,释放资源: close cl;
   • 注意: pename 必须与 emp 表中的 ename 列类型一致;
4. 光标的属性
   • %isopen
   • %rowcount: 影响的行数;
   • %found
   • %notfound

// 查询并打印员工的姓名和薪水
    declare
        // 定义光标
        cursor cemp is select ename, sal from emp;
        pename  emp.ename%type;
        psal    emp.sal%type;
    begin
        // 打开光标
        open cemp;

        loop
            // 取出当前记录
            fetch cemp into pename, sal;

            // 如果没有取到记录,则退出循环
            exit when cemp%notfound;

            dbms_output.put_line(pename||'的薪水是'||psal);
        end loop;

        // 关闭光标
        close cemp;
    end;

// 带参数的光标
    // 查询某个部门的员工姓名
    declare
        // dno number 表示形参
        cursor cemp(dno number) is select ename from emp where deptno=dno;
        pename emp.ename%type;
    begin
        open cemp(20);
        loop
            fetch cemp into pename;
            exit when cemp%notfound;

            dbms_output.put_line(pename);
        end loop;
        close cemp;
    end;

2.5 异常

1. 系统定义异常
   • no_data_found: 没有找到数据;
   • too_many_rows: select...into 语句匹配多个行;
   • zero_divide: 被零除;
   • value_error: 算术或转换错误;
   • timeout_on_resource: 在等待资源时,发生超时;

3. 存储过程和存储函数

1. 概念
   • 存储在数据库中供所有用户程序调用的子程序叫存储过程,存储函数;
2. 存储过程

// 创建存储过程
    create [or replace] PROCEDURE 过程名(参数列表)
    AS
        PLSQL 子程序体;

    // 给指定的员工涨100, 并且打印涨前和涨后的薪水
    create or replace procedure raiseSalary(eno in number)
    as

        // 定义变量,用来保存涨前的薪水
        psal  emp.sal%type;
    begin
        // 得到涨前的薪水
        select sal into psal from emp where empno=eno;

        // 涨100
        update emp set sal=sal+100 where empno=eno;

        // 输出
        dbms_output.put_line('涨前: '||psal||'   涨后:'||(psal+100));
    end sayHelloWorld;

    // 调用存储过程
    // 第一种方式
    exec raiseSalary(7556);

    // 第二种方式
    begin
        raiseSalary(7556);
        raiseSalary(7839);
        commit;
    end;

3.3 存储函数

• 存储函数为一命名的存储程序,可带参数,并返回一个计算值.存储函数和存储过程的结构类似,但必须有一个 RETURN
  子句,用于返回函数值.
• 但存储过程和存储函数都可以通过 out 指定一个或多个输出参数.

// 创建存储函数
    CREATE [OR REPLACE] FUNCTION 函数名(参数列表)
    RETURN 函数值类型
    AS
        PLSQL 子程序体;

4. 包结构和包体

// 需求: 在 out 参数中使用游标
//       查询某个部门中所有员工的所有信息

    // 申明包结构
    CREATER OR REPLACE PACKAGE MYPACKAGE AS

        // 自定义类型, "ref cursor"表示为游标类型
        type empcursor is ref cursor;

        // 定义存储过程, 即查询某个部门中所有员工的信息
        procedure queryEmpList(dno in number, empList out empcursor);
    END MYPACKAGE;


    // 创建包体
    CREATE OR REPLACE PACKAGE BODY MYPACKAGE AS

        // 对存储过程的实现
        procedure queryEmpList(dno in number, empList out empcursor) AS
        begin
            open empList for select * from emp where deptno=dno;
        end queryEmpList;
    END MYPACKAGE;

5. 触发器

5.1 概念

• 数据库触发器是一个与表相关联的,存储的PL/SQL程序.每当一个特定的数据操作语句(INSERT, UPDATE, DELETE)
  在指定的表上发出时,Oracle 自动地执行触发器中定义的语句序列.

5.2 触发器的类型

• 语句级触发器: 在指定的操作语句操作之前或之后执行一次,不管这条语句影响了多少行;
• 行级触发器(FOR EACH ROW): 触发语句作用的每一条记录都被触发.在行级触发器中使用 :old 和 :new 伪记录
  变量,识别值的状态;

5.3 触发器的应用

• 数据确认;
• 实施复杂的安全性检查;
• 做审计,跟踪表上所做的数据操作等;
• 数据的备份和同步;

// 创建触发器
    CREATE [or REPLACE] TRIGGER 触发器名
    {BEFORE | AFTER}
    {DELETE | INSERT | UPDATE [OF 列名]}
    ON 表名
    [FOR EACH ROW [WHEN(条件)]]
    PLSQL 块