Oracle開發常見問題

Oracle開發常見問題

SQL Server和Oracle兩大資料庫系統是日常項目開發中最常見的資料庫，但更多的初學者可能對SQL Server會更熟悉一些，因為微軟做的東西就是簡單易用，比較傻瓜化。但Oracle在市場中的地位又不容忽視，而Oracle和SQL Server還是有著很大的差別的。本節就講一些初學者在使用Oracle進行開發時會經常遇到的問題。

5.5.1  Oracle和SQL Server的常用函數對比

1．數學函數

 

  
①絕對值   
  
    S：select abs(-1) value  
  
    O：select abs(-1) value from dual  
  
②取整(大)   
  
    S：select ceiling(-001) value   
  
    O：select ceil(-001)  value from dual  
  
③取整（小）   
  
    S：select floor(-001) value     
  
    O：select floor(-001) value from dual  
  
④取整（截取）  
  
    S：select cast(-002 as int)  value    
  
    O：select trunc(-002) value from dual   
  
⑤四捨五入  
  
    S：select round(23456,4) value    23460  
  
    O：select round(23456,4) value from dual 2346  
  
⑥e為底的冪   
  
    S：select Exp(1) value          
  
    O：select Exp(1) value from dual    
  
⑦取e為底的對數  
  
    S：select log(7182818284590451) value       
  
    O：select ln(7182818284590451) value from dual;    
  
⑧取10為底對數  
  
    S：select log10(10) value         
  
    O：select log(10,10) value from dual;     
  
⑨取平方  
  
    S：select SQUARE(4) value     
  
    O：select power(4,2) value from dual    
  
⑩取平方根  
  
    S：select SQRT(4) value     
  
    O：select SQRT(4) value from dual    
  
 求任意數為底的冪  
  
    S：select power(3,4) value       
  
    O：select power(3,4) value from dual    
  
 取隨機數  
  
    S：select rand() value    
  
    O：select sys.dbms_random.value(0,1) value from dual;  
  
 取符號  
  
    S：select sign(-8) value    -1  
  
    O：select sign(-8) value from dual  -1 
 

2．數值比較

 

  
①求集合最大值  
  
    S：select max(value) value from   
  
   (select 1 value union  
  
   select -2 value union  
  
   select 4 value union  
  
   select 3 value)a  
  
    O：select greatest(1,-2,4,3) value from dual  
  
②求集合最小值  
  
    S：select min(value) value from   
  
   (select 1 value union  
  
   select -2 value union  
  
   select 4 value union  
  
   select 3 value)a  
  
    O：select least(1,-2,4,3) value from dual  
  
③如何處理null值（F2欄位中的null以10代替）  
  
    S：select F1,IsNull(F2,10) value from Tbl  
  
    O：select F1,nvl(F2,10) value from Tbl 
 

3．字串函數

 

  
①求字元序號  
  
    S：select ascii('a') value  
  
    O：select ascii('a') value from dual  
  
②從序號求字元  
  
    S：select char(97) value  
  
    O：select chr(97) value from dual  
  
③串連  
  
    S：select '11'+'22'+'33' value  
  
    O：select CONCAT('11','22')||33 value from dual  
  
④子串位置--返回3  
  
    S：select CHARINDEX('s','sdsq',2) value   
  
    O：select INSTR('sdsq','s',2) value from dual  
  
⑤模糊子串的位置--返回2，參數去掉中間%則返回7  
  
    S：select patindex('%d%q%','sdsfasdqe') value   
  
    O：oracle沒發現，但是instr可以通過第四個參數控制出現次數  
  
   select INSTR('sdsfasdqe','sd',1,2) value from dual 返回6  
  
⑥求子串  
  
    S：select substring('abcd',2,2) value   
  
    O：select substr('abcd',2,2) value  from dual  
  
⑦子串代替--返回aijklmnef  
  
    S：SELECT STUFF('abcdef', 2, 3, 'ijklmn') value  
  
    O：SELECT Replace('abcdef', 'bcd', 'ijklmn') value  from dual  
  
⑧左補空格（LPAD的第1個參數為空白格則同space函數）  
  
    S：select space(10)+'abcd' value  
  
    O：select LPAD('abcd',14) value from dual  
  
⑨右補空格（RPAD的第1個參數為空白格則同space函數）  
  
    S：select 'abcd'+space(10) value  
  
    O：select RPAD('abcd',14) value from dual  
  
⑩發音相似性比較（這2個單詞傳回值一樣，發音相同）  
  
    S：select soundex ('Smith'), soundex ('Smythe')  
  
    O：SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual  
  
SQL Server中用SELECT DIFFERENCE('Smithers', 'Smythers') 
比較soundex的差返回0～4，4為同音，1最高 
 

4．日期函數

 

  
①系統時間  
  
    S：select getdate() value  
  
    O：select sysdate value   from dual  
  
②求日期  
  
    S：select  convert(char(10),getdate(),20) value  
  
    O：select trunc(sysdate) value   from dual  
  
select to_char(sysdate,'yyyy-mm-dd') value   from dual  
  
③求時間  
  
    S：select  convert(char(8),getdate(),108) value  
  
    O：select to_char(sysdate,'hh24:mm:ss') value   from dual  
  
④當月最後一天  
  
    S：不知道  
  
    O：select LAST_DAY(sysdate) value  from dual  
  
⑤本星期的某一天（比如星期日）  
  
    S：不知道  
  
    O：SELECT Next_day(sysdate,7) vaule FROM DUAL;  
  
⑥字串轉時間  
  
    S：select  cast('2004-09-08'as datetime) value  
  
    O：SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss') 
vaule FROM DUAL;  
  
⑦求2日期某一部分的差（比如秒）  
  
    S：select  datediff(ss,getdate(),getdate()+3) value  
  
    O：直接用兩個日期相減（比如d1-d2=3）  
  
SELECT (d1-d2)*24*60*60 vaule FROM DUAL;  
  
⑧根據差值求新的日期（比如分鐘）  
  
    S：select  dateadd(mi,8,getdate()) value  
  
    O：SELECT sysdate+8/60/24 vaule FROM DUAL; 
  
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; 
     
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資料庫了。