Oracle

Oracle

日期

參考資料:(https://bbs.csdn.net/topics/70502638)

to_date('2005-08-01','yyyy-mm-dd')

狀況1

參考資料:(http://programdolt.iteye.com/blog/1186690)

select * from table where endDate>? and endDate<?

sql修改成这样：

select * from table where endDate>to_date(?,’yyyymmddhh24miss’) and endDate<to_date(?,’yyyymmddhh24miss’);

然后将传入参数格式化成对应格式的字符串在传入，这样由Oracle将字符串转成Date类型，就很顺利的走索引区间扫描，效率最高。

ORA-01818

可能同時用到 AM/PM 和 HH24 要擇一使用

ORA-01861

參考資料:(http://horace1123.pixnet.net/blog/post/26551354-ora-01861-oracle%E6%97%A5%E6%9C%9F%E6%A0%BC%E5%BC%8F%E9%8C%AF%E8%AA%A4%E8%A8%8A%E6%81%AF)

如何實作MSSQL中的TOP()

參考資料:(https://jackypan1989.wordpress.com/2012/08/08/sql-學習筆記-oracle-top-1-top-n-之語法/)

找出表格內的前100筆資料 TOP(100)

select *
from table
where rownum < 100

倘若想要搜尋結果(where) 後的100筆結果，就要外面再包一層

select *
from
    (select *
    from table
    where 條件
    order by field desc)
where rownum < 100

別名

Oracle 似乎表格的別名不能加 AS，但是欄位的名稱可以加AS

select c.COURSE_NAME as 課程名稱 , sc.COURSE_SCORE as 分數 ,s.stud_name as 學生姓名, s.STUD_TEL as 電話
from student_course sc
join course c on sc.COURSE_NO = c.COURSE_NO
join student s on sc.STUD_NO = s.STUD_NO
where c.COURSE_NAME = '系統分析' and sc.COURSE_SCORE >= 70;

Primary Key vs Unique Index

參考資料:(https://tomkuo139.blogspot.com/2010/03/oracle-database-primary-key-unique.html)

Primary Key = Unique Index + Not null 若只有Unique Index 是可以為null。

Table 中，最多只能有一個 Primary Key, 但能擁有多個 Unique Index。

Primary Key 的字串不能有空格，Unique Index 的可以包含空格。

SQL語法

參考資料:(https://www.techonthenet.com/oracle/unique.php)

使用CREATE TABLE 創建 unique constraint

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)
);

階層式查詢

參考資料:(https://dotblogs.com.tw/jeff-yeh/2009/05/20/8489) 參考資料:(https://www.cnblogs.com/ZHF/archive/2008/09/10/1288101.html)

Select UserNo,UserName 
from
(
        Select UserNo,UserName,UserActive from UserMgrInfo
        Start with UserNo='Z001'
        Connect by UserNo=prior UserMgrNo
)
Where UserActive='1'

如果where放在子查詢裡面，會中斷 (?,待實作)

SELECT *
FROM table START WITH (條件1) 根的資料 
CONNECT BY (條件2) 子欄位= PRIOR 父欄位
--CONNECT BY (條件2) PRIOR 父欄位 = 子欄位 同上，反正PRIOR 放在父欄位前面
WHERE (條件3)

Primary Keys

參考資料:(https://www.techonthenet.com/oracle/primary_keys.php)

注意

primary key 不能超過32 columns 可以在 create table 或 alter table 中定義primary key

CREATE

create table 的同時，建立primary key

--Example:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n)
);

CREATE TABLE supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

如果Table 已經建出來了

使用 alter table 來建立 primary key

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);

ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name);

DROP

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

ALTER TABLE supplier
DROP CONSTRAINT supplier_pk;

DISABLE

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

ALTER TABLE supplier
DISABLE CONSTRAINT supplier_pk;

ENABLE

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

ALTER TABLE supplier
ENABLE CONSTRAINT supplier_pk;

RENAME

ALTER INDEX primary RENAME TO new_primary;
ALTER TABLE table RENAME CONSTRAINT primary TO new_primary_name;

INDEX

CREATE

一般建索引

CREATE INDEX idx_name ON tb_name(column_1, column_2, column_3);

基於函數的索引

參考資料:(https://bbs.csdn.net/topics/70502638)

create index idx_name on tbname(to_date(column,'yyyy-mm-dd'));

RENAME

ALTER INDEX [schema.]index RENAME TO  new_index_name;

ALTER INDEX [schema.]index PARTITION partition RENAME TO  new_index_name;

TABLE

RENAME

RENAME TABLE table TO new_table_name;

Column

add Column

新增一個型態為 varchar2 的欄位，而且要怎麼決定裡面的長度，是根據字元 & 位元

--位元
ALTER TABLE TableName ADD tmp_col VARCHAR2(100 Byte);

--字元
ALTER TABLE TableName ADD tmp_col VARCHAR2(100 Char);

modify size 修改欄位長度

參考資料:(https://blog.xuite.net/akf3dsmax8/wretch/192494927-Oracle+%E4%BF%AE%E6%94%B9%E6%AC%84%E4%BD%8D%E9%95%B7%E5%BA%A6%EF%BC%8C%E4%BF%9D%E7%95%99%E5%8E%9F%E8%B3%87%E6%96%99)

倘若要修改data type 則必須要用下列作法

(先搬走 > 更改 > 搬回來)

ALTER TABLE TableName ADD tmp_col VARCHAR2(4000);
UPDATE TableName SET tmp_col = ColumnName ; 
UPDATE TableName SET ColumnName = null; 
ALTER TABLE TableName MODIFY (ColumnName VARCHAR2(4000)); 
UPDATE TableName SET ColumnName = tmp_col; 
ALTER TABLE TableName DROP column tmp_col;

倘若是直接改大的話，就直接改就好

ALTER TABLE TableName 
MODIFY (ColumnName VARCHAR2(4000));

CLOB

CLOB 不能被拿來比較 UNION 和 MINUS 都不能用 UNION ALL 可以用

CREATE VIEW SCHEMA.VIEW_NAME
AS
(
    SELECT * FROM TABLENAME
    UNION ALL
    SELECT * FROM TABLENAME_HIS
)
;

ALTER VIEW

(參考資料:https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4004.htm)

CREATE OR REPLACE FORCE VIEW VIEW_NAME
(
   ColumnName1 , ColumnName2 , ColumnName3 , ColumnName4,
   ColumnName5 , ColumnName6
)
AS
   SELECT A.ColumnName1 , A.ColumnName2, A.ColumnName3, A.ColumnName4,
          B.ColumnName5, B.ColumnName6
     FROM TABLENAME1 A, TABLENAME2 B
    WHERE A.ColumnName1 = B.ColumnName1;