Oracle基礎函數及對象樣本學習總結，oracle樣本

Oracle基礎函數及對象樣本學習總結，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，左側不足補字串DSELECT LPAD('ABcDe',3,'D')FROM dual; --   ABcSELECT RPAD('ABcDe',10,'D')FROM dual; --   ABcDeDDDDD，右側不足補字串DSELECT RPAD('ABcDe',3,'D')FROM dual; --   ABcSELECT 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; --245SELECT ROUND(245.1234,2) FROM dual;  --245.12SELECT ROUND(245.6234,0) FROM dual;  --246SELECT ROUND(245.1234,-2) FROM dual;  --200SELECT ROUND(255.1234,-2) FROM dual;  --300SELECT ROUND(-245.1234,2) FROM dual;  --  -245.12SELECT ROUND(-245.6234,0) FROM dual;  --  -246SELECT ROUND(-245.1234,-2) FROM dual;  -- -200SELECT ROUND(-255.1234,-2) FROM dual;  -- -300SELECT TRUNC(245.1234,2) FROM dual;  --   245.12SELECT TRUNC(245.6234,0) FROM dual;  --   245SELECT TRUNC(245.1234,-2) FROM dual;  --  200SELECT TRUNC(255.1234,-2) FROM dual;  --  200SELECT MOD(1600,300) FROM dual;       --  100SELECT 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-13SELECT 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;                                --若是常量數字，需在前加0SELECT 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月 -2012SELECT 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;     --    0SELECT NVL(NULL,'N/A') FROM dual; --    N/ASELECT NVL(123,0) FROM dual;      --    123SELECT NVL('學生','N/A') FROM dual; --    學生SELECT NVL2(NULL,0,1) FROM dual;  --    1SELECT NVL2(123,0,1) FROM dual;  --    0SELECT NULLIF(1,1) FROM dual;    --    NULLSELECT NULLIF(2,1) FROM dual;    --    2SELECT 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_SALARYFROM   employees;--CASE THEN樣本CASE  WHEN THEN  WHEN THEN  ELSEENDSELECT 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;--##分組函數AVGCOUNTMAXMINSUMSTDDEVVARIANCE/*IN、ANY、ALL運算子    IN 等於列表中的任意一個。    < ANY 意味著低於最高值；> ANY 意味著高於最低值；= ANY 等同於 IN。    < ALL 意味著低於最低值；> ANY 意味著高於最高低值。樣本：SELECT employee_id, last_name, job_id, salaryFROM   employeesWHERE  salary < ANY(SELECT salaryFROM   employeesWHERE  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_idWHEN 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);*/--明確交易控制語句COMMITROLLBACKSAVEPOINT name_valuesROLLBACK TO SAVEPOINT name_values--ALTER TABLE語句樣本ALTER TABLE dept80ADD (job_id VARCHAR2(9));ALTER TABLE dept80MODIFY (last_name VARCHAR2(30));ALTER TABLE dept80DROP 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 employeesIS 'Employee Information';--約束類型NOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECKCREATE 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 employeesADD CONSTRAINT  emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);ALTER TABLE employeesDROP CONSTRAINT emp_manager_fk;ALTER TABLE departmentsDROP PRIMARY KEY CASCADE; --刪除departments表上的PRIMARY KEY約束，並刪除employees.department_id列上關聯的FOREIGN KEY約束。ALTER TABLE employeesDISABLE CONSTRAINT emp_emp_id_pk CASCADE;   --禁用約束ALTER TABLE employeesENABLE 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 empvu20AS     SELECT * FROM employeesWHERE department_id = 20WITH 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 employeesORDER 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 CURRVALINSERT INTO departments(department_id,department_name,location_id)VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500);SELECT dept_deptid_seq.CURRVALFROM dual;--索引CREATE INDEX  emp_last_name_idxON employees(last_name);--同義字CREATE SYNONYM  empFOR  employees;CREATE PUBLIC SYNONYM  deptFOR departments