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,"9,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




--##字符函数,大小写处置函数
SELECT LOWER("ABcDe") 
FROM dual;