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

PL/SQL
6>合计函数
函数名称
COUNT() MAX() MIN()
功能描述
计算满足条件的记录数 对指定的列求最大值 对指定的列求最小值
AVG()
SUM()
对指定的列求平均值
計算列的和
PL/SQL
• PL/SQL程序 C语言的程序最终是一些.c文件，而PL/SQL程序的最终是 存储过程、包、触发器以及PL/SQL块，而最主要是存储 过程。
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右边的空格
数据库原理和设计
董肇华 2008-12-17
内容
Oracle数据库基础
PL/SQL
SQL优化
DB优化
Oracle数据库基础
• 逻辑结构
存储单元： Block Extent Segment Tablespace
数据库对象： 表 索引 序列 视图 触发器 存储过程 函数 包
Oracle数据库基础
Oracle数据库
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的正切值
功能描述
将日期D转换为FMT格式的字符串 将字符串CHAR转换为FMT格式的日期 将字符串CHAR转换为FMT格式的时间戳 将字符转换为数值
PL/SQL
4>条件测试 函数名称
DECODE(CHAR,N1,CHAR1,N2,CHAR2...)
功能描述
CHAR的值为N1，替换为CHAR1; 为N2，替换为CHAR2，以此类推
Select语 句可以 带条件
PL/SQL
（3）赋值语句 1）直接赋值： 变量 := 值; 2）Select赋值： Select 列名 into 变量 from 表名 where条件;
PL/SQL
（4）流程控制语句
条件语句 句
循环语
PL/SQL
1）条件语句：
IF <条件> THEN 处理语句; END IF;
程序结构
文件结构： 数据文件 临时文件 日志文件 控制文件 参数文件 密码文件
Oracle数据库基础
Oracle数据库基础
数据的读写过程有何不同？
用户进程 用户进程 Select 服务器进程 DBWR 数据缓存池 否 该写? 数据文件 否 有? 是 是 数据文件 读过 程 否 数据缓存池 DML 写过 程
Objects
表
索引
视图
序列
存储过程
函数
触发器
包
Segments
数据段
索引段
临时段
回滚段 这些对象都保 存在哪里？
Oracle数据库基础
System表空间 数据字典表 数据字典表 数据字典表 数据字典表
Oracle数据库基础
• 程序结构
内存结 构： SGA PGA 进程结构： SMON PMON DBWR LGWR ARC0 CKPT
服务器进程
有?
是
内容
Oracle数据库基础
PL/SQL
SQL优化
DB优化
PL/SQL
• PL/SQL语言 PL/SQL是Oracle提供的基于数据库的二次开发语言，用 来帮助用户在数据库中实现业务逻辑和一些日常维护管理 工作。作为一门开发语言，其组成部分理应包括： 变量类型、变量定义、赋值语句、流程控制语句、函数。 因为PL/SQL基于SQL92标准之上，所以它另外还包括 SQL语句。
得到两个日期之间的月数
得到日期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)
IF <条件> THEN 处理语句; ELSE 处理语句; END IF;
IF <条件> THEN 处理语句; ELSIF <条件> THEN 处理语句; ELSE 处理语句; END IF;
PL/SQL
2）循环语句：
FOR 计数器 IN 下限..上限 LOOP 处理语句; WHILE <条件> END LOOP; LOOP 处理语句; END LOOP;
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
释放内存
SQL优化
SQL优化
• 执行计划 （1）什么是执行计划 执行计划就是用户和数据库服务程序都能识别的执 行SQL语句 的顺序指令集合。 使用EXPLAIN前请执 行脚本： $ORACLE_HOME/ （2）如何得到执行计划 rdbms/admin/
utlxplan.sql
set autotrace on
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程序
存 储 过 程
包
触 发 器
PL / SQL 程 序 块
PL/SQL
（1）存储过程
CREATE PROCEDURE 存储过程名(参数列表) AS 变量定义列表; BEGIN 处理语句; END存储过程名;
参数和变量有何不同？
PL/SQL
（2）包
包头： CREATE PACKAGE 包名 AS 变量定义列表; 函数定义列表; 包体： 存储过程定义列表; CREATE PACKAGE BODY 包名 AS END包名; 函数实现; 存储过程实现; END包名;
内容
Oracle数据库基础
PL/SQL
SQL优化
DB优化
SQL优化
可以理解为指向一段内存的句柄
•
SQL语句的执行步骤
生成执行计划
Create a Cursor
Parse the Statement Describe Results of a Query Define Output of a Query
Decode函数 和什么SQL语句 功能类似？
select tablespace_name, case when tablespace_name=‘SYSTEM’ then ‘系统表空间' else ‘用户表空间' end case from dba_tablespaces
PL/SQL
5>算数函数 函数名称
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)
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 SQL语句 流程控制语句 函数
异常处理语句
PL/SQL
（1）变量类型
变量类型
Number Int Pls_integer Binary_integer Char Varchar2 Long Boolean LOB Cursor 自定义变量类型
类型说明