导航

Oracle开发常见问题<收藏>

Posted on 2010-07-29 22:53  kingwangzhen  阅读(282)  评论(1编辑  收藏  举报

SQL Server和Oracle两大数据库系统是日常项目开发中最常见的数据库,但更多的初学者可能对SQL Server会更熟悉一些,因为微软做的东西就是简单易用,比较傻瓜化。但Oracle在市场中的地位又不容忽视,而Oracle和SQL Server还是有着很大的差别的。本节就讲一些初学者在使用Oracle进行开发时会经常遇到的问题。

5.5.1  Oracle和SQL Server的常用函数对比

1.数学函数

①绝对值 

l         S:select abs(-1) value

l         O:select abs(-1) value from dual

②取整(大) 

l         S:select ceiling(-001) value 

l         O:select ceil(-001)  value from dual

③取整(小) 

l         S:select floor(-001) value  

l         O:select floor(-001) value from dual

④取整(截取)

l         S:select cast(-002 as int)  value  

l         O:select trunc(-002) value from dual 

⑤四舍五入

l         S:select round(23456,4) value    23460

l         O:select round(23456,4) value from dual 2346

⑥e为底的幂 

l         S:select Exp(1) value        

l         O:select Exp(1) value from dual  

⑦取e为底的对数

l         S:select log(7182818284590451) value    

l         O:select ln(7182818284590451) value from dual; 

⑧取10为底对数

l         S:select log10(10) value      

l         O:select log(10,10) value from dual;   

⑨取平方

l         S:select SQUARE(4) value   

l         O:select power(4,2) value from dual  

⑩取平方根

l         S:select SQRT(4) value   

l         O:select SQRT(4) value from dual  

求任意数为底的幂

l         S:select power(3,4) value     

l         O:select power(3,4) value from dual  

取随机数

l         S:select rand() value 

l         O:select sys.dbms_random.value(0,1) value from dual;

取符号

l         S:select sign(-8) value    -1

l         O:select sign(-8) value from dual  -1

2.数值比较

①求集合最大值

l         S:select max(value) value from

   (select 1 value union

   select -2 value union

   select 4 value union

   select 3 value)a

l         O:select greatest(1,-2,4,3) value from dual

②求集合最小值

l         S:select min(value) value from

   (select 1 value union

   select -2 value union

   select 4 value union

   select 3 value)a

l         O:select least(1,-2,4,3) value from dual

③如何处理null值(F2字段中的null以10代替)

l         S:select F1,IsNull(F2,10) value from Tbl

l         O:select F1,nvl(F2,10) value from Tbl

3.字符串函数

①求字符序号

l         S:select ascii('a') value

l         O:select ascii('a') value from dual

②从序号求字符

l         S:select char(97) value

l         O:select chr(97) value from dual

③连接

l         S:select '11'+'22'+'33' value

l         O:select CONCAT('11','22')||33 value from dual

④子串位置——返回3

l         S:select CHARINDEX('s','sdsq',2) value

l         O:select INSTR('sdsq','s',2) value from dual

⑤模糊子串的位置——返回2,参数去掉中间%则返回7

l         S:select patindex('%d%q%','sdsfasdqe') value

l         O:oracle没发现,但是instr可以通过第四个参数控制出现次数

   select INSTR('sdsfasdqe','sd',1,2) value from dual 返回6

⑥求子串

l         S:select substring('abcd',2,2) value

l         O:select substr('abcd',2,2) value  from dual

⑦子串代替——返回aijklmnef

l         S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value

l         O:SELECT Replace('abcdef', 'bcd', 'ijklmn') value  from dual

⑧左补空格(LPAD的第1个参数为空格则同space函数)

l         S:select space(10)+'abcd' value

l         O:select LPAD('abcd',14) value from dual

⑨右补空格(RPAD的第1个参数为空格则同space函数)

l         S:select 'abcd'+space(10) value

l         O:select RPAD('abcd',14) value from dual

⑩发音相似性比较(这2个单词返回值一样,发音相同)

l         S:select soundex ('Smith'), soundex ('Smythe')

l         O:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual

SQL Server中用SELECT DIFFERENCE('Smithers', 'Smythers') 比较soundex的差返回0~4,4为同音,1最高

4.日期函数

①系统时间

l         S:select getdate() value

l         O:select sysdate value   from dual

②求日期

l         S:select  convert(char(10),getdate(),20) value

l         O:select trunc(sysdate) value   from dual

select to_char(sysdate,'yyyy-mm-dd') value   from dual

③求时间

l         S:select  convert(char(8),getdate(),108) value

l         O:select to_char(sysdate,'hh24:mm:ss') value   from dual

④当月最后一天

l         S:不知道

l         O:select LAST_DAY(sysdate) value  from dual

⑤本星期的某一天(比如星期日)

l         S:不知道

l         O:SELECT Next_day(sysdate,7) vaule FROM DUAL;

⑥字符串转时间

l         S:select  cast('2004-09-08'as datetime) value

l         O:SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss') vaule FROM DUAL;

⑦求2日期某一部分的差(比如秒)

l         S:select  datediff(ss,getdate(),getdate()+3) value

l         O:直接用两个日期相减(比如d1-d2=3)

SELECT (d1-d2)*24*60*60 vaule FROM DUAL;

⑧根据差值求新的日期(比如分钟)

l         S:select  dateadd(mi,8,getdate()) value

l         O:SELECT sysdate+8/60/24 vaule FROM DUAL;

5.5.2  Oracle和SQL Server的语句区别

1.Oracle如何实现自增列

如果你一直都是用SQL Server作为开发数据库的,而新的项目需要使用Oracle数据库,那么你会突然发现SQL Server和Oracle还是有着很大的差别的。首先,我在SQL Server中用得很顺手的自增长字段就在Oracle中找不到了,取而代之,Oracle可以用序列来实现。示例如下。

(1)建立测试数据表。

CREATE TABLE TEST

(

 ID NUMBER,

 NAME VARCHAR2(20),

 PRIMARY KEY(ID)

);

(2)创建序列。

CREATE SEQUENCE SEQ_TEST;

(3)创建触发器。

CREATE OR REPLACE TRIGGER AUTOINCREMENT

BEFORE INSERT ON TEST

FOR EACH ROW

WHEN (NEW.ID IS NULL)

BEGIN

SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL;

END;

(4)插入数据。

INSERT INTO TEST(NAME) VALUES('NAME1');

(5)查看插入结果。

SELECT * FROM TEST;

2.Parameter的区别

在SQL Server中我们可以按以下方式使用SQL语句:"Insert into Table (Field1,field2) values(@Value1,@Value2)",然后我们再new 几个Paramter:new SqlParameter("@Value1",value)...

在查询字符串中使用@+字符来描述参数,在SqlParameter中的参数名也要使用“@”符号。而在Oracle中SQL语句不能使用@符号,以冒号“:”代替,如:

--Sql Server的Sql 语句

insert into Table (Column1,Column2) values (@Value1,@Value2) 

--Oracel中的Sql 语句

 Insert Into Table (Column1,Column2) values (:Value1,:Value2)

3.存储过程的不同

Oracle中的存储过程叫做包(Packages),一个包分为包头和包体,类似于C++中的类声明。包头定义了存储过程的名称和参数,包体除了名称和参数,还包括存储过程的所有语句。与SQL Server不同,在Oracle中存储过程一般写成Function,而不是Procedure。Oracle存储过程并不直接返回记录集,记录集以游标的形式通过参数返回。一个包(Packages)可以包含多个存储过程,使用存储过程时采用“包名.存储过程名”的方式,下面是一个典型的Oracle存储过程,它位于名称为“Test”的包(Packages)中,它的使用方式应为Test.GetList。

Function GetList(keywords In varchar2,p_info_list_Cursor out get_data_cur_type)

   Return Number 

As 

Begin

       open p_info_list_Cursor For 

       Select * from Test where Key=keywords

;

       Return 0;

End;

5.5.3  ASP.NET连接Oracle失败的解决方法

安装完ASP.NET Oracle客户端后,ASP.NET应用程序使用System.Data.OracleClient访问Oracle数据库如果出现这种错误:System.Exception:System.Data.OracleClient requires Oracle client software version 8.1.7 or greater. 但在同一台机器上用WinForm客户端又能够正常访问到数据库。

则其原因:

Oracle 9i客户端在安装到Windows的NTFS分区下时的安全认证设置不正确,引起本机的Authenticated Users用户无法看到ORACLE_HOME目录下的内容; 这导致在ASP.NET以Authenticated Users权限使用System.Data.OracleClient连接Oracle数据库时报出以上错误。

解决方法如下:

(1)以管理员的身份登录。

(2)找到ORACLE_HOME文件夹(我的是C:\oracle\ora92),用鼠标右键单击,在弹出的快捷菜单中选择“属性”→“安全”命令,在组或用户栏中选择“Authenticated Users”选项,在下面权限列表中把“读取和运行”的权限去掉,再单击“应用”按钮;重新选择“读取和运行”选项,单击“应用”按钮,单击权限框下面的“高级”按钮,选择“Authenticated Users”选项后面的应用,选择“该文件夹、子文件夹及文件”选项,单击“确定”按钮把权限的更改应用于该文件夹。

(3)重新启动计算机,让权限设置生效(请注意,这一步很重要)。

(4)登录后运行ASP.NET应用,就可以正常连接Oracle数据库了。