Oracle常用代码总结

在Oracle数据库中，常用的SQL代码和PL/SQL代码可以用于各种数据操作任务。以下是一些常见的Oracle代码示例：

1. 创建表：

CREATE TABLE employees (
    employee_id NUMBER(6),
    first_name VARCHAR2(20),
    last_name VARCHAR2(25),
    email VARCHAR2(25),
    phone_number VARCHAR2(20),
    hire_date DATE,
    job_id VARCHAR2(10),
    salary NUMBER(8,2),
    commission_pct NUMBER(2,2),
    manager_id NUMBER(6),
    department_id NUMBER(4)
);

2. 插入数据：

INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (100, 'John', 'Doe', 'john.doe@example.com', '123-4567-8901', TO_DATE('2000-01-01', 'YYYY-MM-DD'), 'IT_PROG', 60000, NULL, 101, 10);

3. 更新数据：

UPDATE employees SET salary = salary + 1000 WHERE employee_id = 100;

4. 删除数据：

DELETE FROM employees WHERE employee_id = 100;

5. 创建索引：

CREATE INDEX idx_last_name ON employees(last_name);

6. 创建视图：

CREATE VIEW active_employees AS
SELECT employee_id, first_name, last_name
FROM employees
WHERE hire_date > SYSDATE - INTERVAL '10' YEAR;

7. 创建存储过程：

CREATE OR REPLACE PROCEDURE raise_salary(p_employee_id IN NUMBER, p_raise_amount IN NUMBER) IS
BEGIN
    UPDATE employees SET salary = salary + p_raise_amount WHERE employee_id = p_employee_id;
    COMMIT;
END;

8. 调用存储过程：

BEGIN
    raise_salary(100, 2000);
END;

9. 创建触发器：

CREATE OR REPLACE TRIGGER audit_employees
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO employees_audit(employee_id, action, audit_date) VALUES (:NEW.employee_id, 'INSERT', SYSTIMESTAMP);
    ELSIF UPDATING THEN
        INSERT INTO employees_audit(employee_id, action, audit_date) VALUES (:NEW.employee_id, 'UPDATE', SYSTIMESTAMP);
    ELSE
        INSERT INTO employees_audit(employee_id, action, audit_date) VALUES (:OLD.employee_id, 'DELETE', SYSTIMESTAMP);
    END IF;
END;

这些代码片段涵盖了Oracle数据库操作的基本方面，包括表的创建、数据的插入、更新、删除，以及索引、视图、存储过程和触发器的创建与使用。这些操作是数据库开发中的基础，对于学习和理解Oracle数据库的结构和操作至关重要。