青云Oracle

青云的oracle开发经验谈

做了很多年的oracle数据库开发，主要精力在pl/sql上，有些不成熟的经验想和大家分享， 有什么错误欢迎指正：daizhicun@126.com

要点：

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 * FROM ABC ，你不知道这个ABC到底是表名还是视图名； 前缀或后缀名一般为v 或vw,有的大写有的小写；

我个人的喜欢是用后缀 ,比如 SELECT * FROM ABC_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,数组等，这些也需要有前缀，有

可能和字段类型的前缀冲突；

下面举一个例子显示其风格：

----------------------------------------------- --审核入库单

CREATE OR REPLACE PROCEDURE AUDI_BILL_IN (

p_bill_in_no bill_in_mt.bill_in_no%TYPE, p_crea_per_no bill_in_mt.crea_per_no%TYPE, p_code OUT PLS_INTEGER, p_msg OUT VARCHAR2 )

IS

v_cnt PLS_INTEGER;

v_bill_in_type bill_in_mt.bill_in_type%TYPE; ............

当然有点规范上，更严的的把参数做了区分，比如输入参数用i_前缀，输出参数用o_前缀，输入输出用io_前缀；

不过我个人习惯凡是参数都用P_前缀(parameter)，凡是变量都用v_前缀(variable),这样最傻瓜化，不用动脑子；

还有一个关键的说明：

Pl/SQL中的参数其实是没有长度的，或者说就是最大长度，我们不能指定长度；

比如定义参数

p_msg OUT VARCHAR2 ; --不能定义成varchar2(200) ; 这里的 varchar2就相当于最大长度4000；

就算我们使用了%TYPE类型做为参数的定义，比如表abc一个字段字段 a varchar2(20); 定义参数

P_a abc.a%type; -- p_a 的真实类型其实还是varchar2(4000);

Oracle为什么设计，我想可能是为了简化开发，因为参数是对外输入输出接口，都以最大的长度设置，不容易出现数据溢出的问题，所以一定要留意这一点，这是和T-SQL不一样的地方；

当然，内部变量的定义还是需要指定长度的；

这里同时也引申了一个注意点，就是有时我们在前台定义调用存储过程或其它对象的参数长度的时候，千万不要“吝啬”，尽量的定义的大些，尽可能的和oracle保持一致；

1.4尽量使用%type作为参数和变量类型；

很多人一直写T-SQL的存储过程写惯了，没留意PL/SQL这么好的功能，建议一定要用； 比如 单号类型，在T-SQL里，只能定义成 v_bill_type varchar(20);

这样定义的缺点就是把类型“钉死”了；万一以后修改表结构，把bill_type的字段类型修改

成number或是其他的，这就就不会自动“跟着更新”；

所以最好写成 v_bill_in_type bill_in_mt.bill_in_type%TYPE;

不过据说直接写类型，程序运行效率更好些，我想即使有效率损失，这么一点点也无所谓吧；

还有记录类型 %rowtype也是个很不错的东西，否者按字段定义，就要定义几十个变量才抵一个%rowtype;

以前在T-SQL里，没有这么好的东西，只能傻傻的一个一个字段变量定义，如今可以不用这么傻了；

1.5代码格式化

很多文章都详细描述了代码格式规范，我这里不再重叙述了，给大家一个最傻瓜话的一个方法，就是用工具，而且作为开发人员，一定要用工具格式化代码，不要在“代码排版”上花费任何无谓的时间；也许有的人认为自己很“牛叉”，自己手工做“排版”，要知道代码经常修修补补，每次都要在修补的地方做手工排排版，还是挺费“时间”的，而且分神，最糟糕的是，手工排版总可能有失误的地方，很难排的一丝一毫都不差；

排版工具推荐使用 pl/sql developer，如：

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和package body两部分，package 只是申明内部

函数或过程名已经全局变量；package body 实现具体函数或过程的脚本； 所以我们加密的时候只要对package body加密；package留给别人查阅，让他们清楚如何在前台程序里调用这些包里的函数和过程，这是“一箭双雕”的事情；

而在独立过程或函数上，就没法两者兼顾；oracle自己内部的包也都是这种模式；

g. 看看ORACLE自己，倾向于使用哪个，我们可以借鉴它的做法；

通过查看（11g的版本），发现sys用户的PROCEDURE, FUNCTION，PACKAGE的数

量分别为72，96，595(嵌入的过程或函数有10344个)，包的逻辑对象数量占有98.4%;只有1%多的对象是独立的过程和函数； 所以我们应该参考

我曾经查看了一个朋友的ebs(oracle公司的erp产品，类似SAP)系统的数据库,其各种对象的分布是：

PACKAGE[18627]

PACKAGE BODY[18404] SYNONYM[16986] VIEW[13122] TRIGGER[1895] JAVA CLASS[1076] INDEX[519] TABLE[313] TYPE[196] SEQUENCE[58] JAVA RESOURCE[39] FUNCTION[16] QUEUE[8] TYPE BODY[5] PROCEDURE[4]

LOB[3]

其中package有1.8万多个，如果每个Package平均有10个procedure或function 那么将有18万左右的业务功能点；让我奇怪的是table少了，只有300多张，而view竟有1.8多个； 至于独立的procedure/function ,ebs机会没用。

1.7用varchar2 还是nvarchar2

其实varcahr2也分两种，varchar2(n) 和 varchar2(n char) ，后者在使用上和nvarchar2类似，都是支持Unicode，至于什么区别，我也不是很清楚；查看数据字典，能发现他们的不同，但是使用上好像没有明显的区别；

我个人更倾向于是使用varchar2类型，原因有两个：

1.7.1看oracle自身的使用“偏好”

当然varchar2里面也分varchar2(n) 和 vachar2(n char)，我以前查过，好像varchar2(n

char)只有48；

Oracle自身为什么使用的nvarchar2较少，我觉得可能有两个原因： a. Oracle的字符集很复杂，有的时候设置不好，就容易导致nvarchar2的数据是乱码；

b. Oracle对Nvarchar2的效率优化可能没有varchar2做的好；就像我们很多时候推

荐是使用pls_integer 代替integer;

当然，nvarchar2的优点也是很明显的，比如定义varchar2(5)，只能录入2个汉字加一个字母，nvarchar2(5)可以录入5个汉字;

1.7.2很多前台程序，对VARCAHR2支持的更好些；

我举个简单的例子

Create table xxx ( a varchar2(5), b varchar2(5 char),c

nvarchar2(5));

然后通过我们最常用toad工具插入数据

前台程序有点傻，把Unicode类型的长度翻倍了，所以干脆就用传统的vachar2算了，这样前台程序可以做的更人性化点；

我的oracle工具可以明确的看到B，C的长度是10；

接着我用了一款由c#开发的oracle工具，荷兰devart公司做的OraDevelper Studo 测试，也得到相同效果；

2．PL/SQL 的常用技巧和注意点

2.1绑定变量

这个问题是个“重中之重”的问题，对于我们这些做OLTP(联机事务处理,特点是并发多，事务短)系统的朋友来说，尤为要重视这个问题；我见过周围太多的了，都没有意识到这个问题的重要性；我自己能做到“洁身自好”，基本在项目开发的时候，不用任何硬编码，但如果合作者大量使用了硬编码，就让我“前功尽弃”；这个问题一直是我心中的痛； 什么绑定变量？举个简单的例子:

Select * from tab1 wher f1=:f1

这样的语句就是绑定变量的语句，:f1对应的值可以为任何值，比如’a’,’ab’,’abc’; 无论为什么值，内部只要解析一次， 如果不用绑定变量，就是硬编码： 比如：

Select * from tab1 wher f1=’a’

Select * from tab1 wher f1=’ab’ Select * from tab1 wher f1=’abc’

这3句话，Oracle就要解析3次，而且随着f1这个字段值不断地变化，解析的次数是“无限”的；

什么是解析？简单的说分两步：

1. 判断sql语句是否合法，表或字段等信息数据字典是否正确等； 2. 生成执行计划树，这个步骤是关键，就比如每天早上从家到公司上班，

有很多条路线，选择一条性价比最好的路线； 如果是硬编码，每次执行SQL的时候，这个解析都得重做一遍，这个是很大的“资源浪费”； oracle 所有执行的语句都要放在服务器的一个内存里(SGA的shared_pool), 如果是绑定变量的SQL，变量无论怎么变化，它在这个内存里只占用一次，而如果是硬编码，where条件的值每变化一次，就会申请一个新的内存块存储，oracle这么做的目的是：“好心”让你这个SQL的解析以后有机会再被用到，避免了重复解析，而硬编码机会很难有机会复用，所以把oracle的好心当做驴肝肺了； 还有更严重的问题,硬编码除了上面说的影响自己的运作速度，还影响别人运作；就像小时候上学，自己上课不想听讲，可以自己趴桌子上睡觉，那大不了是自己学不到东西，而如果大声讲话，就会影响到别人，这个是不能容忍的，硬编码这一点上是最糟糕的；因为“内存”是有大小限制的，如果硬编码申请太多，就会把“别人”那些解析好的SQL“挤走”，别人就没法复用它，需要重新解析；

同时每次申请新的内存保存SQL,也会产生一种锁，叫闩(latch)锁，是最轻量级的一种锁，但会影响并发性，在高并发的情况下，硬编码甚至会导致服务器game over;这不是危言耸听，是TOM大师的的警世之言；

这个是TOM大师的原话：“使用绑定变量

如果我要写一本书谈谈如何构建不可扩缩的Oracle应用，肯定会把\不要使用绑定变量\作为第一章和最后一章的标题重点强调。这是导致性能问题的一个主要原因，也是阻碍可扩缩性的一个重要因素。Oracle将已解析、已编译的SQL连同其他内容存储在共享池（shared pool）中，这是系统全局区（System Global Area ，SGA）中一个非常重要的共享内存结构。第4章将详细讨论共享池。这个结构能完成\平滑\操作，但有一个前提，要求开发人员在大多数情况下都会使用绑定变量。如果你确实想让Oracle缓慢地运行，甚至几近停顿，只要根本不使用绑定变量就可以办到。 ”

下面一幅图，展示了同一个查询，用绑定变量和硬编码两种方法对SGA中共享池(shared_pool)这块内存的影响

2.2不要动不动就用游标

在开发过程中，经常看到procedure/package里充满了一坨坨的显式游标，而且很多地方都是2，3嵌套，游标看上去是“万金油”，但最好还是在不得已的时候再用，很多初学者或者SQL经验技巧不够好的人， 都喜欢在是实现业务逻辑的时候，脑子里马上想到了游标，因为游标很类似于前台程序开发中while/for 循环；不过如果深入研究过oracle的运作机制后，就不会肆无忌惮的使用游标了；

我认为游标的缺点有以下几处：

a. 容易有并发脏数据，游标打开后，这些记录集在一个个的循环处理的

这段时间内，它所对应的表的数据可能别别人修改，也就是说，游标的数据时可能是5秒前的数据，执行一个游标的逻辑处理后，整个5秒钟内，游标的记录集的真实数据可能发生了很多变化；这样处理的逻辑就会出问题，这样的问题，并不是“程序逻辑”造成的，所以一旦出现数据问题，从“程序逻辑”上分析，是找不到原因的。简单的说，这个就是类似“刻舟求剑”的误区，对于动态变化的东西，我们在先前的某个时间段得到的东西是不能作为依据的；

当然，对于稍微了解oracle锁机制的人，知道for update 的处理 比如，定义一个游标 select * from tabel1 where … for update

其实，知道在游标里使用for update ，只能说前进了一下步，但它也不能滥用，滥用for update 也会有很多弊端； a.1 滥用for update ,会加大了锁的几率，对高并发系统有串行影响； a.2 有的时候for update使用不好，会在程序内部报错；

a.3 有的时候没法加for update ,比如稍微复杂些的sql，多表关联，带聚合函数等，就没法加上for update;

b. 很多技巧都可以代替游标，举个简单的例子，比如要求出库100件；

仓库库存分布

货位 数量

1 20

2 50 3 40 4 45

…..

这种情况，很多人都是用游标做循环去取数据； 其实，大可不必：

我们通过分析函数 sum(..) over (order by..) ; 对库存分布虚拟两个个累计和列出来即可：

比如：

仓库库存分布

货位 数量 累加数量 前一个累加数量(累加数量-当前数量) 1 20 20 0 2 50 70 20

3 40 110 70 (70~110之间的满足100) 4 45 155 110 …..

这样，我们就不必要做循环一条条的去判断，只要

判断累加数量<=100 and前一个累加数>100 即可，于是马上分析1，2，3货位都出库；1，2全出，3货位出：出库要求(100)- 前一个累计(70)=30个 Pl/sql代码可以这样写：

Insert into 出库任务

select 货位, case 累加数量<=100 then数量 else 100-前一个累加数量 end as 出库数量 from 仓库库存分布 where 累加数量<=100 and前一个累加数>100

一句话搞定的事情，何必用游标去循环慢慢折腾；就像今年春节晚会，郭冬临的小品，一句话的事情，搞那么复杂干嘛；效率低就不跟你计较了，关键是简单的

程序给你写复杂了，增加了逻辑的复杂度，增加了潜在bug的可能性，容易产生并发造成的脏数据；而且会因为多了很多锁影响其他会话的执行；有百害而无一利；

当然我只是抛砖引玉，其实很多技巧都可以避免游标的时候，或减少游标嵌套的层数；

c. 游标有时“高不成低不就”，我在pl/sql用的稍微复杂些的算法，比如回

溯，递归等，鉴于游标只能前后滚动，而且没有想数组那样的按照行

号来回跳转，所以，必须把这些记录值存在数组后才能做灵活的处理； d. 游标效率低，这个表面上到无所谓，慢就慢点，但是如果高并发，就

增加了死锁的几率；

2.3写update语句的注意点； 2.3.1 “进攻式”编程：

我们在写update 语句的时候，有时候未必需要“真写”,

常见的例子是：单据审核，比如把单据状态从0变成1;(0-初始 1-审核 2-完成)

很多人的写法是：

Begin

Select 单据状态 into v_单据状态 from 单据表 where 单号=’123’; If v_单据状态=’1’ Then

dbms_output.put_line (‘单号123已经审核’); return; End if;

Update单据表 set 单据状态=’1’ where单号=’123’; dbms_output.put_line (‘单号123审核成功’);

…………… End;

这样的写法看上去很符合人的逻辑：先判断是否已经审核了，如果已经审核了，就不要做了，如果还没有什么，那么就打上标志；

可是漏洞很大：还是我上面说的“刻舟求剑”；

因为这段代码主要有两句话，先查询状态，再更改状态；

但是这两句话有时间差，第二句话执行的时候，第一句话的查询结果可能发生；也就是说在高并发的情况下，该程序可能得到错误的结果；

怎么去保护呢，很自然，有人想到了:

Select 单据状态 into v_单据状态 from 单据表 where 单号=’123’; 这句话后面加上 for update ；的确，加上这个确实让代码“安全”了；

但是这样，如果高并发，虽然运行不会出逻辑问题，但是如果100人同时执行，执行一次需要0.1秒，那么最终全部完全的时间是10秒；因为这里是串行的，每个人执行到for update 这里就会排队依次执行；

所以最好这样写： begin

Update单据表 set 单据状态=’1’ where单号=’123’ and 单据状态=’0’; If sql%rowcount=0

then

dbms_output.put_line (‘单号123审核成功’); else

dbms_output.put_line (‘单号123已经审核’); return; end if; …………… End;

这种写法“一石二鸟”，一条update语句既可以作为更新，又含有了检查的功能； 通俗的说：要更新什么，就在where里面写上它更新前应该的状态；

这样如果有100个人同时执行的时候，只有第一个人顺利执行，其他人等第一个花0.1秒提交后，全部是sql%rowcount=0不会引起锁，所以就不会有串行执行的互相等待；

这种以Update 兼容select 的做法叫做“进攻式”编程，这个术语，好像是有一本叫做“SQL编程艺术”里提出的；原理就是避免或减少“防御性”的判断，直接做数据的处理；好处是提供并发性，简化程序，防止脏数据；

顺便说一句：

有的人防止并发异常，自作聪明的“发明”了一种写法；

你不是怕并发异常吗？所以他在过程的第一句就来上：

Update 单据 set memo=memo where 单号=’123’； –-随便找个字段“自己”更新“自己” 这样的目的就是为了“锁住”，多个人同时执行的时候不会有数据异常；

我以前遇到一个合作伙伴的项目，每个过程前的第一句话，都来上这种“什么也不做，就是要锁你”这样的怪语句，如果真的要锁，你还不如写成;

Select … into .. from 单据 where 单号=’123’ for update

至少这样的语句还容易理解，而你来个 update… set 自己=自己 where..；这让人看了很“费解”(也许他不知道for update )；

于是我就问他为什么这样做，他还理直气壮的跟我说，这是他们团队的一个“高手”想出的“妙招”，用来防止多个人同时做一件事情发生的数据异常；我不怀疑这个高手的智商，可是这些高手难道就不能多看点书，遇到问题就会出“怪招”，还自鸣得意；

而且每执行一个update语句，都会产生日志的，你就是 update 自己=自己，看上去啥也没干，那还是要生成修改前和修该后的日志，这些无谓的日志去消耗干嘛呢；

如果有兴趣查看日志的消耗，我写了一段脚本，有兴趣的试试：

DECLARE

V_VALUE1 PLS_INTEGER; V_VALUE2 PLS_INTEGER; begin

-- 记录执行前的redo size ; SELECT A.VALUE INTO V_VALUE1

FROM V$MYSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC# AND NAME = 'redo size';

--------------------------------

--写上你的update 语句，哪怕update 自己=自己 ，也会生成日志； COMMIT;--及时把redo buffer 的数据写会redolog文件；

-------------------

-- 记录执行后的redo size ; SELECT A.VALUE

INTO V_VALUE2

FROM V$MYSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC# AND NAME = 'redo size';

dbms_output.put_line('执行前redo size:' || to_char(V_VALUE1));

dbms_output.put_line('执行后redo size:' || to_char(V_VALUE2));

dbms_output.put_line('增加的redo size:' || to_char(V_VALUE2 - V_VALUE1)); end;

2.3.2 update带返回值：

这个“功能”非常实用，如果不知道这个功能，pl/sql算白学了， 不能说t-sql没有这样的功能，你就不知道用；

就像老外到中国不去长城一样；

该功能的表达方式如：

Update table1 set field2=xxx where field1=xxx Return fiend1,field2,field3 into v1,v2,v3;

或者批量(多条记录)：

Update table1 set field2=xxx where field1=xxx

Return fiend1,field2,field3 BULK COLLECT into v1,v2,v3;--变量是数组类型；

这样可以省略很多select 代码；这个倒无所谓，有的时候，update之后，再想selelct 不是那么方面的；

比如：Update table1 set field2=(selelct … from …) where field1=xxx Return field2 into v2; 这个就非常方便；

这个功能如果仅仅是为了减少部分select，那到不算什么大特色；相对t-sql，也只不过方面些而已；

但有的时候，对于高并发的处理，它有很多妙用；

比如：仓库入库，

在我们在入库前，就要做货位分配处理，假如有100托货，同时入库，入库的原则很简答，就是找最小货位；你怎么保证着100托的货同时瞬间分配而不会互相干扰；

这个很简答的问题，一般人都这样写：

Begin

Select min(货位) invo v_货位 from 货位表 where 货位状态=’空闲’； Update 货位表 set 货位状态=’已分配’ where 货位=v_货位; dbms_output.put_line('分配货位成功,是:’|| v_货位); End ;

但是这样写，只能一个一个的按顺序分配，如果100个人同时执行这样的分配工作，那么这些货位都“打架”了；

所以，这个问题，我的最初的写法是： Begin

For I in 1..10

Loop

Select min(货位) invo v_货位 from 货位表 where 货位状态=’空闲’； Update 货位表 set 货位状态=’已分配’ where 货位=v_货位; If sql%rowcunt=1 then

Begin

dbms_output.put_line('分配货位成功,是:’|| v_货位); exit; end if; end loop; End ;

就是给它10次机会，如果并发太高，每次找到最小货位后，就被别人“捷足先登”，那只能多试几次了；

总有一次能成功把；当然，如果胆子大一点，可能给“无限”的尝试次数，不管这样，这种写法很怪；

有什么好的解决方法吗,这里就用到了update返回值的方法； Begin

Update 货位表 set 货位状态=’已分配’ where 货位=(Select min(货位) from 货位表 where 货位状态=’空闲’) where 货位状态=’空闲’) returning 货位 into v_货位; dbms_output.put_line('分配货位成功,是:’|| v_货位); End ;

这样即使100个人同时分配，也不会分到一起的，说老实话，如果不是用这个update …return into .. 的技巧，我还真没想到有什么特别好的方法来处理这个问题；

有的人会怀疑，该写法是不是“安全”的，我经过很多测试，确保是安全的，

大家仔细看这句话：

Update 货位表 set 货位状态=’已分配’ where 货位=(Select min(货位) from 货位表 where 货位状态=’空闲’)

AND 货位状态='空闲'; returning 货位 into v_货位;

这句话里，虽然只是操作一个表，但是其实分两部分，

第1部分：Select min(货位) from 货位表 where 货位状态=’空闲’

第2部分： Update 货位表 set 货位状态=’已分配’where 货位=（第1部分） AND 货位状态='空闲' 也许有人有会疑惑，会不会在高并发的的时候出现这种情况：

第1部分先得到了一个最小货位 001，执行到第2部分的时候，001 的状态已经不是“空闲”，因为第1部分没有锁；

当第2部分执行的时候，可能得到的sql%rowcount=0 ;

我个人认为这种情况不可能，oracle的运行机制可能是这样的： 假如两个人(不同的session)同时执行这句嵌套的update ;

那么同时得到的最小货位肯定一样，其中一个先更新，这时候在提交前会锁住该货位；

另一个发现该货位的记录锁住了，就整个放弃，等你完成，我在重新执行这个SQL，于是它会不停地重新执行这条语句，直到前一个人提交了，这时候它检索的min(货位)也不可能是001货位了，而可能是002货位；

我个人的理解是：对于一条SQL,无论该SQL多么复杂，在执行的过程中，它内部可能要分好几步去执行，但是只要有一步被锁住，那么它前面做好的几步会全部作废，全部重头再执行，如果执行到某一步，发现又有锁了，那再重新全部回头再做，以此循环，直到成功。

这就类似于做一件高级的陶瓷艺术品，有很多工序，中间某个烧火的工序时间控制错了，那么前面的工序全部作废，重头再来；

如果这种原理，就非常安全；

本文来源：https://www.bwwdw.com/article/jd06.html