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 11 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 stateCA; -- 更新表的具体记录 UPDATE customer SET nameJoe WHERE customer_id10; -- 当 paid 列大于零时将列 invoice 更新为 paid UPDATE movies SET invoicepaid WHERE paid 0;第一条语句不带WHERE会将state列整列更新属于高危操作日常更新务必带上主键或唯一性条件。二、SEQUENCES序列的创建与动态调整序列是 Oracle 生成连续数字的数据库对象常用于主键自增替代方案Oracle 没有 MySQL 式的AUTO_INCREMENT12c 之前通常配合触发器或应用层使用序列。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;各参数含义参数含义默认值MINVALUE序列最小值1MAXVALUE序列最大值NOMAXVALUE10^27-1 以内START WITH起始值从MINVALUE开始INCREMENT BY步长正数递增、负数递减1CACHE缓存在内存中的序列值个数提升性能NOCACHE则每次落盘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_id5; 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因为调用的函数是等价的。四者的区别在于长度单位与字符集语义函数度量单位适用场景LENGTH字符数通用LENGTHB字节数单字节字符集或计算存储字节LENGTHCUnicode 完全字符多字节字符集如 UTF-8LENGTH2UCS2 码元UTF-16 环境LENGTH4UCS4 码元UTF-32 环境当数据库字符集为 AL32UTF8 且内容含中文等非 ASCII 字符时LENGTHB与LENGTH的结果会不同。Instr查找子串位置Instr在字符串中返回一个整数该整数指定字符串中子字符串的位置。程序员可以指定他们想要检测的字符串的外观以及起始位置。不成功的搜索返回0。instr( string1, string2, [ start_position ], [ nth_appearance ] )start_position从第几个字符开始搜索可省略默认 1nth_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 ( 删除右侧的空格 );这将返回删除右侧的空格仅删除右侧空格左侧保留。三者区别总结函数作用默认行为TRIM删除两侧字符默认删除两侧空格可加LEADING/TRAILING/BOTH指定方向LTRIM仅删除左侧默认删除空格可指定字符集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_TYPER其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。结合第六节表空间总大小查询可快速算出使用率判断是否需要扩容或清理。捕捉运行很久的 SQLCOLUMN 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 --restartalways \ --mount sourceoracle_vol,target/home/oracle/app/oracle/oradata \ registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g注意registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g是非官方或认证的 Docker 镜像仅建议在开发/测试环境使用。各参数含义参数说明-d以后台运行的方式启动容器-it分配伪终端pseudo-TTY并保持 STDIN 打开-p 1521:1521将主机 1521 端口映射到容器 1521 端口用于访问 Oracle 数据库--name Oracle_11g为容器指定名称Oracle_11g--restartalways容器退出时总是自动重启持久化说明--mount sourceoracle_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-classcols-2、wrap-classrow-span-2、classNamewrap-text正是该排版系统的控制标记用于控制卡片布局、跨行跨列与代码换行展示。【免费下载链接】reference面向开发者的技术速查清单Cheat Sheets集合整理常见技术、工具与开发流程帮助快速查阅关键信息提高开发效率。项目地址: https://gitcode.com/GitHub_Trending/referen/reference创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考