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