-- 部门表
|
CREATE TABLE sys_department (
|
id NUMBER(19) PRIMARY KEY,
|
dept_code VARCHAR2(50 CHAR) NOT NULL UNIQUE,
|
dept_name VARCHAR2(100 CHAR) NOT NULL,
|
parent_code VARCHAR2(50 CHAR) DEFAULT '0',
|
sort_order NUMBER(10) DEFAULT 0,
|
description VARCHAR2(500 CHAR),
|
status NUMBER(1) DEFAULT 1,
|
create_time DATE DEFAULT SYSDATE,
|
update_time DATE DEFAULT SYSDATE,
|
create_by VARCHAR2(50 CHAR),
|
update_by VARCHAR2(50 CHAR),
|
deleted NUMBER(1) DEFAULT 0,
|
version NUMBER(10) DEFAULT 1
|
);
|
|
-- 用户表
|
CREATE TABLE sys_user (
|
id NUMBER(19) PRIMARY KEY,
|
username VARCHAR2(50 CHAR) NOT NULL UNIQUE,
|
real_name VARCHAR2(100 CHAR),
|
password VARCHAR2(100 CHAR) NOT NULL,
|
email VARCHAR2(100 CHAR),
|
phone VARCHAR2(20 CHAR),
|
dept_code VARCHAR2(50 CHAR),
|
status NUMBER(1) DEFAULT 1,
|
create_time DATE DEFAULT SYSDATE,
|
update_time DATE DEFAULT SYSDATE,
|
create_by VARCHAR2(50 CHAR),
|
update_by VARCHAR2(50 CHAR),
|
deleted NUMBER(1) DEFAULT 0,
|
version NUMBER(10) DEFAULT 1
|
);
|
|
-- Excel数据表（交易流水数据）
|
CREATE TABLE data_excel (
|
id NUMBER(19) PRIMARY KEY,
|
sort_no NUMBER(10),
|
seq_no NUMBER(10),
|
secondary_unit VARCHAR2(100 CHAR),
|
unit_code VARCHAR2(50 CHAR),
|
unit_name VARCHAR2(200 CHAR),
|
transaction_no VARCHAR2(100 CHAR),
|
summary VARCHAR2(500 CHAR),
|
amount NUMBER(19, 2),
|
accounting_period VARCHAR2(20 CHAR),
|
voucher_no VARCHAR2(50 CHAR),
|
bp_contract_no VARCHAR2(100 CHAR),
|
contract_center_no VARCHAR2(100 CHAR),
|
bp_invoice_no VARCHAR2(100 CHAR),
|
gk_invoice_no VARCHAR2(100 CHAR),
|
business_relation VARCHAR2(200 CHAR),
|
remark VARCHAR2(500 CHAR),
|
status NUMBER(1) DEFAULT 1,
|
create_time DATE DEFAULT SYSDATE,
|
update_time DATE DEFAULT SYSDATE,
|
create_by VARCHAR2(50 CHAR),
|
update_by VARCHAR2(50 CHAR),
|
deleted NUMBER(1) DEFAULT 0,
|
version NUMBER(10) DEFAULT 1
|
);
|
|
-- 数据任务表
|
CREATE TABLE data_task (
|
id NUMBER(19) PRIMARY KEY,
|
unit_code VARCHAR2(50 CHAR),
|
unit_name VARCHAR2(200 CHAR),
|
status NUMBER(1) DEFAULT 0,
|
task_count NUMBER(10),
|
success_count NUMBER(10),
|
remark VARCHAR2(1000 CHAR),
|
create_time DATE DEFAULT SYSDATE,
|
update_time DATE DEFAULT SYSDATE,
|
create_by VARCHAR2(50 CHAR),
|
update_by VARCHAR2(50 CHAR),
|
deleted NUMBER(1) DEFAULT 0,
|
version NUMBER(10) DEFAULT 1,
|
last_time DATE DEFAULT SYSDATE
|
);
|
|
-- 创建序列
|
CREATE SEQUENCE seq_sys_department START WITH 1 INCREMENT BY 1;
|
CREATE SEQUENCE seq_sys_user START WITH 1 INCREMENT BY 1;
|
CREATE SEQUENCE seq_data_excel START WITH 1 INCREMENT BY 1;
|
CREATE SEQUENCE seq_data_task START WITH 1 INCREMENT BY 1;
|
|
-- 创建触发器（自动填充ID）
|
CREATE OR REPLACE TRIGGER trg_sys_department
|
BEFORE INSERT ON sys_department
|
FOR EACH ROW
|
BEGIN
|
SELECT seq_sys_department.NEXTVAL INTO :NEW.id FROM DUAL;
|
END;
|
/
|
|
CREATE OR REPLACE TRIGGER trg_sys_user
|
BEFORE INSERT ON sys_user
|
FOR EACH ROW
|
BEGIN
|
SELECT seq_sys_user.NEXTVAL INTO :NEW.id FROM DUAL;
|
END;
|
/
|
|
CREATE OR REPLACE TRIGGER trg_data_excel
|
BEFORE INSERT ON data_excel
|
FOR EACH ROW
|
BEGIN
|
SELECT seq_data_excel.NEXTVAL INTO :NEW.id FROM DUAL;
|
END;
|
/
|
|
CREATE OR REPLACE TRIGGER trg_data_task
|
BEFORE INSERT ON data_task
|
FOR EACH ROW
|
BEGIN
|
SELECT seq_data_task.NEXTVAL INTO :NEW.id FROM DUAL;
|
END;
|
/
|
|
-- 插入初始化数据
|
INSERT INTO sys_department (dept_code, dept_name, parent_code, sort_order, description, status) VALUES
|
('001', '总公司', '0', 1, '总公司', 1);
|
|
INSERT INTO sys_department (dept_code, dept_name, parent_code, sort_order, description, status) VALUES
|
('001001', '技术部', '001', 1, '技术研发部门', 1);
|
|
INSERT INTO sys_department (dept_code, dept_name, parent_code, sort_order, description, status) VALUES
|
('001002', '市场部', '001', 2, '市场营销部门', 1);
|
|
-- 创建默认用户 sysadmin，密码 sysadmin (MD5加密)
|
INSERT INTO sys_user (username, real_name, password, email, phone, dept_code, status) VALUES
|
('sysadmin', '系统管理员', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'sysadmin@example.com', '13800138000', '001', 1);
|
|
COMMIT;
|
