技术开发部oracle初级.doc

技术部oracle初级培训教材目 录第一章 数据库设计基础2第一节 常用数据类型（number, date, varchar2）2第二节 表，主键，外键，索引，视图3第三节 数据操纵语言、常用PL/SQL控制结构5第四节 触发器、存储过程、游标和其它6第二章 SQL语言9第一节 SQL简介9第二节 查询SELECT 语句的使用11第三节 函数 14第四节 更新-Insert语句22第五节 更新语句-Update22第六节 删除语句Delete23第三章 PL/SQL基础知识24第一节 PL/SQL简介24第二节 创建和使用过程25第三节 创建和使用函数27第四节 创建并使用包30第五节 创建和使用触发器39第四章 oracle数据库基本维护方法46第一节 组织文件系统46第二节 规划数据库文件布局50第三节 建立参数文件52第一章 数据库设计基础前言 目标：简单介绍Oracle开发的知识，关于Oracle的安装、配置、备份、优化方面的知识，属于Oracle系统管理培训的内容，此处不进行讨论。第一节 常用数据类型（number, date, varchar2）1、varchar2：变长字符类型，格式: varchar2(n) 最大长度: 4000字节。绝对不可以用CHAR类型。因为CHAR类型是定长的，ORACLE会自在后面补空格，CHAR类型极大浪费物理空间，又使程序处理很麻烦，绝对禁止。2、number：数字类型，格式NUMBER(p,s) 数字类型 p为整数位，s为小数位；例如：number(12,2)，number(10,0) 其中number(p,0)可以写成number(p)，例如number(10)3、date：日期类型，包括日期和时间，在内部实际上按数字处理的，具有较高的速度。常用格式是yyyy-mm-dd hi24:mi:ss 或者yyyy/mm/dd hi24:mi:ss第二节 表，主键，外键，索引，视图1、 表：字段，主键，外键，索引表：本身是实际且独立存在的。是数据库存储数据的基本单元2、主键：一般地，必须给表建立主键，如基础数据、业务单据等表建立一个数字类型的主键，并使用ORACLE序列号发生器（以后称为“内部序列号”）赋值。关系表、余额、发生额表除外。3、索引：索引是数据库设计的一个极为重要的部分，索引建得好不好是区别数据库设计是业余水平还是专业水平的一个极为重要的参数。千万不要以为索引建得越多速度就越快。索引建得不好不仅不能提高速度，而且会极大降低速度系统运行的效率。在PM系统中，通常需要建索引的字段：1、 外键关联字段必须建索引。有的字段如录入人标识号可以酌情不建，但总单细目关联字段无条件必须建索引。2、 日期必须建索引。3、 操作码必须建索引（或唯一索引），名称可以建唯一索引进行唯一性约束。不能建索引的字段：建索引的字段要求该字段的值越不重复越好。另外，一个查询SQL只能用一个索引，因此如果优化查询速度必须知道ORACLE用了哪个索引，这样才能有意识地建索引进行优化。用explain plan命令可以知道SQL的运行过程，参看相关资料。做为索引的设计 ： 如果不建索引或索引建得不好，在一定数据量下，有的SQL可能等好几分钟才出来或根本出不来，但建索引后就是秒级响应。但在开发测试时，没有这么大的数据，在用户那时实施之初也不会反应，但一年后用户就会怨声载道。因此在数据库设计时就必须考虑建立索引的问题，不能等出了问题再补救。索引的副作用：l 索引会引起物理空间的开销。l 会降低插入、删除、修改记录的速度。4、 视图（view）视图是虚表。数据库只存储视图的定义，不存储对应的数据。视图用一组查询来定义，是被存储的查询。基于视图的开发：根据关系数据库的理论基础，应该尽量避免表中的数据冗余，这样在做Datawindow显示时，如果没有视图，会使Datawindow非常复杂，关联多个表。而基于视图的开发，可以简化这些设计。如图:综合统计查询单据管理视图4视图5视图3视图2视图1表n表2表1 上图想表达的意义是许多开发工作可以基于视图，而视图的开发可以基于表或另一些视图。 视图的好处：l 视图包装了一些设计，避免了重复劳动。l 视图可以基于另一视图，分解简化设计。如的集合管理，集合可以有任意多层，如果展开将是如下情形：select * from sometab a where col1 in (select col1 from sometab b where col1 in (select col1 from sometab c where col1 in ( )union ) );而用视图后就简化为：select * from sometab_v_a;而sometable_v_a又是基于sometable_v_b等的查询。化简了算法。l 视图不对应物理表，便于扩充，非常灵活。可以方便地加列，换表，还可以用decode等函数转意。最好的一个例子是在开发一半后提出所有业务单据中要加入货品产地，改了若干视图和在Datawindow加一列就解决了。 使用视图注意问题：l 视图本身有一定的时间开销，如果使用层次过多会降低查询速度。如果对速度要求较高，应使用视图直接对表操作，并进行的优化。l 关联多表的视图如果设计好可以增、删、改，设计不好就只能查询，并且必须是oracle7.3以上版本才具有此特性。在数据库设计时，最好一并进行视图设计，以后的程序设计可以大量基于视图开发。第三节 数据操纵语言、常用PL/SQL控制结构1、 数据操纵语言数据操纵语言（DML）是用户经常使用的语言，包括了数据库管理系统（DBMS）的主要功能，主要有数据查询和数据的增、删、改等功能，其中查询的表达方式是DML的主要部分。l 查询数据Selectl 插入数据Insertl 删除数据Deletel 更新数据Update2、 常用PL/SQL控制结构：if then elsif then end if loop end loop 第四节 触发器、存储过程、游标和其它1、 触发器触发器分类：DML触发器、替代触发器和系统触发器3大类DML触发器可以由DML语句（insert、delete、update）触发，通常说的触发器就是指DML触发器，在触发器中可以进行Insert、delete、update操作。替代触发器只能定义在视图上，直接对视图的插入等操纵是非法的，建立替代触发器来代替激发它的DML语句运行。系统触发器在发生如数据库启动或关闭系统事件时激发，而不是在执行DML语句时激发；也可以在DDL时激发，比如在建表时激发.DML触发器又可以分为：3种语句（Insert、Update、Delete）2种定时（之前、之后）2级（行或语句）共12中可能的触发器类型。行级触发器：对由触发语句变更的每一行激发一次。行级触发器由FOR EACH ROW字句表示。语句级触发器：在语句之前或之后触发一次。行级触发器的相关标识：行级触发器是按触发语句所处理的行激发的，在触发器内部，需要访问正在处理中的行的数据，这是通过两个相关的标识符： :old和:new来实现的触发语句标识符:old标识符:newINSERT无定义所有字段为空NULL该语句结束时将插入的值UPDATE更新前行的原始值该语句结束时将更新的值DELETE行删除前的原始值无定义所有字段为空NULL下面是一个行触发器的例子，当插入或更新员工信息时，控制员工的薪水在一定的范围之内：CREATE TRIGGER scott.salary_check BEFORE INSERT OR UPDATE OF sal, job ON scott.emp FOR EACH ROW WHEN (new.job PRESIDENT) DECLARE minsal NUMBER; maxsal NUMBER; BEGIN /* 从SAL_GUIDE 表中取的某个职务的最低和最高薪水 */ SELECT minsal, maxsal INTO minsal, maxsal FROM sal_guide WHERE job = :new.job; /* 如果员工薪水小于最小薪水或大于最大薪水，都触发错误，禁止更新 */ IF (:new.sal maxsal) THEN raise_application_error( -20601, Salary | :new.sal | out of range for job | :new.job | for employee | :new.ename ); END IF; END;2、 存储过程Oracle有两种类型的存储过程：function和procedure，两者的区别在于，function需要返回一个值，procedure不需要返回值。另外Oracle还提供了一个包（Package），可以把某一类相关的function、procedure放在一个包（package）中；ORACLE 创建存储过程的语法为：create or replace procedure 过程名参数1 in|out|in out 数据类型,参数2 in|out|in out 数据类型. is|as pl/sql 语句例如，可以建立一个存储过程，每当用户修改数据库的重要数据时，记录操作日志：create procedure update_log is begin insert into update_log_tab(use_name,update_date,operation) values(user,sysdate,update);end;然后，就可以在其它存储过程中、PB中调用此存储过程了。3、 游标（Cursor）在Oracle的存储过程中，当select的返回值是多行时，就必须使用游标。游标的使用过程：l 定义游标： cursor c_tmp is select * from pub_goods_price where priceid=9;l 打开游标：Open c_tmp;l 循环取游标中的值：fetch c_tmp into m_tmp;l 关闭游标：close c_tmp;例子：declare cursor c_tmp is select * from pub_goods_price where priceid=9; m_tmp c_tmp%rowtype; v_ccc number;begin open c_tmp; loopfetch c_tmp into m_tmp;if m_tmp%notfound thenexit;end if; select count(*) into v_ccc from pub_goods_price where priceid=10 and goodsid=m_tmp.goodsid; if v_ccc=0 then insert into pub_goods_price (priceid,goodsid,price,priceunit) values (10,m_tmp.goodsid,m_tmp.price,m_tmp.priceunit); end if; end loop; close c_tmp; commit;end;需要注意的是：一定要记住关闭游标，初学者经常会犯的错误就是忘了关闭游标，由于光标没有关闭，资源没有释放，下一次运行同一段代码时，很可能无法运行。4、 复杂查询exists, not exists：in, not in：强烈建议不要使用not in的子查询，对于not in的子查询一定要进行优化。union, union all：联合查询，两者的区别在于：union对于重复的记录会只显示一个，而union all 则不处理重复记录，将显示所有的符合条件的记录。用EXISTS替代IN 在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 例如：在业务系统查询出所有销售部门的人员使用 IN 的例子SELECT * FROM pub_employeeWHERE deptid IN (SELECT companyidFROM pub_companyWHERE deptsalesflag = 1) 由于IN 和 NOT IN的执行效率都很低，所以必须转换成使用EXISTS或NOT EXISTS的方式：SELECT * FROM pub_employeeWHERE EXISTS (SELECT x FROM pub_companyWHERE deptsalesflag = 1AND companyid = pub_emoloyee.deptid)第二章 SQL语言第一节 SQL简介1、SQL概述 SQL 的诞生于IBM 公司在加利福尼亚San Jose 的试验室中在七十年代SQL 由这里开发出来最初它们被称为结构化查询语言Structured Query Language 并常常简称为sequel 开始时它们是为IBM 公司的DB2 系列数据管理系统RDBMS 关系型数据库管理系统而开发的您在今天仍可以买到在不同平台下运行的该系统事实上是SQL造就了RDBMS 它是一种非过程语言与第三代过程语言如C 和COBOL 产生于同一时代2、 SQL有关标准 随着SQL语言应用的日益广泛，ANSI和ISO先后制定了多个SQL标准包括：SQL-86：较为简单，主要包括数据定义语言、数据操纵语言、嵌入式语法等几个部分。SQL-89：增加了对完整性约束的支持。SQL-92：也称SQL2，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。SQL-3：增加对面向对象模型的支持。3、SQL特点1）综合统一 集数据定义，数据操纵，数据查询和数据控制于一体。单一的结构-关系，带来了数据操作符的统一。2）度非过程化 用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径。3）向集合的操作方式操作的对象和操作的结果均为集合。4）以同一种语法结构提供两种使用方式 SQL既是自含式语言，又是嵌入式语言5）语言简洁、易学易用例如：SQL语言动词 SQL 功能动词数据查询SELECT数据定义CREATE,DROP,ALTER数据操纵INSERT,UPDATE,DELETE数据控制GRANT,REVOKE在SQL/PLUS中书写SELECT语句在SQL/PLUS中，可以分多行输入一个SQL语句，不同的子句应当放置在不同的行中以便阅读和修改，应当利用缩进使语句可读性更好。SQL命令对大小写不敏感，最终输入的SQL语句存放于SQL*Plus的缓冲区中执行SQL语句的方法在SQL语句的最后写一个“；”号在SQL提示符下输入一个“/”号在SQL提示符下输入RUN命令第二节 查询SELECT 语句的使用1、SELECT语句的基本语法Select ALL|DISTINCT,From ,Where Group By ,HAVING Order By , 1）SELECT语句的语义其语义是：从FROM子句给定的表中，检索出满足WHERE子句给定检索条件的记录，并根据SELECT子句从这些记录中选出指定的列（属性）所构成的结果集合。SELECT和FROM两个子句定义了投影操作。WHERE子句给出了选取操作的定义（当涉及多表操作时，WHERE子句要同时给出连接操作条件）。可见，查询语句作为整体代表着关系代数中的投影、选取、连接等操作的组合。但是，由于在查询语句中并不规定投影、选取、连接等操作的执行顺序。因此，它比关系代数更一般，更少过程化。例1、查询全体学生的姓名与学号Select Sname, Sno From Student;查询全体学生的全部属性 Select * From Student;查看全体学生的姓名及其出生年份 Select Sname, 2001-Sage From Student;查询全体学生所在学校、姓名、出生年份和所在系。Select 北航SCHOOL, Sname,2001-Sage “Birth”, Sdept AS DEPARTMENTFrom Student;查询选修了课程的学生学号 Select Distinct Sno From SC;查询学生的姓名和所在系 Select Distinct Sno, Sdetp From SC;查询计算机系全体学生的姓名Select Sname From Student where Sdept=CS;查询所有年龄在20 岁以下的学生姓名及其年龄 Select Sname From Student where Sage SELECT ID,TO_CHAR(date_ordered,MM/YY) ORDERED 2 FROM s_ord；ID ORDERED- -100 08/92105 09/92109 09/92110 09/92111 09/92SQL SELECT id，2 TO_CHAR(total, $9,999,999) TOTAL3 FROM s_ord；ID TOTAL- -107 $142,171110 $1,539111 $2,7704、聚集函数1）Count(DISTINCT|ALL |*) 求元组个数例：查询学生总数Select Count(*) From Student;例：查询各门课及其相应选课人数。Select Cno, Count(Sno) From SC Group By Cno;例：查询选课至少为三门的学生的学号及其选课门数。 Select Sno, Count(Sno) From SC Group By Sno Having Count(Sno)=3;2）Sum(DISTINCT|ALL 表达式) 对数值列求总和例：统计某单位的职工平均奖金，平均收入和总劳资花费SELECT AVG(BONUS), AVG(SALARY+NVL(BONUS,0), SUM(SALARY+NVL(BONUS,0)FROM EMP;3）Avg(DISTINCT|ALL 表达式) 求数值列的平均值例：计算1号课程的学生平均成绩Select AVG(Grade) From SC Where Cno=1;例：统计某单位的职工平均奖金，平均收入和总劳资花费SELECT AVG(BONUS), AVG(SALARY+NVL(BONUS,0), SUM(SALARY+NVL(BONUS,0)4）Max(DISTINCT|ALL 表达式) 求最大值 例： 查询选修1号课程的学生最高分数Select MAX(Grade) From SC Where Cno=1;Min(DISTINCT|ALL 表达式) 求最小值5、连接条件 连接查询中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为： . . 其中的 称为连接字段， 连接条件的各个连接字段类型必须是可比的，但不必是相同的。 主要有：= ， ，= ，90);2）嵌套查询在SQL语言中，一个SELECT-FROM-WHERE 称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。上层的查询块称为外层查询或父查询，下层查询称为内层查询或子查询。外层查询利用子查询来获取检索条件的条件值，并根据这个条件值确定外层查询的结果集合。通过子查询，可以利用一系列简单查询来构造一个复杂查询。 子查询必须用圆括号括起来，只能出现在比较运算符的右边，子查询中的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。 不相关子查询不相关子查询的执行顺序是由内向外，即每个子查询在上一级查询处理前求解，子查询的结果集合作为其外部查询的检索条件的条件值。 相关子查询相关子查询的查询条件引用了其外部查询的某个属性值，从而其执行依赖于其外部查询。其执行顺序是：首先取外部查询选取一行记录（称为候选行），然后子查询利用候选行中相关列的值查询结果数据。接着外部查询利用子查询返回的结果集合判断候选行是否满足检索条件，若满足，则把它放入外部查询的结果集合。重复上述步骤直到处理完外部查询的每一行候选行。例：查询与刘晨在同一个系学习的学生。Select Sno, Sname, Sdept From StudentWhere Sdept = (Select Sdept From Student Where Sname = 刘晨);查询选修了课程名为信息系统的学生的学号和姓名。Select Sno, Sname From StudentWhere Sno IN (Select Sno From SCWhere Cno IN (Select Cno From CourseWhere Cname = 信息系统);查询其他系中比信息系中某一学生年龄小的学生姓名和年龄。Select Sname, Sage From StudentWhere Sdept IS And Sage (SELECT AVG(Grade) From SC Where Cno=2); 3）集合查询查询选修了1号或2号课程的学生Select Sno From SC Where Cno=1UnionSelect Sno From SC Where Cno=2查询选修了1号课程的学生和选修了2号课程的学生的交集Select Sno From SCWhere Cno=1 IntersectSelect Sno From SCWhere Cno=2;查询计算机系的学生与年龄不大于19岁的学生的差集Select * from StudentWhere Sdept = CSMinusSelect * from StudentWhere Sage 19;第四节 更新-Insert语句语法：Insert Into (,) Values(,)插入子查询结果 Insert Into (,) 例：Insert Into StudentValues (95020, 陈冬, IS, 18);Insert Into SC (Sno, Cno)Values(95020, 1);Insert Into Dept_Age (Sdept, Avgage)Select Sdept, AVG(Sage) From StudentGroup By Sdept;第五节 更新语句-Update语法Update Set =, =Where 例：将学生95001的年龄改为22岁Update StudentSet Sage = 22Where Sno=95001将所有学生的年龄增加1 岁 Update Student Set Sage=Sage+1将计算机系全体学生的成绩置零。Update SC Set Grade = 0 Where CS = (Select Sdept From Student Where Student.Sno = SC.Sno)第六节 删除语句Delete语法：Delete From Where 例：删除学号为95019的学生的记录Delete From Student Where Sno=95019 删除所有学生的选课记录 Delete From SC删除计算机系所有学生的选课记录Delete From SCWhere CS = (Select Sdept From Student Where Student.Sno = SC.Sno)第三章 PL/SQL基础知识第一节 PL/SQL简介1、PL/SQL概述PL/SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对Oracle数据库进行访问。由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。除此之外，可以在Oracle数据库的某些客户端工具中，使用PL/SQL语言也是该语言的一个特点2、PL/SQL特点2.1、PL/SQL的块结构PL/SQL程序的基本结构是块。所有的PL/SQL程序都是由块组成的，这些块之间还可以相互嵌套。通常，程序中的每一块都实现一个逻辑操作，从而把不同的任务进行分割，由不同的块来实现。22、错误处理PL/SQL块中的异常处理部分是用来响应应用程序运行中遇到的错误。把程序的主体部分与错误处理部分代码相互隔离，这样，程序的结构看起来十分清晰。23、变量和类型信息在数据库与PL/SQL程序之间是通过变量进行传递的。所谓变量就是可以由程序读取或赋值的存储单元。通常，变量是在PL/SQL块的声明部分定义的。24、循环结构PL/SQL支持多种循环结构。所谓循环就是指可以重复执行的同一代码段。25、游标游标是用来处理使用SELECT语句从数据库中检索到的多行记录的工具。借助于游标的功能，数据库应用程序可以对一组记录逐个进行处理。26、过程和函数PL/SQL中的过程和函数（通称为子程序）是PL/SQL块的一种特殊类型，这种类型的子程序可以编译的形式存放在数据库中，并为后续的程序块调用。27、包PL/SQL中的子程序可以和变量与类型共同组成包。PL/SQL的包由两部分组成，即说明部分和包体。一个包可以带有多个相关的过程。第二节 创建和使用过程1、创建过程语句的语法如下所示：CREATE OR REPLACE PROCEDURE procedure_name(argumentIN|OUT|INOUT type,.argumentIN|OUT|INOUTtype)IS|ASprocedure_body其中procedure_name是要创建的过程名，argument是过程的参数名，type是关联参数的类型，proedure_body是构成该过程代码的PL/SQL块。为了修改过程的代码，首先必须将该过程撤消，然后再重建。由于这种操作已经是开发过程的标准方式，所以关键字OR REPLACE允许将撤消和重建这两步操作合并为一个操作。如果过程存在，首先撤消该过程，而不给出任何警告提示。（可以使用命令DROP PROCEDURE来撤消一个过程）如果该过程已经不存在，就可以直接创建它。如果该过程已存在而没有关键字OR REPLACE,则CREATE语句将返回一条Oracle错误信息“ORA-955:该名称已被当前对象使用”。和其他的CREATE语句一样，创建过程是一种DDL操作，因此，在过程创建前和创建后，都要执行一条隐式的COMMIT命令。这种操作可以通过使用关键字IS或AS来实现，这两个关键字是等价的。过程体是一种带有声明部分，可执行语句部分和异常部分的PL/SQL块。该声明部分是位于关键字IS或AS和关键字BEGIN之间的语句。可执行部分（该部分是必须要有的）是位于关键字BEGIN和EXCEPTION之间的语句。最后，异常部分位于关键字EXCEPTION和关键字END之间的语句。提示在过程和函数中没有使用关键字DECLARE。取而代之的是关键字IS或AS。这种语法风格是PL/SQL从Ada语言中继承下来的。Oracle形参可以有三种模式，IN，OUT或INOUT。如果没有为形参指定模式，其默认模式为IN。表2-1说明了模式间的区别模式说明模式说明IN当过程被调用时，实参的值将传入该过程。在该过程内部，形参类似PL/SQL使用的常数，即该值具有只读属性不能对其修改。当该过程结束时，控制将返回到调用环境，这时，对应的实参没有改变。Out当过程被调用时，实参具有的任何值将被忽略不计。在该过程内部，形参的作用类似没有初始化的PL/SQL变量，其值为空（NULL）。该变量具有读写属性。当该过程结束时，控制将返回调用环境，形参的内容将赋予对应的实参。Inout当该模式是模式IN和OUT的组合。当调用过程时，实参的值将被传递到该过程中。在该过程内部，形参相当于初始化的变量，并具有读写属性。当该过程结束时，控制将返回到调用环境中，形参的内容将赋予实参表21过程的结构应具有下面所示的特征：CREATE OR REPLACE PROCEDURE procedure_name parameter_list AS/* Declarative section is here */BEGIN/* Executable section is here */EXCEPTION/* Exception section is here */END procedure_name;2、例：CREATE OR REPLACE PROCEDURE AddNewStudent (p_FirstName students.first_name%TYPE,p_LastName students.last_name%TYPE,p_Major students.major%TYPE) ASBEGIN- Insert a new row in the students table. Use- student_sequence to generate the new student ID, and- 0 for current_credits.INSERT INTO students (ID, first_name, last_name,major, current_credits)VALUES (student_sequence.nextval, p_FirstName, p_LastName,p_Major, 0);END AddNewStudent;一旦创建了该过程，我们就可以从其他的PL/SQL块中对其进行调用：BEGINAddNewStudent(Zelda,Zudni