-- 查看表空间物理文件、大小、使用率
SELECT B.FILE_NAME 物理文件名,
       B.TABLESPACE_NAME 表空间,
       B.BYTES / 1024 / 1024 大小M,
       (B.BYTES - SUM(NVL(A.BYTES, 0))) / 1024 / 1024 已使用M,
       SUBSTR((B.BYTES - SUM(NVL(A.BYTES, 0))) / (B.BYTES) * 100, 1, 5) 利用率
  FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
 WHERE A.FILE_ID = B.FILE_ID
 GROUP BY B.TABLESPACE_NAME, B.FILE_NAME, B.BYTES,B.USER_BYTES
 ORDER BY B.TABLESPACE_NAME;
 
-- 创建表空间 
CREATE TABLESPACE ODI_REP LOGGING
DATAFILE 'F:\APP\SENNEY\ORADATA\ORACLE11G\odi_rep01.dbf'
SIZE 32M AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;
-- 创建临时表空间
CREATE TEMPORARY TABLESPACE ODI_REP_TEMP
TEMPFILE 'F:\APP\SENNEY\ORADATA\ORACLE11G\odi_rep_temp01.Dbf'
SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

-- 创建数据库对象： 视图
CREATE OR REPLACE VIEW emp_v AS 
SELECT  employee_id, last_name, salary
FROM employees
WHERE department_id = 80;
-- 删除视图
DROP VIEW emp_v;

CREATE VIEW dept_sum_v(name, minsal, maxsal, avgsal) AS
SELECT d.department_name, MIN(e.salary), MAX(e.salary), AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;
-- 查询视图
SELECT * FROM dept_sum_v;
-- 删除视图
DROP VIEW dept_sum_v;

-- 创建数据库对象：序列
CREATE SEQUENCE dept_deptid_seq_1
INCREMENT BY 1000
START WITH 2000
MAXVALUE 5000
MINVALUE 1000
CYCLE
CACHE 3;

-- 查询数据库对象下一个值
SELECT dept_deptid_seq_1.NEXTVAL FROM DUAL;
-- 查询数据库对象当前值
SELECT dept_deptid_seq_1.currval FROM DUAL;
-- 序列使用示例
INSERT INTO departments (department_id, department_name, location_id)
VALUES(dept_deptid_seq_1.nextval,'Support', 2500);

-- 创建数据库对象： 索引
CREATE INDEX emp_last_name_idx ON employees(last_name);
-- 删除数据库对象：索引
DROP INDEX emp_last_name_idx;
/*
标准索引
CREATE INDEX index_name ON table_name (field_name)  TABLESPACE index_tbs;
唯一索引
CREATE UNIQUE INDEX index_name ON table_name (field_name)  TABLESPACE index_tbs;
组合索引
CREATE INDEX index_name ON table_name (field_name1,field_name2..field_namen,)  TABLESPACE index_tbs;
位图索引
CREATE BITMAP INDEX index_name ON table_name (field_name)
基于函数的索引
CREATE INDEX index_name ON table_name (LOWER(field_name));
*/

--  删除数据库同义词
drop synonym emp2;
-- 查询其他用户的表，需要其他用户授予查询权限
-- grant select on tree to scott;
-- 使用scott用户授予hr查询emp表的权限
grant select on emp to hr;
-- 使用hr用户查询scott用户的emp表
select * from scott.emp;

-- 创建同义词
create synonym emp2 for scott.emp;
-- 使用同义词
select * from emp2;

-- 查询employees表的全部列
select * from employees;
-- 查询employees表的指定列：hire_date、department_id
select hire_date, department_id from employees;

-- 查询时给表/列起别名（只在这句SQL中有效）
select t.employee_id "员工ID", t.hire_date 工作 from employees t;
select t.employee_id as "员工ID", t.hire_date as 工作 from employees t;

-- =
select t.employee_id, t.last_name, t.job_id, t.department_id
from employees t
where t.department_id = 90;

-- <=
select T.Employee_Id, T.Last_Name, T.Job_Id, T.Department_Id, T.Salary
from employees T 
where T.Salary <= 2200;

-- between and
select T.Employee_Id, T.Last_Name, T.Job_Id, T.Department_Id, T.Salary
from employees T
where T.Salary between 2200 and 2400;

-- in
SELECT T.EMPLOYEE_ID, T.LAST_NAME, T.MANAGER_ID
FROM EMPLOYEES T
WHERE T.MANAGER_ID IN (101,103);

-- 是否为 NULL值
SELECT T.EMPLOYEE_ID, T.LAST_NAME, T.MANAGER_ID
  FROM EMPLOYEES T
 WHERE T.MANAGER_ID IS NULL;
 
SELECT * FROM EMPLOYEES T
WHERE T.COMMISSION_PCT IS NOT NULL;

-- 模糊查询：通配符示例（_代表一个任意字符，%代表任意多个任意字符）
SELECT * FROM EMPLOYEES T WHERE T.FIRST_NAME LIKE '%A%';

SELECT T.LAST_NAME FROM EMPLOYEES T WHERE T.LAST_NAME LIKE '_o%';

-- 转义字符 例如：%\%%

-- ROWNUM 前n行
SELECT LAST_NAME, DEPARTMENT_ID, SALARY FROM EMPLOYEES WHERE ROWNUM <= 5;

-- AND, OR
SELECT T.EMPLOYEE_ID, T.LAST_NAME, T.JOB_ID, T.SALARY
  FROM EMPLOYEES T
 WHERE T.SALARY >= 10000
   AND T.EMPLOYEE_ID BETWEEN 120 AND 150;  

-- ORDER BY
-- 默认升序，DESC 降序
SELECT LAST_NAME, JOB_id, DEPARTMENT_ID, HIRE_DATE "HIRE"
  FROM EMPLOYEES
 ORDER BY "HIRE" DESC;
-- 部门ID升序，部门内薪水降序
SELECT LAST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID ASC, SALARY DESC; 

-- 分组查询：未在GROUP BY中的字段必须包括在聚合函数中

-- 计算各个部门中每个JOB的平均工资
SELECT t.department_id, t.job_id, AVG(t.salary) avg_sal
  FROM employees t
 GROUP BY t.department_id, t.job_id;

-- Where 和 having 的区别
-- 1. WHERE 子句从数据源中去掉不符合其搜索条件的数据
-- 2. HAVING子句从分组后的数据中去掉不符合其组搜索条件的各组数据行
-- 3. HAVING必须与GROUP BY搭配使用，且一定是在GROUP BY子句后面

-- 列出各种类别（job）的最低薪金（sal），且最低薪金小于1000
SELECT T.JOB, MIN(T.SAL)
  FROM EMP T
 GROUP BY T.JOB
HAVING MIN(T.SAL) < 1000;

-- START WITH: 从哪个节点开始递归查询
-- CONNECT BY PRIOR：指定如何递归（递归关系）
-- PRIOR:先前的， PRIOR FATHER = SON表示使用上一次的FATHER作为这次SON进行迭代（向上递归）
-- PRIOR SON = FATHER表示使用上一次的SON作为这次FATHER进行迭代（向下递归）
SELECT LEVEL, SON, LPAD(SON, LENGTH(SON) + (LEVEL * 3) - 1, '-') PERSON
FROM TREE
START WITH SON = '孙子1'
CONNECT BY PRIOR FATHER = SON;

SELECT LEVEL, SON, LPAD(SON, LENGTH(SON) + (LEVEL * 3) - 1, '-') PERSON, CONNECT_BY_ISLEAF     
FROM TREE
START WITH FATHER IS NULL
CONNECT BY PRIOR SON = FATHER;

-- dual: Oracle默认虚表，查询该表时返回单列单值
-- initcap(str) 首字母大写
select initcap('hello world') from dual;
-- lower/upper: 大小写
select lower('Hello World') from dual;
select upper('Hello World') from dual;

-- replace：替换
select replace('Hello World', 'H', 'M') from dual;

-- instr: 字符出现位置
select instr('Hello World', 'l', 4, 2) from dual;
-- substr: 截取字符串
select substr('Hello World', 1, 5) from dual;
-- 正则截取
select REGEXP_SUBSTR('2019~01~03', '[^~]+',1,2) from dual;
-- trim: 去除前后空格/指定字符
select trim('  Hello World  ') from dual;
select trim('H' from 'Hello World') from dual;

-- concat: 字符串连接
select concat('Hello', 'World') from dual;
select 'Hello' || 'World' from dual;
-- lpad/rpad: 左/右补齐
select lpad(24000, 10, '*') from dual;
select rpad(24000, 10, '*') from dual;

-- length: 字符串长度
select length('Hello World') from dual;

-- ABS(n)：绝对值
select abs(-15) from dual;

-- CEIL(n): 向上取整
select ceil(44.778) from dual;
-- FLOOR(n): 向下取整
select floor(100.2) from dual;

-- Power(m, n): m的n次方
select power(4, 2) from dual;

-- Mod(m, n): 求m对n的模
select mod(10, 3) from dual;

-- ROUND(m, n): 对m保留n位小数(四舍五入)
select round(100.256, 2) from dual;
-- Trunc(m, n): 对m保留n位小数(截取)
select trunc(100.256,2) from dual;

-- ADD_MONTHS(日期, 增值): 追加N个月份
select ADD_MONTHS(sysdate, 4) from dual;

select extract(year from sysdate) from dual;

--MONTHS_BETWEEN(日期,日期): 两日期相差多少月
select MONTHS_BETWEEN(SYSDATE, t.hire_date)
  from employees t
 where t.employee_id = '100';

-- LAST_DAY(日期)：返回当月最大天数
select LAST_DAY(SYSDATE) from dual;

-- NEXT_DAY: 获得某一日期后的第一个星期几的值(星期日为1)
select NEXT_DAY(sysdate, 1) from dual;

-- ROUND(日期, 'YEAR'/'MONTH'): 日期四舍五入
select t.hire_date, ROUND(t.hire_date, 'MONTH')
  from employees t
 where t.employee_id = '101';

-- TRUNC(日期, 'YEAR'/'MONTH'): 日期截取
select t.hire_date, TRUNC(t.hire_date, 'MONTH')
  from employees t
 where t.employee_id = '101';
select trunc(sysdate) from dual  --2011-3-18  今天的日期为2011-3-18 
select trunc(sysdate, 'mm')   from   dual  --2011-3-1    返回当月第一天. 
select trunc(sysdate,'yy') from dual  --2011-1-1       返回当年第一天 
select trunc(sysdate,'dd') from dual  --2011-3-18    返回当前年月日 
select trunc(sysdate,'yyyy') from dual  --2011-1-1   返回当年第一天 
select trunc(sysdate,'d') from dual  --2011-3-13 (星期天)返回当前星期的第一天 
select trunc(sysdate, 'hh') from dual   --2011-3-18 14:00:00   当前时间为14:41    
select trunc(sysdate, 'mi') from dual  --2011-3-18 14:41:00   TRUNC()函数没有秒的精确

-- 转换函数
-- TO_DATE: 转换为日期格式
select TO_DATE('2016-06-01', 'YYYY-MM-DD') from dual;

-- TO_CHAR: 转换为字符串
select TO_CHAR(sysdate, 'YYYY-MM') from dual;
select t.last_name,
       t.hire_date,
       TO_CHAR(t.hire_date, 'YYYY-MM-DD HH12:MI:SS')
  from employees t
 where t.employee_id = '100';

-- TO_NUMBER：转换为数字
select TO_NUMBER('100') as num from dual;

-- NVL(EXPR1,EXPR2): 如果EXPR1为空返回EXPR2
select nvl('', 0) from dual;
SELECT T1.COMMISSION_PCT,
       T2.COMMISSION_PCT,
       NVL(T1.COMMISSION_PCT, 0) + NVL(T2.COMMISSION_PCT, 0)
  FROM EMPLOYEES T1, EMPLOYEES T2
 WHERE T1.EMPLOYEE_ID = '100'
   AND T2.EMPLOYEE_ID = '145';

-- NVL2(EXPR1,EXPR2,EXPR3): 如果EXPR1为空返回EXPR3,否则返回EXPR2
SELECT NVL2('', 1, 2) FROM DUAL;

SELECT T.LAST_NAME,
       T.SALARY,
       T.COMMISSION_PCT,
       NVL2(T.COMMISSION_PCT, 'Y', 'N')
  FROM EMPLOYEES T;

-- NULLIF(EXPR1, EXPR2): 如果EXPR1 = EXPR2, 返回空，否则返回EXPR1
SELECT NULLIF(2, 2) FROM DUAL;

-- COALESCE(EXPR1,EXPR2,,,,): 返回第一个不为空的值，如果全为空，返回空
select COALESCE('','','3','') from dual;

-- SUM: 求和
SELECT SUM(salary) from employees;

-- AVG: 求平均
select AVG(salary) from employees;

-- MAX/MIN: 求最大/最小值
select MAX(salary) from employees;
select MIN(salary) from employees;

-- COUNT: 计数
select count(employee_id) from employees;

-- 分析函数形式：func_name() over([partition by 列名 order by 列名])
select t.empno,
       t.ename,
       t.deptno,
       t.sal,
       row_number() over(partition by t.deptno order by t.sal desc) sort_row_number_,
       rank() over(partition by t.deptno order by t.sal desc) sort_rank,
       dense_rank() over(partition by t.deptno order by t.sal desc) sort_dense_rank,
       lag(t.empno, 1, '00') over (partition by t.deptno order by t.sal desc) fetch_lag,
       lead(t.empno, 1, '00') over (partition by t.deptno order by t.sal desc) fetch_lead,
       count(t.empno) over(partition by t.deptno) count_emp,
       max(t.sal) over(partition by t.deptno) max_dept_sal,
       min(t.sal) over(partition by t.deptno) min_dept_sal,
       sum(t.sal) over(partition by t.deptno) sum_dept_sal,
       avg(t.sal) over(partition by t.deptno) avg_dept_sal
  from emp t;

/*
游标使用注意事项：
	(1)使用完成之后一定要关闭游标； 
	(2)不能关闭已经被关闭的游标； 
    (3)能不使用游标就不要使用游标，因为游标效率十分低下。
*/

-- 显式游标
-- loop循环方式
declare
   cursor dept_cursor is select department_id, department_name from test_cursor; --定义游标
   v_dept_id test_cursor.department_id%type;
   v_dept_name test_cursor.department_name%type;
begin
  open dept_cursor; -- 2.打开游标
  loop
    fetch dept_cursor into v_dept_id,v_dept_name; -- 3. 提取当前行数据
    dbms_output.put_line(v_dept_id||': '||v_dept_name);
    exit when dept_cursor%rowcount > 10;
    end loop;
    close dept_cursor;  -- 4. 关闭游标
end;

-- for循环方式
-- 使用子查询形式遍历
begin
  for v_record in ( select * from employees )
    loop
    dbms_output.put_line(v_record.employee_id||': '||v_record.last_name||': '||v_record.salary);
  end loop;
end;
-- 使用游标方式遍历
declare 
  cursor emp_cursor is select * from employees;
begin
  for v_record in emp_cursor
    loop
    dbms_output.put_line(v_record.employee_id||': '||v_record.last_name||': '||v_record.salary);
  -- exit when emp_cursor%rowcount > 10; --只打印10行
  end loop;
end; 

-- 隐式游标：默认名称sql
-- 游标的四个重要属性：found, notfound, rowcount,isopen
-- 隐式游标的isopen属性永远为false
declare 
begin
  update test_cursor set manager_id = 108 where department_id between 1000 and 2000;
  
  if sql%found then
    dbms_output.put_line('共更新数据'||sql%rowcount||'条');
  else
    dbms_output.put_line('未找到要更新的数据');
  end if;    
end;

-- 带参游标：与显示游标的区别是可以带参数，从而根据where条件划定范围 
declare
   cursor dep_by_id_cursor(p_id number default 10) is 
          select t.* from test_cursor t where t.department_id <= p_id;  
begin
  dbms_output.put_line('游标不传参测试：');
  for v_dep in dep_by_id_cursor loop --不传参，取默认值10
    dbms_output.put_line(v_dep.department_id||': '||v_dep.department_name);
  end loop;  
  dbms_output.put_line('游标传参测试：');
  for v_dep in dep_by_id_cursor(100) loop --传参
      dbms_output.put_line(v_dep.department_id||': '||v_dep.department_name);
  end loop;
end;  

/*
    REF游标和游标变量用于处理运行时动态执行的SQL查询，创建游标变量需要两个步骤：
    1）声明REF游标类型
    2）声明REF游标类型变量 
*/
-- REF游标：打开游标时动态指定具体结果集，不能使用for循环遍历
-- 弱类型REF游标
declare 
   type t_ref_cursor is ref cursor; -- 声明ref游标类型
   v_cursor t_ref_cursor; -- 声明ref游标类型变量
   v_dept departments%rowtype;
   v_emp employees%rowtype;
begin
  -- 打印departments表10前行数据
  dbms_output.put_line('打印departments表前10行数据:');
  open v_cursor for select * from departments; --打开游标时指定

  loop
    fetch v_cursor into v_dept;
    dbms_output.put_line(v_dept.department_id ||': '|| v_dept.department_name);
  exit when v_cursor%rowcount > 10;
  end loop;
  close v_cursor;
  
  -- 打印employees表前10行数据
  dbms_output.put_line('打印employees表前10行数据:');
  open v_cursor for select * from employees;
  loop
    fetch v_cursor into v_emp;
    dbms_output.put_line(v_emp.employee_id ||': '|| v_emp.last_name);
  exit when v_cursor%rowcount > 10;
  end loop;
  close v_cursor;
 
end;     

-- 强类型REF游标【预先指定查询类型与返回类型】
-- 参考 https://www.cnblogs.com/nick-huang/p/4609100.html
declare

    Type ref_cur_emp IS REF CURSOR RETURN scott.emp%RowType;
    cur_emp ref_cur_emp;
    rec_emp cur_emp%RowType;

    v_sql varchar2(100) := 'select * from scott.emp t';

begin
    -- xxx Open cur_emp For v_sql;
    Open cur_emp For
        select * from scott.emp t;
    Loop
        fetch cur_emp
            InTo rec_emp;
        Exit When cur_emp%NotFound;
        dbms_output.put_line(cur_emp%rowcount || ' -> ' || rec_emp.empno ||
                             '   ' || rec_emp.sal);
    End Loop;
    Close cur_emp;

end;

-- 游标补充点：更新删除数据
select * from test_cursor for update;
select t.*, rowid from test_cursor t;

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 80;

DECLARE
  CURSOR emp_cursor IS
    (SELECT employee_id, last_name, department_name
      FROM employees, departments
     WHERE employees.department_id = departments.department_id
       AND employees.department_id = 80)
       FOR UPDATE OF salary NOWAIT; -- 当没有获得资源报错
BEGIN
  for v_emp in emp_cursor loop
    update employees set salary = salary * 1.1 where current of emp_cursor; 
    --where current of 条件指定游标所指的当前行（多表时可能会匹配不到）
  END LOOP; 
END;