Oracle 欄位合併

SELECT T.SDATE , T.YM ||'/'|| N.YM AS YM from DAILY

select userid, concat(surname,name) as your_name from user;

SQLLDR

create user User1 identified by "PassWord";
grant connect,resource to User1;
GRANT CREATE VIEW TO User1;

-------------------------------
sqlldr userid=user/pass@ctrl control=load.ctl data=data.txt log=mylog.log bad=mybad.bad discard=myDiscard.log Errors=20000000 direct=true parallel=true
---------------------------
LOAD DATA
--CHARACTERSET AL32UTF8--原始碼要判斷是否為UTF8
APPEND
INTO TABLE tablename
fields terminated by ","
--optionally enclosed by '"'
(
  COM "trim(:COM)",--1
  SDATE "TO_DATE(:SDATE,'YYYY-MM-DD HH24:MI:SS')",--7
  NOTE filler  
)
--"to_date(:STARTTIME1||:STARTTIME發佈文章, 'yyyy/mm/dd,hh24:mi:ss')"

SQLLDR POSITION

sqlldr userid=user/pass@ctrl control=load.ctl log=load.log
---------------------------------------------------
Load data
CHARACTERSET ZHT16MSWIN950
infile 'hello.txt'
Append
into table TableName
fields terminated by ' '
(
IDNO       POSITION(1:10) ,
NAME     POSITION(11:20)
)

Oracle insert

http://psoug.org/reference/insert.html

INSERT SELECT

INSERT INTO table1
SELECT col1, col2, col3
FROM table2 where col1 ='a';

SELECT * FROM table1;

Oracle em

http://localhost:1158/em/console/logon/logon

修改使用者tuser改用TABLESPACE

ALTER USER "tuser" DEFAULT TABLESPACE "USER01"

增加資料庫空間

ALTER TABLESPACE "USER01" ADD DATAFILE 'c:\Oracle\USER01.DBF' SIZE 30G

增加temp空間

ALTER TABLESPACE "TEMP" ADD TEMPFILE 'c:\Oracle\TEMP02.DBF' SIZE 30G

CREATE TABLE "PUSER"."iis"

( "SDATE" VARCHAR2(20),

"STIME" VARCHAR2(20)

) TABLESPACE "USERS"

Oracle Date function to_date()

--更改日期顯示的格式
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD';

--找日期
select * from ph
where to_char(sdate,'YYYY-MM-DD') = '2009-06-02'

--顯示日期
select to_char(sdate,'YYYY-MM-DD') from ph
group by to_char(sdate,'YYYY-MM-DD')

--between
select * from ph
where sdate between to_date('2009-04-19 23:40:00','YYYY/MM/DD HH24:MI:SS')
and to_date('2009-04-20 00:40:10','YYYY/MM/DD HH24:MI:SS')

--日期加一天
select sysdate+1 from dual
-> 2009-06-18 15:37:29


select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') FROM DUAL
-> 2007/09/20 15:24:13

--得到日期差
select trunc(sysdate - to_date('20070901','yyyymmdd')) aa from dual;

--得到小時差
select trunc((sysdate - to_date('20070901','yyyymmdd'))*24) aa from dual;

--得到分鐘差
select trunc((sysdate - to_date('20070901','yyyymmdd'))*24*60) aa from dual;

--得到秒差
select trunc((sysdate - to_date('20070901','yyyymmdd'))*24*60*60) aa from dual;

--日期加小時
select to_char(sysdate+2/24,'YYYY/MM/DD HH24:MI:SS') aa from dual;

--日期加分鐘
select to_char(sysdate+10/1440,'YYYY/MM/DD HH24:MI:SS') aa from dual;

--日期加秒鐘
select to_char(sysdate+10/86400,'YYYY/MM/DD HH24:MI:SS') aa from dual;



常用之日期格式

日期格式 說明
------------------------------------------------------------------------
YYYY/MM/DD -- 年/月/日
YYYY -- 年(4位)
YYY -- 年(3位)
YY -- 年(2位)
MM -- 月份
DD -- 日期
D -- 星期
-- 星期日 = 1 星期一 = 2 星期二 = 3
-- 星期三 = 4 星期四 = 5 星期五 = 6 星期六 = 7

DDD -- 一年之第幾天
WW -- 一年之第幾週
W -- 一月之第幾週
YYYY/MM/DD HH24:MI:SS -- 年/月/日 時(24小時制):分:秒
YYYY/MM/DD HH:MI:SS -- 年/月/日 時(非24小時制):分:秒
J -- Julian day，Bc 4712/01/01 為1
RR/MM/DD -- 公元2000問題

Oracle update

2個資料表間的更新
UPDATE T1 P
SET col1=
(SELECT col1 FROM T2 S WHERE P.col2 = S.col2)
-----------------------

UPDATE phone p
SET address =
(SELECT address FROM addr a WHERE p.address = address)

Oracle substring

select a, substr(concat(trim(a),' '),-5,4) as substr from tablename;

A SUBSTR
12345 2345
2345 2345
22345 2345

select * from tablename where substr(concat(trim(a),' '),-5,4) = '2345';

12345
2345
22345

select col, substr(trim(col),-4,4) from tablename;
12345,2345
22345,2345
2345,2345

Oracle Create User

1. create user 帳號 identified by "密碼"
2. grant connect,resource to 帳號;
   

Oracle JDBC Query

driver

1. ocrs12.jar
   
2. ojdbc14.jar
   
3. ojdbc14_g.jar

下載http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html


public void QueryOracle()
{
PreparedStatement ptmt=null;;
Connection conn=null;;
ResultSet rs;
String DB_IP="127.0.0.1";
String DB_SID="";
String DB_User="";
String DB_Password="";
String Column="";
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection
("jdbc:oracle:thin:@"+DB_IP+":1521:"+DB_SID,DB_User,DB_Password);
ptmt = conn.prepareStatement("select * from unaddr group by bid");
rs = ptmt.executeQuery();
while(rs.next()){
Column = rs.getString("Column");
}
ptmt.clearParameters();
} catch (ClassNotFoundException e) {
System.out.println("找不到驅動程式");
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (ptmt != null) {
try {
ptmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}//end


Oracle 隨機取資料

Select *
From (
Select * From [tablename]
Order By dbms_random.random
)
Where Rownum <= 1;--看要取表列前幾筆

-----------------
產生亂數
select dbms_random.random from dual

資料庫連線軟體 - Oracle SQLDeveloper

下載 http://www.oracle.com/technology/software/products/sql/index.html
可以連Oracle ,Access