Oracle 特定语法

# Oracle 特定语法

# 概述

Oracle Database 是企业级关系型数据库管理系统的领导者，以其强大的功能、高可用性和可扩展性而闻名。本章节详细介绍Oracle的特定语法、高级功能和最佳实践。

# Oracle 数据类型

# 1. 基本数据类型

-- 创建包含各种数据类型的表
CREATE TABLE DataTypesDemo (
    -- 数值类型
    NumberVal NUMBER,                      -- 可变精度数值
    NumberFixed NUMBER(10,2),              -- 固定精度数值
    IntegerVal INTEGER,                    -- 整数
    FloatVal FLOAT,                        -- 浮点数
    BinaryFloatVal BINARY_FLOAT,           -- 32位浮点
    BinaryDoubleVal BINARY_DOUBLE,         -- 64位浮点
    
    -- 字符类型
    CharVal CHAR(10),                      -- 定长字符
    VarChar2Val VARCHAR2(255),             -- 变长字符
    NCharVal NCHAR(10),                    -- 定长Unicode字符
    NVarChar2Val NVARCHAR2(255),           -- 变长Unicode字符
    ClobVal CLOB,                          -- 大字符对象
    NClobVal NCLOB,                        -- Unicode大字符对象
    
    -- 日期时间类型
    DateVal DATE,                          -- 日期时间
    TimestampVal TIMESTAMP,                -- 时间戳
    TimestampTZVal TIMESTAMP WITH TIME ZONE,        -- 带时区时间戳
    TimestampLTZVal TIMESTAMP WITH LOCAL TIME ZONE, -- 本地时区时间戳
    IntervalYMVal INTERVAL YEAR TO MONTH,  -- 年月间隔
    IntervalDSVal INTERVAL DAY TO SECOND,  -- 日秒间隔
    
    -- 二进制类型
    RawVal RAW(255),                       -- 原始二进制
    BlobVal BLOB,                          -- 大二进制对象
    BFileVal BFILE,                        -- 外部文件
    
    -- 行标识符
    RowIdVal ROWID,                        -- 行标识符
    URowIdVal UROWID,                      -- 通用行标识符
    
    -- XML类型
    XmlVal XMLType                         -- XML数据
);

-- 插入示例数据
INSERT INTO DataTypesDemo (
    NumberVal, NumberFixed, IntegerVal, FloatVal, BinaryFloatVal, BinaryDoubleVal,
    CharVal, VarChar2Val, NCharVal, NVarChar2Val, ClobVal, NClobVal,
    DateVal, TimestampVal, TimestampTZVal, TimestampLTZVal,
    IntervalYMVal, IntervalDSVal,
    RawVal, BlobVal,
    XmlVal
) VALUES (
    123456.789, 12345.67, 123456, 123.456, 123.456f, 123456.789d,
    'CHAR_VAL', 'VARCHAR2示例', N'NCHAR_VAL', N'NVARCHAR2示例', 'CLOB大文本示例', N'NCLOB大文本示例',
    DATE '2023-12-25', TIMESTAMP '2023-12-25 14:30:00.123456',
    TIMESTAMP '2023-12-25 14:30:00.123456 +08:00',
    TIMESTAMP '2023-12-25 14:30:00.123456',
    INTERVAL '2-6' YEAR TO MONTH, INTERVAL '10 12:30:45.123' DAY TO SECOND,
    HEXTORAW('48656C6C6F'), HEXTORAW('576F726C64'),
    XMLType('<root><item>示例XML</item></root>')
);

-- 查询数据类型信息
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    DATA_LENGTH,
    DATA_PRECISION,
    DATA_SCALE,
    NULLABLE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'DATATYPESDEMO'
ORDER BY COLUMN_ID;

# 2. 用户定义类型

-- 创建对象类型
CREATE OR REPLACE TYPE AddressType AS OBJECT (
    Street VARCHAR2(100),
    City VARCHAR2(50),
    State VARCHAR2(50),
    ZipCode VARCHAR2(10),
    Country VARCHAR2(50),
    
    -- 成员方法
    MEMBER FUNCTION GetFullAddress RETURN VARCHAR2
);
/

-- 实现对象类型方法
CREATE OR REPLACE TYPE BODY AddressType AS
    MEMBER FUNCTION GetFullAddress RETURN VARCHAR2 IS
    BEGIN
        RETURN Street || ', ' || City || ', ' || State || ' ' || ZipCode || ', ' || Country;
    END GetFullAddress;
END;
/

-- 创建嵌套表类型
CREATE OR REPLACE TYPE PhoneNumberType AS OBJECT (
    PhoneType VARCHAR2(20),
    PhoneNumber VARCHAR2(20)
);
/

CREATE OR REPLACE TYPE PhoneListType AS TABLE OF PhoneNumberType;
/

-- 创建VARRAY类型
CREATE OR REPLACE TYPE EmailListType AS VARRAY(5) OF VARCHAR2(100);
/

-- 使用用户定义类型创建表
CREATE TABLE Customers (
    CustomerID NUMBER PRIMARY KEY,
    CustomerName VARCHAR2(100),
    Address AddressType,
    PhoneNumbers PhoneListType,
    EmailAddresses EmailListType
) NESTED TABLE PhoneNumbers STORE AS PhoneNumbersTab;

-- 插入复杂数据
INSERT INTO Customers VALUES (
    1,
    '张三',
    AddressType('北京市朝阳区建国路1号', '北京', '北京市', '100000', '中国'),
    PhoneListType(
        PhoneNumberType('手机', '13800138000'),
        PhoneNumberType('办公', '010-12345678')
    ),
    EmailListType('zhangsan@email.com', 'zhang.san@company.com')
);

-- 查询复杂数据
SELECT 
    c.CustomerID,
    c.CustomerName,
    c.Address.GetFullAddress() AS FullAddress,
    c.Address.City AS City,
    c.EmailAddresses
FROM Customers c;

-- 查询嵌套表数据
SELECT 
    c.CustomerID,
    c.CustomerName,
    p.PhoneType,
    p.PhoneNumber
FROM Customers c,
     TABLE(c.PhoneNumbers) p;

# Oracle 特有功能

# 1. 序列 (SEQUENCE)

-- 创建序列
CREATE SEQUENCE order_seq
    START WITH 1000
    INCREMENT BY 1
    MAXVALUE 999999
    MINVALUE 1
    CYCLE
    CACHE 20
    ORDER;

-- 创建使用序列的表
CREATE TABLE Orders (
    OrderID NUMBER PRIMARY KEY,
    CustomerID NUMBER,
    OrderDate DATE DEFAULT SYSDATE,
    Amount NUMBER(10,2)
);

-- 使用序列插入数据
INSERT INTO Orders (OrderID, CustomerID, Amount) VALUES
(order_seq.NEXTVAL, 1001, 299.99);

INSERT INTO Orders (OrderID, CustomerID, Amount) VALUES
(order_seq.NEXTVAL, 1002, 599.99);

-- 查看序列当前值
SELECT order_seq.CURRVAL FROM DUAL;

-- 查看序列信息
SELECT 
    SEQUENCE_NAME,
    MIN_VALUE,
    MAX_VALUE,
    INCREMENT_BY,
    CYCLE_FLAG,
    ORDER_FLAG,
    CACHE_SIZE,
    LAST_NUMBER
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'ORDER_SEQ';

-- 修改序列
ALTER SEQUENCE order_seq
    INCREMENT BY 5
    MAXVALUE 9999999
    CACHE 50;

-- 删除序列
-- DROP SEQUENCE order_seq;

# 2. 分析函数和窗口函数

-- 创建销售数据表
CREATE TABLE Sales (
    SaleID NUMBER PRIMARY KEY,
    Salesperson VARCHAR2(50),
    Department VARCHAR2(50),
    SaleAmount NUMBER(10,2),
    SaleDate DATE
);

-- 插入测试数据
INSERT ALL
    INTO Sales VALUES (1, '张三', '销售部', 10000, DATE '2023-01-15')
    INTO Sales VALUES (2, '李四', '销售部', 15000, DATE '2023-01-20')
    INTO Sales VALUES (3, '王五', '市场部', 12000, DATE '2023-02-10')
    INTO Sales VALUES (4, '张三', '销售部', 8000, DATE '2023-02-15')
    INTO Sales VALUES (5, '李四', '销售部', 20000, DATE '2023-03-05')
    INTO Sales VALUES (6, '王五', '市场部', 18000, DATE '2023-03-10')
    INTO Sales VALUES (7, '赵六', '技术部', 25000, DATE '2023-03-15')
    INTO Sales VALUES (8, '钱七', '技术部', 22000, DATE '2023-04-01')
SELECT 1 FROM DUAL;

-- 分析函数示例
SELECT 
    Salesperson,
    Department,
    SaleAmount,
    SaleDate,
    
    -- 排名函数
    ROW_NUMBER() OVER (ORDER BY SaleAmount DESC) AS RowNum,
    RANK() OVER (ORDER BY SaleAmount DESC) AS RankNum,
    DENSE_RANK() OVER (ORDER BY SaleAmount DESC) AS DenseRank,
    PERCENT_RANK() OVER (ORDER BY SaleAmount) AS PercentRank,
    CUME_DIST() OVER (ORDER BY SaleAmount) AS CumeDist,
    
    -- 分区排名
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY SaleAmount DESC) AS DeptRank,
    
    -- 聚合窗口函数
    SUM(SaleAmount) OVER (PARTITION BY Department) AS DeptTotal,
    AVG(SaleAmount) OVER (PARTITION BY Salesperson) AS PersonAvg,
    COUNT(*) OVER (PARTITION BY Department) AS DeptCount,
    
    -- 移动窗口
    SUM(SaleAmount) OVER (
        ORDER BY SaleDate 
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS MovingSum,
    
    -- 累计计算
    SUM(SaleAmount) OVER (
        ORDER BY SaleDate 
        ROWS UNBOUNDED PRECEDING
    ) AS CumulativeSum,
    
    -- 前后值比较
    LAG(SaleAmount, 1) OVER (ORDER BY SaleDate) AS PrevAmount,
    LEAD(SaleAmount, 1) OVER (ORDER BY SaleDate) AS NextAmount,
    
    -- 首尾值
    FIRST_VALUE(SaleAmount) OVER (
        PARTITION BY Department ORDER BY SaleDate
        ROWS UNBOUNDED PRECEDING
    ) AS FirstSale,
    LAST_VALUE(SaleAmount) OVER (
        PARTITION BY Department ORDER BY SaleDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LastSale,
    
    -- 分位数
    NTILE(4) OVER (ORDER BY SaleAmount) AS Quartile,
    
    -- 比率函数
    RATIO_TO_REPORT(SaleAmount) OVER (PARTITION BY Department) AS DeptRatio,
    RATIO_TO_REPORT(SaleAmount) OVER () AS TotalRatio
    
FROM Sales
ORDER BY SaleDate;

-- 统计函数
SELECT 
    Department,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SaleAmount) AS MedianAmount,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY SaleAmount) AS MedianAmountDisc,
    LISTAGG(Salesperson, ', ') WITHIN GROUP (ORDER BY SaleAmount DESC) AS SalespersonList
FROM Sales
GROUP BY Department;

# 3. 层次查询

-- 创建员工层次结构表
CREATE TABLE Employees (
    EmployeeID NUMBER PRIMARY KEY,
    EmployeeName VARCHAR2(50),
    ManagerID NUMBER REFERENCES Employees(EmployeeID),
    Department VARCHAR2(50),
    Salary NUMBER(10,2)
);

-- 插入层次数据
INSERT ALL
    INTO Employees VALUES (1, '总经理', NULL, '管理层', 50000)
    INTO Employees VALUES (2, '销售总监', 1, '销售部', 30000)
    INTO Employees VALUES (3, '技术总监', 1, '技术部', 35000)
    INTO Employees VALUES (4, '财务总监', 1, '财务部', 28000)
    INTO Employees VALUES (5, '销售经理', 2, '销售部', 20000)
    INTO Employees VALUES (6, '开发经理', 3, '技术部', 25000)
    INTO Employees VALUES (7, '测试经理', 3, '技术部', 22000)
    INTO Employees VALUES (8, '销售代表1', 5, '销售部', 15000)
    INTO Employees VALUES (9, '销售代表2', 5, '销售部', 16000)
    INTO Employees VALUES (10, '开发工程师1', 6, '技术部', 18000)
    INTO Employees VALUES (11, '开发工程师2', 6, '技术部', 19000)
    INTO Employees VALUES (12, '测试工程师', 7, '技术部', 16000)
SELECT 1 FROM DUAL;

-- 层次查询 - 自顶向下
SELECT 
    LEVEL,
    LPAD(' ', (LEVEL-1)*2) || EmployeeName AS Hierarchy,
    EmployeeID,
    ManagerID,
    Department,
    Salary,
    SYS_CONNECT_BY_PATH(EmployeeName, ' -> ') AS Path,
    CONNECT_BY_ROOT EmployeeName AS TopManager,
    CONNECT_BY_ISLEAF AS IsLeaf
FROM Employees
START WITH ManagerID IS NULL
CONNECT BY PRIOR EmployeeID = ManagerID
ORDER SIBLINGS BY EmployeeName;

-- 层次查询 - 自底向上
SELECT 
    LEVEL,
    LPAD(' ', (LEVEL-1)*2) || EmployeeName AS Hierarchy,
    EmployeeID,
    ManagerID,
    SYS_CONNECT_BY_PATH(EmployeeName, ' <- ') AS Path
FROM Employees
START WITH EmployeeName = '开发工程师1'
CONNECT BY EmployeeID = PRIOR ManagerID;

-- 层次聚合查询
SELECT 
    LEVEL,
    EmployeeName,
    Salary,
    SUM(Salary) OVER (PARTITION BY CONNECT_BY_ROOT EmployeeID) AS TeamTotalSalary,
    COUNT(*) OVER (PARTITION BY CONNECT_BY_ROOT EmployeeID) AS TeamSize
FROM Employees
START WITH ManagerID IS NULL
CONNECT BY PRIOR EmployeeID = ManagerID;

-- 查找所有下属
SELECT 
    m.EmployeeName AS Manager,
    e.EmployeeName AS Subordinate,
    LEVEL AS HierarchyLevel
FROM Employees m, Employees e
WHERE m.EmployeeName = '技术总监'
START WITH e.ManagerID = m.EmployeeID
CONNECT BY PRIOR e.EmployeeID = e.ManagerID;

# 4. PIVOT 和 UNPIVOT

-- 创建季度销售数据
CREATE TABLE QuarterlySales (
    Year NUMBER,
    Quarter VARCHAR2(2),
    Region VARCHAR2(20),
    Sales NUMBER(10,2)
);

-- 插入测试数据
INSERT ALL
    INTO QuarterlySales VALUES (2023, 'Q1', '北区', 100000)
    INTO QuarterlySales VALUES (2023, 'Q2', '北区', 120000)
    INTO QuarterlySales VALUES (2023, 'Q3', '北区', 110000)
    INTO QuarterlySales VALUES (2023, 'Q4', '北区', 130000)
    INTO QuarterlySales VALUES (2023, 'Q1', '南区', 90000)
    INTO QuarterlySales VALUES (2023, 'Q2', '南区', 95000)
    INTO QuarterlySales VALUES (2023, 'Q3', '南区', 105000)
    INTO QuarterlySales VALUES (2023, 'Q4', '南区', 115000)
    INTO QuarterlySales VALUES (2023, 'Q1', '东区', 80000)
    INTO QuarterlySales VALUES (2023, 'Q2', '东区', 85000)
    INTO QuarterlySales VALUES (2023, 'Q3', '东区', 90000)
    INTO QuarterlySales VALUES (2023, 'Q4', '东区', 95000)
SELECT 1 FROM DUAL;

-- PIVOT 操作 - 行转列
SELECT *
FROM (
    SELECT Region, Quarter, Sales
    FROM QuarterlySales
    WHERE Year = 2023
)
PIVOT (
    SUM(Sales)
    FOR Quarter IN ('Q1' AS Q1, 'Q2' AS Q2, 'Q3' AS Q3, 'Q4' AS Q4)
)
ORDER BY Region;

-- 多值PIVOT
SELECT *
FROM (
    SELECT Region, Quarter, Sales
    FROM QuarterlySales
    WHERE Year = 2023
)
PIVOT (
    SUM(Sales) AS Total,
    AVG(Sales) AS Average
    FOR Quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
)
ORDER BY Region;

-- 创建透视后的数据用于UNPIVOT
CREATE TABLE RegionSalesMatrix AS
SELECT *
FROM (
    SELECT Region, Quarter, Sales
    FROM QuarterlySales
    WHERE Year = 2023
)
PIVOT (
    SUM(Sales)
    FOR Quarter IN ('Q1' AS Q1, 'Q2' AS Q2, 'Q3' AS Q3, 'Q4' AS Q4)
);

-- UNPIVOT 操作 - 列转行
SELECT Region, Quarter, Sales
FROM RegionSalesMatrix
UNPIVOT (
    Sales
    FOR Quarter IN (Q1 AS 'Q1', Q2 AS 'Q2', Q3 AS 'Q3', Q4 AS 'Q4')
)
ORDER BY Region, Quarter;

# 5. 正则表达式函数

-- 创建测试数据
CREATE TABLE TextData (
    ID NUMBER,
    Text VARCHAR2(200)
);

INSERT ALL
    INTO TextData VALUES (1, '联系电话：13800138000，邮箱：zhang@email.com')
    INTO TextData VALUES (2, '手机号码：15912345678，QQ：123456789')
    INTO TextData VALUES (3, '办公电话：010-12345678，传真：010-87654321')
    INTO TextData VALUES (4, '邮件地址：user.name@company.com.cn')
    INTO TextData VALUES (5, '网址：https://www.example.com/path?param=value')
SELECT 1 FROM DUAL;

-- REGEXP_LIKE - 正则匹配
SELECT ID, Text
FROM TextData
WHERE REGEXP_LIKE(Text, '1[3-9]\d{9}');  -- 匹配手机号

-- REGEXP_SUBSTR - 提取匹配的子串
SELECT 
    ID,
    Text,
    REGEXP_SUBSTR(Text, '1[3-9]\d{9}') AS MobilePhone,
    REGEXP_SUBSTR(Text, '\w+@\w+\.\w+(\.\w+)?') AS Email,
    REGEXP_SUBSTR(Text, 'https?://[^\s]+') AS URL
FROM TextData;

-- REGEXP_REPLACE - 正则替换
SELECT 
    ID,
    Text,
    REGEXP_REPLACE(Text, '1[3-9]\d{9}', '***********') AS MaskedText,
    REGEXP_REPLACE(Text, '(\w+)@(\w+)', '\1@***') AS MaskedEmail
FROM TextData;

-- REGEXP_INSTR - 查找匹配位置
SELECT 
    ID,
    Text,
    REGEXP_INSTR(Text, '1[3-9]\d{9}') AS PhonePosition,
    REGEXP_INSTR(Text, '\w+@\w+') AS EmailPosition
FROM TextData;

-- REGEXP_COUNT - 计算匹配次数
SELECT 
    ID,
    Text,
    REGEXP_COUNT(Text, '\d') AS DigitCount,
    REGEXP_COUNT(Text, '\w+@\w+') AS EmailCount
FROM TextData;

-- 复杂正则表达式示例
SELECT 
    ID,
    Text,
    -- 提取所有数字
    REGEXP_SUBSTR(Text, '\d+', 1, 1) AS FirstNumber,
    REGEXP_SUBSTR(Text, '\d+', 1, 2) AS SecondNumber,
    -- 验证邮箱格式
    CASE WHEN REGEXP_LIKE(Text, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') 
         THEN '有效邮箱' ELSE '无效邮箱' END AS EmailValidation
FROM TextData;

# PL/SQL 编程

# 1. 基本语法结构

-- 简单的PL/SQL块
DECLARE
    v_message VARCHAR2(100);
    v_count NUMBER;
BEGIN
    v_message := 'Hello, Oracle!';
    SELECT COUNT(*) INTO v_count FROM Sales;
    
    DBMS_OUTPUT.PUT_LINE(v_message);
    DBMS_OUTPUT.PUT_LINE('销售记录总数: ' || v_count);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('发生错误: ' || SQLERRM);
END;
/

-- 变量声明和赋值
DECLARE
    -- 基本变量
    v_name VARCHAR2(50) := '张三';
    v_salary NUMBER(10,2);
    v_hire_date DATE := SYSDATE;
    
    -- 使用%TYPE
    v_emp_name Employees.EmployeeName%TYPE;
    v_emp_salary Employees.Salary%TYPE;
    
    -- 使用%ROWTYPE
    v_emp_record Employees%ROWTYPE;
    
    -- 常量
    c_max_salary CONSTANT NUMBER := 100000;
    
    -- 布尔变量
    v_is_manager BOOLEAN := FALSE;
BEGIN
    -- 赋值操作
    v_salary := 25000;
    v_emp_name := '李四';
    
    -- 从数据库查询赋值
    SELECT EmployeeName, Salary 
    INTO v_emp_name, v_emp_salary
    FROM Employees 
    WHERE EmployeeID = 1;
    
    -- 整行赋值
    SELECT * INTO v_emp_record
    FROM Employees 
    WHERE EmployeeID = 1;
    
    DBMS_OUTPUT.PUT_LINE('员工姓名: ' || v_emp_record.EmployeeName);
    DBMS_OUTPUT.PUT_LINE('员工薪资: ' || v_emp_record.Salary);
END;
/

# 2. 控制结构

-- IF-THEN-ELSE 语句
DECLARE
    v_salary NUMBER;
    v_bonus NUMBER;
    v_grade VARCHAR2(10);
BEGIN
    SELECT Salary INTO v_salary
    FROM Employees
    WHERE EmployeeID = 1;
    
    -- 简单IF语句
    IF v_salary > 30000 THEN
        v_bonus := v_salary * 0.15;
    ELSIF v_salary > 20000 THEN
        v_bonus := v_salary * 0.10;
    ELSIF v_salary > 10000 THEN
        v_bonus := v_salary * 0.05;
    ELSE
        v_bonus := 0;
    END IF;
    
    -- CASE语句
    CASE 
        WHEN v_salary >= 40000 THEN v_grade := 'A';
        WHEN v_salary >= 30000 THEN v_grade := 'B';
        WHEN v_salary >= 20000 THEN v_grade := 'C';
        ELSE v_grade := 'D';
    END CASE;
    
    DBMS_OUTPUT.PUT_LINE('薪资: ' || v_salary);
    DBMS_OUTPUT.PUT_LINE('奖金: ' || v_bonus);
    DBMS_OUTPUT.PUT_LINE('等级: ' || v_grade);
END;
/

-- 循环语句
DECLARE
    v_counter NUMBER := 1;
    v_sum NUMBER := 0;
BEGIN
    -- 基本LOOP
    LOOP
        v_sum := v_sum + v_counter;
        v_counter := v_counter + 1;
        EXIT WHEN v_counter > 10;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('1到10的和: ' || v_sum);
    
    -- WHILE循环
    v_counter := 1;
    v_sum := 0;
    WHILE v_counter <= 10 LOOP
        v_sum := v_sum + v_counter;
        v_counter := v_counter + 1;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('WHILE循环结果: ' || v_sum);
    
    -- FOR循环
    v_sum := 0;
    FOR i IN 1..10 LOOP
        v_sum := v_sum + i;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('FOR循环结果: ' || v_sum);
    
    -- 反向FOR循环
    DBMS_OUTPUT.PUT_LINE('倒数:');
    FOR i IN REVERSE 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
/

# 3. 游标处理

-- 显式游标
DECLARE
    -- 声明游标
    CURSOR emp_cursor IS
        SELECT EmployeeID, EmployeeName, Salary
        FROM Employees
        WHERE Department = '技术部'
        ORDER BY Salary DESC;
    
    -- 游标记录变量
    v_emp_record emp_cursor%ROWTYPE;
    v_total_salary NUMBER := 0;
    v_count NUMBER := 0;
BEGIN
    -- 打开游标
    OPEN emp_cursor;
    
    LOOP
        -- 提取数据
        FETCH emp_cursor INTO v_emp_record;
        
        -- 检查是否还有数据
        EXIT WHEN emp_cursor%NOTFOUND;
        
        v_count := v_count + 1;
        v_total_salary := v_total_salary + v_emp_record.Salary;
        
        DBMS_OUTPUT.PUT_LINE('员工' || v_count || ': ' || 
                           v_emp_record.EmployeeName || 
                           ', 薪资: ' || v_emp_record.Salary);
    END LOOP;
    
    -- 关闭游标
    CLOSE emp_cursor;
    
    DBMS_OUTPUT.PUT_LINE('技术部总人数: ' || v_count);
    DBMS_OUTPUT.PUT_LINE('技术部总薪资: ' || v_total_salary);
    DBMS_OUTPUT.PUT_LINE('平均薪资: ' || ROUND(v_total_salary/v_count, 2));
END;
/

-- 游标FOR循环
DECLARE
    v_dept_total NUMBER;
BEGIN
    FOR dept_rec IN (
        SELECT Department, COUNT(*) AS EmpCount, SUM(Salary) AS TotalSalary
        FROM Employees
        GROUP BY Department
        ORDER BY TotalSalary DESC
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('部门: ' || dept_rec.Department || 
                           ', 人数: ' || dept_rec.EmpCount || 
                           ', 总薪资: ' || dept_rec.TotalSalary);
    END LOOP;
END;
/

-- 参数化游标
DECLARE
    CURSOR dept_cursor(p_dept VARCHAR2) IS
        SELECT EmployeeName, Salary
        FROM Employees
        WHERE Department = p_dept
        ORDER BY Salary DESC;
    
    v_dept VARCHAR2(50) := '销售部';
BEGIN
    DBMS_OUTPUT.PUT_LINE('=== ' || v_dept || ' 员工信息 ===');
    
    FOR emp_rec IN dept_cursor(v_dept) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.EmployeeName || ': ' || emp_rec.Salary);
    END LOOP;
END;
/

-- 可更新游标
DECLARE
    CURSOR emp_cursor IS
        SELECT EmployeeID, EmployeeName, Salary
        FROM Employees
        WHERE Department = '技术部'
        FOR UPDATE OF Salary;
    
    v_bonus_rate NUMBER := 0.1;
BEGIN
    FOR emp_rec IN emp_cursor LOOP
        -- 更新当前行
        UPDATE Employees
        SET Salary = Salary * (1 + v_bonus_rate)
        WHERE CURRENT OF emp_cursor;
        
        DBMS_OUTPUT.PUT_LINE('已更新员工: ' || emp_rec.EmployeeName);
    END LOOP;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('技术部员工加薪完成');
END;
/

# 4. 异常处理

-- 预定义异常处理
DECLARE
    v_emp_name VARCHAR2(50);
    v_salary NUMBER;
BEGIN
    -- 可能引发异常的代码
    SELECT EmployeeName, Salary
    INTO v_emp_name, v_salary
    FROM Employees
    WHERE EmployeeID = 999;  -- 不存在的员工ID
    
    DBMS_OUTPUT.PUT_LINE('员工: ' || v_emp_name);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('错误: 未找到指定的员工');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('错误: 查询返回多行数据');
    WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('错误: 数值或转换错误');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('未知错误: ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('错误代码: ' || SQLCODE);
END;
/

-- 用户定义异常
DECLARE
    -- 声明异常
    salary_too_high EXCEPTION;
    invalid_department EXCEPTION;
    
    v_salary NUMBER;
    v_dept VARCHAR2(50);
    v_max_salary NUMBER := 60000;
BEGIN
    v_salary := 70000;
    v_dept := '未知部门';
    
    -- 检查薪资
    IF v_salary > v_max_salary THEN
        RAISE salary_too_high;
    END IF;
    
    -- 检查部门
    IF v_dept NOT IN ('销售部', '技术部', '财务部', '管理层') THEN
        RAISE invalid_department;
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('验证通过');
EXCEPTION
    WHEN salary_too_high THEN
        DBMS_OUTPUT.PUT_LINE('错误: 薪资超过最大限制 ' || v_max_salary);
    WHEN invalid_department THEN
        DBMS_OUTPUT.PUT_LINE('错误: 无效的部门名称 ' || v_dept);
END;
/

-- 使用PRAGMA EXCEPTION_INIT
DECLARE
    -- 将Oracle错误代码关联到异常名称
    duplicate_key EXCEPTION;
    PRAGMA EXCEPTION_INIT(duplicate_key, -1);
    
    foreign_key_violation EXCEPTION;
    PRAGMA EXCEPTION_INIT(foreign_key_violation, -2291);
BEGIN
    -- 尝试插入重复的主键
    INSERT INTO Employees (EmployeeID, EmployeeName, Department)
    VALUES (1, '重复员工', '测试部');
EXCEPTION
    WHEN duplicate_key THEN
        DBMS_OUTPUT.PUT_LINE('错误: 员工ID已存在');
    WHEN foreign_key_violation THEN
        DBMS_OUTPUT.PUT_LINE('错误: 外键约束违反');
END;
/

-- 异常传播和重新抛出
DECLARE
    v_result NUMBER;
    
    FUNCTION divide_numbers(p_num1 NUMBER, p_num2 NUMBER) RETURN NUMBER IS
    BEGIN
        IF p_num2 = 0 THEN
            RAISE_APPLICATION_ERROR(-20001, '除数不能为零');
        END IF;
        RETURN p_num1 / p_num2;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('函数中发生错误: ' || SQLERRM);
            RAISE;  -- 重新抛出异常
    END;
BEGIN
    v_result := divide_numbers(10, 0);
    DBMS_OUTPUT.PUT_LINE('结果: ' || v_result);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('主程序捕获异常: ' || SQLERRM);
END;
/

# 5. 存储过程和函数

-- 创建存储过程
CREATE OR REPLACE PROCEDURE UpdateEmployeeSalary(
    p_emp_id IN NUMBER,
    p_increase_rate IN NUMBER,
    p_result OUT VARCHAR2
) IS
    v_old_salary NUMBER;
    v_new_salary NUMBER;
    v_emp_name VARCHAR2(50);
    
    emp_not_found EXCEPTION;
BEGIN
    -- 获取员工信息
    BEGIN
        SELECT EmployeeName, Salary
        INTO v_emp_name, v_old_salary
        FROM Employees
        WHERE EmployeeID = p_emp_id;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE emp_not_found;
    END;
    
    -- 计算新薪资
    v_new_salary := v_old_salary * (1 + p_increase_rate);
    
    -- 更新薪资
    UPDATE Employees
    SET Salary = v_new_salary
    WHERE EmployeeID = p_emp_id;
    
    -- 返回结果
    p_result := '员工 ' || v_emp_name || ' 薪资从 ' || v_old_salary || 
                ' 调整为 ' || v_new_salary;
    
    COMMIT;
EXCEPTION
    WHEN emp_not_found THEN
        p_result := '错误: 员工ID ' || p_emp_id || ' 不存在';
        ROLLBACK;
    WHEN OTHERS THEN
        p_result := '错误: ' || SQLERRM;
        ROLLBACK;
END UpdateEmployeeSalary;
/

-- 调用存储过程
DECLARE
    v_result VARCHAR2(200);
BEGIN
    UpdateEmployeeSalary(1, 0.1, v_result);
    DBMS_OUTPUT.PUT_LINE(v_result);
END;
/

-- 创建函数
CREATE OR REPLACE FUNCTION CalculateBonus(
    p_salary NUMBER,
    p_performance_rating NUMBER
) RETURN NUMBER IS
    v_bonus NUMBER;
BEGIN
    CASE p_performance_rating
        WHEN 5 THEN v_bonus := p_salary * 0.2;   -- 优秀
        WHEN 4 THEN v_bonus := p_salary * 0.15;  -- 良好
        WHEN 3 THEN v_bonus := p_salary * 0.1;   -- 一般
        WHEN 2 THEN v_bonus := p_salary * 0.05;  -- 较差
        ELSE v_bonus := 0;                       -- 差
    END CASE;
    
    RETURN v_bonus;
EXCEPTION
    WHEN OTHERS THEN
        RETURN 0;
END CalculateBonus;
/

-- 使用函数
SELECT 
    EmployeeName,
    Salary,
    CalculateBonus(Salary, 4) AS Bonus,
    Salary + CalculateBonus(Salary, 4) AS TotalCompensation
FROM Employees
WHERE Department = '技术部';

-- 创建表函数
CREATE OR REPLACE FUNCTION GetDepartmentStats(
    p_dept VARCHAR2
) RETURN SYS_REFCURSOR IS
    v_cursor SYS_REFCURSOR;
BEGIN
    OPEN v_cursor FOR
        SELECT 
            EmployeeName,
            Salary,
            RANK() OVER (ORDER BY Salary DESC) AS SalaryRank,
            ROUND(Salary / SUM(Salary) OVER () * 100, 2) AS SalaryPercent
        FROM Employees
        WHERE Department = p_dept
        ORDER BY Salary DESC;
    
    RETURN v_cursor;
END GetDepartmentStats;
/

-- 使用表函数
DECLARE
    v_cursor SYS_REFCURSOR;
    v_name VARCHAR2(50);
    v_salary NUMBER;
    v_rank NUMBER;
    v_percent NUMBER;
BEGIN
    v_cursor := GetDepartmentStats('技术部');
    
    LOOP
        FETCH v_cursor INTO v_name, v_salary, v_rank, v_percent;
        EXIT WHEN v_cursor%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(v_name || ': ' || v_salary || 
                           ' (排名: ' || v_rank || 
                           ', 占比: ' || v_percent || '%)');
    END LOOP;
    
    CLOSE v_cursor;
END;
/

# 性能优化

# 1. 执行计划分析

-- 查看执行计划
EXPLAIN PLAN FOR
SELECT e.EmployeeName, e.Salary, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.Department = d.DepartmentCode
WHERE e.Salary > 20000
ORDER BY e.Salary DESC;

-- 显示执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 使用AUTOTRACE
SET AUTOTRACE ON EXPLAIN STATISTICS;

SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 20000;

SET AUTOTRACE OFF;

-- 查看实际执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

-- 使用SQL监控
SELECT /*+ MONITOR */ 
    e.EmployeeName, 
    e.Salary,
    s.SaleAmount
FROM Employees e
JOIN Sales s ON e.EmployeeName = s.Salesperson
WHERE e.Salary > 15000;

-- 查看SQL监控报告
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR() FROM DUAL;

# 2. 索引优化

-- 创建B树索引
CREATE INDEX idx_emp_salary ON Employees(Salary);
CREATE INDEX idx_emp_dept_salary ON Employees(Department, Salary);

-- 创建函数索引
CREATE INDEX idx_emp_upper_name ON Employees(UPPER(EmployeeName));

-- 创建位图索引（适用于低基数列）
CREATE BITMAP INDEX idx_emp_dept_bitmap ON Employees(Department);

-- 创建反向键索引
CREATE INDEX idx_emp_id_reverse ON Employees(EmployeeID) REVERSE;

-- 查看索引使用情况
SELECT 
    i.INDEX_NAME,
    i.INDEX_TYPE,
    i.UNIQUENESS,
    i.STATUS,
    s.NUM_ROWS,
    s.DISTINCT_KEYS,
    s.CLUSTERING_FACTOR
FROM USER_INDEXES i
JOIN USER_IND_STATISTICS s ON i.INDEX_NAME = s.INDEX_NAME
WHERE i.TABLE_NAME = 'EMPLOYEES';

-- 分析索引效率
SELECT 
    INDEX_NAME,
    COLUMN_NAME,
    COLUMN_POSITION
FROM USER_IND_COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES'
ORDER BY INDEX_NAME, COLUMN_POSITION;

-- 重建索引
ALTER INDEX idx_emp_salary REBUILD;

-- 收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'IDX_EMP_SALARY');

# 3. SQL优化技巧

-- 使用绑定变量
VARIABLE v_dept VARCHAR2(50);
EXEC :v_dept := '技术部';

SELECT EmployeeName, Salary
FROM Employees
WHERE Department = :v_dept;

-- 避免隐式类型转换
-- 不好的写法
SELECT * FROM Employees WHERE EmployeeID = '1';

-- 好的写法
SELECT * FROM Employees WHERE EmployeeID = 1;

-- 使用EXISTS替代IN（当子查询返回大量数据时）
-- 使用EXISTS
SELECT e.EmployeeName
FROM Employees e
WHERE EXISTS (
    SELECT 1 FROM Sales s 
    WHERE s.Salesperson = e.EmployeeName
);

-- 使用分析函数替代子查询
-- 不好的写法
SELECT 
    EmployeeName,
    Salary,
    (SELECT AVG(Salary) FROM Employees e2 WHERE e2.Department = e1.Department) AS DeptAvg
FROM Employees e1;

-- 好的写法
SELECT 
    EmployeeName,
    Salary,
    AVG(Salary) OVER (PARTITION BY Department) AS DeptAvg
FROM Employees;

-- 使用CASE替代多个查询
SELECT 
    Department,
    SUM(CASE WHEN Salary > 30000 THEN 1 ELSE 0 END) AS HighSalaryCount,
    SUM(CASE WHEN Salary BETWEEN 20000 AND 30000 THEN 1 ELSE 0 END) AS MidSalaryCount,
    SUM(CASE WHEN Salary < 20000 THEN 1 ELSE 0 END) AS LowSalaryCount
FROM Employees
GROUP BY Department;

# 高可用性和备份

# 1. Data Guard 配置

-- 在主库上启用归档模式
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- 启用强制日志记录
ALTER DATABASE FORCE LOGGING;

-- 创建备库控制文件
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/backup/standby.ctl';

-- 配置Data Guard参数
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STANDBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY';
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE;
ALTER SYSTEM SET FAL_SERVER=STANDBY;
ALTER SYSTEM SET FAL_CLIENT=PRIMARY;

-- 查看Data Guard状态
SELECT 
    DEST_ID,
    STATUS,
    DESTINATION,
    ERROR
FROM V$ARCHIVE_DEST
WHERE DEST_ID <= 2;

-- 查看归档日志应用状态
SELECT 
    SEQUENCE#,
    APPLIED,
    COMPLETION_TIME
FROM V$ARCHIVED_LOG
WHERE DEST_ID = 2
ORDER BY SEQUENCE# DESC;

# 2. RMAN 备份

-- 配置RMAN
RMAN TARGET /

-- 配置备份参数
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';

-- 全库备份
BACKUP DATABASE PLUS ARCHIVELOG;

-- 增量备份
BACKUP INCREMENTAL LEVEL 0 DATABASE;
BACKUP INCREMENTAL LEVEL 1 DATABASE;

-- 备份表空间
BACKUP TABLESPACE USERS;

-- 备份控制文件和参数文件
BACKUP CURRENT CONTROLFILE;
BACKUP SPFILE;

-- 查看备份信息
LIST BACKUP SUMMARY;
LIST BACKUP OF DATABASE;

-- 验证备份
VALIDATE BACKUP;

-- 恢复数据库
RESTORE DATABASE;
RECOVER DATABASE;

-- 恢复表空间
RESTORE TABLESPACE USERS;
RECOVER TABLESPACE USERS;

-- 恢复到指定时间点
RUN {
    SET UNTIL TIME "TO_DATE('2023-12-25 14:30:00', 'YYYY-MM-DD HH24:MI:SS')";
    RESTORE DATABASE;
    RECOVER DATABASE;
}

# 最佳实践

# 1. 数据库设计

合理使用Oracle数据类型（VARCHAR2而不是VARCHAR）
使用序列生成主键
适当使用分区表处理大数据量
使用对象类型和集合类型处理复杂数据结构

# 2. PL/SQL开发

使用%TYPE和%ROWTYPE提高代码可维护性
合理使用游标，避免过度使用
实施完善的异常处理机制
使用绑定变量提高性能

# 3. 性能优化

定期收集统计信息
合理创建和维护索引
使用分析函数替代复杂子查询
监控和优化SQL执行计划

# 4. 安全管理

实施最小权限原则
使用角色管理权限
启用审计功能
定期更新Oracle版本

# 5. 高可用性

配置Data Guard实现灾难恢复
制定完善的备份策略
定期测试恢复过程
监控数据库性能和可用性

通过掌握这些Oracle特定的功能和最佳实践，可以充分利用Oracle数据库的强大特性，构建高性能、可靠的企业级数据库应用。

SQL Server 充电宝小程序实现

Jeskson-微服务分布式

Choose mode

Oracle 特定语法

# Oracle 特定语法

# 概述

# Oracle 数据类型

# 1. 基本数据类型

# 2. 用户定义类型

# Oracle 特有功能

# 1. 序列 (SEQUENCE)

# 2. 分析函数和窗口函数

# 3. 层次查询

# 4. PIVOT 和 UNPIVOT

# 5. 正则表达式函数

# PL/SQL 编程

# 1. 基本语法结构

# 2. 控制结构

# 3. 游标处理

# 4. 异常处理

# 5. 存储过程和函数

# 性能优化

# 1. 执行计划分析

# 2. 索引优化

# 3. SQL优化技巧

# 高可用性和备份

# 1. Data Guard 配置

# 2. RMAN 备份

# 最佳实践

# 1. 数据库设计

# 2. PL/SQL开发

# 3. 性能优化

# 4. 安全管理

# 5. 高可用性