数据库第8章数据编程.ppt

第8章数据库编程 主讲教师 杨丽丽E mail yll Tel87091337 O 第8章Oracle数据库编程 8 1PL SQL简介8 2游标8 3存储过程8 4触发器 8 1PL SQL简介 1 简介PL SQL也是一种程序语言 叫做过程化SQL语言 ProceduralLanguage SQL PL SQL是Oracle数据库对标准数据库语言SQL的扩展 在普通SQL语句的使用上增加了编程语言的特点 所以PL SQL就是把数据操作和查询语句组织在PL SQL代码的过程性单元中 通过逻辑判断 循环等操作实现复杂的功能或者计算的程序语言 8 1PL SQL简介 1 简介PL SQL程序由三个部分组成 即声明部分 执行部分 异常处理部分 PL SQL程序的结构如下 DECLARE 声明部分 在此声明PL SQL用到的变量 类型及游标 以及局部的存储过程和函数 BEGIN 执行部分 过程及SQL语句 即程序的主要部分 EXCEPTION 执行异常部分 错误处理 END 8 1PL SQL简介 1 简介PL SQL程序由三个部分组成 即声明部分 执行部分 异常处理部分 PL SQL程序的结构如下 DECLARE 声明部分 在此声明PL SQL用到的变量 类型及游标 以及局部的存储过程和函数 BEGIN 执行部分 过程及SQL语句 即程序的主要部分 EXCEPTION 执行异常部分 错误处理 END 8 1PL SQL简介 1 简介PL SQL程序由三个部分组成 即声明部分 执行部分 异常处理部分 PL SQL程序的结构如下 DECLARE 声明部分 在此声明PL SQL用到的变量 类型及游标 以及局部的存储过程和函数 BEGIN 执行部分 过程及SQL语句 即程序的主要部分 EXCEPTION 执行异常部分 错误处理 END 8 1PL SQL简介 1 简介PL SQL程序由三个部分组成 即声明部分 执行部分 异常处理部分 PL SQL程序的结构如下 DECLARE 声明部分 在此声明PL SQL用到的变量 类型及游标 以及局部的存储过程和函数 BEGIN 执行部分 过程及SQL语句 即程序的主要部分 EXCEPTION 执行异常部分 错误处理 END 8 1PL SQL简介 1 简介PL SQL程序由三个部分组成 即声明部分 执行部分 异常处理部分 PL SQL程序的结构如下 DECLARE 声明部分 在此声明PL SQL用到的变量 类型及游标 以及局部的存储过程和函数 BEGIN 执行部分 过程及SQL语句 即程序的主要部分 EXCEPTION 执行异常部分 错误处理 END 其中执行部分是必须的 8 1PL SQL简介 2 变量及数据类型 1 变量声明在declare部分声明变量 格式如下 declare变量名数据类型 变量名数据类型 2 变量赋值变量赋值格式如下 变量名 常量或表达式可以在声明变量的同时给变量赋值 也可以在执行部分单独给变量赋值 8 1PL SQL简介 2 变量及数据类型例 DECLAREv snoVARCHAR2 10 04001 v cnoVARCHAR2 10 001 v gradeNUMBER 90 BEGININSERTINTOSCVALUES v sno v cno v grade COMMIT END 8 1PL SQL简介 2 变量及数据类型除了常用的数据类型外 在Oracle中还有两个比较特殊的数据类型 TYPE和 ROWTYPE TYPE定义一个变量 其数据类型与已经定义的某个数据变量的类型相同 或者与数据库表的某个列的数据类型相同 这时可以使用 TYPE 使用 TYPE特性的优点在于 所引用的数据库列的数据类型可以不必知道 所引用的数据库列的数据类型可以实时改变 8 1PL SQL简介 2 变量及数据类型 ROWTYPEPL SQL提供 ROWTYPE操作符 返回一个记录类型 其数据类型和数据库表的数据结构相一致 使用 ROWTYPE特性的优点在于 所引用的数据库中列的个数和数据类型可以不必知道 所引用的数据库中列的个数和数据类型可以实时改变 8 1PL SQL简介 3 运算符与表达式一般运算符 8 1PL SQL简介 3 运算符与表达式关系运算符 8 1PL SQL简介 3 运算符与表达式逻辑运算符 8 1PL SQL简介 4 pl sql流程控制语句PL SQL的流程控制语句 包括如下三类 顺序语句 GOTO语句 无条件跳转到指定的标号NULL语句 说明 不用做任何事情 的意思 相当于一个占位符条件语句 IF语句循环语句 LOOP ENDLOOP语句WHILE语句FOR循环语句 8 1PL SQL简介 IFTHENPL SQL和SQL语句ENDIF IFTHENPL SQL和SQL语句ELSIFTHEN其它语句ELSIFTHEN其它语句ELSE其它语句ENDIF IFTHENPL SQL和SQL语句ELSE其它语句ENDIF 条件语句 declaremsc grade type beginselectgradeintomfromscwheresno 04001 andcno 005 ifm 60andm 80andm 90thendbms output put line 良好 elsedbms output put line 优秀 endif end 条件语句 8 1PL SQL简介 编写PL SQL程序 查询姓名为陈旭的学生所选所有课程的成绩 根据成绩输出成绩等级 8 1PL SQL简介 循环语句LOOP要执行的语句 EXITWHENENDLOOP WHILELOOP要执行的语句 ENDLOOP declarexnumber 2 0 beginloopx x 1 dbms output put line x的当前值为 x exitwhenx 10 endloop end 8 1PL SQL简介 declarexnumber 0 beginx 1 whilex 10loopdbms output put line x的当前值为 x x x 1 endloop end 8 1PL SQL简介 8 1PL SQL简介 循环语句FOR循环变量IN下限 上限LOOP要执行的语句 ENDLOOP 每循环一次 循环变量自动加1 跟在IN后面的数字必须是从小到大的顺序 而且必须是整数 不能是变量或表达式 可以使用EXIT退出循环 beginforxin1 10loopdbms output put line x的当前值为 x endloop end 第8章Oracle数据库编程 8 1PL SQL简介8 2游标8 3存储过程8 4触发器 1 问题提出 Selects sno sname cname gradeFroms sc cWheres sno o oandcollege 信息 SQL操作数据特点 集合操作 1 问题提出 Selects sno sname cname gradeFroms sc cWheres sno o oandcollege 信息 declareavarchar2 10 bvarchar2 20 cvarchar2 40 dnumber mnumber begina 04023 b 段希飞 c 形式与政策 d 100 m 0 if d 60 thenm m d dbms output put line a b c d m endif end SQL操作数据特点 集合操作 1 问题提出 Selects sno sname cname gradeFroms sc cWheres sno o oandcollege 信息 declareavarchar2 10 bvarchar2 20 cvarchar2 40 dnumber mnumber begina 04023 b 段希飞 c 形式与政策 d 100 m 0 if d 60 thenm m d dbms output put line a b c d m endif end SQL操作数据特点 集合操作 1 问题提出 Selects sno sname cname gradeFroms sc cWheres sno o oandcollege 信息 declareavarchar2 10 bvarchar2 20 cvarchar2 40 dnumber mnumber begina 04023 b 段希飞 c 形式与政策 d 100 m 0 if d 60 thenm m d dbms output put line a b c d m endif end SQL操作数据特点 集合操作 1 问题提出 Selects sno sname cname gradeFroms sc cWheres sno o oandcollege 信息 declareavarchar2 10 bvarchar2 20 cvarchar2 40 dnumber mnumber begina 04023 b 段希飞 c 形式与政策 d 100 m 0 if d 60 thenm m d dbms output put line a b c d m endif end SQL操作数据特点 集合操作 1 问题提出 Selects sno sname cname gradeFroms sc cWheres sno o oandcollege 信息 declareavarchar2 10 bvarchar2 20 cvarchar2 40 dnumber mnumber begina 04023 b 段希飞 c 形式与政策 d 100 m 0 if d 60 thenm m d dbms output put line a b c d m endif end SQL操作数据特点 集合操作 编程语言操作数据特点 记录操作 1 问题提出 declareavarchar2 10 bvarchar2 20 cvarchar2 40 dnumber mnumber begina 04023 b 段希飞 c 形式与政策 d 100 m 0 if d 60 thenm m d dbms output put line a b c d m endif end 如何在一段程序中处理所有的查询结果 2 问题分析 declareavarchar2 10 bvarchar2 20 cvarchar2 40 dnumber mnumber begina 04023 b 段希飞 c 形式与政策 d 100 m 0 if d 60 thenm m d dbms output put line a b c d m endif end 方案1 使用多个变量 v1 v2 v3 vn 查询结果数据量大 不断变化 不可行 2 问题分析 declareavarchar2 10 bvarchar2 20 cvarchar2 40 dnumber mnumber begina 04023 b 段希飞 c 形式与政策 d 100 m 0 if d 60 thenm m d dbms output put line a b c d m endif end 方案2 临时存储查询结果 临时存储 内存空间 3 游标概念 在PL SQL程序中 对于处理多行记录的事务经常使用游标来实现 游标是系统为用户开设的一个数据缓冲区 内存中一段连续的存储单元 存放SQL语句的执行结果 每个游标区都有一个名字 PL SQL通过游标提供了一个对结果集进行逐行处理的能力 游标可以看作一种特殊的指针 它与某个查询结果相联系 可以指向结果集的任意位置 以便对指定位置的数据进行处理 使用游标可以在查询数据的同时对数据进行处理 4 游标使用步骤 4 游标使用步骤 declareavarchar2 10 bvarchar2 20 cvarchar2 40 dnumber mnumber begina 04023 b 段希飞 c 形式与政策 d 100 m 0 if d 60 thenm m d dbms output put line a b c d m endif end 请在内存中给我分配一段连续的存储空间 我要用来存放 查询的执行结果 DeclareCursorc1isSelects sno sname cname gradeFroms sc cWheres sno o oandcollege 信息 翻译 4 游标使用步骤 declareavarchar2 10 bvarchar2 20 cvarchar2 40 dnumber mnumber cursorc1isSelects sno sname cname gradeFroms sc cWheres sno o oandcollege 信息 begina 04023 b 段希飞 c 形式与政策 d 100 m 0 if d 60 thenm m d dbms output put line a b c d m endif end 0 x00000018 0 x0000001B 0 x0000001C 0 x0000001D 0 x0000001E 0 x0000001F 0 x00000019 0 x0000001A 4 游标使用步骤 declareavarchar2 10 bvarchar2 20 cvarchar2 40 dnumber mnumber cursorc1isSelects sno sname cname gradeFroms sc cWheres sno o oandcollege 信息 begina 04023 b 段希飞 c 形式与政策 d 100 m 0 if d 60 thenm m d dbms output put line a b c d m endif end 0 x00000018 0 x0000001B 0 x0000001C 0 x0000001D 0 x0000001E 0 x0000001F 0 x00000019 0 x0000001A openc1 4 游标使用步骤 declareavarchar2 10 bvarchar2 20 cvarchar2 40 dnumber mnumber cursorc1isSelects sno sname cname gradeFroms sc cWheres sno o oandcollege 信息 beginopenc1 a 04023 b 段希飞 c 形式与政策 d 100 m 0 if d 60 thenm m d dbms output put line a b c d m endif end 4 游标使用步骤 04023 段希飞 形式与政策 100 abcd fetchc1intoa b c d 04023 段希飞 形式与政策 100 04023 段希飞 形式与政策 100 4 游标使用步骤 04023 段希飞 形式与政策 100 abcd fetchc1intoa b c d if d 60 thenm m d dbms output put line a b c d m endif 04023 段希飞 体育 65 4 游标使用步骤 abcd fetchc1intoa b c d if d 60 thenm m d dbms output put line a b c d m endif 04023 段希飞 体育 65 4 游标使用步骤 04023 段希飞 形式与政策 100 abcd loopfetchc1intoa b c d if d 60 thenm m d dbms output put line a b c d m endif endloop c1 notfound 指针指向结果集中的元组返回False 否则返回True 4 游标使用步骤 loopfetchc1intoa b c d if d 60 thenm m d dbms output put line a b c d m endif endloop exit whenc1 notfound 4 游标使用步骤 loopfetchc1intoa b c d if d 60 thenm m d dbms output put line a b c d m endif endloop exit whenc1 notfound 4 游标使用步骤 Closec1 4 游标使用步骤 declareavarchar2 10 bvarchar2 20 cvarchar2 40 dnumber mnumber cursorc1isselects sno sname cname gradefroms sc cwheres sno o oandcollege 信息 beginopenc1 loopfetchc1intoa b c d exitwhenc1 notfound if d 60 thenm m d dbms output put line a b c d m endif endloop closec1 end 4 游标使用步骤 declareavarchar2 10 bvarchar2 20 cvarchar2 40 dnumber mnumber cursorc1isselects sno sname cname gradefroms sc cwheres sno o oandcollege 信息 beginopenc1 loopfetchc1intoa b c d exitwhenc1 notfound if d 60 thenm m d dbms output put line a b c d m endif endloop closec1 end 声明游标 4 游标使用步骤 declareavarchar2 10 bvarchar2 20 cvarchar2 40 dnumber mnumber cursorc1isselects sno sname cname gradefroms sc cwheres sno o oandcollege 信息 beginopenc1 loopfetchc1intoa b c d exitwhenc1 notfound if d 60 thenm m d dbms output put line a b c d m endif endloop closec1 end 打开游标 声明游标 4 游标使用步骤 declareavarchar2 10 bvarchar2 20 cvarchar2 40 dnumber mnumber cursorc1isselects sno sname cname gradefroms sc cwheres sno o oandcollege 信息 beginopenc1 loopfetchc1intoa b c d exitwhenc1 notfound if d 60 thenm m d dbms output put line a b c d m endif endloop closec1 end 提取游标数据 打开游标 声明游标 4 游标使用步骤 declareavarchar2 10 bvarchar2 20 cvarchar2 40 dnumber mnumber cursorc1isselects sno sname cname gradefroms sc cwheres sno o oandcollege 信息 beginopenc1 loopfetchc1intoa b c d exitwhenc1 notfound if d 60 thenm m d dbms output put line a b c d m endif endloop closec1 end 关闭游标 提取游标数据 打开游标 声明游标 8 2游标 声明游标 就是定义一个游标名 以及与其相对应的SELECT语句 格式 CURSORcursor name parameter parameter ISselect statement 游标参数只能为输入参数 其格式为 parameter name IN datatype DEFAULT expression 在指定数据类型时 不能使用长度约束 如NUMBER 4 CHAR 10 等都是错误的 8 2游标 打开游标 就是执行游标所对应的SELECT语句 将其查询结果放入工作区 并且游标指针指向工作区的首部 标识游标结果集合 格式 OPENcursor name parameter value parameter value 8 2游标 推进游标指针并取当前记录 向后推动游标指针 然后将工作区中游标指针当前所指向的记录取出放入指定的输出变量中 格式 FETCHcursor nameINTO variable list record variable 8 2游标 说明 1 变量必须与SELECT语句中的目标列表达式具有一一对应关系 2 FETCH语句通常用在一个循环结构中 通过循环执行FETCH语句逐条取出结果集中的行进行处理 3 为进一步方便用户处理数据 现在一些关系数据库管理系统对FETCH语句做了扩充 允许用户向任意方向以任意步长移动游标指针 8 2游标 关闭游标 当提取和处理完游标结果集合数据后 应及时关闭游标 以释放该游标所占用的系统资源 并使该游标的工作区变成无效 不能再使用FETCH语句取其中数据 关闭后的游标可以使用OPEN语句重新打开 格式 CLOSEcursor name 8 2游标 declarev snos sno type v snames sname type cursorc2 v depts college typedefault 信息 isselectsno snamefromswherecollege v dept beginopenc2 v dept 植保 fetchc2intov sno v sname whilec2 foundloopdbms output put line v sno v sname fetchc2intov sno v sname endloop closec2 end 8 2游标 游标属性 FOUND 布尔型属性 当最近一次读记录时成功返回 则值为TRUE NOTFOUND 布尔型属性 与 FOUND相反 ISOPEN 布尔型属性 当游标已打开时返回TRUE ROWCOUNT 数字型属性 返回已从游标中读取的记录数 第8章Oracle数据库编程 8 1PL SQL简介8 2游标8 3存储过程8 4触发器 8 3存储过程 ORACLE提供可以把PL SQL程序存储在数据库中 并可以在任何地方来运行它 这样就叫存储过程或函数 过程和函数统称为PL SQL子程序 他们是被命名的PL SQL块 均存储在数据库中 并通过输入参数 输出参数或输入 输出参数与其调用者交换信息 过程和函数的唯一区别是函数通过函数名向调用者返回数据 而过程则不返回数据 8 3存储过程 创建过程语法格式 CREATE ORREPLACE PROCEDUREProcedure name argment IN INOUT Type argment IN OUT INOUT Type IS AS BEGINEXCEPTIONEND 8 3存储过程 创建过程语法格式 CREATE ORREPLACE PROCEDUREProcedure name argment IN INOUT Type argment IN OUT INOUT Type IS AS BEGINEXCEPTIONEND 8 3存储过程 创建过程语法格式 CREATE ORREPLACE PROCEDUREProcedure name argment IN INOUT Type argment IN OUT INOUT Type IS AS BEGINEXCEPTIONEND 8 3存储过程 创建过程语法格式 CREATE ORREPLACE PROCEDUREProcedure name argment IN INOUT Type argment IN OUT INOUT Type IS AS BEGINEXCEPTIONEND 8 3存储过程 创建过程语法格式 CREATE ORREPLACE PROCEDUREProcedure name argment IN INOUT Type argment IN OUT INOUT Type IS AS BEGINEXCEPTIONEND 8 3存储过程 调用存储过程 beginProcedure name parameter1 parameter2 end 创建不带参数的存储过程 例 查询学号为04001的学生姓名 性别和学院 createorreplaceprocedurep2isv snames sname type v sexs ssex type v colleges college type beginselectSname ssex collegeintov sname v sex v collegefromswheresno 04001 dbms output put line v sname v sex v college end 创建不带参数的存储过程 例 查询信息学院学生的考试成绩 列出学生的姓名 课程名和成绩 createorreplaceprocedurep3isv snames sname type v ame type v gradesc grade type cursorc1isselectSname Cname Gradefroms sc cwheres sno o oandcollege 信息 beginopenc1 loopfetchc1intov sname v cname v grade exitwhenc1 notfound dbms output put line v sname v cname v grade endloop closec1 end 创建带输入参数和输出参数的存储过程 统计指定课程的平均成绩和选课人数 将统计的结果用输出参数返回 createorreplaceprocedurep4 v o type avg gradeoutnumber totaloutnumber isbeginselectavg grade count sno intoavg grade totalfromscwherecno v cno end 创建带输入参数和输出参数的存储过程 调用p4 declareavarchar2 10 001 bnumber cnumber beginp4 a b c dbms output put line b c end 创建插入数据的存储过程 在S表中插入一条学生记录 Createorreplaceprocedurep1 v snos sno type v snames sname type v colleges college type asbegininsertintos sno sname college values v sno v sname v college commit end 创建删除数据的存储过程 删除考试成绩不及格学生的修课记录 createorreplaceprocedurep5isbegindeletefromscwheregrade 60 commit end 创建修改数据的存储过程 将指定课程的学分增加2分 createorreplaceprocedurep6 v ame type isbeginupdatecsetccredit ccredit 2wherecname v cname commit end 删除存储过程 删除存储过程 DROPPROCEDURE存储过程名 第8章Oracle数据库编程 8 1PL SQL简介8 2游标8 3存储过程8 4触发器 案例 库存表 出库表 库存量 原库存量 出库数量 案例 如何让这两个操作一起绑定执行呢 库存表 出库表 InsertInto出库表Values 11081000060 687406187 15 to date 2015 12 20 yyyy mm dd 张三 Update库存表Set库存量 库存量 15Where商品编号 687406187 1 触发器概念 触发器是一种特殊的存储过程 当某个ORACLE事件发生时系统自动地运行 所以运行触发器就叫触发 火灾 烟雾探测器 自动灭火 1 触发器概念 触发器由三个部分组成 事件 条件 动作 事件 或称为触发事件 当某个事件发生的时候就运行触发器 条件 判断是否达到指定的条件 如果没有达到条件将不执行动作 可以留空 表示直接执行动作 动作 条件通过后所要做的事情 火灾 烟雾探测器 自动灭火 事件 条件 动作 2 定义触发器 创建触发器语法格式CREATEORREPLACETRIGGERtrigger name BEFORE AFTER ON FOREACHROW WHEN 2 定义触发器 创建触发器语法格式CREATEORREPLACETRIGGERtrigger name BEFORE AFTER ON FOREACHROW WHEN 2 定义触发器 创建触发器语法格式CREATEORREPLACETRIGGERtrigger name BEFORE AFTER ON FOREACHROW WHEN 2 定义触发器 创建触发器语法格式CREATEORREPLACETRIGGERtrigger name BEFORE AFTER ON FOREACHROW WHEN 触发时序 2 定义触发器 创建触发器语法格式CREATEORREPLACETRIGGERtrigger name BEFORE AFTER ON FOREACHROW WHEN INSERTDELETEUPDATE 2 定义触发器 创建触发器语法格式CREATEORREPLACETRIGGERtrigger name BEFORE AFTER ON FOREACHROW WHEN 2 定义触发器 创建触发器语法格式CREATEORREPLACETRIGGERtrigger name BEFORE AFTER ON FOREACHROW WHEN 行触发器 2 定义触发器 创建触发器语法格式CREATEORREPLACETRIGGERtrigger name BEFORE AFTER ON FOREACHROW WHEN 3 案例分析 触发器所在的表 触发事件 触发时序 行级触发 语句级触发 行级触发 出库表 INSERT AFTER 库存表 出库表 4 案例实现 createorreplacetriggergxkcafterinserton出库表foreachrowbeginupdate库存表set库存量 whereendgxkc 商品编号 库存表 出库表 4 案例实现 当触发器被触发时 要使用被插入 更新或删除的记录中的列值 就需要用到以下两个修饰符 NEW修饰符访问操作完成后列的值 OLD修饰符访问操作完成前列的值 INSERT NULL 有效 UPDATE 有效 有效 有效 DELETE NULL 4 案例实现 createorreplacetriggergxkcafterinserton出库表foreachrowbeginupdate库存表set库存量 whereendgxkc 商品编号 库存量 new 出库数量 new 商品编号 5 触发器编写 创建触发器 实现