Oracle SQL 与 PL/SQL 备忘清单：DML 操作、序列、字符串函数、约束、索引与 DBA 实战速查

Oracle SQL 与 PL/SQL 备忘清单：DML 操作、序列、字符串函数、约束、索引与 DBA 实战速查

【免费下载链接】reference面向开发者的技术速查清单（Cheat Sheets）集合，整理常见技术、工具与开发流程，帮助快速查阅关键信息，提高开发效率。项目地址: https://gitcode.com/GitHub_Trending/referen/reference

本篇速查指南以docs/oracle.md为主体，系统覆盖 Oracle 数据库日常开发与运维中最常用的 SQL 片段：从 SELECT/INSERT/UPDATE/DELETE 四大 DML 语句，到序列（Sequence）的创建与调整、字符串函数（LENGTH/INSTR/REPLACE/SUBSTR/TRIM）的精确语义，再到 DDL 中的表结构变更与约束管理、索引创建与统计信息收集，最后延伸至 DBA 常用的数据字典与动态性能视图查询。读完本文，你可以直接复制其中的语句完成建表、约束、索引、用户授权与数据库健康检查等高频操作。

一、入门：Oracle 最常用的 DML 语句

DML（数据操纵语言）是日常开发中使用频率最高的一类 SQL。本节覆盖 SELECT、SELECT INTO、INSERT、DELETE 与 UPDATE 的典型写法，其中的dual虚表、INTO变量赋值等是 Oracle 与 MySQL/PostgreSQL 差异最明显的地方。

SELECT 语句

多条件精确查询：

SELECT * FROM beverages WHERE field1 = 'Kona' AND field2 = 'coffee' AND field3 = 122;

要点说明：

• WHERE中的多个过滤条件使用AND连接，Oracle 对字符串比较区分大小写，'Kona'与'kona'并不相等；
• 如需在 SQL*Plus 或 PL/SQL 中验证无表表达式（如SELECT 1+1 FROM dual;），必须借助 Oracle 专有的dual虚表，该表在内存中只有一行，专用于这种“无表查询”场景。

SELECT INTO 语句

PL/SQL 块中常用SELECT ... INTO将查询结果直接写入变量：

SELECT name, address, phone_number INTO v_employee_name, v_employee_address, v_employee_phone_number FROM employee WHERE employee_id = 6;

注意该语句的约束：查询必须恰好返回一行，若返回多行会触发TOO_MANY_ROWS，返回零行则触发NO_DATA_FOUND异常，这是与普通SELECT最大的区别。

INSERT 语句

使用 VALUES 关键字插入

-- 按表定义顺序插入全部列 INSERT INTO table_name VALUES ('Value1', 'Value2', ... ); -- 显式指定列，推荐写法，避免表结构变化导致错位 INSERT INTO table_name (Column1, Column2, ... ) VALUES ( 'Value1', 'Value2', ... );

使用 SELECT 语句插入

-- 从其他表批量复制数据 INSERT INTO table_name SELECT Value1, Value2, ... FROM table_name; -- 指定列映射的批量插入 INSERT INTO table_name (Column1, Column2, ...) SELECT Value1, Value2, ... FROM table_name;

INSERT ... SELECT是数据迁移、报表临时表填充的常用手段，两表列数、数据类型需一一对应。

DELETE 语句

DELETE FROM table_name WHERE some_column = some_value; DELETE FROM customer WHERE sold = 0;

两点提醒：省略WHERE将清空整张表；生产环境误删后可用闪回查询（Flashback）恢复，但最稳妥的做法是删除前先SELECT确认命中行数。

UPDATE 语句

-- 更新该表的整个列，设置 `state` 列所有值为 `CA` UPDATE customer SET state='CA'; -- 更新表的具体记录 UPDATE customer SET name='Joe' WHERE customer_id=10; -- 当 `paid` 列大于零时，将列 `invoice` 更新为 `paid` UPDATE movies SET invoice='paid' WHERE paid > 0;

第一条语句不带WHERE，会将state列整列更新，属于高危操作；日常更新务必带上主键或唯一性条件。

二、SEQUENCES：序列的创建与动态调整

序列是 Oracle 生成连续数字的数据库对象，常用于主键自增替代方案（Oracle 没有 MySQL 式的AUTO_INCREMENT，12c 之前通常配合触发器或应用层使用序列）。

CREATE SEQUENCE

序列的语法：

CREATE SEQUENCE sequence_name MINVALUE value MAXVALUE value START WITH value INCREMENT BY value CACHE value;

例如：

CREATE SEQUENCE supplier_seq MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20;

各参数含义：

生产环境建议显式设置CACHE，减少对字典表的写入频率，但要注意数据库崩溃时缓存中的号段会丢失（可能出现跳号）。

ALTER SEQUENCE

将序列增加一定数量

ALTER SEQUENCE <sequence_name> INCREMENT BY <integer>; ALTER SEQUENCE seq_inc_by_ten INCREMENT BY 10;

改变序列的最大值

ALTER SEQUENCE <sequence_name> MAXVALUE <integer>; ALTER SEQUENCE seq_maxval MAXVALUE 10;

设置序列循环或不循环

ALTER SEQUENCE <sequence_name> <CYCLE | NOCYCLE>; ALTER SEQUENCE seq_cycle NOCYCLE;

CYCLE表示到达MAXVALUE后从头开始，NOCYCLE（默认）在到达最大值后报错。

配置序列以缓存值

ALTER SEQUENCE <sequence_name> CACHE <integer> | NOCACHE; ALTER SEQUENCE seq_cache NOCACHE;

设置是否按顺序返回值

ALTER SEQUENCE <sequence_name> <ORDER | NOORDER>; ALTER SEQUENCE seq_order NOORDER; ALTER SEQUENCE seq_order;

ORDER保证请求序列值的顺序与实际返回顺序一致（常用于 RAC 集群等并发环境），NOORDER为默认值，性能更好。

从字符串生成查询（动态 SQL）

有时需要从字符串创建查询，Oracle PL/SQL 中可用游标变量（REF CURSOR）配合OPEN ... FOR动态执行 SQL 文本：

PROCEDURE oracle_runtime_query_pcd IS TYPE ref_cursor IS REF CURSOR; l_cursor ref_cursor; v_query varchar2(5000); v_name varchar2(64); BEGIN v_query := 'SELECT name FROM employee WHERE employee_id=5'; OPEN l_cursor FOR v_query; LOOP FETCH l_cursor INTO v_name; EXIT WHEN l_cursor%NOTFOUND; END LOOP; CLOSE l_cursor; END;

这是一个如何完成动态查询的非常简单的示例。核心流程是：先声明REF CURSOR类型，将 SQL 文本赋值给VARCHAR2变量，OPEN ... FOR打开游标，循环FETCH取值，以%NOTFOUND作为退出条件，最后CLOSE释放资源。更复杂的动态 SQL 建议使用原生动态 SQL 语句EXECUTE IMMEDIATE。

三、字符串操作函数

Oracle 内置函数族中字符串处理函数使用频率最高。本节整理docs/oracle.md中演示的五个函数及其精确语义，全部示例均可直接粘贴到 SQL*Plus、SQL Developer 或 PL/SQL 块中运行。

LENGTH 系列：返回字符数

length( string1 );

SELECT length('hello world') FROM dual;

这将返回 11，因为参数由 11 个字符组成，包括空格。

SELECT lengthb('hello world') FROM dual; SELECT lengthc('hello world') FROM dual; SELECT length2('hello world') FROM dual; SELECT length4('hello world') FROM dual;

这些也返回11，因为调用的函数是等价的。四者的区别在于长度单位与字符集语义：

当数据库字符集为 AL32UTF8 且内容含中文等非 ASCII 字符时，LENGTHB与LENGTH的结果会不同。

Instr：查找子串位置

Instr（在字符串中）返回一个整数，该整数指定字符串中子字符串的位置。程序员可以指定他们想要检测的字符串的外观以及起始位置。不成功的搜索返回0。

instr( string1, string2, [ start_position ], [ nth_appearance ] )

• start_position：从第几个字符开始搜索，可省略，默认 1；
• nth_appearance：指定取第几次出现，可省略，默认 1。

示例一：

instr( 'oracle pl/sql cheatsheet', '/');

这将返回10，因为第一次出现的/是第十个字符。

示例二：

instr( 'oracle pl/sql cheatsheet', 'e', 1, 2);

这将返回17，因为第二次出现的e是第17个字符。

示例三：

instr( 'oracle pl/sql cheatsheet', '/', 12, 1);

这将返回0，因为第一次出现的/在起点之前，即第12个字符。注意搜索起点之前的匹配不计入，这就是返回0的原因。

Replace：字符串替换

replace(string1, string_to_replace, [ replacement_string ] ); replace('i am here','am','am not');

这返回i am not here。replacement_string可省略，省略时等价于删除匹配到的子串。

Substr：截取子串

SELECT substr( 'oracle pl/sql cheatsheet', 8, 6) FROM dual;

返回pl/sql，因为pl/sql中的p在字符串中的第8个位置（从oracle中的o处的1开始计算）。

SELECT substr( 'oracle pl/sql cheatsheet', 15) FROM dual;

返回cheatsheet，因为c在字符串中的第15个位置，t是字符串中的最后一个字符。省略第三个参数表示一直截取到字符串末尾。

SELECT substr('oracle pl/sql cheatsheet', -10, 5) FROM dual;

返回cheat，因为c是字符串中的第10个字符，从字符串末尾以t作为位置1开始计算。起始位置为负数时，Oracle 从右向左计数。

Trim：去除首尾字符

这些函数可用于从字符串中过滤不需要的字符。默认情况下，它们会删除空格，但也可以指定要删除的字符集。

trim ( [ leading | trailing | both ] [ trim-char ] from string-to-be-trimmed ); trim (' 删除两侧的空格 ');

这将返回"删除两侧的空格"。

ltrim ( string-to-be-trimmed [, trimming-char-set ] ); ltrim (' 删除左侧的空格 ');

这将返回"删除左侧的空格"（仅删除左侧空格，右侧保留）。

rtrim ( string-to-be-trimmed [, trimming-char-set ] ); rtrim (' 删除右侧的空格 ');

这将返回"删除右侧的空格"（仅删除右侧空格，左侧保留）。

三者区别总结：

四、DDL SQL：表结构与约束管理

DDL（数据定义语言）负责表的创建与结构变更，以及约束（Constraint）的建立与删除。约束是保证数据完整性的核心机制，docs/oracle.md给出了从建表到约束维护的完整语句族。

创建表

创建表的语法：

CREATE TABLE [table name] ( [column name] [datatype], ... );

示例：

CREATE TABLE employee (id int, name varchar(20));

Oracle 的常用数据类型包括NUMBER(p,s)、VARCHAR2(n)、CHAR(n)、DATE、TIMESTAMP、CLOB、BLOB等，建议建表时同时声明NOT NULL与默认值。

添加列

添加列的语法：

ALTER TABLE [table name] ADD ( [column name] [datatype], ... );

示例：

ALTER TABLE employee ADD (id int)

修改列

修改列的语法：

ALTER TABLE [table name] MODIFY ( [column name] [new datatype]);

ALTER表语法和示例：

ALTER TABLE employee MODIFY( sickHours s float );

修改列类型需满足新旧类型兼容（如VARCHAR2扩宽通常可行，收窄或跨类型转换需评估存量数据）。

删除列

删除列的语法：

ALTER TABLE [table name] DROP COLUMN [column name];

示例：

ALTER TABLE employee DROP COLUMN vacationPay;

约束类型和代码

Oracle 数据字典通过一个字符的CONSTRAINT_TYPE标识约束类型：

| 类型代码 | 类型描述 | 作用于级别 | | :-- | -- | -- | |C| 检查表（CHECK） | Column | |O| 在视图上只读 | Object | |P| 首要的关键（主键 PRIMARY KEY） | Object | |R| 参考 AKA 外键（REFERENTIAL） | Column | |U| 唯一键（UNIQUE） | Column | |V| 检查视图上的选项（VIEW CHECK OPTION） | Object |

显示约束

以下语句显示了系统中的所有约束：

SELECT table_name, constraint_name, constraint_type FROM user_constraints;

user_constraints只返回当前用户 schema 下的约束；如需全局查看需使用all_constraints或dba_constraints（需相应权限）。

选择参照约束

以下语句显示了源和目标表/列对的所有引用约束（外键）：

SELECT c_list.CONSTRAINT_NAME as NAME, c_src.TABLE_NAME as SRC_TABLE, c_src.COLUMN_NAME as SRC_COLUMN, c_dest.TABLE_NAME as DEST_TABLE, c_dest.COLUMN_NAME as DEST_COLUMN FROM ALL_CONSTRAINTS c_list, ALL_CONS_COLUMNS c_src, ALL_CONS_COLUMNS c_dest WHERE c_list.CONSTRAINT_NAME = c_src.CONSTRAINT_NAME AND c_list.R_CONSTRAINT_NAME = c_dest.CONSTRAINT_NAME AND c_list.CONSTRAINT_TYPE = 'R'

原理：外键约束记录在ALL_CONSTRAINTS中（CONSTRAINT_TYPE='R'），其R_CONSTRAINT_NAME指向被引用（父表）的主键/唯一约束；ALL_CONS_COLUMNS存放约束与列的关系，通过两次关联分别取出子表列与父表列。这是排查外键级联关系、生成删除脚本的常用查询。

对表设置约束（CHECK）

使用CREATE TABLE语句创建检查约束的语法是：

CREATE TABLE table_name ( column1 datatype null/not null, column2 datatype null/not null, ... CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE] );

例如：

CREATE TABLE suppliers ( supplier_id numeric(4), supplier_name varchar2(50), CONSTRAINT check_supplier_id CHECK (supplier_id BETWEEN 100 and 9999) );

[DISABLE]可选，表示先禁用约束再建表，适合导入大量历史数据时使用，导入完成后再ENABLE。

表上的唯一索引（UNIQUE 约束）

使用CREATE TABLE语句创建唯一约束的语法是：

CREATE TABLE table_name ( column1 datatype null/not null, column2 datatype null/not null, ... CONSTRAINT constraint_name UNIQUE (column1, column2, column_n) );

例如：

CREATE TABLE customer ( id integer not null, name varchar2(20), CONSTRAINT customer_id_constraint UNIQUE (id) );

UNIQUE (column1, column2, column_n)支持复合唯一键，即多列组合值不能重复。

添加唯一约束

唯一约束的语法是：

ALTER TABLE [table name] ADD CONSTRAINT [constraint name] UNIQUE([column name]) USING INDEX [index name];

例如：

ALTER TABLE employee ADD CONSTRAINT uniqueEmployeeId UNIQUE(employeeId) USING INDEX ourcompanyIndx_tbs;

USING INDEX可指定承载该唯一约束的索引，从而控制索引所在的表空间与存储参数。

添加外键约束

外键约束的语法是：

ALTER TABLE [table name] ADD CONSTRAINT [constraint name] FOREIGN KEY (column,...) REFERENCES table [(column,...)] [ON DELETE {CASCADE | SET NULL}];

例如：

ALTER TABLE employee ADD CONSTRAINT fk_departament FOREIGN KEY (departmentId) REFERENCES departments(Id);

ON DELETE CASCADE表示父表记录删除时级联删除子表记录；ON DELETE SET NULL表示将子表外键列置为NULL。省略时 Oracle 默认禁止删除仍被引用的父记录（NO ACTION语义）。

删除约束

删除（删除）约束的语法是：

ALTER TABLE [table name] DROP CONSTRAINT [constraint name];

例如：

ALTER TABLE employee DROP CONSTRAINT uniqueEmployeeId;

五、INDEXES：索引创建、统计信息与维护

索引是 Oracle 查询性能优化的核心手段。本节覆盖普通索引、唯一索引、基于函数的索引的创建，以及重命名、收集统计信息与删除。

创建索引

创建索引的语法是：

CREATE [UNIQUE] INDEX index_name ON table_name ( column1, column2, . column_n ) [ COMPUTE STATISTICS ];

• UNIQUE表示索引列中值的组合必须是唯一的；
• COMPUTE STATISTICS告诉 Oracle 在创建索引期间收集统计信息。然后优化器使用这些统计信息来选择执行语句时的最佳执行计划。

例如：

CREATE INDEX customer_idx ON customer (customer_name);

在此示例中，已在名为customer_idx的客户表上创建了一个索引。它仅包含customer_name字段。

下面创建一个包含多个字段的索引：

CREATE INDEX customer_idx ON supplier (customer_name, country);

以下内容在创建索引时收集统计信息：

CREATE INDEX customer_idx ON supplier (customer_name, country) COMPUTE STATISTICS;

创建基于函数的索引

在 Oracle 中，您不仅限于在列上创建索引。您可以创建基于函数的索引。

创建基于函数的索引的语法是：

CREATE [UNIQUE] INDEX index_name ON table_name (function1, function2, . function_n) [ COMPUTE STATISTICS ];

例如：

CREATE INDEX customer_idx ON customer (UPPER(customer_name)); -- 已创建基于 customer_name 字段的大写评估的索引

基于函数的索引可以让WHERE UPPER(customer_name) = '...'这类查询走索引，避免全表扫描。为确保 Oracle 优化器在执行 SQL 语句时使用此索引，请确保UPPER(customer_name)的计算结果不为NULL值。为确保这一点，请将UPPER(customer_name) IS NOT NULL添加到WHERE子句中，如下所示：

SELECT customer_id, customer_name, UPPER(customer_name) FROM customer WHERE UPPER(customer_name) IS NOT NULL ORDER BY UPPER(customer_name);

由于 Oracle 默认索引不包含全NULL的条目，显式声明IS NOT NULL能让优化器放心地选择该函数索引。

重命名索引

重命名索引的语法是：

ALTER INDEX index_name RENAME TO new_index_name;

例如：

ALTER INDEX customer_id RENAME TO new_customer_id;

在此示例中，customer_id重命名为new_customer_id。

收集索引的统计信息

如果您需要在索引首次创建后收集统计信息或者您想要更新统计信息，您总是可以使用ALTER INDEX命令来收集统计信息。您收集统计信息以便 Oracle 可以有效地使用索引。这将重新计算表大小、行数、块数、段数并更新字典表，以便 Oracle 在选择执行计划时可以有效地使用数据。

收集索引统计信息的语法是：

ALTER INDEX index_name REBUILD COMPUTE STATISTICS;

例如：

ALTER INDEX customer_idx REBUILD COMPUTE STATISTICS;

在此示例中，为名为customer_idx的索引收集统计信息。REBUILD同时完成索引重建与统计信息刷新，适合碎片化严重的索引。

删除索引

删除索引的语法是：

DROP INDEX index_name;

例如：

DROP INDEX customer_idx;

在此示例中，删除了customer_idx。注意：支撑主键/唯一约束的索引不能直接DROP，需先删除对应约束。

六、DBA 相关：用户、权限与数据库运维查询

本节汇集数据库管理员与开发 DBA 高频使用的语句：用户与权限管理、表空间/文件/日志/事务的监控视图，以及长事务捕捉脚本。

创建用户

创建用户的语法是：

CREATE USER username IDENTIFIED BY password;

例如：

CREATE USER brian IDENTIFIED BY brianpass;

授予特权

授予权限的语法是：

GRANT privilege TO user;

例如：

GRANT dba TO brian;

dba是系统预定义角色；日常最小权限原则下，建议按需授予CONNECT、RESOURCE角色或具体对象权限。

更改密码

更改用户密码的语法是：

ALTER USER username IDENTIFIED BY password;

例如：

ALTER USER brian IDENTIFIED BY brianpassword;

查看表空间的名称以及大小

SELECT t.table_name, ROUND(SUM(bytes / (1024 * 1024)), 0) AS ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.table_name = d.table_name GROUP BY t.table_name;

结果按表空间分组，ts_size为该表空间数据文件总大小（MB，四舍五入取整）。实际使用中更常见的写法是按d.tablespace_name分组（上述语句以table_name列关联dba_tablespaces与dba_data_files两个字典视图）。

查看还没提交的事务

select * from v$locked_object; select * from v$transaction;

• v$locked_object：当前被锁定的对象信息（含会话、对象 ID 等），用于定位锁冲突；
• v$transaction：当前活动事务列表，配合v$session可追查阻塞源。

查看数据库库对象

SELECT owner, object_type, status, COUNT(*) AS count# FROM all_objects GROUP BY owner, object_type, status;

按属主、对象类型、状态（VALID/INVALID）统计对象数量，INVALID数量突增通常意味着依赖对象失效，需要重新编译或检查部署。

查看数据库的版本

SELECT version FROM Product_component_version WHERE SUBSTR(PRODUCT, 1, 6) = 'Oracle';

Product_component_version是 Oracle 提供的版本信息视图，PRODUCT字段以Oracle开头即为主数据库组件。

查看数据库的创建日期和归档方式

SELECT created, Log_Mode, Log_Mode FROM v$Database;

v$Database是数据库级动态视图：CREATED为库创建时间，LOG_MODE标识归档模式（ARCHIVELOG/NOARCHIVELOG），归档模式是执行备份恢复（RMAN）的前提。

查看控制文件

select name from v$controlfile;

查看日志文件

select member from v$logfile;

查看表空间的使用情况

SELECT SUM(bytes)/(1024*1024) AS free_space, tablespace_name FROM dba_free_space GROUP BY tablespace_name;

按表空间统计剩余空闲空间（MB）。结合第六节表空间总大小查询，可快速算出使用率，判断是否需要扩容或清理。

捕捉运行很久的 SQL

COLUMN username FORMAT A12 COLUMN opname FORMAT A16 COLUMN progress FORMAT A8 SELECT username, sid, opname, ROUND(sofar * 100 / totalwork, 0) || '%' AS progress, time_remaining, sql_text FROM v$session_longops, v$sql WHERE time_remaining <> 0 AND sql_address = address AND sql_hash_value = hash_value;

COLUMN ... FORMAT用于 SQL*Plus 输出对齐；查询通过sql_address、sql_hash_value将长操作会话（v$session_longops）与 SQL 文本（v$sql）关联，输出用户名、会话 ID、操作名、完成进度百分比、剩余时间与完整 SQL 文本，是定位慢 SQL 与长事务的利器。

七、本地环境：用 Docker 快速拉起 Oracle 实例

如果你想在本地实践以上语句（尤其是 DBA 相关查询），可以参考docs/docker.md中记录的 Oracle 11g 容器化部署方式，一条命令即可启动实例：

docker run -d -it -p 1521:1521 --name Oracle_11g --restart=always \ --mount source=oracle_vol,target=/home/oracle/app/oracle/oradata \ registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g

注意：registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g是非官方或认证的 Docker 镜像，仅建议在开发/测试环境使用。

各参数含义：

持久化说明：--mount source=oracle_vol,target=/home/oracle/app/oracle/oradata将名为oracle_vol的 Docker 卷挂载到容器内的/home/oracle/app/oracle/oradata路径，使 Oracle 数据文件保存在持久化卷中，容器重启后数据不丢失。

八、更多阅读

• 本文整理的语句全部来自 Oracle 备忘清单，该文档是当前仓库docs/目录下 SQL 类速查表之一，与 MySQL、PostgreSQL 等数据库速查表同属一个体系；
• 若想了解 Oracle 容器化部署的完整参数与持久化细节，可继续阅读 Docker 备忘清单 中的 Oracle 一节；
• 速查表在仓库中的渲染样式由 Quick Reference 排版说明 统一约定，docs/oracle.md中的<!--rehype:...-->注释（如body-class=cols-2、wrap-class=row-span-2、className=wrap-text）正是该排版系统的控制标记，用于控制卡片布局、跨行跨列与代码换行展示。

【免费下载链接】reference面向开发者的技术速查清单（Cheat Sheets）集合，整理常见技术、工具与开发流程，帮助快速查阅关键信息，提高开发效率。项目地址: https://gitcode.com/GitHub_Trending/referen/reference