-- DDL 示例
CREATE TABLE employees (
    emp_id NUMBER(10),
    name VARCHAR2(100)
);

-- DML 示例
INSERT INTO employees VALUES (1, '张三');
COMMIT;

-- TCL 示例
SAVEPOINT sp1;
DELETE FROM employees WHERE emp_id = 1;
ROLLBACK TO sp1;

-- 数值类型示例
CREATE TABLE numeric_demo (
    int_col     NUMBER(10),        -- 整数，10 位
    dec_col     NUMBER(10,2),      -- 小数，总 10 位，小数 2 位
    float_col   FLOAT(53),         -- 浮点数
    percent     NUMBER(5,2)        -- 百分比：99.99
);

INSERT INTO numeric_demo VALUES (1234567890, 12345.67, 3.14159, 99.99);

-- 字符类型示例
CREATE TABLE char_demo (
    fixed_code  CHAR(10),           -- 固定 10 字符
    name        VARCHAR2(100),     -- 可变长度姓名
    bio         CLOB               -- 简历等长文本
);

-- 插入示例
INSERT INTO char_demo VALUES ('EMP', '张三', 'Oracle DBA，10年经验...');

-- 日期时间示例
CREATE TABLE datetime_demo (
    create_date DATE,                              -- DATE 类型
    create_time TIMESTAMP(6),                      -- 毫秒精度
    create_tz   TIMESTAMP WITH TIME ZONE,         -- 带时区
    work_period INTERVAL YEAR(2) TO MONTH,        -- 工作年限
    break_time  INTERVAL DAY(3) TO SECOND(2)      -- 休息时长
);

-- 插入日期
INSERT INTO datetime_demo VALUES (
    SYSDATE,
    SYSTIMESTAMP,
    CURRENT_TIMESTAMP,
    INTERVAL '5-3' YEAR TO MONTH,      -- 5年3个月
    INTERVAL '3 02:30:15' DAY TO SECOND   -- 3天2小时30分15秒
);

-- 日期运算
SELECT 
    SYSDATE + 7 AS next_week,
    SYSDATE - 30 AS last_month,
    SYSDATE + NUMTODSINTERVAL(2, 'HOUR') AS two_hours_later
FROM dual;

-- LOB 类型示例
CREATE TABLE lob_demo (
    id          NUMBER PRIMARY KEY,
    name        VARCHAR2(100),
    photo       BLOB,                  -- 员工照片
    resume      CLOB,                  -- 简历
    contract    BFILE                  -- 合同 PDF
);

-- 使用绑定变量插入 LOB
DECLARE
    v_blob BLOB;
    v_clob CLOB;
BEGIN
    INSERT INTO lob_demo (id, name, photo, resume)
    VALUES (1, '张三', EMPTY_BLOB(), EMPTY_CLOB())
    RETURNING photo, resume INTO v_blob, v_clob;
    
    -- 写入 BLOB
    DBMS_LOB.WRITE(v_blob, length('二进制数据'), 1, UTL_RAW.CAST_TO_RAW('二进制数据'));
END;
/

-- AND: 所有条件都为 TRUE
SELECT * FROM employees WHERE salary > 5000 AND department = 'IT';

-- OR: 任一条件为 TRUE
SELECT * FROM employees WHERE department = 'IT' OR department = 'HR';

-- NOT: 条件取反
SELECT * FROM employees WHERE NOT (department = 'IT');

-- 组合使用
SELECT * FROM employees 
WHERE (salary > 5000 OR commission > 1000) 
AND hire_date > DATE '2020-01-01';

-- 字符函数示例
SELECT 
    CONCAT('Mr.', name) AS full_name,     -- 拼接
    LENGTH(name) AS name_len,             -- 长度
    UPPER(name) AS name_upper,            -- 大写
    SUBSTR(name, 1, 1) || '***' AS masked -- 脱敏
FROM employees;

-- 字符串截取详解
SELECT 
    SUBSTR('Oracle Database', 1, 6)     AS from_start,   -- Oracle
    SUBSTR('Oracle Database', -8)       AS from_end,     -- Database
    SUBSTR('Oracle Database', 8, 4)     AS middle,       -- Data
    INSTR('Oracle Database', 'a')       AS first_pos,    -- 4
    INSTR('Oracle Database', 'a', 5)    AS next_pos      -- 7
FROM dual;

-- 日期函数示例
SELECT 
    SYSDATE,                                          -- 2024-01-15 10:30:00
    ADD_MONTHS(SYSDATE, 3),                          -- 3 个月后
    ADD_MONTHS(SYSDATE, -6),                         -- 6 个月前
    MONTHS_BETWEEN('2024-12-31', '2024-01-01'),     -- 月份差
    LAST_DAY(SYSDATE),                               -- 月末
    NEXT_DAY(SYSDATE, 'FRIDAY'),                     -- 下个周五
    EXTRACT(YEAR FROM SYSDATE),                      -- 年份
    TRUNC(SYSDATE, 'IW'),                            -- 本周一
    TRUNC(SYSDATE, 'YYYY')                           -- 年初
FROM dual;

-- 日期格式化
SELECT 
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS datetime1,
    TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"') AS datetime2,
    TO_CHAR(SYSDATE, 'DAY') AS weekday,
    TO_CHAR(SYSDATE, 'Q') AS quarter
FROM dual;

-- 转换函数示例
SELECT 
    -- 数字格式化
    TO_CHAR(1234567.89, '999,999,999.99') AS formatted_num,
    
    -- 数字转字符串
    TO_CHAR(1234) || ' items',
    
    -- 空值处理
    NVL(commission, 0) AS commission,
    NVL2(commission, '有提成', '无提成') AS commission_status,
    COALESCE(phone, email, '无联系方式') AS contact
FROM employees;

-- 日期格式模型
| 格式 | 说明 | 示例 |
|------|------|------|
| YYYY | 4位年份 | 2024 |
| MM | 2位月份 | 01-12 |
| DD | 2位日期 | 01-31 |
| HH24 | 24小时制 | 00-23 |
| HH12 | 12小时制 | 01-12 |
| MI | 分钟 | 00-59 |
| SS | 秒 | 00-59 |
| DAY | 星期几名称 | MONDAY |
| MONTH | 月份名称 | JANUARY |
| Q | 季度 | 1-4 |
| WW | 年中第几周 | 1-53 |

-- 聚合函数示例
SELECT 
    COUNT(*) AS total_count,               -- 总行数
    COUNT(DISTINCT department) AS dept_cnt, -- 部门数
    SUM(salary) AS total_salary,            -- 工资总和
    AVG(salary) AS avg_salary,              -- 平均工资
    MAX(salary) AS max_salary,              -- 最高工资
    MIN(salary) AS min_salary               -- 最低工资
FROM employees;

-- LISTAGG 字符串聚合（Oracle 11g+）
SELECT 
    department,
    LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY hire_date) AS employees
FROM employees
GROUP BY department;

-- 结果示例：
-- DEPARTMENT | EMPLOYEES
-- -----------+--------------------
-- IT         | Smith,Jones,Lee
-- HR         | Brown,Wilson

-- 分析函数示例
SELECT 
    employee_id,
    department_id,
    salary,
    -- 部门内排名
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank,
    -- 部门内工资占比
    ROUND(salary / SUM(salary) OVER (PARTITION BY department_id) * 100, 2) AS dept_pct,
    -- 累计求和
    SUM(salary) OVER (ORDER BY employee_id ROWS UNBOUNDED PRECEDING) AS cumulative_sum,
    -- 移动平均（最近3个月）
    AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees
ORDER BY department_id, salary DESC;

-- 分页查询（使用 ROWNUM）
SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM (
        SELECT employee_id, first_name, salary
        FROM employees
        ORDER BY salary DESC
    ) a WHERE ROWNUM <= 20
) WHERE rn > 10;

-- 解释：
-- 1. 内层：完整查询 + 排序
-- 2. 中层：ROWNUM 限制行数（必须在外层包装）
-- 3. 外层：过滤起始行

-- Oracle 12c+ 简化语法
SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- FETCH 子句选项
OFFSET n ROWS                  -- 跳过前 n 行
FETCH FIRST n ROWS ONLY        -- 取前 n 行
FETCH FIRST n ROWS WITH TIES   -- 包括并列的最后几行
FETCH FIRST n PERCENT ROWS ONLY -- 取前 n%

-- 组织架构层级查询
SELECT 
    LEVEL,                          -- 层级
    employee_id,
    first_name || ' ' || last_name AS name,
    manager_id,
    PRIIOR first_name || ' ' || PRIOR last_name AS manager_name
FROM employees
START WITH manager_id IS NULL      -- 从 CEO 开始
CONNECT BY PRIOR employee_id = manager_id;  -- 递归条件

-- 格式化显示层级
SELECT 
    LPAD(' ', 2 * (LEVEL - 1)) || first_name AS org_chart
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- SYS_CONNECT_BY_PATH 路径
SELECT 
    SYS_CONNECT_BY_PATH(first_name, '/') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- 查找叶子节点和根节点
SELECT *
FROM employees
WHERE LEVEL > 1
CONNECT BY PRIOR employee_id = manager_id
START WITH employee_id = 100
AND LEVEL <= 3;

✅ SQL 分为 DDL、DML、DCL、TCL 四大类
✅ 掌握数值、字符、日期、Lob 等核心数据类型
✅ 熟练使用各种运算符和表达式
✅ 记住常用函数：字符串、数值、日期、转换函数
✅ 理解分析函数（窗口函数）的强大功能
✅ 掌握 Oracle 分页查询和层级查询