Oracle基礎函數及對象示例學習總結
--##字符函數,大小寫處理函數
SELECT LOWER('ABcDe')
FROM dual; -- abcde，全部轉換為小寫
SELECT UPPER('ABcDe')
FROM dual; -- ABCDE，全部轉換為大寫
SELECT INITCAP('aBcDe')
FROM dual; -- Abcde，首字母大寫
--##字符函數,字符串處理函數
SELECT SUBSTR('ABcDe',2,2)
FROM dual; -- Bc，截取字符串
SELECT SUBSTR('ABcDe',2)
FROM dual; -- BcDe，截取字符串
SELECT SUBSTR('ABcDe',-3,2)
FROM dual; -- cD，截取字符串
SELECT INSTR('ABcDefgdDh','D',3,2)
FROM dual; -- 9，從第三個字符串開始查詢第二個字符D的位置
SELECT INSTR('ABcDefgdDh','D')
FROM dual; -- 4，從第一個字符串開始查詢第一個字符D的位置
SELECT LPAD('ABcDe',10,'D')
FROM dual; -- DDDDDABcDe，左側不足補字符串D
SELECT LPAD('ABcDe',3,'D')
FROM dual; -- ABc
SELECT RPAD('ABcDe',10,'D')
FROM dual; -- ABcDeDDDDD，右側不足補字符串D
SELECT RPAD('ABcDe',3,'D')
FROM dual; -- ABc
SELECT REPLACE('ABcDeEe','e',8)
FROM dual; --ABcD8E8，替換字符
SELECT LENGTH('ABcDe')
FROM dual; -- 5，計算字符串長度
SELECT CONCAT('Hello','World')
FROM dual; -- HelloWorld，連接字符串
SELECT TRIM('H' FROM 'HelloWorld')
FROM dual; --elloWorld，去除首字母
SELECT TRIM('d' FROM 'HelloWorld')
FROM dual; --HelloWorl，去除尾字母
SELECT TRIM('' FROM 'HelloWorld')
FROM dual; --，隱藏字符
--數字函數
SELECT ROUND(245.1234) FROM dual; --245
SELECT ROUND(245.1234,2) FROM dual; --245.12
SELECT ROUND(245.6234,0) FROM dual; --246
SELECT ROUND(245.1234,-2) FROM dual; --200
SELECT ROUND(255.1234,-2) FROM dual; --300
SELECT ROUND(-245.1234,2) FROM dual; -- -245.12
SELECT ROUND(-245.6234,0) FROM dual; -- -246
SELECT ROUND(-245.1234,-2) FROM dual; -- -200
SELECT ROUND(-255.1234,-2) FROM dual; -- -300
SELECT TRUNC(245.1234,2) FROM dual; -- 245.12
SELECT TRUNC(245.6234,0) FROM dual; -- 245
SELECT TRUNC(245.1234,-2) FROM dual; -- 200
SELECT TRUNC(255.1234,-2) FROM dual; -- 200
SELECT MOD(1600,300) FROM dual; -- 100
SELECT MOD(1500,300) FROM dual; -- 0
--日期函數
SELECT SYSDATE
FROM dual; -- 2012-05-12 12:50:18 ，當前日期
SELECT MONTHS_BETWEEN('15-4月-2012','01-1月-2012')
FROM dual; -- 3.45161290322581，兩個日期之間的月數
SELECT ADD_MONTHS(SYSDATE,2)
FROM dual; --2012-07-12 14:23:32，添加月份到日期字段中
SELECT NEXT_DAY('15-4月-2012',3)
FROM dual; --2012-04-17 ，添加天數到日期字段中
SELECT NEXT_DAY(SYSDATE,'星期一')
FROM dual;
SELECT LAST_DAY(SYSDATE)
FROM dual; --2012-05-31 14:25:22，本月份的最後一天
SELECT ROUND(SYSDATE,'month')
FROM dual; --2012-05-01，月份四捨五入
SELECT ROUND(SYSDATE)
FROM dual; --2012-05-13
SELECT TRUNC(SYSDATE,'month')
FROM dual; --月份截取
SELECT TRUNC(SYSDATE,'day')
FROM dual;
--轉化函數
SELECT '123' + 123 FROM dual; --246，字符串隱式轉化
SELECT TO_CHAR(123456.6543,'L099,999.00') AS "Number Format"
FROM dual; --若是常量數字，需在前加0
SELECT TO_CHAR(123456.6543,'$099,999.00') AS "Number Format"
FROM dual;
SELECT TO_CHAR(SYSDATE,'DAY DD-MON-YYYY') AS "Data Format"
FROM dual; --星期二 15-5月 -2012
SELECT TO_DATE('12-5-2012','DD-MM-YYYY')
FROM dual; --格式字符串必須有。沒有意義？
SELECT TO_NUMBER('5432.1234')
FROM dual;
SELECT TO_NUMBER('15,155,100.56','999,999,999.00')
FROM dual; --變量字符串與格式字符串的格式必須一致
--常規函數
SELECT NVL(NULL,0) FROM dual; -- 0
SELECT NVL(NULL,'N/A') FROM dual; -- N/A
SELECT NVL(123,0) FROM dual; -- 123
SELECT NVL('學生','N/A') FROM dual; -- 學生
SELECT NVL2(NULL,0,1) FROM dual; -- 1
SELECT NVL2(123,0,1) FROM dual; -- 0
SELECT NULLIF(1,1) FROM dual; -- NULL
SELECT NULLIF(2,1) FROM dual; -- 2
SELECT NULLIF('好','不好') FROM dual; -- 好
SELECT COALESCE(NULL,NULL,'不好','好') FROM dual; -- 不好，參數必須為同一數據類型
--DECODE示例
DECODE
SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary) REVISED_SALARY
FROM employees;
--CASE THEN示例
CASE
WHEN THEN
WHEN THEN
ELSE
END
SELECT last_name, job_id, salary,
CASE job_id
WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary
END "REVISED_SALARY"
FROM employees;
--##分組函數
AVG
COUNT
MAX
MIN
SUM
STDDEV
VARIANCE
/*
IN、ANY、ALL運算符
IN 等於列表中的任意一個。
< ANY 意味著低於最高值；> ANY 意味著高於最低值；= ANY 等同於 IN。
< ALL 意味著低於最低值；> ANY 意味著高於最高低值。
示例：
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
*/
--創建腳本示例
INSERT INTO departments (department_id, department_name, location_id)
VALUES (&"Department Id",'&"Department Name"',&Location_Id);
/*
MERGE語句
提供有條件地在數據表中更新或插入數據的功能。
如果該行存在就執行UPDATE,如果是新行則執行INSERT
示例：在COPY_EMP表中插入或更新行，以便與EMPLOYEES表匹配
MERGE INTO copy_emp c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
...
c.department_id = e.department_id
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,
e.email, e.phone_number, e.hire_date, e.job_id,
e.salary, e.commission_pct, e.manager_id,
e.department_id);
*/
--顯式事務控制語句
COMMIT
ROLLBACK
SAVEPOINT name_values
ROLLBACK TO SAVEPOINT name_values
--ALTER TABLE語句示例
ALTER TABLE dept80
ADD (job_id VARCHAR2(9));
ALTER TABLE dept80
MODIFY (last_name VARCHAR2(30));
ALTER TABLE dept80
DROP COLUMN job_id;
--SET UNUSED標記不可用與刪除表全部不可用字段
ALTER TABLE emp SET UNUSED(department_id)
ALTER TABLE emp DROP UNUSED COLUMNS; --不能刪除某一個不可用字段，若刪除將全部刪除。
--RENAME重命名對象
RENAME employees2 TO emp
--COMMENT向表中添加字段
COMMENT ON TABLE employees
IS 'Employee Information';
--約束類型
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
CREATE TABLE employees(
employee_id NUMBER(6),
first_name VARCHAR2(20),
...
job_id VARCHAR2(10) NOT NULL,
CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID));
CREATE TABLE employees(
employee_id NUMBER(6),
email VARCHAR2(25),
...
department_id NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_email_uk UNIQUE(email));
CREATE TABLE employees(
employee_id NUMBER(6),
hire_date DATE NOT NULL,
...
CONSTRAINT emp_email_uk UNIQUE(email));
CREATE TABLE employees(
employee_id NUMBER(6),
...
salary NUMBER(2)
CONSTRAINT emp_salary_min CHECK (salary > 0),...
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk
FOREIGN KEY(manager_id)
REFERENCES employees(employee_id);
ALTER TABLE employees
DROP CONSTRAINT emp_manager_fk;
ALTER TABLE departments
DROP PRIMARY KEY CASCADE; --刪除departments表上的PRIMARY KEY約束，並刪除employees.department_id列上關聯的FOREIGN KEY約束。
ALTER TABLE employees
DISABLE CONSTRAINT emp_emp_id_pk CASCADE; --禁用約束
ALTER TABLE employees
ENABLE CONSTRAINT emp_emp_id_pk; --啟用約束
/*
級聯約束
CASCADE CONSTRAINTS子句是和 DROP COLUMN子句一起使用的。
CASCADE CONSTRAINTS子句會刪除涉及到在已刪除列上定義的主鍵或唯一關鍵字的所有引用完整性約束。
CASCADE CONSTRAINTS子句還將刪除在已刪除列上定義的鄋多列約束。
CREATE TABLE test1 (
pk NUMBER PRIMARY KEY,
fk NUMBER,
col1 NUMBER,
col2 NUMBER,
CONSTRAINT fk_constraint FOREIGN KEY (fk) REFERENCES test1,
CONSTRAINT ck1 CHECK (pk > 0 and col1 > 0),
CONSTRAINT ck2 CHECK (col2 > 0));
ALTER TABLE test1
DROP (pk) CASCADE CONSTRAINTS;
ALTER TABLE test1
DROP (pk, fk, col1) CASCADE CONSTRAINTS;
*/
--視圖使用WITH CHECK OPTION子句
CREATE OR REPLACE VIEW empvu20
AS
SELECT * FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck --引用約束empvu20_ck
--視圖拒絕DML操作
WITH READ ONLY
--排序TOP-N分析：偽列ROWNUM關鍵字
SELECT ROWNUM as RANK, last_name, salary
FROM (SELECT last_name,salary FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 3;
--序列
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10 --步長
START WITH 120 --開始位
MAXVALUE 9999 --結束位
NOCACHE --
NOCYCLE; --達到結束位不重新開始循環
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
--序列中的偽列,
NEXTVAL
CURRVAL
INSERT INTO departments(department_id,department_name,location_id)
VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500);
SELECT dept_deptid_seq.CURRVAL
FROM dual;
--索引
CREATE INDEX emp_last_name_idx
ON employees(last_name);
--同義詞
CREATE SYNONYM emp
FOR employees;
CREATE PUBLIC SYNONYM dept
FOR departments
