数据库原理和设计(oracle基础和调优)

程序结构
文件结构： 数据文件 临时文件 日志文件 控制文件 参数文件 密码文件
Oracle数据库基础
Oracle数据库基础
数据的读写过程有何不同？
用户进程 用户进程 Select 服务器进程 DBWR 数据缓存池 否 该写? 数据文件 否 有? 是 是 数据文件 读过 程 否 数据缓存池 DML 写过 程
服务器进程
有?
是
内容
Oracle数据库基础
PL/SQL
SQL优化
DB优化
PL/SQL
• PL/SQL语言 PL/SQL是Oracle提供的基于数据库的二次开发语言，用 来帮助用户在数据库中实现业务逻辑和一些日常维护管理 工作。作为一门开发语言，其组成部分理应包括： 变量类型、变量定义、赋值语句、流程控制语句、函数。 因为PL/SQL基于SQL92标准之上，所以它另外还包括 SQL语句。
Database 表空间
Tablespaces
表空间
表空间
Objects
表
索引
视图
序列
存储过程
函数
触发器
包
Segments Extents Blocks
数据段 分区 块 块
索引段 分区 块 分区
临时段
回滚段
Segment包含Extent Extent包含Block Block包含OS Block
Oracle数据库基础
系统级触发器： CREATE TRIGGER 触发器名 AFTER 系统级触发条件 ON DATABASE|SCHEMA BEGIN 处理语句; END 触发器名;
触发器中能用commit吗？
PL/SQL
（4）PL/SQL程序块
DECLARE 变量列表; BEGIN 处理语句; EXCEPTION WHEN 异常名称 THEN 异常处理语句; END;
Decode函数 和什么SQL语句 功能类似？
select tablespace_name, case when tablespace_name=‘SYSTEM’ then ‘系统表空间' else ‘用户表空间' end case from dba_tablespaces
PL/SQL
5>算数函数 函数名称
ABS(N) CEIL(N) EXP(N) FLOOR(N) LN(N) LOG(M,N) MOD(M,N) POWER(M,N) SORT(N) TRUNC(N,M) COS(N) SIN(N) TAN(N)
功能描述
得到N的绝对值 得到大于或等于N的最大整数 得到N的E的N次幂 得到小于或等于N的最小整数 得到N的自然对数 得到以M为底N的对数 得到M除以N的余数 得到M的N次幂 得到N的平方根,N>=0 得到在M位截断的N的值 得到N的余弦值 得到N的正弦值 得到N的正切值
数据库原理和设计
董肇华 2008-12-17
内容
Oracle数据库基础
PL/SQL
SQL优化
DB优化
Oracle数据库基础
• 逻辑结构
存储单元： Block Extent Segment Tablespace
数据库对象： 表 索引 序列 视图 触发器 存储过程 函数 包
Oracle数据库基础
Oracle数据库
备注
产生溢出时出现错误 表示带符号的整数 最大255个字符 最大2000个字符 最长2GB
PL/SQL
（2）变量定义 1）一般变量定义： 变量名 类型标识符 [not null]:=缺省值;
定义时 初始化
2）游标定义： CURSOR cursor_name IS select_statement;
LOOP 处理语句; EXIT WHEN <条件> END LOOP;
看一个for循环例子： for i in 1..2 loop dbms_output.put_line(i); end loop;
PL/SQL
（5）异常处理语句 EXCEPTION WHEN 异常名称 THEN 异常处理语句;
CURSOR_ALREADY_OPEN 试图打开一个已打开的光标 DUP_VAL_ON_INDEX 试图破坏一个唯一性限制 INVALID_CURSOR 试图使用一个无效的光标 INVALID_NUMBER 试图对非数字值进行数字操作 LOGIN_DENIED 无效的用户名或者口令 NO_DATA_FOUND 查询未找到数据 NOT_LOGGED_ON 还未连接就试图数据库操作 TIMEOUT_ON_RESOURCE 发生超时 TOO_MANY_ROWS SELECT INTO命令返回的多行 TRANSACTION_BACKED_OUT 由于死锁提交被退回 ZERO_DIVIDE 试图被零除
内容
Oracle数据库基础
PL/SQL
SQL优化
DB优化
SQL优化
可以理解为指向一段内存的句柄
•
SQL语句的执行步骤
生成执行计划
Create a Cursor
Parse the Statement Describe Results of a Query Define Output of a Query
PL/SQL
（6）函数 1）内部函数： 1>字符函数 函数名称
ASCII(CHAR) CHR(N) CONCAT(CHAR1,CHAR2) LPAD('123',3,'51') LPAD('123',5,'51') LPAD('123',6,'51') 结果分别是什么？
功能描述
得到字符串的第一个字符的ASCII值 得到数值N指定的字符 连接两个字符串
PL/SQL
6>合计函数
函数名称
COUNT() MAX() MIN()
功能描述
计算满足条件的记录数 对指定的列求最大值 对指定的列求最小值
AVG()
SUM()
对指定的列求平均值
計算列的和
PL/SQL
• PL/SQL程序 C语言的程序最终是一些.c文件，而PL/SQL程序的最终是 存储过程、包、触发器以及PL/SQL块，而最主要是存储 过程。
SQL优化
SQL优化
• 访问路径 （1）全表扫描（Full Table Scans, FTS） （2）通过ROWID的表存取（Table Access by ROWID） （3）索引扫描（Index Scan） 1）索引唯一扫描(index unique scan) 2）索引范围扫描(index range scan) 3）索引全扫描(index full scan) 4）索引快速扫描(index fast full scan)
LENGTH(CHAR)
LOWER(CHAR) UPPER(CHAR) LPAD(CHAR1,N,CHAR2) RPAD(CHAR1,N,CHAR2) REPLACE(CHAR1,CHAR2,CHAR3)
取一字符串CHAR的长度
将字符串CHAR全部转换为小写 将字符串CHAR全部转换为大写 用字符串CHAR2包括的字符左填CHAR1,使其长度为N 用字符串CHAR2右填字符串CHAR1,使其长度为N 将字符串CHAR1中含CHAR2的子串全部替换为CHAR3 去掉字符串CHAR左边的空格 去掉字符串CHAR右边的空格
当用到绑定变量的时候 当查询由用户交互地输入需要输出的列名
让接收变量的类型与对应列的类型匹配
Bind Any Variables Parallelize the Statement Run the Statement Fetch Rows of a Query Close the Cursor
查询语句才返回数据 当指定并行执行的时候
Objects
表
索引
视图
序列
存储过程
函数
触发器Fra Baidu bibliotek
包
Segments
数据段
索引段
临时段
回滚段 这些对象都保 存在哪里？
Oracle数据库基础
System表空间 数据字典表 数据字典表 数据字典表 数据字典表
Oracle数据库基础
• 程序结构
内存结 构： SGA PGA 进程结构： SMON PMON DBWR LGWR ARC0 CKPT
功能描述
将日期D转换为FMT格式的字符串 将字符串CHAR转换为FMT格式的日期 将字符串CHAR转换为FMT格式的时间戳 将字符转换为数值
PL/SQL
4>条件测试 函数名称
DECODE(CHAR,N1,CHAR1,N2,CHAR2...)
功能描述
CHAR的值为N1，替换为CHAR1; 为N2，替换为CHAR2，以此类推
释放内存
SQL优化
SQL优化
• 执行计划 （1）什么是执行计划 执行计划就是用户和数据库服务程序都能识别的执 行SQL语句 的顺序指令集合。 使用EXPLAIN前请执 行脚本： $ORACLE_HOME/ （2）如何得到执行计划 rdbms/admin/
utlxplan.sql
set autotrace on
IF <条件> THEN 处理语句; ELSE 处理语句; END IF;
IF <条件> THEN 处理语句; ELSIF <条件> THEN 处理语句; ELSE 处理语句; END IF;
PL/SQL
2）循环语句：
FOR 计数器 IN 下限..上限 LOOP 处理语句; WHILE <条件> END LOOP; LOOP 处理语句; END LOOP;
Select语 句可以 带条件
PL/SQL
（3）赋值语句 1）直接赋值： 变量 := 值; 2）Select赋值： Select 列名 into 变量 from 表名 where条件;
PL/SQL
（4）流程控制语句
条件语句 句
循环语
PL/SQL
1）条件语句：
IF <条件> THEN 处理语句; END IF;
得到两个日期之间的月数
得到日期D后第一个由CHAR命名的星期的日期 得到按指定的FMT模式舍入到的最近的日期 得到当前系统的日期和时间
FMT：日期格式说明符 如：’YYYY-MM-DD HH24:MI:SS’
PL/SQL
3>转换函数 函数名称
TO_CHAR(D,FMT) TO_DATE(CHAR,FMT) TO_TIMESTAMP(CHAR,FMT) TO_NUMBER(CHAR)
PL/SQL程序
存 储 过 程
包
触 发 器
PL / SQL 程 序 块
PL/SQL
（1）存储过程
CREATE PROCEDURE 存储过程名(参数列表) AS 变量定义列表; BEGIN 处理语句; END存储过程名;
参数和变量有何不同？
PL/SQL
（2）包
包头： CREATE PACKAGE 包名 AS 变量定义列表; 函数定义列表; 包体： 存储过程定义列表; CREATE PACKAGE BODY 包名 AS END包名; 函数实现; 存储过程实现; END包名;
explain plan for
dbms_system. set_sql_trace_in_session
10G的 功能
SQL优化
1）set autotrace on
附加的统 计信息
SQL优化
2）explain plan for
SQL优化
1）autotrace 2）explain plan 3）dbms_system （3）如何分析执行计划
关注一下 存储过程怎么传出游标
PL/SQL
（3）触发器 Oracle的触发器分为：系统级、表级、行级。 其中系统级主要用来实现数据库的管理和维护工 作，而其他两种主要用来实现企业的业务逻辑。
触发器
系 统 级 触 发 器
表 级 触 发 器
行 级 触 发 器
PL/SQL
表级触发器： CREATE TRIGGER 触发器名 BEFORE|AFTER INSERT| DELETE|UPDATE OF 列名 ON 表名 BEGIN 处理语句; END 触发器名; 行级触发器： CREATE TRIGGER触发器名 BEFORE|AFTERINSERT| DELETE|UPDATE OF 列名 ON 表名 FOR EACH ROW BEGIN 处理语句; END 触发器名;
变量类型 变量定义 赋值语句
PL/SQL SQL语句 流程控制语句 函数
异常处理语句
PL/SQL
（1）变量类型
变量类型
Number Int Pls_integer Binary_integer Char Varchar2 Long Boolean LOB Cursor 自定义变量类型
类型说明
数字型 整数型 整数型 整数型 定长字符型 变长字符型 变长字符型 布尔型 大对象 游标
123 51123 515123
LTRIM(CHAR) RTRIM(CHAR)
PL/SQL
2>日期函数 函数名称
ADD_MONTHS(D,N) LAST_DAY(D)
函数功能
在D日期的基础上增加N个月 得到D日期所在月份最后一天的日期
MONTH_BETWEEN(D1,D2)
NEXT_DAY(D,CHAR) ROUNT(D,FMT) SYSDATE