GaussDB T 初始化Oracle HR相关对象及数据

oracle有一个用于测试和学习的HR相关对象，并有相关初始数据，想着在GaussDB中创建同样的数据，发现直接执行load_sample.sql脚本还是存在很多报错，需要逐个去排查。最后整理成了可以直接运行的脚本，供大家参考：load_gauss_sample.sql，https://www.modb.pro/download/3532

直接运行脚本后，就会创建相关对象和初始数据：

--运行脚本
--创建的账号需要有connect和resource权限，并且需要额外授予create view权限
[omm@pr7 bin]$ zsql steven/'modb123$'@127.0.0.1:1888 -q

@/home/omm/load_gaussdb_sample.sql

SQL> select OBJECT_NAME,OBJECT_TYPE,created from user_objects  where created > to_date('20191227','yyyymmdd') order by 3;

OBJECT_NAME                 OBJECT_TYPE     CREATED               
--------------------------- --------------- ----------------------
REGIONS                     TABLE           2019-12-27 03:51:19   
REG_ID_PK                   INDEX           2019-12-27 03:51:19   
COUNTRIES                   TABLE           2019-12-27 03:51:19   
COUNTRY_C_ID_PK             INDEX           2019-12-27 03:51:19   
LOCATIONS                   TABLE           2019-12-27 03:51:19   
LOC_CITY_IX                 INDEX           2019-12-27 03:51:19   
LOC_COUNTRY_IX              INDEX           2019-12-27 03:51:19   
LOC_STATE_PROVINCE_IX       INDEX           2019-12-27 03:51:19   
LOC_ID_PK                   INDEX           2019-12-27 03:51:19   
LOCATIONS_SEQ               SEQUENCE        2019-12-27 03:51:19   
DEPARTMENTS                 TABLE           2019-12-27 03:51:19   
DEPT_LOCATION_IX            INDEX           2019-12-27 03:51:19   
DEPT_ID_PK                  INDEX           2019-12-27 03:51:19   
DEPARTMENTS_SEQ             SEQUENCE        2019-12-27 03:51:19   
JOBS                        TABLE           2019-12-27 03:51:19   
JOB_ID_PK                   INDEX           2019-12-27 03:51:19   
EMPLOYEES                   TABLE           2019-12-27 03:51:19   
EMP_EMP_ID_PK               INDEX           2019-12-27 03:51:19   
EMP_JOB_IX                  INDEX           2019-12-27 03:51:19   
EMP_MANAGER_IX              INDEX           2019-12-27 03:51:19   
EMP_NAME_IX                 INDEX           2019-12-27 03:51:19   
EMP_EMAIL_UK                INDEX           2019-12-27 03:51:19   
EMP_DEPARTMENT_IX           INDEX           2019-12-27 03:51:19   
EMPLOYEES_SEQ               SEQUENCE        2019-12-27 03:51:19   
JOB_HISTORY                 TABLE           2019-12-27 03:51:19   
JHIST_DEPARTMENT_IX         INDEX           2019-12-27 03:51:19   
JHIST_EMPLOYEE_IX           INDEX           2019-12-27 03:51:19   
JHIST_EMP_ID_ST_DATE_PK     INDEX           2019-12-27 03:51:19   
JHIST_JOB_IX                INDEX           2019-12-27 03:51:19   
EMP_DETAILS_VIEW            VIEW            2019-12-27 03:51:19   
SECURE_DML                  PROCEDURE       2019-12-27 03:51:19   
SECURE_EMPLOYEES            TRIGGER         2019-12-27 03:51:19   
ADD_JOB_HISTORY             PROCEDURE       2019-12-27 03:51:19   
UPDATE_JOB_HISTORY          TRIGGER         2019-12-27 03:51:19   

34 rows fetched.

可以执行HR相关的测试SQL：

SQL> SELECT first_name "First", last_name "Last"
  2 FROM employees
  3 WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i');

First                Last                     
-------------------- -------------------------
Harrison             Bloom                    
Lex                  De Haan                  
Kevin                Feeney                   
Ki                   Gee                      
Nancy                Greenberg                
Danielle             Greene                   
Alexander            Khoo                     
David                Lee                      

8 rows fetched.

SQL> SELECT first_name || ' ' || last_name "Name",
  2   NVL((commission_pct * 100), 0) "Comm Rate",
  3   NVL2(commission_pct, 
  4     ROUND(salary * 12 + commission_pct * 300000, 2),
  5     salary * 12) "With $300K Sales"
  6 FROM employees
  7 WHERE job_id LIKE '%_M%' AND department_id = 80;

Name                                           Comm Rate                                With $300K Sales                        
---------------------------------------------- ---------------------------------------- ----------------------------------------
John Russell                                   40                                       288000                                  
Karen Partners                                 30                                       252000                                  
Alberto Errazuriz                              30                                       234000                                  
Gerald Cambrault                               30                                       222000                                  
Eleni Zlotkey                                  20                                       186000                                  

5 rows fetched.

更多sql可以参考oracle的官方文档：https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_querying.htm

清理脚本:

alter table locations drop CONSTRAINT loc_c_id_fk ;
ALTER TABLE employees drop CONSTRAINT emp_dept_fk;
ALTER TABLE employees drop CONSTRAINT emp_job_fk;
ALTER TABLE employees drop CONSTRAINT emp_manager_fk;
ALTER TABLE departments drop  CONSTRAINT dept_mgr_fk;

drop PROCEDURE add_job_history;
drop PROCEDURE secure_dml;
drop TABLE job_history purge;
drop table employees purge;
drop SEQUENCE employees_seq;
drop table employees purge;
drop TABLE jobs purge;
drop SEQUENCE departments_seq;
drop TABLE departments purge;
drop TABLE locations purge;
drop TABLE COUNTRIES purge; 
drop TABLE regions purge; 
drop TRIGGER SECURE_EMPLOYEES;
drop TRIGGER UPDATE_JOB_HISTORY;
purge recyclebin;

原脚本整改记录，部分错误排查记录如下：

• 不支持REM，:g/REM/s//–/g :g/Rem/s//–/g :g/INC–ENT/s//INCREMENT/g

• alter table ADD CONSTRAINT不支持同时加多个约束，需要拆分

• 不支持ORGANIZATION INDEX，会报错GS-00101，需要去掉countries表的选项

• 不支持set NLS_LANGUAGE=American，去掉

• 创建视图emp_details_view报错GS-01001, Permissions were insufficient，需要赋权grant create view

• 创建视图不支持WITH READ ONLY，要去掉emp_details_view视图结尾的WITH READ ONLY选项