oracle内部培训.docx
《oracle内部培训.docx》由会员分享，可在线阅读，更多相关《oracle内部培训.docx（21页珍藏版）》请在冰豆网上搜索。
oracle内部培训
要点：
1.PL/SQL的使用规范；
2.PL/SQL的常用技巧和注意点
3.项目中整理的技巧花絮；
4.ORACLE结构简述
5.ORACLE数据库的安装使用说明；
1．PL/SQL的使用规范；
1.1对象命名规范
在mssql中，对象名可以大小写错开写，所以命名的时候比较方便，比如入库单号这个字段，可以用BillInNo,这样可读性还不错，可是到了oracle里，该方法就不灵了，因为Oracle不区分大小写，所以不可写成BILLINNO，这样可读性很差，建议写成BILL_IN_NO这样的格式；
当然如果非要区分大小写，也不是不可，我们可以加上””,比如”BillInNo”;但是这样做不推荐，因为访问该字段的时候，必须写成select”BillInNo”..，一个字也不能差，大小写一点不能错；
对于其他对象，表名，视图名，过程名，报名，触发器名…，也类似这种方法用“_”作为分割符的方法；
对于视图名，因为其调用方法和表名类似，所以最好对视图给给个前缀或后缀，否者写一个语句
SLEECT*FROMABC，你不知道这个ABC到底是表名还是视图名；
前缀或后缀名一般为v或vw,有的大写有的小写；
我个人的喜欢是用后缀,比如SELECT*FROMABC_VW;为什么用后缀呢？
因为在检索视图的时候，很多时候都用首字母检索，如果用前缀，就不便于首字母检索了；
对于过程，函数，包这3个实现业务逻辑的名字；
我建议不要使用前后缀，有的人习惯在所有的过程前加上PROC_，我觉得没必要，反而有点画蛇添足；
如果真的要加，那为了风格一致，所有对象都加前缀，表名是：
TB_ABC,视图名是：
VW_ABC,函数名是:
FUN_ABC;
因为其实在调用的时候，其实都能区分的；
比如：
调用过程：
Begin
BILL_IN_AUDI（P1=>:
P1,P2=>:
P2）;
End;
调用函数：
Begin
:
RESULT：
=BILL_IN_AUDI（:
P1,:
P2）;
End;
调用包：
Begin
BILL_IN.AUDI（:
P1,:
P2）;
End;
基本看到调用写法，就能区分不同的逻辑业务对象；
1.2备注的重要性
表,视图,字段都有备注属性，希望大家把备注都填完整了，这样便于别人迅速的了解含义，虽然借助其他文档，也可以查看表结构含义，但是很多时候由于表结构经常做“微调”，文档未必能及时同步更新，就算及时更新了文档，也未必及时的传到相关人手上，还有，有的人不喜欢看文档，所以最直接的方法，就是把这些对象的含义清楚明确的直接写在数据库对应的对象里；
还有一点，就是同一个字段，最好只有一个含义，这样可读性更好；
比如BILL_NO,一会儿是入库单号的意思，一会儿是盘点单号的意思，给开发人员带来很大的混淆；
为了检测同一个字段的含义是否一致，可以使用下面工具检查并修正：
对于视图，在sqlserver里，它是没有视图名和字段名备注的，所以对于视图的含义就必须用额外的文档保存，而oracle就这个优点，视图和表一视同仁，都有对象名和字段名备注（视图名和字段的备注好像很少人关注，甚有些人都不知道有这个功能，一些第三方工具也不是很关注）；
所以希望大家千万要使用好对象备注，当然对于procedure,function,package也要写好自己的备注；哪怕三言两语，让别人大概知道什么回事；
1.3参数变量命名规范；
其实规范很多，无所谓那个最合理，但是有一点是需要重点区分的；
在存储过程里，参数和内部变量一定要使用不同的前缀；因为程序一旦复杂了，参数和内部变量名很容易“混淆”
有的人习惯使用字段类型的前缀作为前缀，我觉得该方法不够好；
原因是：
1.字段类型太“细”了，一般我们看到变量基本就知道其类型；而且很多时候对类型也不敏感；
2.参数或内部变量不仅仅是字段类型，还有cursor,数组等，这些也需要有前缀，有可能和字段类型的前缀冲突；
下面举一个例子显示其风格：
-----------------------------------------------
--审核入库单
CREATEORREPLACEPROCEDUREAUDI_BILL_IN
（
p_bill_in_nobill_in_mt.bill_in_no%TYPE,
p_crea_per_nobill_in_mt.crea_per_no%TYPE,
p_codeOUTPLS_INTEGER,
p_msgOUTVARCHAR2
）
IS
v_cntPLS_INTEGER;
v_bill_in_typebill_in_mt.bill_in_type%TYPE;
............
当然有点规范上，更严的的把参数做了区分，比如输入参数用i_前缀，输出参数用o_前缀，输入输出用io_前缀；
不过我个人习惯凡是参数都用P_前缀（parameter），凡是变量都用v_前缀（variable）,这样最傻瓜化，不用动脑子；
还有一个关键的说明：
Pl/SQL中的参数其实是没有长度的，或者说就是最大长度，我们不能指定长度；
比如定义参数
p_msgOUTVARCHAR2;--不能定义成varchar2（200）;
这里的varchar2就相当于最大长度4000；
就算我们使用了%TYPE类型做为参数的定义，比如表abc一个字段字段avarchar2（20）;
定义参数
P_aabc.a%type;--p_a的真实类型其实还是varchar2（4000）;
Oracle为什么设计，我想可能是为了简化开发，因为参数是对外输入输出接口，都以最大的长度设置，不容易出现数据溢出的问题，所以一定要留意这一点，这是和T-SQL不一样的地方；
当然，内部变量的定义还是需要指定长度的；
这里同时也引申了一个注意点，就是有时我们在前台定义调用存储过程或其它对象的参数长度的时候，千万不要“吝啬”，尽量的定义的大些，尽可能的和oracle保持一致；
1.4尽量使用%type作为参数和变量类型；
很多人一直写T-SQL的存储过程写惯了，没留意PL/SQL这么好的功能，建议一定要用；
比如单号类型，在T-SQL里，只能定义成v_bill_typevarchar（20）;
这样定义的缺点就是把类型“钉死”了；万一以后修改表结构，把bill_type的字段类型修改成number或是其他的，这就就不会自动“跟着更新”；
所以最好写成v_bill_in_typebill_in_mt.bill_in_type%TYPE;
不过据说直接写类型，程序运行效率更好些，我想即使有效率损失，这么一点点也无所谓吧；
还有记录类型%rowtype也是个很不错的东西，否者按字段定义，就要定义几十个变量才抵一个%rowtype;
以前在T-SQL里，没有这么好的东西，只能傻傻的一个一个字段变量定义，如今可以不用这么傻了；
1.5代码格式化
很多文章都详细描述了代码格式规范，我这里不再重叙述了，给大家一个最傻瓜话的一个方法，就是用工具，而且作为开发人员，一定要用工具格式化代码，不要在“代码排版”上花费任何无谓的时间；也许有的人认为自己很“牛叉”，自己手工做“排版”，要知道代码经常修修补补，每次都要在修补的地方做手工排排版，还是挺费“时间”的，而且分神，最糟糕的是，手工排版总可能有失误的地方，很难排的一丝一毫都不差；
排版工具推荐使用pl/sqldeveloper，如：
1.6用procedure还是package
我个人更倾向于更多的使用package；这两个理论上并不冲突，不管习惯上应该有权重之分；
之所以有人很少或不用package，也许是被T-SQL害的，因为很多开发人员接触的第一个数据库都是mssql，没有接触过package；
我总结下来，package相对于procedure有这几个优点：
a.结构清晰，如果全部是过程，那么稍微复杂点的系统就会有数千个过程，感觉很乱，如果用包，可以把相关业务合并，把数千个合并成数百个，结构清晰，查阅方便；
b.命名方便，有点类似于java或C#里的namespace;特别是业务逻辑多了，没有分类，命名容易冲突
c.功能更强些，比如如果输出cursor（在前台程序一般叫DataSet），用procudure好像是不能实现的，而且嵌入java等些高级应用，package也更容易点；
d.对于很简单的相关业务点，用独立的过程感觉很浪费，比如我们做个设备跟踪的操作，假如有十个点，每个点都做很少的操作，这样就要写10个过程，每个过程就那么两三句话，这时候用package就非常合适，一个package里内嵌10个小过程，看上去比写10个独立的小过程更舒服些，关键可读性好多了，关联的业务点都放在一起了；容易维护；
e.面向对象更好，可以做到overload等一些面向对象的特性；
f.包加密方便，因为包分package和packagebody两部分，package只是申明内部函数或过程名已经全局变量；packagebody实现具体函数或过程的脚本；
所以我们加密的时候只要对packagebody加密；package留给别人查阅，让他们清楚如何在前台程序里调用这些包里的函数和过程，这是“一箭双雕”的事情；
而在独立过程或函数上，就没法两者兼顾；oracle自己内部的包也都是这种模式；
g.看看ORACLE自己，倾向于使用哪个，我们可以借鉴它的做法；
通过查看（11g的版本），发现sys用户的PROCEDURE,FUNCTION，PACKAGE的数量分别为72，96，595（嵌入的过程或函数有10344个），包的逻辑对象数量占有98.4%;只有1%多的对象是独立的过程和函数；
所以我们应该参考
1.7用varchar2还是nvarchar2
其实varcahr2也分两种，varchar2（n）和varchar2（nchar），后者在使用上和nvarchar2类似，都是支持Unicode，至于什么区别，我也不是很清楚；查看数据字典，能发现他们的不同，但是使用上好像没有明显的区别；
我个人更倾向于是使用varchar2类型，原因有两个：
1.7.1看oracle自身的使用“偏好”
当然varchar2里面也分varchar2（n）和vachar2（nchar），我以前查过，好像varchar2（nchar）只有48；
Oracle自身为什么使用的nvarchar2较少，我觉得可能有两个原因：
a.Oracle的字符集很复杂，有的时候设置不好，就容易导致nvarchar2的数据是乱码；
b.Oracle对Nvarchar2的效率优化可能没有varchar2做的好；就像我们很多时候推荐是使用pls_integer代替integer;
当然，nvarchar2的优点也是很明显的，比如定义varchar2（5），只能录入2个汉字加一个字母，nvarchar2（5）可以录入5个汉字;
1.7.2很多前台程序，对VARCAHR2支持的更好些；
我举个简单的例子
Createtablexxx（avarchar2（5）,bvarchar2（5char）,cnvarchar2（5））;
然后通过我们最常用toad工具插入数据
前台程序有点傻，把Unicode类型的长度翻倍了，所以干脆就用传统的vachar2算了，这样前台程序可以做的更人性化点；
我的oracle工具可以明确的看到B，C的长度是10；
接着我用了一款由c#开发的oracle工具，荷兰devart公司做的OraDevelperStudo测试，也得到相同效果；
2．PL/SQL的常用技巧和注意点
2.1绑定变量
这个问题是个“重中之重”的问题，对于我们这些做OLTP（联机事务处理,特点是并发多，事务短）系统的朋友来说，尤为要重视这个问题；我见过周围太多的了，都没有意识到这个问题的重要性；我自己能做到“洁身自好”，基本在项目开发的时候，不用任何硬编码，但如果合作者大量使用了硬编码，就让我“前功尽弃”；这个问题一直是我心中的痛；
什么绑定变量？
举个简单的例子:
Select*fromtab1wherf1=:
f1
这样的语句就是绑定变量的语句，:
f1对应的值可以为任何值，比如’a’,’ab’,’abc’;
无论为什么值，内部只要解析一次，
如果不用绑定变量，就是硬编码：
比如：
Select*fromtab1wherf1=’a’
Select*fromtab1wherf1=’ab’
Select*fromtab1wherf1=’abc’
这3句话，Oracle就要解析3次，而且随着f1这个字段值不断地变化，解析的次数是“无限”的；
什么是解析？
简单的说分两步：
1.判断sql语句是否合法，表或字段等信息数据字典是否正确等；
2.生成执行计划树，这个步骤是关键，就比如每天早上从家到公司上班，有很多条路线，选择一条性价比最好的路线；
如果是硬编码，每次执行SQL的时候，这个解析都得重做一遍，这个是很大的“资源浪费”；
oracle所有执行的语句都要放在服务器的一个内存里（SGA的shared_pool）,如果是绑定变量的SQL，变量无论怎么变化，它在这个内存里只占用一次，而如果是硬编码，where条件的值每变化一次，就会申请一个新的内存块存储，oracle这么做的目的是：
“好心”让你这个SQL的解析以后有机会再被用到，避免了重复解析，而硬编码机会很难有机会复用，所以把oracle的好心当做驴肝肺了；
还有更严重的问题,硬编码除了上面说的影响自己的运作速度，还影响别人运作；就像小时候上学，自己上课不想听讲，可以自己趴桌子上睡觉，那大不了是自己学不到东西，而如果大声讲话，就会影响到别人，这个是不能容忍的，硬编码这一点上是最糟糕的；因为“内存”是有大小限制的，如果硬编码申请太多，就会把“别人”那些解析好的SQL“挤走”，别人就没法复用它，需要重新解析；
同时每次申请新的内存保存SQL,也会产生一种锁，叫闩（latch）锁，是最轻量级的一种锁，但会影响并发性，在高并发的情况下，硬编码甚至会导致服务器gameover;这不是危言耸听，是TOM大师的的警世之言；
这个是TOM大师的原话：
“使用绑定变量
如果我要写一本书谈谈如何构建不可扩缩的Oracle应用，肯定会把"不要使用绑定变量"作为第一章和最后一章的标题重点强调。
这是导致性能问题的一个主要原因，也是阻碍可扩缩性的一个重要因素。
Oracle将已解析、已编译的SQL连同其他内容存储在共享池（sharedpool）中，这是系统全局区（SystemGlobalArea，SGA）中一个非常重要的共享内存结构。
第4章将详细讨论共享池。
这个结构能完成"平滑"操作，但有一个前提，要求开发人员在大多数情况下都会使用绑定变量。
如果你确实想让Oracle缓慢地运行，甚至几近停顿，只要根本不使用绑定变量就可以办到。
”
下面一幅图，展示了同一个查询，用绑定变量和硬编码两种方法对SGA中共享池（shared_pool）这块内存的影响
2.2不要动不动就用游标
在开发过程中，经常看到procedure/package里充满了一坨坨的显式游标，而且很多地方都是2，3嵌套，游标看上去是“万金油”，但最好还是在不得已的时候再用，很多初学者或者SQL经验技巧不够好的人，都喜欢在是实现业务逻辑的时候，脑子里马上想到了游标，因为游标很类似于前台程序开发中while/for循环；不过如果深入研究过oracle的运作机制后，就不会肆无忌惮的使用游标了；
我认为游标的缺点有以下几处：
a.容易有并发脏数据，游标打开后，这些记录集在一个个的循环处理的这段时间内，它所对应的表的数据可能别别人修改，也就是说，游标的数据时可能是5秒前的数据，执行一个游标的逻辑处理后，整个5秒钟内，游标的记录集的真实数据可能发生了很多变化；这样处理的逻辑就会出问题，这样的问题，并不是“程序逻辑”造成的，所以一旦出现数据问题，从“程序逻辑”上分析，是找不到原因的。
简单的说，这个就是类似“刻舟求剑”的误区，对于动态变化的东西，我们在先前的某个时间段得到的东西是不能作为依据的；
当然，对于稍微了解oracle锁机制的人，知道forupdate的处理
比如，定义一个游标select*fromtabel1where…forupdate
其实，知道在游标里使用forupdate，只能说前进了一下步，但它也不能滥用，滥用forupdate也会有很多弊端；
a.1滥用forupdate,会加大了锁的几率，对高并发系统有串行影响；
a.2有的时候forupdate使用不好，会在程序内部报错；
a.3有的时候没法加forupdate,比如稍微复杂些的sql，多表关联，带聚合函数等，就没法加上forupdate;
b.很多技巧都可以代替游标，举个简单的例子，比如要求出库100件；
仓库库存分布
货位数量
120
250
340
445
…..
这种情况，很多人都是用游标做循环去取数据；
其实，大可不必：
我们通过分析函数sum（..）over（orderby..）;
对库存分布虚拟两个个累计和列出来即可：
比如：
仓库库存分布
货位数量累加数量前一个累加数量（累加数量-当前数量）
120200
2507020
34011070（70~110之间的满足100）
445155110
…..
这样，我们就不必要做循环一条条的去判断，只要
判断累加数量<=100and前一个累加数>100即可，于是马上分析1，2，3货位都出库；1，2全出，3货位出：
出库要求（100）-前一个累计（70）=30个
Pl/sql代码可以这样写：
Insertinto出库任务
select货位,case累加数量<=100then数量else100-前一个累加数量endas出库数量from仓库库存分布where累加数量<=100and前一个累加数>100
一句话搞定的事情，何必用游标去循环慢慢折腾；就像今年春节晚会，郭冬临的小品，一句话的事情，搞那么复杂干嘛；效率低就不跟你计较了，关键是简单的程序给你写复杂了，增加了逻辑的复杂度，增加了潜在bug的可能性，容易产生并发造成的脏数据；而且会因为多了很多锁影响其他会话的执行；有百害而无一利；
当然我只是抛砖引玉，其实很多技巧都可以避免游标的时候，或减少游标嵌套的层数；
c.游标有时“高不成低不就”，我在pl/sql用的稍微复杂些的算法，比如回溯，递归等，鉴于游标只能前后滚动，而且没有想数组那样的按照行号来回跳转，所以，必须把这些记录值存在数组后才能做灵活的处理；
d.游标效率低，这个表面上到无所谓，慢就慢点，但是如果高并发，就增加了死锁的几率；
2.3写update语句的注意点；
2.3.1“进攻式”编程：
我们在写update语句的时候，有时候未必需要“真写”,
常见的例子是：
单据审核，比如把单据状态从0变成1;（0-初始1-审核2-完成）
很多人的写法是：
Begin
Select单据状态intov_单据状态from单据表where单号=’123’;
Ifv_单据状态=’1’
Then
dbms_output.put_line（‘单号123已经审核’）;
return;
Endif;
Update单据表set单据状态=’1’where单号=’123’;
dbms_output.put_line（‘单号123审核成功’）;
……………
End;
这样的写法看上去很符合人的逻辑：
先判断是否已经审核了，如果已经审核了，就不要做了，如果还没有什么，那么就打上标志；
可是漏洞很大：
还是我上面说的“刻舟求剑”；
因为这段代码主要有两句话，先查询状态，再更改状态；
但是这两句话有时间差，第二句话执行的时候，第一句话的查询结果可能发生；也就是说在高并发的情况下，该程序可能得到错误的结果；
怎么去保护呢，很自然，有人想到了:
Select单据状态intov_单据状态from单据表where单号=’123’;
这句话后面加上forupdate；的确，加上这个确实让代码“安全”了；
但是这样，如果高并发，虽然运行不会出逻辑问题，但是如果100人同时执行，执行一次需要0.1秒，那么最终全部完全的时间是10秒；因为这里是串行的，每个人执行到forupdate这里就会排队依次执行；
所以最好这样写：
begin
Update单据表set单据状态=’1’where单号=’123’and单据状态=’0’;
Ifsql%rowcount=0
then
dbms_output.put_line（‘单号123审核成功’）;
else
dbms_output.put_line（‘单号123已经审核’）;
return;
endif;
……………
End;
这种写法“一石二鸟”，一条update语句既可以作为更新，又含有了检查的功能；
通俗的说：
要更新什么，就在where里面写上它更新前应该的状态；
这样如果有100个人同时执行的时候，只有第一个人顺利执行，其他人等第一个花0.1秒提交后，全部是sql%rowcount=0不会引起锁，所以就不会有串行执行的互相等待；
这种以Update兼容select的做法叫做“进攻式”编程，这个术语，好像是有一本叫做“SQL编程艺术”里提出的；原理就是避免或减少“防御性”的判断，直接做数据的处理；好处是提供并发性，简化程序，防止脏数据；
顺便说一句：
有的人防止并发异常，自作聪明的“发明”了一种写法；
你不是怕并发异常吗？
所以他在过程的第一句就来上：
Update单据setmemo=memowhere单号=’123’；–-随便找个字段“自己”更新“自己”
这样的目的就是为了“锁住”，多个人同时执行的时候不会有数据异常；
我以前遇到一个合作伙伴的项目，每个过程前的第一句话，都来上这种“什么也不做，就是要锁你”这样的怪语句，如果真的要锁，你还不如写成;
Select…into..from单据where单号=’123’forupdate
至少这样的语句还容易理解，而你来个update…set自己=自己where..；这让人看了很“费解”（也许他不知道forupdate）；
于是我就问他为什么这样做，他还理直气壮的跟我说，这是他们团队的一个“高手”想出的“妙招”，用来防止多个人同时做一件事情发生的数据异常；我不怀疑这个高手的智商，可是这些高手难道就不能多看点书，遇到问题就会出“怪招”，还自鸣得意；
而
升级会员 