顯示具有 Oracle 標籤的文章。顯示所有文章

2013年11月11日星期一

Oracle如何檢核空字串(How to compare a VARCHAR2 variable, which is an empty value?)

上星期遇到的情況，當測試資料也建好了，就很直覺下了一個SQL語法去檢核該條件欄位是不是空的？
SQL> SELECT * FROM TXN FROM USERDATE != ''
結果卻找不到任何資料！當下覺得怎麼會這樣，明明資料才建好，不加條件就找得到資料。只好看文件找解答，最後的結果是
Oracle doesn't differentiate between empty strings and NULL, Use the IS NULL syntax to check if variable is an empty string.
閱讀全文...

2013年1月9日星期三

Oracle 計算時間差

突然來了個要計算每筆交易的時間，試著用Oracle提供的函式來解決，將最後的結果做個記錄。

計算兩日期的時間差：START_DATE, END_DATE
天：ROUND(TO_NUMBER(END_DATE - START_DATE))
小時：ROUND(TO_NUMBER(END_DATE - START_DATE) *24)
分：ROUND(TO_NUMBER(END_DATE - START_DATE) *24*60)
秒：ROUND(TO_NUMBER(END_DATE - START_DATE) *24*60*60)
毫秒：ROUND(TO_NUMBER(END_DATE - START_DATE) *24*60*60*1000)

上述的START_DATE, END_DATE為日期形態，如果遇到日期都用字串形態存入資料庫時，需在用TO_DATE轉換
TO_DATE(START_DATE, 'YYYYMMDDHH24MISS')
YYYYMMDDHH24MISS 這格式需帶入符合您的日期格式

閱讀全文...

2012年5月16日星期三

MS SQL Server 如何建立 Linked Server 連接 Oracle

因為專案的需求，所以研究了一下如何在 SQL Server 建立 Linked Server 連接 Oracle。
寫下此篇記錄，方便以後忘記時可以參考。

1. 首先要安裝 Oracle Client ，並設定好 tnsnames.ora ，其中的 NET_SERVICE_NAME 在建立 Linked Server 時會用到。
例：tnsnames.ora
NET_SERVICE_NAME =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = YourDbIP)(PORT = YourDbPort))
    )
    (CONTECT_DATA =
        (SERVICE_NAME = YourServiceName)
    )
)

2. 建立 Linked Server
開啟 Microsoft SQL Server Management Studio 來新增連結的伺服器。

在一般頁面中各欄位填入資料：
連結的伺服器：填入這個連結的名稱，例： ORACLELNK
伺服器類型，請選擇其他資料來源。
提供者：請選擇 Microsoft OLE DB Provider for Oracle
產品名稱：填 oracle
產品來源：請填入你在 tnsnames.ora 設定的網路服務名稱(NET_SERVICE_NAME)

切換至安全性頁面

請選擇使用此安全性內容建立，並在遠端登入欄位填入帳號，指定密碼欄位填入登入密碼後，按確定，即可以建立 Linked Server 。

3. 測試連線是否正常

成功就會顯示成功的訊息，如

4. 查詢測試
SELECT * FROM OPENQUERY(ORACLELNK, 'SELECT * FROM YourTable')

閱讀全文...

2011年8月16日星期二

Oracle去除空白(Trim Space)

往往在操作PL/SQL時會遇到所謂的靈異現象，明明兩字串肉眼看都一模一樣，可是程式就是不往設定的流程跑，會發生此問題，主要是PL/SQL和Oracle在對資料型態不同的字串處理方式不一樣。

PL/SQL以varchar2類型接收Oracle的char類型，將會自動去除後端的空白；以char類型接收varchar2類型，會補滿空白。

Oracle的varchar2接收char時，不會去除空白；Oracle的char接收varchar2會補滿空白。

因此當兩字串比較時，就會因空白的差異而得到非預期的結果。此時建議用

RTRIM

函示來去除尾端的空白，讓程式正常運行。
閱讀全文...

2011年8月15日星期一

Oracle日期運算問題

不管是資料庫操作或是Shell script撰寫，日期的運算加減是常會遇到的一個問題，Oracle提供了一些常用的運算函數來操作這些日期的運算問題。

1. SYSDATE + or - 天數

EX:
SQL> SELECT SYSDATE FROM dual;
SYSDATE
----------
2011/08/15

2. 日期加數值
EX:
SQL> SELECT SYSDATE+10 FROM dual;
SYSDATE+10
----------
2011/08/25

3. 日期減數值
EX:
SQL> SELECT SYSDATE-15 FROM dual;
SYSDATE-15
----------
2011/07/31

4. 日期相減
EX:
SQL> SELECT SYSDATE- TO_DATE('2011/08/14') FROM dual;
SYSDATE-TO_DATE('2011/08/14')
-----------------------------
1.51289352

SQL> SELECT TRUNC(SYSDATE- TO_DATE('2011/08/14')) FROM dual;
TRUNC(SYSDATE-TO_DATE('2011/08/14'))
------------------------------------
1

5. 日期相減獲得小時差距
EX:
SQL> SELECT TRUNC((SYSDATE - TO_DATE('2011/08/14'))*24) FROM dual;
TRUNC((SYSDATE-TO_DATE('2011/08/14'))*24)
-----------------------------------------
36

SQL> SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE,'YY
-------------------
2011/08/15 12:21:52

6. 日期相減獲得分鐘差距
EX:
SQL> SELECT TRUNC((SYSDATE - TO_DATE('2011/08/14'))*24*60) FROM dual;
TRUNC((SYSDATE-TO_DATE('2011/08/14'))*24*60)
--------------------------------------------
2182

7. 日期相減獲得秒數差距
EX:
SQL> SELECT TRUNC((SYSDATE - TO_DATE('2011/08/14'))*24*60*60) FROM dual;
TRUNC((SYSDATE-TO_DATE('2011/08/14'))*24*60*60)
-----------------------------------------------
130993

8. 日期加 N 小時
EX:
SQL> SELECT TO_CHAR(SYSDATE+(1/24), 'YYYY/MM/DD HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE+(1/
-------------------
2011/08/15 13:24:37

9. 日期加 N 分鐘
EX:
SQL> SELECT TO_CHAR(SYSDATE+(1/1440), 'YYYY/MM/DD HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE+(1/
-------------------
2011/08/15 12:26:11

10. 日期加 N 秒數
EX:
SQL> SELECT TO_CHAR(SYSDATE+(1/86400), 'YYYY/MM/DD HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE+(1/
-------------------
2011/08/15 12:25:33

11. ADD_MONTHS(d, n)
從時間點 d 加上 n 小時
EX:
SQL> SELECT SYSDATE, ADD_MONTHS(SYSDATE, 3) FROM dual;
SYSDATE ADD_MONTHS
---------- ----------
2011/08/15 2011/11/15

12. LAST_DAY(d)
從時間點 d 起，當月的最後一天
EX:
SQL> SELECT SYSDATE, LAST_DAY(SYSDATE) 月底 FROM dual;
SYSDATE 月底
---------- ----------
2011/08/15 2011/08/31

13. NEXT_DAY(d, char)
從時間點 d 開始，下星期幾的日期
char: SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY

EX:
SQL> SELECT SYSDATE, NEXT_DAY(SYSDATE, 'MONDAY') "下星期一" FROM dual;
SYSDATE 下星期一
---------- ----------
2011/08/15 2011/08/22

SQL> SELECT SYSDATE, NEXT_DAY(SYSDATE, 'MONDAY')+1 FROM dual;
SYSDATE NEXT_DAY(S
---------- ----------
2011/08/15 2011/08/23

14. MONTHS_BETWEEN(d1, d2)
計算兩日期之間的相隔月數

EX:
SQL> SELECT TRUNC(MONTHS_BETWEEN('2011/08/31','2011/07/01')) FROM dual;
TRUNC(MONTHS_BETWEEN('2011/08/31','2011/07/01'))
------------------------------------------------
1

以15號為四捨五入
SQL> SELECT ROUND(MONTHS_BETWEEN('2011/08/31','2011/07/01')) FROM dual;
ROUND(MONTHS_BETWEEN('2011/08/31','2011/07/01'))
------------------------------------------------
2

15. NEW_TIME(d, z1, z2)
轉換新時區

EX:
SQL> SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') "遠東地區" ,
2 TO_CHAR(NEW_TIME(SYSDATE,'EST','GMT'),'YYYY/MM/DD HH24:MI:SS') "格林威治"
3 FROM dual;
遠東地區格林威治
------------------- -------------------
2011/08/15 13:57:47 2011/08/15 18:57:47

16. ROUND(d[, fmt])
對日期作四捨五入的運算
月份以每月15號為基準
年份以六月為基準

EX:
SQL> SELECT SYSDATE,ROUND(SYSDATE,'MONTH') FROM dual;
SYSDATE ROUND(SYSD
---------- ----------
2011/08/15 2011/08/01

SQL> SELECT SYSDATE,ROUND(SYSDATE,'YEAR') FROM dual;
SYSDATE ROUND(SYSD
---------- ----------
2011/08/15 2012/01/01

17. TRUNC(d[, fmt])
對日期作無條件捨去的運算

EX:
SQL> SELECT SYSDATE, TRUNC(SYSDATE,'YEAR') FROM dual;
SYSDATE TRUNC(SYSD
---------- ----------
2011/08/15 2011/01/01

SQL> SELECT SYSDATE, TRUNC(SYSDATE,'MONTH') FROM dual;
SYSDATE TRUNC(SYSD
---------- ----------
2011/08/15 2011/08/01

閱讀全文...

Oracle與日期有關的常用函數

Oracle 用來取得目前系統時間的函數為sysdate。
EX:
SQL> SELECT sysdate FROM dual;
SYSDATE
---------
15-AUG-11

*更改目前session日期顯示格式
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
Session altered.

SQL> SELECT sysdate FROM dual;
SYSDATE
----------
2011-08-15

常用的日期格式：
1. YYYY/MM/DD
YYYY 年(4位)
MM 月份(2位)
DD 日期(2位)
SQL> SELECT TO_CHAR(sysdate, 'YYYY/MM/DD') FROM dual;
TO_CHAR(SY
----------
2011/08/15

2. 取得星期幾
Sunday=1, Monday=2, ...
SQL> SELECT TO_CHAR(sysdate, 'D') FROM dual;
T
-
2

SQL> SELECT TO_CHAR( TO_DATE('2011/08/14'), 'D') FROM dual;
T
-
1

3. DDD 一年的第幾天
SQL> SELECT TO_CHAR(sysdate, 'DDD') FROM dual;
TO_
---
227

4. WW 一年的第幾週
SQL> SELECT TO_CHAR(sysdate, 'WW') FROM dual;
TO
--
33

5. W 一月的第幾週
SQL> SELECT TO_CHAR(sysdate, 'W') FROM dual;
T
-
3

6. YYYY/MM/DD HH24:MI:SS AM
YYYY 年
MM 月份
DD 日期
HH24/HH HH24表採24小時制
MI 分鐘
SS 秒數
AM/PM 顯示上/下午
SQL> SELECT TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS AM') FROM dual;
TO_CHAR(SYSDATE,'YYYY/
----------------------
2011/08/15 11:48:43 AM

SQL> SELECT TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS PM') FROM dual;
TO_CHAR(SYSDATE,'YYYY/
----------------------
2011/08/15 11:49:03 AM

7. J 顯示Juilan Day, BC 4712/01/01為1
SQL> SELECT TO_CHAR(sysdate, 'J') FROM dual;
TO_CHAR
-------
2455789

SQL> SELECT TO_CHAR(TO_DATE('2011/08/14'),'J') FROM dual;
TO_CHAR
-------
2455788

8. RR/MM/DD
公元 2000 問題
00-49 表下世紀
50-99 表本世紀
SQL> SELECT to_DATE('99/12/31','RR/MM/DD') FROM dual;
TO_DATE('9
----------
1999-12-31

SQL> SELECT TO_DATE('02/02/02','RR/MM/DD') FROM dual;
TO_DATE('0
----------
2002-02-02

SQL> SELECT TO_DATE('49/12/31','RR/MM/DD') FROM dual;
TO_DATE('4
----------
2049-12-31

SQL> SELECT TO_DATE('50/01/01','RR/MM/DD') FROM dual;
TO_DATE('5
----------
1950-01-01

閱讀全文...

2011年8月12日星期五

Oracle內建常用字串函數

字串的開始位置是1
字串函數傳回字串值
CHR, CONCAT, INITCAP, LOWER, LPAD, LTRIM, REPLACE, RPAD, RTRIM, SUBSTR, TRANSLATE, UPPER
字串函數傳回數字值
ASCII, INSTR, LENGTH

1. CHR(n)

將ASCII CODE轉換成database character set.
EX:
SQL> SELECT CHR(67)||CHR(72)||CHR(82) "CHR 範例" FROM dual;
CHR 範例
--------
CHR

2. CONCAT(string, string)

可連結兩字串
EX:
SQL> SELECT CONCAT('Hello, ', 'World!') "CONCAT 範例" FROM dual;
CONCAT 範例
-------------
Hello, World!

3. INITCAP(string)

將每一個字的字首轉換成大寫
EX:
SQL> SELECT INITCAP('oracle') "INITCAP 範例" FROM dual;
INITCAP 範例
------------
Oracle

4. LOWER(string)

將大寫字轉換成小寫
EX:
SQL> SELECT LOWER('ORACLE') "LOWER 範例" FROM dual;
LOWER 範例
----------
oracle

5. UPPER(string)

將小寫字轉換成大寫
EX:
SQL> SELECT UPPER('oracle') "UPPER 範例" FROM dual;
UPPER 範例
----------
ORACLE

6. LPAD(string1, n[, string2])

將字串右靠，不足n長度，則左補string2，string2預設是空白。
EX:
SQL> SELECT LPAD('Oracle', 10, 'X') "LPAD 範例" FROM dual;
LPAD 範例
----------
XXXXOracle

7. RPAD(string1, n[, string2])

將字串左靠，不足n長度，則右補string2，string2預設是空白。
EX:
SQL> SELECT RPAD('Oracle', 10, 'X') "RPAD 範例" FROM dual;
RPAD 範例
----------
OracleXXXX

8. LTRIM(string[, set])

從字串最左邊去除所有set字元，set預設為空白。
EX:
SQL> SELECT LTRIM('XXXXOracle', 'X') "LTRIM 範例" FROM dual;
LTRIM 範例
----------
Oracle

9. RTRIM(string[, set])

從字串最右邊去除所有set字元，set預設為空白。
EX:
SQL> SELECT RTRIM('OracleXXXX', 'X') "RTRIM 範例" FROM dual;
RTRIM 範例
----------
Oracle

10. REPLACE(string, search_string[, replace_string])

在字串中尋找search_string，找到後將其至換成replace_string，若是省略replace_string，則會將找到的search_string去除掉。
EX:
SQL> SELECT REPLACE('O1234e', '1234', 'racl') "REPLACE 範例" FROM dual;
REPLAC 範例
-----------
Oracle

11. TRANSLATE(string, from_string, to_string)

將string中的from_string改成to_string
EX:
SQL> SELECT TRANSLATE(1234512, '12345', 'ABCDE') "TRANSLATE 範例" FROM dual;
TRANSLATE 範例
--------------
ABCDEAB

12. SUBSTR(string,m[,n])

將字串由第m個字元開始擷取n個字元。
若m為0時，由第一個字元開始；m為負數時，由字串最後面開始算起。
若省略n，則會傳回m之後的所有字元。
若n為負數，則傳回NULL。
EX:
SQL> SELECT SUBSTR('abcdefg', 2, 4) "SUBSTR 範例" FROM dual;
SUBSTR 範例
-----------
bcde

SQL> SELECT SUBSTR('abcdefg', -4, 3) "SUBSTR 範例" FROM dual;
SUBSTR 範例
-----------
def

SQL> SELECT SUBSTR('abcdefg', 2) "SUBSTR 範例" FROM dual;
SUBSTR 範例
-----------
bcdefg

SQL> SELECT SUBSTR('abcdefg', 3, -3) "SUBSTR 範例" FROM dual;
SUBSTR 範例
-----------

13. INSTR(string1,string2[,n[,m]])

從string1中第n個字元開始尋找第m次遇到string2的位置。
n,m預設值為1，可省略。
EX:
SQL> SELECT INSTR('abcdefgabcdefg', 'cd') "INSTR 範例" FROM dual;
INSTR 範例
----------
3

14. LENGTH(string)

計算字串長度
EX:
SQL> SELECT LENGTH('abcedfg') "LENGTH 範例" FROM dual;
LENGTH 範例
-----------
7

15. ASCII(char)

取得字元的ascii碼
EX:
SQL> SELECT ASCII('A') "ASCII 範例" FROM dual;
ASCII 範例
----------
65

閱讀全文...

Oracle內建常用數字函數

Oracle內建常用數字函數：
CEIL, FLOOR, ROUND, TRUNC, ABS, MOD.

1. CEIL(n)

傳回 > n 或 = n 的最小整數
EX:
SQL> SELECT CEIL(2.01) FROM DUAL;

CEIL(2.01)
----------
3

SQL> SELECT CEIL(-2.01) FROM DUAL;

CEIL(-2.01)
-----------
-2

2. FLOOR(n)

傳回 < n 或 = n 的最大整數
EX:
SQL> SELECT FLOOR(2.5) FROM DUAL;

FLOOR(2.5)
----------
2

SQL> SELECT FLOOR(-2.5) FROM DUAL;

FLOOR(-2.5)
-----------
-3

3. ROUND(n[,m])

對n值做四捨五入，m表示由小數點前後第幾位開始四捨五入，m需為整數，預設值為0
EX:
SQL> SELECT 3.1415 數值, ROUND(3.1415, 2) FROM DUAL;

數值 ROUND(3.1415,2)
---------- ---------------
3.1415 3.14

SQL> SELECT 14.99 數值, ROUND(14.99, -1) FROM DUAL;

數值 ROUND(14.99,-1)
---------- ---------------
14.99 10

4. TRUNC(n[,m])

將n值由小數點前後幾位開始無條件捨去，m可省略，需為整數，預設為0
EX:
SQL> SELECT 3.1415 數值, TRUNC(3.1415, 2) FROM DUAL;

數值 TRUNC(3.1415,2)
---------- ---------------
3.1415 3.14

5. ABS(n)

取得n的絕對值
EX:
SQL> SELECT ABS(-5) FROM DUAL;

ABS(-5)
----------
5

SQL> SELECT ABS(3.1415) FROm DUAL;

ABS(3.1415)
-----------
3.1415

6. MOD(m,n)

取得m除以n後的餘數
EX:
SQL> SELECT MOD(5, 3) FROM DUAL;

MOD(5,3)
----------
2

SQL> SELECT MOD(8, 4) FROM DUAL;

MOD(8,4)
----------
0

閱讀全文...

SQL*PLUS環境指令

Oracle SQL*PLUS 環境指令常應用於shell script撰寫時，實在非常有用。
SET：設定目前SQL*PLUS使用環境
SHOW：察看目前SQL*PLUS使用環境
STORE：儲存目前SQL*PLUS使用環境
STORE SET finename.sql [Create|Replace|append]

SET ECHO OFF (可壓抑start, @執行時，顯示SQL指令)
SET FEEDBACK OFF (不return查詢筆數)
SET HEADING OFF (不顯示column Heading)
SET LINESIZE 1024 (設定紀錄長度最大顯示)
SET NEWPAGE 0 (不換頁)
SET PAGESIZE 0 (每頁長度)
SET SPACE 0 (設定欄位間顯示間格)
SET VERIFY OFF (不顯示置換SQL指令)
閱讀全文...

2011年8月5日星期五

SQL*PLUS定義變數和顯示

SQL*PLUS操作應用
VARIABLE: Define sql*plus bind variable。
Note: 變數只能被用於PL/SQL Block中，且使用定義的變數，須加前置":"。
PRINT: Print out sql*plus bind variable

定義變數可用的資料類別：
NUMBBER
CHAR, CHAR(n): n = 1~255
VARCHAR2(n): n = 1~2000
REFCURSOR: Is a reference PL/SQL Cursor variable.

Sample:
$ sqlplus youruser/yourpass@yourdb;
SQL> var ss number
SQL> var aa varchar2(10)
SQL> var bb varchar2(10)
SQL> BEGIN (進入 anonymous PL/SQL Block)
2 :aa := 'Test';
3 :bb := '20110101';
4 :ss := pk_seq_pool.getSeqNo(:aa, :bb);
5 END ;
6 /

PL/SQL procedure successfully completed.
SQL> PRINT (顯示出在sqlplus定義變數內容，也可設定set autoprint on，這樣procedure執行完後會自動顯示內容)
SS
----------
1

BB
--------------------------------
20110101

AA
--------------------------------
Test

SQL> quit;
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
閱讀全文...

2011年8月1日星期一

Connect to Oracle with Java

Java 提供 JDBC API 來連接資料庫，但要連接到各式各樣的資料庫就要透過各廠商提供的JDBC DRIVER來處理了。

這篇是介紹Java如何連接到Oracle。相關的Oracle JDBC DRIVER請至Oracle下載。

Oracle提供了三種JDBC DRIVER：
1. JDBC Thin Driver (no local SQL*Net installation required/ handy for applets)
2. JDBC OCI for writing stand-alone Java applications
3. JDBC KPRB driver (default connection) for Java Stored Procedures and Database JSPs.
三種都提供相同的syntax and APIs。唯一要注意的是要找對應你的JDK版本來下載，不然會連不上。

Thin Driver
Thin Driver是百分百純JAVA打造，透過SOCKET(TCP/IP)的方式來連接資料庫。
有兩種URL syntax
舊的，只對SID有用：
jdbc:oracle:thin:@[HOST][:PORT]:SID
新的，則多了SERVICE NAME
jdbc:oracle:thin:[USER/PASSWORD]@[HOST][:PORT]:SID
jdbc:oracle:thin:[USER/PASSWORD]@//[HOST][:PORT]/SERVICE

例：
String url = "jdbc:oracle:thin:@//yourhost:yourport/orcl";
or
String url = "jdbc:oracle:thin:@yourhost:yourport:orcl";

如果不知道SID或SERVICE NAME，可以在tnsnames.ora檔案中找到它們。
XE =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = XE)
  )
)

ORCL =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
   (CONNECT_DATA =
     (SID = ORCL)
   )
)

範例(ConnThin.java)：
import java.sql.*;

class ConnThin {
  public static void main (String args []) {
    Connection conn = null;

    try {
      // Load the JDBC driver
      String driverName = "oracle.jdbc.driver.OracleDriver";
      Class.forName(driverName);

      // Create a connection to the database
      String serverName = "yourhost";
      String portNumber = "yourport";
      String sid = "yourdatabase";
      String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
      String username = "username";
      String password = "password";
      conn = DriverManager.getConnection(url, username, password);
      System.out.println("Database connected.");

    } catch (ClassNotFoundException e) {
      // Could not find the database driver
      System.err.println(e.getMessage());
    } catch (SQLException e) {
      // Could not connect to the database
      System.err.println(e.getMessage());
    }
    finally
    {
      try
      {
        if(conn != null)
          conn.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
  }
}

OCI Driver
OCI Driver是透過Oracle Call Interface來連接Oracle。
底下為Oracle提供的範例：
import java.sql.*;
class dbAccess {
  public static void main (String args []) throws Exception
  {
    Class.forName ("oracle.jdbc.OracleDriver");

    Connection conn = DriverManager.getConnection
      ("jdbc:oracle:oci8:@hostname_orcl", "scott", "tiger");
         // or oci7 @TNSNames_Entry, userid, password
    try {
      Statement stmt = conn.createStatement();
    try {
      ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
    try {
      while (rset.next())
        System.out.println (rset.getString(1)); // Print col 1
    } finally {
      try { rset.close(); } catch (Exception ignore) {}
    }
    } finally {
      try { stmt.close(); } catch (Exception ignore) {}
    }
    } finally {
      try { conn.close(); } catch (Exception ignore) {}
    }
  }
}

KPRB driver
KPRB driver
KPRB Driver是透過資料庫現有或預設的SESSION來連線，不需要額外的URL、USERNAME and PASSWORD。

底下為Oracle提供的範例：
import java.sql.*;
class dbAccess {
  public static void main (String args []) throws SQLException
  {
    Connection conn = (new oracle.jdbc.OracleDriver()).defaultConnection();

    try {
      Statement stmt = conn.createStatement();
    try {
      ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
    try {
      while (rset.next())
        System.out.println (rset.getString(1)); // Print col 1
    } finally {
      try { rset.close(); } catch (Exception ignore) {}
    }
    } finally {
      try { stmt.close(); } catch (Exception ignore) {}
    }
    } finally {
      try { conn.close(); } catch (Exception ignore) {}
    }
  }
}

雖然Oracle提供了三種JDBC Driver，但最常用還是以Thin Driver為主，準確的提供資料庫連接資訊就可以透過SOCKET來連接，簡單明瞭。

閱讀全文...

2011年7月13日星期三

SQL 語法如何將多欄位查詢結果合併成一個字串

不常用的東西，果然會忘記，今天剛好有人問起如何將多欄位查詢結果合併成一個字串，努力回想下，想起來了，果然可以，順便做一下紀錄，當作備忘。

測試資料：
Table: usrdata
Field: id, firstname, lastname
Data: 0,'Java','Sun' ; 1, 'java','oracle'

Oracle Database:
SQL> SELECT firstname||lastname FROM USRDATA;
firstname||lastname
-----------------------------
JavaSun
javaoracle

看起來在Oracle環境下是可行的。朋友使用的資料庫為PostgreSQL，驗證也是可行的。

本來事情已解決，但是空閒下來時，就想說MySQL也來試試看，結果....失敗了...
mysql> select firstname||lastname from usrdata;
firstname||lastname
---------------------
0
0

查了一下MySQL手冊(http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat)
mysql> select concat(firstname, lastname) from usrdata;
concat(firstname, lastname)
-----------------------------
JavaSun
javaoracle

嘿嘿，解決了，繼續來驗證微軟的資料庫(SQL Server, access)，沒記錯的話，關鍵字是(&)
select firstname & lastname from usrdata;
firstname & lastname
-----------------------------
JavaSun
javaoracle

總結：
每個資料庫在執行SQL語法時，字串連結的處理都不太一樣。
Oracle和PostgreSQL使用符號(||)。
MySQL使用CONCAT(col1, col2, ...)。
SQL Server使用符號(&)。

閱讀全文...

2010年10月12日星期二

Connect to multiple database with Java

這應該是常會遇到的狀況，當要從一個資料庫轉出資料到另一種資料庫時，如果import、export不符合使用，就必須手動來撰寫符合這樣需求的工具了。

Java是透過JDBC來連接資料庫，所以只要找到資料庫適當的driver，並透過Class.forName method來載入和註冊jdbc driver，就可以操作資料庫了。

下面是一個從Oracle轉出資料，再將資料轉入SQLite的例子(使用前請先將相關資料庫資料和SQL語法改成符合你所需的)

。

Oracle2SOLite.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

// http://www.oracle.com/technetwork/indexes/downloads/index.html
// JDBC Driver->ojdbc14.jar
// http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC
// JDBC Driver->sqlite-jdbc-3.7.3.jar

public class Oracle2SQLite {
    public static void main(String[] args) {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@ip:port:name";
        String user = "user";
        String password = "pwd";

        String driver1 = "org.sqlite.JDBC";
        String url1 = "jdbc:sqlite:D:/Work/sample.db";

        Connection conn = null;
        Connection connection = null;
        Statement stmt = null;
        Statement statement = null;

        System.out.println("***** Start *****");
        try {
            System.out.println("1. connect to " + url);
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
            stmt = conn.createStatement();

            System.out.println("2. connect to " + url1);
            Class.forName(driver1);
            connection = DriverManager.getConnection(url1);
            statement = connection.createStatement();

            StringBuffer sqlStr = new StringBuffer();
            // 組合相關 Select 語法
            sqlStr.append("SELECT * FROM Sample");

            System.out.println("3. query");
            StringBuffer sqlOut = new StringBuffer();
            ResultSet result = stmt.executeQuery(sqlStr.toString());
            System.out.println("4. insert");
            while(result.next()) {
                sqlOut.delete(0, sqlOut.length());
                // 組合 Insert 語法
                sqlOut.append("INSERT INTO TxnData VALUES('XXX', 'YYY', 'ZZZ')");

                statement.executeUpdate(sqlOut.toString());
            }
        }
        catch(ClassNotFoundException e) {
            System.out.println("找不到驅動程式: " + e.getMessage());
            e.printStackTrace();
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
        finally {
            if(stmt != null) {
                try {
                    stmt.close();
                }
                catch(SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null) {
                try {
                    conn.close();
                }
                catch(SQLException e) {
                    e.printStackTrace();
                }
            }

            if(statement != null) {
                try {
                    statement.close();
                }
                catch(SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection != null) {
                try {
                    connection.close();
                }
                catch(SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

閱讀全文...

訂閱：文章 (Atom)

網頁

2013年11月11日 星期一

2013年1月9日 星期三

2012年5月16日 星期三

2011年8月16日 星期二

2011年8月15日 星期一