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，也可是UNIQUEOracle对DESC（降序）索引认为是基于函数的索引。序列Sequence使用序列为表的码值生成唯一的序列值聚簇Cluster视图View由一个预定义的查询构成，可看作为数据库的逻辑表。SELECT text_length FROM user_views WHERE view_name = viewnameSet long 5000SELECT text FROM user_views WHERE view_name = viewname快照Snapshot快照与视图都是基于查询结果的。但快照使用物理的存储空间，快照中的数据一般都是静态的，只有刷新时才改变。快照的目的是提供某个时间点的数据。二、数据类型CHAR(n)，255字符VARCHAR2(n)：2000字符LONG，2G字符，只能用在列SELECT列表，UPDATE语句的SET子句或INSERT语句的VALUES子句中，一个表中只能有一个LONG列，LONG列不能建索引，也不能有完整性约束，不能用于表达式。DATENUMBER(p,s)，s可以小于0，最高有效位38位RAW，255个字节，存储二进制数据LONG RAW，存储大型二进制数据第二部分 Oracle查询基本查询SELECT DISTINCT表别名列别名使用表达式数值型：+、-、*、/日期型：+、-字符串连接：|函数条件和组合查询逻辑操作符：=、!=、=范围操作符：BETWEEN值集操作符：IN、NOT IN模式匹配：LIKE _ 或 %组合逻辑连接符：AND、OR、NOT伪列实际上在表中不存在，但查询时能返回一个值。Sequence.CURRVAL和Sequence.NEXTVALSELECT sequence_name.NEXTVAL FROM dualSELECT sequence_name.CURRVAL FROM dual一旦选择了Sequence.NEXTVAL，该值便不能再重用ROWID，记录的物理存储位置ROWNUM，检索的结果数据的序列号SELECT userid,email FROM awoke WHERE rownumSELECTTO_CHAR(sysdate,YYYY-MM-DDHH24: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:01ROUND_DATE：2003-05-01 00:00:00TRUNC(date,format),按格式掩码输入日期后返回SQLSELECT TO_CHAR(sysdate,YYYY-MM-DDHH24: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:11TRUNC_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 WorldINSTR(input_string,search_string,n,m)，从字符串中的第n个字符开始查找搜索字符串底第m次出现。注意是字节数。SQLSELECT INSTR(时光软件cicro时光宽带,时光,1,2) FROM dual;结果：14LENGTH(string)，返回字符串字节长度，大小写转换LOWER(string)，UPPER(string)删除串中字符LTRIM(string,set_of_chars)，删除所有前导字符，默认为空格SQL SELECT LTRIM(ccsc,c) FROM dual;结果：scRTRIM(string,set_of_chars)，删除所有后继字符，默认为空格TRIM(string)，去掉左右空格TRIM(LEADINGTRAILINGBOTH trim_chartrim_char FROM trim_source)SQL SELECT TRIM( c FROM ccsc) FROM dual;结果：s填充串中字符LPAD(string,n,pad_chars)，在字符串左边填充上pad_chars指定的字符（不指定为空格），使其总长为nRPAD(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)，字符替换，将所有出现在搜索字符集中的字符转换成替换字符集中的相应字符。SQLSELECT 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_idSTART 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_idSTART WITH org_name = Development II;不显示某些分支在CONNECT BY子句中添加条件连接树查询与其它表Oracle不允许使用SELECT语句在树查询中连接其它表，SELECT e.emp_name,d.dept_name FROM emp e, dept dWHERE e.dept_id = d.dept_idCONNECT BY e.supervisor_emp_id = PRIOR e.emp_idSTART 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_idSTART WITH supervisor_emp_id IS NULL) e2WHERE 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查询高级技术DECODEOracle函数注：标准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,已送达,失效) orderStatusFROM orderORDER BY order_status统计GROUP BY HAVINGHAVING 子句用于将返回的数据行进行一次筛选。至少有10种商品的商户SELECT pname,count(goods.goodsid) FROM company,goodsWHERE 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_salaryFROM dept d, emp e, (SELECT dept_id, avg(salary) avg_salary FROM emp GROUP BY dept_no) gWHERE 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) OrderMonthFROM company c, order o, orderdetail odWHERE pid = pid AND o.orderstatus = 4AND o.orderid = od.orderidAND o.createdate BETWEEN 2002-01-01 AND 2002-12-31GROUP 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 odWHERE pid = pid AND o.orderstatus = 4AND o.orderid = od.orderidAND o.createdate BETWEEN 2002-01-01 AND 2002-12-31GROUP 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 customeridHASHKEY 200;创建表CREATE TABLE customer () CLUSTER customer_clu(customer_id)使用散列聚簇SELECT customer_name FROM customer WHERE customerid = 100300;单表聚簇与索引聚簇相似，但是在同一个表中。使用聚簇的优点：不用减少存取数据的Oracle块数不用索引直接存取数据码值使用较少的存储空间使用聚簇的缺点：降低插入的速度降低全表扫描的速度序列SEQUENCE序列是用来快速生成序列数的Oracle数据库对象，可为数据行提供一个系统生成的码值。使用序列可以不用等待释放锁，多个进程可以同时从该序列中获得下一个值。CREATE SEQUENCE goods_seqINSERT 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 ASSELECT panyname,a.orderedFROM company a , order bWHERE 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事务2UPDATE goods SET goodsname = PCWHERE goodsid = 100061;UPDATE orderdetail SET goodsname = PCWHERE goodsid = 100061;UPDATE orderdetail SET goodsname = NoteWHERE goodsid = 100071;UPDATE goods SET goodsname = NoteWHERE 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使用注意事项第十三部分 试题