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在数据库内部的执行逻辑，才能写出高性能、健壮稳定的业务代码。