除了Select,还有什么好玩的?

发表于:2017-12-05 08:00

字体: | 上一篇 | 下一篇 | 我要投稿

 作者:杨富焜    来源:51Testing软件测试网原创

#
select
分享:
  用了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软件测试网及内容提供者拥有本文全部版权,未经明确的书面许可,任何人或单位不得对本文进行复制、转载或镜像,否则将追究法律责任。

《2023软件测试行业现状调查报告》独家发布~

关注51Testing

联系我们

快捷面板 站点地图 联系我们 广告服务 关于我们 站长统计 发展历程

法律顾问:上海兰迪律师事务所 项棋律师
版权所有 上海博为峰软件技术股份有限公司 Copyright©51testing.com 2003-2024
投诉及意见反馈:webmaster@51testing.com; 业务联系:service@51testing.com 021-64471599-8017

沪ICP备05003035号

沪公网安备 31010102002173号