测试技能：在oracle中自用存储过程进行测试数据构造

一、实践

–循环赋值

declare
i integer; --定义变量
begin
i := 101;
loop
/* 插入数据 /
insert into emp (EMP_ID, E_ID, UM_ID, NAME, BIRTHDAY, ADDRESS, POSTID, IDNO, GENDER, MARRY, MARRY_DESC, EM_NATIONAL, PHONE, XLNO, XL, COUNTRY, DEPT_ID, STATE, EMP_TYPE, EMP_TYPE_DESC, UPDATETIME)
values
(to_char(‘10000’+i), to_char(‘50100017847’ +i), CONCAT(‘kekexili’,to_char(i)), CONCAT(‘kekexili’,to_char(i)), ‘1900-01-01’, ‘深圳市XXXX’, ’ ', ‘123456190001012’, ‘m’, ‘M’, ‘已婚’, ‘不详’, ‘13800000000’, ‘10’, ‘大学专科’, ‘USA_PID’, ‘S000023309’, ‘A’, ‘1’, ‘正式员工’, ‘20171105’);
/ 参数递增 /
i := i + 1;
/ 停止条件 */
exit when i > 401;
end loop;
commit;
end;
/

二、其它
–存储过程,游标

declare c_num_acs_sequ number(10);
c_num_busi_scop NUMBER(10);
pk_num number(10);
CURSOR c_sal IS SELECT busi_code FROM OPP_BUSI_ACS_CFG group by busi_code order by busi_code;
begin
select count() INTO c_num_acs_sequ from user_tab_cols where table_name=‘OPP_BUSI_ACS_CFG’ and column_name=‘ACS_SEQU’;
SELECT COUNT() INTO c_num_busi_scop FROM USER_TAB_COLS WHERE TABLE_NAME=‘OPP_BUSI_ACS_CFG’ AND COLUMN_NAME=‘BUSI_SCOPE’;
SELECT COUNT() into pk_num FROM USER_CONSTRAINTS where CONSTRAINT_TYPE=‘P’ and TABLE_NAME=‘OPP_BUSI_ACS_CFG’;
IF c_num_acs_sequ=0 THEN
BEGIN
EXECUTE IMMEDIATE ‘alter table OPP_BUSI_ACS_CFG rename column ACS_ID to ACS_SEQU’;
EXECUTE IMMEDIATE ‘alter table OPP_BUSI_ACS_CFG modify(ACS_SEQU number(10))’;
END;
END IF;
if pk_num>0 then
BEGIN
EXECUTE IMMEDIATE ‘alter table OPP_BUSI_ACS_CFG drop constraint PK_OPP_BUSI_ACS_CFG’;
EXECUTE IMMEDIATE ‘alter table OPP_BUSI_ACS_CFG add constraint PK_OPP_BUSI_ACS_CFG primary key(busi_code,acs_sequ)’;
END;
end if;
SELECT COUNT() into pk_num FROM user_indexes where TABLE_NAME=‘OPP_BUSI_ACS_CFG’;
IF pk_num>0 THEN
BEGIN
EXECUTE IMMEDIATE ‘drop index PK_OPP_BUSI_ACS_CFG’;
END;
END IF;
FOR v_sal IN c_sal LOOP
EXECUTE IMMEDIATE (‘update OPP_BUSI_ACS_CFG set acs_sequ = rownum * 100 where busi_code =’’’ || v_sal.busi_code ||’’’’);
END LOOP;
IF c_num_busi_scop=0 THEN
EXECUTE IMMEDIATE ‘ALTER TABLE OPP_BUSI_ACS_CFG ADD BUSI_SCOPE VARCHAR2(32) DEFAULT NULL’;
END IF;
end;

–本想修改
set serveroutput on
declare
l_cur sys_refcursor;
l_empno varchar2(1000);
v_count varchar2(16) := ‘50100017858’;
begin
open l_cur for
select ODS_DICT_ITEM from dict_mapping where DICT_CODE = ‘NATIONALITY’;

loop
    fetch l_cur into l_empno;
    exit when l_cur%notfound;    
    dbms_output.put_line(l_empno);
    update emp  set EM_NATIONAL = l_empno where e_id = v_count;
    v_count := to_char(to_number(v_count) + 1);
end loop;
commit;

end;
/

–修改
procedure loop_cursor_for
is
begin
declare cursor userinfo_cur is select * from userinfo_table;
begin
FOR userinfo_rec in userinfo_cur
loop
dbms_output.put_line(‘username is:’ || userinfo_rec.user_name);
end loop;
end;
end loop_cursor_for;