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

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

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

【声明】本内容来自华为云开发者社区博主，不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息，否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
Oracle SQL 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入