ORACLE基础学习 企业信息化部2015 01 15 课程内容 数据库基本概念介绍 SQL程序语言 SQL语言 数据库基本概念 1 Oracle服务器 Oracle服务器中包含多种文件结构 进程结构和内存结构 但是 处理SQL语句时 并非所有这些结构都会用到 某些结构用于提高数据库的性能 确保该数据库在遇到软件或硬件错误时可以恢复 或者执行维护该数据库所需的其它任务 Oracle服务器包括一个Oracle例程和一个Oracle数据库 Oracle例程 Oracle例程是后台进程和内存结构的组合 只有启动例程后 才能访问数据库中的数据 每次启动例程时 会分配系统全局区 SGA 并启动Oracle后台进程 后台进程代表调用进程执行各种功能 它们把为每个用户运行的多个Oracle程序所处理的功能统一起来 后台进程执行输入 输出 I O 并监视其它Oracle进程来提高并行性 从而使性能和可靠性更加优越 数据库基本概念 2 Oracle数据库 Oracle数据库包含操作系统文件 也称为数据库文件 这些文件为数据库信息提供了实际的物理存储 数据库文件用于确保数据一致性并能在例程失败时得以恢复 表空间 是实际存储数据的地方 数据库存储分配就是表空间的定义过程 因此表空间的设计对数据库物理设计极为重要 其参数设定好坏对数据库的性能及维护影响巨大 数据库基本概念 3 表 表是由确定的列数和可变的行数组成的逻辑结构 是存放数据的基本单元 数据库基本概念 4 视图 视图提供了在一个表或多个表中查看数据的替代方法 它是表上的一个动态窗口 视图并没有存储数据 它只是将一个查询结果集表示成一个虚拟表 索引 索引是为了加快查询的速度而在表上创建的对象 课程内容 数据库基本概念介绍 SQL程序语言 SQL语言 SQL数据类型 SQL语言 SQL语言 创建表 使用CREATETABLE语句创建表 需指定表名 列名和数据类型 可通过tablespace指定表空间 SQL语言 创建表 CREATE建表示例 比较常用的建表方法 改为 ulocal tbs SQL语言 创建视图 使用CREATEVIEW语句创建视图 以限制对表的数据访问或代替常用的SQL查询语句视图查询方式 SQL语言 修改表 SQL语言 修改表 SQL语言 删除表 在ORACLE10版本以后 如果DROP不带PURGE选项 则会把表先放在回收站DROPTABLEDEPTPURGE SELECT FROMUSER RECYCLEBIN PURGETABLEDEPT SQL语言 RENAME 要更改表 视图 序列或同义词的名称 可以执行RENAME语句 RENAME的另一种写法 SQL语言 注释 可以使用COMMENT语句向表或列添加备注 SQL语言 约束 常用的约束有四种 非空约束 主键约束 外键约束和唯一性约束 SQL语言 约束 非空约束 为表的列添加非空约束后 再为该表添加数据时就不可以插入空置了 主键约束 主键约束在一个表中 只能定义一个 定义为主键约束的可以是列或者列的组合 其中不能包含重复值 并且不能包含NULL值 外键约束 是让两个表通过外键建立关系 在使用外键约束时 被引用的列应该具有主键约束或者具有唯一性约束 唯一性约束 用来保证表中的某一列或者表中的某几列组合起来不重复的一种手段 SQL语言 约束 除了在表创建时 定义约束关系 也可以在表创建后建立或取消约束关系 语句如下 SQL语言 插入数据 使用INSERT语句为表添加数据 SQL语言 插入数据 更新数据 更新数据 删除数据 查询语句 Subselect语法 Select cause 查询语句 选择列 查询语句 选择行 查询语句 选择行 查询语句 选择行 可以使用AND和OR来指定任意多个查询条件 查询语句 谓词 查询语句 谓词 查询语句 谓词 查询语句 排序 查询语句 去除重复行 查询语句 运算次序 查询语句的运算次序如下 一个子句的输出是下一个子句的输入 只有运算次序在后面的子句才能引用运算次序在前面的子句 查询语句 给表达式命名 查询语句 子查询 查询语句 分组函数 分组函数对列中的一组值进行运算以得到单个结果值 查询语句 数学函数 数学函数 查询语句 字符函数 字符函数 查询语句 日期函数 日期函数 查询语句 转换函数 转换函数 查询语句 字符串使用单引号 如 Clerk ORDERBY指定多列时 先按第一列排序 对于第一列相同的行 再按第二列排序 不同列可以指定不同的排序 如 SELECTDISTINCTDEPT JOBFROMSTAFFWHEREDEPT 30ORDERBYDEPTASC JOBDESC 查询语句 多个搜索条件 SELECTDEPT NAME JOBFROMSTAFFWHEREJOB Clerk AND DEPT 20ORDEPT 21 查询语句 分组 将WHERE子句与GROUPBY子句一起使用 查询语句 分组 在GROUPBY子句之后使用HAVING子句 查询语句 标量全查询 标量全查询返回一行 且该行只有一个值 查询语句 条件表达式 查询语句 条件表达式 查询语句 相关子查询 查询语句 集合运算 集合运算的查询语句必须要有相同的数据类型和列数 查询语句 集合运算 查询语句 集合运算 INTERSECT相当于取两个集合的交集 MINUS相当于取两个集合的差集 就是 去同留异 查询语句 连接 查询语句 连接 查询语句 连接 查询语句 连接 查询语句 连接 查询语句 层次查询 当表具有层次结构数据时 通过使用层次查询可以更直观的显示数据结果 并显示其数据之间的层次关系 STARTWITH 用于指定层次查询的根行CONNECTBY 用于指定父行和子行之间的关系 在其后的表达式中 必须使用PRIOR引用父行 课程内容 数据库基本概念介绍 SQL程序语言 SQL语言 PL SQL支持的流程控制结构 条件控制IF语句CASE语句循环控制LOOP循环WHILE循环FOR循环顺序控制GOTO语句NULL语句 PL SQL支持的流程控制结构 流程控制结构 条件控制 IF语句根据条件执行一系列语句 有三种形式 IF THEN IF THEN ELSE和IF THEN ELSIF CASE语句用于根据单个变量或表达式与多个值进行比较执行CASE语句前 先计算选择器的值 流程控制结构 条件控制 流程控制结构 条件控制 LOOP 无条件循环WHILE 根据条件循环FOR 循环固定的次数循环退出控制命令 EXIT 流程控制结构 条件控制 GOTO语句 无条件地转到标签指定的语句因为GOTO语句会增加程序的复杂性 并且使得程序的可读性变差 一般不建议使用 NULL语句 什么也不做的空语句通常用在异常语句中 对于发生的异常生么都不做的时候可以加一句NULL 动态SQL 动态SQL是指在PL SQL程序执行时生成的SQL语句编译程序对动态SQL不做处理 而是在程序运行时动态构造语句 对语句进行语法分析并执行DDL语句命令和会话控制语句不能在PL SQL中直接使用 但是可以通过动态SQL来执行执行动态SQL的语法 EXECUTEIMMEDIATEdynamic sql string INTOdefine variable list USINGbind argument list 异常 在运行程序时出现的错误叫做异常发生异常后 语句将停止执行 控制权转移到PL SQL块的异常处理部分异常有两种类型 预定义异常 当PL SQL程序违反Oracle规则或超越系统限制时隐式引发用户定义异常 用户可以在PL SQL块的声明部分定义异常 自定义的异常通过RAISE语句显式引发 异常 ORACLE常用的异常 异常 在异常发生时 可以使用ORACLE内置的两个变量 SQLCODE获取异常错误号 SQLERRM获取异常错误消息 在程序中总会有未预料到的异常 故要在异常处理部分最后加上WHENOTHERS增强程序的健壮性 执行PL SQL程序 内存单元 保存到游标中 一次处理一行 检索行 提取行 游标简介 逐行处理查询结果 以编程的方式访问数据游标的类型 隐式游标 REF游标 显式游标 在PL SQL程序中执行DMLSQL语句时自动创建隐式游标 显式游标用于处理返回多行的查询 REF游标用于处理运行时才能确定的动态SQL查询的结果 游标类型 游标简介 游标简介 在PL SQL中使用DML语句时自动创建隐式游标隐式游标自动声明 打开和关闭 其名为SQL通过检查隐式游标的属性可以获得最近执行的DML语句的信息隐式游标的属性有 FOUND SQL语句影响了一行或多行时为TRUE NOTFOUND SQL语句没有影响任何行时为TRUE ROWCOUNT SQL语句影响的行数 ISOPEN 游标是否打开 始终为FALSE 隐式游标 SQL SETSERVEROUTPUTONSQL BEGINUPDATEprodSETprice 270WHEREpid 0005 IFSQL FOUNDTHENDBMS OUTPUT PUT LINE 数据已修改 ENDIF END 游标简介 游标简介 隐式游标 循环游标用于简化游标处理代码当用户需要从游标中提取所有记录时使用循环游标的语法如下 FORINLOOPENDLOOP 游标简介 显式游标在PL SQL块的声明部分定义查询 该查询可以返回多行显式游标的操作过程 数据库 打开游标 提取行 变量 关闭游标 显式游标 游标简介 带参数的显式游标 声明显式游标时可以带参数以提高灵活性声明带参数的显式游标的语法如下 CURSOR ISselect statement 游标简介 使用显式游标更新行2 1 允许使用游标删除或更新活动集中的行声明游标时必须使用SELECT FORUPDATE语句CURSORISSELECTstatementFORUPDATE UPDATESETWHERECURRENTOF 程序 子程序 命名的PL SQL块 编译并存储在数据库中 子程序的各个部分 声明部分可执行部分异常处理部分 可选 子程序的分类 过程 执行某些操作函数 执行操作并返回值 过程 创建过程的语法 CREATE ORREPLACE PROCEDURE IS ASBEGIN EXCEPTION END 程序 程序 过程 过程参数的三种模式 IN用于接受调用程序的值默认的参数模式OUT用于向调用程序返回值INOUT用于接受调用程序的值 并向调用程序返回更新的值 程序 函数 函数是可以返回值的命名的PL SQL子程序 创建函数的语法 CREATE ORREPLACE FUNCTION param1 param2 RETURNIS AS localdeclarations BEGINExecutableStatements RETURNresult EXCEPTIONExceptionhandlers END 过程和函数的比较 程序 程序 程序包 程序包是对相关过程 函数 变量 游标和异常等对象的封装程序包由规范和主体两部分组成 声明程序包中公共对象 包括类型 变量 常量 异常 游标规范和子程序规范等 声明程序包私有对象和实现在包规范中声明的子程序和游标 程序包 规范 主体 程序 创建程序包 程序包规范CREATE ORREPLACE PACKAGEpackage nameIS AS Publicitemdeclarations Subprogramspecification END package name 程序包主体CREATE ORREPLACE PACKAGEBODYpackage nameIS AS Privateitemdeclarations Subprogrambodies BEGINInitialization END package name 系统视图 V SESSIONV VERSIONALL JOBSALL ALL TABLESALL OJBECTSALL COL COMMENTSALL IND COLUMN