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 := &num;

    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)

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

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

3. 存储过程和存储函数

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

// 创建存储过程

    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 块

巴特西

PL/SQL 入门

1. 概述

2. 语法

2.1 变量和常量

2.2 IF 语句

2.3 循环

2.4 游标(Cursor)

2.5 异常

3. 存储过程和存储函数

3.3 存储函数

4. 包结构和包体

5. 触发器

5.1 概念

5.2 触发器的类型

5.3 触发器的应用

最新文章

热门文章