🐪Oracle速查手册

database
约 41 分钟阅读

阅读量:150次
Oracle

一、基础概念与工具

项目说明与用法
DUAL 表系统内置的单行单列虚拟表，常用于不依赖实际表的 SELECT 查询。
示例：SELECT SYSDATE FROM DUAL;
📖 维基百科
SQL*PlusOracle 命令行交互工具，支持环境命令、脚本执行和结果格式化。
📖 维基百科
快速参考文档Oracle 官方提供的《SQL Language Quick Reference》PDF，汇总语法、函数与格式模型。
🔗 官方文档

二、SQL*Plus 与环境命令

类别命令示例
连接与退出sqlplus username/password@host:port/SID
EXIT / QUIT
脚本执行@script.sql 或 START script.sql
输出控制SET LINESIZE 200
SET PAGESIZE 50
SET HEADING ON
列格式化COLUMN col_name FORMAT A20
COLUMN col_name HEADING '列名'
变量与参数SHOW PARAMETER sga
SHOW USER
SHOW ALL
结果导出SPOOL /path/to/output.log
SPOOL OFF
会话管理CONNECT new_user/password@service

三、数据定义语言（DDL）

对象类型语法示例
表管理sql<br>CREATE TABLE employees (<br> id NUMBER PRIMARY KEY,<br> name VARCHAR2(50)<br>) TABLESPACE users;<br>
DROP TABLE employees PURGE;
表结构修改ALTER TABLE employees ADD (email VARCHAR2(100));
ALTER TABLE employees MODIFY (name VARCHAR2(100));
ALTER TABLE employees RENAME COLUMN name TO full_name;
ALTER TABLE employees DROP COLUMN email;
ALTER TABLE employees RENAME TO staff;
约束管理sql<br>ALTER TABLE employees <br>ADD CONSTRAINT pk_emp PRIMARY KEY (id);<br>
ALTER TABLE employees DROP CONSTRAINT pk_emp;
索引管理CREATE INDEX idx_name ON employees(name);
DROP INDEX idx_name;
ALTER INDEX idx_name REBUILD COMPUTE STATISTICS;
视图CREATE OR REPLACE VIEW emp_view AS SELECT * FROM employees;
DROP VIEW emp_view;
序列CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;
DROP SEQUENCE emp_seq;
同义词CREATE SYNONYM emp_syn FOR hr.employees;
DROP SYNONYM emp_syn;
表空间CREATE TABLESPACE tbs1 DATAFILE '/path/file.dbf' SIZE 100M;
DROP TABLESPACE tbs1 INCLUDING CONTENTS AND DATAFILES;

四、数据操作语言（DML）

操作类型语法示例
数据插入INSERT INTO employees (id, name) VALUES (1, 'John');
INSERT INTO emp_backup SELECT * FROM employees;
数据更新UPDATE employees SET salary = 5000 WHERE id = 1;
数据删除DELETE FROM employees WHERE id = 1;
查询基础SELECT id, name FROM employees WHERE department = 'IT';
排序分页sql<br>-- 传统分页<br>SELECT * FROM (<br> SELECT t.*, ROWNUM rn FROM employees t<br>) WHERE rn BETWEEN 11 AND 20;<br><br>-- 12c+ 分页<br>SELECT * FROM employees <br>OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;<br>
聚合分组SELECT dept, AVG(salary) FROM employees GROUP BY dept HAVING AVG(salary) > 5000;
高级查询sql<br>-- 子查询<br>SELECT * FROM employees <br>WHERE salary > (SELECT AVG(salary) FROM employees);<br><br>-- WITH 子句<br>WITH dept_stats AS (<br> SELECT dept, AVG(salary) avg_sal FROM employees GROUP BY dept<br>)<br>SELECT * FROM dept_stats WHERE avg_sal > 5000;<br>
合并操作sql<br>MERGE INTO employees t <br>USING new_employees s <br>ON (t.id = s.id)<br>WHEN MATCHED THEN UPDATE SET t.salary = s.salary<br>WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);<br>
返回子句DELETE FROM employees WHERE id = 1 RETURNING name INTO v_name;

五、函数与表达式

函数类别常用函数示例
字符串函数SUBSTR('Oracle', 2, 3) → 'rac'
INSTR('Oracle', 'a') → 5
UPPER('hello'), LOWER('WORLD')
TRIM(' Hello ') → 'Hello'
REPLACE('ABC', 'B', 'X') → 'AXC'
数值函数ROUND(123.456, 2) → 123.46
TRUNC(123.456, 2) → 123.45
CEIL(123.1) → 124
FLOOR(123.9) → 123
日期函数SYSDATE -- 当前日期
TO_DATE('2024-01-01', 'YYYY-MM-DD')
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
ADD_MONTHS(SYSDATE, 3)
MONTHS_BETWEEN(date1, date2)
转换函数TO_CHAR(123) → '123'
TO_NUMBER('123') → 123
CAST('123' AS NUMBER) → 123
条件函数sql<br>CASE WHEN salary > 5000 THEN 'High'<br> ELSE 'Normal' <br>END<br>
DECODE(status, 1, 'Active', 0, 'Inactive', 'Unknown')
空值处理NVL(commission, 0)
NVL2(commission, 'Has', 'None')
COALESCE(phone, mobile, 'No Contact')
NULLIF(salary, 0)
分析函数sql<br>ROW_NUMBER() OVER (ORDER BY salary DESC)<br>RANK() OVER (PARTITION BY dept ORDER BY salary DESC)<br>LAG(salary) OVER (ORDER BY hire_date)<br>

六、PL/SQL 编程

编程元素语法结构
程序结构`sql
DECLARE
v_name VARCHAR2(50);
BEGIN
SELECT name INTO v_name FROM employees WHERE id = 1;
DBMS_OUTPUT.PUT_LINE('Name: '		 v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
`
流程控制sql<br>-- IF 语句<br>IF salary > 10000 THEN<br> bonus := 1000;<br>ELSIF salary > 5000 THEN<br> bonus := 500;<br>ELSE<br> bonus := 100;<br>END IF;<br><br>-- 循环<br>FOR i IN 1..10 LOOP<br> DBMS_OUTPUT.PUT_LINE(i);<br>END LOOP;<br><br>WHILE counter < 100 LOOP<br> counter := counter + 1;<br>END LOOP;<br>
存储程序sql<br>CREATE OR REPLACE PROCEDURE raise_salary(<br> p_emp_id IN NUMBER,<br> p_amount IN NUMBER<br>) IS<br>BEGIN<br> UPDATE employees <br> SET salary = salary + p_amount <br> WHERE id = p_emp_id;<br> COMMIT;<br>END;<br><br>CREATE OR REPLACE FUNCTION get_employee_name(<br> p_emp_id IN NUMBER<br>) RETURN VARCHAR2 IS<br> v_name VARCHAR2(100);<br>BEGIN<br> SELECT name INTO v_name FROM employees WHERE id = p_emp_id;<br> RETURN v_name;<br>END;<br>
包管理sql<br>CREATE OR REPLACE PACKAGE emp_pkg AS<br> PROCEDURE hire_employee(p_name VARCHAR2, p_salary NUMBER);<br> FUNCTION get_salary(p_emp_id NUMBER) RETURN NUMBER;<br>END emp_pkg;<br><br>CREATE OR REPLACE PACKAGE BODY emp_pkg AS<br> PROCEDURE hire_employee(p_name VARCHAR2, p_salary NUMBER) IS<br> BEGIN<br> INSERT INTO employees(name, salary) VALUES(p_name, p_salary);<br> END;<br> <br> FUNCTION get_salary(p_emp_id NUMBER) RETURN NUMBER IS<br> v_salary NUMBER;<br> BEGIN<br> SELECT salary INTO v_salary FROM employees WHERE id = p_emp_id;<br> RETURN v_salary;<br> END;<br>END emp_pkg;<br>
触发器sql<br>CREATE OR REPLACE TRIGGER audit_employee<br>BEFORE UPDATE ON employees<br>FOR EACH ROW<br>BEGIN<br> INSERT INTO audit_log VALUES(:OLD.salary, :NEW.salary, SYSDATE);<br>END;<br>
游标处理sql<br>DECLARE<br> CURSOR emp_cur IS SELECT id, name FROM employees;<br> v_id employees.id%TYPE;<br> v_name employees.name%TYPE;<br>BEGIN<br> OPEN emp_cur;<br> LOOP<br> FETCH emp_cur INTO v_id, v_name;<br> EXIT WHEN emp_cur%NOTFOUND;<br> -- 处理数据<br> END LOOP;<br> CLOSE emp_cur;<br>END;<br>

七、事务与并发控制

事务操作命令与说明
事务控制COMMIT; -- 提交事务
ROLLBACK; -- 回滚事务
SAVEPOINT sp1; -- 设置保存点
ROLLBACK TO SAVEPOINT sp1; -- 回滚到保存点
并发控制SELECT ... FOR UPDATE; -- 行级锁定
SELECT ... FOR UPDATE NOWAIT; -- 不等待锁
LOCK TABLE employees IN EXCLUSIVE MODE; -- 表级锁
隔离级别Oracle 默认 READ COMMITTED
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
锁监控查询 V$LOCK, V$LOCKED_OBJECT, V$SESSION 视图

八、性能优化

优化技术实施方法
执行计划sql<br>EXPLAIN PLAN FOR SELECT * FROM employees;<br>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);<br>
统计信息sql<br>EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');<br>EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');<br>
优化器提示SELECT /*+ INDEX(emp emp_idx) */ * FROM employees emp;
SELECT /*+ FULL(emp) */ * FROM employees emp;
SELECT /*+ PARALLEL(emp, 4) */ * FROM employees emp;
索引优化ALTER INDEX emp_name_idx REBUILD;
ALTER INDEX emp_name_idx MONITORING USAGE;
SQL 监控查询 V$SQL, V$SQLAREA, V$SESSION_LONGOPS

九、分区表技术

分区类型创建语法
范围分区sql<br>CREATE TABLE sales (<br> sale_id NUMBER,<br> sale_date DATE,<br> amount NUMBER<br>) PARTITION BY RANGE (sale_date) (<br> PARTITION p2023 VALUES LESS THAN (DATE '2024-01-01'),<br> PARTITION p2024 VALUES LESS THAN (DATE '2025-01-01'),<br> PARTITION p_max VALUES LESS THAN (MAXVALUE)<br>);<br>
列表分区sql<br>CREATE TABLE customers (<br> customer_id NUMBER,<br> region VARCHAR2(20)<br>) PARTITION BY LIST (region) (<br> PARTITION p_east VALUES ('Beijing', 'Shanghai'),<br> PARTITION p_west VALUES ('Chengdu', 'Chongqing')<br>);<br>
哈希分区sql<br>CREATE TABLE logs (<br> log_id NUMBER,<br> log_message VARCHAR2(4000)<br>) PARTITION BY HASH (log_id) PARTITIONS 4;<br>
组合分区sql<br>CREATE TABLE transactions (<br> trans_id NUMBER,<br> trans_date DATE,<br> amount NUMBER<br>) PARTITION BY RANGE (trans_date)<br>SUBPARTITION BY HASH (trans_id) SUBPARTITIONS 4 (<br> PARTITION p2023 VALUES LESS THAN (DATE '2024-01-01'),<br> PARTITION p2024 VALUES LESS THAN (DATE '2025-01-01')<br>);<br>
分区维护ALTER TABLE sales ADD PARTITION p2025 VALUES LESS THAN (...);
ALTER TABLE sales DROP PARTITION p2020;
ALTER TABLE sales TRUNCATE PARTITION p2024;
ALTER TABLE sales EXCHANGE PARTITION p2024 WITH TABLE stage_sales;

十、索引技术

索引类型特性与创建语法
B-tree 索引CREATE INDEX emp_name_idx ON employees(last_name);
位图索引CREATE BITMAP INDEX emp_gender_idx ON employees(gender);
函数索引CREATE INDEX emp_upper_name_idx ON employees(UPPER(last_name));
反向键索引CREATE INDEX emp_id_rev_idx ON employees(id) REVERSE;
压缩索引CREATE INDEX emp_comp_idx ON employees(dept_id, hire_date) COMPRESS 1;
分区索引sql<br>CREATE INDEX sales_date_idx ON sales(sale_date)<br>LOCAL (<br> PARTITION p2023,<br> PARTITION p2024<br>);<br>

十一、物化视图

操作语法示例
创建物化视图sql<br>CREATE MATERIALIZED VIEW sales_summary_mv<br>REFRESH FAST ON COMMIT<br>AS<br>SELECT region, SUM(amount) total_sales<br>FROM sales <br>GROUP BY region;<br>
创建物化视图日志sql<br>CREATE MATERIALIZED VIEW LOG ON sales <br>WITH ROWID, SEQUENCE(region, amount) <br>INCLUDING NEW VALUES;<br>
刷新操作EXEC DBMS_MVIEW.REFRESH('SALES_SUMMARY_MV', 'C');
EXEC DBMS_MVIEW.REFRESH('SALES_SUMMARY_MV', 'F');
监控信息SELECT mview_name, refresh_mode, last_refresh_date FROM user_mviews;

十二、安全管理

安全功能命令示例
用户与权限CREATE USER john IDENTIFIED BY password;
GRANT CREATE SESSION, CREATE TABLE TO john;
GRANT SELECT, INSERT ON hr.employees TO john;
REVOKE DELETE ON hr.employees FROM john;
角色管理CREATE ROLE report_user;
GRANT SELECT ANY TABLE TO report_user;
GRANT report_user TO john;
审计AUDIT SELECT TABLE BY ACCESS;
AUDIT INSERT, UPDATE ON hr.employees;
NOAUDIT SELECT TABLE;
数据加密ALTER TABLE employees MODIFY (ssn ENCRYPT USING 'AES256');

十三、系统视图

视图类别常用视图
数据字典USER_TABLES, USER_INDEXES, USER_CONSTRAINTS
ALL_TABLES, ALL_OBJECTS
DBA_USERS, DBA_TABLESPACES, DBA_DATA_FILES
性能视图V$SESSION, V$SQL, V$SQLAREA
V$LOCK, V$PROCESS
V$SGAINFO, V$PGASTAT
V$SYSTEM_EVENT, V$SESSION_WAIT
存储监控DBA_FREE_SPACE, DBA_SEGMENTS
DBA_EXTENTS, DBA_DATA_FILES

十四、备份与恢复

备份方式操作命令
Data Pump 导出expdp hr/password DIRECTORY=dpump_dir DUMPFILE=hr_backup.dmp SCHEMAS=hr
Data Pump 导入impdp hr/password DIRECTORY=dpump_dir DUMPFILE=hr_backup.dmp REMAP_SCHEMA=hr:hr_new
RMAN 备份sql<br>RMAN> BACKUP DATABASE PLUS ARCHIVELOG;<br>RMAN> BACKUP TABLESPACE users;<br>RMAN> BACKUP CURRENT CONTROLFILE;<br>
RMAN 恢复sql<br>RMAN> RESTORE DATABASE;<br>RMAN> RECOVER DATABASE;<br>
闪回技术sql<br>FLASHBACK TABLE employees TO TIMESTAMP SYSDATE - 1/24;<br>SELECT * FROM employees AS OF TIMESTAMP SYSDATE - 5/1440;<br>FLASHBACK DATABASE TO TIMESTAMP SYSDATE - 1;<br>

十五、高级特性

特性描述与示例
数据库链接CREATE DATABASE LINK remote_db CONNECT TO user IDENTIFIED BY password USING 'tns_alias';
SELECT * FROM employees@remote_db;
多租户架构ALTER SESSION SET CONTAINER = PDB1;
SHOW CON_NAME
SELECT NAME, OPEN_MODE FROM V$PDBS;
调度任务sql<br>BEGIN<br> DBMS_SCHEDULER.CREATE_JOB(<br> job_name => 'nightly_report',<br> job_type => 'PLSQL_BLOCK',<br> job_action => 'BEGIN generate_report; END;',<br> start_date => SYSTIMESTAMP,<br> repeat_interval => 'FREQ=DAILY;BYHOUR=2',<br> enabled => TRUE<br> );<br>END;<br>
高级数据类型集合类型、XMLType、空间数据类型、JSON 支持

添加新评论