---
# System prepended metadata

title: Oracle

---

# Oracle

### 1.oracle中的（+）是一种特殊的用法，（+）表示外连接，并且总是放在非主表的一方。
- 例如左外连接：
select A.a,B.a from A LEFT JOIN B ON A.b=B.b;
- 等价于
select A.a,B.a from A,B where A.b = B.b(+);
- 再举个例子，这次是右外连接：
select A.a,B.a from A RIGHT JOIN B ON A.b=B.b;
- 等价于
select A.a,B.a from A,B where A.b (+) = B.b;


# union
 Select ACC_TYPE, 'NULL' AS UNIT,ACCOUNT     From CA_ACCOUNT     Where ACCOUNT='N124431241'  union    select ACC_TYPE, REL_UNIT as UNIT,ACCOUNT      From CA_USER_TO_ROLE      Where ACCOUNT='91F284-ss'
 
 ![](https://i.imgur.com/uhKXXBb.png)


# left join
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
左側資料表的所有記錄都會加入到查詢結果中，右表沒有匹配記錄也會顯示。
![](https://i.imgur.com/Jwbd0V5.png)

# 輸入字元:id
SELECT YY  FROM NCA_PLAN_EXAM_MAN WHERE YY = :YY
![](https://i.imgur.com/gLG3VKd.png)


--役男報名資料
SELECT * FROM NCA_STUDENT_SIGNUP WHERE ACCOUNT='F126817118' 

SELECT * FROM NCA_STUDENT_RESUME_LOG WHERE ACCOUNT='F126817118' 
-- 役男資料
SELECT * FROM NCA_ACCOUNT_RESUME WHERE ACCOUNT='F126817118' 
-- 受訓資料
SELECT * FROM NCA_PERSON_TRAINING WHERE ACCOUNT='F126817118' 
-- 進入公司的資料
SELECT * FROM NCA_PERSON_WORKING WHERE ACCOUNT='F126817118' 

--單位帳號
SELECT * FROM NCA_ACCOUNT_UNIT WHERE ACCOUNT='101T0829' 

--核準役男
SELECT * FROM NCA_APPLY WHERE UNIT_ACCOUNT='90F367' 

--登入網站的帳號
SELECT * FROM NCA_ACCOUNT

--NCA培訓梯次
SELECT * FROM NCA_TRAINING_BATCH_PERIOD

-- 西元年與民國年的對應
SELECT DISTINCT TO_CHAR(START_DATE, 'YYYY') AS YEAR,YY FROM NCA_TRAINING_BATCH_PERIOD WHERE YY<>999
 ![](https://i.imgur.com/mRRBEj0.png)

 
SELECT YEAR-1911 AS ROC_YYY, YEAR||'0101' AS SD,  YEAR||'1231' AS ED,YEAR FROM (SELECT DISTINCT TO_CHAR(START_DATE, 'YYYY') AS YEAR,YY FROM NCA_TRAINING_BATCH_PERIOD WHERE YY<>999)
![](https://i.imgur.com/Wi2FsrH.png)


