• 模拟业务最小测试用例02


    Oracle数据库对很多开发人员而言,基本就只是一个存储数据的仓库,只不过这个仓库功能非常强大,额外提供了很多好用的功能,需要的时候会用就好,不会纠结于某个细节。而对很多DBA而言,正好相反,喜欢沉溺于某些细节上,对某些小知识点对应的原理理解的非常透彻,但却往往很少会站在开发层面认识Oracle。
    本文旨在构造一份相对较全面的测试数据,对开发常用的对象都模拟创建一份测试用例,DBA就可以直接拿去做一些基本测试。

    环境:Oracle 11.2.0.4

    1.初始化测试数据

    初始化测试数据的脚本主要包含了如下内容:
    • 1.表空间创建
    • 2.用户创建及赋权
    • 3.表创建
    • 4.索引创建
    • 5.视图、同义词、序列、dblink
    • 6.存储过程、函数、包、定时任务、触发器

    脚本:initData.sh

    #!/bin/bash
    #name:initData.sh
    #function:to initial data for test.
    #usage: oracle用户登录,执行 sh initData.sh > /tmp/initData.log
    
    #logon database
    sqlplus -S / as sysdba <<EOF
    
    
    prompt ============================
    prompt ==  summary         
    prompt ============================
    prompt
    prompt 1.表空间创建 
    prompt 2.用户创建及赋权
    prompt 3.表创建
    prompt 4.索引创建
    prompt 5.视图、同义词、序列、dblink
    prompt 6.存储过程、函数、包、定时任务、触发器
    prompt
    
    prompt ============================
    prompt == 1.表空间创建 
    prompt ============================
    prompt Tablespace:DBS_D_JINGYU
    prompt Tablespace:DBS_I_JINGYU
    prompt TEMPORARY Tablespace:TEMP_JINGYU
    prompt 
    -- 数据表空间
    create tablespace dbs_d_jingyu datafile size 30M autoextend on maxsize 500M;
    -- 临时表空间
    create temporary tablespace temp_jingyu tempfile size 30M autoextend on maxsize 500M;
    -- 索引表空间(可选)
    create tablespace dbs_i_jingyu datafile size 30M autoextend on maxsize 500M;
    
    
    prompt ============================
    prompt == 2.用户创建及赋权
    prompt ============================
    prompt User:jingyu 权限:connect, resource
    prompt User:ludan  权限:connect, resource, dba
    prompt
    -- 假设创建用户 jingyu 密码 jingyu,默认临时表空间 temp_jingyu, 默认数据表空间 dbs_d_jingyu。
    CREATE USER jingyu IDENTIFIED BY jingyu
      TEMPORARY TABLESPACE temp_jingyu
      DEFAULT TABLESPACE dbs_d_jingyu
      QUOTA UNLIMITED ON dbs_d_jingyu;
    
    -- 假设创建用户 ludan 密码 ludan,默认临时表空间temp_jingyu, 默认数据表空间 dbs_d_jingyu。
    CREATE USER ludan IDENTIFIED BY ludan
      TEMPORARY TABLESPACE temp_jingyu
      DEFAULT TABLESPACE dbs_d_jingyu
      QUOTA UNLIMITED ON dbs_d_jingyu;
    
    -- 赋权
    grant resource, connect to jingyu;
    grant resource, connect, dba to ludan;
    
    
    prompt ============================
    prompt == 3.表创建
    prompt ============================
    prompt 
    
    prompt 3.1 普通堆表
    prompt ============================
    prompt
    --3.1 普通堆表
    --prepare 
    conn /as sysdba
    alter user scott identified by tiger account unlock;
    conn scott/tiger
    grant select on emp to jingyu;
    grant select on dept to jingyu;
    
    -- 业务用户登录
    conn jingyu/jingyu
    -- 删除 emp
    --drop table emp cascade constraints purge;
    --drop table dept cascade constraints purge;
    -- 创建emp
    create table emp tablespace dbs_d_jingyu as select * from scott.emp;
    create table dept tablespace dbs_d_jingyu as select * from scott.dept;
    -- 查询T1,T2表数据量
    select count(1) from emp;
    select count(1) from dept;
    -- 创建后面JOB用到的表
    create table t_times(col1 date);
    create table t_sum(col1 date, col2 number);
    create table t_times_old as select * from t_times where 1=2;
    
    -- 业务用户登录
    conn ludan/ludan
    -- 创建 t_obj
    create table t_obj as select * from dba_objects;
    -- 查询 数据量
    select count(1) from t_obj;
    
    prompt 3.2 分区表
    prompt ============================
    prompt
    --3.2 分区表
    conn ludan/ludan
    create table t_obj_part
    partition by range(CREATED)
    (
      partition P2015 values less than (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace dbs_d_jingyu,
      partition P2016 values less than (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace dbs_d_jingyu,
      partition P2017 values less than (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace dbs_d_jingyu,
      partition P2018 values less than (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace dbs_d_jingyu
    ) as select * from t_obj;
    
    select count(1) from t_obj_part partition(P2018);
    
    prompt 3.3 全局临时表
    prompt ============================
    prompt
    --3.3 全局临时表
    conn ludan/ludan
    create global temporary table t_tmp_session on commit preserve rows as select * from dba_objects where 1 = 2;
    create global temporary table t_tmp_transaction on commit delete rows as select * from dba_objects where 1 = 2;
    
    prompt 3.4 索引组织表
    prompt ============================
    prompt
    --3.4 索引组织表
    conn jingyu/jingyu
    --select dbms_metadata.get_ddl('TABLE',upper('emp'),'JINGYU') from dual;
    
    CREATE TABLE emp_iot
       (    "EMPNO" NUMBER(4,0),
            "ENAME" VARCHAR2(10),
            "JOB" VARCHAR2(9),
            "MGR" NUMBER(4,0),
            "HIREDATE" DATE,
            "SAL" NUMBER(7,2),
            "COMM" NUMBER(7,2),
            "DEPTNO" NUMBER(2,0),
            primary key(empno)
       )organization index;
    
    insert into emp_iot select * from emp;
    commit;
    
    --select * from jingyu.emp where empno = 7788;
    --select * from jingyu.emp_iot where empno = 7788;
    
    --select * from jingyu.emp where ename = 'SCOTT';
    --select * from jingyu.emp_iot where ename = 'SCOTT';
    
    prompt 3.5 簇表
    prompt ============================
    prompt
    --3.5 簇表
    conn jingyu/jingyu
    
    create cluster shc
    (cust_id number,
    order_dt timestamp SORT)
    hashkeys 10000
    hash is cust_id
    size 8192;
    
    create table cust_orders(
    cust_id number,
    order_dt timestamp SORT,
    order_number number,
    username varchar2(30),
    ship_addr number,
    bill_addr number,
    invoice_num number
    )
    cluster shc(cust_id, order_dt);
    
    --select * from dba_objects where object_name = 'SHC';
    
    prompt 3.6 外部表
    prompt ============================
    prompt
    --3.6 外部表
    conn /as sysdba
    !mkdir -p /home/oracle/external_table
    create or replace directory external_table as '/home/oracle/external_table';
    grant read,write on directory external_table to jingyu;
    
    conn jingyu/jingyu
    create table ext_emp (ename,job,sal,dname)
    organization external
    (type oracle_datapump default directory external_table location('ext_emp'))
    as select ename,job,sal,dname from emp join dept on emp.deptno=dept.deptno;
    
    
    prompt ============================
    prompt == 4.索引创建
    prompt ============================
    prompt 主键、外键、唯一索引、普通索引、位图索引、函数索引
    prompt
    
    prompt 4.1 普通B-Tree索引
    prompt ============================
    prompt
    --4.1 普通B-Tree索引
    --ludan.idx_t_obj_name
    conn ludan/ludan
    create index idx_t_obj_name on t_obj(object_name) tablespace dbs_i_jingyu;
    --jingyu.empno
    conn jingyu/jingyu
    create index idx_emp_1 on emp(empno, ename) tablespace dbs_i_jingyu;
    create index idx_emp_2 on emp(mgr) tablespace dbs_i_jingyu;
    
    prompt 4.2 唯一索引
    prompt ============================
    prompt
    --4.2 唯一索引
    conn ludan/ludan
    create unique index ux_t_obj_id on t_obj(object_id) tablespace dbs_i_jingyu;
    
    prompt 4.3 位图索引
    prompt ============================
    prompt
    --4.3 位图索引
    conn jingyu/jingyu
    create bitmap index bx_emp_job on emp(job) tablespace dbs_i_jingyu;
    
    prompt 4.4 函数索引
    prompt ============================
    prompt
    --4.4 函数索引
    conn jingyu/jingyu
    create index fx_emp_hiredate on emp(to_char(hiredate,'yyyy-mm-dd')) tablespace dbs_i_jingyu;
    --select * from jingyu.emp where to_char(hiredate,'yyyy-mm-dd') = '1981-11-17';
    
    prompt 4.5 主键、外键
    prompt ============================
    prompt
    --4.5 主键、外键
    conn jingyu/jingyu
    alter table emp add constraint pk_emp_empno primary key(empno);
    alter table dept add constraint pk_dept_deptno primary key(deptno);
    alter table emp add constraint fk_emp_references_dept foreign key(deptno) references dept(deptno);
    
    
    prompt ============================
    prompt == 5.视图、同义词、序列、dblink
    prompt ============================
    prompt 视图、同义词、序列、dblink
    prompt
    
    prompt 5.1 视图
    prompt ============================
    prompt
    --5.1 视图
    conn /as sysdba
    grant create view to jingyu;
    
    conn jingyu/jingyu
    create view emp_high_sal as select * from emp where sal > 3000;
    
    prompt 5.2 同义词
    prompt ============================
    prompt
    --5.2 同义词
    conn ludan/ludan
    create synonym s_emp for jingyu.emp;
    create synonym s_dept for jingyu.dept;
    
    --public synonym
    create public synonym pubic_emp for jingyu.emp;
    create public synonym pubic_dept for jingyu.dept;
    
    prompt 5.3 序列
    prompt ============================
    prompt
    --5.3 序列
    conn jingyu/jingyu
    
    --drop sequence seq_1;
    create sequence seq_1 start with 1 increment by 1;
    
    --drop table t_test_seq purge;
    create table t_test_seq(id number, name varchar2(20));
    insert into t_test_seq values(seq_1.nextval, 'jingyu');
    insert into t_test_seq values(seq_1.nextval, 'jingyu');
    insert into t_test_seq values(seq_1.nextval, 'jingyu');
    commit;
    
    prompt 5.4 dblink
    prompt ============================
    prompt
    --5.4 dblink
    --tnsnames.ora
    --JYZHAO =
    --  (DESCRIPTION =
    --    (ADDRESS = (PROTOCOL = TCP)(HOST = jyrac-scan)(PORT = 1521))
    --    (CONNECT_DATA =
    --      (SERVER = DEDICATED)
    --      (SERVICE_NAME = jyzhao)
    --    )
    --  )
    
    --connect user
    conn ludan/ludan
    --private dblink
    create database link to_jyzhao_jy connect to jingyu identified by jingyu using 'JYZHAO';
    --public dblink
    create public database link to_jyzhao_ld connect to ludan identified by ludan using 'JYZHAO';
    
    prompt ============================
    prompt == 6.存储过程、函数、包、定时任务、触发器
    prompt ============================
    prompt 存储过程、函数、包、定时任务
    prompt
    
    prompt 6.1 存储过程:
    prompt ============================
    prompt
    --6.1 存储过程:
    --功能:可以输入雇员名,新工资,可以修改雇员的工资。
    conn jingyu/jingyu
    create or replace procedure p_update_sal(v_ename varchar2,v_newsal number) is
    	begin
    		update emp set sal=v_newsal where ename=v_ename;
    		commit;
    	end;
    /
    
    prompt 6.2 函数:
    prompt ============================
    prompt
    --6.2 函数:
    --功能:输入雇员的姓名,返回该雇员的年薪。
    conn jingyu/jingyu
    create function f_yearSal(empName varchar2) return number is
        yearSal number(7,2);
        begin
            select sal * 12 + nvl(comm,0) * 12 into yearSal from emp where ename=empName;
            return yearSal;
        end;
    /
    
    --SQL> 调用函数
    --var income number;
    --call f_yearSal('SCOTT') into:income;
    --set serveroutput on
    --exec dbms_output.put_line(:income);
    
    prompt 6.3 包:
    prompt ============================
    prompt
    --6.3 包
    --package
    create package pkg_emp is
        procedure p_update_sal(v_ename varchar2,v_newsal number);
        function f_yearSal(empName varchar2) return number;
    end;
    /
    
    --package body
    create or replace package body pkg_emp is
        procedure p_update_sal(v_ename varchar2,v_newsal number) is
    		begin
    			update emp set sal=v_newsal where ename=v_ename;
    			commit;
    		end;
        function f_yearSal(empName varchar2) return number is
        	yearSal number(7,2);
        	begin
            	select sal * 12 + nvl(comm,0) * 12 into yearSal from emp where ename=empName;
            	return yearSal;
       		end;
       	end;
    /
    
    
    --调用包中的过程:
    --call pkg_emp.p_update_sal('SMITH',133);
    --调用包中的函数:
    --var abc number
    --call pkg_emp.f_yearSal('SMITH') into:abc;
    
    prompt 6.4 JOB:
    prompt ============================
    prompt
    --6.4 JOB
    --select OWNER, JOB_NAME, JOB_STYLE, JOB_TYPE, STATE from DBA_SCHEDULER_JOBS;
    --select JOB_NAME, JOB_STYLE, JOB_TYPE, STATE from USER_SCHEDULER_JOBS;
    conn ludan/ludan
    
    --作业功能:每秒向T_TIMES表插入当前系统时间,运行1w次后终止。
    BEGIN
    sys.dbms_scheduler.create_job( 
    job_name => '"JINGYU"."J_INSERT"',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin
      -- Insert PL/SQL code here
      insert into t_times values(sysdate);
      commit;
    end;',
    repeat_interval => 'FREQ=SECONDLY',
    start_date => to_timestamp_tz('2015-12-09 05:05:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
    job_class => '"DEFAULT_JOB_CLASS"',
    comments => 'Insert the current date into the T_TIMES table.',
    auto_drop => FALSE,
    enabled => FALSE);
    sys.dbms_scheduler.set_attribute( name => '"JINGYU"."J_INSERT"', attribute => 'max_runs', value => 10000); 
    END;
    /
    
    --ENABLE(启动作业)
    exec dbms_scheduler.enable('"JINGYU"."J_INSERT"');
    --DISABLE(禁用作业)
    --exec dbms_scheduler.disable('"JINGYU"."J_INSERT"');
    --DROP_JOB(删除作业)
    --exec dbms_scheduler.drop_job('"J_INSERT"');
    
    
    
    prompt 6.5 触发器:
    prompt ============================
    prompt
    --6.5 Trigger
    conn jingyu/jingyu
    --功能:只要有更新emp表的操作,就会记录sal字段的变化。
    --drop table aud_salary_history;
    create table aud_salary_history(
    empno NUMBER(4),
    old_sal NUMBER(7,2),
    new_sal NUMBER(7,2),
    change_time varchar2(50)
    );
    
    create or replace trigger tri_sal after update
    on emp
        for each row
    begin
        insert into aud_salary_history VALUES
      (:OLD.empno, :OLD.sal, :NEW.sal, TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
    end;
    /
    
    --update emp set sal = 1000 where empno=7369;
    --select * from aud_salary_history;
    --rollback;
    
    prompt ============================
    prompt ===initData Completed!!!====
    prompt ============================
    
    EOF
    

    2.清空测试数据

    清空测试数据的脚本主要包含如下内容:
    • 1.删除用户
    • 2.删除表空间
    • 3.删除public对象

    脚本:delData.sh

    #!/bin/bash
    #name:delData.sh
    #function:to delete data for test.
    #usage: oracle用户登录,执行 sh delData.sh > /tmp/delData.log
    
    #logon database
    sqlplus -S / as sysdba <<EOF
    
    prompt ============================
    prompt == 1.Drop User
    prompt ============================
    prompt 
    --drop user 
    --select s.username, s.sid, s.serial#, p.spid from v$session s, v$process p where s.paddr = p.addr and s.username = 'JINGYU';
    select s.username, s.sid, s.serial#, p.spid from v$session s, v$process p where s.paddr = p.addr and s.username is not null;
    drop user ludan cascade;
    drop user jingyu cascade;
    
    prompt ============================
    prompt == 2.Drop Tablespace
    prompt ============================
    prompt 
    --drop tablespace
    drop tablespace dbs_d_jingyu including contents and datafiles;
    drop tablespace dbs_i_jingyu including contents and datafiles;
    drop tablespace temp_jingyu including contents and datafiles;
    
    prompt ============================
    prompt == 3.Drop Public Objects
    prompt ============================
    prompt 
    --public dblink
    select dbms_metadata.get_ddl('DB_LINK',DB_LINK,'PUBLIC') FROM DBA_DB_LINKS where owner='PUBLIC';
    drop public database link TO_JYZHAO_LD;
    select dbms_metadata.get_ddl('DB_LINK',DB_LINK,'PUBLIC') FROM DBA_DB_LINKS where owner='PUBLIC';
    
    --public synonym
    SELECT DBMS_METADATA.GET_DDL('SYNONYM',a.SYNONYM_NAME,a.owner) FROM DBA_SYNONYMS a where a.owner ='PUBLIC' and table_owner in ('JINGYU','LUDAN');
    drop public synonym PUBIC_DEPT;
    drop public synonym PUBIC_EMP;
    SELECT DBMS_METADATA.GET_DDL('SYNONYM',a.SYNONYM_NAME,a.owner) FROM DBA_SYNONYMS a where a.owner ='PUBLIC' and table_owner in ('JINGYU','LUDAN');
    
    --external tables
    select * from dba_external_tables;
    --外部表会随用户被删除而删除,但外部表真实的文件还在,需要删除掉(rm命令危险,故注释掉手工执行):
    --!rm -rf /home/oracle/external_table
    
    prompt ============================
    prompt ===delData Completed!!!====
    prompt ============================
    
    EOF
    
  • 相关阅读:
    Bresenham画线算法
    DDA算法
    GL_LINES & GL_LINE_STRIP & GL_LINE_LOOP
    贝塞尔曲线
    弱引用
    Lambert模型
    ShadowVolume
    Phong Shading
    求反射向量
    Vertex Modifier of Surface Shader
  • 原文地址:https://www.cnblogs.com/jyzhao/p/9038472.html
Copyright © 2020-2023  润新知