From 8a89054c2f1dfa06c6dfc744217b35e66b8a3661 Mon Sep 17 00:00:00 2001 From: 疯狂的狮子li <15040126243@163.com> Date: 星期三, 09 三月 2022 16:21:53 +0800 Subject: [PATCH] add 增加 oracle_test.sql 测试sql文件 --- script/sql/oracle/oracle_test.sql | 196 +++++++++++++++++++++++++++++++++++++++++++++++++ 1 files changed, 196 insertions(+), 0 deletions(-) diff --git a/script/sql/oracle/oracle_test.sql b/script/sql/oracle/oracle_test.sql new file mode 100644 index 0000000..97a427e --- /dev/null +++ b/script/sql/oracle/oracle_test.sql @@ -0,0 +1,196 @@ +create table test_demo ( + id number(20) not null, + dept_id number(20) default null, + user_id number(20) default null, + order_num number(10) default 0, + test_key varchar2(255) default null, + value varchar2(255) default null, + version number(10) default 0, + create_time date, + create_by varchar2(64) default null, + update_time date, + update_by varchar2(64) default null, + del_flag number(2) default 0 +); + +alter table test_demo add constraint pk_test_demo primary key (id); + +comment on table test_demo is '娴嬭瘯鍗曡〃'; +comment on column test_demo.id is '涓婚敭'; +comment on column test_demo.dept_id is '閮ㄩ棬id'; +comment on column test_demo.user_id is '鐢ㄦ埛id'; +comment on column test_demo.order_num is '鎺掑簭鍙�'; +comment on column test_demo.test_key is 'key閿�'; +comment on column test_demo.value is '鍊�'; +comment on column test_demo.version is '鐗堟湰'; +comment on column test_demo.create_time is '鍒涘缓鏃堕棿'; +comment on column test_demo.create_by is '鍒涘缓浜�'; +comment on column test_demo.update_time is '鏇存柊鏃堕棿'; +comment on column test_demo.update_by is '鏇存柊浜�'; +comment on column test_demo.del_flag is '鍒犻櫎鏍囧織'; + +create table test_tree ( + id number(20) not null, + parent_id number(20) default 0, + dept_id number(20) default null, + user_id number(20) default null, + tree_name varchar2(255) default null, + version number(10) default 0, + create_time date, + create_by varchar2(64) default null, + update_time date, + update_by varchar2(64) default null, + del_flag number(2) default 0 +); + +alter table test_tree add constraint pk_test_tree primary key (id); + +comment on table test_tree is '娴嬭瘯鏍戣〃'; +comment on column test_tree.id is '涓婚敭'; +comment on column test_tree.parent_id is '鐖秈d'; +comment on column test_tree.dept_id is '閮ㄩ棬id'; +comment on column test_tree.user_id is '鐢ㄦ埛id'; +comment on column test_tree.tree_name is '鍊�'; +comment on column test_tree.version is '鐗堟湰'; +comment on column test_tree.create_time is '鍒涘缓鏃堕棿'; +comment on column test_tree.create_by is '鍒涘缓浜�'; +comment on column test_tree.update_time is '鏇存柊鏃堕棿'; +comment on column test_tree.update_by is '鏇存柊浜�'; +comment on column test_tree.del_flag is '鍒犻櫎鏍囧織'; + +insert into sys_user(user_id, dept_id, user_name, nick_name, user_type, email, phonenumber, sex, avatar, password, status, del_flag, login_ip, login_date, create_by, create_time, update_by, update_time, remark) values (3, 108, 'test', '鏈儴闂ㄥ強浠ヤ笅 瀵嗙爜666666', 'sys_user', '', '', '0', '', '$2a$10$b8yUzN0C71sbz.PhNOCgJe.Tu1yWC3RNrTyjSQ8p1W0.aaUXUJ.Ne', '0', '0', '127.0.0.1', sysdate, 'admin', sysdate, 'test', sysdate, null); +insert into sys_user(user_id, dept_id, user_name, nick_name, user_type, email, phonenumber, sex, avatar, password, status, del_flag, login_ip, login_date, create_by, create_time, update_by, update_time, remark) values (4, 102, 'test1', '浠呮湰浜� 瀵嗙爜666666', 'sys_user', '', '', '0', '', '$2a$10$b8yUzN0C71sbz.PhNOCgJe.Tu1yWC3RNrTyjSQ8p1W0.aaUXUJ.Ne', '0', '0', '127.0.0.1', sysdate, 'admin', sysdate, 'test1', sysdate, null); + +insert into sys_menu(menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values (5, '娴嬭瘯鑿滃崟', 0, 5, 'demo', null, 1, 0, 'M', '0', '0', null, 'star', 'admin', sysdate, 'admin', sysdate, ''); + +insert into sys_menu(menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values (1500, '娴嬭瘯鍗曡〃', 5, 1, 'demo', 'demo/demo/index', 1, 0, 'C', '0', '0', 'demo:demo:list', '#', 'admin', sysdate, '', null, '娴嬭瘯鍗曡〃鑿滃崟'); +insert into sys_menu(menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values (1501, '娴嬭瘯鍗曡〃鏌ヨ', 1500, 1, '#', '', 1, 0, 'F', '0', '0', 'demo:demo:query', '#', 'admin', sysdate, '', null, ''); +insert into sys_menu(menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values (1502, '娴嬭瘯鍗曡〃鏂板', 1500, 2, '#', '', 1, 0, 'F', '0', '0', 'demo:demo:add', '#', 'admin', sysdate, '', null, ''); +insert into sys_menu(menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values (1503, '娴嬭瘯鍗曡〃淇敼', 1500, 3, '#', '', 1, 0, 'F', '0', '0', 'demo:demo:edit', '#', 'admin', sysdate, '', null, ''); +insert into sys_menu(menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values (1504, '娴嬭瘯鍗曡〃鍒犻櫎', 1500, 4, '#', '', 1, 0, 'F', '0', '0', 'demo:demo:remove', '#', 'admin', sysdate, '', null, ''); +insert into sys_menu(menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values (1505, '娴嬭瘯鍗曡〃瀵煎嚭', 1500, 5, '#', '', 1, 0, 'F', '0', '0', 'demo:demo:export', '#', 'admin', sysdate, '', null, ''); + +insert into sys_menu(menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values (1506, '娴嬭瘯鏍戣〃', 5, 1, 'tree', 'demo/tree/index', 1, 0, 'C', '0', '0', 'demo:tree:list', '#', 'admin', sysdate, '', null, '娴嬭瘯鏍戣〃鑿滃崟'); +insert into sys_menu(menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values (1507, '娴嬭瘯鏍戣〃鏌ヨ', 1506, 1, '#', '', 1, 0, 'F', '0', '0', 'demo:tree:query', '#', 'admin', sysdate, '', null, ''); +insert into sys_menu(menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values (1508, '娴嬭瘯鏍戣〃鏂板', 1506, 2, '#', '', 1, 0, 'F', '0', '0', 'demo:tree:add', '#', 'admin', sysdate, '', null, ''); +insert into sys_menu(menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values (1509, '娴嬭瘯鏍戣〃淇敼', 1506, 3, '#', '', 1, 0, 'F', '0', '0', 'demo:tree:edit', '#', 'admin', sysdate, '', null, ''); +insert into sys_menu(menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values (1510, '娴嬭瘯鏍戣〃鍒犻櫎', 1506, 4, '#', '', 1, 0, 'F', '0', '0', 'demo:tree:remove', '#', 'admin', sysdate, '', null, ''); +insert into sys_menu(menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values (1511, '娴嬭瘯鏍戣〃瀵煎嚭', 1506, 5, '#', '', 1, 0, 'F', '0', '0', 'demo:tree:export', '#', 'admin', sysdate, '', null, ''); + +insert into sys_role(role_id, role_name, role_key, role_sort, data_scope, menu_check_strictly, dept_check_strictly, status, del_flag, create_by, create_time, update_by, update_time, remark) values (3, '鏈儴闂ㄥ強浠ヤ笅', 'test1', 3, '4', 1, 1, '0', '0', 'admin', sysdate, '', null, null); +insert into sys_role(role_id, role_name, role_key, role_sort, data_scope, menu_check_strictly, dept_check_strictly, status, del_flag, create_by, create_time, update_by, update_time, remark) values (4, '浠呮湰浜�', 'test2', 4, '5', 1, 1, '0', '0', 'admin', sysdate, '', null, null); + +insert into sys_role_menu(role_id, menu_id) values (3, 1); +insert into sys_role_menu(role_id, menu_id) values (3, 5); +insert into sys_role_menu(role_id, menu_id) values (3, 100); +insert into sys_role_menu(role_id, menu_id) values (3, 101); +insert into sys_role_menu(role_id, menu_id) values (3, 102); +insert into sys_role_menu(role_id, menu_id) values (3, 103); +insert into sys_role_menu(role_id, menu_id) values (3, 104); +insert into sys_role_menu(role_id, menu_id) values (3, 105); +insert into sys_role_menu(role_id, menu_id) values (3, 106); +insert into sys_role_menu(role_id, menu_id) values (3, 107); +insert into sys_role_menu(role_id, menu_id) values (3, 108); +insert into sys_role_menu(role_id, menu_id) values (3, 500); +insert into sys_role_menu(role_id, menu_id) values (3, 501); +insert into sys_role_menu(role_id, menu_id) values (3, 1001); +insert into sys_role_menu(role_id, menu_id) values (3, 1002); +insert into sys_role_menu(role_id, menu_id) values (3, 1003); +insert into sys_role_menu(role_id, menu_id) values (3, 1004); +insert into sys_role_menu(role_id, menu_id) values (3, 1005); +insert into sys_role_menu(role_id, menu_id) values (3, 1006); +insert into sys_role_menu(role_id, menu_id) values (3, 1007); +insert into sys_role_menu(role_id, menu_id) values (3, 1008); +insert into sys_role_menu(role_id, menu_id) values (3, 1009); +insert into sys_role_menu(role_id, menu_id) values (3, 1010); +insert into sys_role_menu(role_id, menu_id) values (3, 1011); +insert into sys_role_menu(role_id, menu_id) values (3, 1012); +insert into sys_role_menu(role_id, menu_id) values (3, 1013); +insert into sys_role_menu(role_id, menu_id) values (3, 1014); +insert into sys_role_menu(role_id, menu_id) values (3, 1015); +insert into sys_role_menu(role_id, menu_id) values (3, 1016); +insert into sys_role_menu(role_id, menu_id) values (3, 1017); +insert into sys_role_menu(role_id, menu_id) values (3, 1018); +insert into sys_role_menu(role_id, menu_id) values (3, 1019); +insert into sys_role_menu(role_id, menu_id) values (3, 1020); +insert into sys_role_menu(role_id, menu_id) values (3, 1021); +insert into sys_role_menu(role_id, menu_id) values (3, 1022); +insert into sys_role_menu(role_id, menu_id) values (3, 1023); +insert into sys_role_menu(role_id, menu_id) values (3, 1024); +insert into sys_role_menu(role_id, menu_id) values (3, 1025); +insert into sys_role_menu(role_id, menu_id) values (3, 1026); +insert into sys_role_menu(role_id, menu_id) values (3, 1027); +insert into sys_role_menu(role_id, menu_id) values (3, 1028); +insert into sys_role_menu(role_id, menu_id) values (3, 1029); +insert into sys_role_menu(role_id, menu_id) values (3, 1030); +insert into sys_role_menu(role_id, menu_id) values (3, 1031); +insert into sys_role_menu(role_id, menu_id) values (3, 1032); +insert into sys_role_menu(role_id, menu_id) values (3, 1033); +insert into sys_role_menu(role_id, menu_id) values (3, 1034); +insert into sys_role_menu(role_id, menu_id) values (3, 1035); +insert into sys_role_menu(role_id, menu_id) values (3, 1036); +insert into sys_role_menu(role_id, menu_id) values (3, 1037); +insert into sys_role_menu(role_id, menu_id) values (3, 1038); +insert into sys_role_menu(role_id, menu_id) values (3, 1039); +insert into sys_role_menu(role_id, menu_id) values (3, 1040); +insert into sys_role_menu(role_id, menu_id) values (3, 1041); +insert into sys_role_menu(role_id, menu_id) values (3, 1042); +insert into sys_role_menu(role_id, menu_id) values (3, 1043); +insert into sys_role_menu(role_id, menu_id) values (3, 1044); +insert into sys_role_menu(role_id, menu_id) values (3, 1045); +insert into sys_role_menu(role_id, menu_id) values (3, 1500); +insert into sys_role_menu(role_id, menu_id) values (3, 1501); +insert into sys_role_menu(role_id, menu_id) values (3, 1502); +insert into sys_role_menu(role_id, menu_id) values (3, 1503); +insert into sys_role_menu(role_id, menu_id) values (3, 1504); +insert into sys_role_menu(role_id, menu_id) values (3, 1505); +insert into sys_role_menu(role_id, menu_id) values (3, 1506); +insert into sys_role_menu(role_id, menu_id) values (3, 1507); +insert into sys_role_menu(role_id, menu_id) values (3, 1508); +insert into sys_role_menu(role_id, menu_id) values (3, 1509); +insert into sys_role_menu(role_id, menu_id) values (3, 1510); +insert into sys_role_menu(role_id, menu_id) values (3, 1511); +insert into sys_role_menu(role_id, menu_id) values (4, 5); +insert into sys_role_menu(role_id, menu_id) values (4, 1500); +insert into sys_role_menu(role_id, menu_id) values (4, 1501); +insert into sys_role_menu(role_id, menu_id) values (4, 1502); +insert into sys_role_menu(role_id, menu_id) values (4, 1503); +insert into sys_role_menu(role_id, menu_id) values (4, 1504); +insert into sys_role_menu(role_id, menu_id) values (4, 1505); +insert into sys_role_menu(role_id, menu_id) values (4, 1506); +insert into sys_role_menu(role_id, menu_id) values (4, 1507); +insert into sys_role_menu(role_id, menu_id) values (4, 1508); +insert into sys_role_menu(role_id, menu_id) values (4, 1509); +insert into sys_role_menu(role_id, menu_id) values (4, 1510); +insert into sys_role_menu(role_id, menu_id) values (4, 1511); + +insert into sys_user_role(user_id, role_id) values (3, 3); +insert into sys_user_role(user_id, role_id) values (4, 4); + +insert into test_demo(id, dept_id, user_id, order_num, test_key, value, version, create_time, create_by, update_time, update_by, del_flag) values (1, 102, 4, 1, '娴嬭瘯鏁版嵁鏉冮檺', '娴嬭瘯', 0, sysdate, 'admin', null, '', 0); +insert into test_demo(id, dept_id, user_id, order_num, test_key, value, version, create_time, create_by, update_time, update_by, del_flag) values (2, 102, 3, 2, '瀛愯妭鐐�1', '111', 0, sysdate, 'admin', null, '', 0); +insert into test_demo(id, dept_id, user_id, order_num, test_key, value, version, create_time, create_by, update_time, update_by, del_flag) values (3, 102, 3, 3, '瀛愯妭鐐�2', '222', 0, sysdate, 'admin', null, '', 0); +insert into test_demo(id, dept_id, user_id, order_num, test_key, value, version, create_time, create_by, update_time, update_by, del_flag) values (4, 108, 4, 4, '娴嬭瘯鏁版嵁', 'demo', 0, sysdate, 'admin', null, '', 0); +insert into test_demo(id, dept_id, user_id, order_num, test_key, value, version, create_time, create_by, update_time, update_by, del_flag) values (5, 108, 3, 13, '瀛愯妭鐐�11', '1111', 0, sysdate, 'admin', null, '', 0); +insert into test_demo(id, dept_id, user_id, order_num, test_key, value, version, create_time, create_by, update_time, update_by, del_flag) values (6, 108, 3, 12, '瀛愯妭鐐�22', '2222', 0, sysdate, 'admin', null, '', 0); +insert into test_demo(id, dept_id, user_id, order_num, test_key, value, version, create_time, create_by, update_time, update_by, del_flag) values (7, 108, 3, 11, '瀛愯妭鐐�33', '3333', 0, sysdate, 'admin', null, '', 0); +insert into test_demo(id, dept_id, user_id, order_num, test_key, value, version, create_time, create_by, update_time, update_by, del_flag) values (8, 108, 3, 10, '瀛愯妭鐐�44', '4444', 0, sysdate, 'admin', null, '', 0); +insert into test_demo(id, dept_id, user_id, order_num, test_key, value, version, create_time, create_by, update_time, update_by, del_flag) values (9, 108, 3, 9, '瀛愯妭鐐�55', '5555', 0, sysdate, 'admin', null, '', 0); +insert into test_demo(id, dept_id, user_id, order_num, test_key, value, version, create_time, create_by, update_time, update_by, del_flag) values (10, 108, 3, 8, '瀛愯妭鐐�66', '6666', 0, sysdate, 'admin', null, '', 0); +insert into test_demo(id, dept_id, user_id, order_num, test_key, value, version, create_time, create_by, update_time, update_by, del_flag) values (11, 108, 3, 7, '瀛愯妭鐐�77', '7777', 0, sysdate, 'admin', null, '', 0); +insert into test_demo(id, dept_id, user_id, order_num, test_key, value, version, create_time, create_by, update_time, update_by, del_flag) values (12, 108, 3, 6, '瀛愯妭鐐�88', '8888', 0, sysdate, 'admin', null, '', 0); +insert into test_demo(id, dept_id, user_id, order_num, test_key, value, version, create_time, create_by, update_time, update_by, del_flag) values (13, 108, 3, 5, '瀛愯妭鐐�99', '9999', 0, sysdate, 'admin', null, '', 0); + +insert into test_tree(id, parent_id, dept_id, user_id, tree_name, version, create_time, create_by, update_time, update_by, del_flag) values (1, 0, 102, 4, '娴嬭瘯鏁版嵁鏉冮檺', 0, sysdate, 'admin', null, '', 0); +insert into test_tree(id, parent_id, dept_id, user_id, tree_name, version, create_time, create_by, update_time, update_by, del_flag) values (2, 1, 102, 3, '瀛愯妭鐐�1', 0, sysdate, 'admin', null, '', 0); +insert into test_tree(id, parent_id, dept_id, user_id, tree_name, version, create_time, create_by, update_time, update_by, del_flag) values (3, 2, 102, 3, '瀛愯妭鐐�2', 0, sysdate, 'admin', null, '', 0); +insert into test_tree(id, parent_id, dept_id, user_id, tree_name, version, create_time, create_by, update_time, update_by, del_flag) values (4, 0, 108, 4, '娴嬭瘯鏍�1', 0, sysdate, 'admin', null, '', 0); +insert into test_tree(id, parent_id, dept_id, user_id, tree_name, version, create_time, create_by, update_time, update_by, del_flag) values (5, 4, 108, 3, '瀛愯妭鐐�11', 0, sysdate, 'admin', null, '', 0); +insert into test_tree(id, parent_id, dept_id, user_id, tree_name, version, create_time, create_by, update_time, update_by, del_flag) values (6, 4, 108, 3, '瀛愯妭鐐�22', 0, sysdate, 'admin', null, '', 0); +insert into test_tree(id, parent_id, dept_id, user_id, tree_name, version, create_time, create_by, update_time, update_by, del_flag) values (7, 4, 108, 3, '瀛愯妭鐐�33', 0, sysdate, 'admin', null, '', 0); +insert into test_tree(id, parent_id, dept_id, user_id, tree_name, version, create_time, create_by, update_time, update_by, del_flag) values (8, 5, 108, 3, '瀛愯妭鐐�44', 0, sysdate, 'admin', null, '', 0); +insert into test_tree(id, parent_id, dept_id, user_id, tree_name, version, create_time, create_by, update_time, update_by, del_flag) values (9, 6, 108, 3, '瀛愯妭鐐�55', 0, sysdate, 'admin', null, '', 0); +insert into test_tree(id, parent_id, dept_id, user_id, tree_name, version, create_time, create_by, update_time, update_by, del_flag) values (10, 7, 108, 3, '瀛愯妭鐐�66', 0, sysdate, 'admin', null, '', 0); +insert into test_tree(id, parent_id, dept_id, user_id, tree_name, version, create_time, create_by, update_time, update_by, del_flag) values (11, 7, 108, 3, '瀛愯妭鐐�77', 0, sysdate, 'admin', null, '', 0); +insert into test_tree(id, parent_id, dept_id, user_id, tree_name, version, create_time, create_by, update_time, update_by, del_flag) values (12, 10, 108, 3, '瀛愯妭鐐�88', 0, sysdate, 'admin', null, '', 0); +insert into test_tree(id, parent_id, dept_id, user_id, tree_name, version, create_time, create_by, update_time, update_by, del_flag) values (13, 10, 108, 3, '瀛愯妭鐐�99', 0, sysdate, 'admin', null, '', 0); -- Gitblit v1.9.3