# 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'


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

# 輸入字元:id
SELECT YY FROM NCA_PLAN_EXAM_MAN WHERE YY = :YY

--役男報名資料
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

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)