PL/SQL数据库学习总结

---

PL/SQL学习总结

---

一、概述

1. PL/SQL是一种高级数据库程序设计语言,专门用于对Oracle数据库进行访问,是对SQL语言存储过程语言的扩展,成为一种过程处理语言
2. PL/SQL是Oracle系统的核心语言,可使用的SQL语句有:
   INSERT,UPDATE,DELETE,SELECT…INTO,COMMIT,ROLLBACK,SAVEPOINT
3. 不能用SQL语句中DDL部分

二、块结构和组成元素

1. PL/SQL程序由三部分组成,分为声明部分,执行部分,异常处理部分
2. 结构
   DECLARE
   //声明部分:声明用到的变量,类型及游标以及局部的存储过程和函数
   BEGIN
   //执行部分:过程及SQL语句
   EXCEPTION
   //执行异常部分:错误处理(when…then)
   END;
3. set serveroutput on命令使语句有输出结果,
4. 表名.列名%type//动态获取指定列的类型,表名%rowtype//动态获取指定表中所有列的类型
5. 记录类型是把逻辑相关的数据作为一个单元存储起来称作域,其作用是存放互不相同但逻辑相关的信息
   声明语法:TYPE 记录类型名 IS RECORD(
   变量声明列表);
   声明记录类型的变量:变量名 记录类型名;

标识符	命名规则
程序变量	V_名字
程序常量	C_名字
游标变量	名字_cursor
异常标识	E_名字
表类型	名字_table_type
表	名字_table
记录类型	名字_record
绑定变量	G_名字
SQL*Plus替代变量	P_名字

三、流程控制

• 条件语句
  • IF<布尔表达式>THEN
     PL/SQL与SQL语句;
    END IF;
  • IF<布尔表达式>THEN
     PL/SQL与SQL语句;
    ELSE
     其他语句;
    END IF;
  • IF<布尔表达式>THEN
     PL/SQL与SQL语句;
    ELSIF<布尔表达式>THEN
     PL/SQL与SQL语句;
    …
    ELSE
     其他语句;
    END IF;
  • CASE 列
       WHEN 比较值1 THEN 返回值1
       WHEN 比较值2 THEN 返回值2
       …
       [ELSE 默认值]
    END;
• 循环语句
  • 四要素:初始化条件,循环体,循环条件,迭代条件
  • LOOP
       循环体;
       EXIT WHEN<判断条件语句>;//循环条件
       迭代条件;
    END LOOP;
  • WHILE<布尔表达式>LOOP//循环条件
       循环体;
       迭代条件;
    END LOOP;
  • FOR 循环计数器 IN 上限 … 下限 LOOP
       循环体;
    END LOOP;
• GOTO,EXIT与CONTINUE
  • 格式:GOTO 指定标号;//无条件的跳转到指定标号去
  • 标号格式:<<标号>>
  • EXIT:结束本层循环
  • CONTINUE:结束本次循环

四、游标

类似于java中的迭代器(iterator),来处理多行记录
游标是一个指向上下文的句柄或指针

• 隐式游标步骤:
  • PL/SQL为DML操作和单行SELECT语句隐式声明游标
  • 通过检查隐式游标的属性可以获取与最近执行的SQL语句相关的信息即通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性
• 显式游标步骤:
  • ①定义游标:
    • 格式:CURSOR 游标名[(参数1 数据类型[，参数2 数据类型…])] IS SELECT语句;
    • 如果定义了参数,则必须在打开游标时传递相应的实际参数
    • 指定参数类型时,不能使用长度约束
  • ②打开游标:
    • OPEN 游标名[(实际参数1[，实际参数2…])];
    • 执行游标所对应的SELECT语句,并把查询结果就被传送到了游标工作区,不能重复打开一个游标
  • ③提取游标:
    • FETCH 游标名 INTO 变量名1[，变量名2…]或记录变量;
    • 游标打开后有一个指针指向数据区,FETCH语句一次返回指针所指的一行数据,要返回多行需重复执行,可以使用循环语句来实现,控制循环可以通过判断游标的属性来进行
  • ④关闭游标:
    • CLOSE 游标名;
    • 显式游标打开后,必须显式地关闭
    • 游标一旦关闭,游标占用的资源就被释放,游标变成无效,必须重新打开才能使用
• 游标属性
  %FOUND 布尔型属性,当最近一次读记录时成功返回,值为true
  %NOTFOUND 布尔型属性,与%FOUND相反
  %ISOPEN 布尔型属性,当游标已打开时返回true
  %ROWCOUNT 数字型属性,返回已从游标中读取的记录数
• 游标for循环
  • 格式:
    FOR 索引变量 IN 游标名[(实际参数1[，实际参数2…])] LOOP
       游标数据处理代码
    END LOOP;
  • 自动执行游标的OPEN,FETCH,CLOSE和循环语句的功能

五、异常处理机制

• 分类:预定义错误,非预定义错误,用户自定义错误
• 预定义错误格式:
  EXCEPTION
     WHEN 异常类型1 THEN 处理异常代码
     WHEN 异常类型2 THEN 处理异常代码
     …
     WHEN OTHERS THEN
  END;
• 非预定义错误格式:
  • ①在定义部分定义异常
    自定义异常名 EXCEPTION;
  • ②在定义部分将定义好的异常情况与预定义错误联系起来
    PRAGMA EXCEPTION_INIT(自定义异常名,预定义错误号)
  • ③在异常处理部分对异常做处理
    WHEN 自定义异常名 THEN 处理异常代码
• 用户自定义错误格式:
  • ①在定义部分定义异常
    自定义异常名 EXCEPTION;
  • ②执行部分触发并转向到异常处理部分
    RAISE 自定义异常名;
  • ③在异常处理部分对异常做处理
    WHEN 自定义异常名 THEN 处理异常代码

六、存储函数与存储过程

• 概述:函数与过程统称为PL/SQL的子程序,函数总是向调用者返回数据,而过程则不返回数据
  存储过程多用于项目之间的数据共享，存储函数多被存储过程调用.
  存储函数可以再sql语句中调用，存储过程不能
• 创建存储函数
  CREATE [OR REPLACE] FUNCTION 函数名(参数列表) RETURN 返回类型
  IS|AS
     [声明变量,记录类型或游标]
  BEGIN
     函数体
  EXCEPTION
     异常处理
  END;
• 创建存储过程
  CREATE [OR REPLACE] PROCEDURE 存储过程名(参数列表)
  BEGIN
    存储过程体
  EXCEPTION
     异常处理
  END;
• 说明
  参数列表定义(参数名 参数模式 参数类型)
  参数模式:
     IN:该参数需调用方传值(默认)
     OUT:该参数可以作为返回值
     INOUT:该参数既需要传入值,又可以作为返回值

七、触发器

• 概述:触发器由一个事件来启动运行,即当某个事件发生时自动的隐式运行,触发器不能接收函数
• Oracle事件指对表或视图进行增删改的操作,Oracle将触发器功能扩展到触发Oracle如数据库的启动与关闭
• DML触发器格式:
  CREATE [OR REPLACE] TRIGGER 触发器名
  BEFORE|AFTER //事件前触发还是事件后触发
  INSERT|DELETE|UPDATE [OF column [,column…]] //什么事件触发
  ON 表名 //作用在哪个表
  [FOR EACH ROW] //触发器为行级触发器默认为语句级
  [WHEN 触发条件]
  触发体 //是标准的PL/SQL语句块
• 替代触发器(instead of触发器)
  对视图进行操作时定义的触发器,替代触发器只能定义在视图上
  格式:CREATE [OR REPLACE] TRIGGER 触发器名
  INSTEAD OF 触发事件
  ON 视图名称
  FOR EACH ROW //替代触发器必须指定为行级的触发器
  [WHEN 触发条件]
  触发体 //是标准的PL/SQL语句块
• 系统事件触发器
  对数据库实例或某个用户模式进行操作时定义的触发器
  分为:数据库系统触发器和用户触发器