Oracle数据库的奇技淫巧

这篇博客是Oracle 数据库使用过程的手记，会永久不定时更新一些新的关于Oracle数据库使用的技巧，特别是不同于SQL Server和My SQL的奇技淫巧。
注：数据库版本：Oracle 11g 数据库管理软件：PL/SQL Developer
（去IOE指的是，减少使用甚至完全不使用IBM小型机、Oracle数据库、EMC存储设备，除开技术方面的原因，更多是政治方面的考量，与政府机构采用华为的设备而不是思科的设备相类似。）

0. 说明 Oracle不区分大小写，存储在数据库里的数据全部为大写，不管你插入时输出的语句是大写还是小写。在操作任何表时，尤其是使用delete语句或者drop语句，最后使用数据库名.表名的形式，这样可以保证操作准确，不会误删。

1. 表别名 即为关系表起一个简略的名字，方便使用，别名紧跟from后的表名。在表名很长需要查询的内容很多时特别适用。

select * from table_name (标准查询语句)
select t.* from table_name t

2. rowid 在PL/SQL里，只有在查询语句里加上rowid，才能在图形界面编辑数据行并保存。

select t.*,rowid from table_name t
select t.*,t.rowid from table_name t //两者等价

3.rownum 不同于SQL Server，Oracle没有TOP关键字，许多跟查询结果行数有关的操作依赖rownum来实现。

--查询前1000行：
select t.* from table_name t where rownum < 1001
select t.* from table_name t where rownum <=1000
//(推荐使用第一行的方法，<=在数据库中的执行时间甚至有可能是<的两倍)

--查询第11到第20共10条数据
select * from (select t.*,rownum r from cfg_id_generator t) where r >= 11 and r <= 20
//(r是rownum的别名，我尝试过不使用别名，不使用别名无法查询到数据)

--查询最新创建的前10条记录
select * from (select t.* from table_name t order by create_date desc) where rownum < 11

--查询前10条记录并按时间排序
select t.* from table_name t where rownum < 11 order by create_date desc
//比较上下有何不同的地方？
//请注意，如果排序依据是主键，上下两种方法结果是相同的 仅在该情况下 下句可以替换上句 否则两者的操作逻辑是不一样的

4. 查询字段值在特定范围内的数据(通用)

select t.*,rowid from table_name t where id in (10000,11000);--只能查询前后两行

select t.*,rowid from table_name t where id >= 10000 and cfg_task_id <= 11000;

select t.*,rowid from table_name t where id between 10000 and 11000

5. 新建Sequence

create sequence BOOK$SEQ  //表名$SEQ
minvalue 1
maxvalue 99999999999
start with 10
increment by 1
cache 200;

6. 格式转换

//日期格式转换
to_date(‘2017-7-26’, ‘yyyy-mm-dd’);    //使用insert语句插入日期格式也使用这一格式
to_date('2017-8-1 16:36:30', 'YYYY-MM-DD HH24:MI:SS');

//字符格式转换
Declare i int;
to_char(i);

7. 循环建表语句脚本(建立从table_test_0 到 table_test_49一共50张表)：

begin
	declare i int;
		tableName varchar2(50);
		sqlString varchar2(1000);
	begin
		i := 0;
		while i < 50 loop
			begin
				tableName := ‘table_test_’ || to_char(i);
				i := i + 1;

				sqlString := ‘create table’ || tableName ||
				‘(
					User_id          number(20),
					User_name       varchar(20),
					Create_Date	    Date,
                )’ ;
                Execute immediate sqlString;
			end;
		end loop;
	end;
end; 

8.查看被锁的表和解锁

--查看被锁的表
select object_name, machine, s.sid, s.serial#
from gv$locked_object l, dba_objects o, gv$session s
where l.object_id = o.object_id
and l.session_id = s.sid;
--对表进行解锁
alter system kill session 'sid, serialid'

参考资料：
http://soft.chinabyte.com/database/27/11420027.shtml