oralce plsql培训.ppt

1、Oracle PL/SQL 培训,主讲：野猪 2007/07/05,1,Oracle最新形势简介,国际领先的市场研究机构IDC在2005年3月题为2004年全球关系型数据库管理系统供应商占有率：五大供应商的初步研究结果显示强劲增长态势的第32969号报告中估计，Oracle数据库年增长率为14.5%，并以41.3%的市场占有率居市场领先地位。,2,Oracle的几个知识点,关于安装，要注意block_size设置为8K，语言要选用GBK字符集，目前oracle9i还不支持GB18030汉字集，这个汉字集支持27484个汉字，而且是国家强制性标准。 启动Oracle数据库，分为命令方式启动以及系

2、统服务中启动两种。 Oracle目录下的networkadmin目录下的listener.ora及tnsnames.ora文件。,3,PL/Sql Developer简介,一个针对oracle的开发工具 功能强大，使用方便，有很好的调试功能。 支持嵌入帮助，可以将自己的oracle文档嵌入到这个系统中，在出现错误等情况时就可以直接查询。,4,Oracle命令(1),DDL命令，数据定义语言，如以create、alter、drop、truncate开头的几组命令，这些命令不用提交。常用的有create table、alter table、create index、truncate table等。

3、 DML命令，数据操纵语言，如insert、update、delete、select，这些命令是需要提交（commit）的。 DCL命令，数据控制语言，如grant、revoke、set role等 提示：使用DDL命令之后，有可能会导致关联对象失效，需要进行无效对象重编译。,5,Oracle 命令(2),事务控制：commit，rollback，savepoint（其他：lock table、set constraint(s)、set transaction） 审计控制：audit、noaudit 系统控制：alter system 会话控制：alter session 其他语句：comme

4、nt（添加注释）、explain plan、analyze（收集统计）、validate、call,6,关于提交(Commit),提交(commit)的主要用处：保存修改，解决冲突。 需要提交的场合：使用数据库DDL命令。 提交的时机：一般情况下，我们在后台调用过程及函数时并不进行提交，而是等后台所有业务完成之后，在前台判断无误之后才进行提交，只要有发生错误，就进行回滚。,7,关于回滚(Rollback),事务控制语言，在需要时可以回滚至未提交事务的原始状态或是回退至设置save point的位置 Oracle9i之前的版本使用回滚段，Oracle9i已不再使用回滚段这个概念，而使用几个特殊的

5、表空间来完成这件工作，所以也就不能像在oracle8中那样指定某个回滚段来进行大事务的操作。,8,表空间(TableSpace),数据库系统在建立时，就已经建立多个系统表空间，如SYSTEM、TEMP、UNDOTBS等。 表空间可以由一个或是多个数据文件(data file)组成。,9,角色(Role),系统自带的一些角色：DBA、CONNECT、RESOURCE等。 一组权限的集合，一般是某些用户有相似的授权要求时，可以考虑建立角色。 使用如下语句创建:create role test;然后再对这个角色授权。,10,用户(user),创建用户：create user test identif

6、ied by test default tablespace test; 用户授权，包括授权给用户某个角色，常见的如DBA，CONNECT，RESOURCE。还有就是将自己的表或是自己可以再授权的表授权给新建的用户，权限包括all，select，update，delete等。 加上with grant option语句之后，就可以再授权给其它用户。,11,DDL之create,后面可以加table，view，index，trigger，tablespace，function，procedure，user等 view，trigger，function，procedure一般是使用create o

7、r replace 语句来引导的。 用于备份表：create table xxx as select * from ttt 创建表的注意事项，主键、索引、默认值、表结构的设计。,12,关于查询（1）,视图就是查询的一种形式。 Select 的函数 ORDER BY 按照指定列排序返回结果的子句 DISTINCT 只返回结果集合内唯一行的关键词 COUNT 返回匹配查询的数据行总数数值的函数 AVG 该函数返回指定列(数字类型)的平均值 SUM 该函数把指定的列中的数字加起来 MIN 该函数返回列中最小的非NULL值 MAX 该函数返回列中的最大值 GROUP BY 按列汇集查询函数结果的子句,

8、13,关于查询（2）,视图中不能使用order by，但可用group by代替来达到排序目的 create view a as select b1,b2 from b group by b1,b2; 查询中最好指定查询列以及where条件，在进行大表的查询时，where条件中的字段最好有索引。 假如已有视图中写的是select * fromaaa 这样的写法，在相关表(aaa)修改之后，应单独编译无效对象。,14,关于查询(3),子查询指的是在主查询的FROM子句或是Where子句中包含其它的查询语句。 一些经典的写法：select id, name from test where id i

9、n (select id from test1); 多表间更新update test a set name=(select name from test1 where id=a.id ) 假如两张表的记录条数不同，那么在这条语句之后还应加上where限制条件。,15,关于查询（4）,查询相同的记录：select name, count(*) from test group by name having count(*)1 查询两张相关表中，一张表有，另一张表没有的记录，常见的如查询有什么人已有档案，但没有照片，使用外联接技术： select a.id0000, a.xming0 from tb

10、_grjbxx a, tbgr_photo b where a.id0000=b.id0000(+) and b.id0000 is null;,16,关于查询(5),查询的差集 minus列出第一查询减去第二查询的结果，查询的列表字段要相同。 查询的并集，有两种，其一为 union， 相同查询结果合并；其二为union all 相同查询结果不合并，并列。 查询的交集 intersect 到得两个查询结果中重复部分的。,17,PL/SQL的一些规范(1),使用create or replace这样的写法 定义变量时使用公司规范，如数字入参PI开头，本地字符变量使用LS开头，变量的字段定义最好使

11、用%TYPE形式，如果是一组针对某表或是游标结果的变量，可以使用%ROWTYPE。,18,PL/SQL的一些规范(2),新建对象的名称最好能望名知义。表名前缀为业务模块简称；视图名前缀为vw；函数名前缀sf；过程名前缀sp；序列号名前缀sq；包名前缀pg，这些名称后都应加业务模块简称及相应的说明。 触发器名前缀为tr +业务模块简称，后面最好能加上时机及事件； 使用标准的抛出错误机制，查询或是进行插入、更新及删除需要使用。,19,循环(Loop),三大类型，其一为Basic loop 先执行再判断条件；其二为For loop，根据For条件执行确定次数的循环。其三是While loop 先判断

12、条件再执行。 不同点： Basic loop及While loop在循环内部都要对判断条件内的变量进行获取，For loop 不用。While loop在进入循环之前，就需要对判断条件的变量进行一次获取 。,20,游标(Cursor),分为显式游标及隐式游标。显式游标需要定义、打开游标及关闭游标。隐式游标不需要。 常用于处理批量数据，如建帐或是更新数据等。小技巧：在进行大批量操作时，最好能对已操作的数据加上标记，并在执行100行记录左右提交一次。,21,触发器（Trigger）,基于数据表，删除表时一起被删除，truncate table 时不会触发。 按事件可分为：insert、delete

13、、update 按时机可分为：before、after 结合起来用可形成6种组合。 新版本oracle支持的时机增加了一种，instead of，用于更新视图等方面，用得比较少。,22,函数(Function),特点：它只有一个返回值，并且可以很方便地调用，可以在其它过程、函数甚至视图中进行调用，在前台可以通过select sf_test(aa,bb) into xxx from dual;这样的方式调用。 要写明入参的类型及返回值的类型，必须有返回值。必须要明确写出return返回值。,23,过程(Procedure),特点：可以有多个返回值，也可以没有返回值。 注意，每个参数都可以同时为i

14、n 及out 类型，但建议最好不要同时为in及out类型，避免一些不必要的误解。不用指明return值，过程会把参数定义中定义成out的变量的当前值返回。,24,包(Package),包的定义中包括包头(说明部分)及包体，包体是可选的。如果包头中没有说明任何过程或函数的话（只有变量声明，游标，类型等），则该包体就不必存在。由于包中的所有对象在包外都是可见的，所以，这种说明方法可用来声明全局变量。 包的使用场合，使用包能提高效率，并且，在实现一些统计报表时，可以用于传入与进程相关的参数。,25,包的其他方面,包头中函数的纯度级别 WNDS: Write No Database State RND

15、S: Read No Database State WNPS: Write No Package State RNPS: Read No Package State PRAGMA RESTRICT_REFRENCES(function_name,WNDS,WNPS,WNPS,RNPS); WNDS必须存在。所以不允许使用SQL的DML语句。,26,任务(Job),任务用于定时完成一些事务，一般情况下是周期运行一个过程。 实例1：trunc(sysdate) + 7 + 780/1440，代表每个星期天的13：00运行。 实例2：trunc(sysdate) + 1 + 60/1440，代表每天

16、的1:00运行。,27,序列号(Sequence),序列号通常用于我们在表中的流水号字段，在调用xxx . NextVal 时就会得到一个唯一的流水号。这比我们用最大值加一的方式得到流水号要好的多，但缺点就是一但申请，就算不用也要跳到下一个号码，有可能得到的流水号不连续。 自动重新计数的方法。有时我们可能需要在一个周期之后重新开始计数。,28,同义词(Synonym),同义词主要是用于跨用户直接访问或是操作的情况。 在A用户授权给B用户某个表的权限之后，B用户就可以建立针对这张表的同义词，之后在使用时，就可以像使用自己的表一样使用，而不用加A.这样的所属用户前缀。 同义词分为私有同义词及公用同义词。,29,数据链路(Database link),数据链路主要用于跨数据库访问或是操作的情况。 在建立数据链路之后，在访问时就可以根据建立连接的用户的权限，对另一数据库进行访问或是操作，需要在表名之后加数据链路名。 数据链路同样分为公用数据链路及私有数据链路。,30,数据链路其他方面,在成功建立数据链路之后，使用时还可能会报错，并且数据链路给加上诸如US.ORACLE.CO