Oracle数据库中有哪些操作语句

发布时间：2021-08-13 14:37:43 作者：Leah
来源：亿速云阅读：163

# Oracle数据库中有哪些操作语句

## 目录
1. [概述](#概述)
2. [数据查询语言(DQL)](#数据查询语言dql)
3. [数据操作语言(DML)](#数据操作语言dml)
4. [数据定义语言(DDL)](#数据定义语言ddl)
5. [数据控制语言(DCL)](#数据控制语言dcl)
6. [事务控制语言(TCL)](#事务控制语言tcl)
7. [特殊操作语句](#特殊操作语句)
8. [PL/SQL特有语句](#plsql特有语句)
9. [总结](#总结)

## 概述
Oracle数据库作为业界领先的关系型数据库管理系统，提供了丰富的SQL语句类型来满足各种数据管理需求。这些语句按照功能可分为六大类：数据查询语言(DQL)、数据操作语言(DML)、数据定义语言(DDL)、数据控制语言(DCL)、事务控制语言(TCL)以及PL/SQL特有语句。

## 数据查询语言(DQL)
DQL主要用于从数据库中检索数据，核心语句是SELECT。

### 基础SELECT语句
```sql
SELECT column1, column2 FROM table_name WHERE condition;

高级查询功能

多表连接 “`sql – 内连接 SELECT a.col1, b.col2 FROM table_a a JOIN table_b b ON a.id = b.id;

– 外连接 SELECT a.col1, b.col2 FROM table_a a LEFT JOIN table_b b ON a.id = b.id;


2. **子查询**
   ```sql
   SELECT name FROM employees 
   WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = '北京');

分析函数

SELECT employee_id, salary, 
      RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as rank
FROM employees;

WITH子句(CTE)

WITH dept_stats AS (
 SELECT dept_id, AVG(salary) avg_sal 
 FROM employees GROUP BY dept_id
)
SELECT * FROM dept_stats WHERE avg_sal > 10000;

数据操作语言(DML)

DML用于操作数据库中的数据记录。

INSERT语句

-- 基本插入
INSERT INTO employees(id, name, salary) VALUES (1, '张三', 8000);

-- 批量插入
INSERT INTO employees(id, name)
SELECT employee_id, last_name FROM old_employees WHERE hire_date > DATE '2020-01-01';

UPDATE语句

UPDATE employees SET salary = salary * 1.1 WHERE dept_id = 10;

-- 关联更新
UPDATE employees e 
SET e.salary = (SELECT MAX(salary) FROM employees)
WHERE e.employee_id = 100;

DELETE语句

DELETE FROM employees WHERE salary < 5000;

-- 关联删除
DELETE FROM employees e 
WHERE EXISTS (SELECT 1 FROM departments d 
              WHERE d.dept_id = e.dept_id AND d.location = '上海');

MERGE语句(upsert操作)

MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.name = s.name, t.value = s.value
WHEN NOT MATCHED THEN INSERT (id, name, value) VALUES (s.id, s.name, s.value);

数据定义语言(DDL)

DDL用于定义和管理数据库对象结构。

表操作

-- 创建表
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(20),
    last_name VARCHAR2(25) NOT NULL,
    hire_date DATE DEFAULT SYSDATE,
    salary NUMBER(8,2) CHECK (salary > 0)
);

-- 修改表
ALTER TABLE employees ADD (email VARCHAR2(50));
ALTER TABLE employees MODIFY (salary NUMBER(10,2));
ALTER TABLE employees DROP COLUMN email;

-- 删除表
DROP TABLE employees PURGE;

索引操作

CREATE INDEX idx_emp_name ON employees(last_name, first_name);
DROP INDEX idx_emp_name;

视图操作

CREATE VIEW emp_dept_view AS
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e JOIN departments d ON e.department_id = d.department_id;

序列操作

CREATE SEQUENCE emp_seq 
START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;

-- 使用序列
INSERT INTO employees VALUES (emp_seq.NEXTVAL, '李四', ...);

数据控制语言(DCL)

DCL用于控制数据库访问权限。

权限管理

-- 授予权限
GRANT SELECT, INSERT ON employees TO user1;

-- 撤销权限
REVOKE UPDATE ON departments FROM user2;

-- 系统权限
GRANT CREATE TABLE, CREATE VIEW TO scott;

角色管理

CREATE ROLE hr_admin;
GRANT SELECT ANY TABLE TO hr_admin;
GRANT hr_admin TO user1, user2;

事务控制语言(TCL)

TCL用于管理数据库事务。

基本事务语句

COMMIT; -- 提交事务
ROLLBACK; -- 回滚事务
SAVEPOINT sp1; -- 创建保存点
ROLLBACK TO sp1; -- 回滚到保存点

事务隔离级别

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION READ ONLY;

特殊操作语句

闪回查询

-- 查询历史数据
SELECT * FROM employees AS OF TIMESTAMP 
TO_TIMESTAMP('2023-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS');

表空间操作

-- 创建表空间
CREATE TABLESPACE tbs_01 DATAFILE '/u01/oracle/tbs01.dbf' SIZE 500M;

-- 修改表空间
ALTER TABLESPACE tbs_01 ADD DATAFILE '/u01/oracle/tbs02.dbf' SIZE 1G;

PL/SQL特有语句

程序结构控制

-- IF语句
IF salary > 10000 THEN
  bonus := salary * 0.2;
ELSIF salary > 5000 THEN
  bonus := salary * 0.1;
ELSE
  bonus := 500;
END IF;

-- CASE语句
CASE dept_id
  WHEN 10 THEN bonus := salary * 0.15;
  WHEN 20 THEN bonus := salary * 0.12;
  ELSE bonus := salary * 0.1;
END CASE;

循环语句

-- FOR循环
FOR i IN 1..10 LOOP
  INSERT INTO test_table VALUES (i);
END LOOP;

-- WHILE循环
WHILE total < 10000 LOOP
  total := total + amount;
END LOOP;

异常处理

BEGIN
  -- 业务逻辑
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('未找到数据');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('错误代码: ' || SQLCODE);
    DBMS_OUTPUT.PUT_LINE('错误信息: ' || SQLERRM);
END;

游标操作

-- 显式游标
DECLARE
  CURSOR emp_cursor IS SELECT * FROM employees;
  v_emp employees%ROWTYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO v_emp;
    EXIT WHEN emp_cursor%NOTFOUND;
    -- 处理数据
  END LOOP;
  CLOSE emp_cursor;
END;

总结

Oracle数据库提供了全面而强大的SQL语句集，涵盖了从基础数据操作到高级数据库管理的各个方面。熟练掌握这些语句对于有效使用Oracle数据库至关重要。不同类型的SQL语句各司其职：

DQL负责数据检索和分析
DML处理数据增删改
DDL管理数据库结构
DCL控制访问权限
TCL确保事务完整性
PL/SQL扩展了过程化编程能力

实际应用中，这些语句往往需要组合使用，配合Oracle特有的功能如闪回查询、分析函数等，可以构建出高效、可靠的数据库应用系统。 “`

这篇文章详细介绍了Oracle数据库中的各类操作语句，按照标准分类组织内容，包含大量实用的代码示例，总字数约3100字。采用Markdown格式编写，包含标题、目录、代码块等标准元素，便于阅读和技术文档使用。