Oracle数据库开发知识点.doc

Oracle数据库开发指南
前言
参考

第一部分 基础
一、数据库对象
表空间Tablespace：存储数据库对象的容器

模式Schema

表Table
表名最长为30个字符，不能以数字开头。一个表最多254个列。
CREATE TABLE tablename AS select_statement，通过子查询建立新表
DESC tablename，查看表或其它数据库对象的定义
ALTER TABLE tablename RENAME TO new_tablename,修改表名
RENAME tablename TO new_tablename,修改表名，注意修改表名后，视图必须重建。

索引Index
保证表中数据的唯一性；减少定位和检索数据所需要的I/O操作
Oracle允许对列值创建索引，也可对表达式创建索引。所以既可是UNIQUE，也可是UNIQUE
Oracle对DESC（降序）索引认为是基于函数的索引。

序列Sequence
使用序列为表的码值生成唯一的序列值

聚簇Cluster

视图View
由一个预定义的查询构成，可看作为数据库的逻辑表。
SELECT text_length FROM user_views WHERE view_name = ‘viewname’
Set long 5000
SELECT text FROM user_views WHERE view_name = ‘viewname’

快照Snapshot
快照与视图都是基于查询结果的。但快照使用物理的存储空间，快照中的数据一般都是静态的，只有刷新时才改变。
快照的目的是提供某个时间点的数据。

二、数据类型
CHAR(n)，255字符
VARCHAR2(n)：2000字符
LONG，2G字符，只能用在列SELECT列表，UPDATE语句的SET子句或INSERT语句的VALUES子句中，一个表中只能有一个LONG列，LONG列不能建索引，也不能有完整性约束，不能用于表达式。
DATE
NUMBER(p,s)，s可以小于0，最高有效位38位
RAW，255个字节，存储二进制数据
LONG RAW，存储大型二进制数据

第二部分 Oracle查询
基本查询
SELECT DISTINCT
表别名
列别名
使用表达式
数值型：+、-、*、/
日期型：+、-
字符串连接：||
函数

条件和组合查询
逻辑操作符：=、!=、<>、<、>、<=、>=
范围操作符：BETWEEN
值集操作符：IN、NOT IN
模式匹配：LIKE ‘_’ 或 ‘%’
组合逻辑连接符：AND、OR、NOT

伪列
实际上在表中不存在，但查询时能返回一个值。
Sequence.CURRVAL和Sequence.NEXTVAL
SELECT sequence_name.NEXTVAL FROM dual
SELECT sequence_name.CURRVAL FROM dual
一旦选择了Sequence.NEXTVAL，该值便不能再重用

ROWID，记录的物理存储位置

ROWNUM，检索的结果数据的序列号
SELECT userid,email FROM awoke WHERE rownum<20 ORDER BY userid

SYSDATE,当前日期和时间
SELECT sysdate+3 FROM dual

LEVEL，查询的深度，用于Oracle高级查询的树查询

空值
IS NULL
Oracle的NVL函数，通知查询用一个值来代替出现的空值

多表连接查询
笛卡儿连接：列出关联表的全部叉积
内连接：抛弃不匹配的记录行
外联接
查询连接
UNION操作符(+)
(SELECT DISTINCT userid,email FROM awoke ) UNION (SELECT ‘libo’,’libo@’)
INTERSECT操作符(^)
(SELECT userid,email FROM awoke WHERE userid LIKE '1') INTERSECT (SELECT '1','fds' FROM dual)
MINUS操作符(-)
(SELECT DISTINCT userid,email FROM awoke) MINUS (SELECT DINSTINCT userid,email FROM awoke)

子查询和动态视图
子查询用在条件WHERE子句
动态视图用在FROM子句，类似普通表，但无法用组成动态视图的各原始表上的索引。

TRUNCATE TABLE tablename,快速删除表中所有记录，且不能回滚
Dual内部表

第二部分 Oracle函数
标准函数

转换函数
TO_CHAR(number[,format]),format类型
TO_CHAR(date,format)，’Q’为季度
TO_DATE(string,format)
TO_NUMBER(number[,format])

日期函数
ADD_MONTHS(date,number),输入日期加上指定的月数后产生的新日期，允许负数和小数
LAST_DAY(date),输入日期的所在月份的最后一天
MONTHS_BETWEEN（date1,date2）,返回两个日期之间的月份，小数部分为月中剩余部分，第二个日期若早则返回负数

NEXT_DAT(date,day),返回输入日期指定若干天后的日期,day为字符型
本周日SELECT NEXT_DAY(sysdate,'SUNDAY') FROM dual
上周五SELECT NEXT_DAY(sysdate-7,'SUNDAY') FROM dual

ROUND(date,format)，把一个日期四舍五入至最接近格式元素指定的形式。
SQL>SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS') cur_date , TO_CHAR(ROUND(sysdate,'MM'),'YYYY-MM-DD HH24:MI:SS') round_date from dual;
结果：
CUR_DATE：2003-04-26 12:00:01
ROUND_DATE：2003-05-01 00:00:00

TRUNC(date,format),按格式掩码输入日期后返回
SQL>SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS') cur_date , TO_CHAR(TRUNC(sysdate,'MM'),'YYYY-MM-DD HH24:MI:SS') trunc_date from dual;
结果：
CUR_DATE：2003-04-26 11:59:11
TRUNC_DATE：2003-04-01 00:00:00

数值函数
ABS(number)，返回绝对值
CEIL(number)，返回大于等于输入值的下一个整数
FLOOR(number)，返回小于等于输入值的最大整数
MOD(n,m)，返回n除m的模
ROUND(number,decimal_digits)，四舍五入，小数位可以为0和负数
SIGN(number)，返回输入值的符号
SQRT(number)，返回输入数据的平方根
TRUNC(number[,decimal_pluces])，在指定的小数位上把数值截断。

字符函数
字符和ASCII码转换
ASCII(character)、CHR(number)

CONCAT(string1,string2)，字符串连接函数，等同于’||’，尽可能使用’||’

INITCAP(string),将字符串中每个单词的第一个字母变成大写，其余字符为小写
SQL> SELECT INITCAP('WE'||' '||'are'||' '||'the world') FROM dual;
结果：We Are The World

INSTR(input_string,search_string[,n,[m]])，从字符串中的第n个字符开始查找搜索字符串底第m次出现。注意是字节数。
SQL>SELECT INSTR('时光软件cicro时光宽带','时光',1,2) FROM dual;
结果：14

LENGTH(string)，返回字符串字节长度，

大小写转换
LOWER(string)，UPPER(string)

删除串中字符
LTRIM(string[,set_of_chars])，删除所有前导字符，默认为空格
SQL> SELECT LTRIM('ccsc','c') FROM dual;
结果：
sc
RTRIM(string[,set_of_chars])，删除所有后继字符，默认为空格
TRIM(string)，去掉左右空格
TRIM([LEADING\TRAILING\BOTH] trim_char\trim_char FROM trim_source)
SQL> SELECT TRIM( 'c' FROM 'ccsc') FROM dual;
结果：
s

填充串中字符
LPAD(string,n[,pad_chars])，在字符串左边填充上pad_chars指定的字符（不指定为空格），使其总长为n
RPAD(string,n[,pad_chars])，在字符串右边填充。

字符排序
NLSSORT(string[,nlsparms]),对字符串按字符集中的字符序列进行排序，缺省为ASCII。

SUBSTR(string,start[,length])，子串处理

字符转换
CONVERT(char_value,target_char_set,source_char_set)，将字符串从一个字符集转换到另一个字符集
REPLCAE(string,search_string[,replace_string])，字符串替换，将所有出现在搜索字符集中的字符串转换成替换字符串。
TRANSLATE(string,search_set[,replace_set])，字符替换，将所有出现在搜索字符集中的字符转换成替换字符集中的相应字符。
SQL>
SELECT TRANSLATE('haha','abcdefghijklmnopqrstuvwxyz','bcdefghijklmnopqrstuvwxyza') FROM dual;
结果：
Ibib

单行函数
GREATEST(list of values)，返回列表中项的最大值或最近日期
LEAST(list of valuss) ，返回列表中项的最小值或最早日期
NVL(expression,replacement_value)，空值转换
NVL2(expression1, expression2, expression3)，空值转换，如果expression1非空，则返回expression2，为空则返回expression3

多行函数
AVG(expression)，平均值
COUNT(expression)，个数
MAX(expression)，最大值
MIN(expression)，最小值
SUM(expression)，求和
STDDEV(expression)，统计标准偏差
VARIANCE(expression)，统计方差

第三部分 Oracle复杂查询
递归查询
树查询
两个子句
CONNECT BY定义表中的各行的关系
START WITH定义数据行查询的初始起点

向下浏览
SELECT org_id,org_name,supervisor_org_id FROM org
CONNECT BY supervisor_org_id = PRIOR org_id
START WITH supervisor_org_id IS NULL;
PRIOR关键字指定当前选定行的supervisor_org_id必须等于前面已选择过的数据行的org_id。

向上浏览
SELECT org_id,org_name,supervisor_org_id FROM org
CONNECT BY org_id = PRIOR supervisor_org_id
START WITH org_name = ‘Development II’;

不显示某些分支
在CONNECT BY子句中添加条件

连接树查询与其它表
Oracle不允许使用SELECT语句在树查询中连接其它表，
SELECT e.emp_name,d.dept_name FROM emp e, dept d
WHERE e.dept_id = d.dept_id
CONNECT BY e.supervisor_emp_id = PRIOR e.emp_id
START WITH e.supervisor_emp_id IS NULL

但可以通过使用查询的FROM子句中的动态视图来创建树查询作为一个独立块执行连接操作。
SELECT e.emp_name,d.dept_name
FROM emp e, dept d,
(
SELECT emp_id FROM emp
CONNECT BY supervisor_emp_id = PRIOR emp_id
START WITH supervisor_emp_id IS NULL
) e2
WHERE e.emp_id = e2.emp_id AND e.dept_id = d.dept_id

外连接
对于在连接表中没有匹配行的数据，可采用外连接。外连接即在被连接的表中加入一个空行来与没有匹配行的数据进行匹配。
在WHERE子句中，在表后放置(+)表示在该表中加入一个空行。
注意，外连接的访问路径是基于单表连接条件的。
使用外连接可执行数据完整性检查，替代性能低的 IN 子查询

相关子查询
特殊的子查询，在主查询的WHERE子句中定义选择条件。相关子查询就是在其条件中引用了主查询中的表的子查询。
EXISTS条件用来判断是否有数据行满足指定的相关条件
NOT EXISTS条件与EXISTS相反。
基本原则：
可以使用简单查询就不要使用相关子查询做相同的事情
如果子查询只返回少量的数据，则相关子查询的效率不高；如果对主查询的每一个实际，相关子查询都返回大量的数据，则应采用子查询。
如果相关子查询是基于子查询表的主键，最好不用相关子查询。
在基于子查询的查询中，应使用NOT EXISTS条件，而不要用NOT IN条件，原因是该条件未使用索引。

第四部分 Oracle查询高级技术
DECODE
Oracle函数
注：
标准SQL语句中以CASE函数体现，MYSQL、SQL Server等数据库支持CASE函数，但Oracle不支持。但Oracle的DECODE函数较为灵活。
DECODE(expression,val1,trans1,val2,trans2,…valn,transn,default)
即：
IF expression == val1 THEN
RETURN trans1;
ELSE IF expression == val2 THEN
RETURN trans2;
…
ELSE IF expression == valn THEN
RETURN transn;
ELSE
RETURN default;

DECODE函数支持嵌套。
按照订单状态进行排序；1未确认，2已确认未受理，3已受理未送达，4已送达，6失效
SELECT ordered，
DECODE(order_status,’1’,’未确认’,’2’,’已确认未受理’,’3’,’已受理未送达’,’4’,’已送达’,’失效’) orderStatus
FROM order
ORDER BY order_status

统计
GROUP BY … HAVING
HAVING 子句用于将返回的数据行进行一次筛选。
至少有10种商品的商户
SELECT pname,count(goods.goodsid) FROM company,goods
WHERE pid = pid(+)
GROUP BY pname HAVING count(goods.goodsid)>=10

在动态视图中使用GROUP BY
在FROM子句中使用GROUP BY 子句，可以将组查询结果数据作为查询的选择条件。
查找工资低于本部门平均工资20％的雇员信息
SELECT
d.dept_name,e.emp_last_name,e.salary,g.avg_salary
FROM
dept d, emp e, (SELECT dept_id, avg(salary) avg_salary FROM emp GROUP BY dept_no) g
WHERE
e.dept_id = d.dept_id AND e.dept_id = g.dept_id AND e.salary<=0.8*g. avg_salary

报表矩阵
通过DECODE函数和分组子句
计算每个商户在2002年每个季度的订单成交额
SELECT pname, SUM(od.price*od.goodsnum),TO_CHAR(o.createdate,’Q’) OrderMonth
FROM company c, order o, orderdetail od
WHERE pid = pid
AND o.orderstatus = ‘4’
AND o.orderid = od.orderid
AND o.createdate BETWEEN ‘2002-01-01’ AND ‘2002-12-31’
GROUP BY pname, TO_CHAR(o.createdate,’Q’)

按矩阵形式表现上面的查询结果
SELECT pname,
SUM(DECODE(TO_CHAR(o.createdate,’Q’),’1’, (od.price*od.goodsnum),0) )“2002-1Q”,
SUM(DECODE(TO_CHAR(o.createdate,’Q’),’2’, (od.price*od.goodsnum),0) )“2002-2Q”,
SUM(DECODE(TO_CHAR(o.createdate,’Q’),’3’, (od.price*od.goodsnum),0) )“2002-3Q”,
SUM(DECODE(TO_CHAR(o.createdate,’Q’),’4’, (od.price*od.goodsnum),0) )“2002-4Q”
FROM company c, order o, orderdetail od
WHERE pid = pid
AND o.orderstatus = ‘4’
AND o.orderid = od.orderid
AND o.createdate BETWEEN ‘2002-01-01’ AND ‘2002-12-31’
GROUP BY pname

自身表连接
显示代码分类表中每个分类名称及上级分类名称
SELECT a.deptname,b.deptname FROM dept a , dept b WHERE a.fatherid = b.deptid

第五部分 数据修改操作
一次基于一个表
提交和回滚

Insert插入
插入单行
插入多行
通过与SELECT集成，可将SELECT检索出来的数据插入至表中。
INSERT INTO table_copy (tid,tname)
SELECT tid,tname FROM table_source WHERE tid LIKE ‘%aaa’

从其它表拷贝数据
CREATE TABLE table_copy AS SELECT …

Update修改
UPDATE语句也可以同INSERT语句一样使用SELECT语句

Delete删除
TRANCATE快速删除
因为删除记录要将数据存储在系统回滚段对象中，以便以后恢复时用。所以大量数据的删除将花费大量的时间。
使用TRANCATE语句可将表中数据及该表上的所有索引项全部删除。该语句不带条件，且删除后不能回滚。

第六部分 数据库对象
为何使用
简化系统功能，高效。

索引
提供唯一键码，提高性能。
索引不存储空值。
索引类型包括：唯一（PERIMARY KEY和UNIQUE约束）和非唯一索引。
任何表上都不应超过5个索引，否则插入、删除和修改操作将会很慢。

何时不用索引：
没有WHERE子句
只有少量数据行的表
被索引的列在WHERE子句的表达式或函数中
被索引的列使用了否定操作符或者与 NULL 比较（IS NULL）

聚簇CLUSTER
基于码值的存储方法，新行并不都存储在同一个Oracle块中，而是根据它们的码值存储在不同的Oracle块中，共有两类聚簇：索引聚簇和散列聚簇。

索引聚簇可根据某个共同码值将多个表的数据存储在同一个Oracle块中（表间连接最多可基于16列），如果某个应用程序经常同时存取两个表，就可以把这两个表的数据存储在数据库的相同位置。例如：订单表和订单明细表。
索引聚簇的缺点是当只访问一个表时，性能会降低。

索引聚簇的创建步骤：
创建一个聚簇，并指定用于连接两个表的列的数据类型和大小；
CREATE CLUSTER order_orderdetail_cluster(ordered VARCHAR2(16));
在该聚簇上创建索引
CREATE INDEX order_orderdetail_index ON CLUSTER order_orderdetail_cluster;
创建表，并在CREATE TABLE语句后面指明存储在该聚簇中的表和表中哪些列存储数据
CREATE TABLE order(ordered VARCHAR2(16) NOT NULL,companyid VARCHAR2(16));
CREATE TABLE orderdetail(ordered VARCHAR2(16) NOT NULL,goodsid VARCHAR2(16));

向这两个表插入数据后，两个表中的行具有相同的ROWID块。
查询下面的语句将会很快
SELECT panyid,a.ordered,b.goodsid
FROM order a,orderdetail b
WHERE a.orderid=b.orderid

散列聚簇只有一个表，类似与Hashtable，当知道码值时，使用散列聚簇可加快完全匹配速度，但如果使用码值范围和通配符，则应使用索引。
可在一个表上同时建立索引和散列聚簇。
散列聚簇的缺点是如果码值不是平均分布的，用户访问所有具有相同码值的数据行时，会降低性能。

散列聚簇的创建步骤：
创建一个散列聚簇对象，指定码列的数据类型、数据行的大小以及不同码值的个数，这些信息用于确定数据行的存储位置和为该散列聚簇分配的初始存储空间大小。
CREATE CLUSTER customer_clu (customerid VARCHAR2(16))
SIZE 1K
HASH IS customerid
HASHKEY 200;
创建表
CREATE TABLE customer (…) CLUSTER customer_clu(customer_id)

使用散列聚簇
SELECT customer_name FROM customer WHERE customerid = ‘100300’;

单表聚簇
与索引聚簇相似，但是在同一个表中。

使用聚簇的优点：
不用减少存取数据的Oracle块数
不用索引直接存取数据
码值使用较少的存储空间

使用聚簇的缺点：
降低插入的速度
降低全表扫描的速度

序列SEQUENCE
序列是用来快速生成序列数的Oracle数据库对象，可为数据行提供一个系统生成的码值。
使用序列可以不用等待释放锁，多个进程可以同时从该序列中获得下一个值。
CREATE SEQUENCE goods_seq
INSERT INTO goods (goodsid,goodsname) VALUES(goods_seq.NEXTVAL,’PC Server’)

创建序列的其它选项
INCREMENT BY，每次递增值，可以为负值，最大值为10的27次方
START WITH，从几开始
MAXVALUE，最大值
MINVALUE，最小值
CYCLE，到最大值后回到最小值
CACHE，每次缓存序列值的个数

序列的缺点：
生成序列数后，不能回滚，因此序列值可能不是连续的。

视图
视图是一个存储的SQL语句，视图不保存数据的拷贝，因此通过视图看到的数据都是最新的。
对简单视图可以进行增删改操作。
视图的列名和底层查询中被选择出来的列名是一样的。如果列名用于函数或者用在该查询SELECT部分的表达式中，则必须使用别名，并且这个别名就是通过视图看到的信息。

视图的创建
CREATE OR REPLACE VIEW view_order AS
SELECT panyname,a.ordered
FROM company a , order b
WHERE panyid = panyid
使用
SELECT * FROM view_order;

视图的优点
简化对复杂数据的访问
可以使用视图对表中数据进行及时概括。即使表中的数据改变了，视图也总能得到最新的信息。
简化编程
数据独立，由于应用程序基于视图而不是实际的表，可以获得一层数据独立性。

数据库链接
数据库链接是其它Oracle或非Oracle数据库的别名，使用数据库链接可以访问另一个数据库中的表和其它数据对象，只要在SQL语句中的远程表名后加上@，后面加上数据库链接名即可。
需要由DBA进行下层网络协议的配置。

同义词
同义词是其它数据库对象的别名。
当我们在应用程序开发时，使用同义词可避免硬编码。

第七部分 事务处理
事务是包含一组修改（增删改）工作的逻辑单位。事务要么被保存到数据库，要么被回滚。一旦事务被提交到数据库，它就不能被回滚。

锁
缺省情况下，Oracle会自动锁住INSERT、UPDATE或DELETE影响的数据行，不需要任何编码。只有受实际影响的行才被锁住，Oracle不会锁住整个表或整个数据块。
第一个获得封锁的用户可以继续工作，而其他想更新相同数据的用户必须等待该锁被释放。

SELECT FOR UPDATE
在执行INSERT、UPDATE或DELETE语句前锁住满足一定条件的数据行，这时可用SELECT FOR UPDATE语句。SELECT FOR UPDATE子句后面必须跟上一列或几列的名字；但在修改时，却没有关系。封锁只对记录一级。
如果这些行已经被锁住，则执行SELECT FOR UPDATE就会等待，导致应用无法执行。使用NO WAIT选项，那么碰到这种情况，SELECT FOR UPDATE语句会失败，而不是等待。

死锁
两个或多个进程在相互等待对方释放锁时会出现死锁问题。
事务1
事务2
UPDATE goods SET goodsname = ‘PC’
WHERE goodsid = ‘100061’;

UPDATE orderdetail SET goodsname = ‘PC’
WHERE goodsid = ‘100061’;
UPDATE orderdetail SET goodsname = ‘Note’
WHERE goodsid = ‘100071’;

UPDATE goods SET goodsname = ‘Note’
WHERE goodsid = ‘100071’;
上面两个事务出现了死锁。
避免死锁
一个简单办法，使用数据库的应用程序最好以相同顺序对表进行封锁。

DDL语句和事务
事务中的DDL语句执行后自动进行COMMIT提交处理，它之前的语句如果是修改语句将不能再回滚。

第八部分 PL/SQL介绍
PL/SQL是Orcale对SQL语言的程序化扩展。主要用在Oracle存储过程和触发器。
具体语法见PL/SQL参考资料。

第九部分 数据库约束、触发器和过程
约束
主键、外键、UNIQUE、NOT NULL、CHECK、REFERENCE(FOREIGN KEY)

软件包、存储过程、函数、触发器

第十部分 性能优化
1、 设置Oracle数据库SGA，设置共享内存
2、 游标Statement，为了优化使用共享缓冲池区中的游标区，应建立并遵从一定的编码标准，使SQL语句看上去完全相同。使用过程、函数创建可重用代码，也可保证使用少量的游标。
3、 当在线用户数大于500时，使用多线程服务器（MTS）
4、 查询优化器：基于规则和基于代价

第十一部分 基于Oracle数据库的项目开发方式

第十二部分JDBC使用注意事项

第十三部分 试题