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

博客园 - 风哥数据库教程

风哥数据库教程 · 2026-09-10 · via 博客园 - 风哥数据库教程

数据库教程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性能调优、故障排查打下扎实基础。

**内容大纲：**

Oracle SQL与PL/SQL体系介绍，64G/8CPU实例相关参数基线，实验环境准备
基础理论：SQL五大语言分类、Oracle数据类型、表与约束原理、索引分类原理、视图/同义词/序列原理、事务与锁机制、PL/SQL程序单元原理
查询理论：基础查询、过滤排序、聚合分组、多表连接、子查询、分析窗口函数、集合运算原理
PL/SQL理论：变量、游标、异常处理、存储过程、函数、触发器、程序包原理
完整实战操作：环境初始化、DDL建表/约束/索引/视图/同义词/序列、DML增删改事务演练、全套DQL查询实战、内置函数实战、PL/SQL匿名块、存储过程、函数、触发器、包开发、数据字典查询、执行计划分析
SQL开发生产规范与巡检脚本
全文总结，生产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常用数据类型理论

**字符类型**

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

**数值类型**

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

**时间日期类型**

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

**二进制类型**

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

1.3 表、五类约束原理

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

**PRIMARY KEY主键**：非空+唯一，一张表仅允许一个主键；
**UNIQUE唯一约束**：字段值不可重复，允许NULL空值；
**NOT NULL非空约束**：列不允许存储NULL；
**CHECK检查约束**，自定义字段取值校验规则；
**FOREIGN KEY外键约束**，参照父表主键，维护主从表参照完整性。

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

1.4 索引原理理论

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

**B‑Tree普通索引**：Oracle默认索引，适合等值、范围过滤条件；
**复合索引**：多字段联合B‑Tree索引，遵循前缀匹配原则；
**函数索引**：对函数运算结果建立索引，解决where子句使用函数导致索引失效问题；
**唯一索引**：索引字段不允许重复；
**位图索引**：适合数据仓库低并发DML场景，OLTP业务禁止大量使用位图索引。

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

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

**视图VIEW**：本质是存储的SELECT语句，不存储物理数据；简化复杂多表查询，做权限隔离；分为普通视图、只读视图（WITH READ ONLY）。
**同义词SYNONYM**：对象别名，私有同义词仅当前schema可见，公共同义词全库可见，需要DBA权限创建。
**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程序单元理论

**匿名块**：临时PL/SQL代码，不持久化存储数据库，执行完毕消失，用于脚本、调试；
**存储过程PROCEDURE**：封装业务逻辑，支持入参、出参，数据库持久保存；
**自定义FUNCTION函数**，必须有返回值，可以直接用于SQL语句；
**触发器TRIGGER**：表发生INSERT/UPDATE/DELETE时自动触发执行；
**程序包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 <'VALID';
SELECT table_name FROM user_tables;
SELECT constraint_name,table_name,constraint_type FROM user_constraints;
EOF
echo "数据库实例SQL相关参数"

sqlplus -S / as sysdba <

赋予执行权限运行

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脚本，同时包含数据字典查询、执行计划分析、对象有效性巡检脚本。

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

DDL语句会隐式提交事务，上线必须预留变更窗口，变更前做好备份；TRUNCATE属于DDL，严禁随意在业务库执行；
DML操作必须显式commit，避免长事务持有行锁；合理使用savepoint保存点；
索引不是越多越好，OLTP业务控制索引数量，优先使用复合索引、函数索引解决索引失效场景；
PL/SQL开发务必处理异常，游标使用完毕必须关闭，防止游标泄露；上线前检查对象status为VALID；
查询优先使用ANSI标准JOIN语法；多表关联、子查询注意ORA‑01427单行子查询返回多行等常见报错；
上线前使用explain plan分析执行计划，提前识别全表扫描性能风险。网上搜索风哥教程可以学习全套数据库教程

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

此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。原文来自 — 版权归原作者所有。

推荐订阅源

博客园 - 风哥数据库教程

前言

一、Oracle SQL开发基础理论

1.1 Oracle SQL与PL/SQL整体体系

1.2 Oracle常用数据类型理论

1.3 表、五类约束原理

1.4 索引原理理论

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

1.6 事务、锁机制理论

1.7 DQL查询语法结构理论

1.8 PL/SQL程序单元理论

二、生产完整实战操作

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

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

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

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

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

2.6 DQL完整查询实战

2.7 Oracle常用内置函数实战

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

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

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

2.11 PL/SQL程序包PACKAGE实战

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

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

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

三、总结