数据库教程FGMT05‑Oracle数据库SQL语言开发与应用实战

## 前言

SQL是Oracle数据库与上层业务交互的标准语言，无论是应用开发人员，还是DBA运维工程师，都必须熟练掌握DDL、DML、DQL、PL/SQL整套语法体系。我是风哥，在大量项目实施工作中，很多生产故障根源来自不规范的SQL编写：约束缺失引发脏数据、索引设计不合理造成全表扫描、长事务忘记提交引发锁阻塞、PL/SQL游标泄露产生ORA‑01000报错。风哥 itpux-com

本文基于Oracle19c单机数据库，硬件规格为**单节点64G内存、8CPU**，数据库实例与数据库名称`fgedudb`，业务操作用户`fgedu`，本地软件路径全部统一替换为`/fgedudb`。教程完整覆盖SQL语言分类、数据类型、DDL对象管理、DML事务控制、DQL各类查询、内置函数、多表连接、子查询、分析函数、PL/SQL编程（匿名块、存储过程、函数、触发器、程序包），配套大量可直接运行的实战SQL脚本，兼顾业务开发规范与DBA运维视角。为后续SQL性能调优、故障排查打下扎实基础。

**内容大纲：**

1. Oracle SQL与PL/SQL体系介绍，64G/8CPU实例相关参数基线，实验环境准备

2. 基础理论：SQL五大语言分类、Oracle数据类型、表与约束原理、索引分类原理、视图/同义词/序列原理、事务与锁机制、PL/SQL程序单元原理

3. 查询理论：基础查询、过滤排序、聚合分组、多表连接、子查询、分析窗口函数、集合运算原理

4. PL/SQL理论：变量、游标、异常处理、存储过程、函数、触发器、程序包原理

5. 完整实战操作：环境初始化、DDL建表/约束/索引/视图/同义词/序列、DML增删改事务演练、全套DQL查询实战、内置函数实战、PL/SQL匿名块、存储过程、函数、触发器、包开发、数据字典查询、执行计划分析

6. SQL开发生产规范与巡检脚本

7. 全文总结，生产SQL开发最佳实践

## 一、Oracle SQL开发基础理论

### 1.1 Oracle SQL与PL/SQL整体体系

Oracle结构化查询语言分为五大类别：DDL数据定义语言、DML数据操纵语言、DQL数据查询语言、DCL数据控制语言、TCL事务控制语言。

- **DDL（Data Definition Language）**：`CREATE / ALTER / DROP / TRUNCATE / RENAME`，管理数据库对象，DDL执行会隐式提交事务，执行完毕不可rollback回滚，生产执行DDL必须规划变更窗口。风哥教程 itpux_com

- **DML（Data Manipulation Language）**：`INSERT / UPDATE / DELETE / MERGE`，业务数据增删改，不会自动提交，依赖`COMMIT`提交、`ROLLBACK`回滚。

- **DQL（Data Query Language）**：`SELECT`查询语句，仅读取数据，不修改业务内容。

- **DCL（Data Control Language）**：`GRANT / REVOKE`权限授予回收。

- **TCL（Transaction Control Language）**：`COMMIT / ROLLBACK / SAVEPOINT`事务保存点控制。

PL/SQL是Oracle对SQL的过程化扩展，支持变量定义、条件判断、循环逻辑、异常捕获，可编写匿名块、存储过程、自定义函数、触发器、程序包，将业务逻辑封装在数据库服务端运行。网上搜索风哥教程可以学习全套数据库教程

> 

> 基线环境硬件64G内存8CPU，实例`fgedudb`，与SQL开发相关关键spfile参数：

> 

> 

> | 参数 | 参数值 | 说明 |

> | --- | --- | --- |

> | memory_target | 48G | 实例总内存，预留16G操作系统 |

> | processes | 2000 | 最大并发进程，适配业务大量会话连接 |

> | open_cursors | 500 | 单会话最大打开游标，防止游标泄露ORA‑01000报错 |

> | session_cached_cursors | 300 | 会话游标缓存，降低SQL软解析CPU消耗 |

> | undo_retention | 900 | undo保留900秒，闪回查询、一致性读依赖undo数据 |

> | parallel_max_servers | 16 | 并行执行进程上限，适配8CPU硬件规格 |

### 1.2 Oracle常用数据类型理论

1. **字符类型**

`VARCHAR2(n)`可变长字符串，Oracle19c最大支持32767字节；`CHAR(n)`定长字符；`CLOB`大文本对象，用于存储超长文本文档。

2. **数值类型**

`NUMBER(p,s)`，p总有效数字位数，s小数位；业务金额强烈推荐number类型，避免浮点数精度丢失；`INTEGER`属于number子类型。

3. **时间日期类型**

`DATE`保存年月日时分秒，业务系统首选；`TIMESTAMP`支持毫秒、微秒时间戳；`TIMESTAMP WITH TIME ZONE`带时区时间。

4. **二进制类型**

`BLOB`二进制大对象，保存图片、文件二进制字节流。

### 1.3 表、五类约束原理

表是schema最核心对象，约束保障业务数据完整性，分为五类约束：

1. **PRIMARY KEY主键**：非空+唯一，一张表仅允许一个主键；

2. **UNIQUE唯一约束**：字段值不可重复，允许NULL空值；

3. **NOT NULL非空约束**：列不允许存储NULL；

4. **CHECK检查约束**，自定义字段取值校验规则；

5. **FOREIGN KEY外键约束**，参照父表主键，维护主从表参照完整性。

> 

> 生产提示：大批量数据导入场景，可临时disable约束，导入完成后enable validate，提升导入性能；外键会增加DML开销，大并发OLTP业务需要权衡。

### 1.4 索引原理理论

索引是优化查询性能的数据库对象，主流索引类型：

1. **B‑Tree普通索引**：Oracle默认索引，适合等值、范围过滤条件；

2. **复合索引**：多字段联合B‑Tree索引，遵循前缀匹配原则；

3. **函数索引**：对函数运算结果建立索引，解决where子句使用函数导致索引失效问题；

4. **唯一索引**：索引字段不允许重复；

5. **位图索引**：适合数据仓库低并发DML场景，OLTP业务禁止大量使用位图索引。

> 

> 生产误区：索引不是越多越好，索引会加重insert/update/delete开销，DML频繁业务严控索引数量。

### 1.5 视图、同义词、序列理论

1. **视图VIEW**：本质是存储的SELECT语句，不存储物理数据；简化复杂多表查询，做权限隔离；分为普通视图、只读视图（WITH READ ONLY）。

2. **同义词SYNONYM**：对象别名，私有同义词仅当前schema可见，公共同义词全库可见，需要DBA权限创建。

3. **SEQUENCE序列**，生成自增数字，用于业务主键ID；`NEXTVAL`取下一个序列值，`CURRVAL`读取当前值；CACHE缓存序列值，减少数据库字典锁竞争，提升高并发性能。风哥数据库教程 itpux-com

### 1.6 事务、锁机制理论

Oracle事务从第一条DML语句开启，以commit或者rollback结束。未提交修改仅当前会话可见，其他会话读取undo回滚段旧数据，实现多版本一致性读。

DML操作产生行级锁，行锁不会阻塞普通SELECT查询；长事务不提交会持续持有行锁，引发业务会话阻塞等待。

`TRUNCATE`属于DDL语句，清空表全部数据，释放段空间，不生成undo，不可回滚，执行速度远高于delete全表删除，生产谨慎使用。

### 1.7 DQL查询语法结构理论

完整SELECT语法执行顺序：

`SELECT‑FROM‑WHERE‑GROUP BY‑HAVING‑ORDER BY`

- WHERE：分组**之前**做行过滤；

- HAVING：分组聚合**之后**过滤聚合结果；

多表连接分为：INNER JOIN内连接、LEFT/RIGHT外连接、FULL全外连接；推荐ANSI标准JOIN写法，可读性优于Oracle旧风格`(+)`写法。

子查询分为单行子查询、多行子查询IN/ANY/ALL、关联子查询、from派生表子查询。分析窗口函数OVER()，实现分组内排名、累计求和，是报表统计核心语法。

### 1.8 PL/SQL程序单元理论

1. **匿名块**：临时PL/SQL代码，不持久化存储数据库，执行完毕消失，用于脚本、调试；

2. **存储过程PROCEDURE**：封装业务逻辑，支持入参、出参，数据库持久保存；

3. **自定义FUNCTION函数**，必须有返回值，可以直接用于SQL语句；

4. **触发器TRIGGER**：表发生INSERT/UPDATE/DELETE时自动触发执行；

5. **程序包PACKAGE**：包头定义接口，包体实现具体逻辑，模块化管理过程、函数、变量。

PL/SQL核心要素：变量、%TYPE/%ROWTYPE类型、游标、异常捕获处理；生产必须注意游标关闭，防止游标泄露造成ORA‑01000报错。

## 二、生产完整实战操作

> 

> 说明：操作系统RHEL7，硬件64G内存8CPU；数据库实例`fgedudb`，全部路径替换`/fgedudb`；sys用户执行环境初始化，业务操作切换`fgedu`用户；所有脚本务必先测试环境验证，生产执行前完成变更评审。

### 2.1 实验环境初始化（sys用户执行）

登录数据库

```

su - oracle

export ORACLE_BASE=/fgedudb/app

export ORACLE_HOME=/fgedudb/app/product/19.0.0/dbhome_1

export ORACLE_SID=fgedudb

sqlplus / as sysdba

```

创建业务表空间、临时表空间，业务用户fgedu，授予基础权限

```

CREATE TABLESPACE fgedu_biz

DATAFILE '+DATA/fgedudb/fgedu_biz01.dbf' SIZE 2G AUTOEXTEND ON NEXT 512M MAXSIZE 15G

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TEMPORARY TABLESPACE fgedu_temp

TEMPFILE '+DATA/fgedudb/fgedu_temp01.tmp' SIZE 1G AUTOEXTEND ON NEXT 256M MAXSIZE 10G;

CREATE USER fgedu IDENTIFIED BY Fgedu@123

DEFAULT TABLESPACE fgedu_biz

TEMPORARY TABLESPACE fgedu_temp

ACCOUNT UNLOCK;

GRANT CREATE SESSION,CREATE TABLE,CREATE VIEW,CREATE SEQUENCE,CREATE SYNONYM,CREATE PROCEDURE,CREATE TRIGGER TO fgedu;

GRANT CONNECT,RESOURCE TO fgedu;

GRANT UNLIMITED TABLESPACE TO fgedu;

ALTER SESSION SET CURRENT_SCHEMA=fgedu;

```

### 2.2 DDL实战：建表与五类约束

业务场景：`fgedu_dept`部门主表，`fgedu_emp`员工从表，主外键关联。

```

--部门主表

CREATE TABLE fgedu_dept (

    dept_id NUMBER(8),

    dept_name VARCHAR2(60) NOT NULL,

    location VARCHAR2(80),

    create_time DATE DEFAULT SYSDATE,

    CONSTRAINT pk_fgedu_dept PRIMARY KEY(dept_id)

);

--员工从表，主键、非空、唯一、检查、外键五类约束完整示例

CREATE TABLE fgedu_emp (

    emp_id NUMBER(10),

    emp_name VARCHAR2(40) NOT NULL,

    salary NUMBER(12,2),

    job VARCHAR2(50),

    dept_id NUMBER(8),

    hire_date DATE,

    email VARCHAR2(100),

    CONSTRAINT pk_fgedu_emp PRIMARY KEY(emp_id),

    CONSTRAINT ck_emp_salary CHECK(salary>0),

    CONSTRAINT uk_emp_email UNIQUE(email),

    CONSTRAINT fk_emp_dept FOREIGN KEY(dept_id) REFERENCES fgedu_dept(dept_id)

);

DESC fgedu_dept;

DESC fgedu_emp;

```

alter修改表结构实战

```

--新增字段

ALTER TABLE fgedu_emp ADD mobile VARCHAR2(30);

--修改字段长度

ALTER TABLE fgedu_emp MODIFY mobile VARCHAR2(40);

--重命名字段

ALTER TABLE fgedu_emp RENAME COLUMN mobile TO phone;

--删除字段

ALTER TABLE fgedu_emp DROP COLUMN phone;

--禁用/启用约束

ALTER TABLE fgedu_emp DISABLE CONSTRAINT fk_emp_dept;

ALTER TABLE fgedu_emp ENABLE CONSTRAINT fk_emp_dept;

```

### 2.3 DDL实战：各类索引创建管理

```

--普通B‑Tree索引

CREATE INDEX idx_fgedu_emp_dept ON fgedu_emp(dept_id);

--复合多列索引

CREATE INDEX idx_fgedu_emp_job_sal ON fgedu_emp(job,salary);

--函数索引，业务经常upper(emp_name)查询

CREATE INDEX idx_fgedu_emp_name_upper ON fgedu_emp(UPPER(emp_name));

--查询当前schema索引字典

SELECT index_name,table_name,index_type FROM user_indexes WHERE table_name IN('FGEDU_EMP','FGEDU_DEPT');

--删除索引

DROP INDEX idx_fgedu_emp_job_sal;

```

### 2.4 DDL实战：视图、同义词、序列

#### 2.4.1 视图

```

--普通关联视图

CREATE VIEW v_fgedu_emp_dept AS

SELECT e.emp_id,e.emp_name,e.salary,e.job,d.dept_name

FROM fgedu_emp e

JOIN fgedu_dept d ON e.dept_id = d.dept_id;

--只读视图，禁止DML修改视图

CREATE VIEW v_fgedu_emp_readonly AS

SELECT emp_id,emp_name,salary,job FROM fgedu_emp

WITH READ ONLY;

SELECT * FROM v_fgedu_emp_dept WHERE ROWNUM<=10;

```

#### 2.4.2 同义词

```

CREATE SYNONYM syn_emp_view FOR v_fgedu_emp_dept;

SELECT * FROM syn_emp_view WHERE ROWNUM<=5;

DROP SYNONYM syn_emp_view;

```

####2.4.3 序列，用于员工主键自增

```

CREATE SEQUENCE seq_fgedu_emp_id

INCREMENT BY 1

START WITH 1001

MAXVALUE 99999999

NOCYCLE

CACHE 100;

SELECT seq_fgedu_emp_id.NEXTVAL FROM DUAL;

SELECT seq_fgedu_emp_id.CURRVAL FROM DUAL;

ALTER SEQUENCE seq_fgedu_emp_id CACHE 200;

```

### 2.5 DML与事务实战：INSERT / UPDATE / DELETE / MERGE

> 

> DML不会自动提交；commit持续生效；rollback撤销未提交变更；savepoint设置保存点。

```

--插入部门数据

INSERT INTO fgedu_dept(dept_id,dept_name,location) VALUES(10,'研发部','一号研发大楼');

INSERT INTO fgedu_dept(dept_id,dept_name,location) VALUES(20,'销售部','二号业务大楼');

INSERT INTO fgedu_dept(dept_id,dept_name,location) VALUES(30,'人事部','行政中心');

COMMIT;

--插入员工，使用序列生成主键emp_id

INSERT INTO fgedu_emp(emp_id,emp_name,salary,job,dept_id,hire_date,email)

VALUES(seq_fgedu_emp_id.NEXTVAL,'张三',18000,'开发工程师',10,TO_DATE('2022‑03‑15','yyyy‑mm‑dd'),'zhangsan@fgedu.com');

INSERT INTO fgedu_emp(emp_id,emp_name,salary,job,dept_id,hire_date,email)

VALUES(seq_fgedu_emp_id.NEXTVAL,'李四',15000,'测试工程师',10,TO_DATE('2021‑08‑20','yyyy‑mm‑dd'),'lisi@fgedu.com');

INSERT INTO fgedu_emp(emp_id,emp_name,salary,job,dept_id,hire_date,email)

VALUES(seq_fgedu_emp_id.NEXTVAL,'王五',22000,'销售经理',20,TO_DATE('2020‑11‑05','yyyy‑mm‑dd'),'wangwu@fgedu.com');

COMMIT;

--update更新

UPDATE fgedu_emp SET salary=19000 WHERE emp_name='张三';

SAVEPOINT sp_01;

ROLLBACK TO sp_01;

--delete删除

DELETE FROM fgedu_emp WHERE emp_id=1003;

ROLLBACK;

--merge 合并语句，匹配更新，不匹配插入

MERGE INTO fgedu_dept t1

USING (SELECT 40 AS dept_id,'运维部' AS dept_name,'运维中心' AS location FROM DUAL) t2

ON (t1.dept_id = t2.dept_id)

WHEN MATCHED THEN UPDATE SET dept_name=t2.dept_name

WHEN NOT MATCHED THEN INSERT(dept_id,dept_name,location) VALUES(t2.dept_id,t2.dept_name,t2.location);

COMMIT;

```

### 2.6 DQL完整查询实战

#### 2.6.1 基础查询、where过滤、order by排序、伪列rownum分页

```

SELECT emp_id,emp_name,salary,job,dept_id FROM fgedu_emp;

--条件过滤

SELECT emp_name,salary,job FROM fgedu_emp WHERE salary >16000;

SELECT emp_name,salary,dept_id FROM fgedu_emp WHERE dept_id=10 AND salary>14000;

--模糊匹配

SELECT emp_name,email FROM fgedu_emp WHERE email LIKE 'zhang%';

--排序

SELECT emp_name,salary FROM fgedu_emp ORDER BY salary DESC;

--分页，工资最高2条

SELECT * FROM (SELECT emp_name,salary FROM fgedu_emp ORDER BY salary DESC) WHERE ROWNUM <=2;

```

#### 2.6.2 聚合函数，group by分组，having过滤

常用聚合：`SUM,AVG,MAX,MIN,COUNT`

```

SELECT dept_id,

AVG(salary) avg_sal,

MAX(salary) max_sal,

MIN(salary) min_sal,

COUNT(emp_id) emp_cnt

FROM fgedu_emp

GROUP BY dept_id

HAVING AVG(salary) > 14000;

```

#### 2.6.3 ANSI标准多表连接

```

--内连接INNER JOIN

SELECT e.emp_name,e.salary,d.dept_name

FROM fgedu_emp e

INNER JOIN fgedu_dept d ON e.dept_id = d.dept_id;

--左外连接LEFT JOIN，保留全部员工，部门不存在也展示员工

SELECT e.emp_name,e.salary,d.dept_name

FROM fgedu_emp e

LEFT JOIN fgedu_dept d ON e.dept_id = d.dept_id;

--右外连接RIGHT JOIN

SELECT e.emp_name,e.salary,d.dept_name

FROM fgedu_emp e

RIGHT JOIN fgedu_dept d ON e.dept_id = d.dept_id;

```

#### 2.6.4 子查询实战

```

--单行子查询，工资高于李四

SELECT emp_name,salary FROM fgedu_emp

WHERE salary > (SELECT salary FROM fgedu_emp WHERE emp_name='李四');

--多行子查询 IN

SELECT emp_name,salary FROM fgedu_emp

WHERE dept_id IN (SELECT dept_id FROM fgedu_dept WHERE dept_name IN('研发部','销售部'));

--关联子查询，高于本部门平均薪资员工

SELECT t.emp_name,t.salary,t.dept_id,t.dept_avg

FROM (

    SELECT e.emp_name,e.salary,e.dept_id,d.avg_dept_sal

    FROM fgedu_emp e

    JOIN (SELECT dept_id,AVG(salary) avg_dept_sal FROM fgedu_emp GROUP BY dept_id) d

    ON e.dept_id=d.dept_id

) t WHERE t.salary > t.avg_dept_sal;

```

####2.6.5 分析窗口函数实战（报表统计常用）

```

--部门内部薪资排名 RANK()

SELECT emp_name,salary,dept_id,

RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) sal_rank

FROM fgedu_emp;

--部门累计求和

SELECT emp_name,salary,dept_id,

SUM(salary) OVER(PARTITION BY dept_id ORDER BY salary ROWS UNBOUNDED PRECEDING) sum_acc

FROM fgedu_emp;

```

### 2.7 Oracle常用内置函数实战

```

--虚表dual测试函数

SELECT SYSDATE,SYSTIMESTAMP FROM DUAL;

--字符串函数 UPPER LOWER SUBSTR CONCAT

SELECT UPPER(emp_name),LOWER(emp_name),SUBSTR(emp_name,1,2) FROM fgedu_emp;

--日期函数 ADD_MONTHS TRUNC

SELECT emp_name,hire_date,ADD_MONTHS(hire_date,6) half_year FROM fgedu_emp;

--NVL空处理，DECODE条件判断

SELECT emp_name,NVL(mobile,'未填写') mobile,

DECODE(job,'开发工程师','技术岗','销售经理','业务岗','其他岗位') job_type

FROM fgedu_emp;

--数字函数ROUND

SELECT salary,ROUND(salary/1000,2) salary_k FROM fgedu_emp;

--CASE WHEN多条件判断

SELECT emp_name,salary,

CASE WHEN salary >=20000 THEN '高薪'

WHEN salary >=15000 THEN '中薪'

ELSE '基础薪资' END AS salary_level

FROM fgedu_emp;

```

### 2.8 PL/SQL实战：匿名块、游标、异常捕获

```

DECLARE

    v_dept_id NUMBER :=10;

    v_emp_cnt NUMBER;

BEGIN

    SELECT COUNT(emp_id) INTO v_emp_cnt FROM fgedu_emp WHERE dept_id=v_dept_id;

    DBMS_OUTPUT.PUT_LINE('部门'||v_dept_id||'员工总数：'||v_emp_cnt);

EXCEPTION

    WHEN NO_DATA_FOUND THEN

        DBMS_OUTPUT.PUT_LINE('没有查询到数据');

    WHEN TOO_MANY_ROWS THEN

        DBMS_OUTPUT.PUT_LINE('返回多条记录异常');

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('其他异常，错误码：'||SQLCODE||'，信息：'||SQLERRM);

END;

/

```

带显式游标循环示例

```

DECLARE

CURSOR cur_emp IS SELECT emp_name,salary FROM fgedu_emp WHERE dept_id=10;

v_emp_name fgedu_emp.emp_name%TYPE;

v_sal fgedu_emp.salary%TYPE;

BEGIN

OPEN cur_emp;

LOOP

    FETCH cur_emp INTO v_emp_name,v_sal;

    EXIT WHEN cur_emp%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE('员工：'||v_emp_name||'，薪资：'||v_sal);

END LOOP;

CLOSE cur_emp;

END;

/

```

### 2.9 PL/SQL存储过程、自定义函数实战

####2.9.1 存储过程，传入部门ID，输出员工数量

```

CREATE OR REPLACE PROCEDURE p_get_dept_emp_cnt(p_dept_id IN NUMBER,p_emp_count OUT NUMBER)

IS

BEGIN

    SELECT COUNT(emp_id) INTO p_emp_count FROM fgedu_emp WHERE dept_id=p_dept_id;

END;

/

--调用存储过程

DECLARE

    v_cnt NUMBER;

BEGIN

    p_get_dept_emp_cnt(10,v_cnt);

    DBMS_OUTPUT.PUT_LINE('部门员工总数：'||v_cnt);

END;

/

```

####2.9.2 自定义函数，返回部门平均薪资

```

CREATE OR REPLACE FUNCTION f_get_dept_avg_sal(p_dept_id IN NUMBER) RETURN NUMBER

IS

    v_avg_sal NUMBER;

BEGIN

    SELECT AVG(salary) INTO v_avg_sal FROM fgedu_emp WHERE dept_id=p_dept_id;

    RETURN v_avg_sal;

END;

/

--函数可以直接在SQL中调用

SELECT dept_id,f_get_dept_avg_sal(dept_id) avg_sal FROM fgedu_dept;

```

### 2.10 PL/SQL触发器实战：员工薪资变更日志触发器

模拟业务：更新员工薪资，自动写入变更日志表

```

CREATE TABLE fgedu_emp_sal_log(

    log_id NUMBER PRIMARY KEY,

    emp_id NUMBER,

    old_sal NUMBER(12,2),

    new_sal NUMBER(12,2),

    update_time DATE DEFAULT SYSDATE

);

CREATE SEQUENCE seq_log_id START WITH 1 INCREMENT BY 1 NOCACHE;

CREATE OR REPLACE TRIGGER tri_emp_sal_change

BEFORE UPDATE OF salary ON fgedu_emp

FOR EACH ROW

BEGIN

    INSERT INTO fgedu_emp_sal_log(log_id,emp_id,old_sal,new_sal)

    VALUES(seq_log_id.NEXTVAL,:OLD.emp_id,:OLD.salary,:NEW.salary);

END;

/

--测试触发器

UPDATE fgedu_emp SET salary=19500 WHERE emp_name='张三';

COMMIT;

SELECT * FROM fgedu_emp_sal_log;

```

### 2.11 PL/SQL程序包PACKAGE实战

包头定义对外接口，包体实现逻辑

```

--包头

CREATE OR REPLACE PACKAGE pkg_fgedu_emp_mgr AS

    PROCEDURE p_query_dept_emp(p_dept_id IN NUMBER,p_cnt OUT NUMBER);

    FUNCTION f_query_avg_sal(p_dept_id IN NUMBER) RETURN NUMBER;

END pkg_fgedu_emp_mgr;

/

--包体

CREATE OR REPLACE PACKAGE BODY pkg_fgedu_emp_mgr AS

PROCEDURE p_query_dept_emp(p_dept_id IN NUMBER,p_cnt OUT NUMBER)

IS

BEGIN

    SELECT COUNT(emp_id) INTO p_cnt FROM fgedu_emp WHERE dept_id=p_dept_id;

END p_query_dept_emp;

FUNCTION f_query_avg_sal(p_dept_id IN NUMBER) RETURN NUMBER

IS

    v_avg NUMBER;

BEGIN

    SELECT AVG(salary) INTO v_avg FROM fgedu_emp WHERE dept_id=p_dept_id;

    RETURN v_avg;

END f_query_avg_sal;

END pkg_fgedu_emp_mgr;

/

--调用包内过程函数

DECLARE

    v_c NUMBER;

BEGIN

    pkg_fgedu_emp_mgr.p_query_dept_emp(10,v_c);

    DBMS_OUTPUT.PUT_LINE('部门人数：'||v_c||'，平均薪资：'||pkg_fgedu_emp_mgr.f_query_avg_sal(10));

END;

/

```

### 2.12 数据字典视图查询（开发DBA高频使用）

```

--查询当前用户全部表

SELECT table_name FROM user_tables;

--约束信息

SELECT constraint_name,table_name,constraint_type FROM user_constraints;

--索引

SELECT index_name,table_name FROM user_indexes;

--视图

SELECT view_name FROM user_views;

--序列

SELECT sequence_name FROM user_sequences;

--存储过程、函数、包、触发器状态，检查是否INVALID失效

SELECT object_name,object_type,status FROM user_objects WHERE status!='VALID';

--查看存储过程源代码

SELECT text FROM user_source WHERE name='P_GET_DEPT_EMP_CNT';

```

### 2.13 执行计划分析，SQL调优基础实战

```

--生成执行计划

EXPLAIN PLAN FOR

SELECT e.emp_name,d.dept_name FROM fgedu_emp e JOIN fgedu_dept d ON e.dept_id=d.dept_id WHERE e.salary>15000;

--查看执行计划

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

```

### 2.14 SQL开发综合巡检脚本，保存为`/fgedudb/soft/sql_check_fgedu.sh`

```

#!/bin/bash

export ORACLE_BASE=/fgedudb/app

export ORACLE_HOME=/fgedudb/app/product/19.0.0/dbhome_1

export ORACLE_SID=fgedudb

export PATH=$ORACLE_HOME/bin:$PATH

echo "====================SQL对象有效性检查===================="

sqlplus -S / as sysdba <<EOF

set pagesize 100 linesize 160

ALTER SESSION SET CURRENT_SCHEMA=fgedu;

SELECT object_name,object_type,status FROM user_objects WHERE status<>'VALID';

SELECT table_name FROM user_tables;

SELECT constraint_name,table_name,constraint_type FROM user_constraints;

EOF

echo "====================数据库实例SQL相关参数===================="

sqlplus -S / as sysdba <<EOF

SHOW PARAMETER open_cursors;

SHOW PARAMETER session_cached_cursors;

SHOW PARAMETER undo_retention;

EOF

```

赋予执行权限运行

```

chmod +x /fgedudb/soft/sql_check_fgedu.sh

./fgedudb/soft/sql_check_fgedu.sh

```

## 三、总结

SQL与PL/SQL是Oracle数据库的基础能力，我是风哥，在多年项目实施中，大量业务性能故障、数据异常，根源都是不规范的SQL与PL/SQL代码：缺少约束产生脏数据，索引设计不合理引发全表扫描，长事务忘记commit造成锁阻塞，游标没有关闭引发游标泄露ORA‑01000，PL/SQL对象编译失效未发现直接上线。风哥 itpux-com

本文基于硬件规格**64G内存、8CPU**的Oracle19c单机实例，全部路径替换为`/fgedudb`，数据库实例`fgedudb`，业务用户`fgedu`。完整覆盖DDL对象管理、DML事务控制、DQL各类查询、内置函数、多表连接、子查询、窗口分析函数，以及PL/SQL匿名块、存储过程、函数、触发器、程序包全套内容，配套大量可直接复用SQL脚本，同时包含数据字典查询、执行计划分析、对象有效性巡检脚本。

生产环境必须遵守几条核心开发规范：

1. DDL语句会隐式提交事务，上线必须预留变更窗口，变更前做好备份；TRUNCATE属于DDL，严禁随意在业务库执行；

2. DML操作必须显式commit，避免长事务持有行锁；合理使用savepoint保存点；

3. 索引不是越多越好，OLTP业务控制索引数量，优先使用复合索引、函数索引解决索引失效场景；

4. PL/SQL开发务必处理异常，游标使用完毕必须关闭，防止游标泄露；上线前检查对象status为VALID；

5. 查询优先使用ANSI标准JOIN语法；多表关联、子查询注意ORA‑01427单行子查询返回多行等常见报错；

6. 上线前使用explain plan分析执行计划，提前识别全表扫描性能风险。网上搜索风哥教程可以学习全套数据库教程

掌握SQL语法只是起点，后续还需要深入学习SQL性能调优、锁与等待事件分析、分区表、物化视图、闪回技术。运维、开发人员不仅要会写出返回结果的SQL，更需要理解SQL在数据库内部的执行逻辑，才能写出高性能、健壮稳定的业务代码。