Oracle超详细（数据库编程）

NUMBER：Oracle中最常用的数值类型，用于存储整数或带有小数点的数字。可以指定精度(precision)和标度(scale)，即数字的总位数和小数点后的位数。例如，NUMBER(5,2)可以存储最大为999.99的数值。
INTEGER：整数类型，是NUMBER类型的一种特殊形式，用于存储不带小数的整数。它占用较小的存储空间，适用于对存储空间要求较高的场景。
FLOAT：浮点数类型，用于存储近似值，具有比NUMBER类型更大的范围和较低的精度要求。可以指定二进制位数(precision)和十进制位数(scale)。
BINARY_FLOAT和BINARY_DOUBLE：二进制浮点数类型，分别用于存储单精度和双精度浮点数。它们提供了高精度的科学计算和工程领域的支持。

（二）字符型数据类型

CHAR：定长字符类型，用于存储指定长度的字符串。如果存储的字符串长度小于指定的长度，Oracle会用空格填充以达到其最大长度。
VARCHAR2：可变长度字符类型，用于存储可变长度的字符串。它只占用实际需要的存储空间，提高了存储效率。
NCHAR和NVARCHAR2：Unicode字符类型，分别对应定长和可变长度的Unicode字符串。它们用于存储国际化和多语言文本。
LONG：长字符串数据类型，但已不推荐使用，建议使用CLOB类型来替代。

（三）日期和时间型数据类型

DATE：日期时间类型，可以存储世纪、年、月、日、时、分和秒的信息。但它不包含时区信息。
TIMESTAMP：时间戳类型，提供了比DATE类型更高的精确度，可以存储到毫秒级，并可以用小数秒表示。
INTERVAL：时间间隔类型，用于存储两个日期或时间之间的差异，可以表示年、月、日、小时、分钟或秒之间的差异。

（四）大对象和二进制数据类型

CLOB：字符大对象类型，用于存储大量的字符数据，包括长文本和文档。最大可以存储4GB的数据。
BLOB：二进制大对象类型，用于存储二进制数据，如图像、视频和音频文件。最大也可以存储4GB的数据。
NCLOB：大规模Unicode字符数据类型，与CLOB类似，但用于存储Unicode字符数据。
RAW：原始二进制类型，用于存储不需要进行字符集转换的二进制数据。最大长度为2000字节，但在某些情况下，如LONG RAW，最大长度可以达到8TB。

（五）其他数据类型

NULL：表示缺失或未知的数据。
ROWID：唯一标识一行数据的行号，是Oracle数据库专有的数据类型。
BOOLEAN：布尔类型，用于存储真或假的值。在Oracle中，布尔类型是通过PL/SQL程序的特殊数据类型实现的。

（六）复合数据类型

ARRAY：数组类型，可以包含多个单一数据类型的值。
TABLE：表类型，包含多行和多列的数据。
REF：引用类型，指向另一个对象。

（七）XMLType数据类型

XMLType：专门用于处理XML文档的数据类型，提供了丰富的XML处理函数和操作符。

Oracle数据库中的数据类型丰富多样，每种数据类型都有其特定的用途和适用场景。了解和掌握这些数据类型的特点和用法，对于设计高效、合理的数据库表结构至关重要。

二、运算部分

Oracle数据库中的运算部分是一个功能强大的工具集，它允许开发者执行各种数学、字符串、日期时间、逻辑、关系以及集合运算，以满足复杂的数据库查询和处理需求。以下是对这些运算类型的详细概述：

（一）算术运算

定义：包括加（+）、减（-）、乘（*）、除（/）等基本操作，以及取模（MOD）、幂运算（使用POWER函数）等。
用途：处理数值数据类型的字段，进行基本的数学计算。

-- 计算员工的年薪  
SELECT employee_id, first_name, last_name, salary * 12 AS annual_salary  
FROM employees;  
  
-- 计算两个部门的总预算差异  
SELECT (SELECT SUM(budget) FROM departments WHERE department_id = 10)  
     - (SELECT SUM(budget) FROM departments WHERE department_id = 20) AS budget_difference  
FROM dual;

（二）字符串运算

定义：涉及字符串的拼接、截取、替换、大小写转换等操作。
函数：包括||（字符串拼接）、CONCAT()、SUBSTR()、INSTR()、REPLACE()、UPPER()、LOWER()、INITCAP()等。
用途：处理文本数据，进行文本分析和格式化。

-- 拼接员工的名字和姓氏  
SELECT first_name || ' ' || last_name AS full_name  
FROM employees;  
  
-- 替换字符串中的特定部分  
SELECT REPLACE(job_title, 'Manager', 'Mgr') AS modified_job_title  
FROM employees;  
  
-- 截取字符串的一部分（例如，截取电子邮件地址中@符号之前的部分）  
SELECT SUBSTR(email, 1, INSTR(email, '@') - 1) AS email_before_at  
FROM employees;

（三）日期和时间运算

定义：允许进行日期的加减、提取日期的各个部分、计算两个日期之间的差异等操作。
函数：包括TRUNC()、ROUND()（在日期上下文中）、MONTHS_BETWEEN()、ADD_MONTHS()、SYSDATE等。
用途：处理日期和时间数据，进行时间计算和日期格式化。

-- 获取当前日期  
SELECT SYSDATE AS today_date FROM dual;  
  
-- 计算员工入职以来的月份数  
SELECT employee_id, MONTHS_BETWEEN(SYSDATE, hire_date) AS months_since_hire  
FROM employees;  
  
-- 截取日期的年份部分  
SELECT TRUNC(hire_date, 'YEAR') AS hire_year  
FROM employees;

（四）逻辑运算

定义：用于在SQL查询中根据条件进行筛选或计算。
运算符：包括AND、OR、NOT、IN、LIKE、BETWEEN等。
用途：构建复杂的查询条件，控制数据的筛选和逻辑处理。

-- 查询工资高于50000且部门编号为10的员工  
SELECT *  
FROM employees  
WHERE salary > 50000 AND department_id = 10;  
  
-- 使用IN运算符查询特定部门的员工  
SELECT *  
FROM employees  
WHERE department_id IN (10, 20, 30);  
  
-- 使用LIKE运算符查询名字以'J'开头的员工  
SELECT *  
FROM employees  
WHERE first_name LIKE 'J%';

（五）关系运算

定义：用于比较两个或多个值之间关系的操作。
运算符：包括=、<>（或!=）、>、<、>=、<=等。
用途：在WHERE子句、HAVING子句以及表达式中控制数据的筛选和逻辑判断。

（六）集合运算

定义：将多个查询的结果组合成一个单一的结果集。
类型：包括UNION（去重）、UNION ALL（保留重复）、INTERSECT（取交集）、MINUS（差集）。
用途：合并或比较多个查询结果，进行数据的汇总和分析。

-- 查询部门10和部门20的员工（去重）  
SELECT employee_id, first_name, last_name  
FROM (  
    SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10  
    UNION  
    SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 20  
);  
  
-- 查询部门10和部门20的员工（包括重复，但通常不会这样设计）  
SELECT employee_id, first_name, last_name  
FROM (  
    SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10  
    UNION ALL  
    SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 20  
);  
  
-- 注意：INTERSECT和MINUS通常用于不同表或查询结果的集合操作，此处不直接适用于单个employees表示例  
-- 假设有两个不同的查询结果集，则可以使用INTERSECT或MINUS

（七）聚合运算

定义：对一组值执行计算，并返回单个值。
函数：包括SUM()、AVG()、MAX()、MIN()、COUNT()等。
用途：在分组查询（GROUP BY）中计算总和、平均值、最大值、最小值或计数。

-- 计算每个部门的员工数  
SELECT department_id, COUNT(*) AS number_of_employees  
FROM employees  
GROUP BY department_id;  
  
-- 计算每个部门的平均工资  
SELECT department_id, AVG(salary) AS average_salary  
FROM employees  
GROUP BY department_id;  
  
-- 使用HAVING子句过滤聚合结果  
SELECT department_id, AVG(salary) AS average_salary  
FROM employees  
GROUP BY department_id  
HAVING AVG(salary) > 50000;

（八）数据类型转换

定义：将一种数据类型转换为另一种数据类型。
函数：包括TO_NUMBER()、TO_DATE()、TO_CHAR()等。
用途：在需要不同数据类型之间进行转换时，确保数据的一致性和准确性。

-- 将字符串转换为数字进行比较（假设employee_id可以部分转换为数字）  
SELECT *  
FROM employees  
WHERE TO_NUMBER(SUBSTR(employee_id, 1, 3)) > 100; -- 假设employee_id的前三位是有效的数字  
  
-- 将日期字符串转换为日期类型  
SELECT TO_DATE('2023-04-01', 'YYYY-MM-DD') AS specific_date FROM dual;  
  
-- 将数字转换为字符串进行拼接  
SELECT 'The employee ID is ' || TO_CHAR(employee_id) AS employee_id_message  
FROM employees;

注意事项

数据类型一致性：在进行运算时，必须确保参与运算的数据类型一致，否则Oracle可能会尝试进行隐式类型转换，这可能会影响运算的结果。
NULL值处理：NULL值在运算中有特殊的行为，任何与NULL值进行比较或运算的操作都可能返回NULL。因此，在处理NULL值时，需要使用特殊的IS NULL或IS NOT NULL条件。
性能考虑：某些运算（特别是集合运算和复杂的聚合运算）可能会对性能产生影响。在设计查询时，需要考虑这一点，并尽量通过优化查询逻辑和索引来减少运算的复杂度。

综上所述，Oracle数据库中的运算部分是一个功能丰富且强大的工具集，它允许开发者执行各种类型的运算和操作，以满足复杂的数据库查询和处理需求。通过合理使用这些运算，开发者可以高效地查询、分析和处理存储在Oracle数据库中的数据。

三、DDL\DQL\DML

在Oracle数据库中，DQL（Data Query Language）、DML（Data Manipulation Language）和DDL（Data Definition Language）是SQL（Structured Query Language）的三个主要部分，它们各自承担着不同的功能和作用。

（一）DDL（数据定义语言）

DDL用于定义和修改数据库的结构。它主要包括CREATE、ALTER和DROP语句，允许用户创建新的数据库对象（如表、索引、视图等），修改现有数据库对象的结构，或删除数据库对象。

1.主要特点包括

CREATE语句用于创建新的数据库对象，如CREATE TABLE用于创建新表。
ALTER语句用于修改现有数据库对象的结构，如ALTER TABLE用于修改表结构。
DROP语句用于删除数据库对象，如DROP TABLE用于删除表。
DDL操作对数据库的结构进行定义和修改，是数据库设计和维护的重要部分。

2.创建一个新表

CREATE TABLE employees (  
    employee_id NUMBER(10) PRIMARY KEY,  
    first_name VARCHAR2(50),  
    last_name VARCHAR2(50),  
    email VARCHAR2(100) UNIQUE,  
    hire_date DATE,  
    salary NUMBER(8, 2)  
);

这个DDL语句创建了一个名为employees的新表，包含员工ID、名字、姓氏、电子邮件、入职日期和薪资等字段。

3.修改表结构

ALTER TABLE employees ADD (department_id NUMBER(10));

这个DDL语句向employees表中添加了一个新列department_id。

4.删除表

DROP TABLE employees;

注意：在实际应用中，删除表是一个危险的操作，因为它会永久移除表及其所有数据。在执行此操作之前，请确保您确实想要删除该表，并且已经备份了任何重要数据。

（二）DQL（数据查询语言）

DQL主要用于从数据库中检索数据。它主要包括SELECT语句，允许用户根据指定的条件查询数据。DQL是用户与数据库进行交互时最常用的部分之一，因为它允许用户获取存储在数据库中的信息。

1.主要特点包括

使用SELECT语句进行数据查询。
可以结合WHERE子句、GROUP BY子句、HAVING子句、ORDER BY子句等进行复杂的数据筛选和排序。
可以使用聚合函数（如SUM、AVG、COUNT等）进行数据统计。

2.查询数据

SELECT first_name, last_name, email, salary  
FROM employees  
WHERE department_id = 10;

这个DQL语句查询了employees表中所有department_id为10的员工的名字、姓氏、电子邮件和薪资。

（三）DML（数据操作语言）

DML用于对数据库中的数据进行增、删、改操作。它主要包括INSERT、UPDATE和DELETE语句，允许用户根据需要对数据库中的数据进行修改。

1.主要特点包括

INSERT语句用于向数据库表中插入新的数据行。
UPDATE语句用于修改数据库表中已存在的数据行。
DELETE语句用于从数据库表中删除数据行。
DML操作直接影响数据库中的数据，因此在进行这些操作时需要谨慎。

2.插入数据

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department_id)  
VALUES (1, 'John', 'Doe', 'john.doe@example.com', SYSDATE, 50000, 10);

这个DML语句向employees表中插入了一行新数据。

3.更新数据

UPDATE employees  
SET salary = 55000  
WHERE employee_id = 1;

这个DML语句将employee_id为1的员工的薪资更新为55000。

4.删除数据

DELETE FROM employees  
WHERE employee_id = 1;

这个DML语句从employees表中删除了employee_id为1的行。

综上所述，DQL、DML和DDL在Oracle数据库中各自承担着不同的角色和功能，共同构成了SQL语言的核心部分。它们允许用户以结构化的方式与数据库进行交互，从而实现对数据的查询、修改和定义操作。

四、函数

Oracle数据库中包含了多种类型的函数，这些函数丰富了SQL的语言功能，提供了更多的操作性。Oracle中的函数主要可以分为单行函数和聚合函数两大类。下面分别介绍这两类函数中的一些常用函数。

（一）单行函数

单行函数对每一行输入执行操作，并返回结果集中的每行一个值。Oracle中的单行函数主要包括字符函数、数字函数、日期函数、转换函数以及其他单行函数。

1.字符函数

ASCII(X)：返回字符X的ASCII码。
CONCAT(X,Y)：连接字符串X和Y。
INSTR(X,STR,START)：在字符串X中查找STR，返回STR在X中第N次出现的位置，如果不指定START，则从字符串的第一个字符开始查找。
LENGTH(X)：返回字符串X的长度。
LOWER(X)：将字符串X转换为小写。
UPPER(X)：将字符串X转换为大写。
LTRIM(X[,TRIM_STR])：把X的左边截去TRIM_STR字符串，缺省截去空格。
RTRIM(X[,TRIM_STR])：把X的右边截去TRIM_STR字符串，缺省截去空格。
TRIM([TRIM_STR FROM]X)：把X的两边截去TRIM_STR字符串，缺省截去空格。
REPLACE(X,old,new)：在X中查找old，并替换成new。
SUBSTR(X,start[,length])：返回X的字串，从start处开始，截取length个字符，缺省length，默认到结尾。

2.数字函数

ABS(X)：返回X的绝对值。
CEIL(X)：返回大于或等于X的最小整数。
FLOOR(X)：返回小于或等于X的最大整数。
LOG(X,Y)：返回X为底Y的对数。
MOD(X,Y)：返回X除以Y的余数。
POWER(X,Y)：返回X的Y次幂。
ROUND(X[,Y])：对X进行四舍五入，Y是保留的小数位数，默认为0。
SQRT(X)：返回X的平方根。
TRUNC(X[,Y])：对X进行截断，Y是保留的小数位数，默认为0。

3.日期函数

SYSDATE：返回当前日期和时间。
ADD_MONTHS(d,n)：在日期d上加上n个月。
LAST_DAY(d)：返回指定日期d所在月份的最后一天。
ROUND(d[,fmt])：根据fmt格式对日期d进行四舍五入。
TRUNC(d[,fmt])：根据fmt格式对日期d进行截断。
EXTRACT(fmt FROM d)：从日期d中提取fmt指定的部分，fmt可以是YEAR、MONTH、DAY等。

4.转换函数

TO_CHAR(X[,fmt])：将X转换为fmt格式的字符串，X可以是日期、数字等。
TO_DATE(X[,fmt])：将字符串X按照fmt格式转换为日期。
TO_NUMBER(X[,fmt])：将字符串X按照fmt格式转换为数字。

（二）聚合函数

聚合函数同时对多行数据进行操作，并返回一个结果。

SUM(X)：返回X列的总和。
AVG(X)：返回X列的平均值。
COUNT(X)：返回X列的行数，如果X是星号（*），则返回所有的行数。
MAX(X)：返回X列的最大值。
MIN(X)：返回X列的最小值。

Oracle中的函数非常丰富，上述只是其中的一部分常用函数。在实际应用中，可以根据需要选择合适的函数来完成各种数据处理任务。

五、PLSQL

Oracle中的PL/SQL是一种强大的过程化编程语言，它扩展了SQL语言，允许在数据库中编写复杂的程序。以下是PL/SQL的一些关键概念及其语法结构的概述，包括条件判断、循环控制、游标、存储过程、自定义函数、包（Package）以及它们在应用中的使用。

（一）基本语法结构

PL/SQL程序通常包含在一个或多个块中，每个块都有可选的声明部分、执行部分和异常处理部分。

DECLARE  
  -- 声明部分：变量、类型、游标等的声明  
BEGIN  
  -- 执行部分：包含SQL语句和PL/SQL语句  
  -- 可以调用存储过程、函数等  
EXCEPTION  
  -- 异常处理部分：处理执行过程中可能发生的错误  
END;

（二）条件判断

PL/SQL使用IF-THEN-ELSIF-ELSE语句进行条件判断。

IF condition1 THEN  
  -- 当condition1为真时执行的语句  
ELSIF condition2 THEN  
  -- 当condition1为假且condition2为真时执行的语句  
ELSE  
  -- 当condition1和condition2都为假时执行的语句  
END IF;

（三）循环控制

PL/SQL支持几种循环结构，包括LOOP、WHILE LOOP和FOR LOOP。

LOOP：简单的无限循环，直到遇到EXIT语句。

LOOP  
  -- 循环体  
  EXIT WHEN some_condition; -- 退出循环的条件  
END LOOP;

WHILE LOOP：当条件为真时执行循环体。

WHILE condition LOOP  
  -- 循环体  
END LOOP;

FOR LOOP：基于范围的循环，常用于遍历数组或集合。

FOR i IN 1..10 LOOP  
  -- 循环体，i从1递增到10  
END LOOP;

（四）游标

游标用于从SELECT语句返回的结果集中逐行检索数据。

DECLARE  
  CURSOR c_emp IS SELECT employee_id, first_name FROM employees;  
  v_emp_id employees.employee_id%TYPE;  
  v_first_name employees.first_name%TYPE;  
BEGIN  
  OPEN c_emp;  
  LOOP  
    FETCH c_emp INTO v_emp_id, v_first_name;  
    EXIT WHEN c_emp%NOTFOUND;  
    -- 处理每行数据  
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id || ', Name: ' || v_first_name);  
  END LOOP;  
  CLOSE c_emp;  
END;

（五）存储过程

存储过程是存储在数据库中的一组为了完成特定功能的SQL语句和PL/SQL语句。

CREATE OR REPLACE PROCEDURE add_employee(  
  p_employee_id IN employees.employee_id%TYPE,  
  p_first_name IN employees.first_name%TYPE,  
  p_last_name IN employees.last_name%TYPE)  
IS  
BEGIN  
  INSERT INTO employees (employee_id, first_name, last_name)  
  VALUES (p_employee_id, p_first_name, p_last_name);  
  COMMIT;  
END add_employee;

（六）自定义函数

自定义函数用于返回一个值。

CREATE OR REPLACE FUNCTION get_employee_name(p_employee_id IN employees.employee_id%TYPE)  
  RETURN employees.first_name%TYPE  
IS  
  v_first_name employees.first_name%TYPE;  
BEGIN  
  SELECT first_name INTO v_first_name  
  FROM employees  
  WHERE employee_id = p_employee_id;  
  RETURN v_first_name;  
EXCEPTION  
  WHEN NO_DATA_FOUND THEN  
    RETURN NULL;  
END get_employee_name;

（七）包（Package）

包是PL/SQL中一种将逻辑上相关的存储过程、函数、类型、变量等封装在一起的数据库对象。

CREATE OR REPLACE PACKAGE emp_pkg AS  
  
  -- 类型、变量、游标等声明  
  TYPE emp_cur_type IS REF CURSOR;  
  
  -- 存储过程、函数声明  
  PROCEDURE add_emp(p_emp_id IN employees.employee_id%TYPE, ...);  
  FUNCTION get_emp_name(p_emp_id IN employees.employee_id%TYPE) RETURN VARCHAR2;  
  
END emp_pkg;

CREATE OR REPLACE PACKAGE BODY emp_pkg AS  
  
  -- 存储过程、函数的实现  
  PROCEDURE add_emp(p_emp_id IN employees.employee_id%TYPE, ...) IS  
  BEGIN  
    -- 实现代码  
  END add_emp;  
  
  FUNCTION get_emp_name(p_emp_id IN employees.employee_id%TYPE) RETURN VARCHAR2 IS  
    v_name employees.first_name%TYPE;  
  BEGIN  
    -- 实现代码  
    RETURN v_name;  
  END get_emp_name;  
  
END emp_pkg;

（八）应用

PL/SQL的这些特性在数据库应用开发中非常有用，它们允许开发者将业务逻辑直接嵌入到数据库中，从而提高应用程序的性能和可维护性。例如，在复杂的查询处理、数据验证、事务控制、报告生成等场景中，PL/SQL都发挥着重要作用。通过创建存储过程和函数，可以将常用的数据库操作封装起来，供应用程序通过简单的SQL调用即可执行复杂的业务逻辑。

六、SQL优化

Oracle中的SQL优化是提高数据库查询性能的重要手段，通过一系列策略和技术，可以显著减少查询响应时间，提高数据检索速度，并降低系统资源消耗。以下是一些Oracle SQL优化的主要方法和技巧：

（一）获取正确的执行计划

使用EXPLAIN PLAN工具来查看SQL语句的执行计划，了解Oracle是如何执行SQL语句的。
通过DBMS_XPLAN.DISPLAY函数查看执行计划的详细信息，分析是否使用了索引、是否进行了全表扫描等。

（二）索引优化

合理选择索引：根据查询需求和数据分布，选择合适的列创建索引。复合索引应尽可能覆盖查询中的条件列和排序列。
避免索引失效：注意索引失效的情况，如统计信息不准确、隐式转换、条件列上存在运算符等。
定期维护索引：定期重建或重组索引，以保持其性能和效率。

（三）查询语句优化

避免SELECT *：只查询需要的列，避免使用SELECT *，以减少数据传输量和解析时间。
优化WHERE子句：将过滤掉最大数量记录的条件放在WHERE子句的末尾，以减少处理的数据量。
使用绑定变量：使用绑定变量可以减少SQL解析的时间，提高SQL语句的执行效率。
替代低效的子查询：尽量避免使用复杂的子查询，可以考虑使用JOIN操作替代。
使用EXISTS替代IN：在可能的情况下，使用EXISTS替代IN，因为EXISTS通常更高效。

（四）表和分区优化

合理设计表结构：通过数据库正规化减少数据冗余，提高数据一致性。
使用分区表：对大表进行分区可以提高查询性能，减少数据维护的开销。

（五）其他优化技巧

调整数据库参数：根据数据库负载情况，合理调整数据库参数，如db_file_multiblock_read_count、db_block_size等。
使用并行处理：利用Oracle的并行处理功能，将查询任务分解为多个子任务同时执行，提高处理速度。
定期维护数据库：定期重建索引、清理无用数据、更新统计信息等，保持数据库性能。