oracle相关学习

建表脚本
-- 创建主表 t_testcase
CREATE TABLE t_testcase (
id NUMBER(20) NOT NULL,
tcid VARCHAR2(100) NOT NULL,
name VARCHAR2(500),
source VARCHAR2(100),
app VARCHAR2(100) NOT NULL,
comments VARCHAR2(1000),
last_updated_by VARCHAR2(100),
last_updated_time DATE DEFAULT SYSDATE,
CONSTRAINT pk_t_testcase PRIMARY KEY (id)
);

-- 创建唯一约束：tcid + app 不能冲突
ALTER TABLE t_testcase
ADD CONSTRAINT uk_t_testcase_tcid_app UNIQUE (tcid, app);

-- 创建历史表 t_testcase_his
CREATE TABLE t_testcase_his (
id NUMBER(20) NOT NULL,
tcid VARCHAR2(100) NOT NULL,
name VARCHAR2(500),
source VARCHAR2(100),
app VARCHAR2(100) NOT NULL,
comments VARCHAR2(1000),
last_updated_by VARCHAR2(100),
last_updated_time DATE,
operation_type VARCHAR2(10), -- INSERT/UPDATE/DELETE
operation_time DATE DEFAULT SYSDATE,
operation_user VARCHAR2(100)
);

-- 创建序列用于自动生成ID
CREATE SEQUENCE seq_t_testcase_id
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;

-- 创建触发器：自动填充ID
CREATE OR REPLACE TRIGGER trg_t_testcase_auto_id
BEFORE INSERT ON t_testcase
FOR EACH ROW
WHEN (NEW.id IS NULL)
BEGIN
SELECT seq_t_testcase_id.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/

-- 创建触发器：记录历史变更
CREATE OR REPLACE TRIGGER trg_t_testcase_history
AFTER INSERT OR UPDATE OR DELETE ON t_testcase
FOR EACH ROW
DECLARE
v_operation_type VARCHAR2(10);
BEGIN
-- 判断操作类型
IF INSERTING THEN
v_operation_type := 'INSERT';
INSERT INTO t_testcase_his (
id, tcid, name, source, app, comments,
last_updated_by, last_updated_time,
operation_type, operation_time, operation_user
) VALUES (
:NEW.id, :NEW.tcid, :NEW.name, :NEW.source, :NEW.app, :NEW.comments,
:NEW.last_updated_by, :NEW.last_updated_time,
v_operation_type, SYSDATE, :NEW.last_updated_by
);
ELSIF UPDATING THEN
v_operation_type := 'UPDATE';
-- 记录更新前的数据
INSERT INTO t_testcase_his (
id, tcid, name, source, app, comments,
last_updated_by, last_updated_time,
operation_type, operation_time, operation_user
) VALUES (
:OLD.id, :OLD.tcid, :OLD.name, :OLD.source, :OLD.app, :OLD.comments,
:OLD.last_updated_by, :OLD.last_updated_time,
v_operation_type, SYSDATE, :NEW.last_updated_by
);
ELSIF DELETING THEN
v_operation_type := 'DELETE';
INSERT INTO t_testcase_his (
id, tcid, name, source, app, comments,
last_updated_by, last_updated_time,
operation_type, operation_time, operation_user
) VALUES (
:OLD.id, :OLD.tcid, :OLD.name, :OLD.source, :OLD.app, :OLD.comments,
:OLD.last_updated_by, :OLD.last_updated_time,
v_operation_type, SYSDATE, :OLD.last_updated_by
);
END IF;
END;
/

-- 创建索引以提高查询性能
-- 主表索引
CREATE INDEX idx_t_testcase_tcid ON t_testcase(tcid);
CREATE INDEX idx_t_testcase_app ON t_testcase(app);
CREATE INDEX idx_t_testcase_last_updated_time ON t_testcase(last_updated_time);
CREATE INDEX idx_t_testcase_last_updated_by ON t_testcase(last_updated_by);

-- 历史表索引
CREATE INDEX idx_t_testcase_his_id ON t_testcase_his(id);
CREATE INDEX idx_t_testcase_his_tcid ON t_testcase_his(tcid);
CREATE INDEX idx_t_testcase_his_app ON t_testcase_his(app);
CREATE INDEX idx_t_testcase_his_operation_time ON t_testcase_his(operation_time);
CREATE INDEX idx_t_testcase_his_operation_user ON t_testcase_his(operation_user);
CREATE INDEX idx_t_testcase_his_operation_type ON t_testcase_his(operation_type);

-- 复合索引（常用查询组合）
CREATE INDEX idx_t_testcase_tcid_app ON t_testcase(tcid, app);
CREATE INDEX idx_t_testcase_his_id_operation_time ON t_testcase_his(id, operation_time);

-- 添加注释
COMMENT ON TABLE t_testcase IS '测试用例表';
COMMENT ON COLUMN t_testcase.id IS '主键ID';
COMMENT ON COLUMN t_testcase.tcid IS '测试用例ID';
COMMENT ON COLUMN t_testcase.name IS '测试用例名称';
COMMENT ON COLUMN t_testcase.source IS '来源';
COMMENT ON COLUMN t_testcase.app IS '应用';
COMMENT ON COLUMN t_testcase.comments IS '备注';
COMMENT ON COLUMN t_testcase.last_updated_by IS '最后更新人';
COMMENT ON COLUMN t_testcase.last_updated_time IS '最后更新时间';

COMMENT ON TABLE t_testcase_his IS '测试用例历史表';
COMMENT ON COLUMN t_testcase_his.operation_type IS '操作类型(INSERT/UPDATE/DELETE)';
COMMENT ON COLUMN t_testcase_his.operation_time IS '操作时间';
COMMENT ON COLUMN t_testcase_his.operation_user IS '操作用户';
回滚脚本
-- 回滚脚本 - 删除所有创建的对象（按依赖关系倒序删除）
-- 删除索引
DROP INDEX idx_t_testcase_his_operation_type;
DROP INDEX idx_t_testcase_his_operation_user;
DROP INDEX idx_t_testcase_his_operation_time;
DROP INDEX idx_t_testcase_his_app;
DROP INDEX idx_t_testcase_his_tcid;
DROP INDEX idx_t_testcase_his_id;
DROP INDEX idx_t_testcase_last_updated_by;
DROP INDEX idx_t_testcase_last_updated_time;
DROP INDEX idx_t_testcase_app;
DROP INDEX idx_t_testcase_tcid;
DROP INDEX idx_t_testcase_tcid_app;
DROP INDEX idx_t_testcase_his_id_operation_time;

-- 删除触发器
DROP TRIGGER trg_t_testcase_history;
DROP TRIGGER trg_t_testcase_auto_id;

-- 删除序列
DROP SEQUENCE seq_t_testcase_id;

-- 删除历史表
DROP TABLE t_testcase_his;

-- 删除主表
DROP TABLE t_testcase;
使用示例
-- 插入测试数据（ID会自动递增）
INSERT INTO t_testcase (tcid, name, source, app, comments, last_updated_by)
VALUES ('TC001', '测试用例1', '手工编写', 'APP1', '第一个测试用例', 'admin');

INSERT INTO t_testcase (tcid, name, source, app, comments, last_updated_by)
VALUES ('TC002', '测试用例2', '自动化生成', 'APP2', '第二个测试用例', 'admin');

-- 更新数据
UPDATE t_testcase
SET name = '更新后的测试用例1', last_updated_by = 'user1', last_updated_time = SYSDATE
WHERE tcid = 'TC001';

-- 查看历史记录
SELECT * FROM t_testcase_his ORDER BY operation_time DESC;