Oracle 開發常見問題

Oracle 開發常見問題

目錄

Oracle的number類型資料的插入和顯示

Oracle時間類型資料的插入和讀出顯示

Oracle自增欄位

【1】Oracle的number類型資料的插入和顯示

(1) 插入number類型的資料

將字串轉化為number類型資料再插入，使用 to_number 函數，實際上這裡不需要轉，會自動轉換。

SQL> insert into TerminalDevice values ( to_number('11111111111111111112'), '1', '1', '12345678901234567890','1','1','2','3','4','5','6','7','8', to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss'), to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss')
);

(2) 查詢顯示

將Number類型資料轉換為char，通過 to_char 函數。

SQL> select to_char(TerminalDevice_ID), TerminalDevice_InstallDate  from TerminalDevice;

TO_CHAR(TERMINALDEVICE_ID) TERMINALDEVICE
---------------------------------------- --------------
11111111111111111111 01-1月 -05
11111111111111111112 01-1月 -05
01-1月 -05

若不轉換，則會按科學計數法一樣顯示。

SQL> select TerminalDevice_ID, TerminalDevice_InstallDate  from TerminalDevice;

TERMINALDEVICE_ID TERMINALDEVICE
----------------- --------------
       1.1111E+19 01-1月 -05
       1.1111E+19 01-1月 -05

【2】Oracle時間類型資料的插入和讀出顯示

(1) 插入日期資料：

字元轉換為日期資料，字串20050101131420轉化為日期

SQL> insert into TerminalDevice (TerminalDevice_InstallDate) values ( to_date('2005-01-01 13:14:20', 'yyyy-MM-dd HH24:mi:ss') );

SQL> update TerminalDevice set TerminalDevice_InstallDate = to_date('20050101131420','yyyyMMddHH24miss')  where TERMINALDEVICE_ID = 11111111111111111111

SQL> select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;

TO_DATE('2005-
--------------
01-1月 -05

SQL> select to_date('20050101131420','yyyyMMddHH24miss') from dual;

SQL> select TerminalDevice_InstallDate from TerminalDevice;

TERMINALDEVICE
--------------
01-1月 -05

(2) 格式化顯示日期資料

日期資料轉化為字元，日期轉化為字串20050101131420

SQL> select to_char(TerminalDevice_InstallDate, 'yyyy-MM-dd HH24:mi:ss') as installDate from TerminalDevice;

INSTALLDATE
-------------------
2005-01-01 13:14:20

SQL> select to_char(TerminalDevice_InstallDate, 'yyyyMMddHH24miss') as installDate from TerminalDevice;

INSTALLDATE
--------------
20050101131420

24 小時的形式顯示出來要用 HH24
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;
to_date() function
1. 日期格式參數 含義說明 
D 一周中的星期幾 
DAY 天的名字，使用空格填充到 9 個字元 
DD 月中的第幾天 
DDD 年中的第幾天 
DY 天的簡寫名 
IW ISO 標準的年中的第幾周 
IYYY ISO 標準的四位年份 
YYYY 四位年份 
YYY,YY,Y 年份的最後三位，兩位，一位 
HH 小時，按 12 小時計 
HH24 小時，按 24 小時計 
MI 分 
SS 秒 
MM 月 
Mon 月份的簡寫 
Month 月份的全名 
W 該月的第幾個星期 
WW 年中的第幾個星期      1. 日期時間間隔操作 
目前時間減去 7 分鐘的時間 
select sysdate,sysdate - interval '7' MINUTE from dual
目前時間減去 7 小時的時間 
select sysdate - interval '7' hour from dual
目前時間減去 7 天的時間 
select sysdate - interval '7' day from dual
目前時間減去 7 月的時間 
select sysdate,sysdate - interval '7' month from dual
目前時間減去 7 年的時間 
select sysdate,sysdate - interval '7' year from dual
時間間隔乘以一個數字 
select sysdate,sysdate - 8 *interval '2' hour from dual
2. 日期到字元操作 
select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual
3. 字元到日期操作 
select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual
具體用法和上面的 to_char 差不多。 
4.TO_NUMBER 
使用TO_NUMBER函數將字元轉換為數字 
TO_NUMBER(char, ['格式'])
數字格式格式 
9 代表一個數字 
0 強制顯示0 
$ 放置一個$符 
L 放置一個浮動本地貨幣符 
. 顯示小數點 
, 顯示千位指示符

【3】Oracle自增欄位

Oracle中沒有像SQL Server那樣有一種資料類型是自增的整形。
Oracle的自增欄位通過sequence實現。

create sequence GOODS_GOODS_ID
minvalue 1
maxvalue 99999999999999999999
start with 1
increment by 1
nocache;

insert into GOODS(Goods_ID, Goods_Name, GOODS_COUNT, GOODS_UNIT, GOODS_PRICE, GOODS_DISCOUNTRATE, GOODS_MONEY, INVOICE_ID) values(GOODS_GOODS_ID.Nextval,'農夫烤雞',10,'只','15.0','0.80','120','12345678901234567890');

insert into GOODS(Goods_ID, Goods_Name, GOODS_COUNT, GOODS_UNIT, GOODS_PRICE, GOODS_DISCOUNTRATE, GOODS_MONEY, INVOICE_ID) values(GOODS_GOODS_ID.Nextval,'魔法圍巾',10,'條','100.0','0.80','800','12345678901234567890');

在oracle中sequence就是所謂的序號，每次取的時候它會自動增加，一般用在需要按序號排序的地方。 
1、Create Sequence 
你首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE許可權， 
CREATE SEQUENCE emp_sequence 
INCREMENT BY 1 -- 每次加幾個 
START WITH 1 -- 從1開始計數 
NOMAXvalue -- 不設定最大值 
NOCYCLE -- 一直累加，不迴圈 
CACHE 10; --設定緩衝cache個序列，如果系統down掉了或者其它情況將會導致序列不連續，也可以設定為---------NOCACHE
針對S_Depart建立的sequence如下：

create sequence S_S_DEPART
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;

一旦定義了emp_sequence，你就可以用CURRVAL，NEXTVAL 
CURRVAL=返回 sequence的當前值 
NEXTVAL=增加sequence的值，然後返回 sequence 值 
比如： 
emp_sequence.CURRVAL 
emp_sequence.NEXTVAL 

可以使用sequence的地方： 
- 不包含子查詢、snapshot、VIEW的 SELECT 語句 
- INSERT語句的子查詢中 
- NSERT語句的valueS中 
- UPDATE 的 SET中 

可以看如下例子： 

insert into S_Depart(departId,Departname,Departorder)values(S_S_Depart.Nextval,'12345',1);