# 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-之語法/](https://jackypan1989.wordpress.com/2012/08/08/sql-%E5%AD%B8%E7%BF%92%E7%AD%86%E8%A8%98-oracle-top-1-top-n-%E4%B9%8B%E8%AA%9E%E6%B3%95/))

找出表格內的前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;
```