ORACLE问题收集（或者说针对MS-SQL的不同点）--2011年3月16日更新

1、ORACLE是没有ID自增长的设置的。只能通过给ID字段创建序列，然后通过触发器给ID字段加上序列的方法来完成。注意：序列代码中一定要nocache，否则会根据缓存数决定ID跳跃的个数，例子：

create sequence ORACESHI_S  --序列
minvalue 1 最小值
maxvalue 999999999999999999999999999
start with 111 --起始值为111
increment by 1 递增数为1
nocache
order;

 

CREATE OR REPLACE TRIGGER oraceshi_bir_t BEFORE --触发器
INSERT ON ORACESHI FOR EACH ROW
begin
select oraceshi_s.nextval into :new.o_id from dual;
end;

 

2、ORACLE的存储过程是没有返回值的。不能在程序结束后直接“return 值”，所以我们只有通过创建一个游标，通过给游标赋值的方式来达到返回值的效果。例子：

CREATE OR REPLACE PROCEDURE UP_ORACESHI_ADD (
USERNAME_in varchar2,
PASSWORD_in varchar2,
falg out NUMBER   --这里是返回值
)
 IS
BEGIN
 INSERT INTO ORACESHI(
 USERNAME,PASSWORD
 ) VALUES(
 USERNAME_in ,PASSWORD_in
 );
  if SQL%ROWCOUNT>0 THEN   --判断，如果sql语句受影响的行数大于0，就返回1，否则就返回0
  falg:=1;  --给游标赋值
  ELSE
  falg:=0;
  END IF;
END UP_ORACESHI_ADD;

  

3、表的字段类型：ORACLE里没有BOOL类型的值，只能用NUMBER类型的0或1代替。NUMBER最大值是38。VARCHAR2最大值是4000（变量为32K）。

 

4、ORACLE中没有TOP关键字，只能用 ROWNUM<=行数 来代替，而且倒序前几条记录的时候，只能将倒序查询的语句写好做为表，然后用ROWNUM<=行数。例子：

SELECT * FROM (SELECT * FROM ORACESHI where sbh=4 ORDER BY FID DESC) WHERE ROWNUM<=5 --查询倒序的sbh=4的前5条记录,oracle里不允许使用TOP.只能这样写。在这里，必须是：FROM (SELECT * FROM ORACESHI where sbh=4 ORDER BY FID DESC)。 如果写成了SELECT * FROM ORACESHI WHERE sbh=4 and ROWNUM<=5 ORDER BY FID，那么将会先查询sbh=4的前5条记录，然后再倒序，结果就是错误的了。

 

 

5、ORACLE中存日期和时间一般是date类型，这些时间一般为秒级。如果要存毫秒级时间，只有设置数据类型为timestamp（这个类型在11g菜单里没有，必须手动添加）。例子：

添加一条记录： insert into ORACESHI(ftime,sbh) values(timestamp'2010-9-2 00:00:25.000',6);

查询一条2个时间段，并将毫秒时间转化为字符串的记录（一定要转化，否则查询出来的是秒级时间）：
select FID,FSTRING,to_char(ftime,'yyyy-mm-dd hh24:mi:ssxff'),SBH from ORACESHI where FTIME between to_timestamp('2010-9-2 00:00:00.000','yyyy-mm-dd hh24:mi:ss:ff') and to_timestamp('2010-9-2 00:00:25.000','yyyy-mm-dd hh24:mi:ss:ff');

 

6、动态SQL语句：在ORACLE里，使用动态的SQL语句要用到关键字 execute immediate 如果是在SQL语句只直接赋字符串类型的值，要用2个单引号，如下：
execute immediate 'update ORACESHI set username=''xingming'' ';

如果是声明的变量，那么就要用3个单引号表示，如：

strUpdate:='[{"obj":'
strAdd:='[{"obj":[' || strUpdate||']}]';
execute immediate 'update ORACESHI set username='''||strAdd||''' ';

 

7、小技巧：如果要根据查询到的行，然后再把那个行做修改，一般的写法要用2个查询的语句（一个是select查询，还有一个是update的WHERE查询），效率很低。也有人用连接查询或ORACLE里的 into变量查询，但都不是最好的。我总结了一个不错的例子：

update ORACESHI a set username=(变量||substr(a.username,10)) where 条件

execute immediate 'update ORACESHI a SET username=('''||strUpdate||'''||substr(a.username,10)) where 条件'

 

8、在用PL/SQL操作ORACLE数据库的时候，如果是修改表结构，最好修改一处就立即应用保存并且关闭，否则，有时候会出现修改的列又还原的情况出现，比如删除一个列，就应该立即应用保存，再打开做下一个操作。还有在建触发器的时候，首先要保证对应表正确，否则容易出现表错误

 

9、没有导入数据库的权限，或导入失败
一般在数据库出现问题，重新安装数据库的时候，会发现原来保存的数据库或表导入不进现在安装好的数据库了，发现是权限不够的原因，但是新建的用户把所有的权限都赋上去了也没用。抱这试试看的想法把ORACLE服务都重启了一下，发现又可以了，所以当出现没有导入权限的情况下还是尝试重启动ORACLE服务为好。

 

10、表空间'USERS'中无权限
这是由于表空间上的配额满了的缘故。尝试grant   unlimited   tablespace   to   user--user为你要分配的用户。

 

11、小技巧（也许其他数据库也可以这样做）

在用PL/SQL这样的工具edit data 的时候，如果在语句后面加一个for update的话，那么就可以将查询结果编辑了。如：

select * from oraceshi for update; 之后按F10，再按下锁键就可以编辑了。编辑完毕别忘记提交。

 

12、ORACLE的数据库，SQL语句中一个字段最大一次能存32K的数据。但是，如果是直接写SQL语句的话会报‘SQL语句过长的错误’，解决办法是用存储过程。就可以存近32K的数据了。还有一点要注意，就是不能用动态的存储过程(execute immediate)，用这个是存4K以上的数据也是会报错的。