用了pl/sql两年,此文算是两年期间的一点总结,不过有一定的适用范围,比如不适用于命令行窗口。数据库坑很大,如有误解,望大神大刀斧正。
一、For update 与 rowid
先写结论:
For update会在语句运行时就给数据加上行级锁,容易使数据库发生锁表。Rowid运行后可以在pl/sql中使用上锁功能编辑数据,提交事务的瞬间才上锁,上锁解锁瞬间完成。使用PL/SQL时用rowid更安全。
解释:
For update如其名,是"为了更新数据而上锁"。通常因为业务需求,为了保持数据一致性、方便后续操作,所以在select……for update时候就给数据上锁,后续业务完成后才解锁。而rowid是通过物理地址去确定某一行数据,其效果比索引还快。
所以for update与rowid其实是两个范畴的语句,使用的目的也是不同,没有比较性。但是在PL/SQL这种客户端工具,在可视化窗口操作时,这两种写法都可以达到修改数据的目的。不过由于上锁时机不一样,导致发生锁表风险不一样。
下面用实例验证这个过程:
1.上方会话用for update锁住,下方会话无法以for update方式查询数据
2. 上方会话回滚事务,下方被卡住的会话马上查询出数据。
3. 使用rowid不会发生锁。
不少测试人员在刚接触PL/SQL时,由于师父、开发的指导,用上了for update。当for update语句运行时,会在对应行(where子句)加上行级锁,无where子句等于全表上锁。万一客户端断网、测试人员忘记提交\回滚事务,则会发生锁表。而rowid则是在提交事务(commit)的瞬间完成上锁、提交、解锁等动作,不容易发生锁表。
For update确实容易发生锁表,但其机制可以被巧用的。个人经验是
1、对于小范围两三个人共享的数据可以用for update防止他人修改;
2、大范围共享数据直接用update\commit;
3、对于频繁读写的数据禁止使用for update的,正式环境全面严禁使用for update。
最后,rowid的写法是:
Select t.*,t.rowid from table t;
因为rowid是属于所有表,所以需要加表名。
二、经常看到的Dual是什么?
Dual并不是一张实际存在的表,也不储存什么数据,主要用于临时的计算或者查询,因为select后面必须有个对象(from),有些运算又不属于任何一个表的,就可以用dual。查看数据库序列sequence时很好用,运行时只会返回一行数据(有些同学的理解是一列,这就依赖于你是横着看还是竖着看数据库吧……)
常用语法有:
SELECT * FROM dual;
SELECT 999*999 FROM dual;
SELECT 9,7,8,6,5,4 FROM dual;
SELECT SYSDATE FROM dual;
SELECT seq_table1_id.currval FROM dual;(每次执行后序列不会改变)
SELECT seq_table2_id.nextval FROM dual;(每次执行后序列会自增)
三、1=1有什么用?
1=1真的没什么用…
但是可以为调试、条件拼凑时带来极大方便。
一般写法是select ……where 1=1 and ……;在新需求的测试、调试过程中,where后面的条件经常是不确定的,而where后面又不能直接跟and,这时候写上1=1后可以方便注释子句,如下图所示:
这样我只要加上"-"就能注释,不用考虑语句顺序,可以随时加and。
四、什么时候用left join
首先假定看到这里的同学都是会用left join的。
以前做过一个运维需求,需要提供一个sql以便实时监控订单生成情况,需要用到的表包括:订单表、渠道表、地市表、支付表、系统字典表等。下图为核心表,系统字典起翻译作用。
面对4、5个以上的表时,我习惯是用left join,而不用"="。因为inner join与left join最大区别是,inner join只会返回两表都有相同链接关键字的记录,而left join是基于左表数据不丢失下,返回记录。
对于这个需求,使用left Join的好处有
1、 订单表、支付表的status可能存在空值、可能某个值在系统字典不存在、可能以后需求会新增一个值而系统字典忘记更新,left join都能避免丢失订单记录,并且及时发现错误。
2、 后续版本如果有bug,或者业务逻辑更改,导致缺少记录地市id、渠道id,亦能避免丢失订单记录。
3、 利于初次编写时发现可能会缺少、非必填的字段。
总得来说,在不确定外表是否有记录、又不想丢了主表数据的情况下,都能使用left join。
五、ORA-12154::TNS:无法处理服务名
这个问题我遇到过两次,都是新机器,都是安装好oracle9i精简版、用一直完好的tnsname.ora,反正什么都是好的情况下,还是会报这个错。直到最近发现一个规律:
就是绿色版pl/sql解压放到program files (x86)这个文件,运行就会报错。原因不明,怀疑是oracle9i在安装时会设置什么怪属性,造成pl/sql报错。
版权声明:51Testing软件测试网及内容提供者拥有本文全部版权,未经明确的书面许可,任何人或单位不得对本文进行复制、转载或镜像,否则将追究法律责任。