# 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;
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://brianwu.gitbook.io/brian/zi-liao-ku/oracle.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.