Oracle數據庫中有哪些操作語句

# 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;

高級查詢功能

1. 多表連接 “`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 = '北京');

1. 分析函數

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

2. 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格式編寫，包含標題、目錄、代碼塊等標準元素，便于閱讀和技術文檔使用。